Регресійний аналіз є одним із найбільш затребуваних методів статистичного дослідження. З його допомогою можна встановити рівень впливу незалежних величин на залежну змінну. У функціоналі Microsoft Excelє інструменти, призначені щодо такого аналізу. Давайте розберемо, що вони являють собою і як ними користуватися.
Підключення пакету аналізу
Але для того, щоб використовувати функцію, що дозволяє провести регресійний аналіз, перш за все, потрібно активувати Пакет аналізу. Тільки тоді необхідні для цієї процедури інструменти з'являться на Стрічці Ексель.
- Переміщуємось у вкладку «Файл».
- Переходимо до розділу «Параметри».
- Відкриється вікно параметрів Excel. Переходимо до підрозділу «Надбудови».
- У самій нижній частині вікна переставляємо перемикач у блоці «Управління» у позицію « Надбудови Excel», якщо він перебуває в іншому положенні. Тиснемо на кнопку «Перейти».
- Відкриється вікно доступних надбудов Ексель. Ставимо галочку біля пункту "Пакет аналізу". Тиснемо на кнопку «OK».
Тепер, коли ми перейдемо у вкладку "Дані", на стрічці в блоці інструментів "Аналіз" ми побачимо нову кнопку - "Аналіз даних".
Види регресійного аналізу
Існує кілька видів регресій:
- параболічна;
- статечна;
- логарифмічна;
- експонентна;
- показова;
- гіперболічна;
- Лінійна регресія.
Про виконання останнього виду регресійного аналізу в Екселі ми докладніше поговоримо далі.
Лінійна регресія у програмі Excel
Внизу, як приклад, представлена таблиця, в якій зазначено середньодобову температуру повітря на вулиці, та кількість покупців магазину за відповідний робочий день. Давайте з'ясуємо за допомогою регресійного аналізу, як саме погодні умовияк температури повітря можуть вплинути на відвідуваність торгового закладу.
Загальне рівняння регресії лінійного вигляду має такий вигляд: У = а0 + а1х1 +…+акхк. У цій формулі Y означає змінну вплив факторів на яку ми намагаємося вивчити. У нашому випадку це кількість покупців. Значення x – це різні чинники, що впливають змінну. Параметри є коефіцієнтами регресії. Тобто саме вони визначають значущість того чи іншого чинника. Індекс k позначає загальну кількість цих факторів.
Розбір результатів аналізу
Результати регресійного аналізу виводяться у вигляді таблиці там, яке зазначено в настройках.
Одним із основних показників є R-квадрат. У ньому вказується якість моделі. У нашому випадку цей коефіцієнт дорівнює 0,705 або близько 70,5%. Це прийнятний рівень якості. Залежність менше ніж 0,5 є поганою.
Ще один важливий показникрозташований у осередку на перетині рядка «Y-перетин» та стовпця «Коефіцієнти». Тут вказується яке значення буде у Y, а в нашому випадку, це кількість покупців, за всіх інших факторів рівних нулю. У цій таблиці дане значеннядорівнює 58,04.
Значення на перетині граф «Змінна X1» та «Коефіцієнти» показує рівень залежності Y від X. У нашому випадку це рівень залежності кількості клієнтів магазину від температури. Коефіцієнт 1,31 вважається досить високим показникомвпливу.
Як бачимо, за допомогою програми Microsoft Excel досить легко скласти таблицю регресійного аналізу. Але працювати з отриманими на виході даними і розуміти їх суть зможе лише підготовлена людина.
Ми раді, що змогли допомогти Вам у вирішенні проблеми.
Задайте своє питання у коментарях, детально розписавши суть проблеми. Наші фахівці намагатимуться відповісти максимально швидко.
Чи допомогла вам ця стаття?
Метод лінійної регресіїдозволяє нам описувати пряму лінію, що максимально відповідає ряду впорядкованих пар (x, y). Рівняння для прямої лінії, відоме як лінійне рівняння, наведено нижче:
ŷ - очікуване значення у при заданому значенніх,
x - незалежна змінна,
a - відрізок на осі y для прямої лінії,
b - нахил прямої лінії.
На малюнку нижче це поняття представлено графічно:
На малюнку вище показана лінія, описана рівнянням = 2+0.5х. Відрізок на осі у - це точка перетину лінією осі у; у разі а = 2. Нахил лінії, b, відношення підйому лінії до довжини лінії, має значення 0.5. Позитивний нахил означає, що лінія піднімається зліва направо. Якщо b = 0, лінія горизонтальна, а це означає, що між залежною та незалежною змінними немає жодного зв'язку. Інакше кажучи, зміна значення x впливає значення y.
Часто плутають ŷ та у. На графіку показано 6 упорядкованих пар точок та лінія, відповідно до даного рівняння
На цьому малюнку показана точка, що відповідає впорядкованій парі х = 2 і у = 4. Зверніть увагу, що очікуване значення у відповідно до лінії при х= 2 є ŷ. Ми можемо підтвердити це за допомогою наступного рівняння:
ŷ = 2 + 0.5х = 2 +0.5 (2) = 3.
Значення у являє собою фактичну точку, а значення ŷ - це очікуване значення у з використанням лінійного рівнянняпри заданому значенні х.
Наступний крок - визначити лінійне рівняння, що максимально відповідає набору впорядкованих пар, про це ми говорили в попередній статті, де визначали вид рівняння за методом найменших квадратів.
Використання Excel для визначення лінійної регресії
Для того, щоб скористатися інструментом регресійного аналізу, вбудованого в Excel, необхідно активувати надбудову Пакет аналізу. Знайти її можна, перейшовши вкладкою Файл -> Параметри(2007+), у діалоговому вікні, що з'явилося. ПараметриExcelпереходимо у вкладку Надбудови.В полі Управлінняобираємо НадбудовиExcelі клацаємо Перейти.У вікні, що з'явилося, ставимо галочку навпроти Пакет аналізу,тиснемо ОК.
У вкладці Данів групі Аналізз'явиться нова кнопка Аналіз даних.
Щоб продемонструвати роботу надбудови, скористаємося даними з попередньої статті, де хлопець та дівчина ділять столик у ванній кімнаті. Введіть дані нашого прикладу з ванною в стовпці А та В чистого листа.
Перейдіть у вкладку Дані,в групі Аналізклацніть Аналіз даних.У вікні, що з'явилося Аналіз данихВиберіть Регресія, як показано на малюнку, і натисніть кнопку ОК.
Встановіть необхідні параметри регресії у вікні Регресія, як показано на малюнку:
Клацніть ОК.На малюнку нижче показані отримані результати:
Ці результати відповідають тим, що ми отримали шляхом самостійних обчислень у попередній статті.
Регресійний аналіз – це статистичний методдослідження, що дозволяє показати залежність тієї чи іншої параметра від однієї чи кількох незалежних змінних. У докомп'ютерну еру його застосування було досить складно, особливо якщо йшлося про великих обсягахданих. Сьогодні, дізнавшись, як побудувати регресію в Excel, можна вирішувати складні статистичні завдання буквально за пару хвилин. Нижче представлені конкретні прикладиз галузі економіки.
Види регресії
Саме це поняття було введено в математику Френсісом Гальтоном у 1886 році. Регресія буває:
- лінійної;
- параболічній;
- статечною;
- експоненційною;
- гіперболічній;
- показовою;
- логарифмічні.
Приклад 1
Розглянемо завдання визначення залежності кількості членів колективу, що звільнилися, від середньої зарплати на 6 промислових підприємствах.
Завдання. На шести підприємствах проаналізували середньомісячну заробітну плату та кількість працівників, які звільнилися за власним бажанням. У табличній формі маємо:
Для завдання визначення залежності кількості працівників, що звільнилися, від середньої зарплати на 6 підприємствах модель регресії має вигляд рівняння Y = а0 + а1×1 +…+аkxk, де хi - що впливають змінні, ai - коефіцієнти регресії, a k - число факторів.
Для цього завдання Y - це показник співробітників, що звільнилися, а впливаючий фактор - зарплата, яку позначаємо X.
Використання можливостей табличного процесора «Ексель»
Аналізу регресії в Excel має передувати застосування наявних табличних даних вбудованих функцій. Однак для цього краще скористатися дуже корисною надбудовою «Пакет аналізу». Для його активації потрібно:
- з вкладки "Файл" перейти до розділу "Параметри";
- у вікні вибрати рядок «Надбудови»;
- клацнути на кнопці «Перейти», розташованої внизу, праворуч від рядка «Управління»;
- поставити галочку поруч із назвою «Пакет аналізу» та підтвердити свої дії, натиснувши «Ок».
Якщо все зроблено правильно, у правій частині вкладки "Дані", розташованому над робочим листом "Ексель", з'явиться потрібна кнопка.
Лінійна регресія в Excel
Тепер, коли під рукою є всі необхідні віртуальні інструменти для здійснення економетричних розрахунків, можемо розпочати вирішення нашого завдання. Для цього:
- клацаємо по кнопці «Аналіз даних»;
- у вікні натискаємо на кнопку «Регресія»;
- в вкладку, що з'явилася, вводимо діапазон значень для Y (кількість звільнених працівників) і для X (їх зарплати);
- підтверджуємо свої дії, натиснувши кнопку «Ok».
В результаті програма автоматично заповнить новий лист табличного процесораданими аналізу регресії. Зверніть увагу! В Excel є можливість самостійно задати місце, якому ви надаєте перевагу для цієї мети. Наприклад, це може бути той самий лист, де знаходяться значення Y і X, або навіть нова книгаспеціально призначена для зберігання подібних даних.
Аналіз результатів регресії для R-квадрату
У Excel даніотримані в ході обробки даних прикладу, що розглядається, мають вигляд:
Насамперед, слід звернути увагу до значення R-квадрата. Він є коефіцієнтом детермінації. У даному прикладі R-квадрат = 0,755 (75,5%), тобто розрахункові параметри моделі пояснюють залежність між параметрами, що розглядаються, на 75,5 %. Чим вище значення коефіцієнта детермінації, тим обрана модель вважається більш застосовною для конкретного завдання. Вважається, що вона коректно визначає реальну ситуацію за значення R-квадрату вище 0,8. Якщо R-квадрату tкр, гіпотеза про незначущість вільного члена лінійного рівняння відкидається.
У розглянутій задачі для вільного члена за допомогою інструментів «Ексель» було отримано, що t=169,20903, а p=2,89Е-12, тобто маємо нульову ймовірність того, що буде відкинута вірна гіпотеза про незначущість вільного члена. Для коефіцієнта за невідомої t=5,79405, а p=0,001158. Іншими словами ймовірність того, що буде відкинута вірна гіпотеза про незначущість коефіцієнта за невідомої, дорівнює 0,12%.
Отже, можна стверджувати, що отримане рівняння лінійної регресії адекватно.
Завдання про доцільність купівлі пакету акцій
Множинна регресія в Excel виконується з використанням того ж інструменту «Аналіз даних». Розглянемо конкретне прикладне завдання.
Керівництво компанія «NNN» має ухвалити рішення про доцільність купівлі 20% пакету акцій АТ «MMM». Вартість пакету (СП) складає 70 млн. американських доларів. Фахівцями NNN зібрані дані про аналогічні угоди. Було ухвалено рішення оцінювати вартість пакета акцій за такими параметрами, вираженими в мільйонах американських доларів, як:
- кредиторська заборгованість (VK);
- обсяг річного обороту (VO);
- дебіторська заборгованість (VD);
- вартість основних фондів (СОФ).
Крім того, використовується параметр заборгованості підприємства із зарплати (V3 П) у тисячах американських доларів.
Рішення засобами табличного процесора Excel
Насамперед, необхідно скласти таблицю вихідних даних. Вона має такий вигляд:
- викликають вікно "Аналіз даних";
- обирають розділ «Регресія»;
- у віконце «Вхідний інтервал Y» вводять діапазон значень залежних змінних зі стовпця G;
- клацають по іконці з червоною стрілкою праворуч від вікна «Вхідний інтервал X» і виділяють на аркуші діапазон всіх значень стовпців B,C, D, F.
Позначають пункт «Новий робочий лист» та натискають «Ok».
Отримують аналіз регресії для цього завдання.
Вивчення результатів та висновки
«Збираємо» із заокруглених даних, представлених вище на аркуші табличного процесора Excel, рівняння регресії:
СП = 0,103 * СОФ + 0,541 * VO - 0,031 * VK + 0,405 * VD +0,691 * VZP - 265,844.
У більш звичному математичному вигляді його можна записати як:
y = 0,103 * x1 + 0,541 * x2 - 0,031 * x3 +0,405 * x4 +0,691 * x5 - 265,844
Дані для АТ «MMM» представлені у таблиці:
Підставивши їх у рівняння регресії, одержують цифру в 64,72 млн американських доларів. Це означає, що акції АТ «MMM» не варто купувати, оскільки їхня вартість у 70 млн американських доларів досить завищена.
Як бачимо, використання табличного процесора «Ексель» та рівняння регресії дозволило ухвалити обґрунтоване рішення щодо доцільності цілком конкретної угоди.
Тепер ви знаєте, що таке регресія. Приклади в Excel, розглянуті вище, допоможуть вам вирішити практичних завданьз галузі економетрики.
Регресійний аналіз - це статистичний метод дослідження, що дозволяє показати залежність того чи іншого параметра від однієї чи кількох незалежних змінних. У докомп'ютерну епоху його застосування було досить складно, особливо якщо йшлося про великі обсяги даних. Сьогодні, дізнавшись, як побудувати регресію в Excel, можна вирішувати складні статистичні завдання буквально за пару хвилин. Нижче представлені конкретні приклади галузі економіки.
Види регресії
Саме це поняття було введено в математику у 1886 році. Регресія буває:
- лінійної;
- параболічній;
- статечною;
- експоненційною;
- гіперболічній;
- показовою;
- логарифмічні.
Приклад 1
Розглянемо завдання визначення залежності кількості членів колективу, що звільнилися, від середньої зарплати на 6 промислових підприємствах.
Завдання. На шести підприємствах проаналізували середньомісячну заробітну плату та кількість працівників, які звільнилися за власним бажанням. У табличній формі маємо:
Кількість звільнених | Зарплата |
||
30000 рублів |
|||
35000 рублів |
|||
40000 рублів |
|||
45000 рублів |
|||
50000 рублів |
|||
55000 рублів |
|||
60000 рублів |
Для завдання визначення залежності кількості працівників, що звільнилися, від середньої зарплати на 6 підприємствах модель регресії має вигляд рівняння Y = а 0 + а 1 x 1 +…+а k x k , де х i — що впливають змінні, a i — коефіцієнти регресії, a k — число факторів.
Для цього завдання Y — це показник співробітників, що звільнилися, а впливаючий фактор — зарплата, яку позначаємо X.
Використання можливостей табличного процесора «Ексель»
Аналізу регресії в Excel має передувати застосування наявних табличних даних вбудованих функцій. Однак для цього краще скористатися дуже корисною надбудовою «Пакет аналізу». Для його активації потрібно:
- з вкладки "Файл" перейти до розділу "Параметри";
- у вікні вибрати рядок «Надбудови»;
- клацнути на кнопці «Перейти», розташованої внизу, праворуч від рядка «Управління»;
- поставити галочку поруч із назвою «Пакет аналізу» та підтвердити свої дії, натиснувши «Ок».
Якщо все зроблено правильно, у правій частині вкладки "Дані", розташованому над робочим листом "Ексель", з'явиться потрібна кнопка.
в Excel
Тепер, коли під рукою є всі необхідні віртуальні інструменти для здійснення економетричних розрахунків, можемо розпочати вирішення нашого завдання. Для цього:
- клацаємо по кнопці «Аналіз даних»;
- у вікні натискаємо на кнопку «Регресія»;
- в вкладку, що з'явилася, вводимо діапазон значень для Y (кількість звільнених працівників) і для X (їх зарплати);
- підтверджуємо свої дії, натиснувши кнопку «Ok».
В результаті програма автоматично заповнить новий аркуш табличного процесора даними аналізу регресії. Зверніть увагу! В Excel є можливість самостійно задати місце, якому ви надаєте перевагу для цієї мети. Наприклад, це може бути той самий аркуш, де є значення Y і X, або навіть нова книга, спеціально призначена для зберігання подібних даних.
Аналіз результатів регресії для R-квадрату
В Excel дані отримані в ході обробки даних прикладу, що розглядається, мають вигляд:
Насамперед, слід звернути увагу до значення R-квадрата. Він є коефіцієнтом детермінації. У цьому прикладі R-квадрат = 0,755 (75,5%), тобто розрахункові параметри моделі пояснюють залежність між параметрами, що розглядаються, на 75,5 %. Чим вище значення коефіцієнта детермінації, тим вибрана модель вважається застосовнішою для конкретної задачі. Вважається, що вона коректно визначає реальну ситуацію за значення R-квадрату вище 0,8. Якщо R-квадрату<0,5, то такой анализа регрессии в Excel нельзя считать резонным.
Аналіз коефіцієнтів
Число 64,1428 показує, яким буде значення Y, якщо всі змінні xi в моделі, що розглядається, обнуляться. Іншими словами можна стверджувати, що на значення аналізованого параметра впливають інші фактори, не описані в конкретній моделі.
Наступний коефіцієнт -0,16285, розташований у осередку B18, показує вагомість впливу змінної Х на Y. Це означає, що середньомісячна зарплата співробітників у межах аналізованої моделі впливає кількість звільнених з вагою -0,16285, т. е. ступінь її впливу зовсім невелика. Знак «-» свідчить про те, що коефіцієнт має негативне значення. Це очевидно, оскільки всім відомо, що чим більша зарплата на підприємстві, тим менше людей висловлюють бажання розірвати трудовий договір чи звільняється.
Множинна регресія
Під таким терміном розуміється рівняння зв'язку з кількома незалежними змінними видами:
y=f(x 1 +x 2 +…x m) + ε, де y — це результативна ознака (залежна змінна), а x 1 , x 2 , …x m — ознаки-фактори (незалежні змінні).
Оцінка параметрів
Для множинної регресії(МР) її здійснюють, використовуючи метод найменших квадратів (МНК). Для лінійних рівнянь виду Y = a + b 1 x 1 +…+b m x m + ε будуємо систему нормальних рівнянь (див. нижче)
Щоб зрозуміти принцип методу, розглянемо двофакторний випадок. Тоді маємо ситуацію, що описується формулою
Звідси отримуємо:
де σ - це дисперсія відповідної ознаки, відображеної в індексі.
МНК застосуємо до рівняння МР в масштабі, що стандартизується. У такому разі отримуємо рівняння:
в якому t y , t x 1, ... t xm - Змінні, що стандартизуються, для яких середні значення рівні 0; β i – стандартизовані коефіцієнти регресії, а середньоквадратичне відхилення – 1.
Зверніть увагу, що всі β i в даному випадку задані як нормовані та централізовані, тому їх порівняння між собою вважається коректним та допустимим. Крім того, прийнято здійснювати відсівання факторів, відкидаючи ті з них, які мають найменші значення βi.
Завдання з використанням рівняння лінійної регресії
Припустимо, є таблиця динаміки ціни конкретного товару протягом останніх 8 місяців. Необхідно ухвалити рішення про доцільність придбання його партії за ціною 1850 руб./т.
номер місяця | назва місяця | ціна товару N |
|
1750 рублів за тонну |
|||
1755 рублів за тонну |
|||
1767 рублів за тонну |
|||
1760 рублів за тонну |
|||
1770 рублів за тонну |
|||
1790 рублів за тонну |
|||
1810 рублів за тонну |
|||
1840 рублів за тонну |
|||
Для вирішення цього завдання в табличному процесорі «Ексель» потрібно задіяти вже відомий за наведеним вище прикладом інструмент «Аналіз даних». Далі вибирають розділ «Регресія» та задають параметри. Потрібно пам'ятати, що у полі «Вхідний інтервал Y» має вводитися діапазон значень для залежної змінної (у разі ціни на товар у конкретні місяці року), а «Вхідний інтервал X» — для незалежної (номер місяця). Підтверджуємо дії натисканням OK. На новому аркуші (якщо було зазначено) отримуємо дані для регресії.
Будуємо за ними лінійне рівняння виду y=ax+b, де як параметри a і b виступають коефіцієнти рядка з найменуванням номера місяця та коефіцієнти та рядки «Y-перетин» з аркуша з результатами регресійного аналізу. Таким чином, лінійне рівняння регресії (УР) для задачі 3 записується у вигляді:
Ціна товару N = 11,714* номер місяця + 1727,54.
або в позначеннях алгебри
y = 11,714 x + 1727,54
Аналіз результатів
Щоб вирішити, чи адекватно отримане рівняння лінійної регресії, використовуються коефіцієнти множинної кореляції (КМК) та детермінації, а також критерій Фішера та критерій Стьюдента. У таблиці «Ексель» з результатами регресії вони виступають під назвами множинний R, R-квадрат, F-статистика та t-статистика відповідно.
КМК R дає можливість оцінити тісноту ймовірнісного зв'язку між незалежною та залежною змінними. Її високе значення свідчить про досить сильний зв'язок між змінними «Номер місяця» та «Ціна товару N у рублях за 1 тонну». Проте характер цього зв'язку залишається невідомим.
Квадрат коефіцієнта детермінації R 2 (RI) є числову характеристику частки загального розкиду і показує, розкид якої частини експериментальних даних, тобто. значень залежної змінної відповідає рівнянню лінійної регресії У даній задачі ця величина дорівнює 84,8%, тобто статистичні дані з високим ступенем точності описуються отриманим УР.
F-статистика, яка називається також критерієм Фішера, використовується для оцінки значущості лінійної залежності, спростовуючи чи підтверджуючи гіпотезу про її існування.
(Критерій Стьюдента) допомагає оцінювати значущість коефіцієнта при невідомій чи вільного члена лінійної залежності. Якщо значення t-критерію > t кр, гіпотеза про незначущість вільного члена лінійного рівняння відкидається.
У розглянутій задачі для вільного члена за допомогою інструментів «Ексель» було отримано, що t=169,20903, а p=2,89Е-12, тобто маємо нульову ймовірність того, що буде відкинута вірна гіпотеза про незначущість вільного члена. Для коефіцієнта за невідомої t=5,79405, а p=0,001158. Іншими словами ймовірність того, що буде відкинута вірна гіпотеза про незначущість коефіцієнта за невідомої, дорівнює 0,12%.
Отже, можна стверджувати, що отримане рівняння лінійної регресії адекватно.
Завдання про доцільність купівлі пакету акцій
Множинна регресія в Excel виконується з використанням того ж інструменту «Аналіз даних». Розглянемо конкретне прикладне завдання.
Керівництво компанія «NNN» має ухвалити рішення про доцільність купівлі 20% пакету акцій АТ «MMM». Вартість пакету (СП) складає 70 млн. американських доларів. Фахівцями NNN зібрані дані про аналогічні угоди. Було ухвалено рішення оцінювати вартість пакета акцій за такими параметрами, вираженими в мільйонах американських доларів, як:
- кредиторська заборгованість (VK);
- обсяг річного обороту (VO);
- дебіторська заборгованість (VD);
- вартість основних фондів (СОФ).
Крім того, використовується параметр заборгованості підприємства із зарплати (V3 П) у тисячах американських доларів.
Рішення засобами табличного процесора Excel
Насамперед, необхідно скласти таблицю вихідних даних. Вона має такий вигляд:
- викликають вікно "Аналіз даних";
- обирають розділ «Регресія»;
- у віконце «Вхідний інтервал Y» вводять діапазон значень залежних змінних зі стовпця G;
- клацають по іконці з червоною стрілкою праворуч від вікна "Вхідний інтервал X" і виділяють на аркуші діапазон всіх значень зі стовпців B, C, D, F.
Позначають пункт «Новий робочий лист» та натискають «Ok».
Отримують аналіз регресії для цього завдання.
Вивчення результатів та висновки
«Збираємо» із заокруглених даних, представлених вище на аркуші табличного процесора Excel, рівняння регресії:
СП = 0,103 * СОФ + 0,541 * VO - 0,031 * VK + 0,405 * VD +0,691 * VZP - 265,844.
У більш звичному математичному вигляді його можна записати як:
y = 0,103 * x1 + 0,541 * x2 - 0,031 * x3 +0,405 * x4 +0,691 * x5 - 265,844
Дані для АТ «MMM» представлені у таблиці:
Підставивши їх у рівняння регресії, одержують цифру в 64,72 млн американських доларів. Це означає, що акції АТ «MMM» не варто купувати, оскільки їхня вартість у 70 млн американських доларів досить завищена.
Як бачимо, використання табличного процесора «Ексель» та рівняння регресії дозволило ухвалити обґрунтоване рішення щодо доцільності цілком конкретної угоди.
Тепер ви знаєте, що таке регресія. Приклади в Excel, розглянуті вище, допоможуть вам вирішити практичні завдання з галузі економетрики.
Пакет MS Excel дозволяє при побудові рівняння лінійної регресії більшу частину роботи зробити дуже швидко. Важливо зрозуміти, як інтерпретувати отримані результати.
Для роботи необхідна надбудова Пакет аналізу, яку необхідно увімкнути в пункті меню Сервіс\Надбудови
В Excel 2007 для включення пакета аналізу треба натиснути на блок. Параметри Excel , натиснувши кнопку в лівому верхньому кутку, а потім кнопку « Параметри Excel» внизу вікна:
Для побудови моделі регресії необхідно вибрати пункт Сервіс\Аналіз даних\Регресія. (В Excel 2007 цей режим знаходиться в блоці Дані/Аналіз даних/Регресія). З'явиться діалогове вікно, яке потрібно заповнити:
1) Вхідний інтервал Y¾ містить посилання на комірки, які містять значення результативної ознаки y. Значення мають бути розташовані у стовпці;
2) Вхідний інтервал X¾ містить посилання на комірки, які містять значення факторів. Значення мають бути розташовані в стовпцях;
3) Ознака Міткиставиться, якщо перші осередки містять пояснювальний текст(Підписи даних);
4) Рівень надійності¾ це довірча ймовірність, яка за умовчанням вважається рівною 95%. Якщо це значення не влаштовує, потрібно включити цю ознаку і ввести необхідне значення;
5) Ознака Константа-нульвключається, якщо необхідно побудувати рівняння, у якому вільна змінна;
6) Параметри виводувизначають, куди мають бути поміщені результати. За промовчанням будує режим Новий робочий лист;
7) Блок Залишкидозволяє включати виведення залишків та побудову їх графіків.
В результаті виводиться інформація, що містить усі необхідні відомостіі згрупована в три блоки: Регресійна статистика, Дисперсійний аналіз, Виведення залишку. Розглянемо їх докладніше.
1. Регресійна статистика:
множинний Rвизначається формулою ( коефіцієнт кореляції Пірсона);
R 
(коефіцієнт детермінації);
Нормований R-квадрат обчислюється за формулою 
(Використовується для множинної регресії);
Стандартна помилка Sобчислюється за формулою 
;
Спостереження - це кількість даних n.
2. Дисперсійний аналіз, рядок Регресія:
Параметр dfдорівнює m(кількість наборів факторів x);
Параметр SSвизначається формулою;
Параметр MSвизначається формулою;
Статистика Fвизначається формулою;
Значимість F. Якщо отримане число перевищує, то приймається гіпотеза (немає лінійного взаємозв'язку), інакше приймається гіпотеза (є лінійний взаємозв'язок).
3. Дисперсійний аналіз, рядок Залишок:
Параметр dfдорівнює;
Параметр SSвизначається формулою 
;
Параметр MSвизначається формулою.
4. Дисперсійний аналіз, рядок Разоммістить суму перших двох стовпців.
5. Дисперсійний аналіз, рядок Y-перетинмістить значення коефіцієнта , стандартної помилки та t-статистики.
P-значення ¾ це значення рівнів значущості, що відповідає обчисленим t-Статистикам. Визначається функцією Стюдрасп ( t-Статистика; ). Якщо P-Значення перевищує , то відповідна змінна статистично незначна і її можна виключити з моделі.
Нижні 95%і Верхні 95%¾ це нижні та верхні межі 95-відсоткових довірчих інтервалів для коефіцієнтів теоретичного рівняння лінійної регресії. Якщо в блоці введення даних значення довірчої ймовірності було залишено за замовчуванням, останні два стовпці будуть дублювати попередні. Якщо користувач ввів своє значення довірчої ймовірності, останні два стовпці містять значення нижньої і верхньої межі для зазначеної довірчої ймовірності.
6. Дисперсійний аналізрядки містять значення коефіцієнтів, стандартних помилок, t-статистик, P-Значень та довірчих інтервалів для відповідних .
7. Блок Виведення залишкумістить значення передбаченого y(У наших позначеннях це) і залишки.
Зміна результативної ознаки y обумовлена варіацією факторної ознаки x. Частку дисперсії, що пояснюється регресією, у спільній дисперсії результативної ознаки характеризує коефіцієнт детермінації R 2. Для лінійної залежності коефіцієнт детермінації дорівнює квадрату коефіцієнта кореляції:R 2 = r xy 2 де r xy - коефіцієнт кореляції.
Наприклад, значення R 2 = 0.83 означає, що в 83% випадків зміни х призводять до зміни y . Іншими словами, точність підбору рівняння регресії – висока.
Розраховується з метою оцінки якості підбору рівняння регресії. Для прийнятних моделей передбачається, що коефіцієнт детермінації має бути більшим за 50%. Моделі з коефіцієнтом детермінації вище 80% можна визнати досить добрими. Значення коефіцієнта детермінації R 2 = означає 1 функціональну залежністьміж змінними.
В разі нелінійної регресіїКоефіцієнт детермінації розраховується через цей калькулятор. При множинні регресії, коефіцієнт детемрмінації можна знайти через сервіс Множинна регресія
У загальному випадку коефіцієнт детермінації знаходиться за формулою: або
Правило складання дисперсій:
,
де – загальна сума квадратів відхилень;
- сума квадратів відхилень, обумовлена регресією («пояснена» чи «факторна»);
- Залишкова сума квадратів відхилень.
За допомогою цього онлайн-калькулятора розраховується коефіцієнт детермінаціїта перевіряється його значимість (Приклад рішення).
Інструкція. Вкажіть кількість вихідних даних. Отримане рішення зберігається в файлі Word. Також автоматично створюється шаблон для перевірки рішення в Excel.
Пакет MS Excel дозволяє при побудові рівняння лінійної регресії більшу частину роботи зробити дуже швидко. Важливо зрозуміти, як інтерпретувати отримані результати. Для побудови моделі регресії необхідно вибрати пункт Сервіс\Аналіз даних\Регресія (в Excel 2007 цей режим знаходиться в блоці Дані/Аналіз даних/Регресія). Потім отримані результати скопіювати блок для аналізу.
