Линия тренда в excel

Линия тренда в Excel

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

Построение графика

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

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

Выделяем данную таблицу. Переходим во вкладку «Вставка». Там на ленте в блоке инструментов «Диаграммы» кликаем по кнопке «График». Из представленного списка выбираем самый первый вариант.

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

Затем подписываем оси. В той же вкладке «Макет» кликаем по кнопке на ленте «Названия осей». Последовательно переходим по пунктам «Название основной горизонтальной оси» и «Название под осью».

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

Для того, чтобы присвоить наименование вертикальной оси также используем вкладку «Макет». Кликаем по кнопке «Название осей». Последовательно перемещаемся по пунктам всплывающего меню «Название основной вертикальной оси» и «Повернутое название». Именно такой тип расположения наименования оси будет наиболее удобен для нашего вида диаграмм.

Создание линии тренда

Теперь нужно непосредственно добавить линию тренда.

1. Находясь во вкладке «Макет» кликаем по кнопке «Линия тренда», которая расположена в блоке инструментов «Анализ». Из открывшегося списка выбираем пункт «Экспоненциальное приближение» или «Линейное приближение».

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

Настройка линии тренда

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

1. Последовательно переходим во вкладке «Макет» по пунктам меню «Анализ», «Линия тренда» и «Дополнительные параметры линии тренда…».

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

• Полиномиальная;
• Линейная;
• Степенная;
• Логарифмическая;
• Экспоненциальная;
• Линейная фильтрация.

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

Если данный показатель равен 1, то модель максимально достоверна. Чем дальше уровень от единицы, тем меньше достоверность.

Если вас не удовлетворяет уровень достоверности, то можете вернуться опять в параметры и сменить тип сглаживания и аппроксимации. Затем, сформировать коэффициент заново.

Прогнозирование

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

1. Опять переходим в параметры. В блоке настроек «Прогноз» в соответствующих полях указываем насколько периодов вперед или назад нужно продолжить линию тренда для прогнозирования. Жмем на кнопку «Закрыть».

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

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

Форматирование линии тренда

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

Откроется панель «Форматирование линии тренда» или справа.

На этой панели вы можете внести следующие изменения:

• Измените цвет или ширину линии тренда. Это может помочь, если вы хотите, чтобы он выделялся другим цветом.
• Измените тип линии тренда (линейная, логарифмическая, экспоненциальная и т. д.).
• Добавление периода прогноза.
• Установка точки пересечения (начните линию тренда с 0 или с определенного числа).
• Отображение уравнения или значения R-квадрата на диаграмме

Давайте посмотрим, как использовать некоторые из этих параметров форматирования.

Изменение цвета и ширины линии тренда

Чтобы изменить цвет линии тренда, выберите параметр «Заливка и линия» на панели «Форматировать линию тренда».

В опциях вы можете изменить опцию цвета и значение ширины.

Добавление периода прогноза к линии тренда

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

Что-то вроде показанного ниже:

Для этого измените значение периода «Вперед» прогноза в параметрах панели «Форматировать линию тренда».

Установка точки пересечения линии тренда

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

По умолчанию (когда вы ничего не указываете) он автоматически определяет перехват на основе набора данных.

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

Если вы хотите, чтобы линия тренда пересекалась с 0, вы можете сделать это, изменив ее, изменив значение «Set Intercept».

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

Надеюсь, вы найдете этот урок полезным.

Как добавить линию тренда в диаграммы Excel (пошаговое руководство)

Как построить линию тренда в MS Excel

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

На листе диаграммы теперь отображается пунктирная линия тренда. Как видите, она не совпадает на 100% со значениями диаграммы — построенная по средневзвешенным значениям, она лишь в общих чертах повторяет её направление. Однако это не мешает нам видеть устойчивый рост числа посещений сайта — на общем результате не сказывается даже «летняя» просадка.

Линия тренда для столбца «Посетители»

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

Ещё одна линия тренда позволяет прояснить ситуацию

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

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

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

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

Уравнение линии тренда в Excel

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

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

Линейная аппроксимация

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

Рассмотрим условное количество заключенных менеджером контрактов на протяжении 10 месяцев:

На основании данных в таблице Excel построим точечную диаграмму (она поможет проиллюстрировать линейный тип):

Выделяем диаграмму – «добавить линию тренда». В параметрах выбираем линейный тип. Добавляем величину достоверности аппроксимации и уравнение линии тренда в Excel (достаточно просто поставить галочки внизу окна «Параметры»).

Обратите внимание! При линейном типе аппроксимации точки данных расположены максимально близко к прямой. Данный вид использует следующее уравнение:. y = 4,503x + 6,1333

y = 4,503x + 6,1333

• где 4,503 – показатель наклона;
• 6,1333 – смещения;
• y – последовательность значений,
• х – номер периода.

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

Чтобы спрогнозировать количество заключенных контрактов, например, в 11 периоде, нужно подставить в уравнение число 11 вместо х. В ходе расчетов узнаем, что в 11 периоде этот менеджер заключит 55-56 контрактов.

Экспоненциальная линия тренда

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

Построим экспоненциальную линию тренда в Excel. Возьмем для примера условные значения полезного отпуска электроэнергии в регионе Х:

Строим график. Добавляем экспоненциальную линию.

Уравнение имеет следующий вид:

• где 7,6403 и -0,084 – константы;
• е – основание натурального логарифма.

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

Логарифмическая линия тренда в Excel

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

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

Построим график и добавим логарифмическую линию тренда для прогноза продаж условного продукта:

R2 близок по значению к 1 (0,9633), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.

Период	14	15	16	17	18	19	20
Прогноз	1005,4	1024,18	1041,74	1058,24	1073,8	1088,51	1102,47

Для расчета прогнозных цифр использовалась формула вида: =272,14*LN(B18)+287,21. Где В18 – номер периода.

Полиномиальная линия тренда в Excel

Данной кривой свойственны переменные возрастание и убывание. Для полиномов (многочленов) определяется степень (по количеству максимальных и минимальных величин). К примеру, один экстремум (минимум и максимум) – это вторая степень, два экстремума – третья степень, три – четвертая.

Полиномиальный тренд в Excel применяется для анализа большого набора данных о нестабильной величине. Посмотрим на примере первого набора значений (цены на нефть).

Чтобы получить такую величину достоверности аппроксимации (0,9256), пришлось поставить 6 степень.

Зато такой тренд позволяет составлять более-менее точные прогнозы.

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

Виды трендов и построение трендовых линий

Грамотное определение рыночных тенденций является залогом успешной торговли. А поскольку тренд – это характерный курс динамики цен во временном диапазоне, выделяется три вида тренда:

1. Основной, длительностью от года до нескольких лет. Такой тренд имеет значение для больших и долгосрочных вложений.
2. Промежуточный, длящийся до нескольких месяцев. По общему правилу, он корректирует основной тренд.
3. Краткосрочный – несколько недель или даже дней. Именно этот вид тренда чаще всего применяется в торговле и труднее всего поддается анализу, ведь на него сильнее всего воздействует изменение экономических показателей, а также те или иные новости.

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

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

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

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

Что показывает линия тренда

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

По линии можно определить силу тренда, используя определенные правила:

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

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

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

Ложный прорыв или «ловушка для трейдера» — это ситуация, при которой цена пересекает линию тренда, но изменения тенденции при этом не происходит, то есть цена продолжает меняться в прежнем направлении (http://binary-options-university.ru/liniya-trenda/). При ложном прорыве нисходящего тренда формируются сигналы для покупки активов, а восходящего – сигналы для продажи. Понятно, что в случае возвращения цены в прежний диапазон проводимые операции обречены на провал. Условием, подтверждающим истинность прорыва, является обязательный разворот тренда – переход линии сопротивления в линию поддержки и обратно после пересечения ее ценой.

Для чего нужна линия тренда

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

Оценивание линии тренда проводится по пяти составляющим:

1. Длина линии: чем она длиннее, тем устойчивее тренд.
2. Угол наклона между трендовой линией и осью: чем он больше, тем более сильна тенденция.
3. Число пересечений тренда с ценой: чем чаще происходят пересечения и «отскоки», тем надежнее динамика.
4. Вид тренда (краткосрочный, промежуточный или основной). Чем больше рассматриваемый временной интервал, тем сильнее можно доверять сложившемуся тренду. Например, линия тренда, оцениваемая в месячном интервале, указывает на более стойкую тенденцию, чем дневная.
5. Количество операций по продажам: если они увеличиваются при изменении цены в направлении трендовой линии, это подтверждает тенденцию. Если число операций вырастает при приближении цены к линии – вероятен разворот тренда.

Как показать линию тренда на графике только в Excel?

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

Показывать линию тренда только на графике

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

В Excel 2007/2010

1. Выберите серию данных (синие кубики) на диаграмме, щелкните правой кнопкой мыши и выберите Добавить линию тренда из контекстного меню. Затем укажите тип линии тренда и нажмите кнопку Закрыть кнопка во всплывающем окне Форматировать линию тренда Диалог.

Внимание: Пользователи не могут добавлять линию тренда для круговых диаграмм в Microsoft Excel. 2

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

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

3. в Форматировать ряд данных диалоговое окно, нажмите Заполнять на левой панели, а затем проверьте Без заливкии нажмите выделяющий цвет на левой панели, затем отметьте Нет линии.

4. Нажмите Закрыть чтобы закрыть диалог, теперь на графике отображается только линия тренда.

В Excel 2013

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

Внимание: Невозможно добавить линию тренда для круговых диаграмм в Excel. 2

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

2. Выберите серию и щелкните правой кнопкой мыши, чтобы отобразить контекстное меню, затем щелкните Форматировать ряд данных. Смотрите скриншот:

3. Затем в Форматировать ряд данных панели, нажмите Заливка и линия вкладка и отметьте Без заливки и Нет линии in FILL и ГРАНИЦА разделы. Смотрите скриншот:

Теперь на графике показана только линия тренда.

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

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

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

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

Метод «скользящих средних» (МСС).

МСС позволяет сгладить ряд значений с тем, чтобы выделить тренд. При использовании этого метода берется среднее (обычно среднеарифметическое) фиксированного числа значений. Например, трехточечное скользящее среднее. Берется первая тройка значений, составленная из данных за январь, февраль и март (10 + 12 + 13), и определяется среднее, равное 35: 3 = 11,67.

Полученное значение 11,67 ставится в центре диапазона, т.е. по строке февраля. Затем «скользим на один месяц» и берется вторая тройка чисел, начиная с февраля по апрель (12 + 13 + 16), и рассчитывается среднее, равное 41: 3 = 13,67, и таким приемом обрабатываем данные по всему ряду. Полученные средние представляют новый ряд данных для построения тренда и его аппроксимации. Чем больше берется точек для вычисления скользящей средней, тем сильнее происходит сглаживание колебаний. Пример из МВА построения тренда дан в табл. 5.2 и на рис. 5.4.

Таблица 5.2 Расчет тренда методом трехточечного скользящего среднего

Характер колебаний исходных данных и данных, полученных методом скользящего среднего, иллюстрирован на рис. 5.4. Из сравнения графиков рядов исходных значений (ряд 3) и трехточечных скользящих средних (ряд 4), видно, что колебания удается сгладить. Чем большее число точек будет вовлекаться в диапазон вычисления скользящей средней, тем нагляднее будет вырисовываться тренд (ряд 1). Но процедура укрупнения диапазона приводит к сокращению числа конечных значений и это снижает точность прогноза.

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

Рис. 5.4. Характер изменения объема продаж по месяцам года:
исходные данные (ряд 3); скользящие средние (ряд 4); экспоненциальное сглаживание (ряд 2); тренд, построенный методом регрессии (ряд 1)

Базовые понятия

Думаю, еще со школы все знакомы с линейной функцией, она как раз и лежит в основе тренда:

Y(t) = a0 + a1*t + E

Y — это объем продаж, та переменная, которую мы будем объяснять временем и от которого она зависит, то есть Y(t);

t — номер периода (порядковый номер месяца), который объясняет план продаж Y;

a0 — это нулевой коэффициент регрессии, который показывает значение Y(t), при отсутствии влияния объясняющего фактора (t=0);

a1 — коэффициент регрессии, который показывает, на сколько исследуемый показатель продаж Y зависит от влияющего фактора t;

E — случайные возмущения, которые отражают влияния других неучтенных в модели факторов, кроме времени t.

Уравнение линии тренда в Excel

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

Линейная аппроксимация

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

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

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

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

Экспоненциальная линия тренда

Этот тип линии тренда знаком сейчас многим. По этому сценарию развиваются все пандемии, а также ряд других процессов. Характерная особенность этого вида линии тренда – цифры постоянно возрастают в геометрической прогрессии. Например, 1,2,4,8 и так далее. По похожему сценарию как раз и развиваются эпидемии, поскольку чем больше больных, тем большему количеству людей может передаться заболевание.

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

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

1

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

2

Достоверность линии тренда в рассматриваемом нами примере составляет 0,938, что говорит, что вероятность ошибки довольно низкая. Следовательно, прогнозам можно доверять. 

Логарифмическая линия тренда

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

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

Давайте сделаем к этому примеру такой график.

3

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

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

4

В нашем кейсе для того, чтобы приблизительно понять, как в будущем будет реализовываться продукция, была применена такая формула: =272,14*LN(B 18)+287,21. Где В18 – номер периода.

Полиномиальная линия тренда в Эксель

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

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

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

5

Шаг 5

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

yi — спрогнозированные уровни ряда,

yi* — фактические уровни ряда,

n — количество складываемых элементов.

Модель может считаться адекватной, если:

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

Сложив весь столбец с ошибками аппроксимации и поделив на 12, получаем среднюю ошибку аппроксимации 4,13%. Это значение меньше 15% и можем сделать вывод об адекватности модели.

Не забывайте, что прогнозы не бывают точными на 100%. Любые неожиданные внешние воздействия могут развернуть значения уровней ряда в неизвестном направлении

Полезные ссылки:

• Ссылка на пример Google Sheets
• Построение функции тренда в Excel. Быстрый прогноз без учета сезонности
• Бывшев В.А. Эконометрика

Екатерина Шипова

Магистр прикладной математики и информатики, веб-аналитик. Сертифицированный специалист Google Аnalytics и Яндекс.Метрика.

• Прогнозирование продаж в Excel с учетом сезонности — 27.06.2018
• Построение функции тренда в Excel. Быстрый прогноз без учета сезонности — 05.06.2018
• Когортный анализ. Сколько пользователей к вам вернулось? — 24.05.2018

Как диаграммы и графики нас обманывают

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

Проиллюстрирую свои слова простейшим примером:

Диаграмма построенная на основе таблицы в MS Excel

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

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

Выводы? Продолжаем в том же духе и вскоре добьемся успеха!

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

Наглядна диаграмма? Вполне. А вот очевидна ли она? Давайте разберемся.