Прогнозирование в Excelс помощью регрессионного анализа

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

Содержание работы:

1 Линейный регрессионный анализ.

2 Экспоненциальный регрессионный анализ.

3 Линейный многомерный регрессионный анализ

Линейный регрессионный анализ

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

MSExcel позволяет заменять имеющуюся таблицу на два вида уравнений - линейное или нелинейное. Если по таблице построить график и точки будут лежать около прямой линии (рисунок 4.1), то такую таблицу можно заменить на линейное уравнение вида y=mx+b. Если точки графика не лежат на прямой (рисунок 4.2), то таблица заменяется на степенное уравнение вида y=b·m^x, где mи b –постоянныекоэффициенты. В обоих случаях MSExcel рассчитывает коэффициент детерминированности r², который показывает, насколько точно уравнение соответствует данным таблицы. Рассчитываются также ошибки mи bдля каждой точки графика.

Одним из методов, используемых для прогнозирования, является регрессионный анализ, т.е. замена таблицы на уравнение и его исследование.

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

Таблица 4.1

х	x1	x2	...	хi	...	хn
y	x1	y2	...	yi	...	yn

у у •

•

• •

•

• •

• • • •

b• •

хх

Рисунок 4.1 Линейная регрессия Рисунок 4.2 Нелинейная регрессия

На графике эти данные отображаются точками (рисунки 4.1, 4.2). Регрессия позволяет подобрать к этим точкам кривую у=f(x), которая вычисляется по методу наименьших квадратов и даёт максимальное приближение к табличным данным.

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

Линейная регрессия

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

y=mx+b,(1)

где:

х - независимая переменная ;

у -зависимая переменная;

m – характеристика наклона прямой;

b - точка пересечения прямой с осью у.

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

Экспоненциальный регрессионный анализ.

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

y=b·m^x,(2)

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

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

Множественная регрессия

Множественная регрессия представляет собой анализ более одного набора данных аргумента х и дает более реалистичные результаты. Множественный регрессионный анализ также может быть как линейным, так и экспоненциальным. Уравнения регрессии (1) и(2) примут соответственно вид (3) и (4):

у=m_lx_l + m₂x₂ + ... + m_nx_n + b(3)

у = b·m_l^xl· m₂^x2· ... ·m_n^xn,(4)

где:

x_l, x₂, ..., x_n - независимые переменные.

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