Побудова парної регресії в Excel. Математичні методи у психології

У попередніх нотатках предметом аналізу часто ставала окрема числова змінна, наприклад, прибутковість взаємних фондів, час завантаження Web-сторінки або обсяг споживання безалкогольних напоїв. У цій і наступних нотатках ми розглянемо методи передбачення значень числової змінної залежно від значень однієї чи кількох інших числових змінних.

Матеріал буде проілюстрований наскрізним прикладом. Прогнозування обсягу продажу у магазині одягу.Мережа магазинів зниженого в ціні одягу Sunflowers протягом 25 років постійно розширювалася. Проте на даний час компанія не має систематичного підходу до вибору нових торгових точок. Місце, в якому компанія збирається відкрити новий магазинвизначається на основі суб'єктивних міркувань. Критеріями вибору є вигідні умови оренди або подання менеджера про ідеальне розташування магазину. Уявіть, що ви - керівник відділу спеціальних проектів та планування. Вам доручили розробити стратегічний план для відкриття нових магазинів. Цей план повинен містити прогноз річного обсягу продажів у магазинах, що знову відкриваються. Ви вважаєте, що торгова площа безпосередньо пов'язана з обсягом виручки і хочете врахувати цей факт у процесі прийняття рішення. Як розробити статистичну модель, яка дозволяє прогнозувати річний обсяг продажу на основі розміру нового магазину?

Як правило, для прогнозування значень змінної використовується регресійний аналіз. Його мета - розробити статистичну модель, що дозволяє передбачати значення залежної змінної, або відгуку, за значеннями, Крайній міріоднієї, незалежної, чи пояснюючої, змінної. У цьому замітці ми розглянемо просту лінійну регресію - статистичний метод, що дозволяє передбачати значення залежної змінної Yза значеннями незалежної змінної X. У наступних нотатках буде описана модель множинної регресії, призначена для передбачення значень незалежної змінної Yза значеннями кількох залежних змінних ( Х 1 , Х 2 , …, X k).

Завантажити нотатку у форматі або , приклади у форматі

Види регресійних моделей

де ρ 1 - Коефіцієнт автокореляції; якщо ρ 1 = 0 (немає автокореляції), D≈ 2; якщо ρ 1 ≈ 1 (позитивна автокореляція), D≈ 0; якщо ρ 1 = -1 (негативна автокореляція), D ≈ 4.

На практиці застосування критерію Дурбіна-Уотсона ґрунтується на порівнянні величини Dз критичними теоретичними значеннями d Lі d Uдля заданого числаспостережень n, числа незалежних змінних моделі k(для простої лінійної регресії k= 1) та рівня значущості α. Якщо D< d L , гіпотеза про незалежність випадкових відхилень відкидається (отже, є позитивна автокореляція); якщо D > d U, гіпотеза не відкидається (тобто автокореляція відсутня); якщо d L< D < d U немає достатніх підстав для прийняття рішення. Коли розрахункове значення Dперевищує 2, то з d Lі d Uпорівнюється не сам коефіцієнт D, а вираз (4 – D).

Для обчислення статистики Дурбіна-Уотсона в Excel звернемося до нижньої таблиці на рис. 14 Виведення залишку. Чисельник у виразі (10) обчислюється за допомогою функції СУММКВРАЗН(масив1;масив2), а знаменник СУММКВ(масив) (рис. 16).

Мал. 16. Формули розрахунку статистики Дурбіна-Уотсона

У нашому прикладі D= 0,883. Основне питання полягає в наступному - яке значення статистики Дурбіна-Уотсона слід вважати досить малим, щоб зробити висновок про існування позитивної автокореляції? Необхідно співвіднести значення D з критичними значеннями ( d Lі d U), що залежать від числа спостережень nта рівня значущості α (рис. 17).

Мал. 17. Критичні значення статистики Дурбіна-Уотсона (фрагмент таблиці)

Таким чином, у задачі про обсяг продажу в магазині, що доставляє товари додому, існують одна незалежна змінна ( k= 1), 15 спостережень ( n= 15) та рівень значущості α = 0,05. Отже, d L= 1,08 та dU= 1,36. Оскільки D = 0,883 < d L= 1,08, між залишками існує позитивна автокореляція, метод найменших квадратів застосовувати не можна.

Перевірка гіпотез про нахил та коефіцієнт кореляції

Вище регресія застосовувалась виключно для прогнозування. Для визначення коефіцієнтів регресії та передбачення значення змінної Yпри заданій величині змінної Xвикористовувався метод найменших квадратів. Крім того, ми розглянули середньоквадратичну помилку оцінки та коефіцієнт змішаної кореляції. Якщо аналіз залишків підтверджує, що умови застосування методу найменших квадратів не порушуються, і модель простої лінійної регресії є адекватною, на основі вибіркових даних можна стверджувати, що між змінними в генеральній сукупності існує лінійна залежність.

Застосуванняt -Критерію для нахилу.Перевіряючи, чи дорівнює нахил генеральної сукупності β 1 нулю, можна визначити, чи існує статистично значуща залежність між змінними Xі Y. Якщо ця гіпотеза відхиляється, можна стверджувати, що між змінними Xі YІснує лінійна залежність. Нульова та альтернативна гіпотези формулюються наступним чином: Н 0: β 1 = 0 (немає лінійної залежності), Н1: β 1 ≠ 0 (є лінійна залежність). За визначенням t-статистика дорівнює різниці між вибірковим нахилом та гіпотетичним значенням нахилу генеральної сукупності, поділеної на середньоквадратичну помилку оцінки нахилу:

(11) t = (b 1 – β 1 ) / S b 1

де b 1 – нахил прямої регресії за вибірковими даними, β1 – гіпотетичний нахил прямої генеральної сукупності, , а тестова статистика tмає t-розподіл з n – 2ступенями свободи.

Перевіримо, чи існує статистично значуща залежність між розміром магазину та річним обсягом продажу при α = 0,05. t-критерій виводиться поряд з іншими параметрами під час використання Пакет аналізу(опція Регресія). Повністю результати роботи Пакету аналізу наведено на рис. 4 фрагмент, що відноситься до t-статистики - на рис. 18.

Мал. 18. Результати застосування t

Оскільки кількість магазинів n= 14 (див. рис.3), критичне значення t-Статистики при рівні значимості α = 0,05 можна знайти за формулою: t L=СТЬЮДЕНТ.ОБР(0,025;12) = –2,1788, де 0,025 – половина рівня значимості, а 12 = n – 2; t U=СТЬЮДЕНТ.ОБР(0,975;12) = +2,1788.

Оскільки t-статистика = 10,64> t U= 2,1788 (рис. 19), нульова гіпотеза Н 0відхиляється. З іншого боку, р-значення для Х= 10,6411, що обчислюється за формулою =1-СТЬЮДЕНТ.РАСП(D3;12;ІСТИНА), приблизно дорівнює нулю, тому гіпотеза Н 0знову відхиляється. Той факт, що рзначення майже дорівнює нулю, означає, що якби між розмірами магазинів і річним обсягом продажів не існувало реальної лінійної залежності, виявити її за допомогою лінійної регресії було б практично неможливо. Отже, між середнім річним обсягом продажів у магазинах та їх розміром існує статистично значуща лінійна залежність.

Мал. 19. Перевірка гіпотези про нахил генеральної сукупності при рівні значимості, що дорівнює 0,05, та 12 ступенях свободи

ЗастосуванняF -Критерію для нахилу.Альтернативним підходом до перевірки гіпотез про нахил простої лінійної регресії є використання F-Критерія. Нагадаємо, що F-Критерій застосовується для перевірки відносин між двома дисперсіями (докладніше див.). При перевірці гіпотези про нахил мірою випадкових помилок є дисперсія помилки (сума квадратів помилок, поділена на кількість ступенів свободи), тому F-Критерій використовує відношення дисперсії, що пояснюється регресією (тобто величини SSR, поділеної на кількість незалежних змінних k), до дисперсії помилок ( MSE = S YX 2 ).

За визначенням F-статистика дорівнює середньому квадрату відхилень, обумовлених регресією (MSR), поділеному на дисперсію помилки (MSE): F = MSR/ MSE, де MSR =SSR / k, MSE =SSE/(n- k - 1), k– кількість незалежних змінних у регресійній моделі. Тестова статистика Fмає F-розподіл з kі n– k – 1ступенями свободи.

При заданому рівні значимості вирішальне правило формулюється так: якщо F > FUнульова гіпотеза відхиляється; в іншому випадку вона не відхиляється. Результати оформлені у вигляді зведеної таблицідисперсійного аналізу, наведені на рис. 20.

Мал. 20. Таблиця дисперсійного аналізу для перевірки гіпотези про статистичної значимостікоефіцієнта регресії

Аналогічно t-критерію F-критерій виводиться в таблицю під час використання Пакет аналізу(опція Регресія). Цілком результати роботи Пакет аналізунаведено на рис. 4, фрагмент, що відноситься до F-Статистиці - на рис. 21.

Мал. 21. Результати застосування F-критерію, отримані за допомогою Пакету аналізу Excel

F-статистика дорівнює 113,23, а р-значення близько до нуля (комірка ЗначимістьF). Якщо рівень значущості α дорівнює 0,05, визначити критичне значення F-розподілу з одним і 12 ступенями свободи можна за формулою F U= F.ОБР (1-0,05; 1; 12) = 4,7472 (рис. 22). Оскільки F = 113,23 > F U= 4,7472, причому р-значення близько до 0< 0,05, нулевая гипотеза Н 0відхиляється, тобто. Обсяг магазину був із його річним обсягом продажів.

Мал. 22. Перевірка гіпотези про нахилі генеральної сукупності при рівні значимості, що дорівнює 0,05, з одним і 12 ступенями свободи

Довірчий інтервал, що містить нахил β 1 .Для перевірки гіпотези про існування лінійної залежності між змінними можна побудувати довірчий інтервал, що містить нахил 1 і переконатися, що гіпотетичне значення 1 = 0 належить цьому інтервалу. Центром довірчого інтервалу, що містить нахил β 1 , є вибірковий нахил b 1 , а його межами - величини b 1 ±t n –2 S b 1

Як показано на рис. 18, b 1 = +1,670, n = 14, S b 1 = 0,157. t 12 =СТЬЮДЕНТ.ОБР(0,975;12) = 2,1788. Отже, b 1 ±t n –2 S b 1 = +1,670 ± 2,1788 * 0,157 = +1,670 ± 0,342, або + 1,328 ≤ β 1 ≤ +2,012. Таким чином, нахил генеральної сукупності з ймовірністю 0,95 лежить в інтервалі від +1,328 до +2,012 (тобто від 1328000 до 2012000 дол.). Оскільки ці величини більші за нуль, між річним обсягом продажів і площею магазину існує статистично значуща лінійна залежність. Якби довірчий інтервал містив нуль, між змінними не було б залежності. Крім того, довірчий інтервал означає, що кожне збільшення площі магазину на 1000 кв. футів призводить до збільшення середнього обсягу продажів на величину від 1328000 до 2012000 доларів.

Використанняt -Критерію для коефіцієнта кореляції.було введено коефіцієнт кореляції r, що є мірою залежності між двома числовими змінними. З його допомогою можна встановити, чи існує між двома змінними статистично значний зв'язок. Позначимо коефіцієнт кореляції між генеральними сукупностями обох змінних символомρ. Нульова та альтернативна гіпотези формулюються наступним чином: Н 0: ρ = 0 (немає кореляції), Н 1: ρ ≠ 0 (є кореляція). Перевірка існування кореляції:

де r = + , якщо b 1 > 0, r = – , якщо b 1 < 0. Тестовая статистика tмає t-розподіл з n – 2ступенями свободи.

У задачі про мережу магазинів Sunflowers r 2= 0,904, а b 1- +1670 (див. рис. 4). Оскільки b 1> 0, коефіцієнт кореляції між обсягом річних продажів та розміром магазину дорівнює r= +√0,904 = +0,951. Перевіримо нульову гіпотезу, яка стверджує, що між цими змінними немає кореляції, використовуючи t-Статистику:

При рівні значимості α = 0,05 нульову гіпотезу слід відхилити, оскільки t= 10,64> 2,1788. Таким чином, можна стверджувати, що між обсягом річних продажів та розміром магазину існує статистично значущий зв'язок.

При обговоренні висновків, що стосуються нахилу генеральної сукупності, довірчі інтервали та критерії перевірки гіпотез є взаємозамінними інструментами. Проте обчислення довірчого інтервалу, що містить коефіцієнт кореляції, виявляється складнішою справою, оскільки вид вибіркового розподілу статистики. rзалежить від справжнього коефіцієнта кореляції.

Оцінка математичного очікування та передбачення індивідуальних значень

У цьому розділі розглядаються методи оцінки математичного очікування відгуку Yта передбачення індивідуальних значень Yпри заданих значеннях змінної X.

Побудова довірчого інтервалу.У прикладі 2 (див. вище розділ Метод найменших квадратів) регресійне рівняннядозволило передбачити значення змінної Y X. У задачі про вибір місця для торгової точкисередній річний обсяг продажу у магазині площею 4000 кв. футів дорівнював 7,644 млн. дол. Однак ця оцінка математичного очікування генеральної сукупності є точковою. для оцінки математичного очікування генеральної сукупності було запропоновано концепцію довірчого інтервалу. Аналогічно можна запровадити поняття довірчого інтервалу для математичного очікування відгукупри заданому значеннізмінної X:

де , = b 0 + b 1 X i- Передбачене значення змінне Yпри X = X i, S YX- Середньоквадратична помилка, n- Обсяг вибірки, Xi- задане значення змінної X, µ Y|X = Xi– математичне очікування змінної Yпри Х = Х i, SSX =

Аналіз формули (13) показує, що ширина довірчого інтервалу залежить від кількох факторів. При заданому рівні значущості зростання амплітуди коливань навколо лінії регресії, виміряне за допомогою середньоквадратичної помилки, призводить до збільшення ширини інтервалу. З іншого боку, як і слід було очікувати, збільшення обсягу вибірки супроводжується звуженням інтервалу. Крім того, ширина інтервалу змінюється в залежності від значень Xi. Якщо значення змінної Yпередбачається для величин Xблизьких до середнього значення , Довірчий інтервал виявляється вже, ніж при прогнозуванні відгуку для значень, далеких від середнього.

Припустимо, що, вибираючи місце для магазину, ми хочемо побудувати 95% довірчий інтервал для середнього річного обсягу продажів у всіх магазинах, площа яких дорівнює 4000 кв. футів:

Отже, середній річний обсяг продажів у всіх магазинах, площа яких дорівнює 4000 кв. футів, з 95%-ною ймовірністю лежить в інтервалі від 6,971 до 8,317 млн. дол.

Обчислення довірчого інтервалу для передбаченого значення.Крім довірчого інтервалу для математичного очікування відгуку при заданому значенні змінної Xчасто необхідно знати довірчий інтервал для передбаченого значення. Незважаючи на те, що формула для обчислення такого довірчого інтервалу дуже схожа на формулу (13), цей інтервал містить передбачене значення, а не оцінку параметра. Інтервал для передбаченого відгуку YX = Xiпри конкретному значенні змінної Xiвизначається за формулою:

Припустимо, що, вибираючи місце для торгової точки, ми хочемо побудувати 95% довірчий інтервал для передбаченого річного обсягу продажів у магазині, площа якого дорівнює 4000 кв. футів:

Отже, передбачений річний обсяг продажів у магазині, площа якого дорівнює 4000 кв. футів, з 95%-ной ймовірністю лежить в інтервалі від 5,433 до 9,854 млн. дол. Як бачимо, довірчий інтервал для передбаченого значення відгуку набагато ширший, ніж довірчий інтервал для його математичного очікування. Це тим, що мінливість при прогнозуванні індивідуальних значень набагато більше, ніж за оцінці математичного очікування.

Підводне каміння та етичні проблеми, пов'язані із застосуванням регресії

Проблеми, пов'язані з регресійним аналізом:

Ігнорування умов застосування методу найменших квадратів.
Помилкова оцінка умов застосування методу найменших квадратів.
Неправильний вибір альтернативних методів у разі порушення умов застосування методу найменших квадратів.
Застосування регресійного аналізубез глибоких знань про предмет дослідження.
Екстраполяція регресії за межі діапазону зміни змінної, що пояснює.
Плутанина між статистичною та причинно-наслідковою залежностями.

Широке розповсюдження електронних таблицьі програмного забезпеченнядля статистичних розрахунків ліквідувало обчислювальні проблеми, що перешкоджали застосуванню регресійного аналізу. Однак це призвело до того, що регресійний аналіз стали застосовувати користувачі, які не мають достатньої кваліфікації та знань. Звідки користувачам знати про альтернативні методи, якщо багато хто з них взагалі не має жодного поняття про умови застосування методу найменших квадратів і не вміє перевіряти їх виконання?

Дослідник не повинен захоплюватися перемелюванням чисел - обчисленням зсуву, нахилу та коефіцієнта змішаної кореляції. Йому потрібні глибші знання. Проілюструємо це класичним прикладом, взятим із підручників. Анскомб показав, що це чотири набору даних, наведених на рис. 23, мають одні й самі параметри регресії (рис. 24).

Мал. 23. Чотири набори штучних даних

Мал. 24. Регресійний аналіз чотирьох штучних наборів даних; виконаний за допомогою Пакет аналізу(Клікніть на малюнку, щоб збільшити зображення)

Отже, з погляду регресійного аналізу, всі ці набори даних абсолютно ідентичні. Якби аналіз був на цьому закінчений, ми втратили б багато корисної інформації. Про це свідчать діаграми розкиду (рис. 25) та графіки залишків (рис. 26), побудовані для цих наборів даних.

Мал. 25. Діаграми розкиду для чотирьох наборів даних

Діаграми розкиду та графіки залишків свідчать про те, що ці дані відрізняються одна від одної. Єдиний набір, розподілений уздовж прямої лінії, - набір А. Графік залишків, обчислених за набором А, немає закономірності. Цього не можна сказати про набори Б, В та Г. Графік розкиду, побудований за набором Б, демонструє яскраво виражену квадратичну модель. Цей висновок підтверджується графіком залишків, які мають параболічну форму. Діаграма розкиду та графік залишків показують, що набір даних містить викид. У цій ситуації необхідно виключити викид із набору даних та повторити аналіз. Метод, що дозволяє виявляти та виключати викиди зі спостережень, називається аналізом впливу. Після виключення викиду результат повторної оцінки моделі може бути зовсім іншим. Діаграма розкиду, побудована за даними набору Р, ілюструє незвичайну ситуацію, у якій емпірична модель значно залежить від окремого відгуку ( Х 8 = 19, Y 8 = 12,5). Такі регресійні моделі необхідно обчислювати особливо ретельно. Отже, графіки розкиду та залишків є вкрай необхідним інструментомрегресійного аналізу та повинні бути його невід'ємною частиною. Без них регресійний аналіз не заслуговує на довіру.

Мал. 26. Графіки залишків для чотирьох наборів даних

Як уникнути підводного каміння при регресійному аналізі:

Аналіз можливого взаємозв'язку між змінними Xі Yзавжди починайте з побудови діаграми розкиду.
Перш ніж інтерпретувати результати регресійного аналізу, перевіряйте умови його застосування.
Побудуйте графік залежності залишків від незалежної змінної. Це дозволить визначити, наскільки емпірична модель відповідає результатам спостереження, та виявити порушення сталості дисперсії.
Для перевірки припущення про нормальному розподіліпомилок використовуйте гістограми, діаграми «ствол та листя», блокові діаграми та графіки нормального розподілу.
Якщо умови застосування методу найменших квадратів не виконуються, використовуйте альтернативні методи(наприклад, моделі квадратичної чи множинної регресії).
Якщо умови застосування методу найменших квадратів виконуються, необхідно перевірити гіпотезу про статистичну значущість коефіцієнтів регресії та побудувати довірчі інтервали, що містять математичне очікування та передбачене значення відгуку.
Уникайте передбачати значення залежної змінної за межами діапазону зміни незалежної змінної.
Майте на увазі, що статистичні залежностіне завжди є причинно-наслідковими. Пам'ятайте, що кореляція між змінними не означає наявності причинно-наслідкової залежності між ними.

РезюмеЯк показано на структурній схемі (рис. 27), у замітці описані модель простої лінійної регресії, умови її застосування та способи перевірки цих умов. Розглянуто t-Критерій для перевірки статистичної значущості нахилу регресії Для передбачення значень залежною змінною використано регресійну модель. Розглянуто приклад, пов'язаний із вибором місця для торгової точки, в якому досліджується залежність річного обсягу продажу від площі магазину. Отримана інформація дозволяє точніше вибрати місце для магазину та передбачити його річний обсяг продажу. У наступних нотатках буде продовжено обговорення регресійного аналізу, а також розглянуто моделі множинної регресії.

Мал. 27. Структурна схеманотатки

Використовуються матеріали книги Левін та ін. Статистика менеджерів. - М.: Вільямс, 2004. - с. 792–872

Якщо залежна змінна є категорійною, необхідно застосовувати логістичну регресію.

Пакет MS Excel дозволяє при побудові рівняння лінійної регресії більшу частину роботи зробити дуже швидко. Важливо зрозуміти, як інтерпретувати отримані результати. Для побудови моделі регресії необхідно вибрати пункт Сервіс\Аналіз даних\Регресія (в Excel 2007 цей режим знаходиться в блоці Дані/Аналіз даних/Регресія). Потім отримані результати скопіювати блок для аналізу.

Вихідні дані:

Результати аналізу

Включати у звіт
Розрахунок параметрів рівняння регресії
Теоретичний матеріал
Рівняння регресії у стандартному масштабі
Множинний коефіцієнт кореляції (Індекс множинної кореляції)
Приватні коефіцієнти еластичності
Порівняльна оцінка впливу аналізованих факторів на результативну ознаку (d – коефіцієнти роздільної детермінації)

Перевірка якості збудованого рівняння регресії
Значимість коефіцієнтів регресії b i (t-статистика. критерій Стьюдента)
Значення рівняння загалом (F-статистика. Критерій Фішера). Коефіцієнт детермінації
Приватні F-критерії

Рівень значущості 0.005 0.01 0.025 0.05 0.1 0.25 0.4

Тема: КОРЕЛЯЦІЙНИЙ І РЕГРЕСІЙНИЙ АНАЛІЗ ВEXCEL

ЛАБОРАТОРНА РОБОТА №1

1. ВИЗНАЧЕННЯ КОЕФІЦІЄНТА ПАРНОЇ КОРРЕЛЯЦІЇ У ПРОГРАМІEXCEL

Кореляційний зв'язок- це неповна, імовірнісна залежність між показниками, яка проявляється лише у масі спостережень.

Парна кореляція- це зв'язок між двома показниками, один із яких є факторним, а інший – результативним.

Множинна кореляціявиникає від взаємодії кількох факторів із результативним показником.

Необхідні умови застосування кореляційного аналізу:

1. Наявність досить великої кількості спостережень про величину досліджуваних факторних та результативних показників.

2. Досліджувані фактори повинні мати кількісний вимір та відображення у тих чи інших джерелах інформації.

Застосування кореляційного аналізу дозволяє вирішити такі завдання:

1.Визначити зміну результативного показника під впливом одного чи кількох факторів.

2. Встановити відносний рівень залежності результативного показника від кожного фактора.

Завдання 1.

Є дані щодо 20 сільськогосподарських господарств. Знайти коефіцієнт кореляціїміж величинами врожайності зернових культур та якістю землі та оцінити його значимість. Дані наведені у таблиці.

Таблиця. Залежність урожайності зернових культур від якості землі

Номер господарства	Якість землі, бал х	Урожайність, ц/га у

Для знаходження коефіцієнта кореляції використовувати функцію Корел.

Значимість коефіцієнта кореляції перевіряється за критерієм Стьюдента.

Для прикладу r=0,99, n=18.

Для знаходження квантилю розподілу Стьюдента використовується функція СТЬЮДРАСПОБР з наступними аргументами: Ймовірність –0,05, Ступені свободи –18.

Порівнявши значення t-статистики з квантилем розподілу Стьюдента зробити висновки про значення коефіцієнта парної кореляції. Якщо розрахункове значення t-статистики більше за квантиль розподілу Стьюдента, то величина коефіцієнта кореляції є значущою.

ПОБУДУВАННЯ РЕГРЕСІЙНОЇ МОДЕЛІ ЗВ'ЯЗКУ МІЖ ДВОМА ВЕЛИЧИНАМИ

Завдання 2.

За даними завдання 1:

1) побудувати рівняння регресії (лінійну модель), що характеризує прямолінійну залежність між якістю землі та врожайністю;

2). виконати перевірку адекватності одержаної моделі.

1 - ий спосіб.

1. На аркуші Excel виділити масив вільних осередків із п'яти рядків та двох стовпців.

2. Викликати функцію Лінейн.

3.Вказати для функції такі аргументи: Із_знач_y Врожайність, ц/га;Із_знач_x- стовпець значень показника Якість землі, бал; Константа -1, Стат - 1(дозволяє обчислити показники, що використовуються для перевірки адекватності моделі. Якщо Стат-0,то такі показники не обчислюватимуться.

4. Натиснути клавішу клавіш Ctrl- Shift- Enter.

У виділені осередки виводяться коефіцієнти моделі, і навіть показники, що дозволяють перевірити модель адекватність (таблиця 2).

Таблиця 2
a 1	a 0
S e1	S e0
R 2	S e

Q R	Q e

a 1 , a 0 - Коефіцієнти моделі;

S e 1 S e 0 - Стандартні помилки коефіцієнтів. Чим точніше модель, тим менші ці величини.

R 2 - Коефіцієнт детермінації. Чим він більший, тим точніше модель.

F- Статистика для перевірки значущості моделі.

n- k-1 – число ступенів свободи (n-обсяг вибірки, k- кількість вхідних змінних; у цьому прикладі n=20, к=1)

Q R- Сума квадратів, обумовлена регресією;

Q e- Сума квадратів помилок.

5. Для перевірки адекватності моделі знайти квантиль розподілу Фішера F f . за допомогою функції FРОЗКЛАД. Для цього в будь-якому вільному осередку ввести функцію FРОЗКЛАДз наступними аргументами: Ймовірність – 0,05, Ступені_свободи _1–1, Ступені_свободи _2–18. Якщо F> F f то модель адекватна вихідним даним

6. Перевірити адекватність збудованої моделі, використовуючи розрахунковий рівень значущості (P). Ввести функцію FРОЗПІЛз наступними аргументами: X– значення статистики F, Ступені_свободи_1–1, Ступені_свободи_2- 18. Якщо розрахунковий рівень значущості P<α =0,05, то модель адекватна исходным данным.

2-й спосіб.

Визначення коефіцієнтів моделі з отриманням показників для перевірки її адекватності та значущості коефіцієнтів.

Вибрати команду Сервіс/Аналіз даних/Регресія. У діалоговому вікні встановити: Вхідний інтервалY– значення показника Врожайність, ц/га,Вхідний інтервалX– значення показника Якість землі, бал.

Встановити прапорець Мітки. В області Параметри виводувибрати перемикач Вихідний інтервалі вказати комірку, з якої почнеться виведення результатів. Щоб отримати результати, натисніть кнопку ОК.

Інтерпретація результатів.

Коефіцієнти моделі, що шукаються, знаходяться в стовпці Коефіцієнти:

Для цього прикладу рівняння моделі має вигляд:

Y=2,53+0,5X

У цьому прикладі зі збільшенням якості ґрунту на один бал урожайність зернових культур підвищується в середньому на 0,5 ц/га.

Перевірка адекватності моделівиконується за розрахунковим рівнем значимості P, вказаним у стовпці ЗначимістьF. Якщо розрахунковий рівень значущості менший за заданий рівень значущості α =0,05, то модель адекватна.

Перевірка статистичної значущостікоефіцієнтів моделі виконується за розрахунковими рівнями значимості P, зазначеними у стовпці P-значення. Якщо розрахунковий рівень значущості менший за заданий рівень значущості α =0,05, то відповідний коефіцієнт моделі статистично значущий.

МножиннийR – коефіцієнт кореляції. Чим ближче його величина до 1, тим більше тісний зв'язок між показниками, що вивчаються. Для цього прикладу R= 0,99. Це дозволяє зробити висновок, що якість землі – один із основних факторів, від якого залежить врожайність зернових культур.

R-квадрат – коефіцієнт детермінації. Він виходить зведенням у квадрат коефіцієнта кореляції – R 2 =0,98. Він показує, що врожайність зернових культур на 98% залежить від якості ґрунту, а на частку інших факторів припадає 0,02%.

Третій метод. ГРАФІЧНИЙ СПОСІБ ПОБУДУВАННЯ МОДЕЛІ.

Самостійно побудувати точкову діаграму, що відбиває зв'язок між урожайністю та якістю землі.

Здобути лінійну модель залежності врожайності зернових культур від якості землі.

Кореляційно-регресійний аналізMS EXCEL

1. Створіть файл вихідних даних у MS Excel (наприклад, таблиця 2)

2. Побудова кореляційного поля

Для побудови кореляційного поля у командному рядку вибираємо меню Вставка/Діаграма. У діалоговому вікні виберіть тип діаграми: Крапкова; вигляд: Точкова діаграма, Що дозволяє порівняти пари значень (Рис. 22).

Рисунок 22 – Вибір типу діаграми

Рисунок 23 – Вигляд вікна при виборі діапазону та рядів
Малюнок 25 – Вид вікна, крок 4

2. У контекстному меню вибираємо команду Додати лінію тренду.

3. У діалоговому вікні, що з'явилося, вибираємо тип графіка (у нашому прикладі лінійна) і параметри рівняння, як показано на малюнку 26.

Натискаємо ОК. Результат представлений малюнку 27.

Рисунок 27 – Кореляційне поле залежності продуктивності праці від фондовооруженности

Аналогічно будуємо кореляційне поле залежності продуктивність праці від коефіцієнта змінності устаткування. (Малюнок 28).

Рисунок 28 – Кореляційне поле залежності продуктивності праці

від коефіцієнта змінності обладнання

3. Побудова кореляційної матриці.

Для побудови кореляційної матриці у меню Сервісобираємо Аналіз даних.

За допомогою інструмента аналізу даних РегресіяКрім результатів регресійної статистики, дисперсійного аналізу та довірчих інтервалів, можна отримати залишки та графіки підбору лінії регресії, залишків та нормальної ймовірності. Для цього потрібно перевірити доступ до пакету аналізу. У головному меню виберіть послідовно Сервіс / Надбудови. Встановіть прапорець Пакет аналізу(Малюнок 29)

Рисунок 30 – Діалогове вікно Аналіз даних

Після натискання ОК в діалоговому вікні, що з'явилося, вказуємо вхідний інтервал (у нашому прикладі А2:D26), групування (у нашому випадку по стовпцях) і параметри виведення, як показано на малюнку 31.

Рисунок 31 – Діалогове вікно Кореляція

Результат розрахунків представлений у таблиці 4.

Таблиця 4 - Кореляційна матриця

	Стовпець 1	Стовпець 2	Стовпець 3
Стовпець 1
Стовпець 2
Стовпець 3

ОДНОФАКТОРНИЙ РЕГРЕСІЙНИЙ АНАЛІЗ

ІЗ ЗАСТОСУВАННЯМ ІНСТРУМЕНТА РЕГРЕСІЇ

Для проведення регресійного аналізу залежності продуктивності праці від фондоозброєності у меню Сервісобираємо Аналіз данихта вказуємо інструмент аналізу Регресія(Малюнок 32).

Рисунок 33 – Діалогове вікно Регресія

Регресійний та кореляційний аналіз – статистичні методи дослідження. Це найпоширеніші способи показати залежність будь-якого параметра від однієї чи кількох незалежних змінних.

Нижче на конкретних практичних прикладах розглянемо ці два дуже популярні серед економістів аналізу. А також наведемо приклад отримання результатів при їх об'єднанні.

Регресійний аналіз у Excel

Показує вплив одних значень (самостійних, незалежних) на залежну змінну. Наприклад, як залежить кількість економічно активного населення кількості підприємств, величини зарплати та інших. властивостей. Або як впливають іноземні інвестиції, ціни на енергоресурси та ін на рівень ВВП.

Результат аналізу дає змогу виділяти пріоритети. І ґрунтуючись на головних чинниках, прогнозувати, планувати розвиток пріоритетних напрямів, приймати управлінські рішення.

Регресія буває:

лінійної (у = а + bx);
параболічній (y = a + bx + cx 2);
експоненційною (y = a * exp (bx));
статечної (y = a * x ^ b);
гіперболічної (y = b/x + a);
логарифмічної (y = b * 1n(x) + a);
показовою (y = a * b^x).

Розглянемо з прикладу побудова регресійної моделі в Excel і інтерпретацію результатів. Візьмемо лінійний тип регресії.

Завдання. На 6 підприємствах була проаналізована середньомісячна заробітна плата і кількість співробітників, що звільнилися. Необхідно визначити залежність кількості співробітників, що звільнилися, від середньої зарплати.

Модель лінійної регресії має такий вигляд:

У = а 0 + а 1 х 1 + ... + а до х к.

Де а – коефіцієнти регресії, х – що впливають змінні, до – число чинників.

У нашому прикладі як У виступає показник працівників, що звільнилися. фактор, що впливає - заробітна плата (х).

У Excel існують інтегровані функції, з допомогою яких можна розрахувати параметри моделі лінійної регресії. Але найшвидше це зробить надбудова «Пакет аналізу».

Активуємо потужний аналітичний інструмент:

Після активації надбудова буде доступна на вкладці "Дані".

Тепер візьмемося безпосередньо регресійним аналізом.

Насамперед звертаємо увагу на R-квадрат та коефіцієнти.

R-квадрат – коефіцієнт детермінації. У прикладі – 0,755, чи 75,5%. Це означає, що розрахункові параметри моделі на 75,5% пояснюють залежність між параметрами, що вивчаються. Що коефіцієнт детермінації, то якісніша модель. Добре – понад 0,8. Погано – менше 0,5 (такий аналіз навряд можна вважати резонним). У нашому прикладі - "непогано".

Коефіцієнт 64,1428 показує, яким буде Y, якщо всі змінні в моделі, що розглядається, будуть рівні 0. Тобто на значення аналізованого параметра впливають і інші фактори, не описані в моделі.

p align="justify"> Коефіцієнт -0,16285 показує вагомість змінної Х на Y. Тобто середньомісячна заробітна плата в межах даної моделі впливає на кількість звільнених з вагою -0,16285 (це невеликий ступінь впливу). Знак «-» вказує на негативний вплив: що більше зарплата, то менше звільнених. Що слушно.

Кореляційний аналіз у Excel

Кореляційний аналіз допомагає встановити, чи між показниками в одній або двох вибірках є зв'язок. Наприклад, між часом роботи верстата та вартістю ремонту, ціною техніки та тривалістю експлуатації, зростанням та вагою дітей тощо.

Якщо зв'язок є, то чи тягне збільшення одного параметра підвищення (позитивна кореляція) чи зменшення (негативна) іншого. Кореляційний аналіз допомагає аналітику визначитися, чи можна за величиною одного показника передбачити можливе значення іншого.

Коефіцієнт кореляції позначається r. Варіюється в межах від +1 до -1. Класифікація кореляційних зв'язків для різних сфер відрізнятиметься. При значенні коефіцієнта 0 лінійної залежності між вибірками немає.

Розглянемо, як з допомогою засобів Excel визначити коефіцієнт кореляції.

Для знаходження парних коефіцієнтів застосовується функція Корел.

Завдання: Визначити, чи є взаємозв'язок між часом роботи токарного верстата та вартістю його обслуговування.

Ставимо курсор у будь-яку комірку і натискаємо кнопку fx.

У категорії «Статистичні» вибираємо функцію КОРРЕЛ.
Аргумент "Масив 1" - перший діапазон значень - час роботи верстата: А2: А14.
Аргумент "Масив 2" - другий діапазон значень - вартість ремонту: В2: В14. Тиснемо ОК.

Щоб визначити тип зв'язку, потрібно подивитися абсолютну кількість коефіцієнта (для кожної сфери діяльності є своя шкала).

Для кореляційного аналізу кількох параметрів (більше 2) зручніше застосовувати "Аналіз даних" (надбудова "Пакет аналізу"). У списку потрібно вибрати кореляцію та позначити масив. Всі.

Отримані коефіцієнти відобразяться у кореляційній матриці. На кшталт такий: