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

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

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

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

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

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

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

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

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

Функция ТЕНДЕНЦИЯ в Excel.

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

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

Как использовать функцию ТЕНДЕНЦИЯ в Excel?

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

Заданные значения и значения Y, которые необходимо предсказать:

Чтобы предсказать значения GPA для заданных результатов тестов в ячейках D2, D3 и D4, мы будем использовать функцию ТЕНДЕНЦИЯ в Excel.

Формула ТЕНДЕНЦИЯ в excel будет принимать существующие значения известных X и Y, а мы передадим новые значения X для вычисления значений Y в ячейках E2, E3 и E4.

= ТЕНДЕНЦИЯ ($ A $ 2: $ A $ 16, $ B $ 2: $ B $ 16, D2)

Мы зафиксировали диапазон для известных значений X и Y, и передаем новое значение X в качестве опорного значения. Применяя ту же формулу ТЕНДЕНЦИИ в Excel к другим ячейкам, мы имеем

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

Примем за основную — линейную расчетную зависимость. Тогда значения затрат времени в зависимости от количества товара будут определяться по формуле: y = 54,511x + 0,1489. Результаты этих расчетов для количества товара, по которому ранее были проведены хронометражные наблюдения, представлены в таблице 3 ниже.

Как построить линию тренда в MS Excel — Вектор развития. Офисные системы для бизнеса

В Гугл Sheets избираем Редактор диаграмм -> Доп и ставим галочку около Полосы тренда. В настройках избираем Ярлычек — Уравнение и Показать R^2.

Разбираемся с трендами в MS Excel

Что же мы видим на графике? Оранжевые столбики «осени» как минимум ни чем не больше «весенних», а то и меньше. Это свидетельствует не об успехе, а скорее наоборот — посетители прибывают, но читают в среднем меньше и на сайте не задерживаются!

Самое время бить тревогу и… знакомится с такой штукой как линия тренда.

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

Арчи Гудвин: Линейный тренд в Excel — простой прогноз для ряда динамики — MS Office и VBA | Специализированные блоги

Уравнение указывает переменные до третьей степени, так как график имеет два пика. Также лицезреем, что коэффициент достоверности близок к единице (заместо 0,5 при линейной аппроксимации), означает линия тренда выбрана верно и предстоящий прогноз будет четким.

Зачем нужна линия тренда

Линия тренда “по-простому”, это непрерывная линия составленная на основе усредненных на основе специальных алгоритмов значений из которых строится наша диаграмма. Иными словами, если наши данные “прыгают” за три отчетных точки с “-5” на “0”, а следом на “+5”, в итоге мы получим почти ровную линию: “плюсы” ситуации очевидно уравновешивают “минусы”.

Исходя из направления линии тренда гораздо проще увидеть реальное положение дел и видеть те самые тенденции, а следовательно – строить прогнозы на будущее. Ну а теперь, за дело!

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

Как построить линию тренда в Excel — Офис Ассист

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