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

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

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

Але, для того, щоб використовувати функцію, що дозволяє провести регресійний аналіз, перш за все, потрібно активувати Пакет аналізу. Тільки тоді необхідні для цієї процедури інструменти з'являться на стрічці Ексель.

1. Переміщаємося у вкладку «Файл».
2. Переходимо в розділ «Параметри».
3. Відкривається вікно параметрів Excel. Переходимо до підрозділу «Надбудови».
4. У самій нижній частині вікна переставляємо перемикач в блоці «Управління» в позицію «Надбудови Excel», якщо він знаходиться в іншому положенні. Тиснемо на кнопку «Перейти».
5. Відкривається вікно доступних надбудов Ексель. Ставимо галочку біля пункту «Пакет аналізу». Тиснемо на кнопку «OK».

Тепер, коли ми перейдемо у вкладку «Дані», на стрічці в блоці інструментів «Аналіз» ми побачимо нову кнопку - «Аналіз даних».

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

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

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

Про виконання останнього виду регресійного аналізу в Ексель ми докладніше поговоримо далі.

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

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

Загальне рівняння регресії лінійного виду виглядає наступним чином: У = а0 + а1х1 +…+акхк . У цій формулі Y означає змінну, вплив чинників на яку ми намагаємося вивчити. У нашому випадку, це кількість покупців. Значення x - це різні фактори, що впливають на змінну. Параметри a є коефіцієнтами регресії. Тобто, саме вони визначають значимість того чи іншого фактора. Індекс k позначає загальну кількість цих самих чинників.

1. Кількома по кнопці «Аналіз даних». Вона розміщена у вкладці «Головна» в блоці інструментів «Аналіз».
2. Відкривається невелике віконце. У ньому вибираємо пункт «Регресія». Тиснемо на кнопку «OK».
3. Відкривається вікно налаштувань регресії. У ньому обов'язковими для заповнення полями є «Вхідний інтервал Y» і «Вхідний інтервал X». Всі інші настройки можна залишити за замовчуванням.

   В поле «Вхідний інтервал Y» вказуємо адресу діапазону комірок, де розташовані змінні дані, вплив чинників на які ми намагаємося встановити. У нашому випадку це будуть комірок стовпчика «Кількість покупців». Адреса можна вписати вручну з клавіатури, а можна, просто виділити необхідний стовпець. Останній варіант набагато простіше і зручніше.

   В поле «Вхідний інтервал X» вводимо адресу діапазону комірок, де знаходяться дані того фактора, вплив якого на змінну ми хочемо встановити. Як говорилося вище, нам потрібно встановити вплив температури на кількість покупців магазину, а тому вводимо адресу осередків в стовпці «Температура». Це можна зробити тими ж способами, що і в поле «Кількість покупців».

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

   Після того, як всі налаштування встановлені, тиснемо на кнопку «OK».

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

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

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

Ще один важливий показник розташований в осередку на перетині рядка «Y-перетин» і шпальти «Коефіцієнти». Тут вказується яке значення буде у Y, а в нашому випадку, це кількість покупців, при всіх інших факторах рівних нулю. У цій таблиці дане значення дорівнює 58,04.

Значення на перетині граф «Мінлива X1» і «Коефіцієнти» показує рівень залежності Y від X. В нашому випадку - це рівень залежності кількості клієнтів магазину від температури. Коефіцієнт 1,31 вважається досить високим показником впливу.

Як бачимо, за допомогою програми Microsoft Excel досить просто скласти таблицю регресійного аналізу. Але, працювати з отриманими на виході даними, і розуміти їх суть, зможе тільки підготовлена ​​людина.

• ← Консолідація даних в програмі Microsoft Excel
• Створення гістограми в Microsoft Excel →