Формула предсказания в excel

Прогнозирование – одна из самых востребованных задач в бизнес‑аналитике, финансовом планировании и любой области, где необходимо оценить будущие показатели на основе исторических данных․ В Microsoft Excel для этой цели существует несколько встроенных функций: ПРЕДСКАЗ (FORECAST), ПРЕДСКАЗ․ЛИНЕЙН (FORECAST․LINEAR), ПРЕДСКАЗ․ETS (FORECAST․ETS), а также вспомогательные функции ТЕНДЕНЦИЯ (TREND), РОСТ (GROWTH) и ЛИНЕСТ (LINEST)․ В статье мы подробно разберём, как работают эти формулы, какие параметры они принимают, какие ограничения имеют и как их применять на практике․

Что такое функция ПРЕДСКАЗ?

Функция ПРЕДСКАЗ (в англоязычной версии – FORECAST) реализует простую линейную регрессию․ Она вычисляет ожидаемое значение y для заданного x на основе известных пар «x – y»․ Формула функции выглядит так:

ПРЕДСКАЗ(целевое_значение_x; известные_значения_y; известные_значения_x)

Где:

• целевое_значение_x – значение переменной x, для которого нужно предсказать y․
• известные_значения_y – массив (столбец/строка) исторических значений зависимой переменной․
• известные_значения_x – массив соответствующих независимых переменных․

Внутри Excel использует уравнение линии регрессии:

y = a + b·x, где

• a – свободный член (пересечение с осью Y),
• b – коэффициент наклона, вычисляемый по формуле b = Σ[(x‑x̄)(y‑ȳ)] / Σ[(x‑x̄)²]․

Таким образом, функция ПРЕДСКАЗ фактически подставляет x в полученную линейную модель и выдаёт предсказанное y․

1․1 Пример использования ПРЕДСКАЗ

1. В ячейках A2:A7 записаны месяцы (1…6)․
2. В ячейках B2:B7 – продажи за каждый месяц․
3. Чтобы предсказать продажи за 7‑й месяц, вводим формулу:

   =ПРЕДСКАЗ(7; B2:B7; A2:A7)

Excel вернёт значение, соответствующее продолжению линейного тренда․

ПРЕДСКАЗ․ЛИНЕЙН (FORECAST․LINEAR)

Начиная с Excel 2016, функция ПРЕДСКАЗ была заменена на более гибкую ПРЕДСКАЗ․ЛИНЕЙН․ Синтаксис идентичен, но ПРЕДСКАЗ оставлена в режиме совместимости․

ПРЕДСКАЗ․ЛИНЕЙН(целевое_значение_x; известные_значения_y; известные_значения_x)

Эта функция поддерживает работу с массивами, динамическими диапазонами и имеет лучшую обработку ошибок․

ПРЕДСКАЗ․ETS (FORECAST․ETS) – прогноз с учётом сезонности

Линейный прогноз подходит только тогда, когда данные действительно следуют прямой линии․ В реальном мире большинство временных рядов имеет сезонные колебания (месячные, квартальные, годовые)․ Для таких задач в Excel предусмотрена функция ПРЕДСКАЗ․ETS (Exponential Triple Smoothing)․

3;1 Синтаксис

ПРЕДСКАЗ․ETS(
 целевая_дата;
 известные_значения;
 известные_времена;
 [сезонность];
 [заполнение_данных];
 [агрегирование];
 [уровень_доверия];
 [прогноз_периодов]
)

Параметры:

• целевая_дата – дата (или номер периода), для которой требуется предсказать значение․
• известные_значения – массив исторических измерений (например, продажи)․
• известные_времена – массив дат/номеров, соответствующих измерениям․
• сезонность (необязательно) – длина сезонного цикла․
  • 0 – автоматическое определение․
  • 1 – отсутствие сезонности․
  • Любое положительное целое (например, 12 для месячной сезонности)․
• заполнение_данных – как обрабатывать пропуски: 0 (по умолчанию) – игнорировать, 1 – заполнять линейной интерполяцией․
• агрегирование – способ объединения нескольких значений в одну точку (0 – среднее, 1 – сумма, 2 – минимум, 3 – максимум и т․д․)․
• уровень_доверия – процент доверительного интервала (по умолчанию 95%)․
• прогноз_периодов – количество периодов, которые нужно спрогнозировать вперёд․

3․2 Пример: прогноз продаж с сезонностью

1. В столбце A – даты (, , …, )․
2. В столбце B – продажи за каждый месяц․
3. Прогнозируем продажи на 3 месяца вперёд (январь‑март 2024) с сезонностью 12:

   =ПРЕДСКАЗ․ETS(
    ДАТА(2024;1;1);
    B2:B13;
    A2:A13;
    12;
    0;
    0;
    95;
    3
    )

Результат будет массивом из трёх значений – прогнозных продаж за каждый из указанных месяцев․

3․3 ПРЕДСКАЗ․ETS․ДОВИНТЕРВАЛ и ПРЕДСКАЗ․ETS․ДОВЕРИТЕЛЬНОСТЬ

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

• ПРЕДСКАЗ․ETS․ДОВИНТЕРВАЛ – возвращает нижнюю и верхнюю границы интервала․
• ПРЕДСКАЗ․ETS․ДОВЕРИТЕЛЬНОСТЬ – вычисляет процент доверия, который использовался․

Пример получения нижней границы:

=ПРЕДСКАЗ․ETS․ДОВИНТЕРВАЛ(
 ДАТА(2024;1;1);
 B2:B13;
 A2:A13;
 12;
 0; 0;
 95;
 1; // 1 – нижняя граница
)

3․4 Ошибки и их причины

• ЧИСЛО! – возникает, если целевая дата находится за пределами диапазона известные_времена и параметр прогноз_периодов не указан․
• #Н/Д – когда в массиве известные_значения слишком много пустых ячеек или данные не упорядочены по возрастанию дат․
• #ЗНАЧ! – неверный тип параметров (например, строка вместо числа)․

Другие полезные функции для прогнозов

4․1 ТЕНДЕНЦИЯ (TREND)

Функция ТЕНДЕНЦИЯ возвращает массив предсказанных значений, построенных по той же линейной модели, что и ПРЕДСКАЗ․ Удобна, когда нужно сразу получить прогноз для нескольких x․

=ТЕНДЕНЦИЯ(известные_значения_y; известные_значения_x; новые_значения_x; [константа])

4․2 РОСТ (GROWTH)

Для экспоненциальных зависимостей используется РОСТ․ Формула аналогична ТЕНДЕНЦИЯ, но работает в логарифмических координатах․

=РОСТ(известные_значения_y; известные_значения_x; новые_значения_x; [константа])

4․3 ЛИНЕСТ (LINEST)

Если требуется получить не только предсказание, но и коэффициенты регрессии, их ошибки и статистику, используйте массивную функцию ЛИНЕСТ․ Возвращает матрицу, содержащую b, a, стандартные ошибки, R‑квадрат и т․д․

{=ЛИНЕСТ(известные_значения_y; известные_значения_x; [константа]; [статистика])}

4․4 Сводка по функциям

Как построить «Лист прогноза» в Excel

Начиная с Excel 2016, в меню Данные есть готовый мастер Лист прогноза․ Он автоматически использует ПРЕДСКАЗ․ETS и генерирует график с доверительными интервалами․

1. Выделите две колонки: даты и соответствующие значения․
2. Перейдите в Данные → Лист прогноза → Создать лист прогноза․
3. В открывшемся окне укажите:
   • Период прогноза (например, 12 месяцев)․
   • Уровень доверия (по умолчанию 95%)․
   • Сезонность – оставить Авто или задать вручную․
4. Нажмите Создать․ Excel создаст новый лист с таблицей прогнозов, графиком и параметрами модели․

Это самый быстрый способ получить профессиональный прогноз без написания формул․

Практический пример: прогноз продаж и восстановление сезонности

6․1 Данные

Допустим, у вас есть продажи за 24 месяца (двухлетний период)․ Таблица выглядит так:

6․2 Шаг 1 – построим линейный прогноз

В ячейке D2 вставляем формулу:

=ПРЕДСКАЗ․ЛИНЕЙН(A25; B2:B25; A2:A25)

где A25 – дата следующего месяца ․ Получим базовое значение без учёта сезонности․

6․3 Шаг 2 – учтём сезонность с помощью ПРЕДСКАЗ․ETS

В ячейке E2 пишем:

=ПРЕДСКАЗ․ETS(
 A25;
 B2:B25;
 A2:A25;
 12; // месячная сезонность
 0; 0;
 95;
 1 // один период вперёд
)

Эта формула вернёт более точный прогноз, учитывающий повторяющиеся колебания․

6․4 Шаг 3 – восстановление сезонности (коэффициенты)

Для восстановления сезонных коэффициентов можно воспользоваться функцией ПРЕДСКАЗ․ETS․СТАТ с типом статистики 2 («Seasonality Index»)․ Пример:

=ПРЕДСКАЗ․ETS․СТАТ(
 B2:B25;
 A2:A25;
 2; // тип статистики – сезонный коэффициент
 12;
 0;
 0
)

Полученный массив коэффициентов (12 значений) умножаем на линейный тренд, получая итоговый прогноз с учётом как тренда, так и сезонности․

Часто задаваемые вопросы

• Можно ли использовать ПРЕДСКАЗ․ETS для нерегулярных дат? – Да, но массив известные_времена должен быть упорядочен по возрастанию, а интервалы между датами могут быть разными․ При больших пропусках рекомендуется задать параметр заполнение_данных=1․
• Что делать, если функция возвращает #Н/Д? – Проверьте, что в диапазонах нет текстовых значений, все даты находятся в правильном формате и нет дублирующихся меток времени․
• Можно ли прогнозировать отрицательные значения? – Да, но для экспоненциальных методов (ETS) отрицательные значения могут вызвать ошибку, так как алгоритм использует логарифмы․ В таких случаях лучше применять линейный прогноз․
• Как изменить уровень доверия? – Параметр уровень_доверия принимает числа от 0 до 100․ Например, ․․․; 99; ․․․ задаст 99‑% доверительный интервал․

Лучшие практики при работе с прогнозом в Excel

1. Подготовка данных: удалите выбросы, заполните пропуски, убедитесь, что даты последовательны․
2. Визуальный анализ: построьте график исходных данных, чтобы понять, есть ли линейный тренд, экспоненциальный рост или ярко выраженная сезонность․
3. Сравнение моделей: расчитайте прогнозы сразу несколькими функциями (ЛИНЕЙН, ETS, GROWTH) и сравните среднеквадратичную ошибку (RMSE) или MAPE․
4. Используйте доверительные интервалы: они помогают оценить риск отклонения от прогноза․
5. Автоматизация: если прогноз нужен регулярно, создайте шаблон с динамическими диапазонами (Таблицы Excel) и используйте имена диапазонов․
6. Регулярно пересматривайте модель: добавляйте новые данные, проверяйте, не изменился ли характер сезонности․

Excel предоставляет мощный набор инструментов для прогнозирования: от простой линейной регрессии (ПРЕДСКАЗ, ТЕНДЕНЦИЯ) до продвинутого экспоненциального сглаживания с учётом сезонности (ПРЕДСКАЗ․ETS)․ Выбор функции зависит от характера ваших данных:

• Если наблюдается линейный рост/падение – используйте ПРЕДСКАЗ․ЛИНЕЙН или ТЕНДЕНЦИЯ․
• Для экспоненциальных или лог‑линейных зависимостей – РОСТ․
• Если в данных присутствует ярко выраженная сезонность – предпочтительнее ПРЕДСКАЗ․ETS (или готовый «Лист прогноза»)․

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

Удачного прогнозирования!