Регресійний аналіз у Microsoft Excel виконують надбудовою «Пакет аналізу» (Analysis ToolPak): увімкніть її в «Файл → Параметри → Надбудови», потім на вкладці «Дані» натисніть «Аналіз даних» → «Регресія», вкажіть вхідні інтервали Y і X — Excel виведе таблицю з R-квадратом і коефіцієнтами рівняння.
Регресійний аналіз — один із найпоширеніших методів статистичного дослідження. Він показує, наскільки незалежні величини впливають на залежну змінну. В Excel для цього є спеціальні інструменти — розберімо, що вони собою являють і як ними користуватися.
Підключення пакета аналізу
Щоб скористатися функцією регресійного аналізу, спершу треба увімкнути «Пакет аналізу». Лише тоді потрібні інструменти з'являться на стрічці Excel.
- Перейдіть на вкладку «Файл» (File).
- Відкрийте розділ «Параметри» (Options).
- У вікні параметрів Excel перейдіть до підрозділу «Надбудови» (Add-ins).
- У нижній частині вікна в полі «Керування» (Manage) виберіть «Надбудови Excel» (Excel Add-ins), якщо там стоїть інше значення, і натисніть «Перейти» (Go).
- У вікні доступних надбудов поставте прапорець біля «Пакет аналізу» і натисніть OK.
Тепер на вкладці «Дані» (Data) у групі «Аналіз» (Analysis) з'явиться кнопка «Аналіз даних» (Data Analysis).
Види регресійного аналізу
Існує кілька видів регресії:
- параболічна;
- степенева;
- логарифмічна;
- експоненціальна;
- показникова;
- гіперболічна;
- лінійна.
Далі детальніше розглянемо, як виконати в Excel лінійну регресію.
Лінійна регресія в Excel
Для прикладу візьмемо таблицю із середньодобовою температурою повітря на вулиці і кількістю покупців магазину в кожен робочий день. З'ясуємо за допомогою регресійного аналізу, як температура повітря впливає на відвідуваність магазину.
Загальне рівняння лінійної регресії має вигляд Y = a0 + a1x1 + … + akxk. Тут Y — змінна, вплив факторів на яку ми досліджуємо (у нашому прикладі — кількість покупців); x — фактори, що впливають на змінну; a — коефіцієнти регресії, які й визначають вагу кожного фактора; k — загальна кількість факторів.
- Натисніть кнопку «Аналіз даних» на вкладці «Дані» в групі «Аналіз».
- У невеликому вікні виберіть «Регресія» (Regression) і натисніть OK.
- Відкриється вікно налаштувань регресії. Обов'язкові поля — «Вхідний інтервал Y» (Input Y Range) і «Вхідний інтервал X» (Input X Range); решту параметрів можна лишити за замовчуванням.
- У полі «Вхідний інтервал Y» вкажіть адресу діапазону клітинок зі змінною, вплив факторів на яку досліджуєте, — у прикладі це стовпець «Кількість покупців». Адресу можна ввести з клавіатури, але простіше виділити потрібний стовпець мишею.
- У полі «Вхідний інтервал X» вкажіть діапазон клітинок фактора, вплив якого хочете встановити, — у прикладі стовпець «Температура». Це робиться так само.
- Інші налаштування дають змогу задати мітки, рівень надійності, нульову константу, показати графік нормальної ймовірності тощо, але здебільшого змінювати їх не потрібно. Варто звернути увагу хіба що на параметри виведення: за замовчуванням результати виводяться на новий аркуш, але можна вибрати вказаний діапазон на тому самому аркуші, де лежить таблиця з даними, або окрему книгу, тобто новий файл.
- Задавши всі параметри, натисніть OK.
Розбір результатів аналізу
Результати регресійного аналізу виводяться таблицею в місці, вказаному в налаштуваннях.
Один з основних показників — R-квадрат: він характеризує якість моделі. У прикладі цей коефіцієнт дорівнює 0,705, тобто приблизно 70,5 %, — це прийнятний рівень. Значення, менші за 0,5, зазвичай свідчать про слабку модель.
Ще один важливий показник — у клітинці на перетині рядка «Y-перетин» (Intercept) і стовпця «Коефіцієнти» (Coefficients). Він показує, яким буде Y (у прикладі — кількість покупців), якщо всі інші фактори дорівнюють нулю. У прикладі це 58,04.
Значення на перетині рядка «Змінна X 1» (X Variable 1) і стовпця «Коефіцієнти» показує, наскільки Y залежить від X, — у прикладі це залежність кількості клієнтів від температури. Коефіцієнт 1,31 свідчить про досить сильний вплив.
Як бачимо, скласти таблицю регресійного аналізу в Excel досить просто. А от правильно інтерпретувати отримані результати зможе лише людина, що розуміється на статистиці.