В современном бизнес‑аналитическом арсенале практически каждый специалист сталкивается с задачей прогноза – предсказать будущие показатели на основе исторических данных. Microsoft Excel, будучи одной из самых популярных программ для работы с таблицами, предоставляет широкий набор функций и инструментов для построения прогнозов. В этой статье мы подробно разберём, как использовать возможности Excel для предсказания значений, какие функции доступны, в чём их различия и какие модели лучше подходят для разных типов данных.
Что такое прогноз в Excel?
Прогноз – это статистический метод, позволяющий оценить будущие значения (например, продажи, спрос, количество заявок) на основе уже имеющихся наблюдений, зависящих от времени. В Excel прогноз строится с помощью:
- встроенных функций (
ПРЕДСКАЗ,ПРЕДСКАЗ.LINEAR,ПРЕДСКАЗ.ETS); - интерактивного инструмента Прогноз листа (англ. Forecast Sheet), который автоматически создаёт таблицу и диаграмму.
Все эти методы основаны на различных алгоритмах: от простейшей линейной регрессии до более сложного экспоненциального сглаживания (ETS).
Основные функции для прогнозирования
2.1 Функция ПРЕДСКАЗ (FORECAST)
Синтаксис функции:
ПРЕДСКАЗ(x; известные_значения_y; известные_значения_x)Где:
- x – значение независимой переменной, для которого требуется предсказать y;
- известные_значения_y – массив исторических значений зависимой переменной;
- известные_значения_x – массив соответствующих независимых переменных (обычно даты или порядковые номера периода).
Эта функция реализует простую линейную регрессию (метод наименьших квадратов). Она подходит, когда зависимость между x и y почти линейна и сезонных колебаний нет.
2.2 Функция ПРЕДСКАЗ.LINEAR (FORECAST.LINEAR)
Работает точно так же, как ПРЕДСКАЗ, но доступна в более новых версиях Excel и имеет более понятный английский идентификатор. Рекомендуется использовать её в новых проектах для совместимости.
2.3 Функция ПРЕДСКАЗ.ETS (FORECAST.ETS)
Эта функция реализует алгоритм экспоненциального сглаживания (ETS) – более современный и гибкий метод, который учитывает:
- тренд (рост или падение);
- сезонность (периодические колебания);
- отклонения и случайные шумы.
Синтаксис (упрощённый):
ПРЕДСКАЗ.ETS(целевое_значение_даты; известные_значения_y; известные_значения_дат; [интервал_сезонности]; [данные_заполнения]; [агрегат]; [прогноз_в_будущее])Важно помнить, что ПРЕДСКАЗ.ETS недоступна в Excel Online, iOS и Android – её можно использовать только в настольных версиях Windows и macOS.
Выбор модели: аддитивная vs мультипликативная сезонность
При работе с временными рядами часто возникает вопрос о типе сезонности:
- Аддитивная модель (additive) – используется, когда амплитуда сезонных колебаний почти постоянна независимо от уровня ряда. Пример: ежемесячные расходы на электроэнергию, меняющиеся примерно на ±50 ₽ независимо от общего уровня расходов.
- Мультипликативная модель (multiplicative) – применяется, когда амплитуда колебаний пропорциональна уровню ряда. Пример: продажи в праздничные дни, где рост в процентах от базового уровня может составлять 20‑30 %.
В Excel тип сезонности автоматически определяет алгоритм ETS, но при необходимости можно задать параметр интервал_сезонности вручную, указав, например, 12 для месячных данных.
Как построить прогноз с помощью Прогноза листа
- Подготовьте данные – столбец с датами (или порядковыми номерами) и соответствующий столбец со значениями.
- Выделите диапазон
A1:B<последняя строка>. - Перейдите в Вставка → Прогноз → Прогноз листа (в английской версии – Data → Forecast Sheet).
- В открывшемся диалоговом окне укажите:
- период прогноза (например, 12 месяцев вперёд);
- тип сезонности (автоматически определяется, но может быть переопределён);
- настройки доверительного интервала (обычно 95 %).
- Нажмите Создать. Excel сгенерирует новый лист с таблицей прогнозных значений и графиком, где будут отображены исторические данные, прогноз и диапазоны доверия.
Такой подход особенно полезен, если вам нужен быстрый визуальный результат без написания формул.
Примеры практического применения
5.1 Прогноз продаж
Допустим, у вас есть данные о продажах за последние 24 месяца (столбец A – даты, столбец B – объём продаж). Чтобы предсказать продажи на следующий год, можно использовать:
=ПРЕДСКАЗ.ETS(ДАТА(2027;1;1); B2:B25; A2:A25; 12)Здесь 12 указывает на месячную сезонность.
5.2 Прогноз расходов с линейной тенденцией
Если сезонных колебаний нет, а наблюдается постепенный рост расходов, удобно воспользоваться функцией ПРЕДСКАЗ:
=ПРЕДСКАЗ(27; B2:B25; A2:A25)Где 27 – номер будущего месяца (например, 27‑й месяц от начала наблюдения).
5.3 Прогноз нагрузки на сервер
Данные о нагрузке собираются каждые 15 минут. Сезонность здесь может быть дневной (24 ч) и недельной (7 дней). В таком случае задаём интервал_сезонности = 96 (24 ч × 4 измерения в час) и используем ПРЕДСКАЗ.ETS с параметром агрегат = 1 (сумма) или 2 (среднее) в зависимости от задачи.
Ограничения и лучшие практики
- Качество входных данных – отсутствие пропусков, корректный тип дат и однородность измерений критичны для точного прогноза.
- Объём исторических данных – для надёжного ETS‑моделирования рекомендуется иметь минимум 2‑3 полного сезона (например, 24‑36 месяцев при месячной сезонности).
- Проверка модели – сравните прогнозные значения с фактическими (если они уже появились) и вычислите ошибки (MAE, MAPE). При больших отклонениях попробуйте изменить тип сезонности или использовать более сложные модели (ARIMA, Prophet) в специализированных инструментах.
- Не переусердствуйте с детализацией – слишком мелкие интервалы (например, 5‑минутные данные) могут привести к переобучению модели.
- Обновляйте прогноз – добавляйте новые наблюдения и пересчитывайте модель, чтобы учесть последние тренды.
Часто задаваемые вопросы
7.1 Почему функция ПРЕДСКАЗ.ETS не работает в Excel Online?
Алгоритм ETS требует значительных вычислительных ресурсов и доступа к локальному движку Excel, который в веб‑версии не реализован. Поэтому для онлайн‑версий рекомендуется использовать простые функции ПРЕДСКАЗ или экспортировать данные в десктопную версию.
7.2 Можно ли комбинировать несколько моделей?
Да. Часто используют гибридный подход: сначала снимают тренд линейной регрессией, а оставшиеся сезонные остатки прогнозируют методом ETS. В Excel это реализуется с помощью дополнительных столбцов (вычисление тренда, остатка, затем применение ПРЕДСКАЗ.ETS к остатку).
7.3 Как задать доверительный интервал?
В функции ПРЕДСКАЗ.ETS параметр прогноз_в_будущее позволяет указать количество будущих точек, а доверительный интервал задаётся в диалоговом окне Прогноз листа. Для чистой функции диапазон доверия не выводится, но его можно рассчитать вручную, используя стандартную ошибку прогноза.
Excel предоставляет мощный, но при этом доступный набор инструментов для предсказания будущих значений. Выбор между простой линейной регрессией (ПРЕДСКАЗ) и более гибким экспоненциальным сглаживанием (ПРЕДСКАЗ.ETS) зависит от характера ваших данных: наличие тренда, сезонных колебаний и объёма исторической информации.
Следуя рекомендациям, описанным в этой статье, вы сможете быстро построить надёжный прогноз, визуализировать его в виде графика и использовать полученные результаты для принятия обоснованных бизнес‑решений.
Дата публикации: | Автор: виртуальный помощник ChatGPT