2015-04-17
477
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 |
| Вариант 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 |
Вопросы для самоконтроля
|
|
|
1. Дайте определение множественной регрессионной модели.
2. Какие задачи решаются на этапе спецификации модели множественной линейной регрессии?
3. Опишите этапы отбора факторов множественной регрессии.
4. Как определяется матрица парных коэффициентов корреляции?
5. Опишите метод отбора факторов, основанный на анализе матрицы парных коэффициентов корреляции.
6. Какой вид имеет классическая линейная модель множественной регрессии?
7. Приведите примеры нелинейных моделей множественной регрессии.
8. Что характеризуют коэффициенты уравнения множественной линейной регрессии?
9. В чем суть МНК для построения линейной модели множественной регрессии?
10. Какая модель множественной линейной регрессии называется классической?
11. Какие задачи решаются на этапе верификации модели множественной линейной регрессии?
12. Как определяется статистическая значимость коэффициентов регрессии?
13. В чем суть статистической значимости коэффициентов регрессии?
14. Как строятся интервальные оценки коэффициентов регрессии и в чем их суть?
15. В чем суть коэффициента детерминации R 2?
16. Как осуществляется анализ статистической значимости коэффициента детерминации?
17. Как определяются средние коэффициенты эластичности для линейной множественной регрессии?
18. В чем сущность мультиколлинеарности факторов?
19. Как оценить наличие мультиколлинеарности факторов с помощью определителя матрицы межфакторной корреляции?
20. Как осуществляется анализ статистической значимости мультиколлинеарности факторов в множественной линейной регрессионной модели?
21. Перечислите предпосылки МНК.
22. Каковы последствия выполнимости или невыполнимости предпосылок МНК?
23. Приведите примеры зависимостей, содержащих качественные факторы.
24. Какие переменные называются значащими, а какие – фиктивными?
25. Какие переменные называются дихотомическими?
26. В чем проявляется необходимость введения фиктивных переменных?
27. Как дихотомическая переменная вводится в эконометрическую модель?
28. Почему при построении регрессионной модели, учитывающей фактор с k уровнями качества, вводится k -1 фиктивная переменная, а не k переменных?
29. В чем проявляется «ловушка фиктивной переменной»?
30. Для решения каких задач применяется тест Чоу?
31. В чем суть теста Чоу?
32. Изложите графическую интерпретацию возможных выводов, полученных на основании теста Чоу?
33. Как с помощью фиктивных переменных в регрессионной модели учитывается сезонность и цикличность?
Тема 4
