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

Автор: Алексей Батурин.

Это первая статья из серии "Как самостоятельно рассчитать прогноз продаж с учетом роста и сезонности", из которой вы узнаете о 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, необходимо

    1. установить курсор в ячейку с формулой и выделить соседнюю справа, как на рисунке;линейный тренд
    2. нажимаем клавишу F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.

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

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

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

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

Рассчитаем значения линейного тренда с помощью стандартной функции 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. сделаем прогноз;

О том, что еще важно знать о линейном тренде, вы можете узнать в статье "Что важно знать о линейном тренде".

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

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

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

Novo Forecast - прогноз в Excel - точно, легко и быстро!

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

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

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

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

 

 

Комментарии   

#41 Алексей Батурин 27.12.2017 09:20
Сергей Иванович, добрый день!
С наступающим Новым Годом! Удачи и счастья в Новом Году!
По поводу формулы =ЛИНЕЙН()
Она очень гибкая в плане настроек и можно сделать все что хотите.
Посмотрите, пожалуйста. пример изменения формулы в этой статье:
https://4analytics.ru/trendi/3-sposoba-rascheta-polinoma-v-excel.html

С Наступающим!

Цитирую Сергей Иванович К.:
Алексей, добрый день и с наступающим Новым Годом!
У меня давно есть вопрос про линию тренда в Excel. Многие реальные процессы, хорошо описываемые экспонентой, асимптотически стремятся (или растут) не обязательно к (из) оси абсцисс. Линия тренда, построенная в Excel при этом очень сильно отклоняется от реального графика, ибо асимптотически стремится именно к нулю ("растёт" из нуля). Как быть? Нет ли каких то усовершенствований, чтобы можно было использовать формулу вида y=a+b*e^c*x, а то и ещё более универсальную - y=a+b*х+c*e^d*x, т.е. чтобы асимптота могла быть и наклонной?
С уважением, Сергей.
Цитировать
#40 Сергей Иванович К. 26.12.2017 20:48
Алексей, добрый день и с наступающим Новым Годом!
У меня давно есть вопрос про линию тренда в Excel. Многие реальные процессы, хорошо описываемые экспонентой, асимптотически стремятся (или растут) не обязательно к (из) оси абсцисс. Линия тренда, построенная в Excel при этом очень сильно отклоняется от реального графика, ибо асимптотически стремится именно к нулю ("растёт" из нуля). Как быть? Нет ли каких то усовершенствова ний, чтобы можно было использовать формулу вида y=a+b*e^c*x, а то и ещё более универсальную - y=a+b*х+c*e^d*x , т.е. чтобы асимптота могла быть и наклонной?
С уважением, Сергей.
Цитировать
#39 Алексей Батурин 04.11.2016 05:58
Цитирую Сергей:
вечер добрый, почему вы используете формулу тренда y(x)=a+bx? обычно используют ax+b

Сергей, доброе утро. Так захотелось, чтобы думали, а не просто копировали. В разных источниках по разному, есть и mx+b, у нас вот так ) Главное суть!
Цитировать
#38 Сергей 03.11.2016 17:42
вечер добрый, почему вы используете формулу тренда y(x)=a+bx? обычно используют ax+b
Цитировать
#37 Анастасия 31.03.2016 12:15
Цитирую Алексей Батурин:
Цитирую Анастасия:

Сочетанием клавиш ctrl+shift+enter
Еще помогло то, что нужно курсор оставлять в графе формул, а не в ячейке
Все остальные формулы на MACe работали в ячейках


Анастасия, спасибо! Если будут вопросы, обращайтесь! Буду рад помочь!

Спасибо Вам огромное еще раз! У Вас прекрасный информационный сайт! Ваш ресурс теперь у меня в нужных закладках, если возникнут какие-либо вопросы, я буду знать, куда обращаться! Спасибо! :-) Я написала Вам письмо на почту, надеюсь, я получу обратную связь
Цитировать
#36 +1 Алексей Батурин 31.03.2016 11:58
Цитирую Анастасия:

Сочетанием клавиш ctrl+shift+enter
Еще помогло то, что нужно курсор оставлять в графе формул, а не в ячейке
Все остальные формулы на MACe работали в ячейках


Анастасия, спасибо! Если будут вопросы, обращайтесь! Буду рад помочь!
Цитировать
#35 Анастасия 31.03.2016 11:37
Цитирую Алексей Батурин:
Цитирую Анастасия:
Алексей, хочу выразить Вам благодарность! Спасибо, что направили к специалистам данной области! Мне помогли!
Теперь, изучая Вашу статью, могу использовать все 5 способов расчета значений линейного тренда в MS Excel :)

Анастасия, как вы проблему решили в итоге?

Сочетанием клавиш ctrl+shift+ente r
Еще помогло то, что нужно курсор оставлять в графе формул, а не в ячейке
Все остальные формулы на MACe работали в ячейках
Цитировать
#34 +1 Алексей Батурин 31.03.2016 10:22
Цитирую Анастасия:
Алексей, хочу выразить Вам благодарность! Спасибо, что направили к специалистам данной области! Мне помогли!
Теперь, изучая Вашу статью, могу использовать все 5 способов расчета значений линейного тренда в MS Excel :)

Анастасия, как вы проблему решили в итоге?
Цитировать
#33 Анастасия 31.03.2016 10:17
Алексей, хочу выразить Вам благодарность! Спасибо, что направили к специалистам данной области! Мне помогли!
Теперь, изучая Вашу статью, могу использовать все 5 способов расчета значений линейного тренда в MS Excel :)
Цитировать
#32 +1 Алексей Батурин 30.03.2016 10:26
Анастасия, попробуйте написать на форум Планеты http://www.planetaexcel.ru/forum/
Они вам точно помогут.
Цитировать

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