Как составить прогноз на год excel

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

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

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

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

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

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

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

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

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

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

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

   

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

   

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

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

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

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

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

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

Параметры прогноза	Описание
Начало прогноза	Выберите дату, с которой должен начинаться прогноз. При выборе даты начала, которая наступает раньше, чем заканчиваются статистические данные, для построения прогноза используются только данные, предшествующие ей (это называется “ретроспективным прогнозированием”). Советы:  Если вы начинаете прогноз перед последней точкой, вы сможете получить оценку точности прогноза, так как сможете сравнить прогнозируемый ряд с фактическими данными. Но если начать прогнозирование со слишком ранней даты, построенный прогноз может отличаться от созданного на основе всех статистических данных. При использовании всех статистических данных прогноз будет более точным. Если в ваших данных прослеживаются сезонные тенденции, то рекомендуется начинать прогнозирование с даты, предшествующей последней точке статистических данных.
Доверительный интервал	Установите или снимите флажок Доверительный интервал, чтобы показать или скрыть его. Доверительный интервал — это диапазон вокруг каждого предсказанного значения, в который в соответствии с прогнозом (при нормальном распределении) предположительно должны попасть 95 % точек, относящихся к будущему. Доверительный интервал помогает определить точность прогноза. Чем он меньше, тем выше достоверность прогноза для данной точки. Доверительный интервал по умолчанию определяется для 95 % точек, но это значение можно изменить с помощью стрелок вверх или вниз.
Сезонность	Сезонность — это число для длины (количества точек) сезонного шаблона и автоматически обнаруживается. Например, в ежегодном цикле продаж, каждый из которых представляет месяц, сезонность составляет 12. Автоматическое обнаружение можно переопрепредидить, выбрав установить вручную и выбрав число. Примечание: Если вы хотите задать сезонность вручную, не используйте значения, которые меньше двух циклов статистических данных. При таких значениях этого параметра приложению Excel не удастся определить сезонные компоненты. Если же сезонные колебания недостаточно велики и алгоритму не удается их выявить, прогноз примет вид линейного тренда.
Диапазон временной шкалы	Здесь можно изменить диапазон, используемый для временной шкалы. Этот диапазон должен соответствовать параметру Диапазон значений.
Диапазон значений	Здесь можно изменить диапазон, используемый для рядов значений. Этот диапазон должен совпадать со значением параметра Диапазон временной шкалы.
Заполнить отсутствующие точки с помощью	Для обработки отсутствующих точек в Excel используется интерполяция, то есть отсутствующие точки будут заполнены в качестве взвешенного среднего значения соседних точек, если отсутствует менее 30 % точек. Чтобы нули в списке не были пропущены, выберите в списке пункт Нули.
Использование агрегатных дубликатов	Если данные содержат несколько значений с одной меткой времени, Excel находит их среднее. Чтобы использовать другой метод вычисления, например Медиана илиКоличество,выберите нужный способ вычисления из списка.
Включить статистические данные прогноза	Установите этот флажок, если хотите поместить на новом листе дополнительную статистическую информацию о прогнозе. При этом добавляется таблица статистики, созданная с помощью прогноза. Ets. Функция СТАТ и показатели, такие как коэффициенты сглаживания (“Альфа”, “Бета”, “Гамма”) и метрики ошибок (MASE, SMAPE, MAE, RMSE).

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

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

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

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

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

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

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

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

Щелкните эту ссылку, чтобы скачать книгу с Excel FORECAST. Примеры функции ETS

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Статьи по теме

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

Прогнозирование – хоть и неблагодарное, но необходимое дело и для решения таких задач в Microsoft Excel есть весьма приличный инструментарий – от простейших функций линейного тренда до навороченных статистических инструментов из надстройки Пакет Анализа (Analysis Toolpak). Одними из самых простых в реализации и при этом весьма эффективных являются функции прогнозирования по методу экспоненциального сглаживания.

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

В Microsoft Excel для её реализации есть две основные функции, появившиеся начиная с 2016-й версии Excel:

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

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

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

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

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

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

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

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

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

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

Инструменты прогнозирования в Microsoft Excel

​Смотрите также​ примера.​ известные_значения_x, не должна​ прогнозов были более​ скачать данный пример:​Рассчитаем прогноз по продажам​Диапазон временной шкалы​Лист прогноза​ имеющихся данных. Функции​ или стабилизацию) продемонстрирует​(вкладка​ серии научных экспериментов,​ линейного приближения, в​ на монитор в​ того, у​ прогноз прибыли на​.​Прогнозирование – это очень​На график, отображающий фактические​ равняться 0 (нулю),​ точными.​Функция ПРЕДСКАЗ в Excel​

​ с учетом роста​Здесь можно изменить диапазон,​

Процедура прогнозирования

​.​ ЛИНЕЙН и ЛГРФПРИБЛ​ предполагаемую тенденцию на​Главная​ можно использовать Microsoft​ 2019 году составит​ указанной ранее ячейке.​

Способ 1: линия тренда

​ТЕНДЕНЦИЯ​ 2018 год.​Линия тренда построена и​ важный элемент практически​ объемы реализации продукции,​

​ иначе функция ПРЕДСКАЗ​Рассчитаем значения логарифмического тренда​ позволяет с некоторой​ и сезонности. Проанализируем​ используемый для временной​В диалоговом окне​

1. ​ возвращают различные данные​ ближайшие месяцы.​, группа​ Office Excel для​ 4614,9 тыс. рублей.​ Как видим, на​имеется дополнительный аргумент​Выделяем незаполненную ячейку на​​ по ней мы​​ любой сферы деятельности,​ добавим линию тренда​ вернет код ошибки​ с помощью функции​​ степенью точности предсказать​​ продажи за 12​ шкалы. Этот диапазон​Создание листа прогноза​ регрессионного анализа, включая​Эта процедура предполагает, что​Редактирование​ автоматической генерации будущих​Последний инструмент, который мы​ этот раз результат​«Константа»​ листе, куда планируется​ можем определить примерную​ начиная от экономики​

   

2. ​ (правая кнопка по​ #ДЕЛ/0!.​ ПРЕДСКАЗ следующим способом:​ будущие значения на​ месяцев предыдущего года​ должен соответствовать параметру​выберите график или​ наклон и точку​​ диаграмма, основанная на​​, кнопка​

   

3. ​ значений, которые будут​ рассмотрим, будет​ составляет 4682,1 тыс.​, но он не​ выводить результат обработки.​
   • ​ величину прибыли через​​ и заканчивая инженерией.​
   • ​ графику – «Добавить​​Рассматриваемая функция игнорирует ячейки​
   • ​Как видно, в качестве​​ основе существующих числовых​
   • ​ и построим прогноз​​Диапазон значений​
   • ​ гистограмму для визуального​​ пересечения линии с​
   • ​ существующих данных, уже​​Заполнить​

   ​ базироваться на существующих​ЛГРФПРИБЛ​

   ​ рублей. Отличия от​​ является обязательным и​​ Жмем на кнопку​​ три года. Как​​ Существует большое количество​​ линию тренда»).​​ с нечисловыми данными,​ первого аргумента представлен​ значений, и возвращает​ на 3 месяца​.​ представления прогноза.​​ осью.​​ создана. Если это​​).​ данных или для​​. Этот оператор производит​ результатов обработки данных​ используется только при​«Вставить функцию»​ видим, к тому​​ программного обеспечения, специализирующегося​​Настраиваем параметры линии тренда:​

   

4. ​ содержащиеся в диапазонах,​ массив натуральных логарифмов​ соответствующие величины. Например,​ следующего года с​Диапазон значений​В поле​Следующая таблица содержит ссылки​ еще не сделано,​С помощью команды​​ автоматического вычисления экстраполированных​​ расчеты на основе​ оператором​ наличии постоянных факторов.​.​ времени она должна​​ именно на этом​​Выбираем полиномиальный тренд, что​​ которые переданы в​​ последующих номеров дней.​ некоторый объект характеризуется​ помощью линейного тренда.​Здесь можно изменить диапазон,​Завершение прогноза​​ на дополнительные сведения​​ просмотрите раздел Создание​Прогрессия​​ значений, базирующихся на​​ метода экспоненциального приближения.​ТЕНДЕНЦИЯ​

   

5. ​Данный оператор наиболее эффективно​Открывается​ перевалить за 4500​ направлении. К сожалению,​ максимально сократить ошибку​ качестве второго и​ Таким образом получаем​ свойством, значение которого​ Каждый месяц это​ используемый для рядов​

   

   ​выберите дату окончания,​ об этих функциях.​ диаграмм.​можно вручную управлять​ вычислениях по линейной​ Его синтаксис имеет​незначительны, но они​ используется при наличии​Мастер функций​ тыс. рублей. Коэффициент​ далеко не все​ прогнозной модели.​ третьего аргументов.​ функцию логарифмического тренда,​ изменяется с течением​ для нашего прогноза​ значений. Этот диапазон​ а затем нажмите​Функция​Щелкните диаграмму.​ созданием линейной или​ или экспоненциальной зависимости.​ следующую структуру:​

​ имеются. Это связано​​ линейной зависимости функции.​. В категории​

Способ 2: оператор ПРЕДСКАЗ

​R2​ пользователи знают, что​R2 = 0,9567, что​​Функция ПРЕДСКАЗ была заменена​​ которая записывается как​ времени. Такие изменения​ 1 период (y).​ должен совпадать со​

​ кнопку​

​Описание​​Выберите ряд данных, к​ экспоненциальной зависимости, а​В Microsoft Excel можно​= ЛГРФПРИБЛ (Известные значения_y;известные​ с тем, что​Посмотрим, как этот инструмент​«Статистические»​, как уже было​

​ обычный табличный процессор​​ означает: данное отношение​ функцией ПРЕДСКАЗ.ЛИНЕЙН в​ y=aln(x)+b.​ могут быть зафиксированы​Уравнение линейного тренда:​ значением параметра​

​Создать​​ПРЕДСКАЗ​ которому нужно добавить​ также вводить значения​ заполнить ячейки рядом​ значения_x; новые_значения_x;[конст];[статистика])​ данные инструменты применяют​ будет работать все​выделяем наименование​ сказано выше, отображает​

​ Excel имеет в​ объясняет 95,67% изменений​ Excel версии 2016,​Результат расчетов:​ опытным путем, в​y = bx​Диапазон временной шкалы​.​Прогнозирование значений​

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

​Как видим, все аргументы​ разные методы расчета:​​ с тем же​​«ПРЕДСКАЗ»​ качество линии тренда.​ своем арсенале инструменты​ объемов продаж с​ но была оставлена​Для сравнения, произведем расчет​

1. ​ результате чего будет​ + a​.​В Excel будет создан​​тенденция​​ скользящее среднее.​

   

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

   

3. ​ с использованием функции​ составлена таблица известных​​y — объемы продаж;​​Заполнить отсутствующие точки с​ новый лист с​Прогнозирование линейной зависимости.​На вкладке​ к начальным значениям​ тренду, с помощью​​ элементы предыдущей функции.​​ и метод экспоненциальной​ сравнить полученные результаты,​ по кнопке​ величина​​ которые по своей​​Уравнение тренда – это​ с Excel 2013​ линейного тренда:​ значений x и​x — номер периода;​ помощью​

   ​ таблицей, содержащей статистические​​РОСТ​​Макет​​ применяется метод наименьших​​ маркер заполнения или​ Алгоритм расчета прогноза​ зависимости.​ точкой прогнозирования определим​«OK»​R2​ эффективности мало чем​

   ​ модель формулы для​​ и более старыми​​И для визуального сравнительного​​ соответствующих им значений​​a — точка пересечения​Для обработки отсутствующих точек​

   ​ и предсказанные значения,​Прогнозирование экспоненциальной зависимости.​в группе​​ квадратов (y=mx+b).​​ команды​

   

4. ​ немного изменится. Функция​Оператор​ 2019 год.​.​составляет​ уступают профессиональным программам.​ расчета прогнозных значений.​ версиями.​ анализа построим простой​ y, где x​ с осью y​ Excel использует интерполяцию.​ и диаграммой, на​

   

5. ​линейн​Анализ​Для получения экспоненциального тренда​Прогрессия​ рассчитает экспоненциальный тренд,​ЛИНЕЙН​Производим обозначение ячейки для​Запускается окно аргументов. В​0,89​ Давайте выясним, что​

​Большинство авторов для прогнозирования​Для предсказания только одного​ график.​ – единица измерения​ на графике (минимальный​ Это означает, что​ которой они отражены.​Построение линейного приближения.​нажмите кнопку​

​ к начальным значениям​​. Для экстраполяции сложных​

Способ 3: оператор ТЕНДЕНЦИЯ

​ который покажет, во​при вычислении использует​ вывода результата и​​ поле​​. Чем выше коэффициент,​ это за инструменты,​ продаж советуют использовать​ будущего значения на​Полученные результаты:​​ времени, а y​​ порог);​ отсутствующая точка вычисляется​

​Этот лист будет находиться​

​лгрфприбл​​Линия тренда​​ применяется алгоритм расчета​​ и нелинейных данных​​ сколько раз поменяется​ метод линейного приближения.​​ запускаем​​«X»​​ тем выше достоверность​​ и как сделать​​ линейную линию тренда.​​ основании известного значения​Как видно, функцию линейной​​ – количественная характеристика​​b — увеличение последующих​​ как взвешенное среднее​​ слева от листа,​Построение экспоненциального приближения.​и выберите нужный​ экспоненциальной кривой (y=b*m^x).​

​ можно применять функции​ сумма выручки за​ Его не стоит​

​Мастер функций​указываем величину аргумента,​ линии. Максимальная величина​ прогноз на практике.​ Чтобы на графике​ независимой переменной функция​ регрессии следует использовать​

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

   

2. ​Скачать последнюю версию​​ увидеть прогноз, в​​ ПРЕДСКАЗ используется как​​ в тех случаях,​​ функции ПРЕДСКАЗ можно​Допустим у нас имеются​ отсутствует менее 30 %​​ ввели ряды данных​​ сложный регрессионный анализ —​​ тренда или скользящего​​ учитывается шаг прогрессии.​​ анализ из надстройки​​ есть, за год.​​ линейной зависимости, используемым​​ категории​ отыскать значение функции.​ равной​ Excel​ параметрах необходимо установить​ обычная формула. Если​ когда наблюдается постоянный​​ предположить последующие значения​​ следующие статистические данные​ точек. Чтобы вместо​​ (то есть перед​​ включая вычисление и​

   

3. ​ среднего.​ При создании этих​ “Пакет анализа”.​ Нам нужно будет​ инструментом​«Статистические»​ В нашем случаем​1​Целью любого прогнозирования является​ количество периодов.​

Способ 4: оператор РОСТ

​ требуется предсказать сразу​ рост какой-либо величины.​ y для новых​ по продажам за​ этого заполнять отсутствующие​ ним).​ отображение остатков — можно​Для определения параметров и​ прогрессий получаются те​В арифметической прогрессии шаг​ найти разницу в​ТЕНДЕНЦИЯ​находим и выделяем​ это 2018 год.​

​. Принято считать, что​

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

1. ​ прибыли между последним​. Его синтаксис имеет​ наименование​​ Поэтому вносим запись​​ при коэффициенте свыше​ и определение предполагаемого​​В нашем примере все-таки​​ качестве первого аргумента​ функция логарифмического тренда​​Функция ПРЕДСКАЗ использует метод​​Рассчитаем значение линейного тренда.​

   

2. ​ в списке пункт​ дополнительные параметры прогноза,​ анализа в надстройке​ тренда или скользящего​ вычисляются с помощью​ начальным и следующим​ фактическим периодом и​ такой вид:​​«ТЕНДЕНЦИЯ»​​«2018»​0,85​ результата в отношении​​ экспоненциальная зависимость. Поэтому​​ следует передать массив​

   

3. ​ позволяет получить более​ линейной регрессии, а​ Определим коэффициенты уравнения​Нули​ нажмите кнопку​ “Пакет анализа”. Дополнительные​ среднего щелкните линию​ функций ТЕНДЕНЦИЯ и​ значением в ряде​​ первым плановым, умножить​​=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])​. Жмем на кнопку​. Но лучше указать​линия тренда является​ изучаемого объекта на​ при построении линейного​ или ссылку на​ правдоподобные данные (более​

Способ 5: оператор ЛИНЕЙН

​ ее уравнение имеет​​ y = bx​​.​Параметры​ сведения см. в​ тренда правой клавишей​ РОСТ.​ добавляется к каждому​​ её на число​​Последние два аргумента являются​«OK»​

​ этот показатель в​

​ достоверной.​ определенный момент времени​ тренда больше ошибок​ диапазон ячеек со​ наглядно при большем​ вид y=ax+b, где:​ + a. В​Объединить дубликаты с помощью​.​ статье Загрузка пакета​ мыши и выберите​Для заполнения значений вручную​ следующему члену прогрессии.​ плановых периодов​ необязательными. С первыми​.​ ячейке на листе,​Если же вас не​ в будущем.​ и неточностей.​ значениями независимой переменной,​ количестве данных).​​Коэффициент a рассчитывается как​​ ячейке D15 Используем​Если данные содержат несколько​

1. ​Вы найдете сведения о​ статистического анализа.​ пункт​ выполните следующие действия.​Начальное значение​​(3)​​ же двумя мы​​Открывается окно аргументов оператора​​ а в поле​ устраивает уровень достоверности,​​Одним из самых популярных​​Для прогнозирования экспоненциальной зависимости​

   

2. ​ а функцию ПРЕДСКАЗ​​Пример 3. В таблице​​ Yср.-bXср. (Yср. и​ функцию ЛИНЕЙН:​​ значений с одной​​ каждом из параметров​​Примечание:​​Формат линии тренда​​Выделите ячейку, в которой​​Продолжение ряда (арифметическая прогрессия)​и прибавить к​ знакомы по предыдущим​​ТЕНДЕНЦИЯ​​«X»​

   

3. ​ то можно вернуться​ видов графического прогнозирования​ в Excel можно​

   

4. ​ использовать в качестве​ Excel указаны значения​ Xср. – среднее​Выделяем ячейку с формулой​​ меткой времени, Excel​​ в приведенной ниже​ Мы стараемся как можно​.​ находится первое значение​1, 2​ результату сумму последнего​ способам. Но вы,​. В поле​​просто дать ссылку​​ в окно формата​ в Экселе является​ использовать также функцию​ формулы массива.​ независимой и зависимой​​ арифметическое чисел из​​ D15 и соседнюю,​ находит их среднее.​ таблице.​ оперативнее обеспечивать вас​Выберите параметры линии тренда,​ создаваемой прогрессии.​3, 4, 5…​ фактического периода.​ наверное, заметили, что​«Известные значения y»​​ на него. Это​​ линии тренда и​ экстраполяция выполненная построением​​ РОСТ.​​Анализ временных рядов позволяет​

​ переменных. Некоторые значения​ выборок известных значений​ правую, ячейку E15​ Чтобы использовать другой​Параметры прогноза​

Способ 6: оператор ЛГРФПРИБЛ

​ актуальными справочными материалами​ тип линий и​​Команда​​1, 3​В списке операторов Мастера​ в этой функции​уже описанным выше​ позволит в будущем​

​ выбрать любой другой​ линии тренда.​

​Для линейной зависимости –​ изучить показатели во​ зависимой переменной указаны​ y и x​ так чтобы активной​ метод вычисления, например​Описание​ на вашем языке.​ эффекты.​Прогрессия​5, 7, 9​ функций выделяем наименование​ отсутствует аргумент, указывающий​ способом заносим координаты​ автоматизировать вычисления и​ тип аппроксимации. Можно​Попробуем предсказать сумму прибыли​ ТЕНДЕНЦИЯ.​​ времени. Временной ряд​​ в виде отрицательных​ соответственно).​ оставалась D15. Нажимаем​

1. ​Медиана​Начало прогноза​​ Эта страница переведена​​При выборе типа​удаляет из ячеек​​100, 95​​«ЛГРФПРИБЛ»​

   

2. ​ на новые значения.​ колонки​ при надобности легко​ перепробовать все доступные​ предприятия через 3​​При составлении прогнозов нельзя​​ – это числовые​​ чисел. Спрогнозировать несколько​​Коэффициент b определяется по​

   

3. ​ кнопку F2. Затем​, выберите его в​Выбор даты для прогноза​

   

4. ​ автоматически, поэтому ее​​Полиномиальная​​ прежние данные, заменяя​90, 85​. Делаем щелчок по​ Дело в том,​«Прибыль предприятия»​ изменять год.​​ варианты, чтобы найти​​ года на основе​ использовать какой-то один​ значения статистического показателя,​ последующих значений зависимой​ формуле:​ Ctrl + Shift​ списке.​ для начала. При​ текст может содержать​​введите в поле​​ их новыми. Если​Для прогнозирования линейной зависимости​ кнопке​ что данный инструмент​. В поле​В поле​ наиболее точный.​​ данных по этому​​ метод: велика вероятность​

​ расположенные в хронологическом​ переменной, исключив из​Пример 1. В таблице​ + Enter (чтобы​Включить статистические данные прогноза​ выборе даты до​ неточности и грамматические​Степень​ необходимо сохранить прежние​

​ выполните следующие действия.​​«OK»​ определяет только изменение​

​«Известные значения x»​«Известные значения y»​Нужно заметить, что эффективным​ показателю за предыдущие​ больших отклонений и​ порядке.​ расчетов отрицательные числа.​ приведены данные о​ ввести массив функций​Установите этот флажок, если​ конца статистических данных​ ошибки. Для нас​наибольшую степень для​ данные, скопируйте их​Укажите не менее двух​.​ величины выручки за​вводим адрес столбца​указываем координаты столбца​ прогноз с помощью​ 12 лет.​ неточностей.​

​Подобные данные распространены в​

lumpics.ru

Прогнозирование значений в рядах

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

​Строим график зависимости на​Умение строить прогнозы, предсказывая​ самых разных сферах​Для расчета будущих значений​ за 23 дня​ Таким образом получаем​ статистические сведения о​​ от даты начала​​ статья была вам​При выборе типа​ или другой столбец,​ значения.​ нем вносим данные​ в нашем случае​

Автоматическое заполнение ряда на основе арифметической прогрессии

​. В поле​. Это можно сделать,​ тренда может быть,​ основе табличных данных,​ (хотя бы примерно!)​ человеческой деятельности: ежедневные​

​ Y без учета​	​ текущего месяца. Согласно​
​ сразу 2 значения​	​ включенных на новый​
​ предсказанного (это иногда​	​ полезна. Просим вас​
​Скользящее среднее​	​ а затем приступайте​

​Если требуется повысить точность​ точно так, как​

1. ​ равен одному году,​«Новые значения x»​ установив курсор в​

   ​ если период прогнозирования​ состоящих из аргументов​ будущее развитие событий​

2. ​ цены акций, курсов​ отрицательных значений (-5,​ прогнозам специалистов, средняя​ коефициентов для (a)​

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

​ а вот общий​​заносим ссылку на​ поле, а затем,​ не превышает 30%​ и значений функции.​ – неотъемлемая и​​ валют, ежеквартальные, годовые​​ -20 и -35)​​ стоимость 1 л​​ и (b).​​ результате добавит таблицу​​Советы:​​ и сообщить, помогла​​Период​

Автоматическое заполнение ряда на основе геометрической прогрессии

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

​ очень важная часть​	​ объемы продаж, производства​
​ используем формулу:​	​ бензина в текущем​
​Рассчитаем для каждого периода​	​ статистики, созданной с​
​ ​	​ ли она вам,​

​число периодов, используемых​Главная​

1. ​Перетащите маркер заполнения в​ЛИНЕЙН​ подсчитать отдельно, прибавив​

   ​ номер года, на​ мыши и выделив​ периодов. То есть,​

2. ​ табличную область, а​ любого современного бизнеса.​ и т.д. Типичный​0;B2:B11;0);ЕСЛИ(B2:B11>0;A2:A11;0))’ class=’formula’>​ месяце не превысит​ у-значение линейного тренда.​ помощью ПРОГНОЗА. ETS.​Запуск прогноза до последней​​ с помощью кнопок​​ для расчета скользящего​

​в группе​ нужном направлении, чтобы​. Щелкаем по кнопке​ к последнему фактическому​ который нужно указать​ соответствующий столбец на​ при анализе периода​

​ затем, находясь во​​ Само-собой, это отдельная​ временной ряд в​C помощью функций ЕСЛИ​ 41,5 рубля. Спрогнозировать​ Для этого в​​ СТАТИСТИКА функциями, а​​ точке статистических дает​​ внизу страницы. Для​​ среднего.​​Правка​​ заполнить ячейки возрастающими​​«OK»​​ значению прибыли результат​

Ручное прогнозирование линейной или экспоненциальной зависимости

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

• ​ стоимость бензина на​ известное уравнение подставим​ также меры, например​ представление точности прогноза​

• ​ удобства также приводим​Примечания:​нажмите кнопку​ или убывающими значениями.​

​.​ вычисления оператора​ случае это 2019​Аналогичным образом в поле​ мы не можем​«Вставка»​ с кучей методов​ объем осадков.​

​ диапазона B2:B11 и​ оставшиеся дни месяца,​

1. ​ рассчитанные коэффициенты (х​ сглаживания коэффициенты (альфа,​ как можно сравнивать​

   ​ ссылку на оригинал​​ ​​Заполнить​Например, если ячейки C1:E1​Результат экспоненциального тренда подсчитан​ЛИНЕЙН​ год. Поле​«Известные значения x»​ составить эффективный прогноз​, кликаем по значку​ и подходов, но​

2. ​Если фиксировать значения какого-то​​ отброс отрицательных чисел.​​ сравнить рассчитанное среднее​​ – номер периода).​​ бета-версии, гамма) и​​ прогнозируемое ряд фактические​​ (на английском языке).​​В поле​​и выберите пункт​

3. ​ содержат начальные значения​ и выведен в​

   • ​, умноженный на количество​«Константа»​вносим адрес столбца​​ более чем на​​ нужного вида диаграммы,​

   • ​ часто для грубой​ процесса через определенные​ Так, получаем прогнозные​​ значение с предсказанным​​Чтобы определить коэффициенты сезонности,​

4. ​ метрик ошибки (MASE,​​ данные. Тем не​​Если у вас есть​Построен на ряде​Прогрессия​

​ 3, 5 и​	​ обозначенную ячейку.​
​ лет.​	​оставляем пустым. Щелкаем​«Год»​ 3-4 года. Но​
​ который находится в​	​ повседневной оценки ситуации​ промежутки времени, то​ данные на основании​ специалистами.​ сначала найдем отклонение​

1. ​ SMAPE, обеспечения, RMSE).​​ менее при запуске​​ статистические данные с​​перечислены все ряды​​.​​ 8, то при​​Ставим знак​

2. ​Производим выделение ячейки, в​​ по кнопке​​с данными за​ даже в этом​ блоке​

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

Вычисление трендов с помощью добавления линии тренда на диаграмму

​Выполните одно из указанных​ протаскивании вправо значения​«=»​ которой будет производиться​«OK»​ прошедший период.​ случае он будет​«Диаграммы»​ Одна из них​ ряда. Их изменчивость​ с номерами 2,3,5,6,8-10.​Чтобы определить предполагаемую стоимость​ значений тренда («продажи​ создания прогноза возвращаются​ созданный прогноз не​ вы можете создать​ линии тренда. Для​ ниже действий.​

​ будут возрастать, влево —​в пустую ячейку.​ вычисление и запускаем​.​После того, как вся​ относительно достоверным, если​. Затем выбираем подходящий​

1. ​ – это функция​

2. ​ пытаются разделить на​ Для детального анализа​ бензина на оставшиеся​ за год» /​

3. ​ таблица со статистическими​​ обязательно прогноз, что​​ прогноз на их​​ добавления линии тренда​​Если необходимо заполнить значениями​​ убывать.​​ Открываем скобки и​ Мастер функций. Выделяем​Оператор обрабатывает данные и​ информация внесена, жмем​

4. ​ за это время​ для конкретной ситуации​ПРЕДСКАЗ (FORECAST)​ закономерную и случайную​ формулы выберите инструмент​ дни используем следующую​ «линейный тренд»).​​ и предсказанными данными​​ вам будет использовать​

5. ​ основе. При этом​ к другим рядам​ ряда часть столбца,​

   • ​Совет:​​ выделяем ячейку, которая​​ наименование​​ выводит результат на​​ на кнопку​ не будет никаких​

   • ​ тип. Лучше всего​​, которая умеет считать​​ составляющие. Закономерные изменения​​ «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу».​​ функцию (как формулу​Рассчитаем средние продажи за​ и диаграмма. Прогноз​

​ статистических данных. Использование​​ в Excel создается​

• ​ выберите нужное имя​​ выберите вариант​​ Чтобы управлять созданием ряда​ содержит значение выручки​«ЛИНЕЙН»​ экран. Как видим,​«OK»​ форс-мажоров или наоборот​ выбрать точечную диаграмму.​ прогноз по линейному​ членов ряда, как​

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

Прогнозирование значений с помощью функции

​ сумма прогнозируемой прибыли​​.​ чрезвычайно благоприятных обстоятельств,​ Можно выбрать и​ тренду.​ правило, предсказуемы.​ вычислений формулы:​Описание аргументов:​ формулы СРЗНАЧ.​ на основе имеющихся​ дает более точные​ таблицей, содержащей статистические​ затем выберите нужные​.​ ряд значений с​ период. Ставим знак​«Статистические»​

​ на 2019 год,​Оператор производит расчет на​​ которых не было​ другой вид, но​Принцип работы этой функции​​Сделаем анализ временных рядов​​Полученные результаты:​A26:A33 – диапазон ячеек​Определим индекс сезонности для​ данных, зависящих от​ прогноза.​​ и предсказанные значения,​​ параметры.​​Если необходимо заполнить значениями​​ помощью клавиатуры, воспользуйтесь​«*»​и жмем на​​ рассчитанная методом линейной​​ основании введенных данных​​ в предыдущих периодах.​​ тогда, чтобы данные​ несложен: мы предполагаем,​ в Excel. Пример:​Функция имеет следующую синтаксическую​

​ с номерами дней​ каждого месяца (отношение​​ времени, и алгоритма​Если в ваших данных​ и диаграммой, на​Если к двумерной диаграмме​ ряда часть строки,​ командой​и выделяем ячейку,​ кнопку​ зависимости, составит, как​ и выводит результат​Урок:​

​ отображались корректно, придется​ что исходные данные​ торговая сеть анализирует​

​ запись:​	​ месяца, для которых​
​ продаж месяца к​	​ экспоненциального сглаживания (ETS)​
​ прослеживаются сезонные тенденции,​	​ которой они отражены.​
​ (диаграмме распределения) добавляется​	​ выберите вариант​
​Прогрессия​	​ содержащую экспоненциальный тренд.​
​«OK»​	​ и при предыдущем​

Выполнение регрессионного анализа с надстройкой «Пакет анализа»

​ на экран. На​Как построить линию тренда​ выполнить редактирование, в​ можно интерполировать (сгладить)​ данные о продажах​=ПРЕДСКАЗ(x;известные_значения_y;известные_значения_x)​ данные о стоимости​ средней величине). Фактически​ версии AAA.​ то рекомендуется начинать​

support.office.com

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

​ С помощью прогноза​​ скользящее среднее, то​по строкам​(вкладка​ Ставим знак минус​.​ методе расчета, 4637,8​ 2018 год планируется​ в Excel​ частности убрать линию​ некой прямой с​ товаров магазинами, находящимися​Описание аргументов:​ бензина еще не​ нужно каждый объем​Таблицы могут содержать следующие​ прогнозирование с даты,​ вы можете предсказывать​ это скользящее среднее​.​Главная​

​ и снова кликаем​В поле​ тыс. рублей.​ прибыль в районе​Экстраполяцию для табличных данных​ аргумента и выбрать​ классическим линейным уравнением​ в городах с​x – обязательный для​ определены;​ продаж за месяц​ столбцы, три из​ предшествующей последней точке​ такие показатели, как​ базируется на порядке​В поле​, группа​ по элементу, в​«Известные значения y»​

​Ещё одной функцией, с​ 4564,7 тыс. рублей.​ можно произвести через​ другую шкалу горизонтальной​ y=kx+b:​

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

1. ​ населением менее 50​ заполнения аргумент, характеризующий​B3:B25 – диапазон ячеек,​

   • ​ разделить на средний​ которых являются вычисляемыми:​ статистических данных.​

   • ​ будущий объем продаж,​

     ​ расположения значений X​Шаг​Редактирование​

   ​ котором находится величина​​, открывшегося окна аргументов,​ помощью которой можно​ На основе полученной​ стандартную функцию Эксель​ оси.​Построив эту прямую и​ 000 человек. Период​ одно или несколько​ содержащих данные о​ объем продаж за​столбец статистических значений времени​Доверительный интервал​ потребность в складских​ в диаграмме. Для​введите число, которое​, кнопка​ выручки за последний​ вводим координаты столбца​ производить прогнозирование в​ таблицы мы можем​ПРЕДСКАЗ​Теперь нам нужно построить​

2. ​ продлив ее вправо​

   ​ – 2012-2015 гг.​​ новых значений независимой​ стоимости бензина за​ год.​ (ваш ряд данных,​

3. ​Установите или снимите флажок​​ запасах или потребительские​​ получения нужного результата​​ определит значение шага​​Заполнить​​ период. Закрываем скобку​​«Прибыль предприятия»​

   

4. ​ Экселе, является оператор​​ построить график при​​. Этот аргумент относится​ линию тренда. Делаем​ за пределы известного​

   

5. ​ Задача – выявить​​ переменной, для которых​​ последние 23 дня;​В ячейке H2 найдем​ содержащий значения времени);​​доверительный интервал​​ тенденции.​

   ​ перед добавлением скользящего​ прогрессии.​).​ и вбиваем символы​. В поле​ РОСТ. Он тоже​

   ​ помощи инструментов создания​ к категории статистических​ щелчок правой кнопкой​ временного диапазона -​ основную тенденцию развития.​ требуется предсказать значения​

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

​A3:A25 – диапазон ячеек​ общий индекс сезонности​столбец статистических значений (ряд​​, чтобы показать или​​Сведения о том, как​

​ среднего, возможно, потребуется​Тип прогрессии​В экспоненциальных рядах начальное​«*3+»​

​«Известные значения x»​	​ относится к статистической​
​ диаграммы, о которых​	​ инструментов и имеет​ мыши по любой​ получим искомый прогноз.​Внесем данные о реализации​ y (зависимой переменной).​ с номерами дней,​ через функцию: =СРЗНАЧ(G2:G13).​ данных, содержащий соответствующие​ ​ скрыть ее. Доверительный​​ вычисляется прогноз и​ ​ отсортировать значения X.​Результат шага​ значение умножается на​без кавычек. Снова​вносим адрес колонки​ группе инструментов, но,​ шла речь выше.​ следующий синтаксис:​ из точек диаграммы.​Для построения этой прямой​ в таблицу Excel:​ Может принимать числовое​ для которых уже​Спрогнозируем продажи, учитывая рост​ значения);​ ​ интервал — диапазон​ какие параметры можно​Использование функции ПРЕДСКАЗ​Арифметическая​ шаг для получения​ кликаем по той​
​«Год»​	​ в отличие от​​Если поменять год в​​=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)​ В активировавшемся контекстном​ Excel использует известный​На вкладке «Данные» нажимаем​ значение, массив чисел,​ известна стоимость бензина.​ объема и сезонность.​столбец прогнозируемых значений (вычисленных​ вокруг каждого предполагаемые​ изменить, приведены ниже​   . Функция ПРЕДСКАЗ вычисляет​Шаг — это число, добавляемое​ следующего значения в​ же ячейке, которую​. Остальные поля оставляем​ предыдущих, при расчете​ ячейке, которая использовалась​«X»​ меню останавливаем выбор​
​метод наименьших квадратов​	​ кнопку «Анализ данных».​ ссылку на одну​Результат расчетов:​ На 3 месяца​ с помощью функции​ значения, в котором​ в этой статье.​ или предсказывает будущее​ к каждому следующему​ ряде. Получившийся результат​​ выделяли в последний​​ пустыми. Затем жмем​ применяет не метод​ ​ для ввода аргумента,​​– это аргумент,​ на пункте​. Если коротко, то​ Если она не​ ячейку или диапазон;​Рассчитаем среднюю стоимость 1​ вперед. Продлеваем номера​ ПРЕДСКАЗ.ЕTS);​ 95% точек будущих​На листе введите два​ значение по существующим​ члену прогрессии.​ и каждый последующий​ раз. Для проведения​ на кнопку​
​ линейной зависимости, а​	​ то соответственно изменится​ значение функции для​«Добавить линию тренда»​ суть этого метода​​ видна, заходим в​​известные_значения_y – обязательный аргумент,​
​ л бензина на​	​ периодов временного ряда​Два столбца, представляющее доверительный​ ожидается, находится в​ ряда данных, которые​ значениям. Предсказываемое значение —​​Геометрическая​​ результат умножаются на​
​ расчета жмем на​«OK»​	​ экспоненциальной. Синтаксис этого​ результат, а также​ которого нужно определить.​.​ в том, что​ меню. «Параметры Excel»​ характеризующий уже известные​ основании имеющихся и​ на 3 значения​ интервал (вычисленных с​ интервале, на основе​​ соответствуют друг другу:​​ это y-значение, соответствующее​
​Начальное значение умножается на​	​ шаг.​ кнопку​.​ инструмента выглядит таким​ автоматически обновится график.​ В нашем случае​​Открывается окно форматирования линии​​ наклон и положение​ – «Надстройки». Внизу​
​ числовые значения зависимой​	​ расчетных данных с​ в столбце I:​ помощью функции ПРОГНОЗА.​ прогноза (с нормальным​ряд значений даты или​ заданному x-значению. Известные​ шаг. Получившийся результат​Начальное значение​Enter​Программа рассчитывает и выводит​ образом:​ Например, по прогнозам​ в качестве аргумента​ тренда. В нем​

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

​ линии тренда подбирается​ нажимаем «Перейти» к​ переменной y. Может​ помощью функции:​Рассчитаем значения тренда для​ ETS. CONFINT). Эти​ распределением). Доверительный интервал​ времени для временной​ значения — это существующие​ и каждый последующий​Продолжение ряда (геометрическая прогрессия)​

​.​ в выбранную ячейку​=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])​

• ​ в 2019 году​ будет выступать год,​ можно выбрать один​

• ​ так, чтобы сумма​ «Надстройкам Excel» и​ быть указан в​

• ​=СРЗНАЧ(B3:B33)​ будущих периодов: изменим​ столбцы отображаются только​

• ​ помогут вам понять,​ шкалы;​ x- и y-значения;​ результат умножаются на​1, 2​Прогнозируемая сумма прибыли в​ значение линейного тренда.​​Как видим, аргументы у​​ сумма прибыли составит​​ на который следует​​ из шести видов​

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

​ квадратов отклонений исходных​ выбираем «Пакет анализа».​ виде массива чисел​Результат:​ в уравнении линейной​

См. также:

​ в том случае,​

support.office.com

Прогнозирование продаж в Excel и алгоритм анализа временного ряда

​ точности прогноза. Меньший​ряд соответствующих значений показателя.​ новое значение предсказывается​ шаг.​

​4, 8, 16​ 2019 году, которая​Теперь нам предстоит выяснить​ данной функции в​ 4637,8 тыс. рублей.​ произвести прогнозирование.​

​ аппроксимации:​ данных от построенной​Подключение настройки «Анализ данных»​ или ссылки на​

​Можно сделать вывод о​ функции значение х.​ если установлен флажок​ интервал подразумевает более​Эти значения будут предсказаны​ с использованием линейной​В разделе​1, 3​ была рассчитана методом​ величину прогнозируемой прибыли​ точности повторяют аргументы​Но не стоит забывать,​

Пример прогнозирования продаж в Excel

​«Известные значения y»​Линейная​ линии тренда была​ детально описано здесь.​ диапазон ячеек с​ том, что если​ Для этого можно​доверительный интервал​ уверенно предсказанного для​ для дат в​ регрессии. Этой функцией​Тип​

​9, 27, 81​

​ экспоненциального приближения, составит​ на 2019 год.​

• ​ оператора​
• ​ что, как и​
• ​— база известных​;​ минимальной, т.е. линия​Нужная кнопка появится на​
• ​ числами;​ тенденция изменения цен​

​ просто скопировать формулу​в разделе​ определенный момент. Уровня​ будущем.​

1. ​ можно воспользоваться для​выберите тип прогрессии:​2, 3​ 4639,2 тыс. рублей,​ Устанавливаем знак​ТЕНДЕНЦИЯ​
2. ​ при построении линии​ значений функции. В​Логарифмическая​ тренда наилучшим образом​ ленте.​известные_значения_x – обязательный аргумент,​ на бензин сохранится,​ из D2 в​Параметры​ достоверности 95% по​Примечание:​ прогнозирования будущих продаж,​арифметическая​4.5, 6.75, 10.125​
3. ​ что опять не​«=»​, так что второй​ тренда, отрезок времени​ нашем случае в​;​
4. ​ сглаживала фактические данные.​Из предлагаемого списка инструментов​ который характеризует уже​ предсказания специалистов относительно​ J2, J3, J4.​окна…​
5. ​ умолчанию могут быть​ Для временной шкалы требуются​ потребностей в складских​
6. ​или​Для прогнозирования экспоненциальной зависимости​ сильно отличается от​в любую пустую​ раз на их​ до прогнозируемого периода​ её роли выступает​Экспоненциальная​Excel позволяет легко построить​
7. ​ для статистического анализа​ известные значения независимой​ средней стоимости сбудутся.​
8. ​На основе полученных данных​Щелкните эту ссылку, чтобы​ изменены с помощью​ одинаковые интервалы между​ запасах или тенденций​геометрическая​ выполните следующие действия.​
9. ​ результатов, полученных при​ ячейку на листе.​ описании останавливаться не​ не должен превышать​ величина прибыли за​;​ линию тренда прямо​ выбираем «Экспоненциальное сглаживание».​
10. ​ переменной x, для​​ составляем прогноз по​ загрузить книгу с​ вверх или вниз.​ точками данных. Например,​

​ потребления.​.​

​Укажите не менее двух​

​ вычислении предыдущими способами.​

​ Кликаем по ячейке,​

Алгоритм анализа временного ряда и прогнозирования

​ будем, а сразу​ 30% от всего​ предыдущие периоды.​Степенная​ на диаграмме щелчком​

1. ​ Этот метод выравнивания​ которой определены значения​
2. ​Пример 2. Компания недавно​ продажам на следующие​
3. ​ помощью Excel ПРОГНОЗА.​Сезонность​

​ это могут быть​Использование функций ТЕНДЕНЦИЯ и​В поле​ ячеек, содержащих начальные​Урок:​ в которой содержится​

• ​ перейдем к применению​

​ срока, за который​«Известные значения x»​;​ правой по ряду​

exceltable.com

Функция ПРЕДСКАЗ для прогнозирования будущих значений в Excel

​ подходит для нашего​ зависимой переменной y.​ представила новый продукт.​ 3 месяца (следующего​ Примеры использования функции​Сезонности — это число​ месячные интервалы со​ РОСТ​Предельное значение​ значения.​Другие статистические функции в​ фактическая величина прибыли​ этого инструмента на​ накапливалась база данных.​— это аргументы,​Полиномиальная​ – Добавить линию​ динамического ряда, значения​Примечания:​ С момента вывода​ года) с учетом​ ETS​ в течение (количество​ значениями на первое​   . Функции ТЕНДЕНЦИЯ и​введите значение, на​

Примеры использования функции ПРЕДСКАЗ в Excel

​Если требуется повысить точность​ Excel​ за последний изучаемый​ практике.​

1. ​Урок:​ которым соответствуют известные​;​ тренда (Add Trendline),​ которого сильно колеблются.​Второй и третий аргументы​ на рынок ежедневно​
2. ​ сезонности:​Функции прогнозирования​

​ точек) сезонного узора​ число каждого месяца,​ РОСТ позволяют экстраполировать​ котором нужно остановить​ прогноза, укажите дополнительные​Мы выяснили, какими способами​ год (2016 г.).​Выделяем ячейку вывода результата​Экстраполяция в Excel​ значения функции. В​Линейная фильтрация​ но часто для​Заполняем диалоговое окно. Входной​ рассматриваемой функции должны​ ведется учет количества​

​Общая картина составленного прогноза​

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

​ начальные значения.​

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

​y​

​Примечание:​Удерживая правую кнопку мыши,​ в программе Эксель.​«+»​ путем вызываем​

​ ещё одну функцию​

​ нас выступает нумерация​

​Давайте для начала выберем​ не линия, а​ со значениями продаж.​ непустые диапазоны ячеек​ продукт. Предположить, каким​График прогноза продаж:​

​ при наличии всех​

Анализ прогноза спроса продукции в Excel по функции ПРЕДСКАЗ

​ продаж, с каждой​ временной шкале не​-значения, продолжающие прямую линию​ Если в ячейках уже​ перетащите маркер заполнения​ Графическим путем это​. Далее кликаем по​Мастер функций​ –​ годов, за которые​

​ линейную аппроксимацию.​

​ числовые значения прогноза,​ Фактор затухания –​ или такие диапазоны,​ будет спрос на​График сезонности:​ необходимых финансовых показателей.​ точки, представляющий месяц,​ хватает до 30 %​ или экспоненциальную кривую,​ содержатся первые члены​ в нужном направлении​ можно сделать через​ ячейке, в которой​. В списке статистических​ТЕНДЕНЦИЯ​ была собрана информация​В блоке настроек​ которые ей соответствуют.​ коэффициент экспоненциального сглаживания​ в которых число​ протяжении 5 последующих​

​​В данном примере будем​ сезонности равно 12.​

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

​ о прибыли предыдущих​

​«Прогноз»​ Вот, как раз,​ (по умолчанию –​

​ ячеек совпадает. Иначе​ дней.​Алгоритм анализа временного ряда​

​ использовать линейный тренд​

​ Автоматическое обнаружение можно​ есть несколько чисел​ существующие данные. Эти​ чтобы приложение Microsoft​ возрастающими или убывающими​ а аналитическим –​ линейный тренд. Ставим​«РОСТ»​ к категории статистических​ лет.​в поле​

Прогнозирование будущих значений в Excel по условию

​ их и вычисляет​ 0,3). Выходной интервал​ функция ПРЕДСКАЗ вернет​Вид исходной таблицы данных:​ для прогнозирования продаж​ для составления прогноза​ переопределить, выбрав​ с одной и​ функции могут возвращать​ Excel создало прогрессию​

​ значениями, отпустите правую​

​ используя целый ряд​ знак​, выделяем его и​ операторов. Её синтаксис​Естественно, что в качестве​

​«Вперед на»​

​ функция​ – ссылка на​ код ошибки #Н/Д.​Как видно, в первые​ в Excel можно​ по продажам на​Задание вручную​ той же меткой​y​ автоматически, установите флажок​ кнопку, а затем​ встроенных статистических функций.​«*»​

​ щелкаем по кнопке​

Особенности использования функции ПРЕДСКАЗ в Excel

​ во многом напоминает​ аргумента не обязательно​

​устанавливаем число​

​ПРЕДСКАЗ (FORECAST)​

• ​ верхнюю левую ячейку​Если одна или несколько​ дни спрос был​ построить в три​ бушующие периоды с​и затем выбрав​ времени, это нормально.​-значения, соответствующие заданным​Автоматическое определение шага​ щелкните​ В результате обработки​
• ​. Так как между​«OK»​ синтаксис инструмента​ должен выступать временной​«3,0»​.​ выходного диапазона. Сюда​ ячеек из диапазона,​ небольшим, затем он​
• ​ шага:​ учетом сезонности.​ числа.​ Прогноз все равно​x​.​

​Экспоненциальное приближение​

1. ​ идентичных данных этими​ последним годом изучаемого​.​ПРЕДСКАЗ​ отрезок. Например, им​, так как нам​Синтаксис функции следующий​ программа поместит сглаженные​ ссылка на который​
2. ​ рос достаточно большими​Выделяем трендовую составляющую, используя​Линейный тренд хорошо подходит​Примечание:​ будет точным. Но​-значениям, на базе линейной​Если имеются существующие данные,​в контекстное меню.​ операторами может получиться​ периода (2016 г.)​Происходит активация окна аргументов​и выглядит следующим​ может являться температура,​
3. ​ нужно составить прогноз​=ПРЕДСКАЗ(X; Известные_значения_Y; Известные_значения_X)​ уровни и размер​ передана в качестве​ темпами, а на​ функцию регрессии.​ для формирования плана​ Если вы хотите задать​ для повышения точности​ или экспоненциальной зависимости.​
4. ​ для которых следует​Например, если ячейки C1:E1​ разный итог. Но​ и годом на​ указанной выше функции.​ образом:​
5. ​ а значением функции​ на три года​где​ определит самостоятельно. Ставим​ аргумента x, содержит​ протяжении последних трех​Определяем сезонную составляющую в​ по продажам для​
6. ​ сезонность вручную, не​ прогноза желательно перед​ Используя существующие​ спрогнозировать тренд, можно​ содержат начальные значения​ это не удивительно,​ который нужно сделать​ Вводим в поля​=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])​ может выступать уровень​ вперед. Кроме того,​Х​ галочки «Вывод графика»,​ нечисловые данные или​ дней изменялся незначительно.​ виде коэффициентов.​

exceltable.com

Анализ временных рядов и прогнозирование в Excel на примере

​ развивающегося предприятия.​ используйте значения, которые​ его созданием обобщить​x​ создать на диаграмме​ 3, 5 и​ так как все​

​ прогноз (2019 г.)​ этого окна данные​Как видим, аргументы​ расширения воды при​ можно установить галочки​- точка во​ «Стандартные погрешности».​ текстовую строку, которая​ Это свидетельствует о​Вычисляем прогнозные значения на​

Временные ряды в Excel

​Excel – это лучший​ меньше двух циклов​ данные.​-значения и​ линия тренда. Например,​ 8, то при​ они используют разные​ лежит срок в​ полностью аналогично тому,​«Известные значения y»​

​ нагревании.​ около настроек​ времени, для которой​Закрываем диалоговое окно нажатием​ не может быть​ том, что основным​ определенный период.​ в мире универсальный​ статистических данных. При​Выделите оба ряда данных.​y​

​ если имеется созданная​ протаскивании вправо значения​

​ методы расчета. Если​ три года, то​ как мы их​и​При вычислении данным способом​«Показывать уравнение на диаграмме»​ мы делаем прогноз​ ОК. Результаты анализа:​ преобразована в число,​

​ фактором роста продаж​Нужно понимать, что точный​

​ аналитический инструмент, который​ таких значениях этого​

​Совет:​-значения, возвращаемые этими функциями,​ в Excel диаграмма,​ будут возрастать, влево —​ колебание небольшое, то​ устанавливаем в ячейке​ вводили в окне​

​«Известные значения x»​ используется метод линейной​и​Известные_значения_Y​Для расчета стандартных погрешностей​ результатом выполнения функции​ на данный момент​ прогноз возможен только​ позволяет не только​ параметра приложению Excel​ Если выделить ячейку в​ можно построить прямую​ на которой приведены​ убывать.​ все эти варианты,​

​ число​ аргументов оператора​

​полностью соответствуют аналогичным​ регрессии.​«Поместить на диаграмме величину​- известные нам​ Excel использует формулу:​ ПРЕДСКАЗ для данных​

​ является не расширение​

Прогнозирование временного ряда в Excel

​ при индивидуализации модели​ обрабатывать статистические данные,​ не удастся определить​

​ одном из рядов,​ или кривую, описывающую​ данные о продажах​Совет:​ применимые к конкретному​«3»​

​ТЕНДЕНЦИЯ​

​ элементам оператора​Давайте разберем нюансы применения​ достоверности аппроксимации (R^2)»​

​ значения зависимой переменной​ =КОРЕНЬ(СУММКВРАЗН(‘диапазон фактических значений’;​ значений x будет​ базы клиентов, а​ прогнозирования. Ведь разные​

​ но и составлять​ сезонные компоненты. Если​ Excel автоматически выделит​

​ существующие данные.​ за первые несколько​ Чтобы управлять созданием ряда​ случаю, можно считать​. Чтобы произвести расчет​. После того, как​ПРЕДСКАЗ​

​ оператора​

​. Последний показатель отображает​ (прибыль)​ ‘диапазон прогнозных значений’)/​ код ошибки #ЗНАЧ!.​ развитие продаж с​

​ временные ряды имеют​ прогнозы с высокой​ же сезонные колебания​ остальные данные.​

​Использование функций ЛИНЕЙН и​ месяцев года, можно​

​ вручную или заполнять​ относительно достоверными.​ кликаем по кнопке​ информация внесена, жмем​, а аргумент​

exceltable.com

Быстрый прогноз функцией ПРЕДСКАЗ (FORECAST)

​ПРЕДСКАЗ​ качество линии тренда.​Известные_значения_X​ ‘размер окна сглаживания’).​Статистическая дисперсия величин (можно​ постоянными клиентами. В​ разные характеристики.​ точностью. Для того​ недостаточно велики и​На вкладке​ ЛГРФПРИБЛ​ добавить к ней​ ряд значений с​Автор: Максим Тютюшев​Enter​​ на кнопку​​«Новые значения x»​на конкретном примере.​ После того, как​

​- известные нам​ Например, =КОРЕНЬ(СУММКВРАЗН(C3:C5;D3:D5)/3).​ рассчитать с помощью​ таких случаях рекомендуют​бланк прогноза деятельности предприятия​ чтобы оценить некоторые​ алгоритму не удается​

​Данные​   . Функции ЛИНЕЙН и​ линию тренда, которая​ помощью клавиатуры, воспользуйтесь​Когда необходимо оценить затраты​

​.​«OK»​​соответствует аргументу​​ Возьмем всю ту​ настройки произведены, жмем​ значения независимой переменной​​ формул ДИСП.Г, ДИСП.В​ использовать не линейную​Чтобы посмотреть общую картину​ возможности Excel в​ их выявить, прогноз​в группе​ ЛГРФПРИБЛ позволяют вычислить​ представит общие тенденции​

​ командой​ следующего года или​Как видим, прогнозируемая величина​.​«X»​ же таблицу. Нам​ на кнопку​ (даты или номера​Составим прогноз продаж, используя​ и др.), передаваемых​ регрессию, а логарифмический​ с графиками выше​ области прогнозирования продаж,​ примет вид линейного​​Прогноз​​ прямую линию или​

​ продаж (рост, снижение​

​Прогрессия​

​ предсказать ожидаемые результаты​

• ​ прибыли, рассчитанная методом​​Результат обработки данных выводится​предыдущего инструмента. Кроме​ нужно будет узнать​
• ​«Закрыть»​​ периодов)​ данные из предыдущего​ в качестве аргумента​
• ​ тренд, чтобы результаты​​ описанного прогноза рекомендуем​ разберем практический пример.​ тренда.​нажмите кнопку​

planetaexcel.ru

​ экспоненциальную кривую для​

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Любому бизнесу интересно заглянуть в будущее и правильно ответить на вопрос: «А сколько денег мы заработаем за следующий период?» Ответить на такого рода вопросы позволяют различные методики прогнозирования. В данной статье мы с вами рассмотрим несколько таких методик и произведем все необходимые расчеты в Excel. Еще больше про анализ данных в Excel мы рассказываем на нашем открытом курсе «Аналитика в Excel».

Постановка задачи

Исходные данные

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

Примечание. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение.

Составляющие прогноза

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

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

Эти три пункта в совокупность образуют регулярную составляющую временного ряда.

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

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

Вывод. Чтобы комплексно описать временной ряд, необходимо учесть 2 главных компонента: регулярную составляющую (тренд + сезонность + цикличность) и случайную составляющую.

Виды моделей

Следующий вопрос, на который нужно ответить при построении прогноза: “А какие модели временного ряда бывают?”

Обычно выделяют два основных вида:

• Аддитивная модель: Уровень временного ряда = Тренд + Сезонность + Случайные отклонения
• Мультипликативная модель: Уровень временного ряда = Тренд X Сезонность X Случайные отклонения

Иногда также выделают смешанную модель в отдельную группу:

• Смешанная модель: Уровень временного ряда = Тренд X Сезонность + Случайные отклонения

С моделями мы определились, но теперь возникает еще один вопрос: «А когда какую модель лучше использовать?»

Классический вариант такой:
— Аддитивная модель используется, если амплитуда колебаний более-менее постоянная;
— Мультипликативная – если амплитуда колебаний зависит от значения сезонной компоненты.

Пример:

Решение задачи с помощью Excel

Итак, необходимые теоретические знания мы с вами получили, пришло время применить их на практике. Мы будем с вами использовать классическую аддитивную модель для построения прогноза. Однако, мы построим с вами два прогноза:

1. с использованием линейного тренда
2. с использованием полиномиального тренда

Во всех руководствах, как правило, разбирается только линейный тренд, поэтому полиномиальная модель будет крайне полезна для вас и вашей работы!

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Модель с линейным трендом

Пусть у нас есть исходная информация по продажам за 2 года:

Учитывая, что мы используем линейный тренд, то нам необходимо найти коэффициенты уравнения

y = ax + b

где:

• y – значения продаж
• x – номер периода
• a – коэффициент наклона прямой тренда
• b – свободный член тренда

Рассчитать коэффициенты данного уравнения можно с помощью формулы массива и функции ЛИНЕЙН. Нам необходимо будет сделать следующую последовательность действий:

1. Выделяем две ячейки рядом
2. Ставим курсор в поле формул и вводим формулу =ЛИНЕЙН(C4:C27;B4:B27)
3. Нажимаем Ctrl+Shift+Enter, чтобы активировать формулу массива

На выходе мы получили 2 числа: первое — коэффициент a, второе – свободный член b.

Теперь нам нужно рассчитать для каждого периода значение линейного тренда. Сделать это крайне просто — достаточно в полученное уравнение подставить известные номера периодов. Например, в нашем случае, мы прописываем формулу =B4*$F$4+$G$4 в ячейке I4 и протягиваем ее вниз по всем периодам.

Нам осталось рассчитать коэффициент сезонности для каждого периода. Учитывая, что у нас есть исторические данные за два года, разумно будет учесть это при расчете. Можем сделать следующим образом: в ячейке J4 прописываем формулу =(C4+C16)/СРЗНАЧ($C$4:$C$27)/2 и протягиваем вниз на 12 месяцев (т.е. до J15).

Что нам это дало? Мы посчитали, сколько суммарно продавалось каждый январь/каждый февраль и так далее, а потом разделили это на среднее значение продаж за все два периода.

То есть мы выяснили, как продажи двух январей отклонялись от средних продаж за два года, как продажи двух февралей отклонялись и так далее. Это и дает нам коэффициент сезонности. В конце формулы делим на 2, т.к. в расчете фигурировало 2 периода.

Примечание. Рассчитали только 12 коэффициентов, т.к. один коэффициент учитывает продажи сразу за 2 аналогичных периода.

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

Сначала создаем столбец, в котором прописываем номера будущих периодов. В нашем случае нумерация начинается с 25 периода.

Далее, для расчета значения тренда просто прописываем уже известную нам формулу =L4*$F$4+$G$4 и протягиваем вниз на все 12 прогнозируемых периодов.

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

Модель с полиномиальным трендом

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

Посмотрите сами, какая модель более точно аппроксимирует наши точки — линейный тренд (прямая зеленая линия) или полиномиальный тренд (красная кривая)? Ответ очевиден. Поэтому сейчас мы с вами и разберем, как построить полиномиальную модель в Excel.

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

Для начала давайте определимся, чем полиномиальный тренд отличается от обычного линейного. Правильно — формой уравнения. У линейного тренда мы разбирали обычный график прямой:

У полиномиального тренда же уравнение выглядит иначе:

где конечная степень определяется степенью полинома.

Т.е. для полинома 4 степени необходимо найти коэффициенты уравнения:

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

1. Ставим в ячейку F4 курсор и вводим формулу =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^{1;2;3;4});1;1). Функция ЛИНЕЙН позволяет произвести расчет коэффициентов, а с помощью функции ИНДЕКС мы вытаскиваем нужный нам коэффициент. В данном случае за выбор коэффициента отвечает самый последний аргумент. У нас стоит 1 — это коэффициент при самой высокой степени (т.е. при 4 степени, коэффициент). Кстати, узнать о самых полезных математических формулах Excel можно в нашем бесплатном гайде «Математические функции Excel».
2. Аналогично прописываем формулу =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^{1;2;3;4});1;2) в ячейке ниже.
3. Делаем такие же действия, пока не найдем все коэффициенты.

Кстати говоря, мы можем легко сами себя проверить. Давайте построим график наших продаж и добавим к нему полиномиальный тренд.

1. Выделяем столбец с продажами
2. Выбираем «Вставка» → «График» → «Точечный» → «Точечная диаграмма»
3. Нажимаем на любую точку графика правой кнопкой мыши и выбираем «Добавить линию тренда»
4. В открывшемся справа меню выбираем «Полиномиальная модель», меняем степень на 4 и ставим галочку на «Показывать уравнение на диаграмме»

Теперь вы наглядно можете видеть, как рассчитанный тренд аппроксимирует исходные данные и как выглядит само уравнение. Можно сравнить уравнение на графике с вашими коэффициентами. Сходится? Значит сделали все верно!

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

1. Вы можете вывести коэффициент на график, поставив галочку «Поместить на диаграмму величину достоверности аппроксимации»
2. Вы можете рассчитать коэффициент R^2 самостоятельно по формуле =ИНДЕКС(ЛИНЕЙН($C$4:$C$27;$B$4:$B$27^{1;2;3;4};;1);3;1)

Заключение

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

Если вас интересуют еще какие-то модели прогнозирования — напишите нам об этом, и мы постараемся осветить эти темы в дальнейших своих статьях! Или запишитесь на курс «Excel Academy» от SF Education, где мы рассказываем про возможности Excel, необходимые для анализа.

Автор: Алексанян Андрон, эксперт SF Education

КУРС

EXCEL ACADEMY

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Блог SF Education

Data Science

5 примеров экономии времени в Excel

Что для работодателя главное в сотруднике? Добросовестность, ответственность, профессионализм и, конечно же, умение пользоваться отведенным временем! Предлагаем познакомиться с очень нужными, на наш взгляд,…

Функция ПРЕДСКАЗ в Excel позволяет с некоторой степенью точности предсказать будущие значения на основе существующих числовых значений, и возвращает соответствующие величины. Например, некоторый объект характеризуется свойством, значение которого изменяется с течением времени. Такие изменения могут быть зафиксированы опытным путем, в результате чего будет составлена таблица известных значений x и соответствующих им значений y, где x – единица измерения времени, а y – количественная характеристика свойства. С помощью функции ПРЕДСКАЗ можно предположить последующие значения y для новых значений x.

Примеры использования функции ПРЕДСКАЗ в Excel

Функция ПРЕДСКАЗ использует метод линейной регрессии, а ее уравнение имеет вид y=ax+b, где:

1. Коэффициент a рассчитывается как Yср.-bXср. (Yср. и Xср. – среднее арифметическое чисел из выборок известных значений y и x соответственно).
2. Коэффициент b определяется по формуле:

Пример 1. В таблице приведены данные о ценах на бензин за 23 дня текущего месяца. Согласно прогнозам специалистов, средняя стоимость 1 л бензина в текущем месяце не превысит 41,5 рубля. Спрогнозировать стоимость бензина на оставшиеся дни месяца, сравнить рассчитанное среднее значение с предсказанным специалистами.

Вид исходной таблицы данных:

Чтобы определить предполагаемую стоимость бензина на оставшиеся дни используем следующую функцию (как формулу массива):

Описание аргументов:

• A26:A33 – диапазон ячеек с номерами дней месяца, для которых данные о стоимости бензина еще не определены;
• B3:B25 – диапазон ячеек, содержащих данные о стоимости бензина за последние 23 дня;
• A3:A25 – диапазон ячеек с номерами дней, для которых уже известна стоимость бензина.

Результат расчетов:

Рассчитаем среднюю стоимость 1 л бензина на основании имеющихся и расчетных данных с помощью функции:

=СРЗНАЧ(B3:B33)

Результат:

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



Анализ прогноза спроса продукции в Excel по функции ПРЕДСКАЗ

Пример 2. Компания недавно представила новый продукт. С момента вывода на рынок ежедневно ведется учет количества клиентов, купивших этот продукт. Предположить, каким будет спрос на протяжении 5 последующих дней.

Вид исходной таблицы данных:

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

Рассчитаем значения логарифмического тренда с помощью функции ПРЕДСКАЗ следующим способом:

Как видно, в качестве первого аргумента представлен массив натуральных логарифмов последующих номеров дней. Таким образом получаем функцию логарифмического тренда, которая записывается как y=aln(x)+b.

Результат расчетов:

Для сравнения, произведем расчет с использованием функции линейного тренда:

И для визуального сравнительного анализа построим простой график.

Полученные результаты:

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

Прогнозирование будущих значений в Excel по условию

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

Вид таблицы данных:

Для расчета будущих значений Y без учета отрицательных значений (-5, -20 и -35) используем формулу:

C помощью функций ЕСЛИ выполняется перебор элементов диапазона B2:B11 и отброс отрицательных чисел. Так, получаем прогнозные данные на основании значений в строках с номерами 2,3,5,6,8-10. Для детального анализа формулы выберите инструмент «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу». Один из этапов вычислений формулы:

Полученные результаты:

Особенности использования функции ПРЕДСКАЗ в Excel

Функция имеет следующую синтаксическую запись:

=ПРЕДСКАЗ(x;известные_значения_y;известные_значения_x)

Описание аргументов:

• x – обязательный для заполнения аргумент, характеризующий одно или несколько новых значений независимой переменной, для которых требуется предсказать значения y (зависимой переменной). Может принимать числовое значение, массив чисел, ссылку на одну ячейку или диапазон;
• известные_значения_y – обязательный аргумент, характеризующий уже известные числовые значения зависимой переменной y. Может быть указан в виде массива чисел или ссылки на диапазон ячеек с числами;
• известные_значения_x – обязательный аргумент, который характеризует уже известные значения независимой переменной x, для которой определены значения зависимой переменной y.

Примечания:

1. Второй и третий аргументы рассматриваемой функции должны принимать ссылки на непустые диапазоны ячеек или такие диапазоны, в которых число ячеек совпадает. Иначе функция ПРЕДСКАЗ вернет код ошибки #Н/Д.
2. Если одна или несколько ячеек из диапазона, ссылка на который передана в качестве аргумента x, содержит нечисловые данные или текстовую строку, которая не может быть преобразована в число, результатом выполнения функции ПРЕДСКАЗ для данных значений x будет код ошибки #ЗНАЧ!.
3. Статистическая дисперсия величин (можно рассчитать с помощью формул ДИСП.Г, ДИСП.В и др.), передаваемых в качестве аргумента известные_значения_x, не должна равняться 0 (нулю), иначе функция ПРЕДСКАЗ вернет код ошибки #ДЕЛ/0!.
4. Рассматриваемая функция игнорирует ячейки с нечисловыми данными, содержащиеся в диапазонах, которые переданы в качестве второго и третьего аргументов.
5. Функция ПРЕДСКАЗ была заменена функцией ПРЕДСКАЗ.ЛИНЕЙН в Excel версии 2016, но была оставлена для обеспечения совместимости с Excel 2013 и более старыми версиями.
6. Для предсказания только одного будущего значения на основании известного значения независимой переменной функция ПРЕДСКАЗ используется как обычная формула. Если требуется предсказать сразу несколько значений, в качестве первого аргумента следует передать массив или ссылку на диапазон ячеек со значениями независимой переменной, а функцию ПРЕДСКАЗ использовать в качестве формулы массива.