СМЫСЛ
Онлайн помощь студентам
Множественная регрессия в Excel.
Статья с подробным разбором.
Корреляционная матрица. Мультиколлинеарность.
Инструмент "Регрессия" из надстройки "Анализ анализ данных". Оценка коэффициентов уравнения множественной регрессии.
Оценка уравнения множественной регрессии с помощью функции ЛИНЕЙН
Видео
Для анализа выбраны годовые данные временных рядов. Пусть Y - зависимый экономический показатель (эндогенная переменная), X1 и X2 - независимые экономические показатели (экзогенные переменные). Данные представлены за 32 года (32 наблюдения).
Строим корреляционную матрицу. Для этого используем надстройку «Анализ данных». Выбираем в диалоговом окне «Анализа данных» - корреляция.
Инструмент "Регрессия" из надстройки "Анализ анализ данных". Оценка коэффициентов уравнения множественной регрессии.
Оценка уравнения множественной регрессии с помощью функции ЛИНЕЙН
Видео
Для анализа выбраны годовые данные временных рядов. Пусть Y - зависимый экономический показатель (эндогенная переменная), X1 и X2 - независимые экономические показатели (экзогенные переменные). Данные представлены за 32 года (32 наблюдения).
Строим корреляционную матрицу. Для этого используем надстройку «Анализ данных». Выбираем в диалоговом окне «Анализа данных» - корреляция.
Надстройка "Анализ данных"
Далее заполняем диалоговое окно:
Диалоговое окно "Корреляция"
Получим корреляционную матрицу:
Корреляционная матрица
Наблюдается тесная связь между переменной Y и Х1, Y и X2, так как попарные коэффициенты корреляции составляют 0,8602 и 0,7479 соответственно. Это выше 0,7 – связь достаточно тесная.
Межу объясняющими переменными Х1 и Х2 коэффициент составляет 0,6311. Он ниже других, но имеет значение больше 0,6. Значит между объясняющими переменными, может быть корреляционная связь (явление мультиколлинеарности). Для обнаружения мультиколлинеарности есть ряд критериев, например VIF-критерий.
Парные коэффициенты корреляции можно ещё найти с помощью статистической функции КОРРЕЛ.
Заполняем диалоговое окно статистической функции КОРРЕЛ.
Межу объясняющими переменными Х1 и Х2 коэффициент составляет 0,6311. Он ниже других, но имеет значение больше 0,6. Значит между объясняющими переменными, может быть корреляционная связь (явление мультиколлинеарности). Для обнаружения мультиколлинеарности есть ряд критериев, например VIF-критерий.
Парные коэффициенты корреляции можно ещё найти с помощью статистической функции КОРРЕЛ.
Заполняем диалоговое окно статистической функции КОРРЕЛ.
Диалоговое окно функции КОРЕЛЛ
Получаем тоже значение, что и корреляционной матрице для Y и X1: 0,860227
Аналогично по отдельности можем найти и другие парные коэффициенты корреляции.
Строим множественную регрессию. Для этого в надстройке «Анализ данных» выбираем регрессия.
Заполняем диалоговое окно:
Аналогично по отдельности можем найти и другие парные коэффициенты корреляции.
Строим множественную регрессию. Для этого в надстройке «Анализ данных» выбираем регрессия.
Заполняем диалоговое окно:
Диалоговое окно "регрессия"
Выбираем уровень надёжности 98%. Для коэффициентов регрессии будет найден доверительный интервал с уровнем с доверительной вероятностью 98%. Так же выбираем остатки, график остатков. Эти данные необходимы нам для дальнейшего анализа во второй части.
Получим.
Получим.
Результат регрессии
Проведём анализ полученных данных.
Таблица R^2
Здесь выводятся: множественный коэффициент корреляции, коэффициент детерминации (R-квадрат), скорректированный коэффициент детерминации (нормированный R-квадрат), стандартная ошибка регрессии (S).
R-квадрат = RSS/TSS
Стандартная ошибка = (ESS/(n-m-1))^0.5
ESS (error sum of squares) – остаточная сумма квадратов.
Построенное уравнение регрессии объясняет изменение результативного показателя Y на 80,99%. Остальные 10,01% приходятся на случайные факторы и факторы не включённые в модель.
R-квадрат = RSS/TSS
Стандартная ошибка = (ESS/(n-m-1))^0.5
ESS (error sum of squares) – остаточная сумма квадратов.
Построенное уравнение регрессии объясняет изменение результативного показателя Y на 80,99%. Остальные 10,01% приходятся на случайные факторы и факторы не включённые в модель.
Таблица дисперсионного анализа
Здесь даны столбцы: со степенями свободы (df); суммой квадратов (SS), суммой квадратов, делённых на степень свободы (MS=SS/df). Так же дано значение F-критерия Фишера и вероятность принятия нулевой гипотезы о равенстве коэффициента детерминации нулю (Значимость F).
TSS (total sum of squares) – общая сумма квадратов отклонений зависимой переменной от средней или вся дисперсия зависимой переменной;
RSS (regression sum of squares) – сумма квадратов, обусловленная регрессией или объясненная часть всей дисперсии;
ESS (error sum of squares) – остаточная сумма квадратов, характеризующая влияние неучтенных факторов или необъясненная дисперсия.
F= (ESS/m)/(RSS/(n-m-1)), n=32 m=2
Значимость F= 3,51562998617576E-11 <<<<0.01. Не можем принять нулевую гипотезу, принимаем альтернативную. Коэффициент детерминации отличен от нуля. Уравнение значимо в целом.
Так же можно сравнить наблюдаемое значение F-критерия с критическим. Берём уровень значимости 0,01, число степеней свободы 2 и 29. И с помощью статистической функции F.ОБР.ПХ находим критическое значение распределения Фишера
TSS (total sum of squares) – общая сумма квадратов отклонений зависимой переменной от средней или вся дисперсия зависимой переменной;
RSS (regression sum of squares) – сумма квадратов, обусловленная регрессией или объясненная часть всей дисперсии;
ESS (error sum of squares) – остаточная сумма квадратов, характеризующая влияние неучтенных факторов или необъясненная дисперсия.
F= (ESS/m)/(RSS/(n-m-1)), n=32 m=2
Значимость F= 3,51562998617576E-11 <<<<0.01. Не можем принять нулевую гипотезу, принимаем альтернативную. Коэффициент детерминации отличен от нуля. Уравнение значимо в целом.
Так же можно сравнить наблюдаемое значение F-критерия с критическим. Берём уровень значимости 0,01, число степеней свободы 2 и 29. И с помощью статистической функции F.ОБР.ПХ находим критическое значение распределения Фишера
Критическое значение распределения Фишера
F.ОБР.ПХ(0,01;2;29) = 5,420445
Наблюдаемое значение оказалось выше критического (61,766> 5,420445), отвергаем нулевую гипотезу – уравнение значимо в целом.
Наблюдаемое значение оказалось выше критического (61,766> 5,420445), отвергаем нулевую гипотезу – уравнение значимо в целом.
Таблица коэффициентов уравнения
В данной таблице выводятся столбцы: коэффициентов регрессии; стандартных ошибок коэффициентов регрессии; t-статистик; вероятности принятия нулевой гипотезы для коэффициентов (Р-значение); доверительные интервалы для коэффициентов регрессии (95% интервал считается по умолчанию, 98% - мы задали сами в диалоговом окне регрессия).
Запишем полученное уравнение множественной регрессии.
Запишем полученное уравнение множественной регрессии.
Уравнение множественной регрессии
Если P-значение < 0.05, то коэффициент регрессии можем принять значимым при 5% уровне.
Т. е. отвергаем нулевую гипотезу при 5% уровне значимости. Т. е. все коэффициенты полученного уравнения статистически значимы при 5% уровне, т. к. для них всех р-значение меньше 0,05.
Так же можем определить значимость коэффициентов, сравнивая наблюдаемые значения t-статистик с критическими. Находим критическое значения с помощью статистической функции СТЬЮДЕНТ.ОБР.2Х.
Т. е. отвергаем нулевую гипотезу при 5% уровне значимости. Т. е. все коэффициенты полученного уравнения статистически значимы при 5% уровне, т. к. для них всех р-значение меньше 0,05.
Так же можем определить значимость коэффициентов, сравнивая наблюдаемые значения t-статистик с критическими. Находим критическое значения с помощью статистической функции СТЬЮДЕНТ.ОБР.2Х.
Тест на нормальность остатков для модели Критическое значение t-статистики Стьюдента
СТЬЮДЕНТ.ОБР.2Х(0,05;29)= 2,04523
Наблюдаемые значения t-статистик из таблицы, выше критического значения при 5% уровне значимости, отвергаем нулевую гипотезу. Коэффициенты регрессии являются статистически значимыми.
Так же можем получить расчёт для множественной регрессии, используя статистическую функцию Excel – ЛИНЕЙН.
Для неё берётся число столбцов, равное количеству объясняющих переменных плюс один (в нашем случае три), а количество строк всегда пять.
Выделяем диапазон: E37:G41. Ставим знак равно и находим функцию ЛИНЕЙН в статистических функциях.
Заполняем диалоговое окно:
Наблюдаемые значения t-статистик из таблицы, выше критического значения при 5% уровне значимости, отвергаем нулевую гипотезу. Коэффициенты регрессии являются статистически значимыми.
Так же можем получить расчёт для множественной регрессии, используя статистическую функцию Excel – ЛИНЕЙН.
Для неё берётся число столбцов, равное количеству объясняющих переменных плюс один (в нашем случае три), а количество строк всегда пять.
Выделяем диапазон: E37:G41. Ставим знак равно и находим функцию ЛИНЕЙН в статистических функциях.
Заполняем диалоговое окно:
Диалоговое окно функции ЛИНЕЙН
Нажимаем клавиши
Ctrl + Shift + Enter
Получаем результат:
Результат ЛИНЕЙН
t-статистики определим делением коэффициента регрессии на стандартную ошибку.
Получим следующую таблицу.
Получим следующую таблицу.
Таблица с результатами расчётов
Кроме t-статистики в этой таблице считает функция ЛИНЕЙН. t-статистики мы подсчитали сами дополнительно.
Принципиальное отличие функции ЛИНЕЙН от регрессии в пакете «Анализ данных» заключается в том, что при изменении самих данных, все значения в таблице ЛИНЕЙН пересчитываются автоматически сразу. А при работе с пакетом «анализа данных» надо запускать процесс ещё раз, т. е. вызывать диалоговое окно, заполнять его и нажимать «ок».
В следующей части мы рассмотрим тестирование полученной модели на наличие автокорреляции остатков и на наличие гетероскедастичности.
Файл с решением:
https://vk.com/wall-216984375_128
https://t.me/smys_l/101
Принципиальное отличие функции ЛИНЕЙН от регрессии в пакете «Анализ данных» заключается в том, что при изменении самих данных, все значения в таблице ЛИНЕЙН пересчитываются автоматически сразу. А при работе с пакетом «анализа данных» надо запускать процесс ещё раз, т. е. вызывать диалоговое окно, заполнять его и нажимать «ок».
В следующей части мы рассмотрим тестирование полученной модели на наличие автокорреляции остатков и на наличие гетероскедастичности.
Файл с решением:
https://vk.com/wall-216984375_128
https://t.me/smys_l/101
Заявка на услуги
Укажите наиболее удобный для ВАС способ связи
и с Вами свяжутся в ближайшее время
и с Вами свяжутся в ближайшее время
Нажимая на кнопку, Вы соглашаетесь на обработку персональных данных в соответствии с Условиями.
- Имя - "" (имя переменной не требуется)
- Поле в одну строку - myvk
- Телефон - myphone
- Телефон - mywhatsapp
- Поле в одну строку - mytelegram
- E-mail - myemail
Вконтакте
Телефон
WhatsApp
Telegram
E-mail
