Метод касательных на графике в excel
Обновлено: 08.07.2024
ABC-анализ широко используется в различных областях экономики: при управлении запасами, выборе поставщиков, работе с клиентами и т. п. [1, 2]. Он основан на эмпирическом законе Парето (принципе 80/20), согласно которому наибольший вклад в суммарный результат S какой-либо деятельности или активности дают усилия небольшого числа ее участников [1]. Под участником (игроком) будем понимать любой объект или субъект, вклад которого учитывается в суммарном итоге. Фольклорная формулировка принципа Парето (ПП): 80% результата дает деятельность 20% игроков. В ПП полагается, что функция результата является аддитивной от вкладов участников, количество которых N.
При проведении ABC-анализа рекомендуют придерживаться следующего алгоритма:
формулировка цели анализа;
определение участников;
установление показателей для дифференциации участников;
получение данных для анализа;
оценка участников по выбранным показателям;
упорядочение игроков согласно величинам показателей;
разделение участников на группы;
экономическая интерпретация результатов анализа, принятие управленческих решений и т. п. [1, 3].
Нами будет рассмотрен случай одного показателя. При этом упорядочение участников обычно осуществляется по убыванию величины показателя [1–4]. Каждому участнику присваивают номер, начиная с игрока с наибольшим значением показателя. Далее по полученным упорядоченным значениям находят для каждого участника показатель с нарастающим итогом. Строят в прямоугольной декартовой системе координат кривую Лоренца (КЛ) [5] зависимости этого показателя y от номера участника x. При построении кривой учитывают точку с координатами (0;0) – нулевой вклад от фиктивного («нулевого») участника. Часто эту кривую строят на основании данных в процентах. Для получения таких данных значения показателя с нарастающим итогом делят на значение для последнего участника (суммарный результат S) и умножают на 100%. Аналогично поступают с номерами участников: все их делят на максимальный номер N и результаты умножают на 100%. Используя полученную кривую, тем или иным способом [1, 3, 4, 6, 7] производят разбиение множества участников на три непересекающихся подмножества (группы) A, B и C.
Методов такого разбиения достаточно много [3, 4, 6, 7]. Мы упомянем эмпирический и метод касательных. Внимание сконцентрируем на последнем, который можно реализовывать аналитически и графически [4].
При графической реализации (рис. 1) начальную и конечную точки кривой Лоренца соединяют отрезком прямой. Далее на этой кривой находят точку (xA; yA), касательная в которой параллельна построенному отрезку прямой. Абсцисса xA найденной точки разделяет группы A и B и сама включается в группу A.?Чтобы разделить оставшихся участников на группы B и C, найденную точку (xA; yA) соединяют отрезком прямой с крайней правой точкой КЛ. На участке КЛ, лежащем правее (xA; yA), находят точку (xB; yB), в которой касательная к кривой параллельна второму отрезку. Абсцисса xB этой точки разделяет группы B и C и сама включается в группу B.?Если абсциссы точек (xA; yA) и (xB; yB) не принадлежат экспериментальным данным, то эти абсциссы не включаются в соответствующую группу. Отметим, что графическое разделение на группы A, B и C подразумевает аппроксимацию экспериментальных данных некоторой кривой [4].
Подобная аппроксимация экспериментальных данных функциональной зависимостью осуществляется при аналитической реализации метода касательных [4, 6]. Пусть аппроксимирующая функция имеет вид y = f(x), причем a x b, f(a) y f(b) (a, b – левая, правая границы области задания функции y = f(x)). Если эмпирические данные нормированы на 100%, то 0 x 1; 0 y ? 1 (f(0) = 0; f(1) = 1).
Уравнение прямой, содержащей первый отрезок, имеет вид
, (1)
в случае нормирования данных оно принимает вид
y = x. (1a)
Если функция y = f(x) непрерывна на [a; b] и дифференцируема в (a; b), то, согласно теореме Лагранжа [8], существует точка xA ? [a; b], в которой производная
; (2)
в случае нормирования –
f'(xA) = 1. (2a)
Точка xA разделяет группы A и B.
Уравнение прямой, содержащей второй отрезок, имеет вид
; (3)
при нормировании данных это уравнение принимает вид
. (3a)
По той же теореме Лагранжа существует точка xB ? [xA; b], в которой производная
; (4)
при нормировке данных
. (4a)
Точка xB разделяет группы B и C.
Следовательно, при аналитическом подходе необходимо: аппроксимировать экспериментальную зависимость аналитической, например методом наименьших квадратов [9]; решить аналитически или численно уравнения (2) и (4) относительно xA и xB соответственно.
Отметим, что метод касательных при любой реализации позволяет осуществлять разбиение на большее, чем три, число групп – путем дальнейшего построения аналогичных (3) отрезков и отыскания касательных к кривой, параллельных этим отрезкам.
Приведем доводы о тесной связи метода касательных с ПП. Согласно последнему вклад SA, который больше половины общего результата S, дают участники, количество NA которых меньше половины общего их числа N.?Тогда средняя скорость нарастания результата для этой доли участников равна SA/NA > (S/2)/(N/2) = S/N, где S/N – средняя скорость нарастания результата для всего множества участников. S/N равна угловому коэффициенту первого отрезка (рис. 1). Тогда абсцисса xA точки касания разделяет участников, дающих скорость роста результата большую или равную S/N и меньшую S/N. Под этой скоростью, даваемой одним участником, понимаем отношение вклада этого участника к единице (один участник). В силу ранжирования исходных данных эти две группы находятся по разные стороны от точки xA (абсцисса xA может относиться к группе A).
Так, метод касательных обеспечивает разделение участников на группы по признаку отношения величины скорости роста результата, даваемой участником, к средней скорости этого роста на рассматриваемом участке КЛ. Непосредственная реализация метода требует, как указывалось выше, либо построения КЛ и нахождения графически касательной к ней, либо применения какого-либо математического пакета, например MathCAD, при аналитическом подходе [6].
Мы предлагаем использовать возможности Microsoft EXCEL. Для обоснования этого подхода вновь обратимся к теореме Лагранжа, точнее к ее доказательству [8]. При этом доказательстве вводится функция
, (6)
то есть в рассматриваемом нами случае функция разности уравнения кривой Лоренца и уравнения отрезка (1). Из (6) сразу получаем, что F(a) = F(b) = 0. Поскольку функция F(x) непрерывна на [a; b], дифференцируема в (a; b) и принимает на концах [a; b] одинаковые значения, то для F(x) выполняется утверждение теоремы Ролля [8]: существует точка xA ? [a; b], в которой F(x) = 0. По построению функция f(x) монотонно возрастает, тогда возможны два вида графика функции F(x):
все участники делают различные вклады в результат, поэтому график F(x) имеет единственный максимум в точке хА – граничной для групп A и B;
есть совокупность участников, дающих одинаковые вклады; тогда график функции F(x) имеет горизонтальный участок (плато), ординаты точек которого превосходят ординаты всех остальных точек графика.
Однозначное разделение на группы методом касательных возможно при первом виде графика функции F(x); функции с такими графиками и будут представлены в примерах. Замечание по поводу второго вида графика функции F(x) приведем в конце данной статьи.
Таким образом, для реальных дискретных экспериментальных данных в Microsoft EXCEL необходимо находить максимумы функций вида (6), построенных на соответствующих интервалах.
Конкретный пример реализации метода касательных в MS EXCEL. Для него взяты тридцать первых позиций табл. 3.1 из [4]. Расчеты в нормированных величинах представлены на листе EXCEL (рис. 2). В столбцах B8:B38 и D8:D38 содержатся исходные данные (учтен также «нулевой» участник – строка B8:H8), в столбцах C8:C38 и E8:E38 – нормированные (общий результат в 117 040 использован при нормировке данных из столбца D8:D38). Определяем границу групп A и B.?Столбец G8:G38 содержит ординаты точек первого отрезка (1а) (угловой коэффициент K1 = 1 из ячейки G5), столбец H8:H38 – значения функции (6). Максимальная величина в этом столбце – в ячейке H16. Тогда, согласно приведенной выше интерпретации метода касательных, количество участников группы A составляет 26,67% (ячейка C16) от общего их числа; они дают 77,54% (ячейка F16) конечного результата, то есть xA = 26,67%, f(xA) = 77,54%. Теперь определяем границу групп B и C. В столбец I16:I38 помещены ординаты точек второго отрезка (3а) (угловой коэффициент K2 = = (1 – f(xA))/(1 – xA) = (1–0,7754)/(1–0,2667) ? 0,3063 из ячейки F5), в столбец J16:J38 – значения функции (6), максимальное значение – в ячейке I16. Таким образом, xB = 56,67% (ячейка C25), f(xB) = 93,31% (ячейка F25). Поэтому группа B содержит 56,67% – 26,67% = 30% участников и дает 93,31% – 77,54% = 15,77% общего результата. Группа C содержит 100% – 56,67% = 43,33% игроков и дает 100% – 93,31% = 6,69% от общего результата. Итак, получили разделение на группы:
Приведем еще один способ реализации метода касательных в MS EXCEL. Для этого обратимся к функции f(x). Исходя из определения, она может быть представлена в виде:
(0 ? u ? N), (7)
где ?(z) – вклад участника с номером z. Функция ?(z) – убывающая, так как предварительно проведено ранжирование вкладов по убыванию. Рассматриваем ненормированный интервал участников. Интегральное представление (7) возможно при достаточно большом их количестве. В противном случае в (7) вместо интегрирования применяется суммирование по номерам участников. На результате это не сказывается.
Для нахождения xA берем соотношение (2). Выпишем его, используя (7):
, так как f(0) = 0.
Величина = представляет
собой среднее значение ??(z)?1 функции вклада всех N участников.
Аналогично после отделения группы A находим xB, используя (4) и (7):
– среднее значение вкладов участников с номерами из (xA; N].
Исходя из вышеизложенного, реализация в MS EXCEL такова. Для всех участников, кроме «нулевого», находим среднюю величину вклада. К группе A отнесем игроков с вкладами, большими либо равными среднему. Для оставшихся участников вновь находим средний вклад. К группе B отнесем тех из них, чьи вклады больше либо равны второму среднему вкладу. После отделения групп A и B получаем группу C.?При необходимости разбиения на большее число групп процедуру нужно продолжить с оставшимися после выделения групп A и B участниками.
Продемонстрируем реализацию в Microsoft EXCEL указанного подхода (рис. 3). Используем те же исходные данные, что и в первом примере; они содержатся в ячейках C4:C33 и D4:D33. Нормированные величины помещены в ячейки B4:B33 и E4:E33; для нормирования значений из E4:E33 взят конечный результат в 117 040 (ячейка D34). Средний вклад по всем участникам находится в D35 (для нормированных данных – в E35). Представители группы A отражены в ячейках B4:B11 и составляют 26,67% от всех участников; эти игроки дают 77,54% от общего результата. Средний вклад для оставшихся после выделения группы A участников находится в D36 (для нормированных данных – в E36). Представители группы B (в ячейках B12:B20) составляют 56,67% – 26,67% = 30% от всех участников и дают 93,31% – 77,54% = 15,77% от общего результата. Тогда группа C содержит 100% – 56,67% = 43,33% участников и дает 100% – 93,31% = 6,69% от общего результата. На листе EXCEL группы выделены цветом с помощью опции «Условное форматирование».
Разбиение на подмножества, полученное при таком подходе, естественно совпадает с разбиением в результате первого подхода, поскольку оба – суть реализации в EXCEL одного и того же метода касательных. Отметим одну особенность последнего. Если кривая Лоренца имеет участок, параллельный первому отрезку (это соответствует второму виду графика функции F(x)), то метод касательных даст не одну точку (xA; yA), а весь этот участок. Такая же ситуация возможна и на шаге разделения групп B и C, а также на других шагах (если они будут предприняты). Это качество относят к недостаткам метода [5], так как оно не позволяет однозначно провести разделение на группы. Наличие прямолинейного участка на КЛ свидетельствует о присутствии нескольких игроков с равными вкладами. Решение об их включении (полном или частичном) или невключении в ту или иную группу должен принимать исследователь, воспользовавшись другим методом, например эмпирическим [4].
Таким образом, изучен метод касательных. Указан признак, согласно которому осуществляется разделение на группы при его применении. Представлена связь метода с эмпирическим принципом Парето, по результатам анализа предложены два подхода для реализации в MS EXCEL. Их легко применять на практике, поскольку они освобождают лицо, проводящее ABC-анализ, от необходимости осуществлять какие-либо графические построения или использовать продвинутые математические пакеты при аналитическом определении границ групп.
Статья поступила в редакцию 09.02.2016?г.
Скользящее среднее используется для сглаживания краткосрочных колебаний с целью определения долгосрочного тренда. Вычислим скользящее среднее с помощью надстройки MS EXCEL Пакет анализа, формулами и с помощью линии тренда на диаграмме.
Метод скользящего среднего состоит в вычислении средних значений на основе предшествующих значений исследуемого числового ряда.
В случае усреднения за 3 периода скользящее среднее равно:
Y скол.i =(Y i + Y i-1 + Y i-2 )/3
На картинке ниже показано как вычислить в MS EXCEL скользящее среднее путем усреднения значений за три периода (за два предыдущих и один текущий).
Примечание : В англоязычной литературе для скользящего среднего используется термин Moving Average (MA) или Simple Moving Average (SMA) , а также rolling average, running average, moving mean.
Получить ряд, сглаженный методом скользящего среднего, можно с помощью надстройки MS EXCEL Пакет анализа (Analysis ToolPak) . Надстройка доступна из вкладки Данные, группа Анализ (EXCEL 2010).
В поле Интервал установим значение 3 – будем усреднять значения ряда за 3 периода. В поле Выходной интервал достаточно ввести ссылку на левую верхнюю ячейку диапазона с результатами (укажем ячейку D7).
Также поставим галочки в поле Вывод графика и Стандартные погрешности (будет выведен столбец с расчетами погрешностей, англ. Standard Errors).
Нажмем ОК.
Диаграмма
Вычисление погрешности
В столбце E, начиная с ячейки Е11, MS EXCEL разместил формулы для вычисления погрешностей (англ. Standard Errors):
=КОРЕНЬ(СУММКВРАЗН(B9:B11;D9:D11)/3)
Т.е. данная погрешность вычисляется по формуле:
Значения y i – это значения исходного ряда в период i. Значения «yi с крышечкой» - значения ряда, полученного методом скользящего среднего, в тот же в период i. Значение n равно 3, т.к. в нашем случае усреднение производится 3 периода.
Формула погрешности совпадает с выражением среднеквадратичной ошибки (англ. RMSE – Root Mean Squared Errors, квадратный корень из среднего значения квадратов ошибок), но вычисленной не для всей выборки (ряда), а только на интервале сглаживания (в нашем случае за 3 периода).
Обычно рассчитывается 2 типа ошибок: ошибка сглаживания (ошибка подгонки модели; англ. fitting errors или residuals) и ошибка прогнозирования (forecast errors).
Погрешности, вычисленные надстройкой Пакет анализа, являются ошибками прогнозирования. Эту погрешность можно использовать, чтобы рассчитать интервал предсказания (prediction interval). Про вычисление прогнозного значения и его интервала предсказания см. статью Прогнозирование методом скользящего среднего .
Отметим, что MS EXCEL вычисляет целый массив погрешностей (столбец Е), но для построения интервала предсказания необходимо только последнее значение.
Недостатком формул, получаемых с помощью Пакета анализа, является то, что при изменении количества периодов усреднения приходится перезапускать расчет, вызывая Надстройку заново.
В файле примера на листе Формулы создана форма для автоматического пересчета скользящего среднего в зависимости от количества периодов.
Значения сглаженного ряда вычисляются с помощью формулы:
= ЕСЛИ(A11
в ячейке D8 содержится количество периодов усреднения. Про функцию СМЕЩ() можно прочитать в этой статье .
Погрешности вычисляются по формуле:
= КОРЕНЬ(СУММКВРАЗН(СМЕЩ(B11;-$D$8+1;;$D$8);СМЕЩ(C11;-$D$8+1;;$D$8))/$D$8)
Выбор количества периодов усреднения для удобства осуществляется с помощью элемента управления Счетчик .
На диаграмме с помощью линии тренда можно построить график Скользящего среднего с заданным количеством периодов усреднения.
Используем данные файла примера на листе Формулы . Сначала построим ряд скользящего среднего с 5-ю периодами усреднения с помощью формул.
Теперь построим линию тренда, которая совпадет с красным графиком «Сглаженный ряд». Для этого:
• Щелкните диаграмму, чтобы выделить ее.
• Выберите ряд данных, к которому нужно добавить график скользящего среднего (синий график).
• На вкладке Макет в группе Анализ нажмите кнопку Линия тренда и выберите пункт Дополнительные параметры линии тренда.
• В открывшемся окне выберите Линейная фильтрация и установите в поле Точки значение 5.
После закрытия окна будет выведен график скользящего среднего, полностью совпадающий с красным графиком, ранее построенным с помощью формул.
Примечание : У метода Скользящее среднее есть несколько модификаций, которые рассмотрены в одноименной статье.
Экспоненциальное сглаживание используется для сглаживания краткосрочных колебаний во временных рядах, чтобы облегчить определение долгосрочного тренда, а также для прогнозирования. Произведем экспоненциальное сглаживание с помощью надстройки MS EXCEL Пакет анализа и формулами. Рассмотрим двойное и тройное экспоненциальное сглаживание для прогнозирования рядов с трендом и сезонностью.
Экспоненциальное сглаживание один из наиболее распространённых методов для сглаживания временных рядов. В отличие от метода Скользящего среднего, где прошлые наблюдения имеют одинаковый вес, Экспоненциальное сглаживание присваивает им экспоненциально убывающие веса, по мере того как наблюдения становятся старше. Другими словами, последние наблюдения дают относительно больший вес при прогнозировании, чем старые наблюдения.
Примечание: Перед прочтением этой статьи рекомендуется прочитать про Скользящее среднее.
Примечание: В англоязычной литературе для экспоненциального сглаживания используется термин Single Exponential Smoothing или Simple Exponential Smoothing (SES).
Напомним, что при усреднении методом Скользящего среднего веса, присвоенные наблюдениям, одинаковы и равны 1/n, где n – количество периодов усреднения. Например, в случае усреднения за 3 периода скользящее среднее равно:
В случае Экспоненциального сглаживания формула выглядит следующим образом:
Параметр альфа определяет степень сглаживания. При малых значениях альфа (0,1 – 0,2) имеет место сильное сглаживание. При значениях близких к 1, сглаженный ряд практически повторяет исходный ряд с задержкой (лагом) на один период. Для медленно меняющегося ряда часто берут небольшие значения альфа=0,1; а для быстро меняющегося 0,3-0,5.
Примечание: Формулы представляют собой рекуррентное соотношение – это когда последующий член ряда вычисляется на основе предыдущего.
Примечание: Существует альтернативный подход к Экспоненциальному сглаживанию: в нем в формуле вместо Yi-1 заменяют на Yi. Этот подход используется в контрольных картах экспоненциально взвешенного скользящего среднего (EWMA).
Получить Экспоненциально сглаженный ряд можно с помощью надстройки Пакет анализа (Analysis ToolPak). Надстройка доступна из вкладки Данные, группа Анализ.
СОВЕТ: Подробнее о других инструментах надстройки Пакет анализа и ее подключении – читайте в статье.
Разместим исходный числовой ряд в диапазоне B7:B32.
Для наглядности пронумеруем каждое значение ряда (столбец А).
Вызовем надстройку Пакет анализа, выберем инструмент Экспоненциальное сглаживание.
Если диапазон включает и заголовок, то нужно установить галочку в поле Метки. В нашем случае устанавливать галочку не требуется, т.к. заголовок столбца не входит в диапазон B7:B32.
Поле Фактор затухания, как и параметр альфа в вышеуказанной формуле, определяет степень сглаживания ряда. Фактор затухания равен (1- альфа). Чем больше Фактор затухания тем глаже получается ряд. Установим значение 0,8.
В поле Выходной интервал достаточно ввести ссылку на левую верхнюю ячейку диапазона с результатами (укажем ячейку D7).
Также поставим галочки в поле Вывод графика и Стандартные погрешности (будет выведен столбец с расчетами погрешностей, англ. Standard Errors).
Нажмем ОК.
В результате работы надстройки, MS EXCEL разместил значения ряда, полученного методом Экспоненциального сглаживания, в столбце D (см. файл примера лист Пакет анализа ).
Первое значение сглаженного ряда, точнее формула = B7 , содержится в ячейке D8. Второе значение вычисляется с помощью формулы = 0,2*B8+0,8*D8 .
Таким образом, Фактор затухания (0,8) определяет вес (вклад) предыдущего значения сглаженного ряда. Соответственно, (1-Фактор затухания)=альфа определяет вес предыдущего значения исходного ряда.
Для отображения рядов MS EXCEL создал диаграмму типа график. Сглаженный ряд на диаграмме называется «Прогноз» (ряд красного цвета).
Диаграмма позволяет визуально определить «выбросы», т.е. значения исходного ряда, которые существенно отличаются от средних значений. Такие «выбросы» могут быть следствием ошибки, но они оказывают существенное влияние на вид сглаженного ряда.
В столбце E, начиная с ячейки Е11, MS EXCEL разместил формулы для вычисления погрешностей (англ. Standard Errors):
Т.е. данная погрешность вычисляется по формуле:
Значения y – это значения исходного ряда в период i. Значения «y с крышечкой» - значения ряда, полученного методом Экспоненциального сглаживания, в тот же в период i. Значение n для экспоненциального сглаживания всегда равно 3, т.е. ошибка вычисляется за 3 последних периода (последние 3 значения учитываются с макимальным весом при расчете текущего значения сглаженного ряда и, соответственно, вносят более 50% вклада в его значение. Величина вклада сильно зависит от альфа).
Подробнее об этой погрешности см. соответствующий раздел в статье про Скользящее среднее.
Как было показано в статье про Взвешенное скользящее среднее веса значений исходного ряда берутся в зависимости от их удаленности от текущего периода. Например, для 3-х периодов усреднения для Взвешенного скользящего среднего можно использовать формулу:
Экспоненциальное сглаживание по сути является модификацией Взвешенного скользящего среднего – при расчете значения сглаженного ряда используются ВСЕ предыдущие значения исходного ряда с весами уменьшающимися в геометрической прогрессии по мере удаления от текущего периода.
Чтобы это показать воспользуемся формулой
и вычислим Yэксп.5, т.е. значения сглаженного ряда для 5-го периода. После очевидных преобразований получим:
Таким образом, вес 4-го (предыдущего) члена ряда =(1-альфа) 0 , а вес 3-го =(1-альфа) 1 и т.д. Пусть t – текущий период (в нашем случае =5). Вес (t-i)-го члена ряда =(1-альфа) t-1-i . Т.к. (1-альфа)<1, то с ростом i растет и вес, и для члена t-1 достигает максимума =1.
Как известно, экспоненциальный рост y=a*EXP(b*x) в случае дискретной области определения с равными интервалами x называют геометрическим ростом (значения экспоненциальной функции y=a*EXP(b*x) являются в этом случае членами геометрической прогрессии m^x).
В нашем случае, приравняв i-й вес (1-альфа) t-1-i соответствующему значению экспоненциальной функции a*EXP(b*i) получим уравнение, которое позволит вычислить коэффициенты a и b (понадобится еще одно уравнение, например, для i-1 веса).
Решив систему из 2-х уравнений получим, a= EXP((t-1)*LN(1-альфа)) и b= LN(1-альфа) .
В файле примера для 26-го члена сглаженного ряда (t=26) вычислены веса всех предыдущих членов. На диаграмме ниже показано, что веса уменьшаются с ростом i в геометрической прогрессии, что соответствует экспоненциальной функции y=0,0038*exp(0,2231*x), где x=i. Вычисления параметров экспоненциальной кривой сделаны с помощью надстройки Поиск решения.
Недостатком формул, получаемых с помощью Пакета анализа, является то, что при изменении Фактора затухания (1-альфа) приходится перезапускать расчет. В файле примера на листе Формулы создана форма для быстрого пересчета Экспоненциального сглаживания в зависимости от значения Фактора затухания (полученный результат, естественно, полностью совпадает с расчетами надстройки Пакет анализа).
Значения ряда вычисляются с помощью формулы:
в ячейке В6 содержится значение Фактора затухания.
Выбор значения Фактора затухания для удобства осуществляется с помощью элемента управления Счетчик с шагом 0,1.
Среди различных методов прогнозирования нельзя не выделить аппроксимацию. С её помощью можно производить приблизительные подсчеты и вычислять планируемые показатели, путем замены исходных объектов на более простые. В Экселе тоже существует возможность использования данного метода для прогнозирования и анализа. Давайте рассмотрим, как этот метод можно применить в указанной программе встроенными инструментами.
Выполнение аппроксимации
Наименование данного метода происходит от латинского слова proxima – «ближайшая» Именно приближение путем упрощения и сглаживания известных показателей, выстраивание их в тенденцию и является его основой. Но данный метод можно использовать не только для прогнозирования, но и для исследования уже имеющихся результатов. Ведь аппроксимация является, по сути, упрощением исходных данных, а упрощенный вариант исследовать легче.
Главный инструмент, с помощью которого проводится сглаживания в Excel – это построение линии тренда. Суть состоит в том, что на основе уже имеющихся показателей достраивается график функции на будущие периоды. Основное предназначение линии тренда, как не трудно догадаться, это составление прогнозов или выявление общей тенденции.
Но она может быть построена с применением одного из пяти видов аппроксимации:
- Линейной;
- Экспоненциальной;
- Логарифмической;
- Полиномиальной;
- Степенной.
Рассмотрим каждый из вариантов более подробно в отдельности.
Способ 1: линейное сглаживание
Прежде всего, давайте рассмотрим самый простой вариант аппроксимации, а именно с помощью линейной функции. На нем мы остановимся подробнее всего, так как изложим общие моменты характерные и для других способов, а именно построение графика и некоторые другие нюансы, на которых при рассмотрении последующих вариантов уже останавливаться не будем.
Прежде всего, построим график, на основании которого будем проводить процедуру сглаживания. Для построения графика возьмем таблицу, в которой помесячно указана себестоимость единицы продукции, производимой предприятием, и соответствующая прибыль в данном периоде. Графическая функция, которую мы построим, будет отображать зависимость увеличения прибыли от уменьшения себестоимости продукции.
-
Для построения графика, прежде всего, выделяем столбцы «Себестоимость единицы продукции» и «Прибыль». После этого перемещаемся во вкладку «Вставка». Далее на ленте в блоке инструментов «Диаграммы» щелкаем по кнопке «Точечная». В открывшемся списке выбираем наименование «Точечная с гладкими кривыми и маркерами». Именно данный вид диаграмм наиболее подходит для работы с линией тренда, а значит, и для применения метода аппроксимации в Excel.
В блоке параметров «Построение линии тренда (аппроксимация и сглаживание)» устанавливаем переключатель в позицию «Линейная».
При желании можно установить галочку около позиции «Показывать уравнение на диаграмме». После этого на диаграмме будет отображаться уравнение сглаживающей функции.
Также в нашем случае для сравнения различных вариантов аппроксимации важно установить галочку около пункта «Поместить на диаграмму величину достоверной аппроксимации (R^2)». Данный показатель может варьироваться от 0 до 1. Чем он выше, тем аппроксимация качественнее (достовернее). Считается, что при величине данного показателя 0,85 и выше сглаживание можно считать достоверным, а если показатель ниже, то – нет.
Сглаживание, которое используется в данном случае, описывается следующей формулой:
В конкретно нашем случае формула принимает такой вид:
Величина достоверности аппроксимации у нас равна 0,9418, что является довольно приемлемым итогом, характеризующим сглаживание, как достоверное.
Способ 2: экспоненциальная аппроксимация
Теперь давайте рассмотрим экспоненциальный тип аппроксимации в Эксель.
-
Для того, чтобы изменить тип линии тренда, выделяем её кликом правой кнопки мыши и в раскрывшемся меню выбираем пункт «Формат линии тренда…».
Общий вид функции сглаживания при этом такой:
где e – это основание натурального логарифма.
В конкретно нашем случае формула приняла следующую форму:
Способ 3: логарифмическое сглаживание
Теперь настала очередь рассмотреть метод логарифмической аппроксимации.
-
Тем же способом, что и в предыдущий раз через контекстное меню запускаем окно формата линии тренда. Устанавливаем переключатель в позицию «Логарифмическая» и жмем на кнопку «Закрыть».
В общем виде формула сглаживания выглядит так:
где ln – это величина натурального логарифма. Отсюда и наименование метода.
В нашем случае формула принимает следующий вид:
Способ 4: полиномиальное сглаживание
Настал черед рассмотреть метод полиномиального сглаживания.
-
Переходим в окно формата линии тренда, как уже делали не раз. В блоке «Построение линии тренда» устанавливаем переключатель в позицию «Полиномиальная». Справа от данного пункта расположено поле «Степень». При выборе значения «Полиномиальная» оно становится активным. Здесь можно указать любое степенное значение от 2 (установлено по умолчанию) до 6. Данный показатель определяет число максимумов и минимумов функции. При установке полинома второй степени описывается только один максимум, а при установке полинома шестой степени может быть описано до пяти максимумов. Для начала оставим настройки по умолчанию, то есть, укажем вторую степень. Остальные настройки оставляем такими же, какими мы выставляли их в предыдущих способах. Жмем на кнопку «Закрыть».
Данный метод наиболее успешно можно применять в том случае, если данные носят постоянно изменчивый характер. Функция, описывающая данный вид сглаживания, выглядит таким образом:
В нашем случае формула приняла такой вид:
Формула, которая описывает данный тип сглаживания, приняла следующий вид:
Способ 5: степенное сглаживание
В завершении рассмотрим метод степенной аппроксимации в Excel.
-
Перемещаемся в окно «Формат линии тренда». Устанавливаем переключатель вида сглаживания в позицию «Степенная». Показ уравнения и уровня достоверности, как всегда, оставляем включенными. Жмем на кнопку «Закрыть».
Данный способ эффективно используется в случаях интенсивного изменения данных функции. Важно учесть, что этот вариант применим только при условии, что функция и аргумент не принимают отрицательных или нулевых значений.
Общая формула, описывающая данный метод имеет такой вид:
В конкретно нашем случае она выглядит так:
Как видим, при использовании конкретных данных, которые мы применяли для примера, наибольший уровень достоверности показал метод полиномиальной аппроксимации с полиномом в шестой степени (0,9844), наименьший уровень достоверности у линейного метода (0,9418). Но это совсем не значит, что такая же тенденция будет при использовании других примеров. Нет, уровень эффективности у приведенных выше методов может значительно отличаться, в зависимости от конкретного вида функции, для которой будет строиться линия тренда. Поэтому, если для этой функции выбранный метод наиболее эффективен, то это совсем не означает, что он также будет оптимальным и в другой ситуации.
Если вы пока не можете сразу определить, основываясь на вышеприведенных рекомендациях, какой вид аппроксимации подойдет конкретно в вашем случае, то есть смысл попробовать все методы. После построения линии тренда и просмотра её уровня достоверности можно будет выбрать оптимальный вариант.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: