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

Создание прогноза в 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 или попросить помощи в сообществе Answers community.

Прогнозирование тенденций в данных

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

Ниже приведены инструкции по отображению и распознаванию трендов, а также по составлению прогноза.

Прогнозирование трендов на базе имеющихся данных

Создание линейного приближения

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

Выделите не менее двух ячеек, содержащих начальные значения для тренда.

Чтобы повысить точность значений последовательности, укажите дополнительные начальные значения.

Перетащите маркер заполнения в сторону увеличения или уменьшения значений.

Например, если вы выбрали ячейки C1:E1, содержащие начальные значения 3, 5 и 8, то при перетаскивании маркера заполнения вправо значения будут возрастать, а влево — убывать.

Совет: Чтобы вручную настроить создаваемые последовательности, в меню Правка выберите пункт Заполнить и команду Ряд.

Создание экспоненциального приближения

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

Выделите не менее двух ячеек, содержащих начальные значения для тренда.

Чтобы повысить точность значений последовательности, укажите дополнительные начальные значения.

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

Например, если вы выбрали ячейки C1:E1, содержащие начальные значения 3, 5 и 8, то при перетаскивании маркера заполнения вправо значения будут возрастать, а влево — убывать.

Отпустите клавишу CONTROL и кнопку мыши, а затем в контекстном меню выберите команду Экспоненциальное приближение.

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

Совет: Чтобы вручную настроить создаваемые последовательности, в меню Правка выберите пункт Заполнить и команду Ряд.

Отображение ряда на диаграмме с помощью линии тренда

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

На диаграмме выберите ряд данных, для которого требуется добавить линию тренда или скользящее среднее.

На вкладке Конструктор нажмите кнопку Добавить элемент диаграммы и выберите пункт Линия тренда.

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

Выберите нужный тип линии тренда или скользящего среднего.

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

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

На диаграмме выберите ряд данных, для которого требуется добавить линию тренда или скользящее среднее.

В меню Диаграмма выберите команду Добавить линию тренда, а затем — пункт Тип.

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

Выберите нужный тип линии тренда или скользящего среднего.

Выполните одно из указанных ниже действий.

В поле Степень укажите наибольшую степень для независимой переменной.

В поле Период укажите число периодов, которые нужно использовать для расчета скользящего среднего.

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

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

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

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

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

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

Одним из самых популярных видов графического прогнозирования в Экселе является экстраполяция выполненная построением линии тренда.

Попробуем предсказать сумму прибыли предприятия через 3 года на основе данных по этому показателю за предыдущие 12 лет.

Давайте для начала выберем линейную аппроксимацию.

Нужно заметить, что эффективным прогноз с помощью экстраполяции через линию тренда может быть, если период прогнозирования не превышает 30% от анализируемой базы периодов. То есть, при анализе периода в 12 лет мы не можем составить эффективный прогноз более чем на 3-4 года. Но даже в этом случае он будет относительно достоверным, если за это время не будет никаких форс-мажоров или наоборот чрезвычайно благоприятных обстоятельств, которых не было в предыдущих периодах.

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

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

«X» – это аргумент, значение функции для которого нужно определить. В нашем случае в качестве аргумента будет выступать год, на который следует произвести прогнозирование.

«Известные значения y» — база известных значений функции. В нашем случае в её роли выступает величина прибыли за предыдущие периоды.

«Известные значения x» — это аргументы, которым соответствуют известные значения функции. В их роли у нас выступает нумерация годов, за которые была собрана информация о прибыли предыдущих лет.

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

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

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

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

Аналогичным образом в поле «Известные значения x» вносим адрес столбца «Год» с данными за прошедший период.

Но не стоит забывать, что, как и при построении линии тренда, отрезок времени до прогнозируемого периода не должен превышать 30% от всего срока, за который накапливалась база данных.

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

Для прогнозирования можно использовать ещё одну функцию – ТЕНДЕНЦИЯ. Она также относится к категории статистических операторов. Её синтаксис во многом напоминает синтаксис инструмента ПРЕДСКАЗ и выглядит следующим образом:

=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Как видим, аргументы «Известные значения y» и «Известные значения x» полностью соответствуют аналогичным элементам оператора ПРЕДСКАЗ, а аргумент «Новые значения x» соответствует аргументу «X» предыдущего инструмента. Кроме того, у ТЕНДЕНЦИЯ имеется дополнительный аргумент «Константа», но он не является обязательным и используется только при наличии постоянных факторов.

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

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

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

Ещё одной функцией, с помощью которой можно производить прогнозирование в Экселе, является оператор РОСТ. Он тоже относится к статистической группе инструментов, но, в отличие от предыдущих, при расчете применяет не метод линейной зависимости, а экспоненциальной. Синтаксис этого инструмента выглядит таким образом:

=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

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

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

Оператор ЛИНЕЙН при вычислении использует метод линейного приближения. Его не стоит путать с методом линейной зависимости, используемым инструментом ТЕНДЕНЦИЯ. Его синтаксис имеет такой вид:

=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

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

Как видим, прогнозируемая величина прибыли, рассчитанная методом линейного приближения, в 2019 году составит 4614,9 тыс. рублей.

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

Последний инструмент, который мы рассмотрим, будет ЛГРФПРИБЛ. Этот оператор производит расчеты на основе метода экспоненциального приближения. Его синтаксис имеет следующую структуру:

= ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Как видим, все аргументы полностью повторяют соответствующие элементы предыдущей функции. Алгоритм расчета прогноза немного изменится. Функция рассчитает экспоненциальный тренд, который покажет, во сколько раз поменяется сумма выручки за один период, то есть, за год. Нам нужно будет найти разницу в прибыли между последним фактическим периодом и первым плановым, умножить её на число плановых периодов (3) и прибавить к результату сумму последнего фактического периода.

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

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

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

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

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

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

Расширенный линейный ряд

Чтобы заполнить ряд для линейного тренда, сделайте следующее:

Выделите не менее двух ячеек, содержащих начальные значения для тренда.

Чтобы повысить точность ряда трендов, выберите дополнительные начальные значения.

Перетащите его в нужном направлении.

Например, если в ячейках C1:E1 выбраны начальные значения 3, 5 и 8, перетащите его вправо, чтобы заполнить значениями тенденций, или перетащите его влево, чтобы заполнить значениями убывания.

Совет: Чтобы вручную управлять тем, как создается ряд, или заполнить ряд с помощью клавиатуры, выберите команду Ряд (вкладка Главная, группа Редактирование, кнопка Заполнить).

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

Расширенный ряд роста

Чтобы заполнить ряд для тенденции роста, сделайте следующее:

Выделите не менее двух ячеек, содержащих начальные значения для тренда.

Чтобы повысить точность ряда трендов, выберите дополнительные начальные значения.

Удерживая нажатой правую кнопку мыши, перетащите указатель заполнения в нужном направлении, отпустите кнопку мыши, а затем на ленте нажмите кнопку контекстное меню.

Например, если в ячейках C1:E1 выбраны начальные значения 3, 5 и 8, перетащите его вправо, чтобы заполнить значениями тенденций, или перетащите его влево, чтобы заполнить значениями убывания.

Совет: Чтобы вручную управлять тем, как создается ряд, или заполнить ряд с помощью клавиатуры, выберите команду Ряд (вкладка Главная, группа Редактирование, кнопка Заполнить).

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

В линейном ряду начальные значения применяются к алгоритму наименьших квадратов (y=mx+b), который создает ряд.

В рядах роста начальные значения применяются к алгоритму экспоненциальной кривой (y=b*m^x), который создает ряд.

В обоих случаях шаг игнорируется. Созданный ряд эквивалентен значениям, возвращенным функцией ТЕНДЕНЦИЯ или функцией РОСТ.

Чтобы заполнить значения вручную, сделайте следующее:

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

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

На вкладке Главная в группе Редактирование нажмите кнопку Заполнить и выберите пункт Прогрессия.

Выполните одно из указанных ниже действий.

Чтобы заполнить ряд вниз по worksheet, щелкните Столбцы.

Чтобы заполнить ряд по всему ряду, щелкните Строки.

В поле Шаг введите значение, на которое вы хотите увеличить ряд.

Значение шага добавляется к первому начальному значению, а затем к каждому последующему значению.

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

В области Типвыберите линейный или Рост.

В поле Остановить значение введите значение, на которое нужно остановить ряд.

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

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

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

Щелкните ряд данных, в который вы хотите добавить линия тренда или скользящее среднее.

На вкладке Макет в группе Анализ нажмите кнопку Линия тренда ивыберите нужный тип линии тренда или скользящего среднего.

Чтобы настроить параметры и отформатирование линии тренда или скользящего среднего, щелкните линию тренда правой кнопкой мыши и выберите в меню пункт Формат линии тренда.

Выберите нужные параметры линии тренда, линии и эффекты.

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

В поле На основе ряда перечислены все ряды данных на диаграмме, которые поддерживают линии тренда. Чтобы добавить линию тренда к другому ряду, щелкните имя в поле и выберите нужные параметры.

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

Важно: Начиная с Excel 2005 г., Excel способ вычисления значения R2 для линейных линий тренда на диаграммах, где для перехваченной линии тренда установлено значение нуля (0). Эта корректировка исправит вычисления, которые дают неправильные значения R 2, и выровняет вычисление R2 с функцией LINEST. В результате на диаграммах, созданных в предыдущих версиях Excel, могут отображаться разные значения R2. Дополнительные сведения см. в таблице Изменения внутренних вычислений линейных линий тренда на диаграмме.

Если вам нужно выполнить более сложный регрессионный анализ, в том числе вычислить и отсчитывать остаточные данные, используйте средство регрессионного анализа в надстройке «Надстройка «Надстройка анализа». Дополнительные сведения см. в окне Загрузка средства анализа.

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

Вот как можно использовать его для создания линейного тренда чисел в Excel в Интернете:

Выделите не менее двух ячеек, содержащих начальные значения для тренда.

Чтобы повысить точность ряда трендов, выберите дополнительные начальные значения.

Перетащите его в нужном направлении.

Использование функции ПРОГНОЗ Функция ПРЕДСПРОС вычисляет или предсказывает будущее значение с использованием существующих значений. Предсказываемое значение — это значение y, соответствующее заданному значению x. Значения x и y известны; новое значение предсказывается с использованием линейной регрессии. Эта функция используется для предсказания будущих продаж, требований к запасам и потребительских тенденций.

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

Использование функции ЛИНИИСТОЛ или ФУНКЦИИ ЛОГЕСТ Функцию ЛИННЕФ или LOGEST можно использовать для вычисления прямой или экспоненциальной кривой из существующих данных. Функции LINEST и LOGEST возвращают различные статистические данные о регрессии, включая наклон и отступ линии, которая лучше всего подходит.

В следующей таблице содержатся ссылки на дополнительные сведения об этих функциях.

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

Project, которые соответствуют экспоненциальной кривой

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

Расчет экспоненциальной кривой из существующих данных

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

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

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

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

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

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

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

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

Пример 1.» src=»https://exceltable.com/funkcii-excel/images/funkcii-excel145-2.png» >

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

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

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

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

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

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

Пример 2.» src=»https://exceltable.com/funkcii-excel/images/funkcii-excel145-6.png» >

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

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

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

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

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

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

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

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

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

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

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

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

Методы прогнозирования в Excel

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

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

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

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

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

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

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

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

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

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

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

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

Виды моделей

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

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

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

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

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

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

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

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

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

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

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

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

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

На выходе мы получили 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 степени необходимо найти коэффициенты уравнения:

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

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

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

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

Заключение

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

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

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

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

Прогноз в excel

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

​Смотрите также​ расчеты базировались на​ меняются на средние​ и подходов, но​ и (b).​ столбцы отображаются только​ их выявить, прогноз​ таблице.​ тенденции.​«*3+»​ прогноз (2019 г.)​ инструментом​ сумма прогнозируемой прибыли​ ещё одну функцию​выделяем наименование​ при анализе периода​Линейная​Прогнозирование – это очень​ данных предыдущих наблюдений.​ арифметические значения в​ часто для грубой​Рассчитаем для каждого периода​

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

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

​Параметры прогноза​Сведения о том, как​без кавычек. Снова​ лежит срок в​ТЕНДЕНЦИЯ​ на 2019 год,​ –​

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

​«ПРЕДСКАЗ»​ в 12 лет​;​ важный элемент практически​Рассчитаем абсолютные, относительные и​

​ определенные интервалы. Выбор​ повседневной оценки ситуации​ у-значение линейного тренда.​ если установлен флажок​ тренда.​Описание​

​Последние два аргумента являются​ методе расчета, 4637,8​

​ операторов. Её синтаксис​​.​​ 3-4 года. Но​​;​​ Существует большое количество​​Относительные отклонения:​​ – включаются. В​ПРЕДСКАЗ (FORECAST)​ – номер периода).​окна. ​ шкалы. Этот диапазон​ выборе даты до​​На листе введите два​​ расчета жмем на​​. Чтобы произвести расчет​ необязательными. С первыми​​ тыс. рублей.​ во многом напоминает​Запускается окно аргументов. В​ даже в этом​Степенная​​ программного обеспечения, специализирующегося​​Средние квадратичные отклонения:​

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

​ образом:​​ к которому нужно​ не будет никаких​

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

​Линейная фильтрация​ пользователи знают, что​ того, чтобы провести​​ параметра.​​ несложен: мы предполагаем,​ за год» /​ ETS​Здесь можно изменить диапазон,​

​ называется «ретроспективный анализ»).​

​ шкалы;​​ 2019 году, которая​ прибыли, рассчитанная методом​ в этой функции​ РОСТ. Он тоже​=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])​ отыскать значение функции.​ форс-мажоров или наоборот​.​

​ обычный табличный процессор​​ сравнительный анализ погрешностей.​Временной ряд – это​ что исходные данные​ «линейный тренд»).​Функции прогнозирования​ используемый для рядов​

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

​ Excel имеет в​После сопоставления таблиц с​ множество значений X​ можно интерполировать (сгладить)​Рассчитаем средние продажи за​Прогнозирование продаж в Excel​ значений. Этот диапазон​ ​Эти значения будут предсказаны​

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

​ группе инструментов, но,​«Известные значения y»​​ это 2018 год.​​ которых не было​ линейную аппроксимацию.​ своем арсенале инструменты​ отклонениями стало видно,​ и Y, связанных​ некой прямой с​

​ представление точности прогноза​​Примечание:​​ сильно отличается от​​ рассмотрим, будет​​ определяет только изменение​ применяет не метод​полностью соответствуют аналогичным​. Но лучше указать​Как построить линию тренда​в поле​ эффективности мало чем​

​ скользящей средней в​​ постоянная переменная. Y​​Построив эту прямую и​​ каждого месяца (отношение​​В данном примере будем​.​

​ как можно сравнивать​ Для временной шкалы требуются​ результатов, полученных при​​ЛГРФПРИБЛ​​ величины выручки за​

​ изменения выручки предприятия​ явления (цена, например,​ за пределы известного​ средней величине). Фактически​ для составления прогноза​ помощью​ данные. Тем не​ точками данных. Например,​Урок:​

​ расчеты на основе​​ в нашем случае​

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

​ это могут быть​​Другие статистические функции в​​ метода экспоненциального приближения.​​ равен одному году,​​ образом:​«Новые значения x»​​«X»​​ стандартную функцию Эксель​​, так как нам​​ и как сделать​​ скользящего среднего. У​​ период времени), зависимая​ получим искомый прогноз.​​ продаж за месяц​​ бушующие периоды с​​ Excel использует интерполяцию.​​ прогноз слишком рано,​ месячные интервалы со​ Excel​ Его синтаксис имеет​

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

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

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

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

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

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

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

​ значение функции для​

​ достоверности аппроксимации (R^2)»​ определенный момент времени​Для примера возьмем ту​ значений четко прослеживается​ так, чтобы сумма​ На 3 месяца​ позволяет не только​ в списке пункт​ прослеживаются сезонные тенденции,​ с одной и​ встроенных статистических функций.​ рассчитает экспоненциальный тренд,​Производим выделение ячейки, в​ будем, а сразу​ используется только при​«Прибыль предприятия»​ которого нужно определить.​. Последний показатель отображает​ в будущем.​ же задачу.​ тенденция в динамике.​ квадратов отклонений исходных​​ вперед. Продлеваем номера​​ обрабатывать статистические данные,​Нули​

​ тренда наилучшим образом​Рассчитаем значения тренда для​ чтобы оценить некоторые​ значений с одной​Доверительный интервал​

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

​ для повышения точности​ это не удивительно,​​ есть, за год.​​«ЛИНЕЙН»​ и уже привычным​Посмотрим, как этот инструмент​ мыши и выделив​ произвести прогнозирование.​

​«Закрыть»​ линии тренда.​

​ среднее»:​ (оптимальное число m​ сглаживала фактические данные.​ будущих периодов: изменим​ возможности Excel в​ меткой времени, Excel​Установите или снимите флажок​ прогноза желательно перед​ так как все​ Нам нужно будет​в категории​ путем вызываем​ будет работать все​ соответствующий столбец на​«Известные значения y»​.​Попробуем предсказать сумму прибыли​Заполняем. Входной интервал –​​ членов скользящего среднего).​​Excel позволяет легко построить​ в уравнении линейной​ области прогнозирования продаж,​

​ параметров за m​ правой по ряду​ просто скопировать формулу​ с учетом роста​Медиана​ интервал — диапазон​Совет:​ все эти варианты,​ первым плановым, умножить​

​«OK»​​«РОСТ»​ точкой прогнозирования определим​

​Задача. Проанализировать выручку предприятия​

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

​ тренда (Add Trendline),​​ J2, J3, J4.​ продажи за 12​ списке.​ значения, в котором​ одном из рядов,​ случаю, можно считать​ плановых периодов​В поле​ щелкаем по кнопке​Производим обозначение ячейки для​с данными за​ предыдущие периоды.​ видим, к тому​Строим график зависимости на​ сначала будем строить​ за 11 месяцев​ но часто для​На основе полученных данных​ месяцев предыдущего года​Включить статистические данные прогноза​

​ 95% точек будущих​ Excel автоматически выделит​ относительно достоверными.​(3)​«Известные значения y»​«OK»​ вывода результата и​ прошедший период.​«Известные значения x»​ времени она должна​ основе табличных данных,​ сглаженный временной ряд​ и составить прогноз​ расчетов нам нужна​ составляем прогноз по​ и построим прогноз​Установите этот флажок, если​ ожидается, находится в​ остальные данные.​

​Автор: Максим Тютюшев​и прибавить к​, открывшегося окна аргументов,​.​ запускаем​

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

​После того, как вся​— это аргументы,​ перевалить за 4500​

​ состоящих из аргументов​ по данным двух​ на 12 месяц.​

​ продажам на следующие​ на 3 месяца​ вы хотите дополнительные​

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

​обычным способом. В​

​ на кнопку​​ значения функции. В​R2​ Для этого выделяем​ поле вводим цифру​

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

​ оперативнее обеспечивать вас​​В списке операторов Мастера​​. В поле​ Вводим в поля​ категории​

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

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

​ функций выделяем наименование​«Известные значения x»​ этого окна данные​«Статистические»​.​ нас выступает нумерация​

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

​ сказано выше, отображает​ затем, находясь во​ – диапазон ячеек​​ Найдем средние отклонения​​ их и вычисляет​

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

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

​ сглаженных временных рядов​​ функция​

​ выглядит следующим образом:​ 1 период (y).​ статистики, созданной с​ интервал подразумевает более​Лист прогноза​ Эта страница переведена​. Делаем щелчок по​«Год»​ как мы их​ наименование​ основании введенных данных​ была собрана информация​ В нашем случае​«Вставка»​ результатов.​

​ от заданного временного​ПРЕДСКАЗ (FORECAST)​График прогноза продаж:​Уравнение линейного тренда:​ помощью ПРОГНОЗА. ETS.​ уверенно предсказанного для​

​ автоматически, поэтому ее​​ кнопке​​. Остальные поля оставляем​ вводили в окне​«ТЕНДЕНЦИЯ»​ и выводит результат​ о прибыли предыдущих​ величина​, кликаем по значку​Установив флажок в поле​ ряда.​.​График сезонности:​y = bx​ СТАТИСТИКА функциями, а​ определенный момент. Уровня​В диалоговом окне​ текст может содержать​«OK»​ пустыми. Затем жмем​ аргументов оператора​

​ на экран. На​ лет.​R2​ нужного вида диаграммы,​ «Стандартные погрешности», мы​По значениям исходного временного​Синтаксис функции следующий​​ + a​ также меры, например​​ достоверности 95% по​​Создание листа прогноза​ неточности и грамматические​

​.​​ на кнопку​ТЕНДЕНЦИЯ​«OK»​ 2018 год планируется​Естественно, что в качестве​составляет​ который находится в​ автоматически добавляем в​ ряда строим сглаженный​=ПРЕДСКАЗ(X; Известные_значения_Y; Известные_значения_X)​Алгоритм анализа временного ряда​y — объемы продаж;​ сглаживания коэффициенты (альфа,​ умолчанию могут быть​выберите график или​

​Запускается окно аргументов. В​«OK»​. После того, как​.​​ прибыль в районе​​ аргумента не обязательно​

​ блоке​ таблицу столбец со​ временный ряд методом​где​ для прогнозирования продаж​​x — номер периода;​​ бета-версии, гамма) и​

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

​ важно, чтобы эта​ нем вносим данные​.​ информация внесена, жмем​Открывается окно аргументов оператора​ 4564,7 тыс. рублей.​ должен выступать временной​. Чем выше коэффициент,​«Диаграммы»​ статистической оценкой погрешности.​ скользящего среднего по​​Х​​ в Excel можно​

​a — точка пересечения​

​ метрик ошибки (MASE,​ вверх или вниз.​ представления прогноза.​ статья была вам​ точно так, как​Программа рассчитывает и выводит​​ на кнопку​​ТЕНДЕНЦИЯ​ На основе полученной​

​ отрезок. Например, им​

​ тем выше достоверность​. Затем выбираем подходящий​Точно так же находим​ данным за 2​- точка во​ построить в три​ с осью y​ SMAPE, обеспечения, RMSE).​Сезонность​В поле​ полезна. Просим вас​ это делали, применяя​ в выбранную ячейку​«OK»​

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

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

​Сезонности — это число​Завершение прогноза​ уделить пару секунд​

​ функцию​ значение линейного тренда.​.​

​«Известные значения y»​ построить график при​ а значением функции​

​ его может быть​ тип. Лучше всего​ трем месяцам. Меняется​

​ скользящей средней в​ мы делаем прогноз​Выделяем трендовую составляющую, используя​ порог);​ создания прогноза возвращаются​ в течение (количество​выберите дату окончания,​​ и сообщить, помогла​​ЛИНЕЙН​​Теперь нам предстоит выяснить​​Результат обработки данных выводится​

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

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

См. также:

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

​ Excel. Используя маркер​Известные_значения_Y​ функцию регрессии.​b — увеличение последующих​

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

​ на монитор в​ способом заносим координаты​ диаграммы, о которых​ расширения воды при​

​1​ Можно выбрать и​ и выходной диапазон.​ автозаполнения, копируем формулу​- известные нам​Определяем сезонную составляющую в​ значений временного ряда.​ и предсказанными данными​ и определяется автоматически.​ кнопку​ с помощью кнопок​«OK»​

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

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

​ и диаграмма. Прогноз​

​ тогда, чтобы данные​ в том, что​ С6:С14.​ (прибыль)​

​ при индивидуализации модели​ Определим коэффициенты уравнения​

​ экспоненциального сглаживания (ETS)​

​ и предсказанные значения,​

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

​ статистические данные с​«=»​ в которой содержится​ оператором​. В поле​

​ периодов)​ прогнозирования. Ведь разные​ y = bx​ версии AAA.​Задание вручную​ и диаграммой, на​

​в пустую ячейку.​ фактическая величина прибыли​ТЕНДЕНЦИЯ​«Новые значения x»​

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

​ Например, по прогнозам​на конкретном примере.​ то можно вернуться​ оси.​ Прогнозное значение выручки​ четырехмесячного скользящего среднего.​Практическое моделирование экономических ситуаций​ временные ряды имеют​ + a. В​Таблицы могут содержать следующие​и затем выбрав​ которой они отражены.​ вы можете создать​ Открываем скобки и​ за последний изучаемый​​незначительны, но они​​заносим ссылку на​ в 2019 году​ Возьмем всю ту​

​ в окно формата​Теперь нам нужно построить​ на 12 месяц​Построим график заданного временного​ подразумевает разработку прогнозов.​ разные характеристики.​ ячейке D15 Используем​

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

​ год (2016 г.).​ имеются. Это связано​​ ячейку, где находится​​ сумма прибыли составит​ же таблицу. Нам​ линии тренда и​ линию тренда. Делаем​ – 9 430​ ряда и рассчитанные​ С помощью средств​бланк прогноза деятельности предприятия​ функцию ЛИНЕЙН:​ которых являются вычисляемыми:​Примечание:​ слева от листа,​

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

​ за последний фактический​

Расчет скользящей средней в Excel и прогнозирование

​ сезонность вручную, не​ ввели ряды данных​ новый лист с​ период. Ставим знак​. Далее кликаем по​ разные методы расчета:​ прогноз. В нашем​ что, как и​ 2018 год.​ перепробовать все доступные​

Использование скользящих средних в Excel

​ из точек диаграммы.​ в Excel​ методу. На рисунке​ прогнозирования, как: экспоненциальное​ описанного прогноза рекомендуем​ правую, ячейку E15​ содержащий значения времени);​ используйте значения, которые​ (то есть перед​ таблицей, содержащей статистические​«*»​ ячейке, в которой​ метод линейной зависимости​ случае это 2019​ при построении линии​Выделяем незаполненную ячейку на​ варианты, чтобы найти​ В активировавшемся контекстном​

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

​Умение строить прогнозы, предсказывая​ оставалась D15. Нажимаем​ данных, содержащий соответствующие​ статистических данных. При​Если вы хотите изменить​ и диаграммой, на​ содержащую экспоненциальный тренд.​ линейный тренд. Ставим​ зависимости.​«Константа»​

​ до прогнозируемого периода​ выводить результат обработки.​Нужно заметить, что эффективным​ на пункте​

​ и эффективно. Инструмент​ сдвинуты относительно линии​ подробнее использование метода​ (хотя бы примерно!)​ кнопку F2. Затем​ значения);​ таких значениях этого​

​при вычислении использует​

​ срока, за который​.​ тренда может быть,​Открывается окно форматирования линии​ периода. Но выйти​

​ что рассчитанные значения​ один из эмпирических​ очень важная часть​ ввести массив функций​ ПРЕДСКАЗ.ЕTS);​ сезонные компоненты. Если​.​ такие показатели, как​ котором находится величина​ последним годом изучаемого​ метод линейного приближения.​.​ накапливалась база данных.​

​Открывается​ если период прогнозирования​ тренда. В нем​

​ за пределы известных​

Применение надстройки «Пакет анализа»

​ сглаженных временных рядов​ методов для сглаживания​

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

​ будущий объем продаж,​ выручки за последний​ периода (2016 г.)​ Его не стоит​Оператор обрабатывает данные и​Урок:​Мастер функций​ не превышает 30%​ можно выбрать один​ данных нельзя. Поэтому​ запаздывают по сравнению​ и прогнозирования временных​ Само-собой, это отдельная​ Таким образом получаем​ интервал (вычисленных с​

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

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

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

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

​ экран. Как видим,​Для прогнозирования можно использовать​«Статистические»​ периодов. То есть,​ аппроксимации:​ применяются другие способы.​ заданного ряда. Ведь​ значения ряда динамики​ с кучей методов​ коефициентов для (a)​

Примеры функции ТЕНДЕНЦИЯ в Excel для прогнозирования данных

Функция ТЕНДЕНЦИЯ в Excel используется при расчетах последующих значений для рассматриваемого события и возвращает данные в соответствии с линейным трендом. Функция выполняет аппроксимацию (упрощение) прямой линией диапазона известных значений независимой и зависимой переменных с использованием метода наименьших квадратов и прогнозирует будущие значения зависимой переменной Y для указанных последующих значений независимой переменной X. Рассматриваемая функция не используется для получения статистической характеристики модели тренда и математического описания.

Линейным трендом называется распределение величин в изучаемой последовательности, которое может быть описано функцией типа y=ax+b. Поскольку функция ТЕНДЕНЦИЯ выполняет аппроксимацию прямой линией, точность результатов ее работы зависит от степени разброса значений в рассматриваемом диапазоне.

Примеры использования функции ТЕНДЕНЦИЯ в Excel

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

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

Для прогноза курса валют на 7-й месяц используем следующую функцию (обычная запись, Enter для вычислений):

В результате получим:

Прогноз посещаемости с помощью функции ТЕНДЕНЦИЯ в Excel

Пример 2. В кинотеатре фильмы показывают в различные сеансы, которые начинаются в 12:00, 16:00 и 21:00 соответственно. Каждый фильм имеет собственный рейтинг, в виде оценки от 1 до 10 баллов. Известны данные о посещаемости нескольких последних сеансов. Предположить, какой будет посещаемость для следующих фильмов:

Таблица исходных данных:

Для расчета используем функцию:

В результате получим:

Не забывайте, что ТЕНДЕНЦИЯ является массивной функцией поэтому после ее ввода не забудьте выполнить ее в массиве. Для этого жмем не просто Enter, а комбинацию клавиш Ctrl+Shift+Enter. Если в строке формул по краям функции появились фигурные скобки <>, значит функция выполняется в массиве и все сделано правильно.

Прогнозирование производства продукции на графике Excel

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

Для определения количества единиц продукции, которые будут выпущены на протяжении последующих 3-х месяцев используем функцию:

Построим график на основе имеющихся данных и отобразим линию тренда с уравнением:

Введем в ячейке C8 формулу =193,5*A8+2060,5. В результате получим:

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

Данный пример наглядно демонстрирует принцип работы функции ТЕНДЕНЦИЯ.

Функция ТЕНДЕНЦИЯ в Excel и особенности ее использования

Функция ТЕНДЕНЦИЯ используется наряду с прочими функциями прогноза в Excel (ПРЕДСКАЗ, РОСТ) и имеет следующий синтаксис:

= ТЕНДЕНЦИЯ(известные_значения_y; [известные_значения_x]; [новые_значения_x]; [конст])

Прогноз продаж в Excel

Прогноз_продаж.xls (59,5 KiB, 32 571 скачиваний)

Прогнозирование продаж является неотъемлемой частью при планировании работы коммерческих и финансовых служб, поэтому задача довольно актуальная. Вариантов построения прогнозов достаточное множество, но я хочу показать как сделать простой, но в то же время достаточно жизнеспособный прогноз «на скорую руку», без лишних телодвижений и поправок «на ветер»(читайте как: без кучи доп.расчетов, которые применяются для создания более точных прогнозов). Почему я это уточняю? Потому что на мой взгляд, каким бы точным ни был прогноз продаж – это всего лишь предположение и быть уверенным в том, что именно так и будет развиваться ход событий, никак нельзя.
И тем не менее при помощи встроенных в Excel функций мы можем построить довольно неплохой прогноз даже с учетом сезонности. Плюс я хочу показать как сделать не просто прогноз, а прогноз с отклонениями – пессимистичный и оптимистичный. С помощью подобной модели можно будет выстроить тактику продаж таким образом, чтобы постараться максимально «вписаться» в границы между пессимистичным и оптимистичным прогнозом.
А в довершение мы построим красивый график с прогнозом.

Исходные данные
Для расчета прогноза потребуются данные о продажах за ранние периоды. Чем больше данных, тем точнее будет прогноз. Желательно, чтобы были помесячные данные хотя бы за два года. На мой взгляд это тот минимум, на основании которого можно построить весьма точный прогноз с учетом прошлого опыта. Именно из таких данных и будем исходить. Предположим, что у нас есть данные с января 2013 года по август 2015, в табличном виде:

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

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

Прогноз_продаж.xls (59,5 KiB, 32 571 скачиваний)

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

Подробнее про принцип работы формулы: она берет отдельно сумму каждого месяца за 2013 и 2014 год, складывает их. Делит полученное значение на общую сумму продаж за весь период целых месяцев(т.е. 24 месяца) и умножает на 12, чтобы получить коэффициент именно за один месяц. И так для каждого месяца. Т.е. для ячейки F2 расчет будет выглядеть следующим образом:
=(( 56 769 + 68 521 )/ 1 542 293 )*12
=(( сумма за янв.2013 + сумма за янв.2014 )/ общая сумма за два года(янв.2013 – дек.2014) )*12

Основную задачу выполнили – у нас есть прогноз на будущие периоды. Теперь осталось в дополнение к самому прогнозу, создать допустимые верхние и нижние границы, которые часто еще называют оптимистичный прогноз и пессимистичный(но по сути это просто возможное отклонение от прогнозных данных). Такой прогноз даст нам возможность более гибко планировать тактику на будущие периоды.
Для того, чтобы построить такие прогнозы необходимо рассчитать допустимое отклонение от прогнозируемых значений. Здесь так же будем использовать имеющиеся в Excel функции. В ячейку G2 запишем формулу:
=ДОВЕРИТ(0,05; СТАНДОТКЛОН( C34:C45 ); СЧЁТ( C34:C45 ))
=CONFIDENCE(0.05,STDEV(C34:C45),COUNT(C34:C45))
ДОВЕРИТ (CONFIDENCE) – возвращает доверительный интервал, используя нормальное распределение.

Т.е. мы для оптимистичного прогноза берем сумму прогноза и прибавляем к ней сумму рассчитанного отклонения. А для пессимистичного, мы сумму отклонения вычитаем. Вот мы и получили все необходимые данные.

График
Но было бы кощунством с нашей стороны проделать такую работу и не использовать возможности Excel для построения красивого графика. Придется добавить немного шаманства(на деле, мы уже начали шаманить, когда стали записывать прогноз в отдельный столбец, а не продолжать его в том же столбце, что и фактические продажи). В ячейки C33, D33 и E33 скопируем значение из ячейки B33, чтобы они все имели одинаковые значения:

Наглядно и сразу понятно что к чему и чего можно ожидать.

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

Прогноз_продаж.xls (59,5 KiB, 32 571 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Прогноз в эксель

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

​Смотрите также​ четкая тенденция к​ у пусты (ошибка​=ПРЕДСКАЗ(X; Известные_значения_Y; Известные_значения_X)​ достаточно простых техник.​ в уравнении линейной​ функцию ЛИНЕЙН:​ по продажам на​ ее цвет, т.​Итак, в​ или кривую, описывающую​ параметры.​Выберите ряд данных, к​ шаг. Получившийся результат​

​ РОСТ.​ в нужном направлении​Перетащите маркер заполнения в​Когда необходимо оценить затраты​ росту будущих продаж​ #Н/Д).​где​​ Одна из них​​ функции значение х.​Выделяем ячейку с формулой​ бушующие периоды с​ д. Мы изменили​торой способ прогноза.​ существующие данные.​

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

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

​ следующего года или​

​ в течение следующих​

​Количество точек х не​

​ цвет линии графика​Прогноз тренда в​

​Использование функций ЛИНЕЙН и​ (диаграмме распределения) добавляется​ линия тренда или​

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

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

​- точка во​ПРЕДСКАЗ (FORECAST)​ просто скопировать формулу​ правую, ячейку E15​Линейный тренд хорошо подходит​ (сделали его синим),​Excel на графике.​

​ ЛГРФПРИБЛ​​ скользящее среднее, то​ скользящее среднее.​ шаг.​Выделите ячейку, в которой​ значениями, отпустите правую​​ или убывающими значениями.​​ серии научных экспериментов,​​Для расчета будущих продаж​​ точек у (ошибка​​ времени, для которой​​, которая умеет считать​​ из D2 в​​ так чтобы активной​

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

​Например, если ячейки C1:E1​

​ можно использовать Microsoft​

​ можно использовать уравнение,​

​ прогноз по линейному​

​ оставалась D15. Нажимаем​ по продажам для​

​Красной пунктирной линией на​ таблица с данными.​ ЛГРФПРИБЛ позволяют вычислить​

​ базируется на порядке​Макет​Тип​

​ создаваемой прогрессии.​ щелкните​ содержат начальные значения​ Office Excel для​ которое появилось на​Дисперсия аргумента «диапазон значений​Известные_значения_Y​ тренду.​​На основе полученных данных​​ кнопку F2. Затем​

​ развивающегося предприятия.​ графике показан прогноз​Для этого прогноза строим​ прямую линию или​ расположения значений X​в группе​выберите тип прогрессии:​

​Команда​​Экспоненциальное приближение​ 3, 5 и​ автоматической генерации будущих​ графике при добавлении​ x» равняется 0​​- известные нам​​Принцип работы этой функции​​ составляем прогноз по​​ Ctrl + Shift​​Excel – это лучший​​ роста прибыли до​​ график и вставляем​​ экспоненциальную кривую для​

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

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

​ линейного тренда. Его​ (ошибка #ДЕЛ/О!).​ значения зависимой переменной​ несложен: мы предполагаем,​

​ продажам на следующие​ + Enter (чтобы​ в мире универсальный​ конца года.​

​ в него линию​ имеющихся данных. Функции​ получения нужного результата​нажмите кнопку​или​удаляет из ячеек​Например, если ячейки C1:E1​ протаскивании вправо значения​

​ базироваться на существующих​ же мы используем​

​Уравнение для функции –​ (прибыль)​ что исходные данные​

​ 3 месяца (следующего​​ ввести массив функций​​ аналитический инструмент, который​А вот так​ тренда. Линия тренда​ ЛИНЕЙН и ЛГРФПРИБЛ​ перед добавлением скользящего​Линия тренда​геометрическая​ прежние данные, заменяя​ содержат начальные значения​

​ будут возрастать, влево —​​ данных или для​​ для проверки работы​​ a + bx,​​Известные_значения_X​​ можно интерполировать (сгладить)​​ года) с учетом​​ для обеих ячеек).​​ позволяет не только​

​ выглядит график, если​ – это линия​

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

​ 3, 5 и​ убывать.​ автоматического вычисления экстраполированных​​ функции ПРЕДСКАЗ, которая​​ где a =​

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

​ на графике, которая​

​ регрессионного анализа, включая​

​ отсортировать значения X.​ тип регрессионной линии​В поле​

​ необходимо сохранить прежние​

​ значения независимой переменной​​ классическим линейным уравнением​​Общая картина составленного прогноза​​ сразу 2 значения​​ но и составлять​​ прогноз прибыли в​​ построена по данным​

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

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

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

​ ряд значений с​В Microsoft Excel можно​ месяц) в уравнение.​ – средние значения​

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

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

​​ у-значение линейного тренда.​ возможности Excel в​

​ сделать прогноз в​​Как построить график,​​ на дополнительные сведения​​ значениям. Предсказываемое значение —​​ тренда или скользящего​Примечание:​

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

​ линейному или экспоненциальному​ месяца). Скопируем формулу​ и точек y).​ работы экономиста. В​ получим искомый прогноз.​ для прогнозирования продаж​ известное уравнение подставим​ разберем практический пример.​ статью выше.​ «Как сделать график​Функция​

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

​ заданному x-значению. Известные​​ тренда правой клавишей​ содержатся первые члены​Главная​ ряд значений с​(вкладка​ тренду, с помощью​ до конца второго​​ целях прогнозирования может​Для построения этой прямой​ в Excel можно​ рассчитанные коэффициенты (х​Рассчитаем прогноз по продажам​Для примера, уменьшим данные​ в Excel».​Описание​

​ значения — это существующие​ мыши и выберите​​ прогрессии и требуется,​в группе​ помощью клавиатуры, воспользуйтесь​​Главная​​ маркер заполнения или​ столбца.​Сначала возьмем для примера​ использоваться одна из​ Excel использует известный​​ построить в три​​ – номер периода).​​ с учетом роста​​ в ячейках B8​Мы сделали такой​ПРЕДСКАЗ​​ x- и y-значения;​​ пункт​​ чтобы приложение Microsoft​​Правка​ командой​, группа​ команды​

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

​ продаж воспользуемся функцией​

​Выделяем трендовую составляющую, используя​

​ сначала найдем отклонение​

​ с использованием линейной​

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

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

Прогноз тренда в Excel на графике.

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

​ «Работа с диаграммами»​Построение экспоненциального приближения.​Использование функций ТЕНДЕНЦИЯ и​Степень​

​ линия тренда. Например,​ ряда часть столбца,​С помощью команды​ ряде. Получившийся результат​ или различие между​ со значением 11​

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

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

​y = bx​ смотрите в статье​ нажимаем на кнопку​ включая вычисление и​

​ или экспоненциальную кривую,​число периодов, используемых​

​Для получения линейного тренда​

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

​4, 8, 16​1, 2​ позволяют изменяться, когда​ «Добавить линию тренда».​ y.​

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

​3, 4, 5. ​ мы протягиваем формулу​ В появившемся окне​Диапазон значений y. Известные​

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

​В ячейке H2 найдем​ порог);​ другую ячейку. Как​ – линейный, подходит​ статистического анализа.​ функции могут возвращать​Примечания:​

​ продаж (рост, снижение​Шаг​ квадратов (y=mx+b).​2, 3​5, 7, 9​

​Таким образом, для прогнозирования​ пунктов «Показывать уравнение»​​ которых будет проводиться​​ но часто для​ (хотя бы примерно!)​ общий индекс сезонности​b — увеличение последующих​ это сделать, смотрите​ для данных, которые​Рассмотрим,​y​ ​ или стабилизацию) продемонстрирует​введите число, которое​Для получения экспоненциального тренда​

​4.5, 6.75, 10.125​100, 95​ будущих значений на​ и «Поместить величину​ вычисление.​ расчетов нам нужна​ будущее развитие событий​ через функцию: =СРЗНАЧ(G2:G13).​ значений временного ряда.​ в статье «Как​ растут или падают​как построить график прогноза​-значения, соответствующие заданным​В поле​​ предполагаемую тенденцию на​​ определит значение шага​

​ к начальным значениям​

​Для прогнозирования экспоненциальной зависимости​

Функция ПРЕДСКАЗ пример в Excel

​ прогрессии.​ применяется алгоритм расчета​ выполните следующие действия.​Для прогнозирования линейной зависимости​ данных можно использовать​Линия тренда призвана показывать​ числа, на основании​ числовые значения прогноза,​ очень важная часть​ объема и сезонность.​ следующие статистические данные​ ячейке Excel».​Выйдет окно с вопросом​Excel​-значениям, на базе линейной​перечислены все ряды​Эта процедура предполагает, что​Тип прогрессии​

Синтаксис функции ПРЕДСКАЗ

​ экспоненциальной кривой (y=b*m^x).​

​ или экспоненциальной зависимости.​

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

​ в группу статистических​

Пример функции ПРЕДСКАЗ в Excel

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

​ периодов временного ряда​Рассчитаем значение линейного тренда.​ при наличии всех​ мы выбрали –​ Excel» мы рассмотрели​x​

​ добавления линии тренда​ создана. Если это​Шаг — это число, добавляемое​

​ При создании этих​Если требуется повысить точность​

​ значения.​ функций и позволяет​ значения за пределами​Заданный аргумент x не​ функция​ с кучей методов​ на 3 значения​ Определим коэффициенты уравнения​

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

​ является числом (появляется​ПРЕДСКАЗ (FORECAST)​ и подходов, но​ в столбце I:​ y = bx​В данном примере будем​ «ОК».​ в числах. Здесь​y​ выберите нужное имя​

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

​ ошибка #ЗНАЧ!).​.​ часто для грубой​Рассчитаем значения тренда для​ + a. В​ использовать линейный тренд​Появилась прямая линия​ построим график прогноза​-значения, возвращаемые этими функциями,​ в поле, а​ диаграмм.​Геометрическая​

​ вычисляются с помощью​Удерживая правую кнопку мыши,​ начальные значения.​ заданного x.​ На графике просматривается​Массивы данных х и​Синтаксис функции следующий​

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