Приклад виконання лабораторної роботи № 6

Для вибору виду рівняння моделі парної регресії проілюструємо графічно залежність вихідних даних за допомогою вбудованого пакету Мастер диаграмм (рис. 6.1).

Рис. 6.1. Графічна ілюстрація залежності вихідних даних

 

Графічне зображення надає можливість припустити про лінійний зв’язок: . Параметри означеної лінійної моделі можна обчислити за формулами (6.1) й за допомогою вбудованої функції ЛИНЕЙН().

Для цього необхідно виконати наступний алгоритм дій:

1. На робочому аркуші формується таблиця, що містить вихідні дані.

2. Виділяється блок (5 стрічок х 2 стовпчики) з метою визначення результатів регресійної статистики.

3. За допомогою режиму Вставка викликається функція ЛИНЕЙН() (рис. 6.2), заповнюються аргументи функції та натискається комбінацію клавіш <CTRL>+<SHIFT>+<ENTER>.

 

Рис. 6.2. Діалогове вікно функції ЛИНЕЙН()

Синтаксис функції ЛИНЕЙН():

Изв_знач_y — множина значень залежної змінної (результативної ознаки). Якщо масив изв_знач_y має один стовпчик, то кожен стовпчик масиву изв_знач_x інтерпретується як окрема змінна. Якщо масив изв_знач_y має одну строку, то кожна строка масиву изв_знач_x інтерпретується як окрема змінна.

Изв_знач_x — множина значень незалежної змінної (пояснюючої змінної, факторної ознаки). Зазначений аргумент є необов’язковим. Масив изв_знач_x може складатися з одного або декількох множин змінних. Якщо використувається тільки одна змінна, то аргументи изв_знач_y та изв_знач_x можуть бути діапазонами будь-якої формы, але обов’язково мати однаковий розмір (кількість значень повинна відповідати кількості значень ). Якщо використовується більш ніж одна змінна, то аргумент изв_знач_y повинен бути діапазоном клітин у вигляді однієї строки або у вигляді одного стовпчика (вектор значень). Якщо аргумент изв_знач_x не заповнюється, то передбачається, що це масив {1;2;3;...} такого ж розміру, що й изв_знач_y.

Константа — необов’язкове логічне значення, яке вказує чи потрібно, щоб вільний член рівняння регресії дорівнював нулю. Якщо аргумент константа має значення ИСТИНА або не заповнеено, то вільний член рівняння обчислюється належним чином. В іншому випадку вільний член рівняння дорівнює нулю і коефіцієнт обчислюється таким чином, щоб виконувалося співвідношення .

Стат — логічне значення, яке вказує, чи потрібно вивести додаткову статистику регресії. Якщо аргумент стат має значення ИСТИНА, то функція ЛИНЕЙН повертає додаткові статистичні показники отриманого рівняння. Якщо аргумент стат має значення ЛОЖЬ або не заповнено, то функція ЛИНЕЙН повертає лише коефіцієнти рівняння регресії.

Додаткова регресійна статистика функції ЛИНЕЙН() виводиться відповідно до схеми, що подано в табл. 6.1.

 

Таблиця 6.1

Схема регресійної статистики функції ЛИНЕЙН()

Значення коефіцієнту	Значення коефіцієнту
Середньоквадратичне відхилення	Середньоквадратичне відхилення
Коефіцієнт детермінації	Середньоквадратичне відхилення
- статистика	Кількість ступенів свободи
Регресійна сума квадратів	Залишкова сума квадратів

 

Поточні результати представлено на рис. 6.3.

 

Рис. 6.3. Результат виконання функції ЛИНЕЙН()

Таким чином, теоретичне рівняння лінійної регресії являє собою: . Оскільки , то регресія невід’ємна, тобто збільшення значення веде до збільшення значення . Коефіцієнт регресії свідчить, що при збільшенні ціни на 1 одиницю попит збільшиться на одиниць. Коефіцієнт детермінації , тобто варіація результата на 99% пояснюється варіацією фактора , на долю не врахованих факторів залишається лише 1%.

Отримані результати моделювання лінійного зв’язку можна встановити використовуючи графічне зображення. Для цього слід застосувати режим Добавить линию тренда, обрати лінійний тип та встановити показ величини коефіцієнта детермінації та рівняння на діаграмі (рис. 6.4).

Рис. 6.4. Застосування режиму Добавить линию тренда

 

Для визначення якості обраної моделі доцільно обчислити середню помилку апроксимації (рис. 6.5):

 

%,

 

тобто в середньому обчислені значення відхиляються від фактичних на 3,21%, що вказує на високу якість рівняння лінійної моделі.

Рис. 6.5. Допоміжні дані для обчислення середньої помилки апроксимації рівняння лінійної моделі

Припустимо, що модель задачі – показникова: .

Для побудови рівняння виконаємо лінеаризацію за допомогою логарифмування: ; , звідки маємо: , де , , .

Отже, здійснено перетворення показникової моделі до лінійної, параметри якої можна обчислити за допомогою функції ЛИНЕЙН(). Виконані обчислення наведено на рис. 6.6.

На підставі проведених обчислень встановлено, що ; . Теоретичне рівняння лінійної регресії: .

 

Рис. 6.6. Допоміжні обчислення параметрів рівняння

Показникової моделі

Виконаємо потенціювання отриманого рівняння та запишемо його у вигляді показникової функції:

 

.

 

Для визначення якості показникової моделі на підставі аналогічних до наведених вище міркувань обчислено середню помилку апроксимації: , тобто в середньому обчислені значення відхиляються від фактичних на 4,65%.

Модель зв’язку на основі показникової функції (експоненти) можна отримати використовуючи графічні можливості середовища Excel.

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

 

 

Рис. 6.7. Моделювання експоненціального тренду за допомогою режиму Добавить линию тренда

 

Припустимо, що модель нашої задачі – степенева: . Перетворимо до лінійного виду за допомогою логарифмування: . Отже, , де , , , . Виконані обчислення наведено на рис. 6.8.

 

 

Рис.6.8. Допоміжні обчислення параметрів рівняння степеневої моделі

Отримано, що ; .

Звідси теоретичне рівняння регресії: .

Виконаємо потенціювання отриманого рівняння та запишемо його у вигляді степеневої функції:

 

.

 

Для визначення якості обраної моделі обчислено середню помилку апроксимації:

,

тобто в середньому обчислені значення відхиляються від фактичних на 0,64%.

Модель зв’язку на основі степенової функції можна отримати використовуючи графічне зображення. Для цього слід застосувати режим Добавить линию тренда, обрати степеневий тип і визначити показ величини коефіцієнта детермінації та рівняння на діаграмі (рис. 6.9).

 

Рис. 6.9. Моделювання степеневого тренду за допомогою режиму Добавить линию тренда

 

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

6.5. Завдання для самостійної роботи

Побудувати лінійну, степеневу та показникову економетричні моделі за даними, що наведені в таблиці. Оцінити парметри моделі. Зробити економічні висновки.

 

Y	X	Y	X	Y	X	Y	X	Y	X
6,5	22,65	6,6	25,39	8,1	26,24	8,1	25,38	8,8	28,35
6,1	22,64	4,3	26,42	6,3	27,48	6,5	25,83	7,8	29,14
8,0	22,49	5,2	26,23	6,2	27,24	6,3	25,74	9,3	30,53
8,0	21,52	7,4	25,40	5,6	26,55	6,5	26,65	9,4	34,22
6,4	23,24	5,8	26,96	6,2	29,4	7,5	30,47	9,9	37,44
9,1	21,81	6,4	26,54	6,5	26,18	7,5	28,93	10,1	35,18
6,2	21,86	5,7	25,69	6,6	25,18	7,5	29,18	8,8	31,55
6,0	20,97	6,9	27,84	5,5	25,71	9,8	30,56	8,8	32,16
5,9	22,50	6,6	27,09	6,0	27,16	9,0	30,18	11,6	35,45
6,8	23,32	8,0	29,19	6,5	25,82	8,5	32,17	10,4	41,69
6,0	24,13	8,7	26,74	6,8	27,17	9,6	30,96	10,1	39,11
6,6	22,25	8,0	26,84	6,0	25,61	7,6	28,33	9,0	32,15

 

 

Y	X	Y	X	Y	X	Y	X	Y	X
12,5	212,65	60,6	205,39	82,1	26,24	8,1	125,38	8,8	428,35
16,1	212,64	40,3	206,42	62,3	27,48	6,5	125,83	7,8	429,14
18,0	212,49	50,2	206,23	62,2	27,24	6,3	125,74	9,3	430,53
18,0	211,52	70,4	205,40	52,6	26,55	6,5	126,65	9,4	444,22
16,4	213,24	50,8	206,96	62,2	29,4	7,5	130,47	9,9	437,44
19,1	211,81	60,4	206,54	62,5	26,18	7,4	128,93	10,1	435,18
16,2	211,86	50,7	205,69	62,6	25,18	7,5	129,18	8,8	431,55
16,0	201,97	60,9	207,84	52,5	25,71	9,8	130,56	8,1	432,16
15,9	212,50	60,6	207,09	62,0	27,16	9,0	130,18	11,6	435,45
16,8	213,32	80,0	209,19	62,5	25,82	8,5	132,17	10,4	441,69
16,0	214,13	80,7	206,74	62,8	27,17	9,6	130,96	10,1	439,11
16,6	212,25	80,0	206,84	62,0	25,61	7,6	128,33	9,0	432,15

 

Y	X	Y	X	Y	X	Y	X	Y	X
			25,39		26,24		25,38		28,35
	22,64		26,42		27,48		25,83		29,14
	22,49		26,23		27,24		25,74		30,53
	21,52		25,40		26,55		26,65		34,22
	23,24		26,96		29,4		30,47		37,44
	21,81		26,54		26,18		28,93		35,18
	21,86		25,69		25,18		29,18		31,55
	20,97		27,84		25,71		30,56		32,16
	22,50		27,09		27,16		30,18		35,45
	23,32		29,19		25,82		32,17		41,69
	24,13		26,74		27,17		30,96		39,11
	22,25		26,84		25,61		28,33		32,15

 

Y	X	Y	X	Y	X	Y	X	Y	X
102,5	212,65	610,6	205,39	802,1	26,24	87,1	125,38	80,8	428,35
106,1	212,64	410,3	206,42	602,3	27,48	67,5	125,83	70,8	429,14
108,0	212,49	510,2	206,23	602,2	27,24	67,3	125,74	90,3	430,53
108,0	211,52	710,4	205,40	502,6	26,55	67,5	126,65	90,4	444,22
106,4	213,24	510,8	206,96	602,2	29,4	77,5	130,47	90,9	437,44
109,1	211,81	610,4	206,54	602,5	26,18	77,4	128,93	100,1	435,18
106,2	211,86	510,7	205,69	602,6	25,18	77,5	129,18	80,8	431,55
106,0	201,97	610,9	207,84	502,5	25,71	97,8	130,56	80,1	432,16
105,9	212,50	610,6	207,09	602,0	27,16	97,0	130,18	101,6	435,45
106,8	213,32	810,0	209,19	602,5	25,82	87,5	132,17	100,4	441,69
106,0	214,13	810,7	206,74	602,8	27,17	97,6	130,96	100,1	439,11
106,6	212,25	810,0	206,84	602,0	25,61	77,6	128,33	90,0	432,15

 

Y	X	Y	X	Y	X	Y	X	Y	X
			215,39		206,24		25,38		218,35
	22,64		216,42		207,48		25,83		219,14
	22,49		216,23		207,24		25,74		310,53
	21,52		215,40		206,55		26,65		314,22
	23,24		216,96		209,4		30,47		317,44
	21,81		216,54		206,18		28,93		315,18
	21,86		215,69		205,18		29,18		311,55
	20,97		217,84		205,71		30,56		312,16
	22,50		217,09		207,16		30,18		315,45
	23,32		219,19		205,82		32,17		411,69
	24,13		216,74		207,17		30,96		319,11
	22,25		216,84		205,61		28,33		312,15

 

6.6. Контрольні запитання

1. Моделі, що відносяться до категорії економетричних.

2. Означення залежних (ендогенних) та незалежних (екзогенних) факторів.

3. Задачі регресійного аналізу та їх відмінність від задач кореляційного аналізу.

4. Етапи побудови економетричних моделей.

5. Поняття про специфікацію економетричної моделі.

6. Помилки специфікації та їх вплив на оцінки параметрів моделі.

7. Умови, за якими можливо використання метода найменших квадратів.

8. Властивості статистичних оцінок параметрів економетричної моделі, які отримані за методом МНК.