Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:

Инструкция_ ЛР №3

.pdf
Скачиваний:
5
Добавлен:
20.02.2016
Размер:
232.46 Кб
Скачать

Эконометрия. Лабораторний практикум

1

ИНСТРУКЦИЯ К ЛАБОРАТОРОЙ РАБОТЕ №3

Множественная регрессия

По заданным статистическим данным для одного зависимого фактора Y и трех влияющих факторов X1, X2, X3 необходимо построить линейную корреляционную зависимость:

ˆy = a0 + a1 × x1 + a2 × x2 + a3 × x3 .

Требуется:

1.Вычислить числовые характеристики для каждого из факторов, входящих в выборку.

2.Провести стандартизацию факторов.

3.Построить корреляционную матрицу R.

4.Исследовать ее и проверить наличие мультиколинеарности по критерию Фаррара-Глобера.

5.Если мультиколинеарность обнаружена, выяснить по критерию Стьюдента между какими именно факторами..

6.Устранить мультиколинеарность.

7.Найти коэффициенты стандартизированного уравнения регрессии

сустраненной мультиколинеарностью.

8.Найти коэффициенты исходного уравнения регрессии и записать уравнение множественной регрессии.

9.Провести сравнение теоретических значений фактора Y со статистическими.

10.Проверить наличие автокорреляции остатков, найти коэффициент автокорреляции.

11.Проверить адекватность полученного уравнения регрессии по критерию Фишера.

12.Оценить степень влияния отдельных факторов на результирующий фактор Y.

13.Найти прогнозное значение фактора Y.

14Построить доверительный интервал для прогноза.

Найти файл « Л Р №3 трафарет. xls » и скопировать его в свою папку. Переименовать, добавив в название свою фамилию.

В отведенном поле листа ввести свои группу и фамилию.

Из таблицы исходных данных выбрать свой вариант данных следующим образом :

Если Ваш номер по журналу меньше или равен 12, то номер варианта совпадает с номером по журналу.

Если Ваш номер по журналу больше 12, то выбирается номер варианта, равный номеру по журналу минус 12.

Занести исходные данные в отведенные ячейки столбцов N:Q.

В ячейки N32:Q32 занести значения факторов X i для прогноза .

Эконометрия. Лабораторный практикум.

2

1.Вычисление числовых характеристик каждого из факторов

Каждому из столбцов исходных данных дать соответствующие имена.

В отведенных для этого ячейках строк 23, 25 и 27 подсчитать числовые ха-

рактеристики факторов X i и Y:

 

 

 

 

 

 

 

средние xi

(«Мастер функций», функция СРЗНАЧ)

дисперсии Di

(«Мастер функций», функция ДИСПР)

 

стандартные отклонения σ i =

 

 

(«Мастер функций», функция КО-

 

Di

РЕНЬ)

ячейкам присвоить соответствующие имена

В ячейке H29 подсчитать объем выборки n (функция СЧЕТ).

Ячейке присвоить имя (например "n" или "объем").

2. Стандартизация факторов

Для каждого из факторов стандартизация проводится по формулам:

 

 

X i -

 

 

 

Y -

 

 

 

 

xi

 

 

Zi =

 

W

=

y

 

σi

 

σ y

 

∙ Выделить весь столбец Z1

 

 

 

 

(ячейки S6:S21).

Ввести формулу:

= ( имя X1 - имя x1) / имя σ1 )

Ввести эту формулу сочетанием клавиш Ctrl + Enter .Дать имя столбцу Z1.

Аналогично провести стандартизацию все остальных факторов.

Каждому из построенных столбцов дать соответствующие имена.

В отведенных для этого ячейках строк 23 и 25 подсчитать числовые харак-

теристики стандартизированных факторов Z i и W:

средние (должны быть равны нулю)

дисперсии (должны быть равны 1)

3. Построение корреляционной матрицы R

Выделить массив ячеек S6:U21 и дать ему имя (напр. Z). Столбец W6:W21 уже имеет имя (напр. W).

Выделить ячейки AK5:AM7 для того, чтобы разместить в них корреляционную матрицу R :

R = Z T × Z n

использовать Мастер Функций категория МАТЕМАТИЧЕСКИЕ функции МУМНОЖ и ТРАНСП ; (матричная формула).

Дать построенной матрице имя ( напр. R0 ).

Эконометрия. Лабораторний практикум

3

В столбце AO5:AO7 аналогично вычислить матрицу Ry : Ry

=

Z T ×W

;

n

 

ей тоже дать имя (напр. Ry).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4. Проверка на наличие мультиколинеарности

 

 

В ячейке AL10 вычислить значение критерия Фаррара - Глобера

 

 

по формуле:

χнабл2

é

(2m+ 1)ù

 

 

 

 

= -ên- 1 -

6

ú × ln det(R)

 

 

 

 

 

 

ë

û

 

 

 

Вместо m (число факторов Xi ) подставить 3, использовать функции LN и МОПРЕД категории МАТЕМАТИЧЕСКИЕ, матрицу R вызывать по имени R0.

В ячейке AO10 поместить взятое из таблиц критическое значение крите-

 

χ кр2 (α ;k) = χкр2

æ

m(m- 1)

ö

= χкр2

(0,05 ; 3)

рия

ç0,05 ;

 

 

÷

2

 

 

 

è

 

ø

 

 

(«Мастер функций», функция ХИ2ОБР)

Сравнивая эти числа, сделать вывод о наличии или отсутствии мультиколинеарности (если наблюдаемое значение больше критического, то она есть). Вывод записать в отведенном поле.

5. Если мультиколинеарность обнаружена, выяснить по критерию Стьюдента между какими именно факторами..

В отведенных ячейках AJ18:AL20 вычислить обратную матрицу ( R–1 )

(функция МОБР категории Математические).

∙ В ячейках AO18; AP18; AP19 вычислить по элементам z ij обратной матри-

цы частные коэффициенты корреляции:

ρij = -

 

 

zij

 

.

 

 

 

 

 

 

z ii ×z jj

 

 

 

 

 

Например, в ячейке AO18 вычислить

с 12 , записывая формулу:

= − AK18/КОРЕНЬ(AJ18*AK19)

Так как матрица симметрична и на главной диагонали стоят единицы, то вычислить нужно только 3 таких числа.

В ячейку AK26 занести взятое из таблиц критическое значение критерия

Стьюдента tкр (α;k) = tкр (0,05; n- m- 1) = tкр (0,05; n- 4)

В ячейках AK24, AL24, AL25 вычислить наблюдаемое значение критерия

 

 

 

ρ ij

 

 

 

Стьюдента:

t ij =

 

 

 

n - 4

 

 

 

 

1 - ρij2

 

 

 

 

 

 

Эконометрия. Лабораторный практикум.

4

Например, в ячейку AK24 записать формулу:

=AO18/ КОРЕНЬ(1−AO18^2)*КОРЕНЬ (n − 4)

В ячейках AO24, AP24, AP25 отметить наличие или отсутствие мультиколи-

неарности между факторами. Если

(t i j )набл

> tкр ,

то мульти-

колинеарность между этими факторами есть и в соответствующей ячейке поставить единицу. Если меньше, то поставить 0 − между такими факторами мультиколинеарности нет.

В отведенном поле записываем вывод о том, какой из факторов следует удалить для устранения мультиколинеарности.

6. Устранение мультиколинеарности.

В отведенные столбцы AU:BA cкопировать исходные и стандартизированные данные для факторов, оставленных в модели. Сделать это следующим образом:

В заголовки столбцов вписать номера оставленных в модели факторов.

Выделить все ячейки первого из столбцов: AU8:AU21. Записать формулу:

=имя копируемого столбца X i

Ввести эту формулу сочетанием клавиш Ctrl + Enter.

Аналогичным образом скопировать остальные столбцы, оставленные в модели.

∙ Выделить столбец AT , вписать в него формулу =1 и ввести сочетанием клавиш Ctrl + Enter.

Выделить три столбца чисел: AT6:AV21 и присвоить имя этой матрице (например XX).

Выделить два столбца чисел: AY6:AZ21 и присвоить имя этой матрице (например ZZ).

В отведенных полях (столбцы BF:BK) убрать из записанных уравнений регрессии отброшенный фактор.

7. Отыскание коэффициентов стандартизированного уравнения регрессии с устраненной мультиколинеарностью.

∙ В ячейках BF12:BG13 вычислить новую корреляционную матрицу R :

R = ZZ T × ZZ n

использовать Мастер Функций категория МАТЕМАТИЧЕСКИЕ функции МУМНОЖ и ТРАНСП ; (матричная формула).

Эконометрия. Лабораторний практикум

5

Дать построенной матрице имя ( напр. RR ).

∙ В столбце BH12:BH13 аналогично вычислить матрицу Ry :

Ry =

ZZT

×W

;

n

 

 

 

 

ей тоже дать имя ( напр. RRy ).

Найти в ячейках BK12:BK13 решение нормальной системы , умножая обратную матрицу на столбец правых частей :

r = R1 × R

α y

(МОБР, МУМНОЖ, матричная формула). Дать каждой из этих ячеек имена.

Вписать найденные значения коэффициентов в уравнение регрессии на отведенном поле.

8. Отыскание коэффициентов исходного уравнения регрессии. Запись уравнения множественной регрессии.

В отведенных ячейках столбца BK вычислить коэффициенты исходного уравнения регрессии по формулам перехода от (a i) к (a i):

а i

= α i ×

σ y

σi

 

 

a0 = y - a1 × x1 - a2 × x2 - a3 × x3 .

Вписать найденные значения коэффициентов (a i ) в уравнение регрессии на отведенном поле.

9. Сравнение теоретических значений фактора Y со статистическими.

В столбец BQ скопировать исходные статистические данные для фактора Y.

Для этого выделить его и сочетанием клавиш Ctrl + Enter ввести формулу:

=имя копируемого столбца Y

В столбце BR подсчитать теоретические значения функции регрессии по построенной формуле

ˆy = a0 + a1 × x1 + a2 × x2 + a3 × x3

Выделить весь столбец и запрограммировать формулу регрессии, вызывая

коэффициенты ai и столбцы Xi по именам

(

Ctrl + Enter

). Дать столбцу имя.

В столбце BS подсчитать "остатки" i

= yi - i .

Выделить весь столбец и ввести формулу сочетанием клавиш Ctrl + Enter. Дать столбцу имя.

∙ В отведенных ячейках вычислить:

среднее значение остатков;

Эконометрия. Лабораторный практикум.

6

n å 2i

дисперсию остатков: Sост2

=

i=1

n l

 

 

l - число коэффициентов в уравнении регрессии (нестандартизированном). Использовать функцию СУММКВ .

 

 

 

стандартное отклонение остатков: S = Sост2

(этой ячейке дать имя).

Если корреляционная зависимость хорошо описывает статистические данные, то значения yi и ˆyi должны быть близкими, остатки i малыми, среднее остат-

ков должно быть приблизительно равно 0, а дисперсия и стандартное отклонение малыми величинами.

10. Проверка остатков на наличие автокорреляции. Отыскание коэффициента автокорреляции

В столбец BZ6:BZ21 скопировать столбец остатков i

(вводя сочетанием клавиш Ctrl + Enter имя копируемого столбца i )

Еще раз скопировать его, но с лагом 1, в столбец BY ( т.е., в BY7:BY22). (здесь ввести его как матричную формулу: курсор и Ctrl +Shift + Enter )

По любому из этих столбцов построить график остатков.

Использовать Мастер Диаграмм, тип График. Отредактировать график так, чтобы он имел вид:

8

 

 

 

 

 

 

 

 

 

 

6

 

 

 

 

 

 

 

 

 

 

4

 

 

 

 

 

 

 

 

 

 

2

 

 

 

 

 

 

 

 

 

 

0

 

 

 

 

 

 

 

 

 

 

-2

1

2

3

4

5

6

7

8

9

10

 

 

 

 

 

 

 

 

 

 

-4

 

 

 

 

 

 

 

 

 

 

-6

 

 

 

 

 

 

 

 

 

 

-8

 

 

 

 

 

 

 

 

 

 

∙ В ячейке CE17 подсчитать значение критерия Дарбина -Уотсона:

n

å( i i1 )2

d

набл

=

2

n

 

 

 

å i

 

 

 

2

 

 

 

1

Для числителя использовать функцию СУММКВРАЗН категории МАТЕМАТИЧЕСКИЕ причем для первого массива взять элементы от первого до предпоследнего, а для второго массива элементы от второго до последнего (выделенные рамкой и цветом).

Эконометрия. Лабораторний практикум

7

Для знаменателя использовать функцию СУММКВ причем брать все элементы столбца BZ.

По таблицам критических точек критерия Дарбина - Уотсона найти две

критических точки d н

и

d в . При этом задавать надежность P = 0,95, объем

выборки n и количество

m

факторов в регрессии.

Занести эти числа в отведенные ячейки.

В ячейки СС23:СА23 занести границы соответствующих зон.

Сравнивая полученные числа, сделать вывод о наличии или отсутствии автокорреляции:

 

Если

0 < dнабл < dн

или 4 dн < dнабл < 4

- автокорреляция есть.

 

 

 

 

 

 

 

 

 

Если

 

dв

< dнабл < 4 dв

 

- автокорреляции нет.

Если

dн < dнабл

< dв

или 4 dв

< dнабл < 4 dн

- объем выборки

недостаточен для ответа на вопрос о наличии автокорреляции .

Вывод записать в отведенное поле.

В отведенной ячейке подсчитать коэффициент автокорреляции.

Использовать функцию КОРРЕЛ для столбцов BY7:BY21 и BZ7:BZ21.

11. Проверка адекватности полученного уравнения регрессии

∙ В ячейке CO6 подсчитать величину коэффициента множественной де-

 

терминации R2 :

R 2

= åα i × riy .

 

 

Коэффициенты регрессии a i

находятся в ячейках BK12:BK13, коэффициенты

 

парной корреляции r iy

- в ячейках

BH12:BH13. Можно использовать функцию

 

СУММПРОИЗВ.

 

 

 

 

 

 

 

 

 

 

 

В ячейке CO8 подсчитать коэффициент множественной корреляции:

 

 

 

R 2

=

 

 

 

.

 

 

 

 

 

 

 

R

 

 

 

В ячейки CO10 и CO11 занести числа степеней свободы:

 

 

k1 = l 1; k2 = n l

 

 

n - объем выборки ; l -

количество параметров ai в уравнении регрессии

 

(после исключения мультиколинеарности их остается три).

В ячейке CL15 подсчитать наблюдаемое значение критерия Фишера:

 

 

Fнабл =

 

R 2

 

×

n - l

 

.

 

 

 

-

R 2

l - 1

 

 

 

1

 

 

∙ В ячейку CO15 занести взятое из таблиц критическое значение крите-

 

рия Фишера

Fкр ( α; k1 ; k2 ).

 

 

 

Уровень значимости α принять, как обычно, равным 0,05. Можно воспользоваться функцией FРАСПОБР.

Эконометрия. Лабораторный практикум.

8

Сравнивая эти два числа, сделать вывод об адекватности построенной модели. Записать его в отведенном поле.

12. Оценка влияния отдельных факторов на результирующий фактор Y.

Занести в ячейки CN25:CO25 значения коэффициентов стандартизированной регрессии αi .

В ячейках CN28:CO28 подсчитать значения частных коэффициентов детерминации:

di = α i × riy

Записать индексы факторов, оставшихся в модели после устранения мультиколинеарности.

В отведенном поле записать вывод о том, какой из факторов Xi оказывает большее влияние на результирующий фактор Y.

 

13. Отыскание прогнозного значения фактора Y

∙ Из прогнозных значений факторов

X i

нужно сформировать матрицу-

строку:

X p = [1 x1p

x2p

.... xmp ]

∙ В ячейки CV7:CW7 скопировать формулой прогнозные значения оставленных в модели факторов X i из заполненных в начале ячеек N32:P32. В ячейку CU7 занести 1. Выделить массив из трех заполненных ячеек CU7:CW7 и присвоить ему имя (например Хр ).

∙ В ячейке CZ7 по построенной формуле множественной регрессии

ˆy = a0 + a1 × x1 + a2 × x2 + a3 × x3

подсчитать значения ˆy p для прогноза. (Отброшенный фактор не включать в формулу).

14. Построение доверительного интервала для прогноза

Формула для доверительного интервала:

ˆy p ± ˆy p .

Его размах равен

ˆy p = tγ,k × S × 1 + X p × (X T × X )1 × X Tp ;

Чтобы его подсчитать, нужно выполнить следующие действия:

Эконометрия. Лабораторний практикум

9

В ячейку CU10 поместить взятое из таблиц критерия Стьюдента (или вычисленное с использованием МАСТЕРА ФУНКЦИЙ) значение коэффициента

t ( γ,k) = t (0,95 ; n 2).

Дать ячейке имя (tg)

∙ В ячейку CW10 скопировать формулой стандартное отклонение остатков S из ячейки BS25 (по имени).

∙ В ячейке CT12 запрограммировать формулу, стоящую под корнем

=1+МУМНОЖ(МУМНОЖ(Xp;МОБР(МУМНОЖ(ТРАНСП(XX);XX)));ТРАНП(Xp))

Здесь XX – имя матрицы исходных нестандартизированных данных, которая помещается в трех столбцах: AT6:AV21; Xp – матрица прогнозных значений. (матричная формула).

В ячейке CW12 подсчитать размах доверительного интервала для прогноза

=tg*S*КОРЕНЬ(CT12)

∙ В ячейках CY12 и CA12 к значению ˆy p из ячейки CZ7 прибавить и вычесть ˆy p .

∙ Записать вывод в отведенной поле.

Сохранить файл в своей личной папке: Сохранить файл на дискете.

Эконометрия. Лабораторный практикум.

10

Исходные данные к лабораторной работе №4

МНОЖЕСТВЕННАЯ РЕГРЕССИЯ

 

 

Значения

факторов X1, Х2, ХЗ и показателя Y

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Вариант 1

 

 

 

Вариант 2

 

 

 

Вариант 3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

X1

Х2

ХЗ

Y

 

X1

Х2

ХЗ

Y

 

X1

Х2

ХЗ

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.70

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.55

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

20.3

13.77

23.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

22.4

14.43

27.13

 

22.66

22.22

14.41

27.00

 

22.75

22.53

14.55

27.18

25.1

22.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

15.06

35.10

 

30.16

25.19

1538

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

33.7

27.4

17.21

38.85

 

33.66

27.27

17.14

38.70

 

33.94

27.60

17.27

38.96

 

 

 

 

 

 

 

 

 

 

 

 

 

 

35.8

28.9

17.47

?

 

35.57

28.80

17.39

?

 

36.00

29.02

17.56

?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Вариант 4

 

 

 

Вариант 5

 

 

 

Вариант 6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

X1

Х2

ХЗ

Y

 

X1

Х2

ХЗ

Y

 

X1

Х2

ХЗ

Y

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.37

10.27

6.40

7.73

 

2.25

9.90

6.09

7.49

 

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.54

 

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.51

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.57

 

13.53

17.42

10.66

18.80

16.31

19.04

11.74

21.15

 

15.95

18.84

11.65

21.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.55

14.60

27.16

 

22.67

22.31

14.33

27.12

 

23.02

22.69

14.56

27.45

25.15

22.56

14.24

29.73

 

24.99

22.39

13.95

29.42

 

25.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.70

24.82

15.03

31.83

 

27.34

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

33.85

27.58

17.30

36.97

 

33.55

27.35

16.96

3874

 

33.76

27.72

17.40

39.20

 

 

 

 

 

 

 

 

 

 

 

 

 

 

35.96

29.09

17.48

?

 

35.71

28.83

17.25

?

 

35.97

29.15

17.77

?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]