Создание прогноза в Excel для Windows

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

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

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

Часть электронной таблицы, содержащая таблицу прогнозируемых чисел и диаграмму прогноза

Создание прогноза

  1. На листе введите два ряда данных, которые соответствуют друг другу:

    • ряд значений даты или времени для временной шкалы;

    • ряд соответствующих значений показателя.

      Эти значения будут предсказаны для дат в будущем.

    Примечание: Для временной шкалы требуются одинаковые интервалы между точками данных. Например, это могут быть месячные интервалы со значениями на первое число каждого месяца, годичные или числовые интервалы. Если на временной шкале не хватает до 30 % точек данных или есть несколько чисел с одной и той же меткой времени, это нормально. Прогноз все равно будет точным. Но для повышения точности прогноза желательно перед его созданием обобщить данные.

  2. Выделите оба ряда данных.

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

  3. На вкладке Данные в группе Прогноз нажмите кнопку Лист прогноза.

    Кнопка "Лист прогнозов" на вкладке "Данные"

  4. В диалоговом окне Создание листа прогноза выберите график или гистограмму для визуального представления прогноза.

    Снимок диалогового окна "Создание листа прогноза" со свернутыми параметрами

  5. В поле Завершение прогноза выберите дату окончания, а затем нажмите кнопку Создать.

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

    Этот лист будет находиться слева от листа, на котором вы ввели ряды данных (то есть перед ним).

Настройка прогноза

Если вы хотите изменить дополнительные параметры прогноза, нажмите кнопку Параметры.

Вы найдете сведения о каждом из параметров в приведенной ниже таблице.

Параметры прогноза

Описание

Начало прогноза

Выбор даты для прогноза для начала. При выборе даты до конца статистических данных используются только данные от даты начала предсказанного (это иногда называется «ретроспективный анализ»).

Советы: 

  • Запуск прогноза до последней точке статистических дает представление точности прогноза как можно сравнивать прогнозируемое ряд фактические данные. Тем не менее при запуске прогноз слишком рано, созданный прогноз не обязательно прогноз, что вам будет использовать статистических данных. Использование всех статистических данных дает более точные прогноза.

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

Доверительный интервал

Установите или снимите флажок доверительный интервал, чтобы показать или скрыть ее. Доверительный интервал — диапазон вокруг каждого предполагаемые значения, в котором 95% точек будущих ожидается, находится в интервале, на основе прогноза (с нормальным распределением). Доверительный интервал помогут вам понять, точности прогноза. Меньший интервал подразумевает более уверенно предсказанного для определенный момент. Уровня достоверности 95% по умолчанию могут быть изменены с помощью вверх или вниз.

Сезонность

Сезонности — это число в течение (количество точек) сезонного узора и определяется автоматически. Например годового цикла продаж, с каждой точки, представляющий месяц, сезонности равно 12. Автоматическое обнаружение можно переопределить, выбрав Задание вручную и затем выбрав числа.

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

Диапазон временной шкалы

Здесь можно изменить диапазон, используемый для временной шкалы. Этот диапазон должен соответствовать параметру Диапазон значений.

Диапазон значений

Здесь можно изменить диапазон, используемый для рядов значений. Этот диапазон должен совпадать со значением параметра Диапазон временной шкалы.

Заполнить отсутствующие точки с помощью

Для обработки отсутствующих точек Excel использует интерполяцию. Это означает, что отсутствующая точка вычисляется как взвешенное среднее соседних точек, если отсутствует менее 30 % точек. Чтобы вместо этого заполнять отсутствующие точки нулями, выберите в списке пункт Нули.

Объединить дубликаты с помощью

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

Включить статистические данные прогноза

Установите этот флажок, если вы хотите дополнительные статистические сведения о включенных на новый лист прогноза. В результате добавит таблицу статистики, созданной с помощью ПРОГНОЗА. ETS. СТАТИСТИКА функциями, а также меры, например сглаживания коэффициенты (альфа, бета-версии, гамма) и метрик ошибки (MASE, SMAPE, обеспечения, RMSE).

Формулы, используемые при прогнозировании

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

Таблицы могут содержать следующие столбцы, три из которых являются вычисляемыми:

  • столбец статистических значений времени (ваш ряд данных, содержащий значения времени);

  • столбец статистических значений (ряд данных, содержащий соответствующие значения);

  • столбец прогнозируемых значений (вычисленных с помощью функции ПРЕДСКАЗ.ЕTS);

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

Скачайте пример книги.

Щелкните эту ссылку, чтобы загрузить книгу с помощью Excel ПРОГНОЗА. Примеры использования функции ETS

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также:

Функции прогнозирования

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×