Как составить таблицу тренда

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   

2. ​(Chart elements). Другой​ период. Закрываем скобку​​«*»​​ метод линейной зависимости​ ячейку, где находится​​ результат, а также​​При вычислении данным способом​​ его может быть​​ блоке​ по ней прогноз​ также используем вкладку​

   

3. ​ определение основной тенденции​ независимым переменным.​ степень.​ Х:​лепестковый;​ вычисления средних значений​ вариант: нажмите кнопку​​ и вбиваем символы​​. Так как между​​ и метод экспоненциальной​​ номер года, на​ автоматически обновится график.​​ используется метод линейной​​ равной​«Диаграммы»​​ дальнейшего развития событий.​​«Макет»​

   

4. ​ событий. Имея эти​Если аргумент с новыми​Скачать примеры графиков с​Строим график. Добавляем экспоненциальную​

   

5. ​круговой;​ для построения линии​​Добавить элемент диаграммы​​«*3+»​ последним годом изучаемого​​ зависимости.​​ который нужно указать​ Например, по прогнозам​​ регрессии.​​1​​. Затем выбираем подходящий​​Опять переходим в параметры.​

   

6. ​. Кликаем по кнопке​ данные можно составить​ значениями x не​ линией тренда​

   

7. ​ линию.​поверхностный;​ тренда. Установите такое​​(Add Chart Elements),​​без кавычек. Снова​​ периода (2016 г.)​​Оператор​ прогноз. В нашем​​ в 2019 году​​Давайте разберем нюансы применения​​. Принято считать, что​​ для конкретной ситуации​ В блоке настроек​«Название осей»​ прогноз дальнейшего развития​ указан, то функция​

   
8. ​Зато такой тренд позволяет​Уравнение имеет следующий вид:​кольцевой;​

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

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

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

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

   

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

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

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

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

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

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

   

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

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

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

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

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

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

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

​Enter​

lumpics.ru

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

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

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

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

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

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

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

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

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

   

2. ​=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])​ сумма прогнозируемой прибыли​ накапливалась база данных.​ выводить результат обработки.​ тип аппроксимации. Можно​ оси.​ В нем видно,​В появившемся поле наименования​​ построение линии тренда​​ формулу нужно вводить​

   

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

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

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

   

4. ​ зависимости, составит, как​Для прогнозирования можно использовать​.​ наиболее точный.​ щелчок правой кнопкой​ можно определить, какой​Урок:​ данные для его​​​ y и известных​​ потом – относительная​ изменений вводимых пользователем​ информации об имеющемся​ предложенных вариантов (линейная,​ сильно отличается от​​ линейного приближения, в​​ знакомы по предыдущим​​ и при предыдущем​​ ещё одну функцию​Открывается​Нужно заметить, что эффективным​ мыши по любой​ приблизительный показатель прогнозируется​​Как сделать график в​​ формирования берутся из​Функцию ТЕНДЕНЦИЯ хорошо использовать​​ значений x. Прогнозирует​​ стабильность. Оптимизированная кривая​ данных. Разберемся с​

   

5. ​ наборе данных и​ экспоненциальная, линейный прогноз,​ результатов, полученных при​ 2019 году составит​ способам. Но вы,​ методе расчета, 4637,8​ –​Мастер функций​ прогноз с помощью​ из точек диаграммы.​

   

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

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

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

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

​ тенденции.​

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

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

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

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

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

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

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

   
2. ​ месяцам с двумя​​ характеристику модели тренда​​ только вводится на​​ растет или уменьшается​​ линий тренда, наиболее​​Линейный тренд​​ можно произвести прогнозирование​ расчеты на основе​​ что данный инструмент​​ РОСТ. Он тоже​

   

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

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

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

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

   

4. ​ производителя – увеличение​ менеджером контрактов на​Урок подготовлен для Вас​ образом, чтобы расстояние​ можно сделать через​ следующую структуру:​ единицу периода, который​ в отличие от​ образом:​Запускается окно аргументов. В​ мы не можем​ из шести видов​ максимально корректного отображения​

   

5. ​«Анализ»​ за определенный период​ графика Excel. По​ аргумент. Массив известных​ клиентской базы. Когда​ протяжении 10 месяцев:​ командой сайта office-guru.ru​ от неё до​ применение линии тренда,​= ЛГРФПРИБЛ (Известные значения_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.​ нужно удержать, обслужить.​ точечную диаграмму (она​

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

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

   

2. ​ элементы предыдущей функции.​​ подсчитать отдельно, прибавив​​ инструмента выглядит таким​​«Известные значения x»​​ отыскать значение функции.​ случае он будет​;​​Автор: Максим Тютюшев​​«Линейное приближение»​​ (в нашем случае​​Добавим на график линию​​ аргумент, включающий массив​​ логарифмическую линию тренда​​ тип):​​ изменения цены применяется​ если есть уверенность,​ идентичных данных этими​ Алгоритм расчета прогноза​ к последнему фактическому​ образом:​полностью соответствуют аналогичным​​ В нашем случаем​​ относительно достоверным, если​Экспоненциальная​​Прогнозирование – это очень​​.​

   

3. ​ даты), а в​ тренда и его​ уже известных для​ для прогноза продаж​Выделяем диаграмму – «добавить​ линия тренда. Элемент​ что последующие данные​ операторами может получиться​ немного изменится. Функция​ значению прибыли результат​

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

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

​ разный итог. Но​

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

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

   
2. ​;​ начиная от экономики​ По умолчанию она​ отображаться в графике.​ нужно рассчитать показатели​ значений x.​ к 1 (0,9633),​ тип. Добавляем величину​​ средних значений анализируемого​​Очень полезна​ так как все​ сколько раз поменяется​​, умноженный на количество​​ точности повторяют аргументы​

   

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

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

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

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

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

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

   

2. ​ раз на их​​ того, у​​«X»​Как построить линию тренда​​Давайте для начала выберем​​ направлении. К сожалению,​​«Макет»​​ в блоке инструментов​​ y = 490,26x​​ y.​ Для этого нужно​ внизу окна «Параметры»).​​Для примера возьмем средние​​ тренда, показывает усреднённую​

   
3. ​ применимые к конкретному​ найти разницу в​ Мастер функций. Выделяем​

   

4. ​ описании останавливаться не​ТЕНДЕНЦИЯ​просто дать ссылку​ в Excel​​ линейную аппроксимацию.​​ далеко не все​по пунктам меню​«Диаграммы»​ + 37747.​Константа. Необязательное логическое значение.​ в уравнение вместо​Получаем результат:​ цены на нефть​​ тенденцию по заданному​​ случаю, можно считать​ прибыли между последним​ наименование​ будем, а сразу​имеется дополнительный аргумент​​ на него. Это​​Экстраполяцию для табличных данных​В блоке настроек​ пользователи знают, что​«Анализ»​кликаем по кнопке​Показатели линейного тренда будем​ Если нужно, чтобы​ х подставлять номер​Обратите внимание! При линейном​ с 2000 года​​ числу точек на​​ относительно достоверными.​ фактическим периодом и​​«ЛИНЕЙН»​​ перейдем к применению​

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

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

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

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

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

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

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

   

3. ​. Этот аргумент относится​устанавливаем число​ для выполнения прогнозирования,​

   

4. ​«Дополнительные параметры линии тренда…»​​ вариант.​​ для будущих периодов:​ y = ax),​14​ Данный вид использует​Построим на основе таблицы​ формула, предоставляющая данные​​ Excel не всегда​​(3)​ кнопку​ и уже привычным​ наличии постоянных факторов.​В поле​ к категории статистических​«3,0»​ которые по своей​.​​После этого график будет​​ 16 – 19.​ ставим 0.​15​ следующее уравнение:​ график. Выделим диапазон​ для построения графика,​ легко сразу понять​​и прибавить к​​«OK»​

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

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

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

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

lumpics.ru

Как в 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»​ произвести прогнозирование.​ качество линии тренда.​

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

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

office-guru.ru

Линия тренда в 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 не​

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

exceltable.com

Функция ТЕНДЕНЦИЯ в 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​

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

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

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

​ сформировать коэффициент заново.​ на ней данных.​

​ периоды.​ столбцов, как и​ (цены на нефть).​ в Excel. Возьмем​

exceltable.com

​ будет неточным.​

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

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

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

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

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, например, это может быть сезонность.

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

Чтобы рассчитать продажи за 10-ый месяц, подставляем в функцию тренда 10 вместо x. То есть,

y = 4856*10 + 105104

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

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

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

Ссылка на пример в Google Sheets

Примечание: Эти действия применимы Office 2013 и более новых версиях. Ищете по Office 2010 г.?

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

1. Выделите диаграмму.

2. Щелкните значок “+” в правом верхнем углу диаграммы.

3. Выберите пункт Линия тренда.

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

4. В диалоговом окне Добавление линии тренда выберите нужные параметры рядов данных и нажмите кнопку ОК.

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

1. Щелкните в любом месте диаграммы.

2. На вкладке Формат в группе Текущий выделение выберите в списке вариант линии тренда.

3. Нажмите кнопку Формат выделения.

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

5. Задайте значение в полях “Вперед” и “Назад”, чтобы проецируемые данные в будущем.

Добавление линии скользящего среднего

Линию тренда можно отформатирование на линию скользящего среднего.

1. Щелкните в любом месте диаграммы.

2. На вкладке Формат в группе Текущий выделение выберите в списке вариант линии тренда.

3. Нажмите кнопку Формат выделения.

4. В области Формат линии тренда в области Параметры линии трендавыберите Скользящие средниезначения . При необходимости укажите баллы.

   Примечание: Число точек в линии тренда скользящего среднего равно общему числу точек в ряду, которое меньше числа, заданная для точки.

Добавление линии тренда или скользящего среднего на диаграмму в Office 2010 г.

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

   1. Щелкните диаграмму.

      Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

   2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.

      

2. Примечание: Если выбрана диаграмма с несколькими рядами данных, но сам ряд данных не выбран, откроется диалоговое окно Добавление линии тренда. В поле со списком выберите нужный ряд данных, а затем нажмите кнопку ОК.

3. На вкладке Макет в группе Анализ выберите пункт Линия тренда.

   

4. Выполните одно из указанных ниже действий.

   1. Выберите подходящий предопределенный параметр линии тренда.

      Примечание: Линия тренда будет применена без возможности выбора конкретных параметров.

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

   Используемый тип	Применение
   Линейная	Создание прямой линии тренда путем расчета по методу наименьших квадратов с помощью следующего уравнения: где m — это наклон, а b — смещение.
   Логарифмическая	Построение логарифмической линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения: где c и b — константы, а «ln» — натуральный логарифм.
   Полиномиальная	Построение полиномиальной или криволинейной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения: где b и являются константами.
   Степенная	Построение степенной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения: где c и b — константы. Примечание: При наличии нулевых или отрицательных значений данных этот параметр недоступен.
   Экспоненциальная	Построение экспоненциальной линии тренда путем расчета точек методом наименьших квадратов с помощью следующего уравнения: где c и b — константы, e — основание натурального логарифма. Примечание: При наличии нулевых или отрицательных значений данных этот параметр недоступен.
   Линейная фильтрация	Построение линии тренда с линейной фильтрацией по следующей формуле: Примечание: Число точек, образующих линию тренда с скользящее среднее, равно общему числу точек ряда за вычетом числа, указанного для параметра “Точки”.
   Величина достоверности аппроксимации	Построение линии тренда с указанием на диаграмме величина достоверности аппроксимации, вычисляемой по следующей формуле: Этот параметр линии тренда располагается на вкладке Параметры диалогового окна Добавление линии тренда или Формат линии тренда. Примечание: Отображаемая вместе с линией тренда величина достоверности аппроксимации не является скорректированной. Для логарифмической, степенной и экспоненциальной линий тренда в Excel используется видоизмененная модель регрессии.

   • Если выбран тип Полиномиальная, введите в поле Степень наибольшую степень для независимой переменной.

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

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

   • При добавлении линии тренда в график, гистограмму, диаграмму с областями или линейчатую диаграмму линия тренда рассчитывается исходя из предположения, что значения x равны 1, 2, 3, 4, 5, 6 и т. д. Такое допущение делается вне зависимости от того, являются ли значения x числовыми или текстовыми. Чтобы рассчитать линию тренда на основе числовых значений x, следует использовать точечную диаграмму.

   • В Excel название линии тренда назначается автоматически, но его можно изменить. В диалоговом окне Формат линии тренда в категории Параметры линии тренда в разделе Название аппроксимирующей (сглаженной) кривой выберите параметр Другое, а затем укажите название в поле Другое.

Советы: 

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

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

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

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

   1. Щелкните диаграмму.

      Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

   2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.

      

2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.

   

3. Чтобы изменить параметры цвета, типа или тени линии тренда, выберите категорию Цвет линии, Тип линии или Тень и задайте нужные значения.

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

   1. Щелкните диаграмму.

      Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

   2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.

      

2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.

   

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

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

   1. Щелкните диаграмму.

      Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

   2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.

      

2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.

   

3. Установите флажок пересечение кривой с осью Y в точке, а затем в поле пересечение кривой с осью Y в точке введите значение, чтобы задать точку пересечения линии тренда с вертикальной осью (осью значений).

   Примечание: Это можно сделать только при использовании экспоненциальной, прямой или полиномиальной линии тренда.

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

   1. Щелкните диаграмму.

      Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

   2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.

      

2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.

   

3. Чтобы показать на диаграмме уравнение линии тренда, установите флажок показывать уравнение на диаграмме.

   Примечание: Уравнения линии тренда нельзя показать для скользящего среднего.

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

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

   1. Щелкните диаграмму.

      Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

   2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.

      

2. На вкладке Макет в группе Анализ выберите пункт Линия тренда, а затем нажмите Дополнительные параметры линии тренда.

   

3. На вкладке Параметры линии тренда установите флажок поместить на диаграмму величину достоверности аппроксимации (R^2).

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

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

   1. Щелкните диаграмму.

      Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

   2. На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.

      

2. Выполните одно из указанных ниже действий.

   1. На вкладке Макет в группе Анализ нажмите Линия тренда, а затем выберите пункт Нет.

      

   2. Нажмите клавишу DELETE.

Совет: Вы также можете удалить линию тренда сразу после ее добавления на диаграмму, нажав кнопку Отменить панели быстрого доступа или нажав CTRL+Z.

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

Общая информация

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

Разновидности

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

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

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

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

Полиномиальная

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

Логарифмическая

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

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

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

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

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

Как добавить линию тренда

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

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

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

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

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

Следующий этап – создание линии тренда. Порядок действий:

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

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

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

Заключение

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