Построение модели парной линейной регрессии средствами

MS Office Excel

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

Для вызова команды «Анализ данных» необходимо нажать на соответствующую кнопку в меню «Данные»:

В меню «Анализ данных» необходимо выбрать инструмент анализа «Регрессия»:

В диалоговом окне «Регрессия» возможно установить следующие параметры анализа:

1. «Входной интервал Y» - ссылка на диапазон значений результативной переменной. Диапазон должен состоять из одного столбца.

2. «Входной интервал X» - ссылка на диапазон значений факторной переменной.

3. «Метки» - необходимо установить флажок, если выделенный диапазон содержит заголовки столбцов.

4. «Уровень надежности» - необходимо установить флажок, если доверительные интервалы для параметров модели регрессии должны быть дополнительно рассчитаны для вероятности отличной от 95%, применяемой по умолчанию. Обычно используют уровень надежности 99% для проверки нулевых гипотез при уровне значимости

5. «Константа – ноль» - необходимо установить флажок, если теоретическое обоснование модели предполагает

6. «Параметры вывода» - необходимо выбрать расположение таблицы с результатами регрессионного анализа.

7. «Остатки» - при установке флажка в результаты регрессии будут включены значения регрессионных остатков.

8. «Стандартизированные остатки» - при установке флажка в результаты регрессии будут включены стандартизированные значения регрессионных остатков (со средней равной 0 и дисперсией равной 1).

9. «График остатков» - при установке флажка будет показана диаграмма рассеяния для значений регрессионных остатков и факторной переменной. Данный график используется для визуальной проверки предпосылок МНК о гомоскедастичности остатков и экзогенности факторной переменной.

10. «График подбора» - при установке флажка будет показан график зависимости фактических и теоретических значений результативной переменной. Данный график используется для визуальной оценки качества построенной модели.

11. «График нормальной вероятности» - при установке флажка будут показаны таблица и график распределения результативной переменной.

Пример 2.13. Проведем анализ данных, характеризующих среднедушевые денежные доходы населения и среднемесячный оборот розничной торговли на душу населения регионов Центрального федерального округа Российской Федерации за 2010 г. с использованием инструмента «Регрессия» пакета анализа MS Office Excel:

Полученная в результате использования инструмента «Регрессия» таблица «Вывод итогов» состоит из следующих элементов:

1. Таблица «Регрессионная статистика»

Для модели парной линейной регрессии доступна интерпретация следующих показателей:

Обозначение в MS Office Excel	Название показателя	Формула расчета	Интерпретация
Множественный R	Линейный коэффициент множественной корреляции		Для парной линейной зависимости равен модулю линейного коэффициента корреляции (Пример 2.2)
R-квадрат	Коэффициент детерминации		Пример 2.6
Стандартная ошибка	Стандартная ошибка регрессии		Пример 2.12
Наблюдения	Число наблюдений, объем выборки

2. Таблица «Дисперсионный анализ»

Для модели парной линейной регрессии доступна интерпретация всех показателей:

Обозначение в MS Office Excel	Название показателя	Формула расчета	Интерпретация
Регрессия:
- df	Число степеней свободы суммы квадратов отклонений, объясненных регрессией (факторной)		Пример 2.7
- SS	Сумма квадратов отклонений, объясненных регрессией (факторная)		Пример 2.6
- MS	Объясненная (факторная) дисперсия на одну степень свободы		Пример 2.7
Остаток:
- df	Число степеней свободы остаточной суммы квадратов отклонений		Пример 2.7
- SS	Остаточная сумма квадратов отклонений		Пример 2.6
- MS	Остаточная дисперсия на одну степень свободы		Пример 2.7
Итого:
- df	Число степеней свободы общей суммы квадратов отклонений		Пример 2.7
- SS	Общая сумма квадратов отклонений		Пример 2.6
F	F -критерий Фишера		Пример 2.7
Значимость F	p -значение для F-критерий Фишера	-	Пример 2.7

3. Таблица «Оценка параметров»

Для модели парной линейной регрессии доступна интерпретация всех показателей:

Обозначение в MS Office Excel	Название показателя	Формула расчета	Интерпретация
Y-пересечение
Коэффициенты	Оценка параметра модели , свободный член		Пример 2.4
Стандартная ошибка	Стандартная ошибка оценки параметра		Пример 2.8
t-статистика	t -критерий Стьюдента для параметра		Пример 2.8
P-Значение	p -значение для t -критерия для параметра	-	Пример 2.8
Нижние 95%	Нижняя граница 95% доверительного интервала параметра		Пример 2.11
Верхние 95%	Верхняя граница 95% доверительного интервала параметра		Пример 2.11
Нижние 99,0%	Нижняя граница 99% доверительного интервала параметра		Пример 2.11
Верхние 99,0%	Верхняя граница 99% доверительного интервала параметра		Пример 2.11
Переменная X 1
Коэффициенты	Оценка параметра модели , коэффициент регрессии		Пример 2.4
Стандартная ошибка	Стандартная ошибка оценки параметра		Пример 2.8
t-статистика	t -критерий Стьюдента для параметра		Пример 2.8
P-Значение	p -значение для t -критерия для параметра	-	Пример 2.8
Нижние 95%	Нижняя граница 95% доверительного интервала параметра		Пример 2.11
Верхние 95%	Верхняя граница 95% доверительного интервала параметра		Пример 2.11
Нижние 99,0%	Нижняя граница 99% доверительного интервала параметра		Пример 2.11
Верхние 99,0%	Верхняя граница 99% доверительного интервала параметра		Пример 2.11

Значения показателей, полученные с использованием инструмента «Регрессия» пакета анализа MS Office Excel совпадают со значениями, полученными нами ранее при самостоятельных расчетах, с учетом ошибок округления.

Рассмотрим графики, построенные с использованием инструмента «Регрессия» пакета анализа MS Office Excel:

1. График остатков:

Рис. 2.8.1. График остатков модели регрессии оборота розничной торговли на душу населения по величине среднедушевых денежных доходов населения

Вид графика остатков говорит нам о том, что:

- величина остатков равномерно распределена вокруг нулевого среднего значения (то есть остатки гомоскедастичны);

- значения остатков не зависят от значений факторной переменной.

2. График подбора:

Рис. 2.8.2. График наблюдаемых и предсказанных значений оборота розничной торговли на душу населения

Вид графика подбора говорит нам о том, что:

- расчетные (предсказанные) значения результативной переменой незначительно отличаются от фактических (наблюдаемых) значений;

- значения остатков (разность между столбцами) не зависят от значений факторной переменной.

Ранее мы говорили о том, что для корректного использования параметрических критериев и построения доверительных интервалов необходимо, чтобы ошибки регрессии подчинялись нормальному распределению. Для проверки соблюдения данной предпосылки оценивают гипотезу о нормальном распределении регрессионных остатков. При этом обычно используют критерии согласия Пирсона, Колмогорова-Смирнова и др.

Однако использование указанных критериев сопряжено с трудностями вычислений, при отсутствии специализированного программного обеспечения. Этого недостатка лишен тест Жарка-Бера при помощи которого проверяется гипотеза вида:

Если нулевая гипотеза верна, то величина:

имеет распределение с числом степеней свободы равным 2.

Величину называют коэффициентом асимметрии и рассчитывают как:

где – центральный момент регрессионных остатков порядка k.

Величину называют коэффициентом эксцесса и рассчитывают как:

Пример 2. 14. Используем тест Жарка-Бера для проверки гипотезы о нормальном распределении остатков модели регрессии оборота розничной торговли по доходам населения.

Значения регрессионных остатков получим при использовании инструмента «Регрессия» модуля «Анализ данных» MS Office Excel, установив флажок «Остатки».

Значения коэффициентов асимметрии и эксцесса определим используя инструмент «Описательная статистика» модуля «Анализ данных» MS Office Excel:

Рассчитаем значение статистики Жарка-Бера:

Табличное значение распределения с числом степеней свободы равным 2 при уровне значимости определим используя формулу «ХИ2ОБР» MS Office Excel:

Поскольку нулевая гипотеза о нормальном распределении ошибки регрессии не может быть отвергнута при заданном уровне значимости.

Следовательно, использование нами для оценки статистической значимости модели регрессии распределений Фишера и Стьюдента является корректным.

Поиск по сайту: