Лабораторная работа № 5
Тема: Модель множественной регрессии
Цель: рассчитать параметры и основные характеристики уравнения линейной множественной регрессии и изучить способ проверки значимости уравнения регрессии, используя инструмент «Регрессия» модуля «Анализ данных».
Теоретические сведения:
Табличный процессор Excel содержит модуль Анализ данных. Этот модуль позволяет выполнить статистический анализ выборочных данных (построение гистограмм, вычисление числовых характеристик и т. д.). Режим работы Регрессия этого модуля осуществляет вычисление коэффициентов линейной множественной регрессии с 
переменными, построение доверительных интервалов и проверку значимости уравнения регрессии в целом.
Ход работы:
Постановка задачи.
Приведены данные о сменной добыче угля на одного рабочего (переменная Y – измеряется в тоннах), мощности пласта (переменная X1 – измеряется в метрах) и уровнем механизации работ в шахте (переменная X2 – измеряется в процентах), характеризующие процесс добычи угля в 10 шахтах.
Рис. 6.1
Требуется: предполагая, что между переменными У, Х1, Х2 существует линейная зависимость, найти аналитическое выражение для этой зависимости, т. е. построить уравнение линейной множественной регрессии.
Запустите Excel. Внесите исходные данные начиная с ячейки А1 (см. Рис.6.1). Для столбцов Х1,Х2,У рассчитайте среднее значение, дисперсию, среднее квадратическое отклонение с помощью соответствующих функций – СРЗНАЧ, ДИСПР, СТАНДОТКЛОНП. Во вспомогательной таблице произведите расчеты следующих характеристик по известным вам формулам и функциям (коэффициенты парной корреляции - КОРРЕЛ)
,
-стандартизованные коэффициенты
, 
- формулы связи между коэффициентами регрессии и стандартизованными коэффициентами
- свободный член уравнения множественной регрессии
- средний коэффициент эластичности
- коэффициент множественной корреляции
- общий критерий Фишера
, 
- частные критерии Фишера
Таблица 6.1
Наименование | Обозначение | Числовое значение (рассчитать) |
Коэффициент парной корреляции (у и х1) | ryx1 | |
Коэффициент парной корреляции (у и х2) | ryx2 | |
Коэффициент парной корреляции (х1 и х2) | rx1x2 | |
Коэффициенты уравнения множественной регрессии в стандартизованном масштабе | β1 | |
β2 | ||
Коэффициенты уравнения множественной регрессии в естественной форме | b1 | |
b2 | ||
Свободный член уравнения множественной регрессии | a | |
Коэффициенты эластичности | Эух1 | |
Эух2 | ||
Частные F-критерии Фишера (фактические значения) | Fх1,факт | |
Fх2,факт | ||
Множественный коэффициент корреляции | Rух1х2 | |
Множественный коэффициент детерминации | R2 | |
Общий F-критерий Фишера (фактическое значение) | Fобщ |
Запишите уравнение множественной регрессии в стандартизованном масштабе и естественной форме. Вызовите режим Регрессия модуля Анализ данных. Для этого выполните следующие действия:
- выберите в меню Сервис – команду Надстройки
- в окне Надстройки установите флажок у пункта «Анализ данных» (если Анализ данных не установлен);
- выполните команду Сервис-Анализ данных
- в списке режимов работы модуля «Анализ данных» выберите режим Регрессия и щелкните на кнопке ОК.
6. После вызова режима регрессия на экране появляется диалоговое окно (см. рис. 6.2), в котором задаются следующие параметры:
Рис. 6.2. Диалоговое окно режима Регрессия
Входной интервал У – вводится диапазон адресов ячеек, содержащих значения у Входной интервал Х – вводится диапазон адресов ячеек, содержащих значения независимых переменных. Метки – включается, если первая строка во входном диапазоне содержит заголовок. Уровень надежности – при включении этого параметра задается надежность
при построении доверительных интервалов. Константа-ноль – при включении этого параметра коэффициент а=0. Выходной интервал – при включении активизируется поле, в которое необходимо ввести адрес левой верхней ячейки выходного диапазона, который содержит ячейки с результатами вычислений режима Регрессия. Новый рабочий лист – при включении этого параметра открывается новый лист, в который начиная с ячейки А1 вставляются результаты работы режима Регрессия. Новая рабочая книга - при включении этого параметра открывается новая книга на первом листе которой начиная с ячейки А1 вставляются результаты работы режима Регрессия. Остатки – при включении вычисляется столбец, содержащий невязки (отклонение У фактического от У теоретического) 
. Стандартизованные остатки – при включении вычисляется столбец, содержащий стандартизованные остатки. График остатков – при включении выводятся точечные графики невязки 
, в зависимости от значений переменных 
. Количество графиков равно числу 
переменных 
. График подбора – при включении выводятся точечные графики предсказанных по построенной регрессии значений 
от значений переменных 
. Количество графиков равно числу 
переменных 
. Результаты работы приводятся на рис. 6.3-6.5 
Рис. 6.3. Результаты работы режима Регрессия
Дадим краткую интерпретацию показателям, значения которых вычисляются в режиме Регрессия. Первоначально рассмотрим показатели, объединенные названием Регрессионная статистика (см. рис.24).
Множественный 
- корень квадратный из коэффициента детерминации.
квадрат – коэффициент детерминации 
.
Нормированный 
квадрат – приведенный коэффициент детерминации 
Стандартная ошибка – оценка 
для среднеквадратического отклонения 
.
Наблюдения – число наблюдений 
.
Перейдем к показателям, объединенных названием Дисперсионный анализ (см. рис. 24).
Столбец 
- число степеней свободы. Для строки Регрессия показатель равен числу независимых переменных m=2, для строки Остаток - равен n-m-1; для строки Итого – равен n-1.
Столбец SS – сумма квадратов отклонений.
Столбец 
дисперсии, вычисленные по формуле
,
т. е. дисперсия на одну степень свободы.
Столбец 
– значение 
, равное 
критерию Фишера.
Столбец значимость 
- значение уровня значимости, соответствующее вычисленной величине 
критерия. Если вероятность меньше уровня значимости 
(обычно 
), то построенная регрессия является значимой..
Перейдем к следующей группе показателей, объединенных в таблице, показанной на рис. 6.4.
Рис.6.4. Продолжение результатов работы режима Регрессия
Столбец Коэффициенты – вычисленные значения коэффициентов а, b1,b2 расположенных сверху-вниз.
Столбец Стандартная ошибка – значения случайных ошибок ma mb1 mb2
Столбец 
статистика – значения статистик Стьюдента, ta, tb1, tb2.
Столбец Р – значение – содержит вероятности случайных событий 
, где 
случайная величина, подчиняющаяся распределению Стьюдента с 
степенями свободы. Если эта вероятность меньше уровня значимости 
, то принимается гипотеза о значимости соответствующего коэффициента регрессии.
Из рисунка 6.5 видно, что значимым коэффициентом является только коэффициент 
.
Столбцы Нижние 95% и Верхние 95% - соответственно нижние и верхние интервалы для оцениваемых коэффициентов.
Перейдем к следующей группе показателей, объединенных в таблице, показанной на рис. 6.5.
Рис. 6.5. Продолжение результатов работы режима Регрессия
Столбец Наблюдение – содержит номера наблюдений.
Столбец Предсказанное У – значения 
, вычисленные по построенному уравнению регрессии.
Столбец Остатки – значения невязок (отклонений) 
Рис 6.6 Диалоговое окно режима Корреляция
Сравните результаты, полученные с вычислениями по формулам и использованием режима «Анализ данных». Сформулируйте вывод.
