ЛАБОРАТОРНАЯ РАБОТА № 9 Линейная парная регрессия
Постановка задачи
Имеются данные о суточном объеме производства и количестве занятых работников для некоторой совокупности дней. В табл. 9.1 приведены данные для 10 дней.
Таблица 9.1
Количество
работников
|
7
|
6
|
7
|
8
|
9
|
9
|
9
|
9
|
6
|
9
|
Объем
производства
|
483
|
489
|
486
|
563
|
570
|
559
|
594
|
575
|
464
|
647
|
По представленным данным необходимо исследовать зависимость суточного объема производства (Y) от количества занятых работников (X). В качестве модели использовать выборочное уравнение линейной регрессии:
у=bx+a.
Требуется определить параметры уравнения линейной регрессии и выполнить статистический анализ полученного уравнения.
Определение параметров выборочного уравнения регрессии
Выделите ячейку А1 и поместите в центре имя независимой (объясняющей) переменной X, как показано на рис. 9.1.
Рис. 9.1
Выделите ячейку В1 и поместите в центре имя зависимой (объясняемой) переменной «Y».
В диапазон ячеек А2:А11 введите данные о количестве работников.
В диапазон ячеек В2:В11 введите данные об объеме производства.
Объедините две ячейки D1:E1 и поместите в центре общий вид уравнения регрессии у=bх+а.
Выделите ячейку D2 и поместите в центре имя коэффициента регрессии b.
Выделите ячейку Е2 и поместите в центре имя свободного члена уравнение регрессии «а».
Выделите ячейку D3 и вызовите мастер функций fx.
Из категории Статистические выберите функцию НАКЛОН.
В диалоговом окне Аргументы функции в поле Известные_значения_у задайте диапазон В2:В11 переменной Y, а в поле Известные_значения_х задайте диапазон А2:А11 переменной X. Щелкните на кнопке ОК. Прочтите результат, уменьшив разрядность до второго знака после запятой.
Выделите ячейку ЕЗ и вызовите мастер функций fx.
Из категории Статистические выберите функцию ОТРЕЗОК.
В диалоговом окне Аргументы функции в поле Известные_значения_у задайте диапазон В2:В11 переменной Y, а в поле Известные_значения_х задайте диапазон А2:А11 переменной X. Щелкните на кнопке ОК. Прочтите результат, уменьшив разрядность до второго знака после запятой.
Построение линии регрессии (линии тренда)
13. Щелкните на инструменте Мастер диаграмм на Стандартной панели инструментов и выберите тип диаграммы Точечная. Точечная диаграмма позволяет сравнить пары значений. Щелкните на кнопке Далее.
Выделите диапазон данных вместе с заголовками столбцов А1:В11 и щелкните на кнопке Далее.
На вкладке Заголовки введите:
Название диаграммы: Линейная регрессия
Ось X (категорий): X
Ось Y (значений): Y
Щелкните на кнопке Далее.
Установите переключатель Поместить диаграмму на листе в положение имеющемся. Щелкните на кнопке Готово.
Установите указатель мыши на любую точку ряда Y и щелкните правой кнопкой.
В контекстном меню выберите команду: Добавить линию тренда...
В диалоговом окне Линия тренда на вкладке Тип выберите Линейная (этот тип устанавливается по умолчанию).
Откройте вкладку Параметры, как показано на рис. 9.2.
Рис. 9.2
В области Название аппроксимирующей (сглаженной) кривой в текстовое поле введите вид линейной функции: у=bx+a.
Установите флажок Показывать уравнение на диаграмме.
Установите флажок Поместить на диаграмму величину достоверности аппроксимации (R^2), т.е. коэффициент детерминации. Щелкните на кнопке ОК.
Отформатируйте диаграмму и расположите ее, как показано на рис. 9.1.
Объясните полученные результаты.
Оценка значимости параметров регрессии
Предварительные расчеты
Скопируйте диапазон данных вместе с заголовками столбцов А1:В11 на Лист 2 в диапазон А1:В11, как показано на рис. 9.3.
Рис. 9.3
Скопируйте диапазон рассчитанных параметров регрессии D1:E3 на Лист 2 в диапазон D1:ЕЗ.
Выделите ячейку D5 и введите заголовок «Хср».
В ячейке D6 рассчитайте среднее значение переменной X. Установите разрядность два знака после запятой.
В ячейки G1:J1 введите следующие заголовки (рис. 9.3): G1: X^2; H1: (X-Xcp)^2; I1: bX+a; J1: (Y-bX-a)^2.
31. В ячейку G2 введите формулу:
=А2^2.
Скопируйте формулу на диапазон G3:G11. Установите разрядность два знака после запятой.
32. В ячейку Н2 введите формулу:
=(A2-$D$6)^2.
Скопируйте формулу на диапазон НЗ:Н11. Установите разрядность два знака после запятой.
33. В ячейку I2 введите формулу:
=$D$3*A2+$Е$3.
Скопируйте формулу на диапазон I3:I11. Установите разрядность два знака после запятой.
34. В ячейку J2 введите формулу:
=(В2-I2)^2.
Скопируйте формулу на диапазон J3:J11. Установите разрядность два знака после запятой.
В ячейках G12, Н12 и J12 подсчитайте суммы соответствующих столбцов. Установите разрядность два знака после запятой.
Выделите ячейку D8 и введите имя переменной n. Выровняйте по центру.
Выделите ячейку D9 и задайте имя переменой n, выполнив команду: Вставка→Имя→Присвоить...
В ячейку D9 введите объем выборки n=10.
В ячейку А14 введите заголовок «Дисперсия ошибок регрессии (необъясненная дисперсия)».
В ячейку I14 введите формулу: =J2/(n-2).
Установите разрядность два знака после запятой.
Расчет t-статистики коэффициента регрессии b
41. В ячейку А16 введите заголовок «Дисперсия параметра b».
42. В ячейку F16 введите формулу расчета: = I14/H12.
Установите разрядность два знака после запятой.
В ячейку А17 введите заголовок «Стандартное отклонение параметра b».
В ячейку F17 введите формулу расчета:
=KOPEHЬ(F16).
Установите разрядность два знака после запятой.
45. В ячейку А18 введите заголовок «t-статистика параметра b».
46. В ячейку F18 введите формулу расчета: = D3/F17.
Установите разрядность два знака после запятой.
Расчет t-статистики свободного члена а
В ячейку А20 введите заголовок «Дисперсия параметра а».
В ячейку F20 введите формулу расчета:
=I14*G12/(n*H12).
Установите разрядность два знака после запятой.
49. В ячейку А21 введите заголовок «Стандартное отклонение параметра а».
50. В ячейку F21 введите формулу расчета:
=KOPEHЬ(F20).
Установите разрядность два знака после запятой.
В ячейку А22 введите заголовок «t-статистика параметра а».
В ячейку F22 введите формулу расчета: =ЕЗ/F21.
Установите разрядность два знака после запятой.
Расчет критического значения
Выделите ячейку А24 и введите заголовок «Критическое значение t-статистики».
Выделите ячейку F24 и вызовите мастер функций fx.
Из категории Статистические выберите функцию СТЬЮДРАСПОБР и в диалоговом окне в поле Вероятность задайте значение уровня значимости а равным 0,05, а в поле Степени_свободы – число степеней свободы, равное n-2. Щелкните на кнопке ОК. Прочтите результат, уменьшив разрядность до второго знака после запятой.
Сделайте выводы о значимости параметров регрессии.
Проверка статистической значимости линейной связи (коэффициента детерминации)
В ячейку D11 введите заголовок «R^2» и выровняйте по центру.
В ячейку D12 введите значение коэффициента детерминации R2, равное 0,79 (см. график на рис. 9.1).
В ячейку А26 введите заголовок «F-статистика».
В ячейку F26 введите формулу расчета:
=D12*(n-2)/(1-D12).
Установите разрядность два знака после запятой.
Выделите ячейку А27 и введите заголовок «Критическое значение F-статистики».
Выделите ячейку F27 и вызовите мастер функций fx.
Из категории Статистические выберите функцию FPACПОБР и в диалоговом окне в поле Вероятность задайте значение уровня значимости а равным 0,05, в поле Степени_свободы 1 – число степеней свободы, равное 1 (здесь число степеней свободы равно числу независимых переменных m=1), а в поле Степени_свободы 2 число степеней свободы, равное n-2. Щелкните на кнопке ОК. Прочтите результат, уменьшив разрядность до второго знака после запятой.
Сделайте выводы о статистической значимости линейной связи.
Использование статистического пакета Анализ данных
65. Скопируйте диапазон данных вместе с заголовками столбцов А1:В11 на Лист 3 в диапазон А1:В11, как показано на рис. 9.4.
Рис. 9.4
66. Откройте пакет анализа данных с помощью команды: Сервис→Анализ данных... и в диалоговом окне из списка «Инструменты анализа» выберите инструмент Регрессия.
67. В диалоговом окне задайте следующие параметры:
Входной интервал Y: $В$2:$В$11.
Входной интервал X: $А$2:$А$11.
Выходной интервал: $D$1.
Щелкните на кнопке ОК.
Обратите внимание! Опция Регрессия выводит большинство показателей, которые мы до этого рассчитали с помощью одиночных функций и формул. На рис. 9.4 эти показатели выделены жирным шрифтом.
|
