Быстрое прогнозирование в Microsoft Excel

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

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

В Microsoft Excel для её реализации есть две основные функции, появившиеся начиная с 2016-й версии Excel:

  • ПРЕДСКАЗ.ETS (FORECAST.ETS) - вычисляет будущие спрогнозированные значения на основе исторических данных.
  • ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ (FORECAST.ETS.CONFINT) - вычисляет размах доверительного интервала - коридора погрешности, в пределах которого с заданной вероятностью наш прогноз должен сбыться.

Особенно приятно, что вводить вручную эти функции и их многочисленные аргументы совершенно не требуется - в Microsoft Excel для этого есть гораздо более удобный инструмент, получивший название Лист прогноза (Forecast Sheet). Давайте рассмотрим работу с ним на следующем примере.

В качестве исходных исторических данных возьмем с сайта AutoVercity реальную статистику по продажам автомобилей в России за 2019-2020 годы (все марки суммарно):

Исходные данные для прогноза

Представим на минуту, что сейчас конец 2020 года и мы хотим, используя эти данные, сделать помесячный прогноз продаж автомобилей на следующие полтора года. Выделим всю нашу таблицу и на вкладке Данные воспользуемся кнопкой Лист прогноза (Data - Forecast Sheet).

Лист прогноза

В открывшемся окне зададим следующие настройки:

  1. Дату завершения прогноза
  2. Сезонность - почти никогда корректно не определяется автоматически, к сожалению, так что лучше задать её вручную. В большинстве бизнесов она годовая (т.е. "узор" колебаний похожим образом повторяется из года в год), так что установим её равной 12 месяцам.
  3. Вероятность, с которой мы требуем попадания будущих фактических значений в коридор доверительного интервала. Чем больше эта вероятность, тем шире интервал (т.е. более размыт прогноз). Обычно используют значения 90-95%.
  4. В правом нижнем углу окна можно дополнительно выбрать реакцию на пустые ячейки (их можно заполнить нулями или средним соседних значений - интерполяцией) и на дубликаты (обычно их усредняют). Однако же, по возможности, лучше заранее подготовить исходные исторические данные, чтобы таких пробелов или дублей в них не было.

После нажатия на кнопку Создать будет сформирован новый лист с прогнозной таблицей и диаграммой, которая по ней построена:

Готовый прогноз

В верхней части таблицы будут идти строки с историческими данными (синяя линия), а в момент их окончания произойдет переключение на три новых столбца с прогнозом функцией ПРЕДСКАЗ.ETS и верхней и нижней границами доверительного интервала, вычисленного с помощью функции ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ.

Ссылки по теме




03.08.2022 03:35:14
Николай, добрый день. Спасибо Вам за прекрасный полезный прием и урок. Подскажите пожалуйста, как быть, если даты продаж идут непоследовательно (т.е. даты продаж не по месяцам, а конкретные даты, например 01.03.2022; 04.03.2022 и т.д), и Excel выдает сообщение: "Не удалось создать прогноз, так временная шкала имеет неодинаковый интервал". Понимаю, что необходимо как-то сгруппировать по месяцам, но пока не понял как. Подскажите? Спасибо.
04.08.2022 20:28:21
Спасибо Николай, У меня тот же вопрос что у Сергейя С

Благодарю
05.08.2022 14:03:09
Добавить вспомогательный столбец с формулой типа ЕСЛИ(И(ДЕНЬ(А1)>=1;ДЕНЬ(А1)<=31);ДАТА(ГОД(А1);МЕСЯЦ(А1);1);"")
Или загрузить таблицу в PowerQuery и проанализировать столбец с датами (выбрав начало/конец месяца)
17.08.2022 05:51:00
31.08.2022 08:54:10
А ещё есть формула КОНМЕСЯЦА(A1;0)
Наверх