Лабораторна робота 13. Кореляційно-регресійний аналіз в microsoft excel

План

1. Послідовність виконання кореляційно-регресійного аналізу.

2. Засоби виконання КРА

3. Інтерпретпція одержаних результатів

Теоретична частина

Кореляційно-регресійний аналіз (КРА) проводиться з метою вимірювання тісноти залежності між досліджуваними ознаками, визначення абсолютного та відносного впливу чинників на результат, виявлення резервів росту (зниження) показників, що вивчаються, і оцінювання величини цих резервів.

В ході КРА вирішується дві основні проблеми:

1. вимірювання тісноти кореляційного зв'язку між змінними (обчислення коефіцієнтів кореляції, детермінації, т.і.).

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

Регресійна модель може бути лінійною або нелінійною: параболічною, степеневою, логарифмічною, т.і.

Більш докладно сутність та технологія проведення КРА розглядалася у курсах: “Методи прийняття управлінських рішень”, “Економіко-математичні методи”, “Статистика”.

Засобами Microsoft Excel можна провести КРА декількома методами. Розглянемо два з них більш докладно на приклаі.

Умова задачі.

За даними, наведеними у табл. 1, виміряти тісноту залежності між курсом акцій та рентабельністю виробництва, та розрахувати коефіцієнти регресії. Проаналізувати отримані результати.

Таблиця 1

Вихідні дані

Курс акцій (У)	Рентабельність виробництва, % (Х)
111	12,4
122	13,7
135	14,8
140	15,5
158	17,1
166	18,9
177	19,4
182	22,2
195	23,5
209	25,6

Порядок рішення

Перший спосіб проведення КРА – на основі вбудованої функції “ЛИНЕЙН”.

У створену робочу книгу вставити робочий лист (ЛИСТ2) та перейменувати його у “КРА”. Ввести в стовпчики вихідні дані.

Далі під вихідними даними активізувати (виділити) розрахунковий блок осередків, розміром 5х2, і застосувати команди:

• Мастер функций f_х - “Статистичні” – “ЛИНЕЙН”.

У діалогове вікно, що з’являється, треба ввести:

1. адреси осередків У

2. адреси осередків Х

3. 1, щоб отримати коефіцієнт а₀, та 0, якщо розрахунок а₀ не потрібен

4. 1, щоб отримати додаткові статистичні параметри; та 0, якщо ці параметри не потрібні.

5. Натиснути команду ОК.

Після цього у верхньому лівому куті активізованого прямокутника з’являється значення а_1. Для того, щоб у блоці розрахункових даних було видно не лише значення першого осередку, треба натиснути “F2”, а потім одночасно комбінацію клавіш “Ctrl+Shift+Enter”. Фрагмент робочого листа “КРА” представлено на рис.5.

	А	В
1	Курс акцій (У)	Рентабельність виробництва, % (Х)
2
3	111	12,4
4	122	13,7
5	135	14,8
6	140	15,5
7	158	17,1
8	166	18,9
9	177	19,4
10	182	22,2
11	195	23,5
12	209	25,6
13
14	Розрахунковий блок
15	7,203	27,619
16	0,421	7,914
17	0,973	5,569
18	292,126	8,000
19	9058,431	248,069

Рис.5. Фрагмент робочого листа “КРА” з вихідними даними та рішенням по першому способу

У розрахунковому блоці А15:В19 (рис.5) містяться наступні дані:

Перший рядок із права в лівий бік: коефіцієнти парної регресії а₀ та а₁. Отже лінійна регресійна модель матиме вигляд:

У = 27,619 + 7,203*х

У другому рядку містяться стандартні помилки відповідних коефіцієнтів парної лінійної регресії.

У третьому рядку із права в лівий бік знаходяться стандартна помилка усього рівняння регресії S_у та коефіцієнт детермінації R².

У четвертому рядку із права в лівий бік – знаходиться число ступенів вільності k та розрахункове значення F- критерію Фішера.

У п’ятому рядку із права в лівий бік - знаходяться сума квадратів відхилень фактичних спостережень У_і від середнього значення У.

Другий спосіб аналізу – на основі вбудованого блока КРА.

Виконати команди “Сервіс” – “Набдудови” – активізувати “Пакет аналізу”.

Потім знову “Сервіс” – “Аналіз даних” – “Регресія” – “ОК”.

В діалоговому вікні, вказати вихідні дані:

1. адреси осередків У

2. адреси осередків Х

3. рівень надійності – 95%

4. вказується вихідний інтервал, куди буде виведена розрахункова інформація (наприклад, адреса осередку С3).

Після команди “ОК” на робочому листі “КРА” з'являється розрахункова таблиця (рис. 6). У ній значення коефіцієнтів регресії знаходяться у нижній частині у стовпчику Коефіцієнти. Їх чисельні значення співпадають з результатами отриманими за першим способом.

ВИВОДИ ПІДСУМКІВ
Регрессионная статистика
Множественный R	0,98658224
R-квадрат	0,97334451
Нормированный R-квадрат	0,97001257
Стандартная ошибка	5,56854246
Наблюдения	10
Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия	1	9058,43068	9058,43068	292,125787	1,39537E-07
Остаток	8	248,069321	31,0086651
Итого	9	9306,5
	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%
Y-пересечение	27,6192178	7,91446237	3,48971497	0,00820185	9,368423039	45,8700126
Переменная X 1	7,20266424	0,42141328	17,0916876	1,3954E-07	6,230882832	8,17444564

Рис.6. Результати КРА курсу акцій за другим способом

Статистичний аналіз побудованої моделі.

Статистичний аналіз здійснюється за допомогою показників:

1. тісноти кореляційного зв’язку

2. точності

3. надійності

4. адекватності.

Показником тісноти кореляційного зв’язку для будь-якої моделі служить коефіцієнт множинної кореляції R. Його величина використовується як міра тісноти зв’язку між результативною ознакою і чинниками, що увійшли до рівняння регресії. Для парної лінійної моделі коефіцієнт множинної кореляції співпадає з абсолютною величиною коефіцієнта парної кореляції.

У нашому прикладі R=0,986582 (рис.6), що свідчить про дуже тісний зв’язок між величинами, що вивчаються.

Якщо значення коефіцієнта парної кореляції знаходиться у межах:

від 0 до 0,3 - зв’язок вважається слабким,

від 0,3 до 0,7 – середнім

від 0,7 до 1 – тісним.

Середня квадратична (стандартна) помилка регресії є абсолютною мірою точності побудованої парної моделі. Для рівняння S_у =5,568542 (рис.6) можна дати таку рекомендацію по тлумаченню її величини: для одних й тих же вихідних даних менша стандартна помилка відповідає більш точній моделі.

Відносна точність регресійного рівняння оцінюється за допомогою коефіцієнта множинної кореляції R², який називається коефіцієнтом детермінації. Він показує частку варіації результативної ознаки У за рахунок чинника Х. У нашій задачі R²= 0,973345 (рис.6), що означає, що (0,973345*100%=) 97,3% варіації курсу акцій пояснюється зміною рентабельності виробництва. На долю чинників, що не увійшли у рівняння приходиться лише 2,7% варіації результативної ознаки У.

Якщо кількість значень (вибірка) невелика (менше 20), то знаходиться нормований коефіцієнт детермінації R_н². Він завжди нижче за R² і враховує співвідношення числа спостережень (N) і кількість коефіцієнтів рівняння регресії (m). У нашому випадку значення R_н² =0,970012 означає, що незважаючи на малу вибірку (N=10), регресійна модель є дуже точною: з урахуванням співвідношення N і m більше 97% варіації курсу акцій пояснюється зміною рентабельності виробництва.

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

Перевірка значущості рівняння зводиться до розрахунку F-критерію Фишера. Розрахункове значення F-критерію = 292,1258 та його р-значущість р=1,39537Е-07 (тобто р=1,39537*10^-7) (рис.6). Оскільки при рішенні задачі приймалася гіпотеза про 95% рівень надійності, то α=0,05 ((100-95)/100). Це означає, що отриманий результат (р=1,39537Е-07) суттєво нижче прийнятого (α=0,05). Тобто, парна регресійна модель у цілому статистично значуща.

Перевірка значущості коефіцієнтів а₀ та а₁ виконується за допомогою стандартних помилок S_у та S_х. У нашому випадку вони дорівнюють S_у =7,914462 та S_х=0,421413, р-значущість коефіцієнтів попадає у критичну область, тобто менше 0,05. Отже модель і окремі коефіцієнти статистично значущі, надійні.

Далі розраховують нижні та верхні 95%-ві довірчі інтервали (“Нижні 95%” та “Верхні 95%” - рис. 6). Тобто з ймовірністю 95% відсотків можна стверджувати, що істинні значення розрахованих коефіцієнтів регресії знаходяться у межах:

Індивідуальні завдання

За даними, наведеними у таблиці, виміряти тісноту залежності між чинниками, та розрахувати коефіцієнти регресії. Проаналізувати отримані результати.

Варіант	Ознаки	Вибірка
		1	2	3	4	5	6	7	8	9	10
1	Енергоозброєнність праці, кВт-год	29	38	46	54	62	70	79	97,3	102	110
	Валова продукція, тис. грн.	15,2	17	25	26,3	32	34,1	38	42	44	47
2	Основні виробничі фонди, млн. грн.	8	10	11	13	15	17	19	22	23	25
	Товарна продукція, млн. грн.	54	63	74	90	112	140	190	210	220	225
3	Врожайність культури, ц/га	298	386	461	545	627	703	790	973
	Кількість внесених добрив, кг/га	36	58,3	60,1	79,2	80,3	10	138	185
4	Стаж роботи, років	1	3	4	2	5	7	8	6
	Середня заробітна плата, грн.	80	90	120	100	110	150	160	130
5	Обсяг будівельно-монтажних робіт, млн. грн.	6,3	6	7,5	8,5	3,5	6,2	7,5	8,7	6	3,7
	Рівень механізації праці, балів	5	4	6	7	3	4	6	7	4	3
6	Рівень кваліфікації, розряд	1	2	3	4	5	6
	Середня заробітна плата, грн.	115	127	138	149	175	180
7	Продуктивність праці, тис. грн.	111	122	135	140	158	166	177	182	195	209
	Середня заробітна плата, грн.	154	184	209	230	255	269	283	301	307	319
8	Продуктивність праці, тис.грн.	124	137	148	155	171	189	194	222	235	256
	Фондоозброєнність, тис. грн.	425	571	610	627	744	787	890	918	943	985
9	Продуктивність праці, тис.грн.	312	335	390	411	430	476	515	554	619	635
	Собівартість одиниці продукції, грн.	308	313	316	314	307	304	300	295	289	285
10	Продуктивність праці, тис.грн.	88	87	100	102	120	133	148	163	170	175
	Середня заробітна плата, грн.	154	184	209	230	255	269	283	301	307	319
11	Продуктивність праці, тис. грн.	97	115	126	134	145	160	171	179	189	193
	Фондоозброєнність, тис. грн.	425	571	610	627	744	787	890	918	943	985

Контрольні питання:

1. Призначення кореляційно-регресійного аналізу

2. У чому полягає сутність статистичного аналізу отриманої моделі

3. За допомогою яких універсальних ППП можна вирішувати такі задачі