II. Технология решения задач регрессионного анализа

Регрессия позволяет проанализировать воздействие на какую-либо зависимую переменную одной или более независимых переменных и позволяет установить аналитическую модель этой зависимости в виде аппроксимирующего полинома.

Если рассматривается зависимость между одной зависимой переменной y и несколькими независимыми x ₁, x ₂, …, x _n, то речь идет о множественной линейной регрессии. В этом случае уравнение регрессии имеет вид

,

где а ₁, а ₂, …, а _n – коэффициенты при независимых переменных, которые нужно вычислить (коэффициенты регрессии); а ₀ – константа.

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

Мерой оценки адекватности регрессионной модели является коэффициент детерминации R ² (R -квадрат), который определяет, с какой степенью точности полученное уравнение регрессии аппроксимирует исходные данные.

Значимость регрессионной модели оценивается с помощью критерия Фишера (F -критерия). Если величина F -критерия значима (), то регрессионная модель является значимой.

В табличном процессоре можно аппроксимировать экспериментальные данные линейным уравнением до 16-го порядка:

.

Для вычисления коэффициентов регрессии служит инструмент Регрессия, который можно включить следующей последовательностью операций:

- выполнить команду Сервис / Анализ данных;

- в раскрывшемся окне диалога Анализ данных выбрать из списка инструмент Регрессия;

- в группе Входные данные в поле Входной интервал Y указать адресную ссылку на диапазон, содержащий значения зависимой переменной, а в поле Входной интервал Х – ссылку на диапазон, содержащий значения независимых переменных, т.е. переменных, влияние которых на зависимую переменную y оценивается; установить флажок Метки, если исходная таблица имеет названия столбцов и флажок Константа-ноль, если ;

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

Если необходимо получить визуальную картинку отличия экспериментальных точек от предсказанных регрессионной моделью, то установить флажок График подбора.

Если нужно получить график нормальной вероятности, то установить флажок График нормальной вероятности.

В выходном диапазоне после выполнения вычислений отображаются результаты дисперсионного анализа, коэффициенты регрессии, стандартная погрешность вычисления y, среднеквадратичные отклонения, количество наблюдений, стандартные погрешности для коэффициентов.

Значения коэффициентов регрессии размещаются в столбце Коэффициенты:

Y -пересечение – коэффициент а ₀;

х ₁ – коэффициент а ₁;

х ₂ – коэффициент а ₂ и т.д.

В столбце Р-Значение содержится оценка достоверности отличия соответствующих коэффициентов от нуля. Если , то коэффициент можно считать нулевым. Это означает, что соответствующая независимая переменная практически не влияет на зависимую переменную.

Значение R-квадрат определяет, с какой степенью точности регрессионное уравнение будет аппроксимировать экспериментальные данные. Если , то точность аппроксимации высокая. При аппроксимация удовлетворительная. В случае когда , точность аппроксимации недостаточна и модель требует улучшения.

Кроме инструмента Регрессия в табличном процессоре для получения параметров уравнения регрессии есть функция ЛИНЕЙН и функция ТЕНДЕНЦИЯ для получения значения у в требуемых точках.

Пример 3. Имеются статистические данные о затратах, связанных с рекламой по телевидению, с рекламой в метро и объеме реализации продукции в рублях, приведенные в таблице 3.

Таблица 3

Исходные данные

Затраты на рекламу по телевидению (руб.)	Затраты на рекламу в метро (руб.)	Объем реализации (руб.)

 

Требуется найти регрессионные коэффициенты для независимых переменных Расходы на рекламу по телевидению и Расходы на рекламу в метро на объем реализации продукции и построить уравнение регрессии.

Решение в MS Excel

1. На рабочем листе в диапазон А1:С8 ввести данные вышеприведенной таблицы.

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

3. В диалоговом окне Регрессия установить следующие параметры (рис. 11):

Рис. 11. Диалоговое окно Регрессия

4. Результаты регрессионного анализа выводятся на экран в заданные ячейки (рис. 12):

Рис. 12. Вывод итогов регрессионного анализа

Полученные результаты и их интерпретация:

- коэффициент детерминации (аппроксимация высокая);

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

- Y -пересечение ;

- - коэффициент при независимой переменной Затраты на рекламу по телевидению;

- - коэффициент при независимой переменной Затраты на рекламу в метро.

С учетом полученных данных функциональная зависимость величины прибыли от затрат на рекламу запишется в виде полинома

,

где x ₁ – величина затрат на рекламу по телевидению; x ₂ – величина затрат на рекламу в метро.