Прогнозирование с учетом сезонности в Excel: как использовать Лист прогноза (Forecast Sheet)
Для прогнозирования с учётом сезонности в Excel для Windows есть Лист прогноза (Forecast Sheet), появившийся в Excel 2016 и доступный в следующих настольных выпусках и Microsoft 365. Он использует экспоненциальное сглаживание ETS. Функция ПРЕДСКАЗ.ETS есть и в поддерживающих её версиях Excel для Mac, но это не означает наличие того же мастера; в Excel для Интернета ETS недоступна.
Эта статья особенно полезна тем, кто ищет формулы в Excel, хочет быстрее разобраться в функциях и применять рабочие примеры без путаницы между русской и английской версиями Excel.
Рассмотрим пример кинотеатра, где выручка возрастает в выходные и падает в будни. Прямая линия может плохо описывать такую сезонность. Низкий R² характеризует соответствие линейной модели имеющимся наблюдениям, но сам по себе не измеряет точность будущего прогноза.
Для построения и проверки прогноза выполните следующие шаги:
- Убедитесь, что ваши данные организованы в два столбца: даты в столбце A и значения (например, продажи) в столбце B. Временная шкала должна иметь постоянный шаг. Желательно заранее обработать пропуски и повторы дат; для оценки сезонности нужны хотя бы два полных цикла.
- Выделите диапазон с данными.
- Перейдите на вкладку «Данные» ленты и нажмите кнопку «Лист прогноза» (Лист прогноза (Forecast Sheet)).
В окне Создание листа прогноза раскройте «Параметры»: проверьте сезонность, конец прогноза и доверительный интервал, при необходимости включите статистику. Excel создаст лист с таблицей, диаграммой и формулами ПРЕДСКАЗ.ETS (FORECAST.ETS). Оценивайте прогноз на отложенном участке истории. Процентная метрика ошибки, например SMAPE, не равна доле объяснённой изменчивости и не гарантирует такую же ошибку в будущем.
Важное ограничение: Модель использует одну длину сезонного цикла: например, 7 для ежедневных данных с недельной сезонностью или 12 для месячных данных с годовой сезонностью. Две независимые сезонности, например недельная и годовая, отдельно не моделируются. Выбор одного периода не гарантирует учёт второго.
Лист прогноза удобен для временных рядов с трендом и одним сезонным циклом. Сравнивайте качество с простой базовой моделью и учитывайте, что изменения спроса и внешние события могут нарушить закономерности истории.