Быстрое прогнозирование в 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).
В открывшемся окне зададим следующие настройки:
- Дату завершения прогноза
- Сезонность - почти никогда корректно не определяется автоматически, к сожалению, так что лучше задать её вручную. В большинстве бизнесов она годовая (т.е. "узор" колебаний похожим образом повторяется из года в год), так что установим её равной 12 месяцам.
- Вероятность, с которой мы требуем попадания будущих фактических значений в коридор доверительного интервала. Чем больше эта вероятность, тем шире интервал (т.е. более размыт прогноз). Обычно используют значения 90-95%.
- В правом нижнем углу окна можно дополнительно выбрать реакцию на пустые ячейки (их можно заполнить нулями или средним соседних значений - интерполяцией) и на дубликаты (обычно их усредняют). Однако же, по возможности, лучше заранее подготовить исходные исторические данные, чтобы таких пробелов или дублей в них не было.
После нажатия на кнопку Создать будет сформирован новый лист с прогнозной таблицей и диаграммой, которая по ней построена:
В верхней части таблицы будут идти строки с историческими данными (синяя линия), а в момент их окончания произойдет переключение на три новых столбца с прогнозом функцией ПРЕДСКАЗ.ETS и верхней и нижней границами доверительного интервала, вычисленного с помощью функции ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ.
Ссылки по теме
- Моделирование и оценка вероятности выигрыша в лотерею
- Оптимизация доставки в Excel с помощью Поиска решения (Solver)
- Быстрое добавление новых данных в диаграмму
Благодарю
Или загрузить таблицу в PowerQuery и проанализировать столбец с датами (выбрав начало/конец месяца)
Спасибо за статью, очень полезная.
Подскажите, пожалуйста, а можно ли применить такой прогноз для массива данных (клиент-продуктовая группа-артикул-дата-объем)?