Предсказание на основе данных в Excel

Всем привет!

Временные ряды и тренды окружают нас повсюду: продажи, посещаемость сайта, расходы, курс валюты. Вопрос «что будет дальше?» — ключевой для принятия решений. К счастью, Excel предлагает мощные, но простые в использовании инструменты для прогнозирования, не требующие глубоких знаний в статистике. Это Прогнозные листы и линейный тренд.

Давайте рассмотрим эти инструменты сегодня.

Часть 1: Прогнозный лист

Это самый современный и визуально впечатляющий инструмент прогнозирования в Excel. Он использует алгоритм экспоненциального сглаживания (ETS), который идеально подходит для данных с сезонностью.

Когда использовать?

• У вас есть исторические данные, упорядоченные по датам или времени.
• Вы подозреваете наличие сезонности (например, пики продаж зимой, спад летом).
• Вам нужен не только цифровой прогноз, но и наглядный график с доверительными интервалами.

Пошаговая инструкция: Создание прогноза

Предпосылка: Ваши данные должны быть в двух столбцах:

1. Столбец с датами/временем (например, с января 2020 по декабрь 2023).
2. Столбец с соответствующими значениями (продажи, трафик и т.д.). Даты должны идти без пропусков.

Пример данных.

Шаги:

1. Выделите любую ячейку в вашей таблице с данными.
2. Перейдите на вкладку «Данные».
3. Нажмите кнопку «Прогноз» (Forecast) и выберите «Лист Прогноза».
4. Появится диалоговое окно с настройками и красивым графиком.
   Дата завершения прогноза: Укажите, до какого момента строить прогноз.
   Доверительный интервал: По умолчанию 95%. Это диапазон, в котором, с вероятностью 95%, будут находиться будущие значения. Чем шире интервал, тем более «осторожный» и размытый прогноз.
5. Нажмите «Создать».

Что вы получите?

Excel создаст новый лист, на котором будет:

• Таблица: Ваши исходные данные и новые столбцы с прогнозируемыми значениями, нижней и верхней границами прогноза.
• Красивый график: На котором наглядно видна история, линия прогноза и доверительный интервал (затененная область).

Важные настройки (кнопка «Параметры» в окне прогноза)

• Начало прогноза: Можно начать прогноз с определенной даты, чтобы исключить ранние, нерелевантные данные.
• Учет сезонности: Excel определяет ее автоматически, но вы можете задать длину цикла вручную (например, 12 для месячных данных с годовой сезонностью).
• Учет аномалий: Включите эту опцию, чтобы алгоритм попытался сгладить резкие выбросы в данных.

Часть 2: Линейный тренд и функция ПРЕДСКАЗ

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

Способ 1: Визуальный метод с линией тренда на графике

1. Постройте обычный точечный или линейный график по вашим данным.
2. Щелкните правой кнопкой мыши по линии данных на графике и выберите «Добавить линию тренда».
3. В открывшейся панели настроек:
   Выберите тип «Линейная».
   Поставьте галочки «показать уравнение на диаграмме» и «величину достоверности аппроксимации (R^2)».

Линия тренда

Как это использовать?

• Уравнение y = a*x + b — это и есть ваша прогнозная модель.
  y — это будущее значение, которое мы хотим спрогнозировать.
  x — это порядковый номер периода (1, 2, 3...). Для прогноза на следующий период возьмите следующий по порядку номер.
  a — это наклон (на сколько в среднем изменяется y при изменении x на 1).
  b — точка пересечения с осью Y.
• R² (R-квадрат): Показывает, насколько хорошо линия соответствует данным. Чем ближе к 1, тем надежнее прогноз.

Способ 2: Формулы для расчета (ПРЕДСКАЗ, ЛИНЕЙН, ТЕНДЕНЦИЯ)

Это более мощный и автоматизируемый подход.

1. =ПРЕДСКАЗ(X; известные_значения_y; известные_значения_x)
   Устаревшая, но простая функция. Предсказывает значение y для заданного x на основе линейной регрессии.
   Пример: =ПРЕДСКАЗ(13; B2:B12; A2:A12) — спрогнозировать значение для 13-го периода на основе данных из столбцов A (номера периодов) и B (значения).
2. =ТЕНДЕНЦИЯ(известные_значения_y; [известные_значения_x]; [новые_значения_x])
   Более современная и мощная функция. Возвращает массив значений, рассчитанных по линейному тренду. Может предсказать сразу несколько значений.
   Пример: Выделите диапазон C2:C6, введите =ТЕНДЕНЦИЯ(B2:B12; A2:A12; A2:A6) и нажмите Ctrl+Shift+Enter (если у вас не динамический массив). Формула рассчитает прогноз для первых 5 периодов. В новых версиях Excel она просто «разольется».
3. =ЛИНЕЙН(известные_значения_y; [известные_значения_x])
   Функция для профессионалов. Возвращает статистические параметры линии тренда (угол наклона a и сдвиг b), как в уравнении на графике.

На сегодня все.

Спасибо за внимание!

⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇

Материалы по Эксель. Содержание данного канала:

https://dzen.ru/a/ZhpQXTxmQDShWlXf

⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆