Печать
PDF

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. сделаем прогноз;

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

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


Зарегистрируйтесь и протестируйте Forecast4AC PROскачать программу для прогнозирования Forecast4AC PRO

график модель прогноза + трендграфик сезенностьграфик прогноз + границы прогноза + трендграфик прогноз к предыдущим периодам

Forcast4AC Pro 4ddabd6bbee26 250x250Подписка "Прогноз с точностью 90% и выше!"

Присоединяясь к нам Вы получаете:

  • Более 10 рекомендаций по прогнозированию продаж!
  • Практические примеры!
  • Ссылку на Forecast4AC PRO - 40 дней бесплатно!
  • + советы по работе с программой!

Forecast4AC PRO - прогноз одним нажатием клавиши!

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

Ваши комментарии и вопросы:

 

Comments  

 
#39 Алексей Батурин 04.11.2016 08:58
Цитирую Сергей:
вечер добрый, почему вы используете формулу тренда y(x)=a+bx? обычно используют ax+b

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

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


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

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

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


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

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

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

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

Спасибо Вам большое!
Но у меня в сочетани клавиш ничего не происходит.. они все равно соскакивают вниз..
Даже и не знаю, что можно придумать :)

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

Алексей, попробовала, к сожалению, вновь не получилось... Сочетание клавиш сдвинуло курсор вниз.... :(
Посмотрела настройки клавиш - все так, как должно быть...
Но в Excel работать это сочетание не хочет..
К слову, Excel 2011 года, вот сейчас изучаю особенности.. может быть в версии программы сбой...
Расстроилась, так как перепробовала немало сочетаний клавиш - эффект нулевой. Продолжаю наблюдать скачки по ячейкам :(
Quote
 
 
+1 #30 Алексей Батурин 30.03.2016 12:52
Цитирую Анастасия:

Спасибо Вам большое!
Но у меня в сочетани клавиш ничего не происходит.. они все равно соскакивают вниз..
Даже и не знаю, что можно придумать :)

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

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


Защитный код
Обновить