Г. Курск

Введем фиктивные переменные

В результате получаем регрессионную функцию трех переменных , а результаты наблюдений можно записать как

Продолжение таблицы с данными:

Вводим эти данные в электронную таблицу. В А1-А4 вводим подписи “Y”, “X”, “Z1”, “Z2”. В ячейки В1-AF4 вводим данные из таблицы. Данные вводятся в 4 строки и 31 столбец, не считая заголовки. Посмотрим, имеются ли мультиколлинеарные факторы. Для этого находим матрицу коэффициентов интеркорреляции. Вызываем меню «Сервис/Анализ данных» и выбираем пункт «Корреляция». В появившемся окне в поле «Входной интервал» задаем ссылку на данные – факторы: А2-AF4. Указываем группирование «По строкам». Ставим флажок в «Метки в первом столбце» (так как в ссылках на таблицу указаны подписи строк). В области «Параметры вывода» ставим флажок напротив «Выходной интервал» и напротив в поле даем ссылку на какую-либо ячейку, откуда будет осуществляться вывод данных, например А7 и нажимаем «ОК». Ставим курсор в С8 и делаем ссылку «=В9», в D8 – ссылку «=В10», в D9 – ссылку «=С10». Ставим курсор в ячейку G7, вводим заголовок « =» и в H7 ставим курсор и задаем функцию «МОПРЕД» с аргументом «Массив» - ссылкой на B8:D10. Результат - 0,7352005, близок к единице, что говорит о достаточно слабой зависимости факторов между собой. Все факторы оставляем в регрессионной модели. вычисляем параметры регрессии. Ставим курсор в А12 и даем заголовок «Параметры регрессии», а затем переводим курсор в А13 и вызываем функцию «ЛИНЕЙН», аргументы которой: «Изв_знач_у» - B1:AF1, «Изв_знач_х» - B2:AF4, «Константа» = 1, «Стат» = 1. Далее обводим 5 строк и 4 столбца А13-D17, и нажимаем F2 и Ctrl+Shift+Enter. В первой строке – коэффициенты регрессионного уравнения, следовательно, уравнение регрессии есть . Вторая строка – стандартные ошибки коэффициентов. Все они меньше самих коэффициентов, это значит, что коэффициенты значимы.

Проверим модель на адекватность. Видно, что F-критерий регрессионной модели равен 63,703. Вычислим критическое значение статистики. Вводим в ячейку G13 подпись «F-критическое» а в Н13 вводим функцию FРАСПОБР, имеющую аргументы: «Вероятность» – 0,05 (произвольно примем по умолчанию), «Степени_свободы_1» - 3 (число независимых переменных равно трем - ). Аргументом «Степени_свободы_2» служит число, показанное в ячейке В16 (в данном примере – 27). Видно, что F-статистика больше ее критического значения, поэтому модель адекватна. Построим уравнение регрессии для каждого города в отдельности. Вводим в G15 подпись «Воронеж», в Н15 подпись «а =», в I15 – формулу «=C13», в Н16 – «b=», в I16 - «=D13+B13». Уравнение для Воронежа есть . Аналогично рассчитываем для Липецка. Вводим в G17 подпись «Липецк», в Н17 подпись «а =», в I17 – формулу «=C13», в Н18 – «b=», в I18 - «=D13+А13». Результат . Для Курска вводим в G19 подпись «Курск», в Н19 подпись «а =», в I19 – формулу «=C13», в Н20 – «b=», в I20 - «=D13». Результат .

Задание на самостоятельную работу (для всех вариантов)

Анализируется зависимость объема производства Y (в денежных единицах, млн. руб. в неделю) от численности занятых рабочих X (тыс. чел.) на 35 предприятиях региона. При этом, при построении регрессионной модели желательно учесть принадлежность предприятия к одной из трех отраслей: машиностроения, легкой промышленности, пищевой промышленности.

Машиностроение:

Легкая промышленность:

Пищевая промышленность:

Построить уравнение множественной регрессии с учетом фактора – отрасли.