Как спрогнозировать объем продаж на следующий год. Прогнозирование продаж: точный расчет или гадание на кофейной гуще? Подготовка к прогнозированию в Excel

Из данной статьи вы узнаете, как в Excel рассчитать прогноз продаж с учетом роста и сезонности .

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

Процесс расчета прогноза разделим на 3 части:

  1. Расчет значение тренда ;
  2. Определение коэффициентов сезонности ;
  3. Прогнозирование продаж ;

Рассчитаем прогноз по месяцам на 2 года и 3 месяца на основании продаж за 5 лет (см. вложенный файл).

Для расчета значений тренда:

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

Для расчета коэффициентов сезонности:

  1. Рассчитываем отклонение фактических значений от значений тренда. Для этого фактические значения делим на значения тренда;
  2. Для каждого месяца определяем среднее отклонение за последние 5 лет.
  3. Определяем общий индекс сезонности - среднее значение коэффициентов, рассчитанных в 4 пункте;
  4. Рассчитываем коэффициенты сезонности ; каждый коэффициент из пункта 4 делим на коэффициент из пункта 5 (см. вложенный файл);

Рассчитываем прогноз продаж с учетом роста и сезонности:

  1. Задаём период, на который мы хотим рассчитать прогноз. Для этого продлеваем номера периодов временного ряда на 2 года и 3 месяца.
  2. Рассчитываем значения трена для будущих периодов . В уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде (от 61 до 87). Получаем y-значения линейного тренда для каждого будущего периода (см. вложенный файл).
  3. Рассчитываем прогноз . Для этого значения линейного тренда умножаем на коэффициенты сезонности.

Прогноз с учетом роста и сезонности готов.

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

  • мероприятия по стимулированию сбыта,

    ввод новых продуктов,

    открытие новых направлений продаж,

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

и т.д., но об этом в следующих статьях.

Точных вам прогнозов!

С помощью программы Forecast4AC PRO вы сможете рассчитывать прогноз с учетом роста и сезонности для более, чем 5000 строк одновременно одним нажатием клавиши. Легко и быстро!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа :

  • Novo Forecast Lite - автоматический расчет прогноза в Excel .
  • 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Цели и задачи прогнозирования объемов сбыта

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

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

Для чего прогнозируют продажи.

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

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

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

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

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

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

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

2. Составление плана и бюджета продвижения - обеспечение необходимого объема продаж требует привлечения определенного количества клиентов, для чего необходимо провести ряд маркетинговых мероприятий, каждое из которых обладает определенным сроком воздействия (в течение которого вы получите основную отдачу) и стоимостью.

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

Если ни одна из этих задач не актуальна для вашей компании и вы не можете сформулировать ни одной другой веской причины, по которой вам необходимо прогнозировать продажи, не прогнозируйте их. Вы сэкономите много времени и ничего, очевидно, не потеряете. Ни в коем случае не стоит прогнозировать просто «ради интереса». Прогнозирование всегда строится на предпосылках, которые часто до конца понимает только тот специалист, который считает прогноз, а «пользователи» прогноза редко знакомятся с этими предпосылками, даже если они хорошо описаны. Редкий директор удержится, от вопроса к руководителю отдела сбыта типа: «А вот тут ваш стажер напрогнозировал с помощью какой-то новейшей математической модели, что вы можете продавать в два раза больше. Так, может, стоит поднять план на следующее полугодие?» Последствия такого вопроса очевидны.

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

Заниматься прогнозированием должны те люди, которые:

1) хорошо разбираются в продажах, рыночной ситуации и методах прогнозирования (потому что выполнять данную работу, не понимая сути прогнозируемого процесса и методов обработки данных, практически бессмысленно - это все равно что считать среднюю температуру по больнице);

2) заинтересованы (замотивированы) в том, чтобы результат был предельно объективным.

Для сотрудников каждого уровня существуют свои задачи прогнозирования.

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

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

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

Как вы видите, в обоих случаях выбор предмета прогнозирования определяется целями:

1)оперативная цель - уточнение плана продаж (прогнозируем объем продаж нашей компании);

2)стратегическая цель - поиск потенциалов продвижения (прогнозируем возможный объем потребления).

А если ваша компания - монополист, что тогда? Значит, вам всего лишь будет проще прогнозировать объем продаж. Но прогнозировать надо все равно, потому что на объем ваших продаж могут повлиять следующие факторы:

1) изменения в законодательстве, действия антимонопольного комитета и другие решения государственных органов;

2) уровень спроса на товары-заменители. Например, если вы монополист в области стеклянной тары для пива, то вполне возможно, что ваш объем продаж снизится из-за широкого использования алюминиевых банок;

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

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

В какой момент надо прогнозировать.

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

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

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

Допустимый уровень точности.

В статистике применяются уровни точности 90, 95, 99 %, но это все не для вас. Вы делаете прогноз не ради хорошей отметки, и полученный результат вам никто не даст сравнить с правильным ответом. Так зачем вы это делаете?

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

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

Есть ли смысл занижать прогнозы

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

Для того, чтобы легче было научиться прогнозировать продажи с учетом роста и сезонности, я разбил 1 большую статью о расчете прогноза на 3 части:

    1. Расчет значений тренда (рассмотрим на примере Линейного тренда в этой статье);
    2. Расчет сезонности;
    3. Расчет прогноза;

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

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

Рассмотрим линейный тренд на примере расчета прогноза продаж в Excel по месяцам.

Временной ряд продажи по месяцам (см. вложенный файл).

В этом временном ряду у нас есть 2 переменных:

  1. Время - месяцы;
  2. Объём продаж;

Уравнение линейного тренда y(x)=a+bx, где

y - это объёмы продаж

x - номер периода (порядковый номер месяца)

a – точка пересечения с осью y на графике (минимальный уровень);

b – это значение, на которое увеличивается следующее значение временного ряда;

1-й способ расчета значений линейного тренда в Excel с помощью графика

Выделяем анализируемый объём продаж и строим график, где по оси Х - наш временной ряд (1, 2, 3… - январь, февраль, март …), по оси У - объёмы продаж. Добавляем линию тренда и уравнение тренда на график. Получаем уравнение тренда y=135134x+4594044

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

  1. Время - значение по оси Х;
  2. Значение "a" и "b" уравнения линейного тренда y(x)=a+bx;

Рассчитываем значения тренда для каждого периода времени от 1 до 25, а также для будущих периодов с 26 месяца до 36.

Например , для 26 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=26 и получаем y=135134*26 +4594044=8107551

27 -го y=135134*27 +4594044=8242686

2-й способ расчета значений линейного тренда в Excel - функция ЛИНЕЙН

1. Рассчитаем коэффициенты линейного тренда с помощью стандартной функции Excel:

=ЛИНЕЙН(известные значения y, известные значения x, константа, статистика)

Для расчета коэффициентов в формулу вводим

    известные значения y (объёмы продаж за периоды),

    известные значения x (номера периодов),

    вместо константы ставим 1,

    вместо статистики 0,

Получаем 135135 - значение (b) линейного тренда y=a+bx;

Для того чтобы Excel рассчитал сразу 2 коэффициента (a) и (b) линейного тренда y=a+bx, необходимо


Получаем 135135, 4594044 - значение (b) и (a) линейного тренда y=a+bx;

2. Рассчитаем значения линейного тренда с помощью полученных коэффициентов. Подставляем в уравнение y=135134*x+4594044 номера периодов - x, для которых хотим рассчитать значения линейного тренда.

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


3-й способ расчета значений линейного тренда в Excel - функция ТЕНДЕНЦИЯ

=ТЕНДЕНЦИЯ(известные значения y; известные значения x; новые значения x; конста)

Подставляем в формулу

  1. известные значения y - это объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);
  2. известные значения x - это номера периодов x для известных значений объёмов продаж y;
  3. новые значения x - это номера периодов, для которых мы хотим рассчитать значения линейного тренда;
  4. константа - ставим 1, необходимо для того, чтобы значения тренда рассчитывались с учетом коэффицента (a) для линейного тренда y=a+bx;

Для того чтобы рассчитать значения тренда для всего временного диапазона, в "новые значения x" вводим диапазон значений X, выделяем диапазон ячеек равный диапазону со значениями X с формулой в первой ячейке и нажимаем клавишу F2, а затем - клавиши CTRL + SHIFT + ВВОД.

4-й способ расчета значений линейного тренда в Excel - функция ПРЕДСКАЗ

Рассчитаем значения линейного тренда с помощью стандартной функции Excel:

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

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

Вместо "известные значения y" - объёмы продаж за анализируемый период (фиксируем диапазон в формуле, выделяем ссылку и нажимаем F4);

"известные значения x" - это номера периодов для каждого выделенного объёма продаж.

3-й и 4-й способ расчета значений линейного тренда быстрее, чем 1 и 2-й, однако с его помощью невозможно управлять коэффициентами тренда, как описано в статье "О линейном тренде ".

5-й способ расчета значений линейного тренда в Excel - Forecast4AC PRO

1. Устанавливаем курсор в начало временного ряда, выбираем в настройках программы:
- Что рассчитываем - значения тренда;
- Тренд - Линейный тренд;
- Временной ряд - месячный;
и сохраняем;

2. Заходим в меню программы и нажимаем "Start_Forecast". Значения линейного тренда рассчитаны.

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

В следующих статье "Как самостоятельно сделать прогноз продаж с учетом роста и сезонности" мы:

  1. рассчитаем коэффициенты сезонности, очищенные от роста и выровненные ;
  2. Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

    3. Тестируйте возможности платных решений:

  • Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

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

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

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

Рассмотрим несколько способов, которые могут дать прогнозированный результат:

1. Линия тренда

Линия тренда – графическое отображение прогнозирования за счет экстраполяции. Звучит заумно? На практике все проще.
Давайте попробуем спрогнозировать сумму доходов компании через 36 месяцев на основе показателей за прошлые 12 лет.

Построим точечную диаграмму на основе исходных данных компании, а именно ее прибыль в течение всех 12 лет. Запишем исходные данные по прибыли в таблицу, выделим все ее поля и перейдем в меню «Вставка» - «Диаграмма» и выберем точечный вид диаграммы.


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

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

По построенной линии тренда можем спрогнозировать доход через три года – он будет более 4500 тыс. руб. Достоверность прогнозирования принято считать верным при «0.85» ед. Эффективность прогнозирования не будет успешным, если период будет превышать 30% от периода базы.

2. Использование оператора ПРЕДСКАЗ

Также в наборе функций программы есть ряд стандартных фунций создания прогноза. Одним из таких является оператор «ПРЕДСКАЗ», синтаксис которого таковой: «=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)».

Аргумент «Х», исходя из нашей таблицы, это искомый год для прогнозирования. «Значения у» - прибыль за прошлое время. «Значения х» - года, в течение которых были собраны данные.

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

В появившемся диалоговом окне укажем все исходные данные, согласно описанию выше.

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



3. Использование оператора ТЕНДЕНЦИЯ

Еще одним статическим оператором, который можно использовать для прогнозирования, является оператор «ТЕНДЕНЦИЯ» со следующим синтаксисом: «=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])». Аргументы оператора идентичны аргументам оператора «ПРЕДСКАЗ».

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

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

4. Использование оператора РОСТ

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

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

5. Использование оператора ЛИНЕЙН

Другой оператор, который может спрогнозировать результат на определенный период времени, оператор «ЛИНЕЙН», который основан на линейном приближении. Его синтаксис схож с прошлыми операторами: «=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])».

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

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

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

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

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

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

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

Совет: ряд (вкладка "Главная ", Группа " Редактирование ", кнопка " Заливка ").

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

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

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

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

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

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

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

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

Заполнение линейного тренда или значений тенденций роста вручную

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Щелкните диаграмму.

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

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

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

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

    • Если вы выбрали параметр полином , введите в поле порядок самое высокое значение для независимой переменной.

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

Примечания:

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

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

Выполнение регрессионного анализа с помощью надстройки "пакет анализа"

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

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

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

Значения проекта с помощью функции листа

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

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

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

Функция

Описание

Значения проекта

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

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

Похожие статьи