Эксель формула предсказания

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

Содержание

1. Общие сведения о прогнозировании в Excel
2. Функция FORECAST․LINEAR (раньше FORECAST)
3. Функция FORECAST․ETS
4. Дополнительные инструменты: анализ данных и встроенные мастеры
5. Практические примеры
6. Полезные рекомендации и типичные ошибки

Общее представление о прогнозировании в Excel

Excel предлагает два основных подхода к построению прогноза:

• Линейное предсказание, основано на простом линейном регрессионном уравнении․ Подходит, когда данные почти лежат
  на прямой линии или их тренд можно адекватно описать первой степенью полинома․
• Временные ряды (ETS), более сложный метод, учитывающий сезонность, тренд и случайные колебания․
  Позволяет получать более точные результаты для данных, изменяющихся периодически (например, продажи по месяцам)․

Функция FORECAST․LINEAR (ранее FORECAST)

2․1 Синтаксис

=FORECAST․LINEAR(x, known_y's, known_x's)

• x, значение независимой переменной, для которого требуется предсказать y․
• known_y’s — массив известных значений зависимой переменной (обычно это ваш целевой показатель)․
• known_x’s — массив известных значений независимой переменной (например, даты или порядковые номера)․

2․2 Как работает функция

Формула рассчитывает коэффициенты линейной регрессии y = a + b·x по методу наименьших квадратов и подставляет
требуемое x․ Результат — прогнозное значение y․

2․3 Пример

Допустим, у вас есть данные о продажах за 6 месяцев:

Прогноз продаж на 7‑й месяц:

=FORECAST․LINEAR(7; B2:B7; A2:A7)

Результат, 210 единиц․

Функция FORECAST․ETS

3․1 Что такое ETS?

ETS (Exponential Triple Smoothing) — метод экспоненциального сглаживания, учитывающий три компонента:

• Тренд — общий рост или падение․
• Сезонность — повторяющиеся шаблоны (неделя, месяц, квартал)․
• Ошибка — случайные флуктуации․

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

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

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

3․3 Пример с сезонностью

У вас есть ежемесячные продажи за два года․ Сезонный период – 12 месяцев․

=FORECAST․ETS(DATE(2027;1;1); C2:C25; B2:B25; 12; 1; 1)

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

Дополнительные инструменты Excel для прогнозирования

4․1 Мастер «Прогноз» (Excel 365, Excel 2021)

Вкладка Данные → Прогноз → Лист прогнозов автоматически создаёт график, рассчитывает прогнозные значения и
выводит доверительные интервалы․ Удобно, когда не требуется ручное написание функций․

4․2 Анализ данных → Регрессия

Пакет «Анализ данных» (надстройка «Analysis ToolPak») позволяет построить множественную регрессию, вывести статистику
(R‑квадрат, p‑значения) и использовать полученные коэффициенты в собственных формулах․

4․3 Динамические массивы и функция FORECAST (новый синтаксис)

В последних версиях Excel функция FORECAST стала алиасом к FORECAST․LINEAR, а подробные
варианты (ETS) вынесены в отдельные функции․ Это упрощает обучение и делает формулы более читаемыми․

Практические примеры

5․1 Прогноз спроса на товар

1. Подготовьте таблицу: в колонке A — даты, в колонке B, количество продаж;
2. Выделите диапазон с данными, откройте Данные → Прогноз → Лист прогнозов․
3. Установите конец диапазона (например, 3 месяца вперёд) и нажмите Создать;
4. Excel построит график, отобразит прогнозные точки и confidence‑intervals․

5․2 Прогноз объёма посещаемости сайта с учётом недельного цикла

Данные собраны за 30 дней, наблюдается рост по пятницам․

=FORECAST․ETS(A31; B2:B31; A2:A31; 7; 1; 1)

Здесь 7 — недельный сезонный период, A31 — дата, на которую нужен прогноз․

5․3 Расчёт прогноза дохода с помощью нескольких факторов (мульти‑регрессия)

Если у вас есть несколько независимых переменных (рекламный бюджет, количество сотрудников, сезонный индекс),
используйте Analysis ToolPak → Регрессия и полученные коэффициенты разместите в формуле:

=($coeff0) + ($coeff1)B2 + ($coeff2)C2 + ($coeff3)*D2

Полезные рекомендации и типичные ошибки

• Проверяйте линейность․ Применять FORECAST․LINEAR к явно нелинейным данным (например, экспоненциальный рост) приводит к большим ошибкам․
• Учитывайте сезонность․ Если в данных присутствует регулярный цикл, предпочтительнее FORECAST․ETS․
• Не забудьте про пропуски․ Функция FORECAST․ETS может игнорировать пустые ячейки, но лучше заполнить их явно, иначе будет смещён тренд․
• Оценка точности․ После получения прогноза сравните предсказанные значения с реальными, используя
  MAPE (Mean Absolute Percentage Error) или RMSE․ Высокие ошибки указывают на необходимость переоценки модели․
• Доверительные интервалы․ В мастере «Прогноз» они отображаются автоматически․ При ручных формулах их можно добавить через
  CONFIDENCE․NORM или CONFIDENCE․T․
• Не переобучайте․ Чем больше параметров модели (сезонность, уровни сглаживания), тем выше риск «прокачать» данные под шум․

Прогнозирование в Excel — мощный и доступный способ получить быстрые оценки будущих показателей без привлечения
специализированных аналитических систем․ Для простых линейных тенденций достаточно функции FORECAST․LINEAR,
а для сложных временных рядов с сезонностью — FORECAST․ETS и встроенный мастер «Прогноз»․
Главное — правильно подготовить данные, понять структуру их изменения и регулярно проверять точность полученных
предсказаний․

Соблюдая рекомендации, изложенные в этой статье, вы сможете уверенно использовать Excel как инструмент
бизнес‑аналитики и принимать более обоснованные решения․