Лабораторная работа «Множественная линейная регрессия и корреляция»
Для объяснения заработной платы
в зависимости от возраста
и стажа по данной специальности
построить и исследовать линейную
регрессионную модель
.
Применить построенную регрессионую
модель для прогноза заработной платы
при
.
Использовать данные, находящиеся
в таблице с вариантами заданий, и
возможности надстройки «Анализ данных»
табличного процессора MS Excel.
Требуется:
1) ввести данные;
2) провести регрессионный анализ;
3) провести анализ общего качества уравнения регрессии;
4) указать стандартную ошибку регрессии;
5) указать стандартные ошибки коэффициентов;
6) проанализировать статистическую значимость коэффициентов при уровне значимости α = 0,05, при необходимости получить новое уравнение регрессии со значимыми коэффициентами;
7) найти точечные и интервальные оценки для коэффициентов регрессии;
8) дать точечный и интервальный прогноз заработной платы по заданным значениям возраста и стажа;
9) рассчитать коэффициенты эластичности и сделать вывод о влиянии факторов на заработную плату.
Выясните, выполняются ли условия теоремы Гаусса – Маркова для случайного члена:
проверить предположение о равенстве нулю математического ожидания случайного члена;
проверить предположение о постоянстве дисперсии случайного члена, то есть о наличии гомоскедастичности с помощью теста ранговой корреляции Спирмена;
проверить предположение о независимости случайного члена в любом наблюдении от его значений во всех других, то есть об отсутствии автокорреляции с помощью статистики
– Дарбина–Уотсона;проверить гипотезу о нормальном распределении остатков;
5) сделать вывод о присутствии или отсутствии мультиколлинеарности.
Порядок выполнения работы
1. В ячейку А1 занесите название первого столбца — «Зарплата», в ячейку В1 — название второго столбца — «Возраст», в ячейку C1 — название третьего столбца — «Стаж». В ячейки А2, А3, …, А21 введите данные первого столбца таблицы (см. варианты заданий), в ячейки В2, В3, …, В21 — данные второго столбца, в ячейки C2, C3, …, С21 — данные третьего столбца.
Введите новое название листа «Исходные данные», щелкнув правой кнопкой мыши на названии листа «Лист 1» и выбрав опцию переименование или двойным щелчком левой кнопкой мыши в поле «Лист 1». Сохраните рабочую книгу под названием «Фамилия-Множрегр».
2. Выберите в опциях меню Сервис → Анализ данных → Регрессия → ОК. Значения параметров в окне:
Входной интервал Y — введите ссылки на ячейки A1:A21;
Входной интервал X — введите ссылки на ячейки B1:C21;
Метки — установите флажок;
Уровень надежности — установите флажок;
Константа ноль — оставьте пустым;
Параметры вывода — установите флажок на Новый рабочий лист и в соответствующее поле введите его название «Регрессия»;
Остатки — установите флажок;
Стандартизированные остатки — оставьте пустым;
График остатков — установите флажок;
График подбора — установите флажок;
График нормальной вероятности — оставьте пустым. Нажмите «ОК».
Расположите диаграммы рядом (на поле диаграммы нажмите левую кнопку мышки, затем поместите курсор на белое поле и при нажатой левой кнопке передвигайте диаграмму вниз) и растяните их (на поле диаграммы нажмите левую кнопку мышки, нижнюю линию границы диаграммы при нажатой левой клавише протяните вниз).
3. Вычислите критическое значение Fкр в свободной ячейке Е15 следующим образом:
– нажмите на fx (вставка функций);
– в поле Категория окна Мастер функций выберите статистические, из предложенных ниже функций выделите FРАСПОБР и нажмите «ОК». Откроется окно Аргументы функций. Заполните поля так:
Вероятность — наберите значение 0,05;
Степени свободы 1 — установите курсор в поле и выделите ячейку В12 столбца df таблицы «Дисперсионный анализ»;
Степени свободы 2 — установите курсор в поле и выделите ячейку В13 столбца df таблицы «Дисперсионный анализ». Нажмите «ОК».
4.
Вычислите критическое значение
в свободной ячейке D20
следующим образом:
– нажмите на fx (вставка функций);
– в поле «Категория» окна Мастер функций выберите статистические, из предложенных ниже функций выделите СТЬЮДРАСПОБР и нажмите «ОК». Откроется окно Аргументы функций. Значения параметров:
Вероятность — наберите значение 0,05;
Степени свободы — введите 20-2-1, где 20 — число наблюдений, 2 — число факторов в уравнении регрессии, 1 — число свободных членов (a0) в уравнении регрессии. ОК.
5. Выберите в опциях меню Сервис → Анализ данных → Описательная статистика → ОК. Значения параметров в диалоговом окне:
Входной интервал — введите ссылки на ячейки С25:С45 (столбец Остатки с названием);
Группирование — установите флажок по столбцам;
Метки — установите флажок в первой строке;
Выходной диапазон – установите флажок на Новый рабочий лист и в поле напротив введите «ЧХ-ост-условие1»;
Итоговая статистика – установите флажок;
Уровень надежности (95%) – установите флажок. Нажмите «ОК».
6. На листе «ЧХ-ост-условие1» в ячейку D3 введите формулу =(В3-0)*КОРЕНЬ(20)/В7 (для подсчета наблюдаемого значения статистики tнабл). В ячейку D4 введите формулу =СТЬЮДРАСПОБР(0,05;20-1) (для подсчета критической точки распределения Стьюдента tкр).
7. На листе «Исходные данные» ячейки B1:B21 скопируйте в ячейку A1 нового листа и назовите лист «Условие2». В ячейку В1 скопируйте из листа «Регрессия» столбец «Остатки» вместе с названием. В ячейку С1 введите «Модуль ост». Выделите С2:С21 и введите формулу массива (нажмите F2, введите формулу, нажмите Ctrl+Shift+Enter) {=ABS(B2:B21)}.
Выберите в опциях меню Сервис → Анализ данных → Ранг и персентиль → ОК и заполните диалоговое окно следующим образом: Входной интервал — введите ссылки на ячейки A1:A21; Метки — установите флажок; Выходной интервал – ячейка D1. «ОК».
Выберите в опциях меню Сервис → Анализ данных → Ранг и персентиль→ ОК и заполните диалоговое окно следующим образом: Входной интервал — введите ссылки на ячейки С1:С21; Метки — установите флажок; Выходной интервал – ячейка Н1. «ОК».
Выделите ячейки D2:G21 и нажмите кнопку Сортировка по возрастанию на панели инструментов. Выделите ячейки H2:K21 и нажмите кнопку Сортировка по возрастанию. Скопируйте ячейки F1:F21 в ячейку М1, ячейки J1:J21 в ячейку N1. Выделите ячейки О2:О21 и введите формулу массива {= (M2:M21-N2:N21)^2}.
Установите курсор на ячейке О22 и введите формулу = 1-6*СУММ(О2:О21)/(20*(20^2-1)).
В ячейку О23 введите формулу = О22*КОРЕНЬ(19) (для вычисления tнабл)
В ячейку О24 введите формулу = СТЬЮДРАСПОБР(0,05;20-2) (для вычисления tкр).
8. Из листа «Регрессия» скопируйте столбец «Остатки» в ячейку А1 нового листа, который назовите «Условие3».
В ячейки В2:В20 введите формулу массива {=(А2:А20-А3:А21)^2}.
В ячейку В22 введите формулу = СУММ(В2:В20).
В ячейки С2:С21 введите формулу массива {=(А2:А21)^2}.
В ячейку С22 введите формулу = СУММ(С2:С21)
В ячейку С23 введите формулу =В22/С22
(для вычисления
).
9. Вернитесь на лист Регрессия. Выберите в опциях меню Сервис → Анализ данных → Гистограмма → ОК. Значения параметров окна:
Входной интервал — введите ссылки на ячейки С25:С45 (столбец Остатки листа «Регрессия» с названием);
Интервал карманов — не заполняйте;
Метки — установите флажок;
Выходной диапазон — введите ссылку на Новый рабочий лист «Гистограмма остатков»;
Парето — оставьте пустым;
Интегральный процент — оставьте пустым;
Вывод графика — установите флажок. Нажмите «ОК». Перенесите гистограмму вниз и растяните ее.
10. Откройте лист «Исходные данные», выберите в опциях меню Сервис → Анализ данных → Корреляция →ОК. Значения параметров окна: Входной интервал – ячейки В1:С21, Метки — установите флажок, Новый рабочий лист – «МК». Нажмите «ОК».
Скопируйте В3 в С2.
В ячейку А5 введите математическую формулу = МОПРЕД(B2:C3).
В ячейку А6 введите формулу =20-1-(1/6)*9*LOG(A5;10) (для нахождения хи-квадрат наблюдаемого ).
В ячейку А7 введите формулу =ХИ2ОБР(0,05;190) (для нахождения хи-квадрат критического).
11. На листе «Исходные данные» введите формулы:
в ячейку А23 =СРЗНАЧ(А2:А21);
в ячейку В23 =СРЗНАЧ(В2:В21);
в ячейку С23 =СРЗНАЧ(C2:C21).
Из листа «Регрессия» скопируйте ячейку В18 в ячейку В24 листа «Исходные данные», ячейку В19 в С24. (значения коэффициентов).
В ячейку В25 введите формулу =В24*В23/А23 (для вычисления коэффициента эластичности переменной Возраст).
В ячейку С25 введите формулу =С24*С23/А23 (для вычисления коэффициента эластичности переменной Стаж).
12. На листе «Регрессия» в ячейкe D21 введите формулу = В17+В18*55+В19*25.
В ячейках D22 и E22 введите формулы
=D21-D20*В7 (левый конец интервального прогноза заработной платы);
=D21+D20*В7 (правый конец интервального прогноза заработной платы).
Варианты задания
Вариант 1 |
Вариант 2 |
Вариант 3 |
Вариант 4 |
||||||||
заработная плата |
возраст |
стаж |
заработная плата |
возраст |
стаж |
заработная плата |
возраст |
стаж |
заработная плата |
возраст |
стаж |
699,7 |
28 |
10 |
564,2 |
26 |
7 |
725 |
20 |
5 |
830,5 |
20 |
5 |
679,7 |
26 |
8 |
554,8 |
25 |
6 |
752,5 |
35 |
6 |
858 |
35 |
6 |
739,7 |
30 |
14 |
582,1 |
28 |
9 |
791 |
28 |
10 |
896,5 |
28 |
10 |
749,6 |
35 |
15 |
635,2 |
35 |
15 |
775,3 |
35 |
8 |
880,8 |
35 |
8 |
759,5 |
41 |
16 |
570,2 |
40 |
9 |
793,5 |
42 |
9 |
899 |
42 |
9 |
779,5 |
45 |
18 |
654,8 |
45 |
18 |
801 |
38 |
10 |
906,5 |
38 |
10 |
629,7 |
27 |
3 |
523,1 |
27 |
3 |
703,5 |
20 |
3 |
809 |
20 |
3 |
729,6 |
35 |
13 |
615,1 |
35 |
13 |
809 |
35 |
11 |
914,5 |
35 |
11 |
699,7 |
30 |
10 |
590,2 |
30 |
10 |
793 |
30 |
10 |
898,5 |
30 |
10 |
619,7 |
23 |
2 |
517,1 |
23 |
2 |
695,5 |
23 |
2 |
801 |
23 |
2 |
669,7 |
29 |
7 |
560,1 |
30 |
7 |
759 |
30 |
7 |
864,5 |
30 |
7 |
709,6 |
33 |
11 |
596,8 |
33 |
11 |
806 |
32 |
11 |
911,5 |
32 |
11 |
799,6 |
40 |
20 |
600,1 |
40 |
12 |
825 |
40 |
12 |
930,5 |
40 |
12 |
809,5 |
41 |
21 |
678,9 |
41 |
20 |
813,5 |
28 |
12 |
919 |
28 |
12 |
659,5 |
41 |
6 |
539,1 |
41 |
6 |
758,7 |
41 |
6 |
864,2 |
41 |
6 |
609,7 |
23 |
1 |
507,6 |
23 |
1 |
684,3 |
23 |
1 |
789,8 |
23 |
1 |
719,6 |
32 |
12 |
608,2 |
32 |
12 |
815 |
32 |
12 |
920,5 |
32 |
12 |
729,6 |
37 |
13 |
613,4 |
37 |
13 |
833,5 |
37 |
13 |
939 |
37 |
13 |
669,6 |
31 |
7 |
558,5 |
31 |
7 |
753,5 |
24 |
7 |
859 |
24 |
7 |
689,7 |
30 |
9 |
588,1 |
22 |
9 |
775 |
22 |
9 |
880,5 |
22 |
9 |
Вариант
5
Вариант
6
Вариант
7
Вариант
8
заработная плата
возраст
стаж
заработная
плата
возраст
стаж
заработная
плата
возраст
стаж
заработная
плата
возраст
стаж
735,5
28
10
604,2
26
7
725
20
5
824,8
26
7
712,8
26
8
594,8
25
6
752,5
35
6
815,4
25
6
778,2
30
14
622,5
28
9
791
28
10
843,1
28
9
794,5
35
15
675,2
35
15
775,3
35
8
895,8
35
15
812,3
41
16
610,2
40
9
793,5
42
9
830,8
40
9
857,5
45
18
694,8
45
18
801
38
10
915,4
45
18
664,2
27
3
563,9
27
3
703,5
20
3
784,5
27
3
774,5
35
13
655,1
35
13
809
35
11
875,7
35
13
738,1
30
10
630
30
10
793
30
10
850,6
30
10
648,5
23
2
557,7
23
2
695,5
23
2
778,3
23
2
706,7
29
7
600,5
30
7
759
30
7
821,1
30
7
751,9
33
11
636,8
33
11
806
32
11
857,4
33
11
851,1
40
20
640,3
40
12
825
40
12
860,9
40
12
862,2
41
21
718,9
41
20
813,5
28
12
939,5
41
20
712,3
41
6
579,5
41
6
758,7
41
6
800,1
41
6
638,9
23
1
547,6
23
1
684,3
23
1
768,2
23
1
760,6
32
12
648,2
32
12
815
32
12
868,8
32
12
777,2
37
13
653,4
37
13
833,5
37
13
874
37
13
709,2
31
7
598,5
31
7
753,5
24
7
819,1
31
7
728,1
30
9
628,2
22
9
775
22
9
848,8
22
9