3. Використовуючи критерій Фішера, з надійністю 0,95 оцінити адекватність побудованої моделі експериментальним даним.

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

5. Обчислити значення частинних коефіцієнтів еластичності для прогнозу.

6. Зробити висновки.

Виконання лабораторної роботи

1. Введення даних. Заносимо данні до нової книги Excel.

	x1	x2	x3	y
1	1,31	3,29	0,36	5,62
2	1,24	2,54	0,32	5,71
3	1,29	2,55	0,41	5,71
4	1,22	5,39	0,32	5,65
5	1,28	2,86	0,34	5,67
6	1,23	2,79	0,33	5,71
7	1,22	5,62	0,31	5,65
8	1,25	5,14	0,38	5,63
9	1,24	5,41	0,32	5,54
10	1,25	2,54	0,30	5,69
11	1,28	2,66	0,32	5,67
12	1,24	2,20	0,33	5,69
13	1,94	5,35	0,59	6,29
14	1,96	4,89	0,59	6,37
15	2,19	5,03	0,57	6,54

2. Дослідження на мультиколінеарність, метод Фаррара-Глобера. Побудуємо кореляційну матрицю.

	x1	x2	x3
x1	1	0,415274	0,953031
x2	0,415274	1	0,418721
x3	0,953031	0,418721	1

Кореляційна матриця має розміри n×n, діагональні елементи дорівнюють1. Елемент а_ij показує щільність лінійного зв"язку між x_i та x_j.

Для побудови кореляційної матриці будемо використовувати функцію КОРРЕЛ() (Вставка→Функція→Статистичні), яка повертає значення коефіцієнта кореляції між двома заданими масивами.

	x1	x2	x3
x1	КОРРЕЛ(Х1,Х1)	КОРРЕЛ(Х1,Х2)	КОРРЕЛ(Х1,Х3)
x2	КОРРЕЛ(Х2,Х1)	КОРРЕЛ(Х2,Х2)	КОРРЕЛ(Х2,Х3)
x3	КОРРЕЛ(Х3,Х1)	КОРРЕЛ(Х3,Х2)	КОРРЕЛ(Х3,Х3)

Як бачимо, щоб заповнити цю матрицю, необхідно занести формули в n² комірок. Спростити виконання цього етапу можливо наступним чином: занести формули лише до першого стовпчику, зафіксувати перший масив і розтягнути формули по рядкам.

	x1	x2	x3
x1	КОРРЕЛ(Х1$,Х1)
x2	КОРРЕЛ(Х2$,Х1)
x3	КОРРЕЛ(Х3$,Х1)

Обчислимо визначник кореляційної матриці (функція МОПРЕД() ).

Обчислимо значення χ² - критерію:

,

де n - кількість спостережень(n=15),

m - кількість факторів (m=3)

|R| - визначник кореляційної матриці.

Знаходимо табличне значення статистики при заданій довірчій ймовірності Р і ступенях вільності . Якщо › , то мультиколінеарність існує. Інакше мультиколінеар-ність відсутня і подальші кроки не виконуються.

Оскільки в нашому прикладі мультиколінеарність існує (59,29>7,8), то слід визначити між якими саме факторами. Для цього обчислимо матрицю, обернену до кореляційної:

Z=R^-1

використовуючи функцію МОБР().

Наступний крок - обчислення частинних коефіцієнтів кореляції за формулою:

де z_ii, z_ij, z_jj – елементи матриці, оберненої до кореляційної

Тут потрібно бути уважним: ми шукаємо ті фактори, зв’язок між якими є суттєвим. І для цього повинні обчислити тісноту зв’язку між кожною можливою парою факторів.

В даному прикладі розглядаємо три фактори, отже залежність може бути між першим та другим, між другим та третім, між першим та третім факторами.

r₁₂= = =0,662033

r₂₃= = =0,699355

r₁₃= = =0,027053

Обчислимо значення t-статистики для кожного частинного коефіцієнта кореляції (див. формули):

=3,18

=3,53

=0,098

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

k=n-m-1=15-3-1=11

Значення t-статистики, що перевищує t_табл=2,2 - це t₁₂, t₂₃. Робимо висновок, що між першим та другим, і між другим та третім факторами існує залежність. Якщо між двома факторами X_i та X_j існує мультиколінеарність, то один із факторів виключається з розгляду (який саме - вирішувати Вам, виходячи з того, що це за економічні величини, як кожна з них впливає на показник ).

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

В розглянутому прикладі на другій ітерації =22,66. Переконалися, що мультиколінеарності ми не позбавилися, виключаємо з розгляду ще один фактор, залишається Х1.

4. Обчислення параметрів регресії за допомогою методу найменших квадратів у матричній формі. Обчислення виконуємо за формулою.

В зазначеній формулі під масивом (Х) розуміють матрицю, перший стовпчик якої містить одиниці («фіктивний» фактор), а всі інші – задані за умовою значення факторів, що залишилися після дослідження на мультиколінеарність. У нашому прикладі масив (Х) має такий вигляд:

	1	1,31
	1	1,24
	1	1,29
	1	1,22
	1	1,28
	1	1,23
X=	1	1,22
	1	1,25
	1	1,24
	1	1,25
	1	1,28
	1	1,24
	1	1,94
	1	1,96
	1	2,19

Масив У – значення показника.

Примітка: перед виконанням цього етапу студентам слід повторити тему «Дії над матрицями» з курсу «Вищої математики».

Обчислення параметрів доцільно проводити в такому порядку:

1. За допомогою функції ТРАНСП() (Вставка→Функция→Ссылки и массивы) обчислюємо транспоновану матрицю (X)^Т (або виконати спеціальну вставку, при цьому не забуваємо поставити помітку "транспонувати"):

.

	=	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
		1,31	1,24	1,29	1,22	1,28	1,23	1,22	1,25	1,24	1,25	1,28	1,24	1,94	1,96	2,19

Зауваження: функції ТРАНСП(), МУМНОЖ(), МОБР() є функціями масиву, тобто результат обчислень представляє собою не одну комірку, а матрицю. Спочатку вводимо формулу у верхню ліву комірку і натискаємо ENTER. Повертаємось у комірку, в яку вводили формулу, виділяємо діапазон, у якому повинна міститися вся обчислювана матриця, натискаємо клавішу F2, після чого одночасно натискаємо клавіші CTRL+SHIFT+ENTER.

2. Використовуючи функцію масиву МУМНОЖ, обчислимо :

	15	21,14
=	21,14	31,29

3. До матриці знайдемо обернену (функція МОБР):

	1,3975	-0,94
=	-0,944	0,67

4. Обчислюємо добуток матриці (X)^Т на вектор Y (МУМНОЖ()).

	87,14
=	124,22

5. Знаходимо значення параметрів шляхом множення матриці [(X)^Т(Х)]^-1 на вектор-стовпчик (X)^TY.

	4,4786		а0
=	0,9443		а1

Тобто рівняння регресії має вигляд:

=4,4786 + 0,9443Х₁ .

Також можна скористатися інструментом «Аналіз даних» або функцією «Лінійна» (див. вказівки до виконання л.р.№1).

5. Обчислюємо розрахункові значення показника, підставивши відповідні значення факторів у формулу =4,4786 + 0,9443Х₁ .

6. Перевірка адекватності моделі. Отриману модель перевіримо на адекватність за допомогою F-критерія Фішера. Оскільки його розрахункове значення міститься в таблиці, отриманій за допомогою функції «Лінійна», нам залишається лише порівняти його з табличним.

7. Обчислення прогнозного значення показника та його довірчого інтервалу. Знайдемо прогнозне значення показника. Для цього підставимо прогнозні значення факторів у побудовану нами модель. Довірча зона для прогнозного значення показника міститься в інтервалі ( ), де

.

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

- добуток (функція МУМНОЖ()):

1	2,23	*	1,3975	-0,94	=	-0,70762	0,5541
			-0,944	0,67

- отриману матрицю помножити на :

-0,70762	0,5541	*	1	=	0,528023
			2,23

Результат цієї дії являє собою матрицю з однієї комірки, тобто число. До нього лише слід додати одиницю і помножити на , яке міститься в таблиці функції "Лінійна"