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

Для роботи необхідна надбудова Пакет аналізу, яку необхідно увімкнути в пункті меню Сервіс\Надбудови

В Excel 2007 для включення пакета аналізу треба натиснути на блок. Параметри Excel, натиснувши кнопку в лівому верхньому кутку, а потім кнопку « Параметри Excel» внизу вікна:

Для побудови моделі регресії необхідно вибрати пункт Сервіс\Аналіз даних\Регресія. (В Excel 2007 цей режим знаходиться в блоці Дані/Аналіз даних/Регресія). З'явиться діалогове вікно, яке потрібно заповнити:

1) Вхідний інтервал Y¾ містить посилання на комірки, які містять значення результативної ознаки y. Значення мають бути розташовані у стовпці;

2) Вхідний інтервал X¾ містить посилання на комірки, які містять значення факторів. Значення мають бути розташовані в стовпцях;

3) Ознака Міткиставиться, якщо перші осередки містять пояснювальний текст (підпису даних);

4) Рівень надійності¾ це довірча ймовірність, яка за умовчанням вважається рівною 95%. Якщо це значення не влаштовує, потрібно включити цю ознаку і ввести необхідне значення;

5) Ознака Константа-нульвключається, якщо необхідно побудувати рівняння, у якому вільна змінна;

6) Параметри виводувизначають, куди мають бути поміщені результати. За промовчанням будує режим Новий робочий лист;

7) Блок Залишкидозволяє включати виведення залишків та побудову їх графіків.

В результаті виводиться інформація, що містить усі необхідні відомостіі згрупована в три блоки: Регресійна статистика , Дисперсійний аналіз , Виведення залишку. Розглянемо їх докладніше.

1. Регресійна статистика:

множинний Rвизначається формулою ( коефіцієнт кореляції Пірсона);

R (коефіцієнт детермінації);

Нормований R-квадрат обчислюється за формулою (використовується для множинної регресії);

Стандартна помилка Sобчислюється за формулою ;

Спостереження - це кількість даних n.

2. Дисперсійний аналіз, рядок Регресія:

Параметр dfдорівнює m(кількість наборів факторів x);

Параметр SSвизначається формулою;

Параметр MSвизначається формулою;

Статистика Fвизначається формулою;

Значимість F. Якщо отримане число перевищує, то приймається гіпотеза (немає лінійного взаємозв'язку), інакше приймається гіпотеза (є лінійний взаємозв'язок).

3. Дисперсійний аналіз, рядок Залишок:

Параметр dfдорівнює;

Параметр SSвизначається формулою ;

Параметр MSвизначається формулою.

4. Дисперсійний аналіз, рядок Разоммістить суму перших двох стовпців.

5. Дисперсійний аналіз, рядок Y-перетинмістить значення коефіцієнта , стандартної помилки та t-статистики.

P-значення ¾ це значення рівнів значущості, що відповідає обчисленим t-Статистикам. Визначається функцією Стюдрасп ( t-Статистика; ). Якщо P-Значення перевищує , то відповідна змінна статистично незначна і її можна виключити з моделі.

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

6. Дисперсійний аналізрядки містять значення коефіцієнтів, стандартних помилок, t-статистик, P-Значень та довірчих інтервалів для відповідних .

7. Блок Виведення залишкумістить значення передбаченого y(У наших позначеннях це) і залишки.

28 жовтень

Доброго дня, шановні читачі блогу! Сьогодні ми поговоримо про нелінійні регресії. Рішення лінійних регресійможна подивитися за ПОСИЛАННЯ .

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

Основними типами нелінійних регресійє:

• поліноміальні (квадратична, кубічна);
• гіперболічна;
• статечна;
• показова;
• логарифмічна.

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

У прогнозуванні за допомогою нелінійних регресій головне з'ясувати коефіцієнт кореляції, який покаже нам чи є. тісний взаємозв'язокмеду двома параметрами чи ні. Як правило, якщо коефіцієнт кореляції близький до 1, отже зв'язок є, і прогноз буде досить точним. Ще одним важливим елементомнелінійних регресій є середня відносна помилка (А ), якщо вона знаходиться в проміжку<8…10%, значит модель достаточно точна.

На цьому, мабуть, теоретичний блок ми закінчимо та перейдемо до практичних обчислень.

У нас є таблиця продажів автомобілів за проміжок 15 років (позначимо його X), кількість кроків вимірювань буде аргументом n, також є виручка за ці періоди (позначимо її Y), нам потрібно спрогнозувати яка буде виручка надалі. Побудуємо наступну таблицю:

Для дослідження нам потрібно буде вирішити рівняння (залежності Y від X): y=ax 2 +bx+c+e. Це парна квадратична регресія. Застосуємо у разі метод найменших квадратів, з'ясування невідомих аргументів — a, b, c. Він приведе до системи рівнянь алгебри виду:

Для вирішення цієї системи скористаємося, наприклад, способом Крамера. Бачимо, що суми, що входять до системи, є коефіцієнтами при невідомих. Для їх обчислення додамо в таблицю кілька стовпців (D,E,F,G,H) і підпишемо відповідно до змісту обчислень — у стовпці D зведемо x у квадрат, E у куб, F у 4 ступінь, у G перемножимо показники x і y, H зведемо x в квадрат і перемножимо з y.

Вийде заповнена потрібними на вирішення рівняння таблиця виду.

Сформуємо матрицю A системи, що складається з коефіцієнтів за невідомих у лівих частинах рівнянь. Помістимо її в комірку А22 і назвемо « А =«. Слідуємо тій системі рівнянь, яку ми обрали для вирішення регресії.

Тобто в осередок B21 ми повинні помістити суму стовпця, де зводили показник X у четвертий ступінь F17. Просто пошлемося на комірку — «=F17». Далі нам необхідна сума стовпця, де зводили X в куб - E17, далі йдемо строго по системі. Таким чином, нам потрібно буде заповнити всю матрицю.

Відповідно до алгоритму Крамера наберемо матрицю А1, подібну до А, в якій замість елементів першого стовпця повинні розміщуватися елементи правих частин рівнянь системи. Тобто сума стовпця X у квадраті помножена на Y, сума стовпця XY та сума стовпця Y.

Також нам знадобляться ще дві матриці — назвемо їх А2 і А3, у яких другий і третій стовпці складаються з коефіцієнтів правих частин рівнянь. Картина буде такою.

Наслідуючи обраний алгоритм, нам потрібно буде обчислити значення визначників (детермінантів, D) отриманих матриць. Скористаємося формулою МОПРЕД. Результати розмістимо в осередках J21: K24.

Розрахунок коефіцієнтів рівняння по Крамеру будемо проводити в осередках навпроти відповідних детермінантів за формулою: a(В комірці M22) - "= K22 / K21"; b(В комірці M23) - "= K23 / K21"; з(В комірці M24) - "= K24 / K21".

Отримаємо наше шукане рівняння парної квадратичної регресії:

y=-0,074x 2 +2,151x+6,523

Оцінимо тісноту лінійного зв'язку індексом кореляції.

Для обчислення додамо до таблиці додатковий стовпець J (назвемо його y*). Розрахунку буде наступною (згідно з отриманим нами рівнянням регресії) — "=$m$22*B2*B2+$M$23*B2+$M$24".Помістимо її в комірку J2. Залишиться протягнути вниз маркер автозаповнення до осередку J16.

Для обчислення сум (Y-Y усереднене) 2 додамо таблицю стовпці K і L з відповідними формулами. Середнє по стовпцю Y порахуємо за допомогою функції СРЗНАЧ.

У осередку K25 розмістимо формулу підрахунку індексу кореляції - «=КОРІНЬ(1-(K17/L17))».

Бачимо, що значення 0,959 дуже близько до 1, отже, між продажами і роками є тісний нелінійний зв'язок.

Залишилося оцінити якість припасування отриманого квадратичного рівняння регресії (індекс детермінації). Він розраховується за формулою квадрата індексу кореляції. Тобто формула в осередку K26 буде дуже проста - = K25 * K25.

Коефіцієнт 0,920 близький до 1, що свідчить про високу якість припасування.

Останньою дією буде обчислення відносної помилки. Додамо стовпець і внесемо туди формулу: «ABS((C2-J2)/C2), ABS — модуль, абсолютне значення. Протягнемо маркером вниз і в комірці M18 виведемо середнє значення (СРЗНАЧ), призначимо коміркам відсотковий формат. Отриманий результат - 7,79% знаходиться в межах допустимих значень помилки<8…10%. Значит вычисления достаточно точны.

Якщо виникне потреба, за набутими значеннями ми можемо побудувати графік.

Файл з прикладом додається - ПОСИЛАННЯ!

Категорії:// Від 28.10.2017

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

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

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

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

Пакет MS Excel дозволяє при побудові рівняння лінійної регресії більшу частину роботи зробити дуже швидко. Важливо зрозуміти, як інтерпретувати отримані результати. Для побудови моделі регресії необхідно вибрати пункт Сервіс\Аналіз даних\Регресія (в Excel 2007 цей режим знаходиться в блоці Дані/Аналіз даних/Регресія). Потім отримані результати скопіювати блок для аналізу.

Початкові дані:

Результати аналізу

Включати у звіт
Розрахунок параметрів рівняння регресії
Теоретичний матеріал
Рівняння регресії у стандартному масштабі
Множинний коефіцієнт кореляції (Індекс множинної кореляції)
Приватні коефіцієнти еластичності
Порівняльна оцінка впливу аналізованих факторів на результативну ознаку (d – коефіцієнти роздільної детермінації)

Перевірка якості збудованого рівняння регресії
Значимість коефіцієнтів регресії b i (t-статистика. критерій Стьюдента)
Значення рівняння загалом (F-статистика. Критерій Фішера). Коефіцієнт детермінації
Приватні F-критерії

Рівень значущості 0.005 0.01 0.025 0.05 0.1 0.25 0.4

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

Результат аналізу дає змогу виділяти пріоритети. І ґрунтуючись на головних чинниках, прогнозувати, планувати розвиток пріоритетних напрямів, приймати управлінські рішення.

Регресія буває:

· Лінійної (у = а + 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 (це невеликий ступінь впливу). Знак «-» вказує на негативний вплив: що більша зарплата, то менше звільнених. Що слушно.