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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сведения о каждом параметре приведены в таблице ниже.

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

Описание

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

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

Советы : 

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

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

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

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

Сезонность

Это число, обозначающее длину (количество точек) шаблона сезонных колебаний. Оно определяется автоматически. Например, для ежегодного цикла продаж, где каждая точка соответствует месяцу, показатель сезонности равен 12. Автоматическое обнаружение можно переопределить. Для этого выберите параметр Установка вручную и задайте число.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

См. также

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

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

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

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

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

×