Регрессионные зависимости и прогнозирование*

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

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

Месяц N	1	2	3	4	5	6	7	8	9
Доход, млн. руб.	4.9	12.5	17.8	23.4	30.2	34.5	46.3	52.2	70.3

Построение регрессионной зависимости производится в два этапа. На первом этапе строим XY - диаграмму ( X - независимая переменная - номер месяца N, Y - зависимая переменная - доход, млн. руб.). Выделить таблицу (без первого столбца с подписями), щелкнуть по кнопке Мастер диаграмм, указать место и размеры прямоугольной области (можно на том же листе), выделяемой под диаграмму (см. раздел “Использование графических возможностей EXCEL”).

Затем выбрать тип диаграммы - XY, подтип - N1 (левая верхняя), указать, что ряды данных в строках, отвести 1 строку для данных по оси X, 0 позиций под текст легенды, ввести название диаграммы, названия осей X и Y. Имеется возможность менять размеры и тип шрифта для подписей, толщину линий путем двойного щелчка на соответствующем элементе. На этом построение XY - диаграммы заканчивается.

Для построения регрессионной зависимости надо: выделить диаграмму (двойным щелчком по ней), выделить ряд данных (щелкнуть по любой метке данных на диаграмме), а затем выполнить команду меню Вставка - Линии тренда. Откроется диалог Линия тренда. Необходимо выбрать тип линии тренда (т.е. вид функциональной зависимости, с помощью которой производится сглаживание экспериментальных данных) среди следующих: линейный, логарифмический, полиномиальный (можно задать степень полинома), степенной, экспоненциальный.

Если в диалоге Линия тренда использовать опцию Параметры, то появляется возможность путем продолжения линии регрессии сделать прогноз значений функции на желаемое число временных единиц. Ниже показана диаграмма, построенная по приведенным выше данным с использованием линейного типа регрессии c прогнозом на 10-12 - й месяцы .

В опции Параметры имеется возможность путем установки флажков “включить” на самой диаграмме показ регрессионного соотношения в виде соответствующей формулы, а также величины R - среднеквадратического отклонения экспериментальных точек от линии регрессии. Чем меньше величина R, тем выше качество приближения экспериментальных данных с помощью линии регрессии. Такой подход можно, наряду с качественным визуальным анализом, использовать при выборе наилучшей функции для сглаживания экспериментальных данных и прогнозирования. На приведенной диаграмме показана формула для регрессионного соотношения.