Реализация задачи построения парной модели регрессии и корреляции в ппп Excel с помощью инструмента Анализа данных «Регрессия»

Далее построим уравнение парной линейной зависимости потребления хлеба и хлебопродуктов от располагаемых ресурсов в ППП Excel с помощью инструмента анализа данных «Регрессия».

В нашем примере эти данные выглядят следующим образом (рисунок 7.14):

Рис.7.14 Результаты расчетов по функции «Линейн»

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

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

Рис.7.15 Подключение надстройки «Пакет анализа»

2) в главном меню выберите Сервис / Анализ данных / Регрессия. Щелкните по кнопке ОК;

3) заполните диалоговое окно ввода данных и параметров вывода (рис.7.16):

.

Рис.7.16 Диалоговое окно ввода параметров инструмента «Регрессия»

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

Входной интервал Х – диапазон, содержащий данные независимого признака;

Метки – флажок, который указывает, содержит ли первая строка названия столбцов или нет;

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

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

Новый рабочий лист – можно задать произвольное имя нового листа.

Чтобы получить информацию и графики остатков, установите соответствующие флажки в диалоговом окне. Щелкните по кнопке ОК

Результаты регрессионного анализа для нашего примера представлены на рисунке 7.17.

Рис.7.17 Результаты применения инструмента «Регрессия»

Экономическая интерпретация параметров уравнения регрессии рассмотрена в примере 7.1.

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

Таблица 7.3.

Фрагмент 1

Регрессионная статистика
Множественный R	0,866
R-квадрат	0,750
Нормированный R-квадрат	0,719
Стандартная ошибка	0,680
Наблюдения	10

В первом фрагменте представлены следующие показатели:

Множественный R – коэффициент корреляции (множественный в случае множественной корреляции, в нашем случае это парный коэффициент корреляции между х и у) . связь между уровнем располагаемых ресурсов и потреблением хлеба и хлебопродуктов высокая, прямая.

Для оценки качества подбора линейной функции рассчитывается квадрат линейного коэффициента корреляции – коэффициент детерминации или 74,5%.

Это означает, что 74,5% вариации потребления хлеба и хлебопродуктов обусловлены различиями в уровне располагаемых доходов населения по децильным группам, остальные 25,5% вызваны прочими факторами, которые в данном случае не рассматриваются (допустим, в нашем случае ими могут быть количество мужчин в домохозяйстве – известно, что мужчины потребляют больше хлеба, чем женщины; численность населения, принципиально не потребляющие хлеба (например, соблюдающих диету и т.д.).

• Нормированный R – квадрат – коэффициент детерминации с поправкой на число степеней свободы ( )

• Стандартная ошибка – ошибка модели

• Наблюдения – число наблюдений, по которым строится модель (в нашем случае это число групп населения n=10).

Таблица 7.4.