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

Как поступить в случае, если для определенных объемов/размеров продукции хронометражные замеры отсутствуют? Или число замеров недостаточно, а дополнительные наблюдения в ближайшее время осуществить невозможно? Наилучший способ решения данной проблемы – построение расчетных зависимостей (уравнений регрессии) с помощью линий тренда в MS Excel.

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

Впоследствии возникла необходимость определения затрат времени на отборку 0,6 и 0,9 м3 товара/заказа. В связи с невозможностью проведения дополнительных хронометражных исследований затраты времени на отборку данных объемов заказа были рассчитаны с помощью уравнений регрессии в MS Excel. Для этого таблица 1 была преобразована в таблицу 2.

Выбор точечной диаграммы, рис. 1

Следующий шаг: курсор мыши был установлен на одной из точек графика и с помощью правой кнопки мыши было вызвано контекстное меню, в котором был выбран пункт: «добавить линию тренда» (рис.2).

Добавление линии тренда, рис. 2

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

Формат линии тренда, рис. 3

В результате были получены графики, представленные на рис. 4 и 5.

Линейная расчетная зависимость, рис. 4

Степенная расчетная зависимость, рис. 5

Наглядный анализ графиков однозначно свидетельствует о близости полученных зависимостей. Кроме того, величина достоверности аппроксимации (R^2), которую также называют коэффициентом детерминации, в случае обеих зависимостей составляет одну и ту же величину 0,97. Известно, что чем ближе коэффициент детерминации к 1, тем больше линия тренда соответствует действительности. Также можно констатировать, что изменение затрат времени на обработку заказа на 97% объясняется изменением количества товара. Поэтому в данном случае не принципиально: какую расчетную зависимость выбрать в качестве основной для последующего расчета временных затрат.

Примем за основную - линейную расчетную зависимость. Тогда значения затрат времени в зависимости от количества товара будут определяться по формуле: y = 54,511x + 0,1489. Результаты этих расчетов для количества товара, по которому ранее были проведены хронометражные наблюдения, представлены в таблице 3 ниже.

Определим среднее отклонение затрат времени, рассчитанных по уравнению регрессии от затрат времени, рассчитанных по данным хронометражных наблюдений: (-0,05+0,10-0,05+0,01)/4=0,0019. Таким образом, затраты времени, рассчитанные по уравнению регрессии отличаются от затрат времени, рассчитанных по данным хронометражных наблюдений всего на 0,19%. Расхождение данных ничтожно мало.

По формуле: y = 54,511x + 0,1489 установим затраты времени для количества товара, по которому ранее не были проведены хронометражные наблюдения (таблица 4).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

где y – исследуемая переменная (например, производительность) или зависимая переменная;
x – число, определяющее позицию (второй, третий и т.д.) года в периоде прогнозирования или независимая переменная.

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

где n – объем исследуемой совокупности (число единиц наблюдений).

Рис. 5.3. Построение тренда методом наименьших квадратов

Значения констант b и a или коэффициента при переменной Х и свободного члена уравнения определяются по формуле:

В табл. 5.1 приведен пример вычисления линейного тренда по данным .

Таблица 5.1. Вычисление линейного тренда

Методы сглаживания колебаний.

При сильных расхождениях между соседними значениями тренд, полученный методом регрессии, трудно поддается анализу. При прогнозировании, когда ряд содержит данные с большим разбросом колебаний соседних значений, следует их сгладить по определенным правилам, а потом искать смысл в прогнозе. К методу сглаживания колебаний
относят: метод скользящих средних (рассчитывается n-точечное среднее), метод экспоненциального сглаживания. Рассмотрим их.

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

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

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

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

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

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

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

Метод экспоненциального сглаживания.

Альтернативный подход к сокращению разброса значений ряда состоит в использовании метода экспоненциального сглаживания. Метод получил название «экспоненциальное сглаживание» в связи с тем, что каждое значение периодов, уходящих в прошлое, уменьшается на множитель (1 – α).

Каждое сглаженное значение рассчитывается по формуле вида:

St =aYt +(1−α)St−1,

где St – текущее сглаженное значение;
Yt – текущее значение временного ряда; St – 1 – предыдущее сглаженное значение; α – сглаживающая константа, 0 ≤ α ≤ 1.

Чем меньше значение константы α , тем менее оно чувствительно к изменениям тренда в данном временном ряду.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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