Лабораторная работа 2
МНОЖЕСТВЕННАЯ РЕГРЕССИЯ И КОРРЕЛЯЦИЯ
Задание. Для объяснения заработной платы в зависимости от возраста
и стажа по данной специальности
построить линейную регрессионную модель
.
Порядок выполнения работы
Запустите программу Microsoft Excel.
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 — число свободных членов (a 0) в уравнении регрессии. ОК.
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 | 564,2 | 830,5 | |||||||||
679,7 | 554,8 | 752,5 | |||||||||
739,7 | 582,1 | 896,5 | |||||||||
749,6 | 635,2 | 775,3 | 880,8 | ||||||||
759,5 | 570,2 | 793,5 | |||||||||
779,5 | 654,8 | 906,5 | |||||||||
629,7 | 523,1 | 703,5 | |||||||||
729,6 | 615,1 | 914,5 | |||||||||
699,7 | 590,2 | 898,5 | |||||||||
619,7 | 517,1 | 695,5 | |||||||||
669,7 | 560,1 | 864,5 | |||||||||
709,6 | 596,8 | 911,5 | |||||||||
799,6 | 600,1 | 930,5 | |||||||||
809,5 | 678,9 | 813,5 | |||||||||
659,5 | 539,1 | 758,7 | 864,2 | ||||||||
609,7 | 507,6 | 684,3 | 789,8 | ||||||||
719,6 | 608,2 | 920,5 | |||||||||
729,6 | 613,4 | 833,5 | |||||||||
669,6 | 558,5 | 753,5 | |||||||||
689,7 | 588,1 | 880,5 |
Вариант 5 | Вариант 6 | Вариант 7 | Вариант 8 | ||||||||
заработная плата | возраст | стаж | заработная плата | возраст | стаж | заработная плата | возраст | стаж | заработная плата | возраст | стаж |
735,5 | 604,2 | 824,8 | |||||||||
712,8 | 594,8 | 752,5 | 815,4 | ||||||||
778,2 | 622,5 | 843,1 | |||||||||
794,5 | 675,2 | 775,3 | 895,8 | ||||||||
812,3 | 610,2 | 793,5 | 830,8 | ||||||||
857,5 | 694,8 | 915,4 | |||||||||
664,2 | 563,9 | 703,5 | 784,5 | ||||||||
774,5 | 655,1 | 875,7 | |||||||||
738,1 | 850,6 | ||||||||||
648,5 | 557,7 | 695,5 | 778,3 | ||||||||
706,7 | 600,5 | 821,1 | |||||||||
751,9 | 636,8 | 857,4 | |||||||||
851,1 | 640,3 | 860,9 | |||||||||
862,2 | 718,9 | 813,5 | 939,5 | ||||||||
712,3 | 579,5 | 758,7 | 800,1 | ||||||||
638,9 | 547,6 | 684,3 | 768,2 | ||||||||
760,6 | 648,2 | 868,8 | |||||||||
777,2 | 653,4 | 833,5 | |||||||||
709,2 | 598,5 | 753,5 | 819,1 | ||||||||
728,1 | 628,2 | 848,8 |
|
|