Порядок выполнения:

1) В ячейку А1 введите название Зарплата, в ячейку В1 – название Возраст, в ячейку C1 – название Стаж. В ячейки А2, А3, …, А21 введите данные первого столбца выбранного варианта задания, в ячейки В2, В3, …, В21 – данные второго столбца, в ячейки C2, C3, …, С21 – данные третьего столбца.

Введите новое название листа «Исходные данные», щелкнув правой кнопкой мыши на названии листа «Лист 1» и выбрав опцию переименование или двойным щелчком левой кнопкой мыши в поле «Лист 1».

2) Выберите в опциях меню Сервис → Анализ данных → Регрессия → ОК. Установите значения параметров в диалоговом окне следующим образом:

• Входной интервал Y — введите ссылки на ячейки A1:A21;

• Входной интервал X — введите ссылки на ячейки B1:C21;

• Метки — установите флажок;

• Уровень надежности — установите флажок;

• Константа ноль — оставьте пустым;

• Параметры вывода — установите флажок на Новый рабочий лист и в соответствующее поле введите его название «Регрессия»;

• Остатки — установите флажок;

• Стандартизированные остатки — оставьте пустым;

• График остатков — установите флажок;

• График подбора — установите флажок;

• График нормальной вероятности — оставьте пустым.

Нажмите ОК.

Расположите диаграммы рядом (на поле диаграммы нажмите левую кнопку мышки, затем поместите курсор на белое поле и при нажатой левой кнопке передвигайте диаграмму вниз) и растяните их (на поле диаграммы нажмите левую кнопку мышки, нижнюю линию границы диаграммы при нажатой левой клавише протяните вниз).

3) Значение коэффициента детерминации находится на листе «Регрессия» в ячейке В5. Наблюдаемое значение F-критерия Фишера находится на листе «Регрессия» в ячейке E12.

Вычислите критическое значение F_кр в свободной ячейке Е15 следующим образом:

– нажмите на f_x (вставка функций);

– в поле Категория окна Мастер функций выберите статистические, из предложенных ниже функций выделите FРАСПОБР и нажмите ОК.

Откроется окно Аргументы функций. Заполните поля так:

• Вероятность — наберите значение 0,05;

• Степени свободы 1 — установите курсор в поле и выделите ячейку В12 столбца df таблицы «Дисперсионный анализ»;

• Степени свободы 2 — установите курсор в поле и выделите ячейку В13 столбца df таблицы «Дисперсионный анализ».

Нажмите ОК.

4) Значение стандартной ошибки регрессии находится на листе «Регрессия» в ячейке В7.

5) Значения стандартных ошибок коэффициентов регрессии находятся на листе «Регрессия» в ячейках С17, С18 и С19 соответственно.

6) Наблюдаемые значения -статистики коэффициентов регрессии , и находятся на листе «Регрессия» в ячейках D17, D18 и D19 соответственно.

Вычислите критическое значение в свободной ячейке D20 следующим образом:

– нажмите на f_x (вставка функций);

– в поле «Категория» окна Мастер функций выберите статистические, из предложенных ниже функций выделите СТЬЮДРАСПОБР и нажмите ОК. Откроется окно Аргументы функций. Заполните поля:

• Вероятность – наберите значение 0,05;

• Степени свободы – введите 20-2-1, где 20 – число наблюдений, 2 – число факторов в уравнении регрессии, 1 – число свободных членов (a₀) в уравнении регрессии.

Нажмите ОК.

7) Точечные оценки коэффициентов регрессии , и находятся на листе «Регрессия» в ячейках В17, В18 и В19 соответственно. Нижняя и верхняя границы доверительного интервала вычислены на листе «Регрессия» в ячейках F17 и G17 для коэффициента , в ячейках F18 и G18 для коэффициента и в ячейках F19 и G19 для коэффициента .

8) На листе «Регрессия» в ячейке D22 введите формулу

= В17+В18*55+В19*25.

9) На листе «Исходные данные» введите формулы:

в ячейку А23 =СРЗНАЧ(А2:А21);

в ячейку В23 =СРЗНАЧ(В2:В21);

в ячейку С23 =СРЗНАЧ(C2:C21).

С листа «Регрессия» скопируйте ячейку В18 в ячейку В24 листа «Исходные данные», ячейку В19 – в ячейку С24 (значения коэффициентов регрессии).

В ячейку В25 введите формулу =В24*В23/А23 (для вычисления коэффициента эластичности переменной Возраст).

В ячейку С25 введите формулу =С24*С23/А23 (для вычисления коэффициента эластичности переменной Стаж).

Проверка модельных предположений:

1) Для проверки условия о равенстве нулю математического ожидания случайной величины на листе «Регрессия» выберите в опциях меню Сервис → Анализ данных → Описательная статистика → ОК. Установите значения параметров в диалоговом окне следующим образом:

• Входной интервал – введите ссылки на ячейки С25:С45 (столбец Остатки с названием);

• Группирование – установите флажок по столбцам;

• Метки – установите флажок в первой строке;

• Выходной диапазон – установите флажок на Новый рабочий лист и в поле напротив введите «Статистика»;

• Итоговая статистика – установите флажок;

• Уровень надежности (95%) – установите флажок.

Нажмите ОК.

Для оценки значимости среднего на листе «Статистика» в ячейку D3 введите формулу =(В3-0)*КОРЕНЬ(20)/В7 (для подсчета наблюдаемого значения статистики ). В ячейку D4 введите формулу =СТЬЮДРАСПОБР(0,05;20-1) (для подсчета критического значения распределения Стьюдента ).

2) Для проверки условия гомоскедастичности остатков с листа «Исходные данные» ячейки B1:B21 скопируйте в ячейку A1 нового листа, который назовите «Спирмен». В ячейку В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) (для вычисления )

В ячейку О24 введите формулу = СТЬЮДРАСПОБР(0,05;20-2) (для вычисления ).

3) Для проверки условия об отсутствии автокорреляции с листа «Регрессия» скопируйте столбец «Остатки» в ячейку А1 нового листа, который назовите «Автокорреляция».

В ячейки В2:В20 введите формулу массива {=(А2:А20-А3:А21)^2}.

В ячейку В22 введите формулу = СУММ(В2:В20).

В ячейки С2:С21 введите формулу массива {=(А2:А21)^2}.

В ячейку С22 введите формулу = СУММ(С2:С21).

В ячейку С23 введите формулу =В22/С22 (для вычисления DW).

4) Для проверки гипотезы о нормальном распределении остатков вернитесь на лист «Регрессия». Выберите в опциях меню Сервис → Анализ данных → Гистограмма → ОК. Заполните значения параметров окна следующим образом:

• Входной интервал – введите ссылки на ячейки С25:С45 (столбец Остатки листа «Регрессия» с названием);

• Интервал карманов – не заполняйте;

• Метки — установите флажок;

• Выходной диапазон – введите ссылку на Новый рабочий лист «Гистограмма»;

• Парето – оставьте пустым;

• Интегральный процент – оставьте пустым;

• Вывод графика – установите флажок.

Нажмите ОК. Перенесите гистограмму вниз и растяните ее.

5) Для проверки условия об отсутствии мультиколлинеарности откройте лист «Исходные данные», выберите в опциях меню Сервис → Анализ данных → Корреляция → ОК. Заполните значения параметров окна следующим образом:

• Входной интервал – ячейки В1:С21;

• Метки – установите флажок;

• Новый рабочий лист – «Мульти».

Нажмите ОК.

Скопируйте ячейку В3 в ячейку С2.

В ячейку А5 введите математическую формулу = МОПРЕД(B2:C3).

В ячейку А6 введите формулу =20-1-(1/6)*9*LOG(A5;10) (для нахождения хи-квадрат наблюдаемого).

В ячейку А7 введите формулу =ХИ2ОБР(0,05;1) (для нахождения хи-квадрат критического).

Приложение: Отчет о результатах вычислений и анализа