Построение функции тренда в Excel. Резвый прогноз без учета сезонности

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

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

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

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

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

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

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

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

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

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

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

Данные для прогноза

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

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

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

В Гугл 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 улучшился.

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

Интересно почитать: Как в excel закрепить несколько строк

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

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

Microsoft Office Excel кроме математических вычислений дозволяет проводить статистический анализ данных и производить предстоящий прогноз. Это может быть благодаря огромным вычислительным мощностям, также спец инструментам, которые интегрированы в редакторе. Сейчас разберемся, как смотрится линия тренда в excel на графике и как ее построить.

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

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

Составляете маленькую таблицу.

На основании этих данных строите линейный график. Для этого перебегайте во вкладку Вставка на Панели инструментов и выбираете подходящий тип диаграммы.

Выходит некая кривая.

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

Чтоб добавить линию тренда, нужно во вкладке Макет надавить одноименную клавишу и избрать подходящий тип приближения.

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

6. Так смотрится линия тренда на графике.

На заметку! Построение полосы приближения идентично для редакторов 2007, 2010 и 2016 годов выпуска.

Способности инструмента

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

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

Характеристики полосы тренда можно условно поделить на четыре блока:

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

Выведем на начальный график уравнение полосы и коэффициент достоверности.

Как видите, значение близко к 0,5, это гласит о низкой достоверности приобретенной полосы тренда, и предстоящий прогноз будет неверным.

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

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

Интересно почитать: Как в excel сделать строки одинаковой высоты

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Уравнение полосы тренда в Excel

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

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

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

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

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

Интересно почитать: Как в excel убрать дублирующие строки

На основании данных в таблице 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

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

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