Как сделать прогноз в Google Таблицах и Excel

Когда нужно спрогнозировать будущие продажи, запасы на складе или другие показатели, ручные расчеты могут занять часы, а итоги получатся с ошибками. С функцией ПРЕДСКАЗ в Excel и Google Таблицах можно быстро и точно предсказать значения на основе ваших данных. Разберемся, когда ее стоит использовать и как построить график за несколько шагов.

Что такое ПРЕДСКАЗ и зачем его используют

Функция ПРЕДСКАЗ в Excel и Google Таблицах прогнозирует будущие значения на основе вашей статистики. Функция работает по принципу линейной регрессии: анализирует известные данные, строит воображаемую прямую линию тренда и вычисляет, какое значение будет следующим. Например, если продажи растут примерно на 10% каждые 30 дней, ПРЕДСКАЗ поможет спрогнозировать, сколько вы заработаете в следующем месяце.

ПРЕДСКАЗ берет два набора данных:

• Значения X — это дата: месяцы, дни или другие периоды.
• Значения Y — числовой показатель: продажи, доходы или посещаемость.

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

Функция ПРЕДСКАЗ полезна в ситуациях, где нужно быстро оценить будущие показатели с одинаковыми постепенными изменениями.

Прогнозирование продаж. Если вы отслеживаете продажи товаров или услуг, ПРЕДСКАЗ посчитает, сколько вы заработаете в следующем периоде. Так можно планировать закупки, рекламные кампании или штат сотрудников.

Управление запасами. Функция помогает оценить, сколько продукции понадобится на складе в будущем. Так бизнес, связанный с логистикой или ретейлом, сможет избежать переизбытка или дефицита товара.

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

Оценка посещаемости мероприятий. Для организаторов ПРЕДСКАЗ может спрогнозировать количество участников на основе данных о предыдущих событиях.

Какие функции ПРЕДСКАЗ бывают

В Excel есть несколько функций семейства ПРЕДСКАЗ:

• ПРЕДСКАЗ.ЛИНЕЙН;
• ПРЕДСКАЗ.ETS;
• ПРЕДСКАЗ.EST.СЕЗОННОСТЬ;
• ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ.

Каждая из функций решает свои задачи и подходит для разных типов данных. В инструментах Excel все функции ПРЕДСКАЗ объединены в «Лист прогноза», так что их необязательно использовать отдельно. Разберем, чем они отличаются и какие возможности дают.

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

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

ПРЕДСКАЗ.ETS. Основная функция для сложных прогнозов. Здесь в расчетах используется алгоритм экспоненциального сглаживания (ETS), который анализирует тренд, сезонность и случайные колебания в данных.

Экспоненциальное сглаживание — способ убрать резкие скачки в статистике и показать общую тенденцию. Например, в один день у магазина 100 продаж, потом — 20 продаж, затем 120 и снова 30. На обычном графике кривая будет как пила с зубчиками, а экспоненциальное сглаживание убирает резкие изменения и показывает только тенденцию: растут продажи или падают.

ПРЕДСКАЗ.EST.СЕЗОННОСТЬ. Функцию используют, чтобы выяснить сезонность данных и ее период, если она есть. Например, функция поможет увидеть повторяющиеся пики продаж каждые 3, 6 или 12 месяцев. Она не создает прогноз, а предоставляет информацию о структуре данных.

ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ. Эта функция дополняет ПРЕДСКАЗ.ETS и показывает доверительный интервал для прогноза. Она рассчитывает диапазон, в котором с высокой вероятностью, например, 95%, окажется реальное значение.

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

ПРЕДСКАЗ.ETS.СТАТ. Функция не делает сам прогноз, а объясняет его параметры. Она помогает понять, как именно Excel делал вычисления и насколько им можно доверять.

Функцию применяют, когда нужно проверить корректность расчетов или сравнить модели. Например, при анализе спроса можно узнать, сколько сезонов учел Excel и есть ли вообще колебания.

Как создать прогноз

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

В Excel

В этой инструкции мы создадим прогноз через инструмент «Лист Прогноза». Он доступен в версии 2016 года и более поздних.

Шаг 1. Подготовьте данные

Заполните два столбца: временную шкалу и числовые показатели.

Шаг 2. Откройте «Лист Прогноза»

Выделите оба ряда данных. Перейдите на вкладку «Данные» → группа «Прогноз» → нажмите «Лист Прогноза».

В открывшемся окне «Создание листа прогноза» выберите тип диаграммы: линейный график или гистограмму для визуализации.

Шаг 3. Укажите начало и конец прогноза

В поле «Конец прогноза» отметьте дату, до которой хотите предсказать значения: например, до 01.12.2025. Чем дальше дата, тем менее точным может быть прогноз, так как он основан на текущем тренде.

Теперь откройте «Параметры». Ниже появятся еще несколько полей для настроек. Первый — «Начало прогноза». Выберите дату, с которой должны начаться вычисления. Например, укажите последний месяц. Если у данных есть сезонные изменения, лучше указывать началом срок за несколько ячеек до последней статистики.

Можно указать дату и с середины временной шкалы. Тогда друг на друга наложатся два графика: с вашими данными и прогнозом. Так получится оценить, насколько точно Excel предсказывает результат.

Шаг 4. Укажите доверительный интервал

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

Более высокий уровень доверия, например, 99%, делает интервал шире, так как Excel учитывает больше возможных отклонений. Более низкий уровень, например, 80%, сужает интервал, но прогноз становится менее надежным.

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

Шаг 5. Настройте сезонность

Сезонность — это повторяющиеся изменения в данных. Например, рост продаж зимой или летом. Если указать правильный цикл, Excel учтет эти всплески и сделает прогноз точнее.

Если вы знаете, что продажи растут каждые три месяца, выберите «Задать вручную» и введите число. Если сезонность слабая или ее нет, Excel построит линейный прогноз. Для точности используйте данные минимум за два предполагаемых цикла.

Чтобы самому посчитать сезонность, посмотрите на статистику и найдите, через сколько периодов повторяются похожие изменения. Например, если продажи растут в декабре, а данные собраны по месяцам, сезонность — 12 месяцев (год). Если пики каждые три месяца, сезонность — 3.

Шаг 6. Обработайте отсутствующие данные

Если статистика собрана неравномерно, например, какой-то месяц пропущен, а другой задублирован, прогноз может быть неточным. Инструмент «Заполнить отсутствующие точки» поможет решить, что делать с пропусками.

По умолчанию стоит «Интерполяция» — Excel берет среднее значение соседних точек. Можно выбрать «Нули», чтобы пропуски стали нулями.

Пропуски в данных могут испортить прогноз. «Интерполяция» заполняет их, предполагая, что значения меняются плавно, например, берет среднее между продажами соседних месяцев. Это сохраняет тренд. «Нули» ставят 0, что подходит, если пропуск означает отсутствие событий, но может исказить результат, если это просто сбой в учете.

Шаг 7. Настройте обработку дубликатов

Если для одной даты есть несколько значений, в Excel можно выбрать медиану, сумму, минимум, максимум или количество.  

Оставьте среднее, если дубликаты случайные и вы хотите усредненный результат. Выберите медиану, если среди чисел есть выбросы. Например, продажа на 100 000 рублей среди обычных 5000. Используйте сумму, если нужно сложить все значения, например, транзакции.

Шаг 8. Добавьте статистические данные

Поставьте галочку напротив «Включить статистические данные прогноза». Эта опция создает таблицу с техническими числами: коэффициенты сглаживания и ошибки, такие как MAE — средняя абсолютная ошибка. Они показывают, как Excel строил прогноз, и помогают понять, насколько он точен. Например, низкая MAE говорит, что прогноз близок к реальным данным. Для обычного пользователя эти числа могут показаться сложными, но аналитикам они дают подсказки о качестве модели.

Шаг 9. Создайте прогноз

Нажмите кнопку «Создать»: Excel добавит новый лист с таблицей и диаграммой.

Google Таблица

В Google Таблице создать прогноз нужно через функцию ПРЕДСКАЗ — она в интерфейсе одна и дает качественный результат только для данных с равномерными изменениями. Если у ваших показателей есть сезонные всплески, лучше использовать Excel.

Шаг 1. Подготовьте данные

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

Шаг 2. Добавьте формулу в ячейки для прогноза

В ячейке рядом с первой будущей датой введите функцию:

=ПРЕДСКАЗ(A13, B2:B12, A2:A12)

Где:  

• A13 — ячейка с новой датой или периодом для прогноза.  
• B2:B12 — исторические значения.  
• A2:A12 — временная шкала.

Протяните ячейку с формулой вниз для остальных будущих дат.

Шаг 3. Исправьте ошибки

Формула может не сработать, если данные указаны некорректно. Вот что стоит проверить, чтобы функция дала верный результат:

1. У всех столбцов указан формат «Число».
2. Даты пронумерованы числами: 1, 2, 3, 4, 5.
3. В формуле номера ячеек прописаны латиницей. Если диапазон «B2:B12» вводите вручную, смените раскладку клавиатуры на английскую.
4. Диапазон ячеек в формуле разделен знаком «;».
5. Диапазоны даты и статистики выделены одинаково, то есть должно быть B2:B12, A2:A12, а не B2:B12, A2:A15.
6. В столбце «Даты» стоят числа для значений прогноза. Если в ячейках будет пусто, значения будут неверными.

Шаг 4. Постройте график

Выделите столбцы со значениями и перейдите в «Вставка» → «Диаграмма».

Что в итоге

Прогнозы в Excel и Google Таблицах помогают предсказать продажи или другие показатели без сложных расчетов. В Excel «Лист Прогноза» создает таблицу с предсказаниями и диаграмму, которая учитывает сезонные пики вроде всплесков заказов перед праздниками.

Функция ПРЕДСКАЗ в Google Таблицах строит линейный прогноз, который подходит для стабильных трендов, например, роста подписчиков. Оба инструмента экономят время: прогноз готов за минуты. Excel лучше для сезонности, Google Таблицы — для быстрых онлайн-расчетов.