Как в эксель построить линию тренда. Решение задач аппроксимации средствами Excel

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

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

Предусмотрено несколько вариантов формирования линии трен-да.

Линейной функцией: y=mx+b

где m — тангенс угла наклона прямой, b — смещение.

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

Логарифмической функцией: y=c*ln⁡x+b

где с и b — константы.

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

Полиномиальной функцией (до 6-й степени включительно): y= b + c 1 *x + c 2 *x 2 + c 3 *x 3 + ...+ c 6* x 6

где b, c 1 , c 2 , ... c 6 — константы.

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

Степенной функцией: y = cxb

где c и b — константы.

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

Экспоненциальной функцией: y = cebx

где c и b — константы, е — основание натурального логарифма.

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

С использованием линейной фильтрации по формуле: F t = (A t +A (t-1) +⋯+A (t-n+1))/n

где n — общее число членов ряда, t — заданное число точек (2 ≤ t < n).

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

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

Линия тренда, а также ее параметры добавляются к данным диа-граммы следующими командами:

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

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

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

Тренд как модель

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

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

Важно!

В расчетах под t обычно подразумевается не год, номер месяца или недели, а именно порядковый номер периода в изучаемой статистической совокупности – динамическом ряде. К примеру, если динамический ряд изучается за несколько лет, а данные фиксировались ежемесячно, то использовать обнуляющуюся нумерацию месяцев, с 1 по 12 и опять сначала, в корне неверно. Также неверно в случае, если изучение ряда начинается, к примеру, с марта месяца в качестве значения t использовать 3 (третий месяц в году), если это первое значение в изучаемой совокупности, то его порядковый номер должен быть 1.

Модель линейного тренда

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

Общий вид уравнения линейного тренда:

Y(t) = a 0 + a 1 *t + Ɛ

Где a 0 – это нулевой коэффициент регрессии, то есть, то каким будет Y в случае, если влияющий фактор будет равен нулю, a 1 – коэффициент регрессии, который выражает степень зависимости исследуемого показателя Y от влияющего фактора t, Ɛ – случайная компонента или стандартная ошибка, по сути являет собой разницу между реально существующими значениями Y и расчетными. t – это единственный влияющий фактор – время.

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

Рассчитать коэффициенты модели можно стандартным Методом наименьших квадратов (МНК). Со всеми этими расчетами Microsoft Excel справляется на ура самостоятельно, при чем, чтобы получить модель линейного тренда либо готовый прогноз существует целых пять способов, которые мы по отдельности разберем ниже.

Графический способ получения линейного тренда

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

Добавим к исходным данным еще один столбец, который назовем t и пометим цифрами по возрастающей порядковые номера всех зафиксированных значений ВВП за указанный период с 2004-го по 2012-й гг. – 9 лет или 9 периодов .

Эксель добавит пустое поле – разметку под будущий график, выделяем этот график и активируем появившуюся вкладку в панели меню – Конструктор , ищем кнопку Выбрать данные , в отрывшемся окне жмем кнопочку Добавить . Всплывшее окошко предложит выбрать данные для построения диаграммы. В качестве значения поля Имя ряда выбираем ячейку, которая содержит текст, наиболее полно отвечающий названию графика. В поле Значения X указываем интервал ячеек стобца t – влияющего фактора. В поле Значения Y указываем интервал ячеек столбца с известными значениями ВВП (Y) – исследуемого показателя.

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

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

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

Построение линейного тренда с помощью формулы ЛИНЕЙН

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

Нам потребуется выделить две рядом стоящие ячейки (на скриншоте это ячейки A38 и B38), далее в строке формул вверху (выделено красным на скриншоте выше) вызываем функцию, написав «=ЛИНЕЙН(», после чего эксель выведет подсказки того, что требуется для этой функции, а именно:

1. выделяем диапазон с известными значениями описываемого показателя Y (в нашем случае ВВП, на скриншоте диапазон выделен синим) и ставим точку с запятой
2. указываем диапазон влияющих факторов X (в нашем случае это показатель t, порядковый номер периодов, на скриншоте выделено зеленым) и ставим точку с запятой
3. следующий по порядку требуемый параметр для функции – это определение того нужно ли рассчитывать константу, так как мы изначально рассматриваем модель с константой (коэффициент a 0 ), то ставим либо «ИСТИНА» либо «1» и точку с запятой
4. далее нужно указать требуется ли расчет параметров статистики (в случае, если бы мы рассматривали этот вариант, то изначально пришлось бы выделить диапазон «под формулу» на несколько строк ниже). Указывать необходимость расчета параметров статистики, а именно стандартного значение ошибки для коэффициентов, коэффициента детерминированности, стандартной ошибки для Y, критерия Фишера, степеней свободы и пр. , есть смысл только тогда, когда вы понимаете, что они означают, в этом случае ставим либо «ИСТИНА», либо «1». В случае упрощенного моделирования, которому мы пытаемся научиться, на этом этапе прописывания формулы, ставим «ЛОЖЬ» либо «0» и добавляем после закрывающую скобочку «)»
5. чтобы «оживить» формулу, то есть заставить ее работать после прописывания всех необходимых параметров, не достаточно нажать кнопку Enter, необходимо последовательно зажать три клавиши: Ctrl, Shift, Enter

Как видим на скриншоте выше, выделенные нами под формулу ячейки заполнились расчетными значениями коэффициентов регрессии для линейного тренда, в ячейке B38 находится коэффициент a 0 , а в ячейке A38 - коэффициент зависимости от параметра t (или x ), то есть a 1 . Подставляем полученные значения в уравнение линейной функции и получаем готовую модель в математическом выражении – y = 169 572,2+138 454,3*t

Чтобы получить расчетные значения Y по модели и, соответственно, чтобы получить прогноз, нужно просто подставить формулу в ячейку экселя, а вместо t указать ссылку на ячейку с требуемым номером периода (смотрите на скриншоте ячейку D25 ).

Для сравнения полученной модели с реальными данными, можно построить два графика, где в качестве Х указать порядковый номер периода, а в качестве Y в одном случае – реальный ВВП, а, в другом – расчетный (на скриншоте диаграмма справа).

Построение линейного тренда с помощью инструмента Регрессия в Пакете анализа

В статье , по сути, полностью описан этот метод, единственная же разница в том, что в наших исходных данных только один влияющий фактор Х (номер периода – t ).

Как видно на рисунке выше, диапазон данных с известными значениями ВВП выделен как входной интервал Y , а соответствующий ему диапазон с номерами периодов t – как входной интервал Х . Итоги расчетов Пакетом анализа выносятся на отдельный лист и выглядит как набор таблиц (см. рисунок ниже) на котором нас интересуют ячейки, которые были закрашены мною в желтый и зеленый цвета. По аналогии с порядком, расписанным в указанной выше статье, из полученных коэффициентов собирается модель линейного тренда y=169 572,2+138 454,3*t , на основе которой и делаются прогнозы.

Прогнозирование с помощью линейного тренда через функцию ТЕНДЕНЦИЯ

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

В целевую ячейку (ту ячейку, где хотим видеть результат) ставим знак равно и вызываем волшебную функцию, прописав «ТЕНДЕНЦИЯ(», далее необходимо выделить , то есть , после ставим точку с запятой и выделяем диапазон с известными значениями Х, то есть с номерами периодов t , которые соответствуют столбцу с известными значениями ВВП, опять ставим точку с запятой и выделяем ячейку с номером периода, для которого мы делаем прогноз (правда, в нашем случае, номер периода можно указать не ссылкой на ячейку, а просто цифрой прямо в формуле), далее ставим еще одну точку с запятой и указываем ИСТИНА или 1 , в качестве подтверждения для расчета коэффициента a 0 , наконец, ставим закрывающую скобочку и нажимаем клавишу Enter .

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

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

Суть данной функции целиком и полностью идентична предыдущей, разница лишь в порядке прописывания исходных данных в формуле и в том, что нет настройки для наличия или отсутствия коэффициента a 0 (то есть функция подразумевает, что этот коэффициент, в любом случае, есть)

Как видно с рисунка выше, в целевую ячейку прописываем «=ПРЕДСКАЗ(» и затем указываем ячейку с номером периода , для которого необходимо просчитать значение по линейному тренду, то есть прогноз, после ставим точку с запятой, далее выделяем диапазон известных значений Y , то есть столбец с известными значениями ВВП , после ставим точку с запятой и выделяем диапазон с известными значениями Х , то есть с номерами периодов t , которые соответствуют столбцу с известными значениями ВВП и, наконец, ставим закрывающую скобочку и жмем клавишу Enter .

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

Подводя итог к статье

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

y = 4,503x + 6,1333

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

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

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

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

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

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

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

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

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

• где 7,6403 и -0,084 – константы;
• е – основание натурального логарифма.

Показатель величины достоверности аппроксимации составил 0,938 – кривая соответствует данным, ошибка минимальна, прогнозы будут точными.

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

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

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

Построим график и добавим логарифмическую линию тренда для прогноза продаж условного продукта:

R2 близок по значению к 1 (0,9633), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.

Например:

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

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

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

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

Чтобы получить такую величину достоверности аппроксимации (0,9256), пришлось поставить 6 степень.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как поступить в случае, если для определенных объемов/размеров продукции хронометражные замеры отсутствуют? Или число замеров недостаточно, а дополнительные наблюдения в ближайшее время осуществить невозможно? Наилучший способ решения данной проблемы – построение расчетных зависимостей (уравнений регрессии) с помощью линий тренда в 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 – это отличный способ установления затрат времени по операциям, которые в силу различных причин не были охвачены хронометражными наблюдениями.