Тема: Модель множественной регрессии

Лабораторная работа № 5

Тема: Модель множественной регрессии

Цель: рассчитать параметры и основные характеристики уравнения линейной множественной регрессии и изучить способ проверки значимости уравнения регрессии, используя инструмент «Регрессия» модуля «Анализ данных».

Теоретические сведения:

Табличный процессор Excel содержит модуль Анализ данных. Этот модуль позволяет выполнить статистический анализ выборочных данных (построение гистограмм, вычисление числовых характеристик и т. д.). Режим работы Регрессия этого модуля осуществляет вычисление коэффициентов линейной множественной регрессии с переменными, построение доверительных интервалов и проверку значимости уравнения регрессии в целом.

Ход работы:

Постановка задачи.

Приведены данные о сменной добыче угля на одного рабочего (переменная Y – измеряется в тоннах), мощности пласта (переменная X1 – измеряется в метрах) и уровнем механизации работ в шахте (переменная X2 – измеряется в процентах), характеризующие процесс добычи угля в 10 шахтах.

Рис. 6.1

Требуется: предполагая, что между переменными У, Х1, Х2 существует линейная зависимость, найти аналитическое выражение для этой зависимости, т. е. построить уравнение линейной множественной регрессии.

1.  Запустите Excel. Внесите исходные данные начиная с ячейки А1 (см. Рис.6.1).

2.  Для столбцов Х1,Х2,У рассчитайте среднее значение, дисперсию, среднее квадратическое отклонение с помощью соответствующих функций – СРЗНАЧ, ДИСПР, СТАНДОТКЛОНП.

3.  Во вспомогательной таблице произведите расчеты следующих характеристик по известным вам формулам и функциям (коэффициенты парной корреляции - КОРРЕЛ)

,-стандартизованные коэффициенты

, - формулы связи между коэффициентами регрессии и стандартизованными коэффициентами

- свободный член уравнения множественной регрессии

- средний коэффициент эластичности

- коэффициент множественной корреляции

- общий критерий Фишера

, - частные критерии Фишера

Таблица 6.1

Наименование	Обозначение	Числовое значение (рассчитать)
Коэффициент парной корреляции (у и х1)	ryx1
Коэффициент парной корреляции (у и х2)	ryx2
Коэффициент парной корреляции (х1 и х2)	rx1x2
Коэффициенты уравнения множественной регрессии в стандартизованном масштабе	b1
b2
Коэффициенты уравнения множественной регрессии в естественной форме	b1
b2
Свободный член уравнения множественной регрессии	a
Коэффициенты эластичности	Эух1
Эух2
Частные F-критерии Фишера (фактические значения)	Fх1,факт
Fх2,факт
Множественный коэффициент корреляции	Rух1х2
Множественный коэффициент детерминации	R2
Общий F-критерий Фишера (фактическое значение)	Fобщ

4.  Запишите уравнение множественной регрессии в стандартизованном масштабе и естественной форме.

5.  Вызовите режим Регрессия модуля Анализ данных. Для этого выполните следующие действия:

- выберите в меню Сервис – команду Надстройки

- в окне Надстройки установите флажок у пункта «Анализ данных» (если Анализ данных не установлен);

- выполните команду Сервис-Анализ данных

- в списке режимов работы модуля «Анализ данных» выберите режим Регрессия и щелкните на кнопке ОК.

6. После вызова режима регрессия на экране появляется диалоговое окно (см. рис. 6.2), в котором задаются следующие параметры:

Рис. 6.2. Диалоговое окно режима Регрессия

1)  Входной интервал У – вводится диапазон адресов ячеек, содержащих значения у

2)  Входной интервал Х – вводится диапазон адресов ячеек, содержащих значения независимых переменных.

3)  Метки – включается, если первая строка во входном диапазоне содержит заголовок.

4)  Уровень надежности – при включении этого параметра задается надежность при построении доверительных интервалов.

5)  Константа-ноль – при включении этого параметра коэффициент а=0.

6)  Выходной интервал – при включении активизируется поле, в которое необходимо ввести адрес левой верхней ячейки выходного диапазона, который содержит ячейки с результатами вычислений режима Регрессия.

7)  Новый рабочий лист – при включении этого параметра открывается новый лист, в который начиная с ячейки А1 вставляются результаты работы режима Регрессия.

8)  Новая рабочая книга - при включении этого параметра открывается новая книга на первом листе которой начиная с ячейки А1 вставляются результаты работы режима Регрессия.

9)  Остатки – при включении вычисляется столбец, содержащий невязки (отклонение У фактического от У теоретического) .

10)  Стандартизованные остатки – при включении вычисляется столбец, содержащий стандартизованные остатки.

11)  График остатков – при включении выводятся точечные графики невязки , в зависимости от значений переменных . Количество графиков равно числу переменных .

12)  График подбора – при включении выводятся точечные графики предсказанных по построенной регрессии значений от значений переменных . Количество графиков равно числу переменных .

6.  Результаты работы приводятся на рис. 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. Продолжение результатов работы режима Регрессия

Столбец Наблюдение – содержит номера наблюдений.

Столбец Предсказанное У – значения , вычисленные по построенному уравнению регрессии.

Столбец Остатки – значения невязок (отклонений)

7.  Для расчета линейных коэффициентов парной корреляции используйте инструмент анализа данных Корреляция. Для этого: в главном меню последовательно выберите пункты Сервис - Анализ данных - Корреляция. Щелкните по кнопке ОК. Заполните диалоговое окно ввода данных и параметров вывода так, как показано на рис 6.6.

Рис 6.6 Диалоговое окно режима Корреляция

8.  Сравните результаты, полученные с вычислениями по формулам и использованием режима «Анализ данных». Сформулируйте вывод.