Функции предсказания в Excel

Excel, как универсальный редактор электронных таблиц, долго и хорошо справлялся с большинством задач прогнозирования. Однако вычисления в Excel не всегда просты и понятны. А теперь в выпуске 2016 года разработчики Microsoft добавили семейство функций ПРОГНОЗ, которые позволяют решать широкий спектр задач прогнозирования на основе экспоненциального сглаживания всего за несколько щелчков мышью.

Рис. 1. Прогнозирование продаж в Excel с помощью семейства функций ПРОГНОЗ

Об экспоненциальном сглаживании

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

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

Фактически оптимизируются три коэффициента:

α — дисперсия относительно среднего

— тренд

— сезонность

Разработчики Microsoft не предоставили пользователям возможность влиять на выбор коэффициентов, кроме периода сезонности (подробнее об этом ниже).

Обзор функций семейства ПРЕДСКАЗ

Excel предлагает 5 функций:

Рис. 2. Семейство функций ПРОГНОЗ в Excel

ПРОГНОЗ: ETS рассчитывает будущее значение на основе существующих (исторических) данных с использованием экспоненциального сглаживания. То есть дает прогноз с числом.

FORECAST.ETS.INTERVAL возвращает доверительный интервал для прогнозируемого значения. Доверительный интервал должен быть отложен по обе стороны от среднего значения. Вместе с FORECAST.ETS позволяет выстраивать «коридор» прогнозов.

FORECAST.ETS.SEASONALITY возвращает длину повторяющегося блока, найденного в указанном временном ряду. Например, 12, если исторические данные представляют собой ежемесячные продажи.

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

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

ПРЕДСКАЗ.ETS

Синтаксис:

FORECAST.ETS (цель_данных; значения; шкала времени; [сезонность]; [заполнение_данными]; [агрегирование])

Например, рассмотрим ежемесячный пассажиропоток в аэропорту (пример из SM). Исторические данные были собраны с января 2009 года по декабрь 2912 года.

Рис. 3. Исторические данные

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

Рис. 4. Ожидаемые значения на основе функции FORECAST.ETS

Дополнительные сведения о формуле в ячейке C50:

= FORECAST.ETS (A50, $ B $ 2: $ B $ 49, $ A $ 2: $ A $ 49, 1, 1)

Первый аргумент — target_date = A50 — 13 января, то есть прогноз пассажиропотока на январь 2013 ищется в ячейке C50. C52 — до A52 и т.д.

Второй аргумент — values ​​= $ B $ 2: $ B $ 49. Исторические данные о пассажиропотоке можно найти здесь. Ссылка является абсолютной, поэтому при перетаскивании формулы ячейки, на которые указывает ссылка, не изменяются.

Третий аргумент — это масштаб времени = $ A $ 2: $ A $ 49. Здесь находятся даты временной шкалы или номера периодов. Важно, чтобы они располагались друг от друга через фиксированный интервал. Если диапазон неверен, Excel все равно будет считать, что диапазон фиксирован и что некоторые данные отсутствуют. Ниже описывается, как решаются такие ситуации. Нет необходимости сортировать массив на основе значений временной шкалы, так как FORECAST.ETS сортирует сами данные перед выполнением вычислений.

Четвертый аргумент [сезонность] = 1. Это необязательный аргумент. Значение по умолчанию — 1. Для этого Excel автоматически определяет сезонность и использует положительные целые числа в качестве длины сезонной модели. Значение 0 предписывает не использовать фактор сезонности, поэтому прогноз будет линейным. Если для этого параметра указано положительное целое число, алгоритм использует его как длину модели сезонности. Например, мы знаем, что сезонность равна 4 (ежеквартальная частота), но предполагается, что она слабая и что автоматический алгоритм Excel может ее не обнаружить и предположить, что сезонности нет. Для начала я бы рекомендовал использовать настройку по умолчанию.

Пятый аргумент — [data_fill] = 1. Это необязательный аргумент. Хотя временная шкала требует постоянного переключения между точками данных, FORECAST.ETS сохраняет до 30% отсутствующих данных и корректирует их автоматически. 0 означает, что алгоритм считает недостающие точки нулями. Если установлено значение 1 (по умолчанию), функция определяет недостающие значения как среднее значение соседних точек.

Шестой аргумент, [агрегирование], в нашем примере опущен. Это необязательный аргумент. Это необходимо, если даты на шкале времени или номера периодов содержат дубликаты. ПРОГНОЗ ETS будет агрегировать баллы с одинаковой меткой времени. Параметр агрегирования — это числовое значение, определяющее способ агрегирования нескольких значений с одной и той же меткой времени. Для значения по умолчанию 0 используется метод AVERAGE; есть также варианты для SUM, COUNT, COUNT, MIN, MAX и MEDIAN.

ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ

Синтаксис:

FORECAST.ETS.INTERVAL (цель_данных; значения; шкала времени; [вероятность]; [сезонность]; [заполнить данные]; [агрегирование])

Новая тема здесь — вероятность. Это необязательно. И принимает значения от 0 до 1 (не входит в комплект). Например, для значения 95% рассчитывается доверительный интервал, при котором прогнозируемые точки будут уменьшаться с вероятностью 95%. Функция FORECAST.ETS.RANGE возвращает значение в тех же физических единицах, что и FORECAST.ETS. Например, 50 ± 8. Здесь 8 — доверительный интервал, возвращаемый FORECAST.ETS.INTERVAL. Он строится от среднего значения прогноза с обеих сторон.

Рис. 5. Расчет границ коридора прогноза

Теперь на основе расчета среднего прогноза, возвращаемого функцией FORECAST.ETS, и границ коридора, возвращаемых формулами…

= C50 ± ПРОГНОЗ.ET.RANGE (A50, $ B $ 2: $ B $ 49, $ A $ 2: $ A $ 49, 0,95, 1, 1)

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

Рис. 6. Исторические и ожидаемые данные о пассажирских перевозках с доверительным интервалом 95

Из-за того, что исторические данные показывают четкие закономерности, границы прогнозов оказались довольно узкими.

ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ

Синтаксис:

FORECAST.ETS.SEASON (значения; раз; [заполнение_данными]; [агрегирование])

Здесь нет новых для нас тем. В простейшем случае FORECAST.ETS.SEASONALITY берет исторические данные и временную шкалу и возвращает целое число, равное сезонности. В нашем примере — 12:

Рис. 7. Значение сезонности

ПРЕДСКАЗ.ETS.СТАТ

Синтаксис:

FORECAST.ETS.STAT (значения; шкала времени; тип_статистики; [сезонность]; [заполнение_данных]; [агрегирование])

Появился новый аргумент: statistics_type. Необходимо. Он принимает целочисленные значения от 1 до 8. Я использовал вложенную функцию LAST (8), которая определяла массив целых чисел: 1, 2,…, 8. А функция FORECAST.ETS.STAT работала как динамическая функция array, создающая столбец из 8 статистических значений:

Рис. 8. Статистика экспоненциального сглаживания

Первый параметр — альфа = 0,9. Чем он больше, тем больше вес самых последних точек данных. Оптимизированные коэффициенты в модели ETS (α, γ и δ) не следует путать со значениями, возвращаемыми FORECAST.ETS.STAT. Это разные вещи. Второй параметр — бета = 0,001. Возвращает значение параметра тренда: чем оно больше, тем больше вес нового тренда. Третий параметр — гамма. Возвращает значение параметра сезонности: чем оно больше, тем больше вес нового сезонного периода. Параметры с четвертого по седьмой являются различными индикаторами точности прогнозов. Восьмой параметр — это размер шага, определенный на временной шкале ретроспективных значений. В нашем случае 31 день.

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

Глазурь на торте — кнопка на ленте, доступная из меню Данные -> Прогноз -> Лист прогнозов. Эта кнопка использует исторические данные для создания нового листа Excel со столбцами прогнозов, пределами достоверности, статистикой модели и довольно приличной диаграммой. Затем наведите курсор на любую ячейку умной таблицы с исходными данными (как на рис. 3) и нажмите на лист прогноза. Откроется окно «Создать лист прогноза.

Рис. 9. Настройка параметров листа прогноза

Здесь (1) вы можете выбрать тип диаграммы: диаграмма или гистограмма, (2) установить период прогноза, (3) выбрать начало прогноза на временной шкале; (4) выбрать значение доверительного интервала, (5) использовать параметр сезонности (число), определяемый функцией FORECAST.ETS автоматически, или установить сезонность принудительно, (6) указать, выводить ли статистику прогноза модели.

Если вы выберете дату начала прогноза на временной шкале исторических данных, прогноз для каждой точки будет использовать только предшествующие ему данные (это называется «ретроспективным взглядом»).

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

Если ваши данные показывают сезонные тенденции, мы рекомендуем начинать прогнозирование с предыдущей даты до последней точки статистики.

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

После настройки параметров в окне «Создать лист прогноза» нажмите «Создать». В нашем примере Excel создаст новый лист, расположенный слева (перед) листом исторических данных:

Рис. 10. Таблица прогнозов