Как проверить прогноз в Excel до использования в плане

Power-инструменты

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

5 мин чтения 1 октября 2026 Алексей Борисов

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

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

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

Возьмём синтетический ряд продаж за 36 месяцев в условных единицах. В нём есть рост и сезонные колебания. Каждая строка таблицы ниже содержит три последовательных участка ряда. Для расчёта перенесите их в один столбец строго по номерам месяцев.

Месяц	Продажи	Месяц	Продажи	Месяц	Продажи
1	103,40	13	132,20	25	161,00
2	94,42	14	120,91	26	147,41
3	106,98	15	134,92	27	162,86
4	111,79	16	141,17	28	170,54
5	117,72	17	148,25	29	178,78
6	127,84	18	159,52	30	191,20
7	123,64	19	153,88	31	184,12
8	113,62	20	141,56	32	169,50
9	129,46	21	159,42	33	189,37
10	136,40	22	168,08	34	199,76
11	148,15	23	182,14	35	216,12
12	169,44	24	206,88	36	244,32

В A2:A37 запишите номера от 1 до 36, в B2:B37 — соответствующие продажи. Первые 30 наблюдений занимают строки 2–31. Проверочные месяцы 31–36 находятся в строках 32–37.

Подход с разделением обучающего и проверочного периодов описан в учебнике Forecasting Principles and Practice. Для нашего упражнения зафиксируем один момент прогноза и одинаковый горизонт в шесть месяцев для всех методов.

Линейный прогноз

Создайте лист «Линейный» с этими двумя столбцами. В C32 запишите следующую формулу и протяните до C37.

=ПРЕДСКАЗ(A32;$B$2:$B$31;$A$2:$A$31)

Изменяется номер прогнозируемого месяца. Диапазоны истории закреплены и заканчиваются месяцем 30. Функция ПРЕДСКАЗ сохраняется для совместимости, а в новых версиях доступна ПРЕДСКАЗ.ЛИНЕЙН. Назначение функций описано в справке Microsoft.

Первый проверочный прогноз получится примерно 186,50. Факт месяца 31 равен 184,12. Абсолютная ошибка составляет около 2,38 условной единицы.

Среднее за три месяца

На отдельном листе «Среднее» оставьте такие же исходные столбцы. Прогноз месяца 31 равен среднему фактов за месяцы 28, 29 и 30. В C32 введите =СРЗНАЧ(B29:B31).

Для месяца 32 факт месяца 31 ещё неизвестен в принятом моменте прогноза. Поэтому используем два последних доступных факта и уже рассчитанный прогноз. В C33 запишите =(B30+B31+C32)/3, в C34 — =(B31+C32+C33)/3.

Начиная с C35 усредняем три предыдущих прогноза. Запишите =СРЗНАЧ(C32:C34) и протяните до C37. Такой расчёт называют рекурсивным — новые шаги опираются на ранее полученные прогнозные значения.

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

Повторение прошлого сезона

На третьем листе «Сезонный» прогноз месяца 31 равен факту месяца 19. В C32 запишите =B20 и скопируйте до C37. Для каждого проверочного месяца получится значение соответствующего месяца предыдущего года.

Метод повторяет сезонный рисунок без отдельной поправки на рост. В нашем ряду это существенное ограничение. На других данных результат может оказаться удачнее, поэтому такой простой ориентир полезно включить в сравнение.

Считаем одинаковую ошибку

На каждом из трёх листов в D32 запишите =ABS(B32-C32) и скопируйте до D37. Затем найдите среднее шести ошибок формулой =СРЗНАЧ(D32:D37). Получится MAE — средняя абсолютная ошибка в тех же единицах, что и продажи.

Метод	MAE на месяцах 31–36
Линейный прогноз	15,50
Среднее за три месяца	22,02
Повторение прошлого сезона	31,87

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

Учебная книга прогнозов, лист «Сравнение». Ошибка MAE рассчитана на одних и тех же шести месяцах. Снимок листа в LibreOffice Calc. Нажмите, чтобы открыть крупнее.

Перед следующим планом

После сравнения можно заново построить выбранный метод на всех 36 известных месяцах и спрогнозировать месяцы 37–42. Граница доступной информации теперь сдвинулась, поэтому изменятся и параметры расчёта.

Проверьте, не произошло ли изменений, которых нет в истории — запуска нового продукта, смены цен, потери крупного клиента. Для таких условий понадобятся дополнительные сценарии и предметное объяснение.

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

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

Практика на корпоративном обучении

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