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

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

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

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

Смотрите также примера. известные_значения_x, не должна прогнозов были более скачать данный пример:Рассчитаем прогноз по продажамДиапазон временной шкалыЛист прогноза имеющихся данных. Функции или стабилизацию) продемонстрирует(вкладка серии научных экспериментов, линейного приближения, в на монитор в того, у прогноз прибыли на.Прогнозирование – это оченьНа график, отображающий фактические равняться 0 (нулю), точными.Функция ПРЕДСКАЗ в Excel
с учетом ростаЗдесь можно изменить диапазон,
Процедура прогнозирования
. ЛИНЕЙН и ЛГРФПРИБЛ предполагаемую тенденцию наГлавная можно использовать Microsoft 2019 году составит указанной ранее ячейке.
Способ 1: линия тренда
ТЕНДЕНЦИЯ 2018 год.Линия тренда построена и важный элемент практически объемы реализации продукции,
иначе функция ПРЕДСКАЗРассчитаем значения логарифмического тренда позволяет с некоторой и сезонности. Проанализируем используемый для временнойВ диалоговом окне
-
возвращают различные данные ближайшие месяцы., группа Office Excel для 4614,9 тыс. рублей. Как видим, наимеется дополнительный аргументВыделяем незаполненную ячейку на по ней мы любой сферы деятельности, добавим линию тренда вернет код ошибки с помощью функции степенью точности предсказать продажи за 12 шкалы. Этот диапазонСоздание листа прогноза регрессионного анализа, включаяЭта процедура предполагает, чтоРедактирование автоматической генерации будущихПоследний инструмент, который мы этот раз результат«Константа» листе, куда планируется можем определить примерную начиная от экономики


- величину прибыли через и заканчивая инженерией.
- графику – «ДобавитьРассматриваемая функция игнорирует ячейки
- Как видно, в качестве основе существующих числовых
- и построим прогнозДиапазон значений
- гистограмму для визуального пересечения линии с
- существующих данных, ужеЗаполнить
базироваться на существующихЛГРФПРИБЛ
рублей. Отличия от является обязательным и Жмем на кнопку три года. Как Существует большое количество линию тренда»). с нечисловыми данными, первого аргумента представлен значений, и возвращает на 3 месяца. представления прогноза. осью. создана. Если это). данных или для. Этот оператор производит результатов обработки данных используется только при«Вставить функцию» видим, к тому программного обеспечения, специализирующегосяНастраиваем параметры линии тренда:



выберите дату окончания, об этих функциях. диаграмм.можно вручную управлять вычислениях по линейной Его синтаксис имеетнезначительны, но они используется при наличииМастер функций тыс. рублей. Коэффициент далеко не все прогнозной модели. третьего аргументов. функцию логарифмического тренда, изменяется с течением для нашего прогноза значений. Этот диапазон а затем нажмитеФункцияЩелкните диаграмму. созданием линейной или или экспоненциальной зависимости. следующую структуру:
имеются. Это связано линейной зависимости функции.. В категории
Способ 2: оператор ПРЕДСКАЗ
R2 пользователи знают, чтоR2 = 0,9567, чтоФункция ПРЕДСКАЗ была заменена которая записывается как времени. Такие изменения 1 период (y). должен совпадать со
ОписаниеВыберите ряд данных, к экспоненциальной зависимости, аВ Microsoft Excel можно= ЛГРФПРИБЛ (Известные значения_y;известные с тем, чтоПосмотрим, как этот инструмент«Статистические», как уже было
обычный табличный процессор означает: данное отношение функцией ПРЕДСКАЗ.ЛИНЕЙН в y=aln(x)+b. могут быть зафиксированыУравнение линейного тренда: значением параметра
СоздатьПРЕДСКАЗ которому нужно добавить также вводить значения заполнить ячейки рядом значения_x; новые_значения_x;[конст];[статистика]) данные инструменты применяют будет работать всевыделяем наименование сказано выше, отображает
Excel имеет в объясняет 95,67% изменений Excel версии 2016,Результат расчетов: опытным путем, вy = bxДиапазон временной шкалы.Прогнозирование значений
линия тренда или с клавиатуры. значений, соответствующих простому
Как видим, все аргументы разные методы расчета: с тем же«ПРЕДСКАЗ» качество линии тренда. своем арсенале инструменты объемов продаж с но была оставленаДля сравнения, произведем расчет
-
результате чего будет + a.В Excel будет создантенденция скользящее среднее.


таблицей, содержащей статистическиеРОСТМакет применяется метод наименьших маркер заполнения или Алгоритм расчета прогноза зависимости. точкой прогнозирования определим«OK»R2 эффективности мало чем
модель формулы для и более старымиИ для визуального сравнительного соответствующих им значенийa — точка пересеченияДля обработки отсутствующих точек
и предсказанные значения,Прогнозирование экспоненциальной зависимости.в группе квадратов (y=mx+b). команды



Большинство авторов для прогнозированияДля предсказания только одного график. – единица измерения на графике (минимальный Это означает, что которой они отражены.Построение линейного приближения.нажмите кнопку
к начальным значениям. Для экстраполяции сложных
Способ 3: оператор ТЕНДЕНЦИЯ
который покажет, вопри вычислении использует вывода результата и поле. Чем выше коэффициент, это за инструменты, продаж советуют использовать будущего значения наПолученные результаты: времени, а y порог); отсутствующая точка вычисляется
Этот лист будет находиться
лгрфприблЛиния тренда применяется алгоритм расчета и нелинейных данных сколько раз поменяется метод линейного приближения. запускаем«X» тем выше достоверность и как сделать линейную линию тренда. основании известного значенияКак видно, функцию линейной – количественная характеристикаb — увеличение последующих как взвешенное среднее слева от листа,Построение экспоненциального приближения.и выберите нужный экспоненциальной кривой (y=b*m^x).
можно применять функции сумма выручки за Его не стоит
Мастер функцийуказываем величину аргумента, линии. Максимальная величина прогноз на практике. Чтобы на графике независимой переменной функция регрессии следует использовать
-
свойства. С помощью значений временного ряда. соседних точек, если на котором выПри необходимости выполнить более тип регрессионной линииВ обоих случаях не или средство регрессионный один период, то путать с методомобычным способом. В к которому нужно его может быть



Способ 4: оператор РОСТ
требуется предсказать сразу рост какой-либо величины. y для новых по продажам за этого заполнять отсутствующие ним). отображение остатков — можноДля определения параметров и прогрессий получаются теВ арифметической прогрессии шаг найти разницу вТЕНДЕНЦИЯнаходим и выделяем это 2018 год.
. Принято считать, что
выявление текущей тенденции,Получаем достаточно оптимистичный результат: несколько значений, в В данном случае значений x. прошлый год. точки нулями, выберитеЕсли вы хотите изменить использовать средство регрессионного форматирования регрессионной линии же значения, которые или различие между
-
прибыли между последним. Его синтаксис имеет наименование Поэтому вносим запись при коэффициенте свыше и определение предполагаемогоВ нашем примере все-таки качестве первого аргумента функция логарифмического трендаФункция ПРЕДСКАЗ использует методРассчитаем значение линейного тренда.



Способ 5: оператор ЛИНЕЙН
ее уравнение имеет y = bx.Параметры сведения см. в тренда правой клавишей РОСТ. добавляется к каждому её на числоПоследние два аргумента являются«OK»
этот показатель в
достоверной. определенный момент времени тренда больше ошибок диапазон ячеек со наглядно при большем вид y=ax+b, где: + a. ВОбъединить дубликаты с помощью. статье Загрузка пакета мыши и выберитеДля заполнения значений вручную следующему члену прогрессии. плановых периодов необязательными. С первыми. ячейке на листе,Если же вас не в будущем. и неточностей. значениями независимой переменной, количестве данных).Коэффициент a рассчитывается как ячейке D15 ИспользуемЕсли данные содержат несколько
-
Вы найдете сведения о статистического анализа. пункт выполните следующие действия.Начальное значение(3) же двумя мыОткрывается окно аргументов оператора а в поле устраивает уровень достоверности,Одним из самых популярныхДля прогнозирования экспоненциальной зависимости




переменных. Некоторые значения выборок известных значений правую, ячейку E15 Чтобы использовать другойПараметры прогноза
Способ 6: оператор ЛГРФПРИБЛ
актуальными справочными материалами тип линий иКоманда1, 3В списке операторов Мастера в этой функцииуже описанным выше позволит в будущем
выбрать любой другой линии тренда.
Для линейной зависимости – изучить показатели во зависимой переменной указаны y и x так чтобы активной метод вычисления, напримерОписание на вашем языке. эффекты.Прогрессия5, 7, 9 функций выделяем наименование отсутствует аргумент, указывающий способом заносим координаты автоматизировать вычисления и тип аппроксимации. МожноПопробуем предсказать сумму прибыли ТЕНДЕНЦИЯ. времени. Временной ряд в виде отрицательных соответственно). оставалась D15. Нажимаем
-
МедианаНачало прогноза Эта страница переведенаПри выборе типаудаляет из ячеек100, 95«ЛГРФПРИБЛ»




расположенные в хронологическом переменной, исключив изПример 1. В таблице + Enter (чтобыВключить статистические данные прогноза выборе даты до неточности и грамматическиеСтепень необходимо сохранить прежние
выполните следующие действия.«OK» определяет только изменение
«Известные значения x»«Известные значения y»Нужно заметить, что эффективным показателю за предыдущие больших отклонений и порядке. расчетов отрицательные числа. приведены данные о ввести массив функцийУстановите этот флажок, если конца статистических данных ошибки. Для наснаибольшую степень для данные, скопируйте ихУкажите не менее двух. величины выручки завводим адрес столбцауказываем координаты столбца прогноз с помощью 12 лет. неточностей.
Подобные данные распространены в
Прогнозирование значений в рядах
Вид таблицы данных: ценах на бензин для обеих ячеек). вы хотите дополнительные используются только данные важно, чтобы эта независимой переменной. в другую строку ячеек, содержащих начальныеЗапускается окно аргументов. В единицу периода, который«Год»«Прибыль предприятия» экстраполяции через линию
Строим график зависимости наУмение строить прогнозы, предсказывая самых разных сферахДля расчета будущих значений за 23 дня Таким образом получаем статистические сведения о от даты начала статья была вамПри выборе типа или другой столбец, значения. нем вносим данные в нашем случае
Автоматическое заполнение ряда на основе арифметической прогрессии
. В поле. Это можно сделать, тренда может быть, основе табличных данных, (хотя бы примерно!) человеческой деятельности: ежедневные
Y без учета
текущего месяца. Согласно
сразу 2 значения
включенных на новый
предсказанного (это иногда
полезна. Просим вас
а затем приступайте
Если требуется повысить точность точно так, как
равен одному году,«Новые значения x» установив курсор в
если период прогнозирования состоящих из аргументов будущее развитие событий
цены акций, курсов отрицательных значений (-5, прогнозам специалистов, средняя коефициентов для (a)
лист прогноза. В называется «ретроспективный анализ»). уделить пару секундвведите в поле к созданию прогрессии. прогноза, укажите дополнительные это делали, применяя
а вот общийзаносим ссылку на поле, а затем, не превышает 30% и значений функции. — неотъемлемая и валют, ежеквартальные, годовые -20 и -35) стоимость 1 л и (b). результате добавит таблицуСоветы: и сообщить, помоглаПериод
Автоматическое заполнение ряда на основе геометрической прогрессии
На вкладке начальные значения. функцию итог нам предстоит ячейку, где находится зажав левую кнопку от анализируемой базы Для этого выделяем
очень важная часть
объемы продаж, производства
бензина в текущем
Рассчитаем для каждого периода
статистики, созданной с
число периодов, используемыхГлавная
Перетащите маркер заполнения вЛИНЕЙН подсчитать отдельно, прибавив
номер года, на мыши и выделив периодов. То есть,
табличную область, а любого современного бизнеса. и т.д. Типичный0;B2:B11;0);ЕСЛИ(B2:B11>0;A2:A11;0))’ > месяце не превысит у-значение линейного тренда. помощью ПРОГНОЗА. ETS.Запуск прогноза до последней с помощью кнопок для расчета скользящего
в группе нужном направлении, чтобы. Щелкаем по кнопке к последнему фактическому который нужно указать соответствующий столбец на при анализе периода
затем, находясь во Само-собой, это отдельная временной ряд вC помощью функций ЕСЛИ 41,5 рубля. Спрогнозировать Для этого в СТАТИСТИКА функциями, а точке статистических дает внизу страницы. Для среднего.Правка заполнить ячейки возрастающими«OK» значению прибыли результат
Ручное прогнозирование линейной или экспоненциальной зависимости
прогноз. В нашем листе. в 12 лет вкладке весьма сложная наука метеорологии, например, ежемесячный выполняется перебор элементов
стоимость бензина на известное уравнение подставим также меры, например представление точности прогноза
удобства также приводимПримечания:нажмите кнопку или убывающими значениями.
. вычисления оператора случае это 2019Аналогичным образом в поле мы не можем«Вставка» с кучей методов объем осадков.
диапазона B2:B11 и оставшиеся дни месяца,
рассчитанные коэффициенты (х сглаживания коэффициенты (альфа, как можно сравнивать
ссылку на оригинал ЗаполнитьНапример, если ячейки C1:E1Результат экспоненциального тренда подсчитанЛИНЕЙН год. Поле«Известные значения x» составить эффективный прогноз, кликаем по значку и подходов, но
Если фиксировать значения какого-то отброс отрицательных чисел. сравнить рассчитанное среднее – номер периода). бета-версии, гамма) и прогнозируемое ряд фактические (на английском языке).В полеи выберите пункт
содержат начальные значения и выведен в
, умноженный на количество«Константа»вносим адрес столбца более чем на нужного вида диаграммы,
часто для грубой процесса через определенные Так, получаем прогнозные значение с предсказаннымЧтобы определить коэффициенты сезонности,
метрик ошибки (MASE, данные. Тем неЕсли у вас естьПостроен на рядеПрогрессия
обозначенную ячейку.
оставляем пустым. Щелкаем«Год» 3-4 года. Но
который находится в
повседневной оценки ситуации промежутки времени, то данные на основании специалистами. сначала найдем отклонение
SMAPE, обеспечения, RMSE). менее при запуске статистические данные сперечислены все ряды. 8, то приСтавим знак
Производим выделение ячейки, в по кнопкес данными за даже в этом блоке
достаточно простых техник. получатся элементы временного значений в строкахВид исходной таблицы данных: фактических данных отПри использовании формулы для прогноз слишком рано, зависимостью от времени, данных диаграммы, поддерживающих
Вычисление трендов с помощью добавления линии тренда на диаграмму
Выполните одно из указанных протаскивании вправо значения«=» которой будет производиться«OK» прошедший период. случае он будет«Диаграммы» Одна из них ряда. Их изменчивость с номерами 2,3,5,6,8-10.Чтобы определить предполагаемую стоимость значений тренда («продажи создания прогноза возвращаются созданный прогноз не вы можете создать линии тренда. Для ниже действий.
будут возрастать, влево —в пустую ячейку. вычисление и запускаем.После того, как вся относительно достоверным, если. Затем выбираем подходящий
пытаются разделить на Для детального анализа бензина на оставшиеся за год» /
таблица со статистическими обязательно прогноз, что прогноз на их добавления линии трендаЕсли необходимо заполнить значениями убывать. Открываем скобки и Мастер функций. ВыделяемОператор обрабатывает данные и информация внесена, жмем
за это время для конкретной ситуацииПРЕДСКАЗ (FORECAST) закономерную и случайную формулы выберите инструмент дни используем следующую «линейный тренд»). и предсказанными данными вам будет использовать
основе. При этом к другим рядам ряда часть столбца,
Совет: выделяем ячейку, которая наименование выводит результат на на кнопку не будет никаких
тип. Лучше всего, которая умеет считать составляющие. Закономерные изменения «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу». функцию (как формулуРассчитаем средние продажи за и диаграмма. Прогноз
статистических данных. Использование в Excel создается
выберите нужное имя выберите вариант Чтобы управлять созданием ряда содержит значение выручки«ЛИНЕЙН» экран. Как видим,«OK» форс-мажоров или наоборот выбрать точечную диаграмму. прогноз по линейному членов ряда, как
Один из этапов массива): год. С помощью предсказывает будущие значения всех статистических данных новый лист с в поле, апо столбцам вручную или заполнять за последний фактическийв категории
Прогнозирование значений с помощью функции
сумма прогнозируемой прибыли. чрезвычайно благоприятных обстоятельств, Можно выбрать и тренду. правило, предсказуемы. вычислений формулы:Описание аргументов: формулы СРЗНАЧ. на основе имеющихся дает более точные таблицей, содержащей статистические затем выберите нужные. ряд значений с период. Ставим знак«Статистические»
на 2019 год,Оператор производит расчет на которых не было другой вид, ноПринцип работы этой функцииСделаем анализ временных рядовПолученные результаты:A26:A33 – диапазон ячеекОпределим индекс сезонности для данных, зависящих от прогноза. и предсказанные значения, параметры.Если необходимо заполнить значениями помощью клавиатуры, воспользуйтесь«*»и жмем на рассчитанная методом линейной основании введенных данных в предыдущих периодах. тогда, чтобы данные несложен: мы предполагаем, в Excel. Пример:Функция имеет следующую синтаксическую
с номерами дней каждого месяца (отношение времени, и алгоритмаЕсли в ваших данных и диаграммой, наЕсли к двумерной диаграмме ряда часть строки, командойи выделяем ячейку, кнопку зависимости, составит, как и выводит результатУрок:
отображались корректно, придется что исходные данные торговая сеть анализирует
Быстрое прогнозирование в Microsoft Excel
Прогнозирование — хоть и неблагодарное, но необходимое дело и для решения таких задач в 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) .
В открывшемся окне зададим следующие настройки:
- Дату завершения прогноза
- Сезонность — почти никогда корректно не определяется автоматически, к сожалению, так что лучше задать её вручную. В большинстве бизнесов она годовая (т.е. «узор» колебаний похожим образом повторяется из года в год), так что установим её равной 12 месяцам.
- Вероятность, с которой мы требуем попадания будущих фактических значений в коридор доверительного интервала. Чем больше эта вероятность, тем шире интервал (т.е. более размыт прогноз). Обычно используют значения 90-95%.
- В правом нижнем углу окна можно дополнительно выбрать реакцию на пустые ячейки (их можно заполнить нулями или средним соседних значений — интерполяцией) и на дубликаты (обычно их усредняют). Однако же, по возможности, лучше заранее подготовить исходные исторические данные, чтобы таких пробелов или дублей в них не было.
После нажатия на кнопку Создать будет сформирован новый лист с прогнозной таблицей и диаграммой, которая по ней построена:
В верхней части таблицы будут идти строки с историческими данными (синяя линия), а в момент их окончания произойдет переключение на три новых столбца с прогнозом функцией ПРЕДСКАЗ.ETS и верхней и нижней границами доверительного интервала, вычисленного с помощью функции ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ.
Инструменты прогнозирования в Microsoft Excel

Прогнозирование – это очень важный элемент практически любой сферы деятельности, начиная от экономики и заканчивая инженерией. Существует большое количество программного обеспечения, специализирующегося именно на этом направлении. К сожалению, далеко не все пользователи знают, что обычный табличный процессор Excel имеет в своем арсенале инструменты для выполнения прогнозирования, которые по своей эффективности мало чем уступают профессиональным программам. Давайте выясним, что это за инструменты, и как сделать прогноз на практике.
Процедура прогнозирования
Целью любого прогнозирования является выявление текущей тенденции, и определение предполагаемого результата в отношении изучаемого объекта на определенный момент времени в будущем.
Способ 1: линия тренда
Одним из самых популярных видов графического прогнозирования в Экселе является экстраполяция выполненная построением линии тренда.
Попробуем предсказать сумму прибыли предприятия через 3 года на основе данных по этому показателю за предыдущие 12 лет.
- Строим график зависимости на основе табличных данных, состоящих из аргументов и значений функции. Для этого выделяем табличную область, а затем, находясь во вкладке «Вставка», кликаем по значку нужного вида диаграммы, который находится в блоке «Диаграммы». Затем выбираем подходящий для конкретной ситуации тип. Лучше всего выбрать точечную диаграмму. Можно выбрать и другой вид, но тогда, чтобы данные отображались корректно, придется выполнить редактирование, в частности убрать линию аргумента и выбрать другую шкалу горизонтальной оси.

- Теперь нам нужно построить линию тренда. Делаем щелчок правой кнопкой мыши по любой из точек диаграммы. В активировавшемся контекстном меню останавливаем выбор на пункте «Добавить линию тренда».

- Линейная;
- Логарифмическая;
- Экспоненциальная;
- Степенная;
- Полиномиальная;
- Линейная фильтрация.
Давайте для начала выберем линейную аппроксимацию.
Способ 2: оператор ПРЕДСКАЗ
Экстраполяцию для табличных данных можно произвести через стандартную функцию Эксель ПРЕДСКАЗ. Этот аргумент относится к категории статистических инструментов и имеет следующий синтаксис:
«X» – это аргумент, значение функции для которого нужно определить. В нашем случае в качестве аргумента будет выступать год, на который следует произвести прогнозирование.
«Известные значения y» — база известных значений функции. В нашем случае в её роли выступает величина прибыли за предыдущие периоды.
«Известные значения x» — это аргументы, которым соответствуют известные значения функции. В их роли у нас выступает нумерация годов, за которые была собрана информация о прибыли предыдущих лет.
Естественно, что в качестве аргумента не обязательно должен выступать временной отрезок. Например, им может являться температура, а значением функции может выступать уровень расширения воды при нагревании.
При вычислении данным способом используется метод линейной регрессии.
Давайте разберем нюансы применения оператора ПРЕДСКАЗ на конкретном примере. Возьмем всю ту же таблицу. Нам нужно будет узнать прогноз прибыли на 2018 год.
- Выделяем незаполненную ячейку на листе, куда планируется выводить результат обработки. Жмем на кнопку «Вставить функцию».

- Открывается Мастер функций. В категории «Статистические» выделяем наименование «ПРЕДСКАЗ», а затем щелкаем по кнопке «OK».

- Запускается окно аргументов. В поле «X» указываем величину аргумента, к которому нужно отыскать значение функции. В нашем случаем это 2018 год. Поэтому вносим запись «2018». Но лучше указать этот показатель в ячейке на листе, а в поле «X» просто дать ссылку на него. Это позволит в будущем автоматизировать вычисления и при надобности легко изменять год.
В поле «Известные значения y» указываем координаты столбца «Прибыль предприятия». Это можно сделать, установив курсор в поле, а затем, зажав левую кнопку мыши и выделив соответствующий столбец на листе.
Аналогичным образом в поле «Известные значения x» вносим адрес столбца «Год» с данными за прошедший период.
Но не стоит забывать, что, как и при построении линии тренда, отрезок времени до прогнозируемого периода не должен превышать 30% от всего срока, за который накапливалась база данных.
Способ 3: оператор ТЕНДЕНЦИЯ
Для прогнозирования можно использовать ещё одну функцию – ТЕНДЕНЦИЯ. Она также относится к категории статистических операторов. Её синтаксис во многом напоминает синтаксис инструмента ПРЕДСКАЗ и выглядит следующим образом:
=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])
Как видим, аргументы «Известные значения y» и «Известные значения x» полностью соответствуют аналогичным элементам оператора ПРЕДСКАЗ, а аргумент «Новые значения x» соответствует аргументу «X» предыдущего инструмента. Кроме того, у ТЕНДЕНЦИЯ имеется дополнительный аргумент «Константа», но он не является обязательным и используется только при наличии постоянных факторов.
Данный оператор наиболее эффективно используется при наличии линейной зависимости функции.
Посмотрим, как этот инструмент будет работать все с тем же массивом данных. Чтобы сравнить полученные результаты, точкой прогнозирования определим 2019 год.
- Производим обозначение ячейки для вывода результата и запускаем Мастер функций обычным способом. В категории «Статистические» находим и выделяем наименование «ТЕНДЕНЦИЯ». Жмем на кнопку «OK».

- Открывается окно аргументов оператора ТЕНДЕНЦИЯ. В поле «Известные значения y» уже описанным выше способом заносим координаты колонки «Прибыль предприятия». В поле «Известные значения x» вводим адрес столбца «Год». В поле «Новые значения x» заносим ссылку на ячейку, где находится номер года, на который нужно указать прогноз. В нашем случае это 2019 год. Поле «Константа» оставляем пустым. Щелкаем по кнопке «OK».

- Оператор обрабатывает данные и выводит результат на экран. Как видим, сумма прогнозируемой прибыли на 2019 год, рассчитанная методом линейной зависимости, составит, как и при предыдущем методе расчета, 4637,8 тыс. рублей.
Способ 4: оператор РОСТ
Ещё одной функцией, с помощью которой можно производить прогнозирование в Экселе, является оператор РОСТ. Он тоже относится к статистической группе инструментов, но, в отличие от предыдущих, при расчете применяет не метод линейной зависимости, а экспоненциальной. Синтаксис этого инструмента выглядит таким образом:
=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])
Как видим, аргументы у данной функции в точности повторяют аргументы оператора ТЕНДЕНЦИЯ, так что второй раз на их описании останавливаться не будем, а сразу перейдем к применению этого инструмента на практике.
- Выделяем ячейку вывода результата и уже привычным путем вызываем Мастер функций. В списке статистических операторов ищем пункт «РОСТ», выделяем его и щелкаем по кнопке «OK».

- Происходит активация окна аргументов указанной выше функции. Вводим в поля этого окна данные полностью аналогично тому, как мы их вводили в окне аргументов оператора ТЕНДЕНЦИЯ. После того, как информация внесена, жмем на кнопку «OK».

- Результат обработки данных выводится на монитор в указанной ранее ячейке. Как видим, на этот раз результат составляет 4682,1 тыс. рублей. Отличия от результатов обработки данных оператором ТЕНДЕНЦИЯ незначительны, но они имеются. Это связано с тем, что данные инструменты применяют разные методы расчета: метод линейной зависимости и метод экспоненциальной зависимости.
Способ 5: оператор ЛИНЕЙН
Оператор ЛИНЕЙН при вычислении использует метод линейного приближения. Его не стоит путать с методом линейной зависимости, используемым инструментом ТЕНДЕНЦИЯ. Его синтаксис имеет такой вид:
=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])
Последние два аргумента являются необязательными. С первыми же двумя мы знакомы по предыдущим способам. Но вы, наверное, заметили, что в этой функции отсутствует аргумент, указывающий на новые значения. Дело в том, что данный инструмент определяет только изменение величины выручки за единицу периода, который в нашем случае равен одному году, а вот общий итог нам предстоит подсчитать отдельно, прибавив к последнему фактическому значению прибыли результат вычисления оператора ЛИНЕЙН, умноженный на количество лет.
- Производим выделение ячейки, в которой будет производиться вычисление и запускаем Мастер функций. Выделяем наименование «ЛИНЕЙН» в категории «Статистические» и жмем на кнопку «OK».

- В поле «Известные значения y», открывшегося окна аргументов, вводим координаты столбца «Прибыль предприятия». В поле «Известные значения x» вносим адрес колонки «Год». Остальные поля оставляем пустыми. Затем жмем на кнопку «OK».

- Программа рассчитывает и выводит в выбранную ячейку значение линейного тренда.

- Теперь нам предстоит выяснить величину прогнозируемой прибыли на 2019 год. Устанавливаем знак «=» в любую пустую ячейку на листе. Кликаем по ячейке, в которой содержится фактическая величина прибыли за последний изучаемый год (2016 г.). Ставим знак «+». Далее кликаем по ячейке, в которой содержится рассчитанный ранее линейный тренд. Ставим знак «*». Так как между последним годом изучаемого периода (2016 г.) и годом на который нужно сделать прогноз (2019 г.) лежит срок в три года, то устанавливаем в ячейке число «3». Чтобы произвести расчет кликаем по кнопке Enter.
Как видим, прогнозируемая величина прибыли, рассчитанная методом линейного приближения, в 2019 году составит 4614,9 тыс. рублей.
Способ 6: оператор ЛГРФПРИБЛ
Последний инструмент, который мы рассмотрим, будет ЛГРФПРИБЛ. Этот оператор производит расчеты на основе метода экспоненциального приближения. Его синтаксис имеет следующую структуру:
= ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])
Как видим, все аргументы полностью повторяют соответствующие элементы предыдущей функции. Алгоритм расчета прогноза немного изменится. Функция рассчитает экспоненциальный тренд, который покажет, во сколько раз поменяется сумма выручки за один период, то есть, за год. Нам нужно будет найти разницу в прибыли между последним фактическим периодом и первым плановым, умножить её на число плановых периодов (3) и прибавить к результату сумму последнего фактического периода.
- В списке операторов Мастера функций выделяем наименование «ЛГРФПРИБЛ». Делаем щелчок по кнопке «OK».

- Запускается окно аргументов. В нем вносим данные точно так, как это делали, применяя функцию ЛИНЕЙН. Щелкаем по кнопке «OK».

- Результат экспоненциального тренда подсчитан и выведен в обозначенную ячейку.

- Ставим знак «=» в пустую ячейку. Открываем скобки и выделяем ячейку, которая содержит значение выручки за последний фактический период. Ставим знак «*» и выделяем ячейку, содержащую экспоненциальный тренд. Ставим знак минус и снова кликаем по элементу, в котором находится величина выручки за последний период. Закрываем скобку и вбиваем символы «*3+» без кавычек. Снова кликаем по той же ячейке, которую выделяли в последний раз. Для проведения расчета жмем на кнопку Enter.
Прогнозируемая сумма прибыли в 2019 году, которая была рассчитана методом экспоненциального приближения, составит 4639,2 тыс. рублей, что опять не сильно отличается от результатов, полученных при вычислении предыдущими способами.
Мы выяснили, какими способами можно произвести прогнозирование в программе Эксель. Графическим путем это можно сделать через применение линии тренда, а аналитическим – используя целый ряд встроенных статистических функций. В результате обработки идентичных данных этими операторами может получиться разный итог. Но это не удивительно, так как все они используют разные методы расчета. Если колебание небольшое, то все эти варианты, применимые к конкретному случаю, можно считать относительно достоверными.