ЛАБОРАТОРНА РОБОТА №2

 

Тема: Побудова та аналіз однофакторної нелінійної моделі залежності між фактором та показником.

Мета заняття: Задана вибірка, яка отримана для показника 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 відповідно;

- розрахувати значення добутку y1i1i, ввівши в комірку Е3 формулу Е3:=B3*D3; для одержання інших розрахункових значень, скопіювати формулу третього рядка в інші комірки блоку Е4:Е15;

- розрахувати значення х1i^2, увівши в комірку F3 формулу F3:=D3^2; для одержання інших розрахункових значень, скопіювати формулу третього рядка в інші комірки блоку F4:F15; 

- розрахувати суму величин y, x, х1= , y111, х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