5.1 Регрессионный анализ в ms Excel

Часто для эконометрического моделирования используют регрессионный анализ в MS Excel.

Для этого необходимо выполнить следующую последовательность действий.

1. Необходимо проверить доступ к пакету анализа. В главном меню файла выполнить Сервис/Надстройка, установить флажок напротив Пакет анализа.

Рис. 5.1.1. Настройка пакета “Поиск решения”

Теперь Пакет анализа можно использовать в работе.

2. В главном меню выполнить Сервис/Анализ данных/Регрессия

Рис. 5.1.2. Настройка пакета “Регрессия”

3. Заполните появившееся диалоговое окно

Рис. 5.1.3. Заполнение диалогового окна

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

Входной интервал X - диапазон ячеек, содержащий данные объясняющих переменных.

Метки – флажок, указывающий на то, содержит ли первая строка название столбцов, содержащих данные Х и У.

Константа – ноль – флажок, указывающий на наличие/отсутствие свободного члена в уравнении.

Замечание: при наличии в уравнении константы (свободного члена) качество модели будет выше.

Уровень надежности – флажок, который определяет уровень надежности (при значении “95”, уровень значимости равен 5% ).

Выходной интервал – диапазон вывода на экран результатов регрессионного анализа (достаточно установить одну первую ячейку).

Новый рабочий лист/ Новая рабочая книга – результат регрессионного анализа выводится на новый рабочий лист или в новую рабочую книгу соответственно.

Регрессионный анализ в MS Excel также позволяет просмотреть графики остатков.

Пакет “Регрессия” удобен при построении множественной регрессии.

После того, как вы нажали на кнопку “OK”, на экране появляется ”ВЫВОД ИТОГОВ”.

Рис. 5.1.4. Результат регрессионного анализа

Здесь вы можете оценить коэффициент детерминации , F – статистику Фишера, а также значение коэффициентов регрессии и их t –статистики и сделать по их значениям качественные выводы.

Y – пересечение – это значение свободного члена (a) в полученной модели.

Переменная Х1 – это значение коэффициента b при переменной х.

Р – значение и Значимость F – вероятности, которые характеризует соответственно значимость коэффициента регрессии (если P<0,05, то коэффициент значим) и значимость модели в целом (если F<0,05, то модель в целом значима).

Задание: какие выводы вы можете сделать о значимости коэффициентов регрессии и о значимости модели в целом по полученным данным? Соответствует ли полученная модель исходным статистическим данным?

Замечание: чтобы использовать регрессионный анализ, необходимо привести модель к линейному виду. В случае, если вы планируете построить регрессию линейную по параметрам (например, ), требуется привести исходные статистические данные для х и у к необходимому виду. То есть, для данного примера в окно “Входной интервал Y” необходимо ввести значения , а в окно “Входной интервал Х” необходимо ввести диапазон данных, соответствующий х и .

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