Аналіз регресії Excel (Зміст)

  • Регресійний аналіз в Excel
  • Пояснення регресії математично
  • Як виконати лінійну регресію в Excel?
    • №1 - Інструмент регресії за допомогою аналізу ToolPak в Excel
    • №2 - Регресійний аналіз за допомогою Scatterplot з Trendline в Excel

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

Лінійна регресія - це статистична методика, яка вивчає лінійний зв’язок між залежною змінною та однією або декількома незалежними змінними.

  • Залежна змінна (називається змінною відповіді / результату): є змінною вашої зацікавленості, яку ви хотіли передбачити, виходячи з інформації, доступної незалежної змінної.
  • Незалежна змінна ( відома також пояснювальна / предикторна змінна): є / є змінною (ими), від якої залежить змінна відповіді. Що означає, що це змінні, за допомогою яких змінну відповіді можна передбачити.

Лінійний взаємозв'язок означає, що зміна незалежної змінної викликає зміну залежної змінної.

В основному є також два типи лінійних відносин.

  1. Позитивне лінійне відношення: Коли незалежна змінна збільшується, збільшується і залежна змінна.
  2. Негативна лінійна залежність : Коли незалежна змінна збільшується, залежна змінна зменшується.

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

Є два основні способи виконання лінійної регресії в excel, використовуючи:

  • Інструмент регресії через Analysis ToolPak
  • Діаграма з розкидом

Насправді існує ще один метод, який використовує ручні формули для обчислення лінійної регресії. Але навіщо вам це робити, коли excel робить розрахунки для вас?

Тому ми будемо говорити лише про два вище обговорені методи.

Припустимо, у вас є дані про зріст і вагу 10 особин. Якщо ви розміщуєте цю інформацію за допомогою діаграми, давайте подивимося, що вона дає.

Як показано на наведеному вище скріншоті, лінійну залежність можна знайти у графі "Зріст" та "Вага". Зараз не сильно залучайте до графіка, ми все одно будемо копати його глибоко у другій частині цієї статті.

Пояснення регресії математично

Математичний вираз для лінійної регресії наведено нижче:

Y = aX + b + ε

Де,

  • Y - залежна змінна чи змінна відповідь.
  • X - незалежна змінна чи предиктор.
  • a - нахил лінії регресії. Що означає, що коли X змінюється, відбувається зміна Y одиницями "a".
  • b - перехоплення. Це значення Y приймає, коли значення X дорівнює нулю.
  • ε - термін випадкової помилки. Виникає через те, що передбачуване значення Y ніколи не буде точно збігатися з фактичним значенням для даного X. Цей термін помилки нам не потрібно турбуватися. Оскільки є деякі програми, які роблять обчислення цього терміна помилки в бекенде для вас. Excel є одним із таких програм.

У такому випадку рівняння стає,

Y = aX + b

Які можна представити як:

Вага = a * Висота + b

Ми спробуємо з'ясувати значення цих a і b методами, про які ми говорили вище.

Як виконати лінійну регресію в Excel?

Подальша стаття пояснює основи регресійного аналізу в excel та показує кілька різних способів зробити лінійну регресію в Excel.

Ви можете завантажити цей шаблон Excel з регресійним аналізом тут - Шаблон Excel з регресійним аналізом

№1 - Інструмент регресії за допомогою аналізу ToolPak в Excel

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

  • У таблиці Excel натисніть на Аналіз даних (присутній у групі Аналіз ) у розділі Дані.

  • Знайдіть регресію . Виберіть його та натисніть ОК.

  • Використовуйте наступні входи в області регресії, яка відкривається.

  • Діапазон введення Y : виберіть комірки, які містять вашу залежну змінну (у цьому прикладі B1: B11)

  • Діапазон введення X : Виберіть комірки, які містять вашу незалежну змінну (у цьому прикладі A1: A11).

  • Поставте прапорець " Мітки", якщо у ваших даних є назви стовпців (у цьому прикладі є назви стовпців).

  • За замовчуванням рівень довіри встановлений на 95%, що може бути змінено відповідно до вимог користувачів.

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

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

  • У розділі " Нормальна ймовірність " ви можете вибрати графіки звичайної ймовірності, які допоможуть перевірити нормальність прогнозів. Натисніть кнопку ОК .

  • Excel обчислить для вас регресійний аналіз за частину секунд.

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

Однією з важливих частин усього цього виходу є R квадрат / скорегована R площа під таблицею ПІДСУМКИ. Що дає інформацію, наскільки хороша наша модель. У цьому випадку значення R квадрата дорівнює 0, 9547. Що тлумачить, що модель має точність на 95, 47% (добре підходить). Або іншою мовою, інформація про змінну Y пояснюється на 95, 47% змінною X.

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

Тепер наше рівняння регресії для прогнозування стає:

Вага = 0, 6746 * Висота - 38, 45508 (значення нахилу для висоти 0, 6746…, а перехоплення становить –38, 45508…)

Ви отримали те, що визначили? Ви визначили функцію, в якій тепер просто потрібно поставити значення Висота, і ви отримаєте значення Ваги.

№2 - Регресійний аналіз за допомогою Scatterplot з Trendline в Excel

Тепер ми побачимо, як у excel ми можемо вмістити рівняння регресії на самому розсіювачі.

  • Виберіть усі два стовпчикові дані (включаючи заголовки).
  • Клацніть на Вставити та виберіть Розсіювати графік під розділом графіків, як показано на зображенні нижче.

  • Дивіться вихідний графік.

  • Тепер на цьому графіку нам потрібно мати мінімум квадратну регресійну лінію. Щоб додати цей рядок, клацніть правою кнопкою миші на будь-якій із точок даних на графіку та виберіть опцію Додати тенденцію лінії.

  • Це дозволить вам мати лінію тренду найменшого квадрата регресії, як показано нижче.

  • Під опцією Формат тренд -рядка встановіть прапорець Показник рівняння на діаграмі.

  • Це дозволяє побачити рівняння найменш квадратичної регресійної лінії на графіку.

Це рівняння, за допомогою якого ми можемо передбачити значення ваги для будь-якого заданого набору значень висоти.

Що слід пам’ятати про регресійний аналіз в Excel

  • Ви можете змінити макет лінії тренду в опції Формат трендлінії в діаграмі розкидання.
  • Завжди рекомендується дивитися на залишкові ділянки, коли ви робите аналіз регресії за допомогою Data Analysis ToolPak в Excel. Це дозволяє краще зрозуміти поширення фактичних значень Y та оцінених значень X.
  • Проста лінійна регресія в excel не потребує ANOVA та регульованої площі R для перевірки. Ці особливості можна врахувати для множинної лінійної регресії. Що виходить за межі цієї статті.

Рекомендовані статті

Це був посібник з регресійного аналізу в Excel. Тут ми обговорюємо, як зробити регресійний аналіз в Excel разом із прикладами excel та шаблоном Excel, який можна завантажити. Ви також можете ознайомитися з іншими запропонованими нами статтями -

  1. Інструмент Excel для аналізу даних
  2. Обчисліть ANOVA в Excel
  3. Як знайти рухомі середні значення Excel
  4. Z TEST Приклади в Excel

Категорія:

Поради Excel