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

 

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

Мета заняття: Задана вибірка, отримана для факторів X1,  Х2 , Х3  і показника Y. Необхідно:

ü знайти кореляційну матрицю;

ü використовуючи –критерій з надійністю Р=0,95 перевірити систему факторів на існування загальної мультиколінеарності;

ü у випадку існування загальної мультиколінеарності, використовуючи t-статистику виявити пари мультиколінеарних факторів; якщо такі пари існують, виключити один із цих факторів;

ü  знайти оцінки параметрів  лінійної регресії, результат отриманих оцінок перевірити, використовуючи сервіс: статистика/линейн;

ü використовуючи F-критерій з надійністю Р=0,95, перевiрити статистичну значущість коефіцієнта детермінації (оцінити адекватність прийнятої моделі статистичним даним ).

Якщо математична модель із заданою надійністю адекватна експериментальним даним, то:

ü використовуючи t-критерій, з надійністю Р=0,95 оцінити значущість параметрів регресії;

ü знайти значення прогнозу показника для заданих значень факторів;

ü його довірчий інтервал з надійністю Р=0,95;

ü частинні коефіцієнти еластичності для точки прогнозу.

 

Хід роботи

1.      Завантажити програму EXCEL.

2.      Вихідні даних факторів розміщуємо у блоці В3:В17, а показника в стовпці Е3:Е17 (рис.1).

Перевіримо систему факторів на мультиколінеарність, зробивши  розрахунки згідно алгоритму Фаррара-Глобера:

3.      Знайдемо кореляційну матрицю та розмістимо її у блоці А23:С25.

Її можна знайти нормалізуючи статистичні дані за формулою:  ,  де n - число розглянутих періодів, m -число факторів, - середнє значення фактора Хi, - середньоквадратичне відхилення фактора Хi.

 При нормалізації статистичних даних використаємо вбудовану функцію СРЗНАЧ (блок В21:Е21) ; СТАНДОТКЛОНП (блок I27:I29) і вбудовану функцію КОРЕНЬ для розрахунку у комірці А21 .


Нормалізовані статистичні дані формуємо у блоці F3:Н17 (рис.1).

(Слід зазначити, що нормалізовані статистичні дані можна знайти за формулою: ).

Для розрахунків елементів кореляційної матриці використовуємо вбудовану функцію СУММПРОИЗВ.  Елемент кореляційної матриці, який знаходиться у i-му рядку та j-му стовпці, знайти  таким чином: СУММПРОИЗВ стовпця нормалізованих статистичних даних i-го фактора й стовпця нормалізованих статистичних даних j-го фактора.

  (Кореляційну матрицю можна знайти, використовуючи послідовно  вбудовані функції: ТРАНС (блок матриці) та МУМНОЖ (блок даних першої матриці; блок даних другої матриці) за формулою , де - кореляційна матриця,  - матриця нормалізованих статистичних даних блоку F3:Н17, - транспонована матриця стосовно матриці .

Або не нормалізуючи статистичні дані, а використовуючи вбудовану функцію КОРРЕЛ (коефіцієнт кореляції між двома однорідними множинами даних.)

4.      Знайдемо визначник матриці |К| і запишемо результат у комірці Е24, використовуючи вбудовану функцію МОПРЕД.

5.      Обчислимо розрахункове значення критерію  за формулою: ,  де n – об’єм вибірки (n=15), m - число факторів моделі (m=3) та розмістимо результат у комірці Е25:=-(14-11/6) *LN(E24).

6.      Знайдемо табличне (критичне) значення критерію і запишемо результат у комірці E26, використовуючи вбудовану функцію ХИ2ОБР (імовірність a=0,05, число ступенів вільності k=m(m-1)/2=3(3-1)/2=3).

7.      Зробимо висновки про наявність загальної мультиколінеарності (рис.2).

 

 

 

 

 

 

Визначимо мультиколінеарні пари факторів за допомогою t-статистики (критерію Стьюдента):

8.      Знайдемо матрицю С, обернену до кореляційної, розмістивши результат у блоці А27:С29.

9.      Розрахуємо частинні коефіцієнти кореляції між парами факторів за формулою:  ,  де сkj - елемент матриці С, що лежить у к-му рядку й j-ому стовпці, cкк  й cjj  - діагональні елементи матриці С, записавши отримані результати у комірки Е27:Е29 відповідно.

10.  Обчислимо розрахункові значення t-статистик за формулою:  , де rkj - частинні коефіцієнти кореляції між парами факторів та запишемо отримані результати у комірки G27:G29 відповідно.

11.  У комірці G26 обчислимо табличне (критичне) значення t-статистики, з використанням вбудованої функції СТЬЮДРАСПОБР (імовірність a=0,05, число ступенів вільності k=n-m-1=15-4=11).

12.  Зробимо висновок про наявність мультиколінеарності між парами факторів (рис.2).

13.  Виключимо один з факторів, між якими існує  мультиколінеарність ( у нашому випадку Х3). Для цього у блоці F23:G24 знайдемо кореляційну матрицю факторів, що залишилися. Обернену матрицю знайдемо у блоці H23:I24 (рис.2), а визначник цієї матриці обчислимо у комірці В38, розрахункове значення Xi2- у комірці В39, а табличне (критичне) значення – у комірці В40 (рис.3).


Порівняємо отримані результати та зробимо висновок про наявність мультиколінеарності між факторами.

Припустимо, що між показником Y і факторами X1 й X2 існує лінійна залежність Y=a0+ a1 X1+ a2 X2. Знайдемо оцінки параметрів, використовуючи матричні операції. Для оцінки параметрів вектора  використаємо формулу .

Порядок знаходження оцінок параметрів моделі.

14.  Знайдемо транспоновану матрицю у блоці А31:О33 по відношенню до матриці  у блоці А3:С17, використовуючи вбудовану функцію ТРАНСП.

15.  Знайдемо добуток матриць  у блоці А35:С37, використовуючи вбудовану функцію МУМНОЖ (блок даних  першої матриці А31:О33; блок даних  другої матриці А3:С17).

16.  Знайдемо обернену матрицю  у блоці D35:F37, використовуючи вбудовану функцію МОБР.

17.  Знайдемо добуток матриць  у блоці Н53:Н37, використовуючи вбудовану функцію МУМНОЖ (А31:О33; Е3:Е17).

18.  Знайдемо оцінки вектора  у блоці G39:G41, використовуючи вбудовану функцію МУМНОЖ (блок дані матриці (D35:F37), блок дані матриці  (Н53:Н37)) (рис.3).

 

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

Порядок знаходження оцінок параметрів регресії з використанням вбудованої функції ЛИНЕЙН.

1)                Відмічаємо блок, де мають знаходитись розрахункові дані: ширина блоку дорівнює числу оцінюваних параметрів, а висота п'яти рядкам.

2)                Відкриваємо діалогове вікно Майстер функцій, вибираємо функцію ЛИНЕЙН і натискаємо кнопку Далі> для переходу в наступне діалогове вікно.

3)                У другому діалоговому вікні вводимо: у перший рядок (у перше поле) блок даних показника, вказуючи діапазон комірок Е3:Е17 або ім'я блоку даних; у другий – блок даних факторів В3:с17 або ім'я блоку; у третій – вводитися слово ИСТИНА, якщо а0 не дорівнює нулю, і слово ЛОЖЬ, якщо а0  дорівнює нулю; у четвертий - вводитися слово ИСТИНА, якщо необхідно знайти не тільки параметри лінії регресії, а й додаткову регресійну статистику. Якщо необхідно знайти тільки параметри лінії регресії, то вводимо слово ЛОЖЬ і натискаємо кнопку Готово для одержання розрахункових даних.

4)                Для того, щоб у блоці розрахункових даних було видно не тільки значення першої комірки, натискаємо клавішу F2, потім Ctrl+Shift+Enter.

Таблиця розрахункових значень додаткової регресійної статистики (блок С39:Е43) має вигляд (рис.3):

а2

а1

а0

r2

S

# Н/Д

Fr1

К

# Н/Д

# Н/Д

У першому рядку справа наліво знаходяться оцінки параметрів множинної лінійної регресії відповідно а0, а1,  а2.

У другому рядку справа наліво знаходяться середні квадратичні відхилення оцінок параметрів , ,  .

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

У четвертому рядку в першій комірці знаходиться розрахункове значення F-статистики, а в другий комірці число вільності.

У п'ятому рядку в першій комірці знаходиться сума квадратів відхилень розрахункових значень показника від його середнього значення, а в другий – сума квадратів залишків.

19.   Запишемо отриману модель у блоці А51:F51 (рис.3).

20.  Знайдемо розрахункові значення показника у стовпці I3:I17, у стовпці J3:J17 – його відхилення від експериментальних даних, у стовпці К3:К17 - квадрати відхилень, а у комірках I19, J19 і К19 їх суми відповідно (рис.1).

Для перевірки адекватності отриманої моделі застосувати критерій Фішера.

21.  Розрахункове значення  критерію Фішера знаходиться у комірці D46.

22.   Обчислимо табличне значення  критерію Фішера у комірці D47, використовуючи вбудовану функцію FРАСПОБР (ймовірність a=0,05, число ступенів вільності k1=m=2 й k2=n-m-1=15-2-1=12) (рис.3).

23.  Порівняємо отримані результати та зробимо висновок про адекватність отриманої моделі.

Розглянемо значущість параметрів моделі.

24.  Розрахуємо t-статистику кожного із параметрів за формулою: , де ,

Si – середньоквадратичне відхилення статистичних даних від розрахункових ( знайдемо у комірці F21) (рис.1), zij – діагональний елемент матриці Z=.

Розрахункові дані для  запишемо у блоці В44:В46, а для tip  у блоці В47:В49 (рис.3).

25.  У комірці В50 обчислимо табличне (критичне) значення t-статистики, використовуючи вбудовану функцію СТЬЮДРАСПОБР (імовірність a=0,05, число ступенів вільності k=n-m-1=15-3=12).

26.  Порівняємо значення  tрозр та tкр  та зробимо висновок про вплив факторів Х1, Х2 на показник Y.

27.  Точкову оцінку значення прогнозу для Х1=9, Х2=30 знайдемо у комірці I18 (рис.1).

28.  Знайдемо довірчий інтервал цієї оцінки у стовбці Н48:Н49 за формулою,, де (рис.3).

 

Алгоритм розрахунку довірчого інтервалу прогнозу:

1) Використовуючи вбудовану матричну функцію МУМНОЖ (блок вектора  А18:С18; блок матриці [Z] D35:F37) знайти добуток матриць  (блок Н44:J44);

2) Використовуючи вбудовану функцію СУММПРОИЗВ(Н44:J44; А18:С18), знайти у комірці Н45 значення .

3) Використовуючи вбудовану функцію КОРЕНЬ, знайти в комірці Н46 значення Syp, а потім у комірці Н47 -  t* Syp .

4) Знайти довірчі межі прогнозу в блоці Н48:Н49 (рис.3).

29.  Знайдемо частинні коефіцієнти еластичності для прогнозу за формулами ,  у комірках В42, В43 відповідно (рис.3). Зробимо висновок.

30.  Підвести підсумки лабораторної роботи і зробити висновки.

31.  Зберегти книгу у своїй робочій папці під ім'ям Лаб.6.

 

Висновки:

1.      Між факторами Х1 і Х3 існує мультіколінеарність, тому один з факторів (Х3) не включаємо в множинну лінійну регресію.

2.      Оскільки Fроз>Fтаб, то з надійністю Р=0,95 математичну модель  можна вважати адекватною експериментальним даним і на підставі прийнятої моделі можна проводити економічний аналіз.

3.      З надійністю р=0,95 можна вважати, що вплив факторів Х1 і Х2 на показник Y значний.

4.      Значення прогнозу показника  з надійністю р=0,95 буде знаходитися у проміжку (70,85; 76,49).

5.      При зміні факторів у точці прогнозу Хна 1% показник Y зміниться на 0,15% при незмінних значеннях фактора Х.

6.      При зміні факторів у точці прогнозу Хна 1% показник Y зміниться на 0,81% при незмінних значеннях фактора Х.

 

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

Варіант 1

Варіант 2

Варіант 3

X1

Х2

Х3

Y

X1

Х2

Х3

Y

X1

Х2

Х3

Y

2,31

10,1

6,32

7,63

2,12

9,97

6,28

7,45

2,41

10,25

6,32

7,73

4,87

11,7

7,73

10,70

4,40

11,43

7,64

10,48

4,78

11,95

7,79

10,94

6,17

13,9

8,48

11,53

6,16

13,61

8,25

11,25

6,26

13,87

8,57

11,82

8,7

14,4

8,70

13,40

8,69

14,28

8,81

13,33

8,95

14,52

8,83

13,59

10,7

15,1

10,50

17,02

10,47

14,93

10,21

16,90

10,75

15,18

10,5

17,15

13,5

17,1

10,52

18,75

13,41

17,04

10,43

18,57

13,57

17,41

10,59

18,81

16,2

18,9

11,68

21,14

15,98

18,82

11,54

20,91

16,43

19,01

11,83

21,26

18,3

2,3

13,77

3,37

18,24

20,30

13,73

23,32

18,55

20,52

13,83

23,38

21,2

21,7

13,70

27,45

20,89

21,48

13,64

27,16

21,45

22,02

13,97

27,62

22,7

2,4

14,43

27,13

22,66

22,22

14,41

27,00

22,75

22,3

14,55

27,18

25,1

2,5

14,07

29,61

24,91

22,41

13,98

29,59

25,19

22,64

14,17

29,87

26,1

24,7

16,46

32,52

26,03

24,45

16,45

32,24

26,25

24,89

16,64

32,64

27,5

24,8

15,02

31,80

27,25

24,75

14,83

31,71

27,63

25,02

15,07

32,01

29,9

25,0

15,27

35,18

29,74

24,89

1,06

35,10

30,16

2,19

15,38

35,25

32,1

26,0

15,66

37,07

31,80

25,95

15,61

36,77

32,20

26,15

15,72

37,14

35,8

28,9

17,4

?

35,57

28,80

17,39

?

36,00

29,02

17,56

?

 

Варіант 4

Варіант 5

Варіант 6

X1

Х2

Х3

Y

X1

Х2

Х3

Y

X1

Х2

Х3

Y

2,37

10,27

6,40

7,73

2,25

9,90

6,09

7,79

2,61

10,35

6,61

7,72

4,77

11,87

7,88

10,85

4,42

11,54

7,49

10,64

4,89

11,78

7,94

10,77

6,24

13,65

8,50

11,4

6,08

13,73

8,46

11,44

6,24

14,09

8,62

11,86

8,70

14,62

8,86

13,52

8,65

14,26

8,59

13,24

9,01

14,64

8,83

13,73

10,79

15,28

10,1

17,13

10,64

14,91

10,43

16,99

10,79

15,17

10,68

17,04

13,60

17,29

10,53

18,75

13,29

17,02

10,52

16,7

13,53

17,42

10,66

18,80

16,31

19,04

11,74

21,15

15,95

18,84

11,65

1,07

16,32

19,24

11,78

21,28

18,40

20,45

13,96

23,49

18,25

20,06

13,55

23,23

18,60

20,60

13,78

23,70

21,25

21,94

13,86

27,50

21,10

21,71

13,67

27,37

21,48

22,04

13,74

27,63

22,87

22,

14,60

27,16

22,67

22,31

14,33

27,12

23,02

22,69

14,56

27,45

25,15

22,6

14,24

29,73

24,99

22,39

13,95

29,42

52,17

22,65

14,09

29,71

26,27

24,79

16,59

32,71

26,00

24,50

16,34

32,43

26,40

24,83

16,66

32,80

27,0

24,82

15,03

31,83

27,37

24,76

14,81

31,74

27,62

24,82

15,12

31,81

30,00

25,11

15,34

35,18

29,75

24,99

15,13

35,16

30,19

25,17

15,42

35,22

32,25

26,11

15,84

37,12

31,87

25,94

15,46

37,07

32,25

26,22

15,77

37,26

35,96

29,09

17,48

?

35,71

28,83

17,25

?

35,97

29,15

17,77

?

Варіант 7

Варіант 8

Варіант 9

X1

Х2

Х3

Y

X1

Х2

Х3

Y

X1

Х2

Х3

Y

2,61

10,32

6,71

7,95

2,40

11,27

6,78

8,61

1,43

9,18

6,17

7,29

4,97

11,96

8,14

10,75

5,66

12,48

8,49

11,33

3,92

10,94

7,70

9,79

6,5

13,96

8,95

11,66

6,87

14,11

9,46

12,48

5,49

13,30

8,43

11,26

9,16

14,62

8,83

13,49

9,52

14,64

9,44

14,37

8,17

13,89

8,12

12,42

10,68

15,18

10,9

17,44

11,57

16,17

10,82

17,43

9,68

14,49

10,41

16,04

13,93

17,34

10,92

19,05

13,73

17,47

11,01

19,50

13,42

16,73

10,39

18,34

16,27

19,21

11,75

21,42

16,87

19,85

12,16

21,22

15,92

17,97

11,36

20,94

18,74

22,36

14,05

23,85

18,73

21,43

14,46

23,84

18,04

19,45

13,32

22,74

21,31

22,21

14,09

27,92

22,16

22,12

13,98

27,80

20,69

21,49

12,72

27,09

22,8

22,84

14,60

27,27

23,22

22,98

14,72

27,82

22,68

21,80

14,22

26,43

25,13

22,63

14,38

30,04

25,84

22,61

14,58

30,01

24,33

21,64

13,51

29,39

26,28

24,93

16,57

32,83

26,69

25,03

17,07

33,32

25,64

24,48

15,83

32,37

27,71

24,94

15,51

31,89

27,78

25,16

15,39

31,87

27,14

24,02

14,52

31,52

30,01

25,27

15,44

35,21

30,86

26,05

15,95

35,35

29,22

21,42

15,06

34,89

32,08

26,18

16,00

37,27

32,87

26,44

16,37

37,50

31,09

25,96

15,32

36,33

36,31

29,23

17,97

?

36,25

29,76

18,44

?

35,64

28,12

16,55

?

Варіант 10

Варіант 11

Варіант 12

X1

Х2

Х3

Y

X1

Х2

Х3

Y

X1

Х2

Х3

Y

3,18

10,95

6,94

8,50

1,49

10,12

5,97

7,54

0,95

8,71

4,67

5,94

5,76

12,66

8,20

11,67

3,65

10,54

6,62

10,36

2,82

10,89

6,16

10,20

7,26

14,35

9,03

12,11

6,11

13,09

7,92

11,46

6,09

13,54

7,18

10,22

8,9

15,04

9,87

14,09

8,59

14,03

8,17

12,38

7,36

12,56

6,81

12,40

11,44

16,26

10,65

18,01

9,73

15,13

10,01

15,88

10,28

13,50

9,87

15,64

14,59

18,13

10,57

19,21

12,49

16,51

10,01

18,21

11,97

16,69

8,58

18,23

16,91

19,73

12,18

21,33

15,58

17,73

11,61

20,34

15,08

18,84

10,22

19,34

18,40

21,09

14,02

23,4

18,03

19,80

13,43

23,34

16,43

19,59

12,22

23,28

21,84

27,47

13,77

27,72

20,43

20,55

13,33

27,34

19,59

20,15

12,39

25,80

23,88

22,58

15,01

27,18

21,71

27,08

13,78

26,30

22,44

20,9

13,12

25,47

25,98

22,68

14,51

30,26

23,8

22,36

13,64

29,51

24,53

20,57

12,58

27,72

26,85

25,76

17,62

33,09

26,00

23,84

15,47

32,25

24,56

23,28

16,15

31,40

28,71

25,63

15,59

32,22

26,52

24,29

14,52

31,78

27,39

24,47

13,30

31,67

30,38

25,00

16,23

35,42

29,26

24,33

14,75

34,71

29,41

23,00

14,73

33,68

32,66

26,36

16,63

37,21

31,55

25,73

14,59

35,96

31,82

27,87

14,94

36,97

36,54

29,13

18,59

?

35,66

28,14

16,45

?

35,20

28,30

16,57

?