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

Як зробити регресійний аналіз у Microsoft Excel: увімкнути «Пакет аналізу», запустити лінійну регресію з інтервалами Y і X і прочитати R-квадрат та коефіцієнти.

Регресійний аналіз у Microsoft Excel виконують надбудовою «Пакет аналізу» (Analysis ToolPak): увімкніть її в «Файл → Параметри → Надбудови», потім на вкладці «Дані» натисніть «Аналіз даних» → «Регресія», вкажіть вхідні інтервали Y і X — Excel виведе таблицю з R-квадратом і коефіцієнтами рівняння.

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

Регресійний аналіз — один із найпоширеніших методів статистичного дослідження. Він показує, наскільки незалежні величини впливають на залежну змінну. В Excel для цього є спеціальні інструменти — розберімо, що вони собою являють і як ними користуватися.

Підключення пакета аналізу

Щоб скористатися функцією регресійного аналізу, спершу треба увімкнути «Пакет аналізу». Лише тоді потрібні інструменти з'являться на стрічці Excel.

  1. Перейдіть на вкладку «Файл» (File).
  2. Відкрийте розділ «Параметри» (Options).
  3. У вікні параметрів Excel перейдіть до підрозділу «Надбудови» (Add-ins).
  4. У нижній частині вікна в полі «Керування» (Manage) виберіть «Надбудови Excel» (Excel Add-ins), якщо там стоїть інше значення, і натисніть «Перейти» (Go).
  5. У вікні доступних надбудов поставте прапорець біля «Пакет аналізу» і натисніть OK.

Тепер на вкладці «Дані» (Data) у групі «Аналіз» (Analysis) з'явиться кнопка «Аналіз даних» (Data Analysis).

Види регресійного аналізу

Існує кілька видів регресії:

  • параболічна;
  • степенева;
  • логарифмічна;
  • експоненціальна;
  • показникова;
  • гіперболічна;
  • лінійна.

Далі детальніше розглянемо, як виконати в Excel лінійну регресію.

Лінійна регресія в Excel

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

Загальне рівняння лінійної регресії має вигляд Y = a0 + a1x1 + … + akxk. Тут Y — змінна, вплив факторів на яку ми досліджуємо (у нашому прикладі — кількість покупців); x — фактори, що впливають на змінну; a — коефіцієнти регресії, які й визначають вагу кожного фактора; k — загальна кількість факторів.

  1. Натисніть кнопку «Аналіз даних» на вкладці «Дані» в групі «Аналіз».
  2. У невеликому вікні виберіть «Регресія» (Regression) і натисніть OK.
  3. Відкриється вікно налаштувань регресії. Обов'язкові поля — «Вхідний інтервал Y» (Input Y Range) і «Вхідний інтервал X» (Input X Range); решту параметрів можна лишити за замовчуванням.
  4. У полі «Вхідний інтервал Y» вкажіть адресу діапазону клітинок зі змінною, вплив факторів на яку досліджуєте, — у прикладі це стовпець «Кількість покупців». Адресу можна ввести з клавіатури, але простіше виділити потрібний стовпець мишею.
  5. У полі «Вхідний інтервал X» вкажіть діапазон клітинок фактора, вплив якого хочете встановити, — у прикладі стовпець «Температура». Це робиться так само.
  6. Інші налаштування дають змогу задати мітки, рівень надійності, нульову константу, показати графік нормальної ймовірності тощо, але здебільшого змінювати їх не потрібно. Варто звернути увагу хіба що на параметри виведення: за замовчуванням результати виводяться на новий аркуш, але можна вибрати вказаний діапазон на тому самому аркуші, де лежить таблиця з даними, або окрему книгу, тобто новий файл.
  7. Задавши всі параметри, натисніть OK.

Розбір результатів аналізу

Результати регресійного аналізу виводяться таблицею в місці, вказаному в налаштуваннях.

Один з основних показників — R-квадрат: він характеризує якість моделі. У прикладі цей коефіцієнт дорівнює 0,705, тобто приблизно 70,5 %, — це прийнятний рівень. Значення, менші за 0,5, зазвичай свідчать про слабку модель.

Ще один важливий показник — у клітинці на перетині рядка «Y-перетин» (Intercept) і стовпця «Коефіцієнти» (Coefficients). Він показує, яким буде Y (у прикладі — кількість покупців), якщо всі інші фактори дорівнюють нулю. У прикладі це 58,04.

Значення на перетині рядка «Змінна X 1» (X Variable 1) і стовпця «Коефіцієнти» показує, наскільки Y залежить від X, — у прикладі це залежність кількості клієнтів від температури. Коефіцієнт 1,31 свідчить про досить сильний вплив.

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

Схожі інструкції

Описова статистика в Excel

Як отримати описову статистику в Excel: увімкнення надбудови «Пакет аналізу», налаштування вхідного й вихідного інтервалу та які показники р...

Як користуватися Excel оновлено