Формула линии тренда. Как диаграммы и графики нас обманывают

07.05.2019

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

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

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

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

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

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

Не менее логично и то, что чем больше страниц просматривает посетитель, тем лучше сайт — он захватывает внимание пользователя и заставляет его углубиться в чтение.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Способ 1: линия тренда

Одним из самых популярных видов графического прогнозирования в Экселе является экстраполяция выполненная построением линии тренда.

Попробуем предсказать сумму прибыли предприятия через 3 года на основе данных по этому показателю за предыдущие 12 лет.

Способ 2: оператор ПРЕДСКАЗ

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

ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)

«X» – это аргумент, значение функции для которого нужно определить. В нашем случае в качестве аргумента будет выступать год, на который следует произвести прогнозирование.

«Известные значения y» — база известных значений функции. В нашем случае в её роли выступает величина прибыли за предыдущие периоды.

«Известные значения x» — это аргументы, которым соответствуют известные значения функции. В их роли у нас выступает нумерация годов, за которые была собрана информация о прибыли предыдущих лет.

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

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

Давайте разберем нюансы применения оператора ПРЕДСКАЗ на конкретном примере. Возьмем всю ту же таблицу. Нам нужно будет узнать прогноз прибыли на 2018 год.

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

Способ 3: оператор ТЕНДЕНЦИЯ

Для прогнозирования можно использовать ещё одну функцию – ТЕНДЕНЦИЯ . Она также относится к категории статистических операторов. Её синтаксис во многом напоминает синтаксис инструмента ПРЕДСКАЗ и выглядит следующим образом:

ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

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

Данный оператор наиболее эффективно используется при наличии линейной зависимости функции.

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

Способ 4: оператор РОСТ

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

РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

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

Способ 5: оператор ЛИНЕЙН

Оператор ЛИНЕЙН при вычислении использует метод линейного приближения. Его не стоит путать с методом линейной зависимости, используемым инструментом ТЕНДЕНЦИЯ . Его синтаксис имеет такой вид:

ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

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

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

Способ 6: оператор ЛГРФПРИБЛ

Последний инструмент, который мы рассмотрим, будет ЛГРФПРИБЛ . Этот оператор производит расчеты на основе метода экспоненциального приближения. Его синтаксис имеет следующую структуру:

ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

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

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

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

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

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

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

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

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

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

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

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

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

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

Построение модели

Итак, мы знаем объем продаж за прошедшие 9 месяцев. Вот, что из себя представляет наша табличка:

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

Определение коэффициентов модели

Строим график. По горизонтали видим отложенные месяцы, по вертикали объем продаж:

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

Если вы делаете все в MS Excel, то правой кнопкой мыши кликаем на график и в выпадающем меню выбираем «Добавить линию тренда».

По умолчанию строится линейная функция. Справа выбираем «Показывать уравнение на диаграмме» и «Величину достоверности аппроксимации R^2».

Вот, что получилось:

На графике мы видим уравнение функции:

y = 4856*x + 105104

Она описывает объем продаж в зависимости от номера месяца, на который мы хотим эти продажи спрогнозировать. Рядом видим коэффициент детерминации R^2, который говорит о качестве модели и на сколько хорошо она описывает наши продажи (Y). Чем ближе к 1, тем лучше.

У меня R^2 = 0,75. Это средний показатель, он говорит о том, что в модели не учтены какие-то другие значимые факторы помимо времени t, например, это может быть сезонность.

Прогнозируем

y = 4856*10 + 105104

Получаем 153664 продажи в следующем месяце. Если добавим новую точку на график, то сразу видим, что R^2 улучшился.

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

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

лепестковый;
круговой;
поверхностный;
кольцевой;
объемный;
с накоплением.

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

Получаем результат:

y = 4,503x + 6,1333

6,1333 – смещения;
х – номер периода.

y = 7,6403е^-0,084x

Например:

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

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

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

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

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

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

Строим таблицу, где в одном столбике будут располагаться временные отрезки (в нашем случае даты), а в другом – величина, динамика которой будет отображаться в графике.
Выделяем данную таблицу. Переходим во вкладку «Вставка». Там на ленте в блоке инструментов «Диаграммы» кликаем по кнопке «График». Из представленного списка выбираем самый первый вариант.
После этого график будет построен, но его нужно ещё доработать. Делаем заголовок графика. Для этого кликаем по нему. В появившейся группе вкладок «Работа с диаграммами» переходим во вкладку «Макет». В ней кликаем по кнопке «Название диаграммы». В открывшемся списке выбираем пункт «Над диаграммой».
В появившееся поле над графиком вписываем то название, которое считаем подходящим.
Затем подписываем оси. В той же вкладке «Макет» кликаем по кнопке на ленте «Названия осей». Последовательно переходим по пунктам «Название основной горизонтальной оси» и «Название под осью».
В появившемся поле вписываем название горизонтальной оси, согласно контексту расположенных на ней данных.
Для того, чтобы присвоить наименование вертикальной оси также используем вкладку «Макет». Кликаем по кнопке «Название осей». Последовательно перемещаемся по пунктам всплывающего меню «Название основной вертикальной оси» и «Повернутое название». Именно такой тип расположения наименования оси будет наиболее удобен для нашего вида диаграмм.
В появившемся поле наименования вертикальной оси вписываем нужное название.

Урок: Как сделать график в Excel

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

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

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

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

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

Последовательно переходим во вкладке «Макет» по пунктам меню «Анализ», «Линия тренда» и «Дополнительные параметры линии тренда…».
Открывается окно параметров, можно произвести различные настройки. Например, можно выполнить изменение типа сглаживания и аппроксимации, выбрав один из шести пунктов:
- Полиномиальная;
- Линейная;
- Степенная;
- Логарифмическая;
- Экспоненциальная;
- Линейная фильтрация.
Для того, чтобы определить достоверность нашей модели, устанавливаем галочку около пункта «Поместить на диаграмму величину достоверности аппроксимации». Чтобы посмотреть результат, жмем на кнопку «Закрыть».

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Помогла ли вам эта статья?

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

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

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

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

Не менее логично и то, что чем больше страниц просматривает посетитель, тем лучше сайт - он захватывает внимание пользователя и заставляет его углубиться в чтение.

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

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

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

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

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

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

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

Добавляем к диаграмме в MS Excel линию тренда

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

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

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

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

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

Линия тренда в Excel. Процесс построения

Линия тренда - это один из основных инструментов анализа данных

Чтобы сформировать линию тренда, необхдимо совершить три этапа, а именно:
1. Создать таблицу;
2. Построить диаграмму;
3. Выбрать тип линии тренда.

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

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

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

Различные вариации л инии тренда

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

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

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

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

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

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

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

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

Линия тренда в Excel. Настройка параметро в функциональной линии

Нажав на кнопку «Линия тренда», выбираем необходимое меню под названием «Дополнительные параметры». В появившемся окне следует нажать на «Формат линии тренда», а после поставить и отметку напротив значения «поместить на диаграмму величину достоверности аппроксимации R^2». После этого закрываем меню, нажав на соответственную кнопку. На самой же диаграмме появляется коэффициент R^2= 0,6442.

После этого отменяем вводимые изменения. Выделив график и нажав на вкладку «Макет», следом нажимаем на «Линию тренда» и наживаем на «Нет». Следом, перейдя в функцию «Формат линии тренда», нажимаем на полиноминальную линию и пытаемся добиться значения R^2= 0,8321, меняя степень.

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

(Visited 10 510 times, 27 visits today)

Линия тренда в Excel на разных графиках

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

Рассмотрим, как добавить линию тренда на график в Excel.

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

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

Построим на основе таблицы график. Выделим диапазон – перейдем на вкладку «Вставка». Из предложенных типов диаграмм выберем простой график. По горизонтали – год, по вертикали – цена.
Щелкаем правой кнопкой мыши по самому графику. Нажимаем «Добавить линию тренда».
Открывается окно для настройки параметров линии. Выберем линейный тип и поместим на график величину достоверности аппроксимации.
На графике появляется косая линия.

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

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

Внимание!!! Линию тренда нельзя добавить следующим типам графиков и диаграмм:

лепестковый;
круговой;
поверхностный;
кольцевой;
объемный;
с накоплением.

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

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

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

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

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

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

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

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

Получаем результат:

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

y = 4,503x + 6,1333

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

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

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

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

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

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

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

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

y = 7,6403е^-0,084x

где 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 степень.

Скачать примеры графиков с линией тренда

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

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

1) Что такое тренд (это важно как отправная точка)
2) Построение трендовых линий
3) Использование в практической торговле
4) Субъективность метода

1) Что такое тренд
_________________
Прежде, чем перейти к построению трендовой линии, надо разобраться непосредственно с самим трендом. Не будем вдаваться в академические споры и для простоты примем следующую формулу:

Тренд (восходящий) – это последовательность растущих максимумов и минимумов, при этом каждый последующий максимум (и минимум) выше предыдущих.

Тренд (нисходящий) – это последовательность падающих (убывающих) максимумов и минимумов, где каждый последующий минимум (и максимум) НИЖЕ предыдущего.

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

2) Построение трендовых линий
____________________________

Это самый сложный вопрос! Мне доводилось видеть дискуссии на много страниц только о том, КАК ПРАВИЛЬНО строить линию тренда! А ведь нам надо не только строить, но и торговать по ней…

Что бы построить трендовую линию надо иметь, как минимум, два максимума (нисходящий тренд) или два минимума (восходящий тренд). Мы должны соединить эти экстремумы линией.

Важно соблюдать следующие правила при построении линий:

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

Данные правила помогут вам правильно строить трендовые линии!

3) Торговля по трендовым линиям
____________________________

Мы имеем две принципиально разные возможности:
А) Использовать линию как уровень поддержки (сопротивления), что бы войти по ней по направлению тренда
Б) Использовать трендовую линию Форекс для того, что бы сыграть на пробой (разворот) тренда.

Оба способа хороши, если уметь «правильно их готовить».

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

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

Если мы хотим сыграть на слом линии тренда, то надо действовать немного иначе:
1) Ждем касание линии
2) Ждем отскока
3) На образовавшуюся галочку ставим ордер бай-стоп (или sell стоп)
Обратите внимание на рисунок.

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

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

4) Субъективность метода
_________________________

Кажется все просто? На деле, используя данный метод, мы столкнемся со следующими трудностями:
А) Угол наклона линии (всегда можно построить линии тренда имеющие разный наклон.
Б) Что считать пробоем трендовой линии (насколько пунктов или процентов цена должна «переломить» линию, что бы считать это прорывом)?
В) Когда линию считать «устаревшей» и строить новую?

Обратите внимание на рисунок.

Красной линией обозначен один из вариантов начертания. Неопытный трейдер мог так провести линию (и поплатиться за это).

В данном деле важен практический опыт. То есть не удается все свести к нескольким простым правилам построения. Именно поэтому индикатора трендовых линий не существует. Точнее, может и существует, но строит их «криво» и неправильно. Эта техника изначально «заточена» под опыт и мастерство трейдера.

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

Нужен ли данный инструмент в вашей торговле – решать только вам!

Успехов и удачных торгов. Артур.
blog-forex.org

Часть 10. Подбор формул по графику. Линия тренда

Для рассмотренных выше задач удавалось построить уравнение или систему уравнений.

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

Процесс подбора эмпирической формулы P(x) для опытной зависимости F(x) называется аппроксимацией (сглаживанием). Для зависимостей с одним неизвестным в Excel используются графики, а для зависимостей со многими неизвестными – пары функций из группы Статистические ЛИНЕЙН и ТЕНДЕНЦИЯ, ЛГРФПРИБЛ и РОСТ.

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

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

Excel предоставляет 5 видов аппроксимирующих функций:

1. Линейная – y=cx+b . Это простейшая функция, отражающая рост и убывание данных с постоянной скоростью.

2. Полиномиальная – y=c0+c1x+c2x2+…+c6x6 . Функция описывает попеременно возрастающие и убывающие данные. Полином 2-ой степени может иметь один экстремум (min или max), 3-ей степени – до 2-х экстремумов, 4-ой степени – до 3-х и т.д.

3. Логарифмическая – y=c lnx+b . Эта функция описывает быстро возрастающие (убывающие) данные, которые затем стабилизируются.

4. Степенная – y=cxb , (х >0и y >0). Функция отражает данные с постоянно увеличивающейся (убывающей) скоростью роста.

5. Экспоненциальная – y=cebx , (e – основание натурального логарифма). Функция описывает быстро растущие (убывающие) данные, которые затем стабилизируются.

Для всех 5-ти видов функций используется аппроксимация данных по методу наименьших квадратов (см. справку по F1 «линия тренда»).

В качестве примера рассмотрим зависимость продаж от рекламы, заданную следующими статистическими данными по некоторой фирме:

(тыс. руб.)	1,5	2,5	3,5	4,5	5,5
Продажи (тыс. руб.)

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

Приступим к решению . В первую очередь введите эти данные в Excel и постройте график, как на рис. 38. Как видно, график построен на основании диапазона B2:J2. Далее, щелкнув правой кнопкой мыши по графику, добавьте линию тренда, как показано на рис. 38.

Чтобы подписать ось Х соответствующими значениями рекламы (как на рис. 38), следует в ниспадающем меню (рис. 38) выбрать пункт И сходные данные . В открывшемся одноименном окне, в закладке Ряд , в поле П одписи оси Х , укажите диапазон ячеек, где записаны значения Х (здесь $B$1:$K$1):

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

После нажатия ОК Вы получите результат, как на рис. 40. Коэффициент детерминации R2= 0.9846, что является неплохой степенью близости. Для подтверждения правильности выбранной функции (поскольку других теоретических соображений нет) спрогнозируйте развитие продаж на 10 периодов вперед. Для этого щелкните правой кнопкой по линии тренда – измените формат – после этого в поле Прогноз: вперед на: установите 10 (рис.

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

Вычисление по полученной формуле =237,96*LN(6)+5,9606 в Excel дает значение 432 тыс. руб.

В Excel имеется функция ПРЕДСКАЗ(), которая вычисляет будущее значение Y по существующим парам значений X и Y значениям с использованием линейной регрессии. Функция Y по возможности должна быть линейной, т.е. описываться уравнением типа c+bx .

Функция предсказания для нашего примера запишется так: =ПРЕДСКАЗ(K1;B2:J2;B1:J1). Запишите – должно получится значение 643,6 тыс. руб.

Часть11. Контрольные задания

Предыдущая12345678910111213141516Следующая

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

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

Поэтому просто прикиньте, какому типу функции более всего соответствует график имеющихся данных: линейной, логарифмической, экспоненциальной, или иной. Если же вы сомневаетесь в выборе типа аппроксимации, можете построить несколько линий, а для большей точности прогноза на вкладке «Параметры» этого же окна отметить флажком пункт «поместить на диаграмму величину достоверности аппроксимации (R^2)».

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

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

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

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

Поэтому для наибольшей точности построения прогноза вам придется воспользоваться одной из нескольких статистических функций: «ПРЕДСКАЗ», «ТЕНДЕНЦИЯ», «РОСТ», «ЛИНЕЙН» или «ЛГРФПРИБЛ». В этом случае вам придется высчитывать значение для каждого последующего периода прогноза вручную. Если вам необходимо произвести более сложный регрессионный анализ данных, вам понадобится надстройка «Пакет анализа», которая не входит в стандартную установку MS Office.