Прогнозирование в один клик в Excel 2016

Статья первоначально опубликована командой Excel (Excel Team) на английском языке здесь.

 «Точные расчеты делать сложно, особенно когда дело касается прогнозов на будущее», — говорил датский физик Нильс Бор. Что ж, прогнозирование в один клик в Excel значительно упрощает эту задачу.

Мы часто используем Excel для анализа данных временных рядов (например, о продажах, использовании сервера или инвентаризации), стараясь выявить повторяющиеся сезонные закономерности и тенденции. В Excel 2016 новые функции прогноза на листе и прогноз одним кликом помогут объяснить данные и понять будущие тенденции.

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

Экспоненциальное сглаживание для прогнозов

Есть множество способов создания прогноза по хронологическим данным. До появления Excel 2016 многие пользовались функцией листа FORECAST() , с помощью которой создается линейный прогноз или экстраполяция на основе продленных линий тренда в свойствах диаграммы.

В новой функции Excel 2016 используется другой алгоритм: экспоненциальное сглаживание (ETS). Это один из самых популярных способов прогнозирования, который уже стал отраслевым стандартом.

Одно из главных преимуществ метода ETS — возможность обнаруживать сезонные закономерности и доверительные интервалы.

Сезонные закономерности

Во многих бизнес-сценариях существует сезонная закономерность, которую желательно учитывать при прогнозе. Примером этого могут служить продажи мороженого, представленные в виде данных по месяцам. В этом случае нам будет представлен годовой цикл, который будет повторяться каждые 12 точек (месяцев). Еще один пример — почасовые данные о дорожном движении, где оптимальная сезонность представлена 24 точками (часами).

В примере ниже видно, как ежегодная сезонность обнаруживается и применяется к прогнозу. Поскольку данные указаны по месяцам и повторяются каждые 12 точек, обнаруженная сезонность равна 12.

Продолжительность сезонности известна не всегда. Этот метод прогноза выявляет сезонные закономерности в хронологических данных, определяя ту, которая наиболее точно соответствует данным. Для наиболее точного прогноза хронологические данные должны содержать как можно больше повторяющихся циклов. Желательно, чтобы полных сезонных циклов было хотя бы 2–3. Автоматически обнаруживаемое значение в разделе сезонности можно найти в диалоговом окне Создание прогноза в разделе Параметры. Если сезонные данные обнаружить не удалось или сезонность известна заранее, то это значение можно переопределить, выбрав параметр Задать вручную.

Доверительные интервалы

Помимо прогнозирования будущих значений для введенного временного ряда, прогноз ETS также может определять доверительный интервал.

Доверительный интервал — это диапазон, окружающий каждое прогнозируемое значение, в который, согласно прогнозу (с нормальным распределением), должно попасть 95 % будущих точек. Доверительный интервал помогает выяснить точность прогноза. Чем меньше интервал, тем выше доверие к прогнозу для той или иной точки. Значение коэффициента доверия, по умолчанию равное 95 %, можно изменить с помощью стрелок вверх и вниз в следующих целях:

  • На основе ширины доверительного интервала можно определить точность прогноза.
  • Экспериментируя с расширенными функциями (учет отсутствующих точек, сезонности и др.), можно отслеживать, как сужается и расширяется предварительный доверительный интервал. Это позволяет определить, насколько полученная модель соответствует хронологическим данным.

Как создать прогноз

Чтобы создать лист прогноза, сначала подготовьте набор данных временного ряда (с временным рядом и рядом значений). Затем на вкладке «Данные» нажмите на кнопку Лист прогноза. Запустится диалоговое окно создания прогноза с пошаговыми инструкциями. Подробные инструкции о том, как создать прогноз, см. на странице Создание прогноза в Excel 2016 для Windows. Второй вариант — если вы умеете пользоваться функциями листа, то можете сделать то же самое с помощью новых функций FORECAST.ETS*, описанных здесь: Справка по функциям прогнозирования. Функции листа обладают теми же возможностями, что и мастер прогнозов.

Как узнать, точен ли прогноз? Можно ли ему доверять?

Как и в случаях со многими статистическими инструментами, точность прогноза будет зависеть от введенных данных. А поскольку данные редко бывают идеальными, очень важно изучить прогноз и понять, насколько он применим в вашем конкретном случае. Есть несколько способов оценить точность прогноза:

Посмотрите на ширину доверительного интервала (см. выше).

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

В примере внизу это отмечено красным цветом: как видно, прогноз был очень близок к фактическим данным.

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

Если вы разбираетесь в статистике, установите флажок Включить статистические данные прогноза, чтобы отобразить сводные показатели точности.

Установите флажок «Включить статистические данные прогноза», чтобы отобразить таблицу статистических значений в прогнозе.

Все статистические данные вычисляются с помощью функции FORECAST.ETS.STAT() и описаны здесь.

Поделитесь своим мнением

Мы только что кратко рассказали вам о новых функциях, доступных в Excel 2016. Опробуйте их сами и поделитесь своими идеями о других функциях и улучшениях, которых недостает в Excel.