Як виконати квадратичну регресію в excel

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

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

Однак інколи зв’язок між змінною-прогнозом і змінною відповіді є нелінійним . Поширеним типом нелінійного співвідношення є квадратичне співвідношення , яке може виглядати як U або перевернуте U на графіку.

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

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

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

Квадратична регресія в Excel

Припустімо, що у нас є дані про кількість відпрацьованих годин на тиждень і рівень щастя (за шкалою від 0 до 100) для 16 різних людей:

По-перше, давайте створимо діаграму розсіювання, щоб побачити, чи лінійна регресія є відповідною моделлю для підгонки даних.

Виділіть клітинки A2:B17 . Потім клацніть вкладку «ВСТАВИТИ» на верхній стрічці, а потім клацніть «Точкова» в області «Діаграми» . Це створить діаграму розсіювання даних:

Легко побачити, що зв’язок між відпрацьованими годинами та заявленим щастям не є лінійним. Насправді він має форму «U», що робить його ідеальним кандидатом для квадратичної регресії .

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

Спочатку виділіть усі значення в стовпці B і перетягніть їх у стовпець C.

Далі введіть формулу =A2^2 у клітинку B2. Це створює значення 36 . Потім клацніть нижній правий кут клітинки B2 і перетягніть формулу вниз, щоб заповнити решту клітинок у стовпці B.

Далі ми підберемо модель квадратичної регресії.

Клацніть ДАНІ на верхній стрічці, а потім клацніть опцію Аналіз даних праворуч. Якщо ви не бачите цей параметр, спершу потрібно інсталювати безкоштовне програмне забезпечення Analysis ToolPak .

Після того, як ви натиснете «Аналіз даних» , з’явиться вікно. Клацніть Регресія , а потім натисніть OK .

Далі заповніть наступні значення в полі регресії , яке з’явиться. Потім натисніть OK .

Будуть відображені такі результати:

Ось як інтерпретувати різні числа у виводі:

R Square: також відомий як коефіцієнт детермінації, це частка дисперсії у змінній відповіді, яку можна пояснити змінними предиктора. У цьому прикладі R-квадрат дорівнює 0,9092 , що вказує на те, що 90,92% дисперсії заявлених рівнів щастя можна пояснити кількістю відпрацьованих годин і кількістю відпрацьованих годин ^2.

Стандартна помилка: Стандартна помилка регресії – це середня відстань між спостережуваними значеннями та лінією регресії. У цьому прикладі спостережувані значення відхиляються в середньому на 9519 одиниць від лінії регресії.

F-статистика : F-статистика обчислюється як регресія MS/залишкова MS. Ця статистика показує, чи регресійна модель забезпечує кращу відповідність даним, ніж модель, яка не містить незалежних змінних. По суті, він перевіряє, чи є регресійна модель у цілому корисною. Як правило, якщо жодна зі змінних предиктора в моделі не є статистично значущою, загальна статистика F також не є статистично значущою. У цьому прикладі F-статистика становить 65,09 , а відповідне значення p <0,0001. Оскільки це p-значення менше 0,05, регресійна модель в цілому є значущою.

Коефіцієнти регресії: Коефіцієнти регресії в останній таблиці дають нам числа, необхідні для написання розрахункового рівняння регресії:

y _hat = b ₀ + b ₁ x ₁ + b ₂ x ₁ ²

У цьому прикладі розраховане рівняння регресії таке:

декларований рівень щастя = -30,252 + 7,173 (Відпрацьовані години) -0,106 (Відпрацьовані години) ²

Ми можемо використовувати це рівняння, щоб обчислити очікуваний рівень щастя людини на основі відпрацьованих годин. Наприклад, очікуваний рівень щастя людини, яка працює 30 годин на тиждень:

зареєстрований рівень щастя = -30,252 + 7,173(30) -0,106(30) ² = 88,649 .

Додаткові ресурси

Як додати квадратичну лінію тренду в Excel
Як читати та інтерпретувати таблицю регресії
Що таке хороше значення R-квадрат?
Розуміння стандартної помилки регресії
Простий посібник із розуміння F-тесту загальної значущості в регресії