 |
Метод экспоненциального сглаживания
|
|
|
|
Предполагается, что наблюдения некоторой величины 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) нет и формулой P(t+1) = P(t) + a*(X(t) – P(t)) (Формула 1) воспользоваться еще невозможно, в качестве прогноза P(2) следует взять X(1).
ФормулаP(t+1) = P(t) + a*(X(t) – P(t)) (Формула 1) легко может быть переписана в ином виде:
P(t+1) = (1 – a)*P(t) + a*X(t).
Теперь видно, что при увеличении константы сглаживания в прогнозе доля последнего наблюдения увеличивается, а доля предыдущих наблюдений убывает.
Пример 1. Вы руководите агентством по прокату автомобилей. По мере приближения зимы Вы замечаете увеличение количества заявок клиентов на транспорт, снабженный багажником для перевозки лыж. Несколько дней спустя после начала проведения исследования в Вашей местности выпало очень много снега и, как следовало ожидать, количество вышеупомянутых заявок резко возросло. Итак, используя результаты выполненных на сегодняшний день наблюдений (в данном случае наблюдение – это количество заявок за день) нам нужно узнать, сколько автомобилей, оборудованных багажником для лыж, необходимо подготовить, чтобы полностью удовлетворить спрос в завтрашний день.
Введите данные наблюдений, как показано на (Рисунок 13).
|
|
|
Раскройте меню Сервис. Если в выпавшем подменю нет команды Анализ данных, то выполните команду Сервис, Надстройки. В появившемся окне Надстройки (Рисунок 14) в списке надстроек установите флажок слева от строки Analysis ToolPak - VBA (функции VBA для работы пакета анализа).
Рисунок 13 Прогноз по методу экспоненциального сглаживания (константа сглаживания равна 0,2)
Выполните команду Сервис, Анализ данных. В списке инструментов анализа выберите строку Экспоненциальное сглаживание. Появится окно Экспоненциальное сглаживание (Рисунок 14), которое следует заполнить.
Установите курсор в поле Входной интервал. Выделите интервал входных данных A1:A12.
Проведите расчет при значении константы сглаживания a, равном 0,2. Для этого введите в поле Фактор затухания значение, равное1 – а, которое в данном случае равно 0,8.
Установите флажок в поле Метки, означающий, что первая ячейка входного интервала является заголовком.
Рисунок 14. Заполнение окна Экспоненциальное сглаживание
Установите курсор в поле Выходной интервал. Выделите ячейку B2 – первую ячейку выходного интервала. В поле Выходной интервал появится строка $B$2.
Установите флажок в поле Вывод графика и щелкните на кнопке ОК. На рабочем листе (Рисунок 13) будет выведен прогноз и диаграмма, позволяющая сравнить прогноз с фактическими данными.
Проанализируйте полученные результаты. В ячейке A11 записано количество заявок, сделанных за десятый день наблюдений. В ячейке B11 записан прогноз на десятый день, полученный сглаживанием на основании данных предыдущих девяти дней наблюдений. В ячейке B12 записан прогноз количества ожидающихся заявок в следующий день. А сколько их будет сделано на самом деле, станет известно только в следующий день. Запись в ячейке B2 означает недостаток данных.
Сравнивая график фактических данных с графиком прогноза, можно сделать вывод о том, что прогноз, полученный сглаживанием, реагирует на скачок фактической функции, но медленнее, чем этого бы хотелось. Реакция будет более быстрой, если уменьшить значение фактора затухания.
|
|
|
Рисунок 15. Предупреждение Excel о предстоящей перезаписи данных
Повторите получение прогноза, заменив значение 0,2 константы сглаживания наибольшим рекомендуемым значением 0,3. Появившееся окно (Рисунок 15) с предложением перезаписать данные закройте щелчком на кнопке ОК.
Рабочий лист Вашей таблицы должен соответствовать изображению на Рисунок 17. Можно заметить, что теперь прогноз быстрее отслеживает скачок фактической функции.
Рисунок 16. Прогноз по методу экспоненциального сглаживания (константа сглаживания равна 0,3)
Снова повторите получение прогноза, заменив значение 0,3 константы сглаживания на 0,1. Анализируя рабочий лист (Рисунок 17) Вы убедитесь, что в последнем случае качество прогноза заметно возросло, а устойчивость прогноза сохранилась.
Рисунок 17 Прогноз по методу экспоненциального сглаживания (константа сглаживания равна 0,9)
Выполняя задание, Вы, конечно, обратили внимание на то, пакет Анализ данных содержит длинный список инструментов анализа. Описание этих инструментов можно найти в справочной системе Excel.
Основная литература:
1. Лавренов С.М. Excel: Сборник примеров и задач. – М.: Финансы и статистика, 2000, 2002.
Дополнительная литература:
1. Уокенбах Д. Подробное руководство по созданию формул в Excel 2002.: Пер. с англ. – М.: Издательский дом «Вильямс», 2003.
2. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах. – СПб.: БХВ – Санкт–Петербург, 2000.
|
|
|
