Тема: Побудова та аналіз
однофакторної нелінійної моделі залежності між фактором та показником.
Мета заняття: Задана вибірка, яка отримана для показника Y і фактора X.
Необхідно:
ü на основі статистичних даних показника Y і фактора X знайти
оцінки параметрів лінії регресії, припускаючи, що стохастична залежність між
фактором X і показником Y має вигляд: Y=a + b;
ü перевірити
адекватність отриманої моделі статистичним даним, використовуючи критерій
Фішера з надійністю Р=0,95;
ü у випадку адекватності отриманої моделі експериментальним
даним з заданою надійністю знайти:
- з надійністю Р=0,95 довірчу зону базисних даних;
- точкову оцінку прогнозу;
- з надійністю Р=0,95 інтервальну оцінку прогнозу;
- оцінки коефіцієнтів еластичності для базисних
значень і прогнозу;
- оцінку індексу кореляції.
ü Побудувати графіки:
- фактичних даних;
- лінії регресії й довірчу зону;
- лінії еластичності.
Хід роботи
1.
Завантажити програму EXCEL.
2.
Сформувати таблицю вихідних даних, заповнивши діапазон
комірок А3:С:15 (рис. 1).
3.
Виконати розрахунки:
- у комірці В16
за допомогою вбудованої функції СЧЕТЗ знайти об’єм вибірки n.
-
розрахувати значення квадратного кореня з х
(x1i= ), використавши при цьому
вбудовану функцію КОРЕНЬ, обчислення розмістити в діапазоні D3:D15 (застосувати процедуру
автозаполнення див. лаб.1);
-
розрахувати середнє значення величин y, x1, використовуючи вбудовану функцію СРЗНАЧ, результати
розмістити у комірки D19 й D18 відповідно;
- розрахувати
значення добутку y1i*х1i, ввівши в комірку Е3 формулу
Е3:=B3*D3; для одержання інших розрахункових значень, скопіювати формулу
третього рядка в інші комірки блоку Е4:Е15;
-
розрахувати значення х1i^2, увівши в комірку F3 формулу F3:=D3^2; для одержання інших
розрахункових значень, скопіювати формулу третього рядка в інші комірки блоку
F4:F15;
-
розрахувати суму величин y, x, х1= , y1*х11, х1^2,
використовуючи вбудовану функцію СУММ або Автосумму, результати
розмістити у комірки В17,С17,D17,Е17 й F17 відповідно.
4.
Для оцінки параметра , де
, ввести в комірку В18 формулу В18:=
(B16*E17-B17*D17)/(B16*F17-D17^2).
5.
Для оцінки параметра , ввести в комірку В19 формулу В19:= D19-B18*D18.
6.
Записати модель у блоці А22:F22.
7.
Обчислити розрахункове значення показника yрозр по формулі . Для цього у комірку G3 ввести формулу G3:= $B$18*D3+$B$19,
використовуючи абсолютне посилання для комірок B18 й B19. Використовуючи
операцію автозаполнення, скопіювати отриману формулу у діапазон G4:G15.
8.
Розрахувати суму величин yрозр,
використовуючи вбудовану функцію СУММ або Автосумму, результат
розмістити у комірці G17.
Так як
математичне очікування відхилень фактичних даних від розрахункових дорівнює
нулю, то при правильному виконанні розрахунків значення комірок В17 й G17
повинні збігатися.
9.
Для визначення розрахункового значення критерію Фішера,
оцінки довірчої зони базисних даних, оцінки довірчого інтервалу й оцінки
прогнозу обчислити значення ,
,
і розмістити їх відповідно
у блоках Н3:Н15, I3:I15 й J3:J15, а їх суми у блоці Н17:J17 (формули ввести
самостійно).
10.Обчислити значення (де m – число
факторів моделі, тобто для нашого випадку m=1), ввівши у комірку D20
формулу D20:= КОРЕНЬ(H17/(B16-2)).
11.У комірку I20 внести табличне
значення t(0,95;11)=2,18 (табличне значення
критерію Стьюдента, можна обчислити використовуючи вбудовану функцію СТЬЮДРАСПОБР
(ймовірність a=0,05, число ступенів
вільності k=n-2=13-2=11)).
12.Обчислити значення ∆ (i=1,…,n), ввівши в
комірку K3 формулу K3:= I$20*D$20*КОРЕНЬ(1/B$16+J3/J$17), використовуючи
абсолютне посилання для комірок I20, D20, B16 й J17. Використовуючи операцію
автозаполнення, скопіювати отриману формулу у діапазон К4:К15.
13.Обчислити значення і розмістити їх
відповідно у блоках L3:L15 і М3:М15 (формули ввести самостійно).
14.Розрахувати коефіцієнт еластичності
за формулою: , ввівши у комірку N3 формулу N3: =$B$18*D3/(2*G3), де
використано абсолютне посилання для комірки В18. Скопіювати отриману формулу у
діапазон N4:N15.
15.Обчислити коефіцієнт
кореляції між x1 та y, використовуючи вбудовану функцію КОРРЕЛ,
розмістивши результат обчислень у комірці L18.
16.Обчислити коефіцієнт
детермінації R2 ( у випадку парної регресії він
збігається із квадратом коефіцієнта кореляції), ввівши в комірку N18
формулу N18:= L18^2.
Для перевірки адекватності
(тобто ступеня відповідності побудованого рівняння регресії наявним
статистичним даним) застосувати критерій Фішера:
17.Обчислити розрахункове значення критерію Фішера за формулою: . Для цього ввести у
комірку L20 формулу L20: = N18/(1-N18)*12.
18.У комірці L19 обчислимо табличне
значення критерію Фішера, використовуючи
вбудовану функцію FРАСПОБР (ймовірність a=0,05,
число ступенів вільності k1=m=1 й k2=n-m-1=13-2=11).
19.Порівнявши отримані результати, можна
зробити висновок про адекватність моделі експериментальним даним.
Проведемо прогнозування за отриманою моделью.
20.
Значення прогнозу
фактору занести у комірку С16, у комірці D16 обчислюється значення Х1 прогнозне, а в комірці G16
- Y прогнозне. Оцінку довірчого пів
інтервалу для прогнозу обчислимо у комірці К16, а межі довірчого інтервалу
перебувають відповідно у комірках L16 і М16.
21.Після проведених дій робочий лист EXEL виглядає як на рис.1.
Для наочного уявлення розрахунків побудувати графіки
статистичних даних, довірчого інтервалу для базисних даних і прогнозу, а також
графік еластичності (див. лаб.1).
22.Проаналізувати отримані графіки
(рис.2).
23.Підвести підсумки лабораторної роботи
і зробити висновки.
24.Зберегти книгу у своїй робочій папці
під ім'ям Лаб.2.
Висновки:
1.
Оскільки Fроз>Fтаб, то з надійністю Р=0,95
побудовану економетричну модель можна
вважати адекватною експериментальним даним і на підставі прийнятої моделі можна
проводити економічний аналіз.
2.
Для значення фактора Хр=15 середнє значення прогнозу
показника Yр=11,08. З надійністю р=0,95 прогноз показника буде набувати
значення в інтервалі (10,27;11,89).
3.
Для прогнозу зміна фактора на 1% викличе зміну показника в
середньому на 0,35%.
Завдання до лабораторної роботи №2
1 |
2 |
3 |
4 |
5 |
|||||
X |
Y |
X |
Y |
X |
Y |
X |
Y |
X |
Y |
2 |
5,857 |
3 |
6,959 |
4 |
7,341 |
5 |
8,050 |
6 |
8,076 |
3 |
6,959 |
4 |
7,341 |
5 |
8,050 |
6 |
8,076 |
7 |
9,261 |
4 |
7,341 |
5 |
8,050 |
6 |
8,076 |
7 |
9,261 |
8 |
9,295 |
5 |
8,050 |
6 |
8,076 |
7 |
9,261 |
8 |
9,295 |
9 |
9,622 |
6 |
8,076 |
7 |
9,261 |
8 |
9,295 |
9 |
9,622 |
10 |
9,482 |
7 |
9,261 |
8 |
9,295 |
9 |
9,622 |
10 |
9,482 |
11 |
9,609 |
8 |
9,295 |
9 |
9,622 |
10 |
9,482 |
11 |
9,609 |
12 |
9,909 |
9 |
9,622 |
10 |
9,482 |
11 |
9,609 |
12 |
9,909 |
13 |
10,436 |
10 |
9,482 |
11 |
9,609 |
12 |
9,909 |
13 |
10,436 |
14 |
11,254 |
11 |
9,609 |
12 |
9,909 |
13 |
10,436 |
14 |
11,254 |
15 |
11,987 |
12 |
9,909 |
13 |
10,436 |
14 |
11,254 |
15 |
11,987 |
16 |
12,457 |
13 |
10,436 |
14 |
11,254 |
15 |
11,987 |
16 |
12,457 |
17 |
12,932 |
14 |
11,254 |
15 |
11,987 |
16 |
12,457 |
17 |
12,932 |
18 |
13,412 |
Хр= |
16 |
Хр= |
17 |
Хр= |
18 |
Хр= |
19 |
Хр= |
20 |
6 |
7 |
8 |
9 |
10 |
|||||
X |
Y |
X |
Y |
X |
Y |
X |
Y |
X |
Y |
7 |
9,261 |
8 |
9,295 |
9 |
9,622 |
10 |
9,482 |
11 |
9,609 |
8 |
9,295 |
9 |
9,622 |
10 |
9,482 |
11 |
9,609 |
12 |
9,909 |
9 |
9,622 |
10 |
9,482 |
11 |
9,609 |
12 |
9,909 |
13 |
10,436 |
10 |
9,482 |
11 |
9,609 |
12 |
9,909 |
13 |
10,436 |
14 |
11,254 |
11 |
9,609 |
12 |
9,909 |
13 |
10,436 |
14 |
11,254 |
15 |
11,987 |
12 |
9,909 |
13 |
10,436 |
14 |
11,254 |
15 |
11,987 |
16 |
12,457 |
13 |
10,436 |
14 |
11,254 |
15 |
11,987 |
16 |
12,457 |
17 |
12,932 |
14 |
11,254 |
15 |
11,987 |
16 |
12,457 |
17 |
12,932 |
18 |
13,412 |
15 |
11,987 |
16 |
12,457 |
17 |
12,932 |
18 |
13,412 |
19 |
14,015 |
16 |
12,457 |
17 |
12,932 |
18 |
13,412 |
19 |
14,015 |
20 |
14,952 |
17 |
12,932 |
18 |
13,412 |
19 |
14,015 |
20 |
14,952 |
21 |
15,426 |
18 |
13,412 |
19 |
14,015 |
20 |
14,952 |
21 |
15,426 |
22 |
16,015 |
19 |
14,015 |
20 |
14,952 |
21 |
15,426 |
22 |
16,015 |
23 |
16,798 |
Хр= |
21 |
Хр= |
22 |
Хр= |
23 |
Хр= |
24 |
Хр= |
25 |