Глава 5. Построение регрессионных моделей с использованием табличного процессора EXCEL

5.1. Построение парного уравнения регрессии с помощью «Мастера диаграмм»

С помощью программной надстройки «Мастер диаграмм» табличного процессора EXCEL можно изобразить поле корреляции и построить линейную, степенную, логарифмическую, экспоненциальную и полиноминальную (до шестой степени) уравнения парной регрессии. Мастер диаграмм вызывается с помощью пункта «Диаграмма…» меню «Вставка»либо нажатием соответствующей кнопки на панели инструментов. Предварительно следует выделить исходные данные вместе с именами переменных. Ряды значений переменных можно расположить либо горизонтально, либо вертикально. В первом случае значения независимой переменной X следует разместить выше значений зависимой переменной Y. Во втором случае ряд X располагается в левом столбце (рис. 5.1).

рис. 5.1. Исходные данные на рабочем листе EXCEL

Для решения эконометрических задач целесообразно выбрать тип диаграммы «Точечная» и, нажав кнопку «Далее», задать основные параметры диаграммы в полях появившейся панели (рис. 5.2). В результате будет построено поле корреляции (рис. 5.3).

рис. 5.2. Панели «Мастера диаграмм»

рис. 5.3. Поле корреляции

Для построения линии регрессии выбирается команда «Добавить линию тренда…» меню «Диаграмма», в результате чего появляется панель «Линия тренда», в которой выбирается форма модели (например, «Линейная»), и устанавливается вывод на диаграмму уравнения регрессии и коэффициента детерминации R² (рис. 5.4). В результате на графике появятся линия регрессии, ее уравнение и R² (рис. 5.5). Аналогично строятся и нелинейные модели.

рис. 5.4. Построение линии регрессии

рис. 5.5. Линия и уравнение регрессии

5.2. Проведение корреляционного анализа с помощью надстройки «Анализ данных»

В состав табличного процессора EXCEL входит набор средств анализа данных (так называемый «Пакет анализа»), предназначенный для эффективного решения многих статистических задач. Надстройка «Пакет анализа» вызывается выбором пункта «Анализ данных…» меню «Сервис» (рис. 5.6).

рис. 5.6. Вызов надстройки «Анализ данных»

Если пункт «Анализ данных…» отсутствует, то следует активизировать надстройку, для чего выбирается пункт «Надстройки» меню «Сервис» и в подвившейся панели «Надстройки» устанавливается флажок в поле «Пакет анализа» (рис. 5.7). После этого в меню «Сервис» должен появиться пункт «Анализ данных…». Если же пункта «Пакет анализа» нет среди доступных надстроек, то эту надстройку нужно установить с инсталляционного диска «Microsoft Office».

рис. 5.7. Активизация надстройки «Пакет анализа»

Как указывалось в § 3.4, перед построением многофакторной регрессионной модели следует проверить исходные данные на коллинеарность факторов, для чего строится матрица парных коэффициентов корреляции. В EXCEL корреляционный анализ может быть проведен с помощью «Пакета анализа». После запуска настройки выбирается инструмент анализа «Корреляция» (рис. 5.8) и заполняются поля появившейся панели (рис. 5.9).

рис. 5.8. Вызов панели корреляционного анализа

рис. 5.9. Панель корреляционного анализа

Ряды значений переменных можно разместить на рабочем листе как вертикально, так и горизонтально. Порядок расположения переменных также не имеет принципиального значения. Однако для последующего проведения регрессионного анализа желательно ряды значений переменных сразу расположить вертикально так, чтобы в первом столбце находился ряд значений результативной переменной Y, а в последующих — ряды значений факторов.

Во входной интервал исходных данных в панели корреляционного анализа целесообразно включить и имена переменных, а в поле «Метки в первой строке» установить флажок. Вывести результаты корреляционного анализа можно как на лист с исходными данными (рис. 5.10), так и на новый рабочий лист, который тут же и создается.

рис. 5.10. Результаты корреляционного анализа

5.3. Построение многофакторной линейной модели регрессии с помощью надстройки «Анализ данных»

Настройка «Пакет анализа» может использоваться и для построения многофакторного уравнения регрессии. После запуска «Пакета анализа» выбирается инструмент анализа «Регрессия» и заполняются поля в появившейся панели (рис. 5.11). Ряды значений переменных должны располагаться на рабочем листе вертикально. Хотя порядок расположения переменных здесь не играет принципиальной роли, однако ряды факторов должны находиться в смежных столбцах (см. рис. 5.10).

В панели регрессионного анализа указываются отдельно интервал значений результата Y и отдельно интервал значений сразу всех факторов. Во входные интервалы исходных данных целесообразно включить и имена переменных, а в поле «Метки» установить флажок. Вывести результаты регрессионного анализа можно как на лист с исходными данными, так и на новый рабочий лист. Одновременно можно задать флажками и вывод предсказанных уравнением регрессии значений результата Y, остатков, их графиков и стандартизированных остатков (рис. 5.12).

рис. 5.11. Панель регрессионного анализа

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