Показує вплив одних значень (самостійних, незалежних) на залежну змінну. Наприклад, як залежить кількість економічно активного населення кількості підприємств, величини зарплати та інших. властивостей. Або як впливають іноземні інвестиції, ціни на енергоресурси та ін на рівень ВВП.
Результат аналізу дає змогу виділяти пріоритети. І ґрунтуючись на головних факторах, прогнозувати, планувати розвиток пріоритетних напрямків, ухвалювати управлінські рішення.
Регресія буває:
· Лінійної (у = а + bx);
· Параболічній (y = a + bx + cx 2);
· Експонентної (y = a * exp (bx));
· статечної (y = a * x ^ b);
· Гіперболічної (y = b / x + a);
· логарифмічної (y = b * 1n (x) + a);
· Показовою (y = a * b^x).
Розглянемо з прикладу побудова регресійної моделі в Excel і інтерпретацію результатів. Візьмемо лінійний тип регресії.
Завдання. На 6 підприємствах була проаналізована середньомісячна заробітна плата і кількість співробітників, що звільнилися. Необхідно визначити залежність кількості співробітників, що звільнилися, від середньої зарплати.
Модель лінійної регресіїмає наступний вигляд:
У = а 0 + а 1 х 1 + ... + а до х к.
Де а – коефіцієнти регресії, х – що впливають змінні, до – число чинників.
У нашому прикладі як У виступає показник працівників, що звільнилися. фактор, що впливає - заробітна плата (х).
У Excel існують інтегровані функції, з допомогою яких можна розрахувати параметри моделі лінійної регресії. Але найшвидше це зробить надбудова «Пакет аналізу».
Активуємо потужний аналітичний інструмент:
1. Натискаємо кнопку «Офіс» та переходимо на вкладку «Параметри Excel». "Надбудови".
2. Внизу, під списком, у полі «Управління» буде напис « Надбудови Excel» (якщо її немає, натисніть прапорець праворуч і виберіть). І кнопка "Перейти". Тиснемо.
3. Відкривається список доступних надбудов. Вибираємо «Пакет аналізу» та натискаємо ОК.
Після активації надбудова буде доступна на вкладці "Дані".
Тепер візьмемося безпосередньо регресійним аналізом.
1. Відкриваємо меню інструмента «Аналіз даних». Вибираємо "Регресія".
2. Відкриється меню для вибору вхідних значень та параметрів виводу (де відобразити результат). У полях для вихідних даних вказуємо діапазон описуваного параметра (У) і фактора (Х), що впливає на нього. Решту можна не заповнювати.
3. Після натискання ОК програма відобразить розрахунки на новому аркуші (можна вибрати інтервал для відображення на поточному аркуші або призначити виведення в нову книгу).
Насамперед звертаємо увагу на R-квадрат та коефіцієнти.
R-квадрат – коефіцієнт детермінації. У прикладі – 0,755, чи 75,5%. Це означає, що розрахункові параметри моделі на 75,5% пояснюють залежність між параметрами, що вивчаються. Чим вищий коефіцієнт детермінації, тим якісніша модель. Добре – понад 0,8. Погано – менше 0,5 (такий аналіз навряд можна вважати резонним). У нашому прикладі - "непогано".
Коефіцієнт 64,1428 показує, яким буде Y, якщо всі змінні в моделі, що розглядається, будуть рівні 0. Тобто на значення аналізованого параметра впливають і інші фактори, не описані в моделі.
p align="justify"> Коефіцієнт -0,16285 показує вагомість змінної Х на Y. Тобто середньомісячна заробітна плата в межах даної моделі впливає на кількість звільнених з вагою -0,16285 (це невеликий ступінь впливу). Знак «-» вказує на негативний вплив: чим більша зарплата, тим менше звільнених Що слушно.
Метод лінійної регресії дозволяє нам описувати пряму лінію, що максимально відповідає ряду впорядкованих пар (x, y). Рівняння для прямої лінії, відоме як лінійне рівняння, наведено нижче:
ŷ — очікуване значення у при заданому значенніх,
x - незалежна змінна,
a - відрізок на осі y для прямої лінії,
b - нахил прямої лінії.
На малюнку нижче це поняття представлено графічно:
На малюнку вище показана лінія, описана рівнянням = 2+0.5х. Відрізок на осі у - це точка перетину лінією осі у; у разі а = 2. Нахил лінії, b, відношення підйому лінії до довжини лінії, має значення 0.5. Позитивний нахил означає, що лінія піднімається зліва направо. Якщо b = 0, лінія горизонтальна, а це означає, що між залежною та незалежною змінними немає жодного зв'язку. Інакше кажучи, зміна значення x впливає значення y.
Часто плутають ŷ та у. На графіку показано 6 упорядкованих пар точок та лінія, відповідно до даного рівняння
На цьому малюнку показана точка, що відповідає впорядкованій парі х = 2 і у = 4. Зверніть увагу, що очікуване значення у відповідно до лінії при х= 2 є ŷ. Ми можемо підтвердити це за допомогою наступного рівняння:
ŷ = 2 + 0.5х = 2 +0.5 (2) = 3.
Значення у являє собою фактичну точку, а значення ŷ - це очікуване значення у з використанням лінійного рівнянняпри заданому значенні х.
Наступний крок - визначити лінійне рівняння, що максимально відповідає набору впорядкованих пар, про це ми говорили в попередній статті, де визначали вид рівняння по .
Використання Excel для визначення лінійної регресії
Для того, щоб скористатися інструментом регресійного аналізу, вбудованого в Excel, необхідно активувати надбудову Пакет аналізу. Знайти її можна, перейшовши вкладкою Файл -> Параметри(2007+), у діалоговому вікні, що з'явилося. ПараметриExcelпереходимо у вкладку Надбудови.В полі Управлінняобираємо НадбудовиExcelі клацаємо Перейти.У вікні, що з'явилося, ставимо галочку навпроти Пакет аналізу,тиснемо ОК.
У вкладці Данів групі Аналізз'явиться нова кнопка Аналіз даних.
Щоб продемонструвати роботу надбудови, скористаємося даними, де хлопець та дівчина ділять столик у ванній кімнаті. Введіть дані нашого прикладу з ванною в стовпці А та В чистого листа.
Перейдіть у вкладку Дані,в групі Аналізклацніть Аналіз даних.У вікні, що з'явилося Аналіз данихВиберіть Регресія, як показано на малюнку, і натисніть кнопку ОК.
Встановіть необхідні параметри регресії у вікні Регресія, як показано на малюнку:
Клацніть ОК.На малюнку нижче показані отримані результати:
Ці результати відповідають тим, які ми отримали шляхом самостійних обчислень у .
Регресійний аналізє одним із найбільш затребуваних методів статистичного дослідження. З його допомогою можна встановити рівень впливу незалежних величин на залежну змінну. У функціоналі Microsoft Excelє інструменти, призначені щодо такого аналізу. Давайте розберемо, що вони являють собою і як ними користуватися.
Але для того, щоб використовувати функцію, що дозволяє провести регресійний аналіз, перш за все, потрібно активувати Пакет аналізу. Тільки тоді необхідні для цієї процедури інструменти з'являться на Стрічці Ексель.
Тепер, коли ми перейдемо у вкладку «Дані»на стрічці в блоці інструментів «Аналіз»ми побачимо нову кнопку – «Аналіз даних».
Види регресійного аналізу
Існує кілька видів регресій:
- параболічна;
- статечна;
- логарифмічна;
- експонентна;
- показова;
- гіперболічна;
- Лінійна регресія.
Про виконання останнього виду регресійного аналізу в Екселі ми докладніше поговоримо далі.
Лінійна регресія у програмі Excel
Внизу, як приклад, представлена таблиця, в якій зазначено середньодобову температуру повітря на вулиці, та кількість покупців магазину за відповідний робочий день. Давайте з'ясуємо за допомогою регресійного аналізу, як саме погодні умовияк температури повітря можуть вплинути на відвідуваність торгового закладу.
Загальне рівняння регресії лінійного вигляду має такий вигляд: У = а0 + а1х1 +…+акхк. У цій формулі Yозначає змінну, вплив чинників яку ми намагаємося вивчити. У нашому випадку це кількість покупців. Значення x- Це різні фактори, що впливають на змінну. Параметри aє коефіцієнтами регресії. Тобто саме вони визначають значущість того чи іншого чинника. Індекс kпозначає загальну кількість цих факторів.
Розбір результатів аналізу
Результати регресійного аналізу виводяться у вигляді таблиці там, яке зазначено в настройках.
Одним із основних показників є R-квадрат. У ньому вказується якість моделі. У нашому випадку цей коефіцієнт дорівнює 0,705 або близько 70,5%. Це прийнятний рівень якості. Залежність менше ніж 0,5 є поганою.
Ще один важливий показникрозташований у осередку на перетині рядка «Y-перетин»та стовпця «Коефіцієнти». Тут вказується яке значення буде у Y, а в нашому випадку, це кількість покупців, за всіх інших факторів рівних нулю. У цій таблиці дане значеннядорівнює 58,04.
Значення на перетині граф «Змінна X1»і «Коефіцієнти»показує рівень залежності Y від X. У нашому випадку це рівень залежності кількості клієнтів магазину від температури. Коефіцієнт 1,31 вважається досить високим показникомвпливу.
Як бачимо, за допомогою програми Microsoft Excel досить легко скласти таблицю регресійного аналізу. Але працювати з отриманими на виході даними і розуміти їх суть зможе лише підготовлена людина.
Пакет MS Excel дозволяє при побудові рівняння лінійної регресії більшу частину роботи зробити дуже швидко. Важливо зрозуміти, як інтерпретувати отримані результати. Для побудови моделі регресії необхідно вибрати пункт Сервіс\Аналіз даних\Регресія (в Excel 2007 цей режим знаходиться в блоці Дані/Аналіз даних/Регресія). Потім отримані результати скопіювати блок для аналізу.
Регресійний аналіз є одним із найбільш затребуваних методів статистичного дослідження. З його допомогою можна встановити рівень впливу незалежних величин на залежну змінну. У функціоналі Microsoft Excel є інструменти, призначені щодо такого аналізу. Давайте розберемо, що вони являють собою і як ними користуватися.
Підключення пакету аналізу
Але для того, щоб використовувати функцію, що дозволяє провести регресійний аналіз, перш за все, потрібно активувати Пакет аналізу. Тільки тоді необхідні для цієї процедури інструменти з'являться на Стрічці Ексель.
- Переміщуємось у вкладку «Файл».
- Переходимо до розділу «Параметри».
- Відкривається вікно параметрів Excel. Переходимо до підрозділу «Надбудови».
- У нижній частині вікна переставляємо перемикач в блоці «Управління» в позицію «Надбудови Excel», якщо він знаходиться в іншому положенні. Тиснемо на кнопку «Перейти».
- Відкриється вікно доступних надбудов Ексель. Ставимо галочку біля пункту "Пакет аналізу". Тиснемо на кнопку «OK».
Тепер, коли ми перейдемо у вкладку "Дані", на стрічці в блоці інструментів "Аналіз" ми побачимо нову кнопку - "Аналіз даних".
Види регресійного аналізу
Існує кілька видів регресій:
- параболічна;
- статечна;
- логарифмічна;
- експонентна;
- показова;
- гіперболічна;
- Лінійна регресія.
Про виконання останнього виду регресійного аналізу в Екселі ми докладніше поговоримо далі.
Лінійна регресія у програмі Excel
Внизу, як приклад, представлена таблиця, в якій зазначено середньодобову температуру повітря на вулиці, та кількість покупців магазину за відповідний робочий день. Давайте з'ясуємо за допомогою регресійного аналізу, як погодні умови у вигляді температури повітря можуть вплинути на відвідуваність торгового закладу.
Загальне рівняння регресії лінійного вигляду має такий вигляд: У = а0 + а1х1 +…+акхк. У цій формулі Y означає змінну вплив факторів на яку ми намагаємося вивчити. У нашому випадку це кількість покупців. Значення x – це різні чинники, що впливають змінну. Параметри є коефіцієнтами регресії. Тобто саме вони визначають значущість того чи іншого чинника. Індекс k позначає загальну кількість цих факторів.
Розбір результатів аналізу
Результати регресійного аналізу виводяться у вигляді таблиці там, яке зазначено в настройках.
Одним із основних показників є R-квадрат. У ньому вказується якість моделі. У нашому випадку цей коефіцієнт дорівнює 0,705 або близько 70,5%. Це прийнятний рівень якості. Залежність менше ніж 0,5 є поганою.
Ще один важливий показник розташований у осередку на перетині рядка «Y-перетин» та стовпця «Коефіцієнти». Тут вказується яке значення буде у Y, а нашому випадку, це кількість покупців, за всіх інших чинниках рівних нулю. У цій таблиці це значення дорівнює 58,04.
Значення на перетині граф «Змінна X1» та «Коефіцієнти» показує рівень залежності Y від X. У нашому випадку це рівень залежності кількості клієнтів магазину від температури. Коефіцієнт 1,31 вважається досить високим показником впливу.
Як бачимо, за допомогою програми Microsoft Excel досить легко скласти таблицю регресійного аналізу. Але працювати з отриманими на виході даними і розуміти їх суть зможе лише підготовлена людина.
Ми раді, що змогли допомогти Вам у вирішенні проблеми.
Задайте своє питання у коментарях, детально розписавши суть проблеми. Наші фахівці намагатимуться відповісти максимально швидко.
Чи допомогла вам ця стаття?
Метод лінійної регресії дозволяє нам описувати пряму лінію, що максимально відповідає ряду впорядкованих пар (x, y). Рівняння для прямої лінії, відоме як лінійне рівняння, наведено нижче:
ŷ - очікуване значення у при заданому значенні х,
x - незалежна змінна,
a - відрізок на осі y для прямої лінії,
b - нахил прямої лінії.
На малюнку нижче це поняття представлено графічно:
На малюнку вище показана лінія, описана рівнянням = 2+0.5х. Відрізок на осі у - це точка перетину лінією осі у; у разі а = 2. Нахил лінії, b, відношення підйому лінії до довжини лінії, має значення 0.5. Позитивний нахил означає, що лінія піднімається зліва направо. Якщо b = 0, лінія горизонтальна, а це означає, що між залежною та незалежною змінними немає жодного зв'язку. Інакше кажучи, зміна значення x впливає значення y.
Часто плутають ŷ та у. На графіку показано 6 упорядкованих пар точок та лінія, відповідно до даного рівняння
На цьому малюнку показана точка, що відповідає впорядкованій парі х = 2 і у = 4. Зверніть увагу, що очікуване значення у відповідно до лінії при х= 2 є ŷ. Ми можемо підтвердити це за допомогою наступного рівняння:
ŷ = 2 + 0.5х = 2 +0.5 (2) = 3.
Значення у є фактичну точку, а значення ŷ - це очікуване значення у з використанням лінійного рівняння при заданому значенні х.
Наступний крок - визначити лінійне рівняння, що максимально відповідає набору впорядкованих пар, про це ми говорили в попередній статті, де визначали вид рівняння за методом найменших квадратів.
Використання Excel для визначення лінійної регресії
Для того, щоб скористатися інструментом регресійного аналізу, вбудованого в Excel, необхідно активувати надбудову Пакет аналізу. Знайти її можна, перейшовши вкладкою Файл -> Параметри(2007+), у діалоговому вікні, що з'явилося. ПараметриExcelпереходимо у вкладку Надбудови.В полі Управлінняобираємо НадбудовиExcelі клацаємо Перейти.У вікні, що з'явилося, ставимо галочку навпроти Пакет аналізу,тиснемо ОК.
У вкладці Данів групі Аналізз'явиться нова кнопка Аналіз даних.
Щоб продемонструвати роботу надбудови, скористаємося даними з попередньої статті, де хлопець та дівчина ділять столик у ванній кімнаті. Введіть дані нашого прикладу з ванною в стовпці А та В чистого листа.
Перейдіть у вкладку Дані,в групі Аналізклацніть Аналіз даних.У вікні, що з'явилося Аналіз данихВиберіть Регресія, як показано на малюнку, і натисніть кнопку ОК.
Встановіть необхідні параметри регресії у вікні Регресія, як показано на малюнку:
Клацніть ОК.На малюнку нижче показані отримані результати:
Ці результати відповідають тим, що ми отримали шляхом самостійних обчислень у попередній статті.
Регресійний аналіз – це статистичний методдослідження, що дозволяє показати залежність тієї чи іншої параметра від однієї чи кількох незалежних змінних. У докомп'ютерну еру його застосування було досить складно, особливо якщо йшлося про великих обсягахданих. Сьогодні, дізнавшись, як побудувати регресію в Excel, можна вирішувати складні статистичні завдання буквально за пару хвилин. Нижче представлені конкретні прикладиз галузі економіки.
Види регресії
Саме це поняття було введено в математику Френсісом Гальтоном у 1886 році. Регресія буває:
- лінійної;
- параболічній;
- статечною;
- експоненційною;
- гіперболічній;
- показовою;
- логарифмічні.
Приклад 1
Розглянемо завдання визначення залежності кількості членів колективу, що звільнилися, від середньої зарплати на 6 промислових підприємствах.
Завдання. На шести підприємствах проаналізували середньомісячну заробітну плату та кількість працівників, які звільнилися за власним бажанням. У табличній формі маємо:
Для завдання визначення залежності кількості працівників, що звільнилися, від середньої зарплати на 6 підприємствах модель регресії має вигляд рівняння Y = а0 + а1×1 +…+аkxk, де хi - що впливають змінні, ai - коефіцієнти регресії, a k - число факторів.
Для цього завдання Y - це показник співробітників, що звільнилися, а впливаючий фактор - зарплата, яку позначаємо X.
Використання можливостей табличного процесора «Ексель»
Аналізу регресії в Excel має передувати застосування наявних табличних даних вбудованих функцій. Однак для цього краще скористатися дуже корисною надбудовою «Пакет аналізу». Для його активації потрібно:
- з вкладки "Файл" перейти до розділу "Параметри";
- у вікні вибрати рядок «Надбудови»;
- клацнути на кнопці «Перейти», розташованої внизу, праворуч від рядка «Управління»;
- поставити галочку поруч із назвою «Пакет аналізу» та підтвердити свої дії, натиснувши «Ок».
Якщо все зроблено правильно, у правій частині вкладки "Дані", розташованому над робочим листом "Ексель", з'явиться потрібна кнопка.
Лінійна регресія в Excel
Тепер, коли під рукою є всі необхідні віртуальні інструменти для здійснення економетричних розрахунків, можемо розпочати вирішення нашого завдання. Для цього:
- клацаємо по кнопці «Аналіз даних»;
- у вікні натискаємо на кнопку «Регресія»;
- в вкладку, що з'явилася, вводимо діапазон значень для Y (кількість звільнених працівників) і для X (їх зарплати);
- підтверджуємо свої дії, натиснувши кнопку «Ok».
В результаті програма автоматично заповнить новий лист табличного процесораданими аналізу регресії. Зверніть увагу! В Excel є можливість самостійно задати місце, якому ви надаєте перевагу для цієї мети. Наприклад, це може бути той самий лист, де знаходяться значення Y і X, або навіть нова книгаспеціально призначена для зберігання подібних даних.
Аналіз результатів регресії для R-квадрату
У Excel даніотримані в ході обробки даних прикладу, що розглядається, мають вигляд:
Насамперед, слід звернути увагу до значення R-квадрата. Він є коефіцієнтом детермінації. У даному прикладі R-квадрат = 0,755 (75,5%), тобто розрахункові параметри моделі пояснюють залежність між параметрами, що розглядаються, на 75,5 %. Чим вище значення коефіцієнта детермінації, тим обрана модель вважається більш застосовною для конкретного завдання. Вважається, що вона коректно визначає реальну ситуацію за значення R-квадрату вище 0,8. Якщо R-квадрату tкр, гіпотеза про незначущість вільного члена лінійного рівняння відкидається.
У розглянутій задачі для вільного члена за допомогою інструментів «Ексель» було отримано, що t=169,20903, а p=2,89Е-12, тобто маємо нульову ймовірність того, що буде відкинута вірна гіпотеза про незначущість вільного члена. Для коефіцієнта за невідомої t=5,79405, а p=0,001158. Іншими словами ймовірність того, що буде відкинута вірна гіпотеза про незначущість коефіцієнта за невідомої, дорівнює 0,12%.
Отже, можна стверджувати, що отримане рівняння лінійної регресії адекватно.
Завдання про доцільність купівлі пакету акцій
Множинна регресія в Excel виконується з використанням того ж інструменту «Аналіз даних». Розглянемо конкретне прикладне завдання.
Керівництво компанія «NNN» має ухвалити рішення про доцільність купівлі 20% пакету акцій АТ «MMM». Вартість пакету (СП) складає 70 млн. американських доларів. Фахівцями NNN зібрані дані про аналогічні угоди. Було ухвалено рішення оцінювати вартість пакета акцій за такими параметрами, вираженими в мільйонах американських доларів, як:
- кредиторська заборгованість (VK);
- обсяг річного обороту (VO);
- дебіторська заборгованість (VD);
- вартість основних фондів (СОФ).
Крім того, використовується параметр заборгованості підприємства із зарплати (V3 П) у тисячах американських доларів.
Рішення засобами табличного процесора Excel
Насамперед, необхідно скласти таблицю вихідних даних. Вона має такий вигляд:
- викликають вікно "Аналіз даних";
- обирають розділ «Регресія»;
- у віконце «Вхідний інтервал Y» вводять діапазон значень залежних змінних зі стовпця G;
- клацають по іконці з червоною стрілкою праворуч від вікна «Вхідний інтервал X» і виділяють на аркуші діапазон всіх значень стовпців B,C, D, F.
Позначають пункт «Новий робочий лист» та натискають «Ok».
Отримують аналіз регресії для цього завдання.
Вивчення результатів та висновки
«Збираємо» із заокруглених даних, представлених вище на аркуші табличного процесора Excel, рівняння регресії:
СП = 0,103 * СОФ + 0,541 * VO - 0,031 * VK + 0,405 * VD +0,691 * VZP - 265,844.
У більш звичному математичному вигляді його можна записати як:
y = 0,103 * x1 + 0,541 * x2 - 0,031 * x3 +0,405 * x4 +0,691 * x5 - 265,844
Дані для АТ «MMM» представлені у таблиці:
Підставивши їх у рівняння регресії, одержують цифру в 64,72 млн американських доларів. Це означає, що акції АТ «MMM» не варто купувати, оскільки їхня вартість у 70 млн американських доларів досить завищена.
Як бачимо, використання табличного процесора «Ексель» та рівняння регресії дозволило ухвалити обґрунтоване рішення щодо доцільності цілком конкретної угоди.
Тепер ви знаєте, що таке регресія. Приклади в Excel, розглянуті вище, допоможуть вам вирішити практичних завданьз галузі економетрики.
