Лист прогноза в excel как включить
Перейти к содержимому

Лист прогноза в excel как включить

  • автор:

ПРОГНОЗ и ПРОГНОЗ. Функции LINEAR

В этой статье описывается синтаксис формулы и использование прогноза. Функции LINEAR и FORECAST в Microsoft Excel.

Примечание: В Excel 2016 функция FORECAST была заменена на FORECAST. LINEAR в составе новых функций прогнозирования. Синтаксис и использование этих двух функций одинаковы, но более старая функция FORECAST в конечном итоге будет нерекомендуемой. Он по-прежнему доступен для обратной совместимости, но рассмотрите возможность использования нового прогноза. Вместо этого функция LINEAR.

Описание

Вычислите или спрогнозируйте будущее значение с помощью существующих значений. Будущее значение является значением y для заданного X-значения. Существующие значения являются известными значениями x и y, а будущее значение прогнозируется с помощью линейной регрессии. Эти функции можно использовать для прогнозирования будущих продаж, требований к запасам или потребительских тенденций.

Синтаксис

Прогноз или прогноз. Аргументы функции LINEAR приведены ниже.

Обязательный

Ссылается на

Точка данных, для которой предсказывается значение.

Известные_значения_y.

Зависимый массив или интервал данных.

Известные_значения_x.

Независимый массив или интервал данных.

Замечания

  • Если значение x не является числом, то функция FORECAST и FORECAST. LINEAR возвращает #VALUE! (значение ошибки).
  • Если known_y или known_x пуста или одна из них имеет больше точек данных, чем другая, прогноз и прогноз. LINEAR возвращает значение ошибки #N/A.
  • Если отклонение known_x равно нулю, то прогноз и прогноз. LINEAR возвращает #DIV/0! (значение ошибки).
  • Уравнение для прогноза и прогноза. LINEAR — это a+bx, где:

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.

Известные значения y

Известные значения x

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

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

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

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

  1. На листе введите два ряда данных, которые соответствуют друг другу:
    • ряд значений даты или времени для временной шкалы;
    • ряд соответствующих значений показателя. Эти значения будут предсказаны для дат в будущем.

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

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

Кнопка

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

Снимок диалогового окна

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Использование агрегатных дубликатов

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

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

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

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

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

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

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

Скачивание образца книги

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Лист прогноза в excel как включить

Прогнозирование данных можно осуществлять в любой версии программы Excel. Тем не менее в 2016 версии появился наглядный и быстрый способ делать прогноз с использованием “листа прогноза”.

Для этого необходимо:

  1. Выделить таблицу с исходными данными (даты/периоды и значения).
  2. На вкладке Данные, в группе Прогноз выбрать Лист прогноза.
  3. В окне Создание листа прогноза задать требуемые параметры прогноза, такие как доверительный интервал, начало/завершение прогноза, учет сезонности и т.д.
  4. Нажать кнопку Создать.

LP2

В результате на новом листе будет получена таблица с прогнозируемыми значениями и график: LP2LP3

Оставьте комментарий!

На сообщение “Лист прогноза – быстрый прогноз в Excel 2016” комментариев 11

  1. Анжела :
    05.04.2016 (09:03) Действительно быстрый прогноз, для быстрой оценки самое то. Я даже не заметила эту новинку
  2. Adel :
    08.04.2016 (08:15) Классная возможность. Жалко только на работе еще не скоро поставят 16й.
  3. Сергей :
    09.04.2016 (23:57) Интересное дополнение. Ещё бы на графике сразу разными цветами прогноз показывал – было бы наглядней.
  4. Алексей :
    20.04.2016 (10:23) А что будет с результатом, если его открыть в предыдущих версиях программы?
  5. Ольга Кулешова :
    24.04.2016 (13:42) Сперва будет доступен и показан. Однако, если вы начнете что-либо править, то в формулах появятся ошибки и, как следствие, график будет перестраиваться…
  6. Kirill :
    10.09.2016 (00:51) Как будет это всё выглядеть в 2010 версии
  7. Marina :
    04.09.2017 (15:01) Очень нравится: быстро, просто, наглядно. Жаль в прежних версиях нет
  8. Рита :
    13.09.2017 (02:24) Очень хорошее дополнение в программе! Я никак не нарадуюсь!
  9. Nikolay :
    22.10.2017 (16:59) Очень удобно. Я строю порой с разными параметрами, отчет располагаются на разных листах, а потом я их разом совмещаю: и наглядно и просто и быстро.
  10. Georgekit :
    21.08.2018 (12:09) variant2
  11. Анатолий :
    24.12.2018 (12:14) Действительно быстро. Даже и не ожидал, что настолько быстро!

Быстрое прогнозирование в Microsoft Excel

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

В качестве исходных исторических данных возьмем с сайта AutoVercity реальную статистику по продажам автомобилей в России за 2019-2020 годы (все марки суммарно):

Исходные данные для прогноза

Представим на минуту, что сейчас конец 2020 года и мы хотим, используя эти данные, сделать помесячный прогноз продаж автомобилей на следующие полтора года. Выделим всю нашу таблицу и на вкладке Данные воспользуемся кнопкой Лист прогноза (Data — Forecast Sheet) .

Лист прогноза

В открывшемся окне зададим следующие настройки:

  1. Дату завершения прогноза
  2. Сезонность — почти никогда корректно не определяется автоматически, к сожалению, так что лучше задать её вручную. В большинстве бизнесов она годовая (т.е. «узор» колебаний похожим образом повторяется из года в год), так что установим её равной 12 месяцам.
  3. Вероятность, с которой мы требуем попадания будущих фактических значений в коридор доверительного интервала. Чем больше эта вероятность, тем шире интервал (т.е. более размыт прогноз). Обычно используют значения 90-95%.
  4. В правом нижнем углу окна можно дополнительно выбрать реакцию на пустые ячейки (их можно заполнить нулями или средним соседних значений — интерполяцией) и на дубликаты (обычно их усредняют). Однако же, по возможности, лучше заранее подготовить исходные исторические данные, чтобы таких пробелов или дублей в них не было.

После нажатия на кнопку Создать будет сформирован новый лист с прогнозной таблицей и диаграммой, которая по ней построена:

Готовый прогноз

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

Ссылки по теме

  • Моделирование и оценка вероятности выигрыша в лотерею
  • Оптимизация доставки в Excel с помощью Поиска решения (Solver)
  • Быстрое добавление новых данных в диаграмму

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *