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

Обновлено: 03.07.2024

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

Это руководство охватывает:

Объяснение таблицы прогнозов Excel

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

В этом руководстве мы объясним, как легко реализовать листы прогнозов в Excel. Идите вперед и читайте дальше, чтобы потрясти все ваши будущие презентации. Пусть это будет вашим руководством по работе с Excel.

По теме:

Если вы используете Excel 2016, 2019 или Excel для Office 365, у вас будет доступ к одной из новых функций Excel: листу прогнозов Excel. Если у вас есть исторические данные, основанные на времени, вы можете использовать их для создания прогноза и предсказания будущих значений. Прогноз поможет вам прогнозировать тенденции, будущие продажи, будущие требования и потребности, чтобы вы могли принимать более эффективные бизнес-решения.

При создании прогноза Excel создает новый рабочий лист, содержащий таблицу, включающую исторические и прогнозные значения, и диаграмму, отображающую данные. Все одним нажатием кнопки.

Видеоучебник — Таблица прогнозов Excel

Выберите оба ряда данных.
На вкладке "Данные" в группе "Прогноз" выберите "Таблица прогнозов".

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

<р>6. Нажмите Создать.

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

Те же данные отображаются в виде гистограммы.

Настроить прогноз

Чтобы настроить прогноз, разверните кнопку "Параметры" перед нажатием кнопки "Создать".

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

Параметры прогноза на листе прогнозов Excel

Дебора Эшби – аккредитованный TAP инструктор по информационным технологиям. Она специализируется на разработке, проведении и проведении курсов Microsoft как онлайн, так и в классе. У нее более 11 лет опыта обучения ИТ и 24 года в ИТ-индустрии. На сегодняшний день она обучила более 10 000 человек в Великобритании и за рубежом в таких компаниях, как HMRC, столичная полиция, парламент, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. Она имеет квалификацию MOS Master для выпусков Microsoft Office 2010, 2013 и 2016 годов, имеет аккредитацию COLF и TAP и является членом Британского учебного института.

Функция ПРОГНОЗ (или ПРОГНОЗ.ЛИНЕЙНЫЙ) в Excel прогнозирует будущее значение по линейному тренду. Функция FORECAST.ETS в Excel прогнозирует будущее значение с помощью экспоненциального тройного сглаживания, которое учитывает сезонность.

Примечание: функция ПРОГНОЗ является старой функцией. Microsoft Excel рекомендует использовать новую функцию ПРОГНОЗ.ЛИНЕЙНАЯ, которая дает точно такой же результат.

ПРОГНОЗ.ЛИНЕЙНЫЙ

<р>1. Приведенная ниже функция ПРОГНОЗ.ЛИНЕЙНЫЙ прогнозирует будущее значение в соответствии с линейным трендом.

Объяснение: когда мы перетаскиваем функцию FORECAST.LINEAR вниз, абсолютные ссылки ($B$2:$B$11 и $A$2:$A$11) остаются прежними, а относительная ссылка (A12) изменяется на A13 и А14.

<р>2. Введите значение 89 в ячейку C11, выберите диапазон A1:C14 и вставьте точечный график с прямыми линиями и маркерами.

Примечание. Когда вы добавляете линию тренда на диаграмму Excel, Excel может отображать уравнение на диаграмме. Это уравнение предсказывает одни и те же будущие значения.

ПРОГНОЗ.ETS

Функция ПРОГНОЗ.ETS в Excel 2016 или более поздних версиях — отличная функция, которая может обнаруживать сезонные закономерности.

<р>1. Приведенная ниже функция FORECAST.ETS прогнозирует будущую стоимость с помощью экспоненциального тройного сглаживания.

Примечание: последние 3 аргумента являются необязательными. Четвертый аргумент указывает длину сезонного паттерна. Значение по умолчанию 1 означает, что сезонность определяется автоматически.

<р>2.Введите значение 49 в ячейку C13, выберите диапазон A1:C17 и вставьте точечный график с прямыми линиями и маркерами.

<р>3. Вы можете использовать функцию FORECAST.ETS.SEASONALITY, чтобы найти продолжительность сезонной модели. Увидев диаграмму, вы, вероятно, уже знаете ответ.

Вывод: в этом примере при использовании функции ПРОГНОЗ.ETS можно также использовать значение 4 в качестве четвертого аргумента.

Прогноз

Используйте инструмент "Лист прогнозов" в Excel 2016 или более поздней версии, чтобы автоматически создать визуальный лист прогнозов.

<р>1. Выберите диапазон A1:B13, показанный выше.

<р>2. На вкладке "Данные" в группе "Прогноз" нажмите "Таблица прогнозов".

Excel открывает диалоговое окно, показанное ниже.

<р>3. Укажите, когда заканчивается прогноз, установите доверительный интервал (по умолчанию 95 %), автоматически определите сезонность или вручную задайте продолжительность сезонного паттерна и т. д.

Этот инструмент использует функцию ПРОГНОЗ.ETS и рассчитывает те же будущие значения. Нижняя и верхняя доверительные границы являются приятным бонусом.

Объяснение: в период 13 мы можем быть уверены на 95%, что количество посетителей будет между 86 и 94.

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

Существует несколько методов прогнозирования в Excel, и каждый из них основан на различных методах. Очевидно, что никто не даст вам окончательных ответов без способности видеть будущее. Эти результаты лучше всего использовать для обоснованных предположений. В нашей статье мы сосредоточимся на 3 часто используемых количественных методах, которые можно легко использовать в Excel.

Скользящие средние
Экспоненциальное сглаживание (ETS)
Линейная регрессия

Вы можете скачать наш образец рабочей тетради ниже.

Скользящие средние

Скользящие средние – это метод, используемый для сглаживания тенденций в данных (т. е. во временных рядах). Идея состоит в том, чтобы отфильтровать микроотклонения в диапазоне выборки, чтобы увидеть долгосрочную тенденцию, которая может повлиять на будущие результаты.

Простейшая форма скользящего среднего рассчитывается путем получения среднего арифметического заданного набора значений. Например, предположим, что вы хотите сгладить ежедневные изменения продаж за неделю. Чтобы рассчитать недельное скользящее среднее, мы должны сначала найти среднее значение за 7 дней, начиная с первого дня. Затем рассчитайте среднее значение за 7 дней со 2-го по 8-й день и используйте эти данные. Для этого можно использовать функцию СРЗНАЧ с относительными ссылками.

Формула

=AVERAGE(B5:B11) в нашем примере вычисляет среднее значение между 4-м и 10-м днями.

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

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

Нажмите значок плюса и наведите указатель мыши на элемент Trendline. Нажмите стрелку вправо и выберите элемент Дополнительные параметры… в раскрывающемся меню. Панель ОПЦИИ ТРЕНДЛИНИИ появится в правой части окна Excel.

Выберите Скользящее среднее и установите Период на основе ваших данных. Вы увидите ту же линию скользящей средней на графике.

Экспоненциальное сглаживание (ETS)

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

Выберите данные, которые содержат временные ряды и значения.
Выберите Данные > Прогноз > Таблица прогнозов.
Выберите тип диаграммы (мы рекомендуем использовать линейную или столбчатую диаграмму).
Выберите дату окончания прогнозирования.
Нажмите кнопку "Создать".

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

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

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

Настройка

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

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

Линейная регрессия

Прогнозирование в Excel можно делать с помощью различных формул. Одной из наиболее часто используемых формул является FORECAST.LINEAR для Excel 2016 и FORECAST для более ранних версий. Хотя Excel по-прежнему поддерживает функцию ПРОГНОЗ, если у вас версия 2016 или более поздняя, мы рекомендуем обновить ваши формулы, чтобы предотвратить любые проблемы в случае устаревания функции. Если у вас нет Excel 2016 или новее, вам следует использовать функцию ПРОГНОЗ. Далее в этой статье мы будем называть эту функцию ПРОГНОЗ.

В отличие от алгоритма ETS, функция ПРОГНОЗ прогнозирует будущие значения с помощью линейной регрессии. Линейная регрессия определяет линейную связь между временными рядами и рядами значений. Этот линейный подход делает его непригодным для данных с сезонностью или другими циклами, а также нелинейными. С другой стороны, линейная регрессия полезна для причинно-следственных моделей из-за ее простоты.

Поскольку в Excel нет мастера для традиционной функции ПРОГНОЗ, вам придется выполнить некоторые необходимые действия вручную.

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

Примечание. Начиная с Excel 2016, функция ПРОГНОЗ была заменена функцией ПРОГНОЗ.ЛИНЕЙНЫЙ. Корпорация Майкрософт рекомендует заменить FORECAST на FORECAST.LINEAR, так как FORECAST в конечном итоге станет устаревшим.

x — точка данных значения x, используемая для расчета прогноза.
known_ys – зависимый массив или диапазон данных (значения y).
kown_xs – независимый массив или диапазон данных (значения x).

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

В статистике линейная регрессия – это подход к моделированию взаимосвязи между зависимой переменной (значения y) и независимой переменной (значения x). FORECAST использует этот подход для вычисления значения y для заданного значения x на основе существующих значений x и y. Другими словами, для заданного значения x функция ПРОГНОЗ возвращает прогнозируемое значение на основе отношения линейной регрессии между значениями x и значениями y.

Пример

В приведенном выше примере формула в ячейке D13 выглядит следующим образом:

где продажи (C5:C12) и периоды (B5:B12) являются именованными диапазонами. С этими входными данными функция ПРОГНОЗ возвращает 1505,36 в ячейке D13. Поскольку формула копируется вниз по таблице, ПРОГНОЗ возвращает предсказанные значения в D13:D16, используя значения в столбце B для x.

Читайте также: