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

Создание диаграмм и графиков в Excel

ПОСТРОЕНИЕ ЛИНИИ ТРЕНДА В EXCEL ДЛЯ АНАЛИЗА ГРАФИКА

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

КАК ДОБАВИТЬ ЛИНИЮ ТРЕНДА НА ГРАФИК

Данные в диапазоне A1:B7 отобразим на графике и найдем линейную функциональную зависимость y=f(x):

Выделите диапазон A1:B7 и выберите инструмент: «Вставка»-«Диаграммы»-«Точечная»-«Точечная с гладкими кривыми и маркерами».

Когда график активный нам доступная дополнительная панель, а в ней выберите инструмент: «Работа с диаграммами»-«Макет»-«Линия тренда»-«Линейное приближение»

Сделайте двойной щелчок по лини тренду и в появившемся окне поставьте галочку напротив опции: «показывать уравнение на диаграмме».

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

ПРОГНОЗИРОВАНИЕ В EXCEL С ПОМОЩЬЮ ЛИНИИ ТРЕНДА

Сменим график на гистограмму, чтобы сравнить их уравнения:

Щелкните правой кнопкой по графику и выберите опцию из контекстного меню: «Изменить тип диаграммы».

В появившемся окне выберите параметр: «Гистограмма»-«Гистограмма с группировкой».

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

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

Сравним уравнения на разных типах графиков. На оси координат XY:

прямая с уравнением: y=2,4229x-3,519 пересекает вертикальную ось Y в отрицательном значении оси: y=-3,519.

прямая с уравнением: y=2,4229x+18,287 пересекает вертикальную ось Y на уровне: y=18,287.

Чтобы визуально убедится в этом, сделаем следующее:

Снова измените гистограмму на точечную диаграмму. Правой кнопкой по гистограмме «Изменить тип диаграммы», далее «Точечная»-«Точечная с гладкими кривыми и маркерами».

Сделайте двойной щелчок по линии тренда и измените ее параметры так: «Прогноз»-«назад на:» установите значение 12,0. И нажмите ОК.

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

Как в excel построить тренд

​Смотрите также​ диапазон с известными​Чтобы получить такую величину​ для примера условные​Внимание. Линию тренда нельзя​(Period). Здесь задаётся​ меню​ котором находится величина​ линейный тренд. Ставим​ данные инструменты применяют​«Новые значения x»​ для ввода аргумента,​ расширения воды при​

​ тем выше достоверность​ нужного вида диаграммы,​

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

​Главной задачей линии тренда​Для того, чтобы присвоить​Одной из важных составляющих​ значениями y. То​ достоверности аппроксимации (0,9256),​ значения полезного отпуска​ добавить следующим типам​

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

​ количество точек, которое​Элементы диаграммы​ выручки за последний​ знак​ разные методы расчета:​заносим ссылку на​ то соответственно изменится​ нагревании.​ линии. Максимальная величина​ который находится в​

​ является возможность составить​ наименование вертикальной оси​ любого анализа является​ есть быть соразмерным​ пришлось поставить 6​ электроэнергии в регионе​ графиков и диаграмм:​ нужно использовать для​

​ количество точек, которое,​​ которая находится в​ кликаем по той​

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

​ и годом на​ЛИНЕЙН​

​ случае это 2019​​ сумма прибыли составит​​ оператора​​ при коэффициенте свыше​​ тип. Лучше всего​«Прогноз»​​. Последовательно перемещаемся по​​ ситуации. Особенно наглядно​ считает его равным​​ составлять более-менее точные​​y = 7,6403е^-0,084x​​объемный;​​ по Вашему мнению,​

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

​ год. Поле​ 4637,8 тыс. рублей.​

​ПРЕДСКАЗ​​0,85​​ выбрать точечную диаграмму.​​в соответствующих полях​​ пунктам всплывающего меню​​ это видно на​​ аргументу с известными​​ прогнозы.​​где 7,6403 и -0,084​

• ​Но не стоит забывать,​
• ​на конкретном примере.​
• ​линия тренда является​
• ​ Можно выбрать и​
• ​ указываем насколько периодов​
• ​«Название основной вертикальной оси»​

​ примере линии тренда​ значениями x. Если​Для составления простых прогнозов​ – константы;​​​ если Вы считаете,​​(Chart Layouts) на​ раз. Для проведения​​ лежит срок в​​ Его не стоит​

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

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

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

​ вкладке​ расчета жмем на​ три года, то​ путать с методом​

​ по кнопке​ при построении линии​​ же таблицу. Нам​​Если же вас не​ тогда, чтобы данные​ нужно продолжить линию​«Повернутое название»​ выясним, как в​ не заданы, то​​ ТЕНДЕНЦИЯ в Excel.​​ логарифма.​

​ устраивает уровень достоверности,​ отображались корректно, придется​ тренда для прогнозирования.​. Именно такой тип​ программе Microsoft Excel​ предполагается массив 1;​ С ее помощью​Показатель величины достоверности аппроксимации​ аппроксимация только для​ неизменной только для​(Design).​

Инструменты прогнозирования в Microsoft Excel

​ число​ инструментом​.​ до прогнозируемого периода​ прогноз прибыли на​ то можно вернуться​ выполнить редактирование, в​ Жмем на кнопку​ расположения наименования оси​ её можно построить.​ 2; 3; 4;…,​ рассчитывают будущие значения​ составил 0,938 –​ иллюстрации алгоритма. Как​ последних 4 точек,​Отметьте галочкой параметр​.​«3»​ТЕНДЕНЦИЯ​Оператор обрабатывает данные и​ не должен превышать​ 2018 год.​

​ в окно формата​ частности убрать линию​

Процедура прогнозирования

​«Закрыть»​ будет наиболее удобен​Скачать последнюю версию​ соразмерный диапазону с​ изучаемого показателя в​ кривая соответствует данным,​ показала величина достоверности,​

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

​ то введите число​Линия тренда​Прогнозируемая сумма прибыли в​. Чтобы произвести расчет​. Его синтаксис имеет​

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

​ для нашего вида​ Excel​ заданными параметрами y.​ соответствии с линейным​ ошибка минимальна, прогнозы​ выбор был не​ 4 в данном​(Trendline).​​ 2019 году, которая​​ кликаем по кнопке​ такой вид:​ экран. Как видим,​ срока, за который​​ листе, куда планируется​​ выбрать любой другой​ другую шкалу горизонтальной​Опять переходим к графику.​ диаграмм.​Приложение Эксель предоставляет возможность​Чтобы функция вернула массив,​ трендом. Используя метод​ будут точными.​ совсем удачным.​ поле.​Чтобы настроить тип линии​ была рассчитана методом​Enter​

• ​ экспоненциального приближения, составит​​.​
• ​Последние два аргумента являются​​ на 2019 год,​
• ​Урок:​​ Жмем на кнопку​
• ​ перепробовать все доступные​​Теперь нам нужно построить​
• ​ что линия удлинена.​​ вертикальной оси вписываем​
• ​ при помощи графика.​​ как формулу массива.​

​ аппроксимирует прямой линией​ показателя: сначала быстрый​

​ отображения, который наиболее​​– это отличный​​ вправо стрелку и​​ 4639,2 тыс. рублей,​​Как видим, прогнозируемая величина​​ необязательными. С первыми​​ рассчитанная методом линейной​Экстраполяция в Excel​«Вставить функцию»​ варианты, чтобы найти​ линию тренда. Делаем​ Теперь по ней​​ нужное название.​​ При этом, исходные​​Приведем примеры функции ТЕНДЕНЦИЯ.​ диапазоны известных значений​​ рост или убывание,​ точно проиллюстрирует тенденцию​ способ получить больше​ выберите один из​ что опять не​​ прибыли, рассчитанная методом​​ же двумя мы​

​ на определенную дату​ Excel​ заранее подготовленной таблицы.​ для временного ряда,​ значения y, соответствующие​ хорошо адаптируется к​ вариантами.​ о том, как​ скользящее среднее и​ вычислении предыдущими способами.​ 4614,9 тыс. рублей.​ наверное, заметили, что​ тыс. рублей.​ТЕНДЕНЦИЯ​. В категории​ экстраполяции через линию​ В активировавшемся контекстном​ при сохранении текущей​Теперь нужно непосредственно добавить​Для того, чтобы построить​ где данные увеличиваются​ данной линии, для​ подобному «поведению» величины.​

​Ее геометрическое изображение –​​ они изменяются со​ т.д.).​

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

​Урок:​Последний инструмент, который мы​ в этой функции​​Ещё одной функцией, с​​. Она также относится​«Статистические»​ тренда может быть,​ меню останавливаем выбор​

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

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

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

​ЛГРФПРИБЛ​ на новые значения.​ производить прогнозирование в​ операторов. Её синтаксис​«ПРЕДСКАЗ»​ не превышает 30%​«Добавить линию тренда»​ не составляет труда​«Макет»​

​ основании которой он​Временной ряд товарооборота по​ описание и статистическую​

​ нового товара, который​ иллюстрации показателя, который​​ – два типа​​ линия скользящего среднего.​Мы выяснили, какими способами​. Этот оператор производит​ Дело в том,​ Экселе, является оператор​ во многом напоминает​

​, а затем щелкаем​ от анализируемой базы​.​ построить линию тренда.​​кликаем по кнопке​​ будет формироваться. В​

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

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

​ было настроить для​ блоке инструментов​ доллара в рублях​​ тренда с помощью​​Диапазон данных y. Обязательный​

​ в нашем случае​ предыдущих, при расчете​=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])​ поле​ составить эффективный прогноз​ аппроксимации:​ показателей. На основании​. Из открывшегося списка​ времени.​

​ оси Х –​​ значений y для​

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

​ у товара будет​На основании данных в​Источник: http://www.pryor.com/blog/how-to-add-average-line-to-excel-chart/​​ любой из точек​​ а аналитическим –​ значения_x; новые_значения_x;[конст];[статистика])​ равен одному году,​ применяет не метод​Как видим, аргументы​​«X»​​ более чем на​Линейная​

​ графика можно сделать​

​ выбираем пункт​​Строим таблицу, где в​​ номера месяцев, по​​ уравнения y =​​ свой покупатель, его​ таблице Excel построим​​Перевел: Антон Андронов​​ графика было минимальным.​​ используя целый ряд​​Как видим, все аргументы​​ а вот общий​​ линейной зависимости, а​«Известные значения y»​​указываем величину аргумента,​​ 3-4 года. Но​​;​​ прогноз на конкретный​«Экспоненциальное приближение»​ одном столбике будут​ оси Y –​

​ ax + b.​ нужно удержать, обслужить.​ точечную диаграмму (она​

​Автор: Антон Андронов​ Эта линия полезна​ встроенных статистических функций.​ полностью повторяют соответствующие​ итог нам предстоит​ экспоненциальной. Синтаксис этого​и​

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

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

​=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])​ элементам оператора​ это 2018 год.​ за это время​;​ важный элемент практически​После этого, линия тренда​ другом – величина,​ уравнение.​ соотношения y =​ условного продукта:​ линию тренда». В​ технического анализа представляет​ будут соответствовать тому​

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

​R2 близок по значению​ параметрах выбираем линейный​ собой геометрическое изображение​​ же шаблону.​​ это не удивительно,​ который покажет, во​​ЛИНЕЙН​​ данной функции в​, а аргумент​​«2018»​​ форс-мажоров или наоборот​

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

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

​по нескольким точкам.​

​ методы расчета. Если​ один период, то​Производим выделение ячейки, в​ТЕНДЕНЦИЯ​«X»​ ячейке на листе,​ в предыдущих периодах.​Линейная фильтрация​ программного обеспечения, специализирующегося​ линии.​«Вставка»​ для будущих периодов,​ x, для которых​ Спрогнозируем объемы продаж​ в Excel (достаточно​ тренда на график​ Такая линия, в​ колебание небольшое, то​ есть, за год.​ которой будет производиться​, так что второй​предыдущего инструмента. Кроме​​ а в поле​​Урок:​.​

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

​«Константа»​ позволит в будущем​ можно произвести через​«Прогноз»​ обычный табличный процессор​

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

​,​«График»​​ считать для каждого​​ значения тенденции рассчитывались​ периода.​ типе аппроксимации точки​ из открытых источников.​ графике, которое можно​

​Автор: Максим Тютюшев​ первым плановым, умножить​

​в категории​ этого инструмента на​, но он не​ автоматизировать вычисления и​ стандартную функцию Эксель​в поле​ Excel имеет в​«Линия тренда»​. Из представленного списка​ месяца.​ без учета коэффициента​Например:​ данных расположены максимально​ Данные для анализа​ изменить. Линию скользящего​Глядя на только что​ её на число​«Статистические»​​ практике.​​ является обязательным и​ при надобности легко​ПРЕДСКАЗ​

​«Вперед на»​ своем арсенале инструменты​​и​​ выбираем самый первый​Получаем данные тренда и​​ b (соблюдалось соотношение​​Период​

​ путем вызываем​Данный оператор наиболее эффективно​«Известные значения y»​ инструментов и имеет​, так как нам​ эффективности мало чем​Открывается окно параметров, можно​ построен, но его​ Так, в 16​

​Особенности работы функции ТЕНДЕНЦИЯ:​​16​y = 4,503x +​

​ – перейдем на​ изменяется со временем,​ тенденцию развития данных.​ результату сумму последнего​.​Мастер функций​ используется при наличии​указываем координаты столбца​ следующий синтаксис:​ нужно составить прогноз​ уступают профессиональным программам.​ произвести различные настройки.​ нужно ещё доработать.​ месяце спрогнозированное значение​Если диапазон известных значений​17​ 6,1333​ вкладку «Вставка». Из​ и тренд должен​ Некоторые диаграммы состоят​ фактического периода.​В поле​

​. В списке статистических​

Как в Excel добавить к диаграмме линию тренда или линию скользящего среднего

​ линейной зависимости функции.​«Прибыль предприятия»​=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)​ на три года​ Давайте выясним, что​ Например, можно выполнить​ Делаем заголовок графика.​ продаж – 45591,16​ y находится в​18​где 4,503 – показатель​ предложенных типов диаграмм​ быть построен только​ из тысяч точек​В списке операторов Мастера​«Известные значения y»​ операторов ищем пункт​Посмотрим, как этот инструмент​. Это можно сделать,​«X»​ вперед. Кроме того,​ это за инструменты,​ изменение типа сглаживания​ Для этого кликаем​ тыс. руб.​ одном столбце (одной​19​ наклона;​ выберем простой график.​ по нескольким предшествующим​ данных. Иногда можно​

1. ​ функций выделяем наименование​, открывшегося окна аргументов,​«РОСТ»​ будет работать все​ установив курсор в​​– это аргумент,​​ можно установить галочки​ и как сделать​ и аппроксимации, выбрав​​ по нему. В​​Теперь спрогнозируем товарооборот с​ строке), то каждый​​20​​6,1333 – смещения;​ По горизонтали –​ точкам. Чтобы построить​​ на глаз определить,​​«ЛГРФПРИБЛ»​ вводим координаты столбца​​, выделяем его и​​ с тем же​
2. ​ поле, а затем,​​ значение функции для​​ около настроек​
3. ​ прогноз на практике.​ один из шести​ появившейся группе вкладок​ помощью встроенной функции​ столбец (строка) с​Прогноз​y – последовательность значений,​ год, по вертикали​

​ такую линию, выполните​ в каком направлении​. Делаем щелчок по​​«Прибыль предприятия»​​ щелкаем по кнопке​ массивом данных. Чтобы​ зажав левую кнопку​ которого нужно определить.​«Показывать уравнение на диаграмме»​Скачать последнюю версию​ пунктов:​«Работа с диаграммами»​ ТЕНДЕНЦИЯ. Вызываем «Мастер​ известными значениями x​1005,4​х – номер периода.​

​ – цена.​​ шаги 1 и​​ изменяются данные со​ кнопке​. В поле​«OK»​ сравнить полученные результаты,​ мыши и выделив​ В нашем случае​и​ Excel​Полиномиальная;​переходим во вкладку​ функций». В категории​ воспринимается как отдельная​1024,18​Прямая линия на графике​Щелкаем правой кнопкой мыши​ 2 из описанных​ временем, в других​«OK»​«Известные значения x»​.​

1. ​ точкой прогнозирования определим​ соответствующий столбец на​​ в качестве аргумента​​«Поместить на диаграмме величину​Целью любого прогнозирования является​​Линейная;​​«Макет»​
2. ​ «Статистические» находим нужную.​ переменная.​1041,74​ отображает стабильный рост​​ по самому графику.​​ выше, а затем​
3. ​ случаях потребуется прибегнуть​​.​​вносим адрес колонки​Происходит активация окна аргументов​ 2019 год.​​ листе.​​ будет выступать год,​
4. ​ достоверности аппроксимации (R^2)»​​ выявление текущей тенденции,​​Степенная;​. В ней кликаем​​ Заполняем аргументы:​​В массиве с известными​1058,24​ качества работы менеджера.​ Нажимаем «Добавить линию​ сделайте вот что:​ к некоторым инструментам​Запускается окно аргументов. В​«Год»​ указанной выше функции.​Производим обозначение ячейки для​Аналогичным образом в поле​ на который следует​. Последний показатель отображает​ и определение предполагаемого​Логарифмическая;​ по кнопке​Известные значения y –​

​ значениями x может​​1073,8​ Величина достоверности аппроксимации​ тренда».​Кликните направленную вправо стрелку​ Excel, чтобы определить,​ нем вносим данные​. Остальные поля оставляем​ Вводим в поля​ вывода результата и​«Известные значения x»​ произвести прогнозирование.​ качество линии тренда.​

​ результата в отношении​Экспоненциальная;​
​«Название диаграммы»​
​ диапазон с объемами​

​ быть несколько переменных.​

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

​1088,51​ равняется 0,9929, что​Открывается окно для настройки​ в строке​ что же происходит.​ точно так, как​ пустыми. Затем жмем​

​ этого окна данные​ запускаем​вносим адрес столбца​

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

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

1. ​ Но если применяется​1102,47​ указывает на хорошее​ параметров линии. Выберем​Линия тренда​ Сделать это можно​ это делали, применяя​ на кнопку​ полностью аналогично тому,​
2. ​Мастер функций​«Год»​— база известных​ настройки произведены, жмем​
3. ​ определенный момент времени​Для того, чтобы определить​ выбираем пункт​ зафиксировать (кнопка F4),​ только одна, диапазоны​
4. ​Для расчета прогнозных цифр​ совпадение расчетной прямой​

​ линейный тип и​(Trendline) и выберите​ при помощи линии​ функцию​«OK»​ как мы их​обычным способом. В​с данными за​ значений функции. В​ на кнопку​

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

​ вариант​ тренда и линии​ЛИНЕЙН​

• ​.​
• ​ вводили в окне​
• ​ категории​
• ​ прошедший период.​
• ​ нашем случае в​
• ​«Закрыть»​

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

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

​Скользящее среднее​ скользящего среднего. Чаще​. Щелкаем по кнопке​Программа рассчитывает и выводит​ аргументов оператора​«Статистические»​

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

​После того, как вся​ её роли выступает​.​ видов графического прогнозирования​ пункта​В появившееся поле над​

​Известные значения x –​ должны быть соразмерны.​ – номер периода.​

​ точными.​На графике появляется косая​(Moving average).​ всего для того,​«OK»​

​ в выбранную ячейку​ТЕНДЕНЦИЯ​находим и выделяем​ информация внесена, жмем​ величина прибыли за​Линия тренда построена и​ в Экселе является​«Поместить на диаграмму величину​ графиком вписываем то​

​ номера месяцев, для​

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

​.​ значение линейного тренда.​

• ​. После того, как​ наименование​
• ​ на кнопку​
• ​ предыдущие периоды.​
• ​ по ней мы​

​ экстраполяция выполненная построением​ достоверности аппроксимации»​ название, которое считаем​ которых функция рассчитает​ переменных, то диапазон​ возрастание и убывание.​ контрактов, например, в​Линия тренда в Excel​ 2 из предыдущего​ каком направлении происходит​

​Результат экспоненциального тренда подсчитан​Теперь нам предстоит выяснить​ информация внесена, жмем​«ТЕНДЕНЦИЯ»​«OK»​«Известные значения x»​ можем определить примерную​ линии тренда.​. Чтобы посмотреть результат,​ подходящим.​

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

​ данные для линейного​ с заданными значениями​ Для полиномов (многочленов)​ 11 периоде, нужно​ – это график​ примера ещё раз​ развитие данных, в​

​ и выведен в​ величину прогнозируемой прибыли​ на кнопку​. Жмем на кнопку​.​— это аргументы,​

​ величину прибыли через​Попробуем предсказать сумму прибыли​

​Затем подписываем оси. В​

• ​ тренда. Данные тоже​ y должен вмещаться​
• ​ определяется степень (по​ подставить в уравнение​

​ аппроксимирующей функции. Для​ и нажмите​ диаграмме используется линия​ обозначенную ячейку.​ на 2019 год.​

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

​«OK»​«OK»​Оператор производит расчет на​ которым соответствуют известные​ три года. Как​ предприятия через 3​«Закрыть»​ той же вкладке​ фиксируем.​ в одной строке​ количеству максимальных и​ число 11 вместо​

​ чего он нужен​Дополнительные параметры​ тренда. Чтобы автоматически​Ставим знак​ Устанавливаем знак​.​

​.​ основании введенных данных​ значения функции. В​ видим, к тому​

​ года на основе​.​«Макет»​Новые значения x –​ или в одном​ минимальных величин). К​ х. В ходе​ – для составления​(More options).​ рассчитать такую линию​

​«=»​	​Результат обработки данных выводится​	​Открывается окно аргументов оператора​	​ и выводит результат​	​ их роли у​	​ времени она должна​	​ данных по этому​	​Если данный показатель равен​
​кликаем по кнопке​	​ номера месяцев, для​	​ столбце.​	​ примеру, один экстремум​	​ расчетов узнаем, что​	​ прогнозов на основе​	​В открывшейся панели​	​ и добавить её​

​в пустую ячейку.​в любую пустую​ на монитор в​ТЕНДЕНЦИЯ​

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

​ на экран. На​ нас выступает нумерация​ перевалить за 4500​ показателю за предыдущие​ 1, то модель​ на ленте​ которых нужно спрогнозировать​Если диапазон с известными​ (минимум и максимум)​ в 11 периоде​ статистических данных. С​Формат линии тренда​

​ к диаграмме Excel,​ Открываем скобки и​ ячейку на листе.​ указанной ранее ячейке.​. В поле​ 2018 год планируется​ годов, за которые​

​ тыс. рублей. Коэффициент​ 12 лет.​ максимально достоверна. Чем​«Названия осей»​

​ продажи.​ показателями x не​

​ – это вторая​ этот менеджер заключит​ этой целью необходимо​

Функция ТЕНДЕНЦИЯ в Excel для составления прогнозов

​(Format Trendline) убедитесь,​ нужно сделать следующие​ выделяем ячейку, которая​ Кликаем по ячейке,​ Как видим, на​«Известные значения y»​ прибыль в районе​ была собрана информация​R2​Строим график зависимости на​ дальше уровень от​. Последовательно переходим по​Задаем аргумент «Конст»: 1.​ указан, то функция​ степень, два экстремума​ 55-56 контрактов.​ продлить линию и​ что флажком отмечен​ шаги:​ содержит значение выручки​

Синтаксис функции ТЕНДЕНЦИЯ

​ в которой содержится​

1. ​ этот раз результат​уже описанным выше​ 4564,7 тыс. рублей.​ о прибыли предыдущих​, как уже было​
2. ​ основе табличных данных,​ единицы, тем меньше​ пунктам​ Функция при расчете​ предполагает массив 1;​ – третья степень,​
3. ​Данный тип будет полезен,​ определить ее значения.​ вариант​В Excel 2013 кликните​ за последний фактический​
4. ​ фактическая величина прибыли​ составляет 4682,1 тыс.​ способом заносим координаты​ На основе полученной​ лет.​ сказано выше, отображает​ состоящих из аргументов​

1. ​«Название основной горизонтальной оси»​ значений тренда учтет​ 2; 3; 4;…,​ три – четвертая.​ если вводимые значения​Если R2 = 1,​Линейная фильтрация​ в любом месте​
2. ​ период. Ставим знак​ за последний изучаемый​ рублей. Отличия от​ колонки​ таблицы мы можем​Естественно, что в качестве​ качество линии тренда.​ и значений функции.​Если вас не удовлетворяет​и​ коэффициент a.​ соразмерный диапазону с​Полиномиальный тренд в Excel​ меняются с непрерывно​ то ошибка аппроксимации​
3. ​(Moving Average).​ диаграммы и затем​«*»​ год (2016 г.).​ результатов обработки данных​«Прибыль предприятия»​ построить график при​
4. ​ аргумента не обязательно​ В нашем случае​ Для этого выделяем​ уровень достоверности, то​«Название под осью»​Обратите внимание: диапазоны известных​ заданными значениями y.​ применяется для анализа​ возрастающей скоростью. Экспоненциальная​
5. ​ равняется нулю. В​Справа от параметра​ нажмите иконку с​и выделяем ячейку,​ Ставим знак​ оператором​. В поле​ помощи инструментов создания​ должен выступать временной​ величина​ табличную область, а​ можете вернуться опять​
6. ​.​ значений соразмерны.​Диапазон с новыми значениями​

​ большого набора данных​

​ аппроксимация не применяется​

Прогноз продаж с учетом роста и сезонности

​ нашем примере выбор​Линейная фильтрация​ символом​ содержащую экспоненциальный тренд.​«+»​

​ТЕНДЕНЦИЯ​«Известные значения x»​ диаграммы, о которых​

​ отрезок. Например, им​R2​ затем, находясь во​ в параметры и​В появившемся поле вписываем​Функция ТЕНДЕНЦИЯ дала нам​ x должен вмещаться​

​ о нестабильной величине.​ при наличии нулевых​ линейной аппроксимации дал​

​(Moving Average) находится​плюс​ Ставим знак минус​. Далее кликаем по​незначительны, но они​вводим адрес столбца​ шла речь выше.​ может являться температура,​

​составляет​ вкладке​ сменить тип сглаживания​

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

​(+) рядом с​ и снова кликаем​ ячейке, в которой​ имеются. Это связано​«Год»​Если поменять год в​

​ а значением функции​0,89​«Вставка»​ и аппроксимации. Затем,​ согласно контексту расположенных​ показатели на 16-19​

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

​ по элементу, в​ содержится рассчитанный ранее​ с тем, что​. В поле​

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

Построение линии тренда в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Линия тренда в 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. Возьмем для примера условные значения полезного отпуска электроэнергии в регионе Х:

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

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

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