Прогноз в Excel: продажи по прямой тренда, пики и доверительный интервал

План на следующий квартал редко берут с потолка: обычно есть продажи за прошлые месяцы, и вопрос в том, куда они идут. Справка Microsoft называет главным условием ровную шкалу: лист прогноза требует временную шкалу с постоянным интервалом, например по месяцам^[4]. Если шаг неровный или половина месяцев пропущена, никакая формула не спасёт. Прогноз в Excel честен ровно настолько, насколько ровны и полны исходные данные.

Порядок работы такой: найти подходящую функцию прогноза, ввести формулу с известными рядами, проверить результат на прошлых месяцах, исправить ошибки #Н/Д и #ЧИСЛО!, если ряды не совпадают, а затем применить расчёт к своим данным. Прогноз в Excel бывает линейным и сезонным, и от этого зависит выбор инструмента. Остальные темы анализа собраны на заглавной странице тренажёра с уроками Excel.

Ключевые факты: прогноз в Excel

• ПРЕДСКАЗ.ЛИНЕЙН предсказывает будущее значение по существующим и строит его по уравнению a + bx^[1].
• Лист прогноза допускает до 30% пропусков на шкале и по умолчанию строит доверительный интервал 95%^[4].
• ПРЕДСКАЗ.ETS недоступна в Excel в браузере, на iOS и Android^[3].
• Справка Microsoft сообщает, что в Excel 2016 ПРЕДСКАЗ заменила ПРЕДСКАЗ.ЛИНЕЙН, а старая функция оставлена для совместимости^[1].
• ТЕНДЕНЦИЯ в отличие от ПРЕДСКАЗ.ЛИНЕЙН возвращает сразу несколько значений для массива новых x^[2].

Как работает прогноз в Excel: ПРЕДСКАЗ.ЛИНЕЙН и уравнение a + bx

Прогноз в Excel по прямой строит функция ПРЕДСКАЗ.ЛИНЕЙН: она подбирает уравнение a + bx по известным рядам и подставляет новый x. Синтаксис по справке: =ПРЕДСКАЗ.ЛИНЕЙН(x;известные_y;известные_x), где x — точка, для которой нужен ответ, известные_y — зависимый ряд, известные_x — независимый^[1]. В английском интерфейсе функция называется FORECAST.LINEAR.

В Excel 2016 старую ПРЕДСКАЗ заменила ПРЕДСКАЗ.ЛИНЕЙН; прежняя функция осталась для обратной совместимости, но для новых книг справка рекомендует новую^[1]. Синтаксис у обеих одинаковый, поэтому старые формулы переписывать не обязательно. Прогноз в Excel по прямой подходит, когда показатель растёт или падает примерно на одну и ту же величину каждый период: выручка нового магазина, число подписчиков, расход материалов. В примере справки ряды y 6, 7, 9, 15, 21 и x 20, 28, 31, 38, 40 при x = 30 дают 10,607253^[1].

Функция прогноза не знает, что такое месяц: она видит только числа. Поэтому месяцы удобнее нумеровать 1, 2, 3 или хранить как даты — так шаг шкалы остаётся ровным. Если в ряду есть текстовые подписи вроде «итого», уберите их из диапазона: функция ждёт только числа.

Разобравшись с аргументами, переходим к примеру продаж — на нём видно, как проверить ответ.

Пошаговый пример: продажи на три месяца вперёд

ПРЕДСКАЗ.ЛИНЕЙН в этом примере продолжает рост продаж за восемь месяцев и даёт оценку на девятый. Номера месяцев стоят в A2:A9, продажи в тысячах рублей — в B2:B9.

1. Введите номера месяцев и продажи — без пропусков и повторов.
2. В D2 введите =ПРЕДСКАЗ.ЛИНЕЙН(9;B2:B9;A2:A9) и нажмите Enter: получится 170,6.
3. Для трёх месяцев сразу введите в D4 =ТЕНДЕНЦИЯ(B2:B9;A2:A9;{9;10;11}) — ответ разольётся на три ячейки: 170,6, 176,9 и 183,2^[8].
4. Проверьте прогноз на прошлом: посчитайте ПРЕДСКАЗ.ЛИНЕЙН для месяца 8 по первым семи и сравните с фактом.
5. Округлите ответ для отчёта и подпишите, что это линейная оценка.

Наклон прямой здесь — около 6,27 тысячи в месяц, то есть продажи растут почти равномерно^[8]. Прогноз в Excel на таких данных выглядит правдоподобно, но чем дальше от последнего месяца, тем больше ошибка: оценку на квартал вперёд стоит пересчитывать каждый месяц, когда приходит новый факт. Если ряды разной длины, функция вернёт #Н/Д: в движке тренажёра так ведёт себя и ПРЕДСКАЗ.ЛИНЕЙН с рядами из восьми и четырёх чисел^[8]. Постройте этот прогноз в уроке тренажёра: урок меняет продажи и проверяет, что формула ссылается на оба ряда.

Однако прямая хороша только для ровного роста, а у сезонных продаж правила другие.

ТЕНДЕНЦИЯ, ПРЕДСКАЗ.ETS или лист прогноза: чем отличаются инструменты

ТЕНДЕНЦИЯ отличается от ПРЕДСКАЗ.ЛИНЕЙН тем, что возвращает сразу массив значений по линейному тренду, а ПРЕДСКАЗ.ETS учитывает сезонность. ТЕНДЕНЦИЯ подгоняет прямую методом наименьших квадратов и возвращает значения y для массива новых x^[2]. В Microsoft 365 её достаточно ввести в одну ячейку и нажать Enter, а в старых версиях подтверждают сочетанием Ctrl+Shift+Enter^[2]. Ответ занимает столько ячеек, сколько новых x передано в третьем аргументе.

ПРЕДСКАЗ.ETS работает по алгоритму AAA экспоненциального сглаживания и требует шкалу с постоянным шагом^[3]. Аргумент сезонности по умолчанию равен 1 — Excel ищет её сам, ноль отключает сезонность, а максимум — 8760, число часов в году^[3]. Если дата цели стоит раньше конца шкалы, функция вернёт #ЧИСЛО!^[3]. Пропуски по умолчанию заполняются средним соседних точек, а одинаковые даты сводятся средним — оба правила меняются последними аргументами^[3].

Семейство сезонных функций шире: справочник Microsoft перечисляет также ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ, ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ и ПРЕДСКАЗ.ETS.СТАТ^[6].

Именно поэтому для отчёта с сезонностью удобнее отдельный инструмент — лист прогноза.

Лист прогноза на практике: сезонность и доверительный интервал

Лист прогноза строит прогноз с сезонностью одной командой и выводит таблицу и диаграмму на новом листе. Выделите столбец дат и столбец значений, затем на вкладке «Данные» в группе Forecast нажмите Forecast Sheet — так кнопка названа в английской справке^[4]. Выберите линейный или столбчатый график, задайте дату окончания и нажмите Create.

Шкала должна идти с постоянным интервалом, но до 30% точек могут отсутствовать^[4]. Ручную сезонность ставят, только если в истории не меньше двух полных циклов^[4]. Расчёт внутри идёт по тому же алгоритму AAA ETS, что и у сезонной функции^[4].

Если начать расчёт раньше последней фактической точки, лист покажет, как модель угадала бы прошлое, — это простой способ проверить её до отчёта^[4]. На Mac линейную оценку можно получить и без формул: выделите не менее двух ячеек ряда и протяните маркер заполнения^[5].

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

Когда прогноз в Excel не подойдёт

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

• Если продажи сезонные, линейная функция проведёт прямую мимо пиков — нужна сезонная модель ETS^[3].
• Если пропусков на шкале больше 30%, данные выходят за предел, который справка допускает для сезонной модели^[3].
• Когда ручная сезонность длиннее половины истории, двух циклов не наберётся, и параметр задавать нельзя^[4].
• При работе в Excel в браузере или на телефоне сезонная функция недоступна^[3].
• Если на шкале повторяются даты, функция ETS вернёт #ЗНАЧ!^[3]: сведите дубли заранее.

ПРЕДСКАЗ.ЛИНЕЙН справка Microsoft относит к Microsoft 365, включая Mac, и к Excel 2024, Excel 2021, Excel 2019 и Excel 2016^[1], а лист с диаграммой описан для Microsoft 365, Excel 2024 и Excel 2021^[4]. В Google Таблицах линейная функция тоже есть: справка Google перечисляет её среди функций, связанных с КОРРЕЛ^[7]. Перед отчётом сверьте число на трёх-четырёх последних месяцах факта: так видно, насколько модель промахивается. Связь двух показателей между собой, а не во времени, разобрана на странице о связи двух рядов и уравнении прямой, а сравнение прогноза с фактом — на странице о план-факт анализе.