5.2.1.7.Адаптивные модели.

Экспоненциальное сглаживание. (рис.5.20)

В столбцах A и B приведены соответственно номера уровней и уровни временного ряда. Тренд этого ряда описывается уравнением:

y_t = 53.187 + 1.668t. (5.83)

Коэффициенты тренда определяются с помощью команды /Сервис/Анализ данных/Регрессия. Значения коэффициентов тренда занесены в ячейках E3 и F3.

Вячейках С3 иD3 устанавливаются начальные значения экспоненциальных средних по (5.53):

-в ячейке С3 - выражение = E3-(1-C29)/C29*F3,

-в ячейке D3 - =E3-2*(1-C29)/C29*F3.

В ячейках С4 и D4 записываются выражения для определения текущих значений экспоненциальных средних по (5.51):

-в ячейку С4 - =$C$29*B4+(1-$C$29)*C3,

-в ячейку D4 - =$C$29*C4+(1-$C$29)*D3.

Выражения С4 и D4 копируются на всю длину временного ряда.

В ячейках E4 и F4 записываются выражения для оценки коэффициентов адаптивного полинома:

-в ячейку E4 - =2*C4-D4,

в ячейку F4 - =$C$29/(1-$C$29)*(C4-D4).

Выражения E4, F4 копируются в блок ячеек E4:F27.

В столбце G определяются расчетные значения ряда по модели прогноза (5.50). В ячейку G5 записывается выражение =E4+F4 и копируется в блок ячеек G4:G27.

ВстолбцахH и I определяются соответственно относительная и среднеквадратическая ошибки аппроксимации:

-в ячейку H5 - =ABS((B5-G5)/B5),

-в ячейку I5 - =(B5-G5)^2.

Выражения H5 и I5 копируются на всю длину ряда. В ячейках H28 и I28 определяются суммы, а в H29 и I30 значения ошибок, т.е. суммы, деленные на длину временного ряда.

На рис.5.20. расчеты выполнены при начальном значении параметра сглаживания равном 0.3 (ячейка С29). Оптимальное значение параметра сглаживания находится методами численной оптимизации. В табличном процессоре эту задачу можно решить с помощью команды/Сервис/Поиск решения. В качестве критерия оптимальности можно использовать относительную ошибку аппроксимации. При выполнении этой команды раскрывается диалог (рис.5.21), в котором необходимо Установить целевую ячейку (H29 - относительная ошибка), которая должна быть равной Минимальному значению, Изменяя ячейки (С29 - параметр сглаживания), при Ограничениях (С29<=1 и C29>=0 - параметр сглаживания изменяется в пределах от 0 до 1).

Параметры оптимизации установить так, как показано на рис.5.22. При нажатии кнопки /Выполнить (рис.5.21) получаем оптимальное значение параметра сглаживания и минимальное значение относительной ошибки аппроксимации (рис.5.23).

Прогноз по (5.50) производится в ячейках В32В34. В ячейку В32 записывается выражение =$E$27+$F$27*(A32-$A$27), которое копируется в ячейки В33 и В34.

Втабличном процессоре есть возможность графически отобразить зависимость целевой функции (относительной ошибки) при изменении параметра сглаживания в пределах 01 -  = f().

Это можно выполнить с помощью команды/Данные/Таблица подстановки. Для этого в столбец J, начиная с ячейки J4, заносятся значения параметра сглаживания с шагом, например 0.05. Это можно выполнить следующим образом. В ячейку J4 вносится начальное значение 0.05, далее вниз выделяется блок из 20 ячеек J4:J23. После этого выполняется команда /Правка/Заполнить/Прогрессия.

Воткрывшемся диалоге рис.5.24 необходимо установить: расположение -По строкам, тип - Арифметическая, Шаг равный 0.05. и нажать OK. Затем в ячейке K3 указать ссылку на целевую ячейку =Н29. Выполнить команду /Данные/Таблица подстановки. В открывшемся диалоге рис.5.25 в строке Подставлять значения по строкам в… указать ссылку на ячейку, содержащее значение параметра сглаживания и нажать OK. Получим таблицу значений целевой функции рис.5.26. По этим значениям построим линейный график - по оси ординат значение целевой функции, по оси абсцисс - параметр сглаживания рис.5.26.

На рис.5.27. изображены график наблюдаемых значений временного ряда (кривая ряд 1), расчетных значений (ряд 2) и прогнозных значений (ряд 3).

Модель Брауна (рис.5.28)

Технология моделирования временного ряда по методу Брауна выполняется также, как и по методу экспоненциального сглаживания. За начальные значения коэффициентов адаптивного полинома взяты коэффициенты тренда (5.83) - ячейки С3 и D3.

В ячейках C4 и D4 записываются выражения для оценки коэффициентов адаптивного полинома по схеме Брауна (5.54, 5.55):

-в ячейке С4 - =C3+D3+(1-$C$29^2)*F4;

-в ячейке D4 - =D3+(1-$C$29)^2*F4.

Выражения С4 и D4 копируются в блок ячеек С4:D27.

В столбце Е определяются расчетные значения ряда по (5.50), в ячейку Е4 записывается выражение =C3+D3 и копируется в блок ячеек Е4:Е27.

В столбце F рассчитываются остатки, в ячейку F4 записывается выражение =B4-E4, которое копируется в блок ячеек F4:F27.

В столбцах G и H определяются соответственно относительная и среднеквадратическая ошибки аппроксимации:

-в ячейку G4 - =ABS((B4-E4)/B4),

-в ячейку H4 - =(B4-E4)^2.

Выражения G4 и H4 копируются на всю длину ряда. В ячейках G28 и H28 определяются суммы, а в G29 и H30 значения ошибок, т.е. суммы, деленные на длину временного ряда.

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

На рис.5.28 расчеты выполнены при начальном значении параметра сглаживания равном 0.3 (ячейка С29). Оптимальное значение параметра сглаживания находится также с помощью команды /Сервис/Поиск решения (рис.5.29).

Прогноз по (5.50) производится в ячейках В33В35. В ячейку В33 записывается выражение =$C$27+$D$27*(A33-$A$27), которое копируется в ячейки В34 и В35.

Зависимость целевой функции (относительной ошибки) от параметра сглаживания в пределах 01  = f() изображена на рис.5.30.

Графики наблюдаемого (Ряд 1), расчетного (Ряд 2) временного ряда и прогноза (Ряд 3) изображены на рис.5.31.