Для тех, кто этого еще не знает.

В Excel имеется несколько инструментов для прогнозирования, в основе которых применяются различные математические модели:

• скользящее среднее (в качестве прогноза принимается среднее значение наблюдаемой величины в нескольких последних измерениях) может быть вычислено с помощью функции с именем СРЗНАЧ или надстройки Скользящее среднее;

• линейный прогноз (к полученным значениям величины приближается прямая линия, на основании которой и рассчитывается прогноз) выполняется с помощью функции с именем ТЕНДЕНЦИЯ или надстройки Регрессия;

• нелинейный прогноз (принимается, что значение величины изменяется нелинейно) может быть получен с помощью функции с именем РОСТ;

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

_________________

Метод экспоненциального сглаживания

Предполагается, что наблюдения некоторой величины X, проводятся через равные промежутки времени. Результат наблюдения обозначим X(t), где – t номер наблюдения. Прогноз P(t+1) для следующего момента времени рассчитывается по формуле:

P(t+1) = P(t) + a*(X(t) – P(t)) (1)

где a – константа сглаживания, выбирается обычно от 0,2 до 0,3. Большие значения константы сглаживания ускоряют отклик прогноза на скачок наблюдаемого процесса, но могут привести к непредсказуемым выбросам.

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

Формула (1) легко может быть переписана в ином виде: P(t+1) = (1 – a)*P(t) + a*X(t). Теперь видно, что при увеличении константы сглаживания в прогнозе доля последнего наблюдения увеличивается, а доля предыдущих наблюдений убывает

Встроенные функции Excel и прогнозирование

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

Функция ЛИНЕЙН

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

Y = X*a + E

Синтаксис вызова этой функции:

ЛИНЕЙН (Известные_значения_Y; Известные_значения_X; Конст; Статистика)

Параметры функции имеют следующий смысл:

• Известные_значения_Y — задает вектор измерений.

• Известные_значения_X — в общем случае матрица значений наблюдаемых параметров. Если речь идет о временном тренде, то элементы X задают моменты времени, в которые проводились измерения. Можно опустить X, если значения элементов составляют последовательность 1, 2, 3 и т. д.

• Булев параметр «Конст» равен Истина (True), если в линейной записи модели присутствует дополнительно свободный член b, не входящий в вектор параметров a.

• Булев параметр «Статистика» равен Истина (True), если наряду с оценками параметров вычисляются и статистические характеристики.

• Результат вычислений этой функции — массив, в общем случае состоящий из 5 строк и n+1 столбцов, где n — это размерность вектора искомых параметров a.

o an, an-1, … a1, b

o σn, σn-1, … σ1, σb

o R*R, σY

o F, df

o Ssreg, Ssresid

• В первой строке идут оценки параметров a и свободного члена b. Оценки идут в обратном порядке, начиная с an. Они и определяют линию регрессии, позволяя рассчитать прогнозируемое значение Y в любой точке, где заданы значения наблюдаемых параметров.

• В следующей строке идут среднеквадратические отклонения этих оценок. Выше мы показали, как вычислить полную корреляционную матрицу оценок. Среднеквадратические отклонения являются диагональными элементами этой матрицы. Точнее, на диагонали стоят их квадраты — дисперсии DI = σI * σI. Значения σI позволяют построить доверительный интервал для соответствующих оценок и вынести суждение об их значимости в линейной модели. Как вычисляются эти значения в Excel, нам осталось непонятно, так как алгоритм не описан. Можно лишь заметить, что применяемый алгоритм не всегда корректен с позиций классической математической статистики. Приведем пример. Пусть оцениваются, как часто бывает, два параметра a и b (Y = at +b). Пусть выполнены всего два измерения — Y1 и Y2. Тогда, каковы ни были ошибки в измерениях, линия регрессии пройдет через две наблюденные точки. Excel скажет, что оши «µ бок в оценках параметров нет, и выдаст значения σ1 и σ2 , равные 0, хотя ясно, что это не так.

• Коэффициент детерминации R2 имеет значение в интервале от 0 до 1 и позволяет оценить, насколько хорошо сглаживаются измеренные значения линией регрессии. Он равен 1, если линия регрессии проходит через все измеренные точки. При этом можно полагать, что есть строгая функциональная зависимость между измеряемым значением Y и параметрами ai. Предыдущий пример показывает, что недостаточное количество измерений может приводить к такому же результату. Поэтому и к этому параметру надо относиться с осторожностью. Вычисляется коэффициент детерминации по формуле:

R2 = Dreg / D

и представляет отношение дисперсии, объясняемой регрессией, к общей дисперсии. О смысле этих терминов мы скажем чуть ниже.

• Мы и так увлеклись понятиями математической статистики, потому не будем говорить о том, что означают и как используются параметры σY, F и число степеней свободы df.

• Последние два значения — Ssreg и Ssresid задают дисперсию, объясняемую регрессией, и остаточную дисперсию, представляющую разность между общей дисперсией и Dreg. Обе дисперсии вычисляются «обычным» способом:

D = (YI — E)2 ; Dreg = (YI — E)2 ,

где E — среднее значение измеренных значений, а YI — сглаженные значения, вычисленные из уравнения регрессии

_________________

Функция ТЕНДЕНЦИЯ и другие функции, используемые для прогноза

В основе всех других функций Excel, используемых для прогноза и регрессионного анализа лежит функция ЛИНЕЙН. Так, если уравнение регрессии уже построено, вычислить значение в новой точке нетрудно. Функция ТЕНДЕНЦИЯ решает эту простую задачу. Она неявно вызывает функцию ЛИНЕЙН и, используя полученные оценки параметров, вычисляет прогнозируемые значения в новых точках. Обращение к ней имеет вид:

ТЕНДЕНЦИЯ(Известные_Y, Известные_X, Новые_значения_X, Конст)

Здесь появился один новый параметр, задающий в общем случае матрицу новых значений X. Все остальные параметры имеют тот же смысл, что и в функции ЛИНЕЙН. В результате возвращается вектор прогнозных значений Y, вычисленный в точках, заданных матрицей новых значений X. Каждая ее строка задает одну точку.

Функция ПРЕДСКАЗ — частный случай функции ТЕНДЕНЦИЯ — используется в линейной модели с двумя параметрами, когда уравнение регрессии имеет вид:

y = a*x + b

В этом случае Y и X представляют одномерные массивы данных. Вызов функции таков:

ПРЕДСКАЗ( x; Известные_Y; Известные_X)

Здесь x — точка, для которой строится прогноз.

Мы говорили о возможности построения нелинейного уравнения регрессии, которое простым преобразованием сводится к задаче линейной регрессии. Такое преобразование и осуществляет функция ЛГРФПРБЛ. Формально здесь используется нелинейная модель:

y = b* a1x1 * a2x2 * … * amxm

Простым логарифмированием модель сводится к линейной.

ln(y) = x1* ln(a1) + x2*ln(a2) + … + xm*ln(am) + b

Функция ЛГРФПРБЛ имеет те же параметры, что и функция ЛИНЕЙН. Обращение к ней:

ЛГРФПРБЛ (Известные_значения_Y; Известные_значения_X; Конст; Статистика)

Как работает эта функция, совершенно ясно: она вызывает функцию ЛИНЕЙН, подавая ей на вход не сами измерения Y, а их логарифмы. Полученные оценки достаточно подвергнуть обратному преобразованию — взять экспоненту, и задача решена. Так строится нелинейное уравнение регрессии. Этот нехитрый прием позволяет самому строить новые модели нелинейной регрессии.

_________________