Прогнозирование в Excel: основные и продвинутые формулы

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

Содержание

1. Базовые формулы: FORECAST.LINEAR и TREND
2. Продвинутые функции: FORECAST.ETS, FORECAST.ETS.SEASONALITY и др.
3. Формулы экспоненциального роста: GROWTH
4. Пошаговый процесс построения прогноза
5. Лучшие практики и типичные ошибки
6. Полезные ресурсы и ссылки

Microsoft Excel – один из самых мощных и универсальных инструментов для работы с данными. С помощью встроенных функций прогнозирования (prediction) пользователь может:

• Анализировать тренды продаж, доходов, расходов.
• Строить линейные и сезонные модели.
• Автоматизировать расчёты, используя повторяемую логику.

Ниже рассмотрим самые популярные формулы, их синтаксис и примеры применения.

Базовые формулы

FORECAST.LINEAR (ранее FORECAST)

Простая линейная регрессия, которая предсказывает значение y для заданного x на основе известных пар (x, y).

FORECAST.LINEAR(x, known_y's, known_x's)

• x – значение независимой переменной, для которого нужно предсказать y.
• known_y’s – диапазон известных значений зависимой переменной.
• known_x’s – диапазон известных значений независимой переменной.

Пример: Предсказываем продажи в 2027 году (x = 2027) на основе данных за 2020‑2026 годы.

=FORECAST.LINEAR(2027; B2:B8; A2:A8)

TREND

Возвращает массив значений, соответствующих линейному тренду, который проходит через известные точки.

TREND(known_y's, [known_x's], [new_x's], [const])

• known_y’s – известные значения зависимой переменной.
• known_x’s – (необязательно) известные значения независимой переменной.
• new_x’s – (необязательно) новые x‑значения, для которых нужен прогноз.
• const – (необязательно) логическое значение, указывающее, следует ли принудительно использовать свободный член (TRUE) или нет (FALSE).

Для получения прогноза на 5 последующих периодов:

=TREND(B2:B8; A2:A8; A9:A13)

Продвинутые функции

FORECAST.ETS

Экспоненциально‑сглаженный прогноз (ETS – Exponential Triple Smoothing) учитывает сезонность и тренд. Идеально подходит для временных рядов с ярко выраженными сезонными колебаниями.

FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

• target_date – дата, для которой нужен прогноз.
• values – диапазон исторических значений.
• timeline – диапазон дат, соответствующий values.
• seasonality – период сезонности (0 = авто, 1 = без сезонности, >1 = количество периодов).
• data_completion – как обрабатывать пропуски (0 = пропуск, 1 = линейная интерполяция).
• aggregation – метод агрегации при наличии нескольких значений на одну дату.

Пример: Прогноз продаж на 1‑й квартал 2027 года при месячной сезонности (12 месяцев).

=FORECAST.ETS(DATE(2027;1;1); B2:B37; A2:A37; 12)

FORECAST.ETS.SEASONALITY

Возвращает оценённый период сезонности, обнаруженный функцией FORECAST.ETS. Полезно, когда вы не уверены в длительности сезона.

FORECAST.ETS.SEASONALITY(values, timeline, [data_completion])

FORECAST.ETS.CONFINT

Возвращает доверительный интервал (confidence interval) для прогноза ETS, позволяя оценить риск отклонения.

FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])

Формулы экспоненциального роста

GROWTH

Модель экспоненциального (мультипликативного) роста, аналогичная LINEST, но возвращает предсказанные значения.

GROWTH(known_y's, [known_x's], [new_x's], [const])

Пример: Предсказываем количество подписчиков, растущее в 2‑кратном темпе каждый месяц.

=GROWTH(B2:B8; A2:A8; A9:A13)

Пошаговый процесс построения прогноза в Excel

1. Подготовьте данные – убедитесь, что в столбце timeline нет пропусков и даты упорядочены по возрастанию.
2. Постройте график – визуальная проверка поможет увидеть наличие тренда и сезонности;
3. Выберите модель:
   • Если данные линейны – используйте FORECAST.LINEAR или TREND.
   • Если присутствует сезонность – предпочтительнее FORECAST.ETS.
   • Если рост экспоненциальный – применяйте GROWTH.
4. Вставьте формулу в ячейку, где нужен результат.
5. Проверьте точность:
   • Разделите набор данных на обучающую и тестовую части.
   • Сравните предсказанные значения с реальными, используя MAPE (Mean Absolute Percentage Error) или R².
6. Добавьте доверительные интервалы (для ETS) с помощью FORECAST.ETS.CONFINT.
7. Автоматизируйте – создайте шаблон с динамическими именованными диапазонами (используйте OFFSET + COUNTA) для автоматического расширения данных.

Лучшие практики и типичные ошибки

• Не игнорируйте пропуски. Функция FORECAST.ETS имеет параметр data_completion, который помогает корректно обрабатывать отсутствующие значения.
• Проверяйте предположения модели. Линейная регрессия требует линейной зависимости; если график выглядит криволинейным, лучше использовать GROWTH или ETS.
• Не переобучайте. Чем больше исторических точек, тем стабильнее прогноз, но слишком «старые» данные могут ввести в заблуждение.
• Используйте названия диапазонов вместо абсолютных ссылок – это повышает читаемость формул.
• Сравнивайте несколько моделей. Иногда комбинирование прогнозов (среднее, взвешенное) даёт более надёжный результат.
• Обновляйте модель регулярно. При появлении новых данных пересчитывайте параметры (особенно сезонность).

Полезные ресурсы и ссылки

• Официальная справка Microsoft Excel – подробное описание всех функций.
• Microsoft Learn – курсы по Excel – бесплатные интерактивные уроки.
• YouTube – видеоуроки по FORECAST.ETS.