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

Зміст

• Підключення пакета аналізу
• Види регресійного аналізу
• Лінійна регресія в програмі 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. у нашому випадку &8212; це рівень залежності кількості клієнтів магазину від температури. Коефіцієнт 1,31 вважається досить високим показником впливу.

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