Как сделать апроксимацию в excel на графике

CKT_l.r.05_Prognozirovanie / Решение задач аппроксимации средствами Excel

доктор физ.– мат. наук, профессор Гавриленко В.В. ассистент Парохненко Л.М.

(Национальный транспортный университет)

Теоретическая справка. На практике при моделировании различных про-

цессов, в частности, экономических, физических, технических, социальных,

широко используются те или иные способы вычисления приближенных значе-

ний функций по известным их значениям в некоторых фиксированных точках.

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

∙ при построении приближенных формул для вычисления значений характер-

ных величин исследуемого процесса по данным таблиц, полученным в ре-

зультате физического или вычислительного эксперимента;

∙ при численном интегрировании, численном дифференцировании, числен-

ном решении дифференциальных уравнений и т.д.;

∙ при необходимости вычисления значений функций в промежуточных точ-

ках рассматриваемого интервала;

∙ при определении значений характерных величин процесса за пределами рас-

сматриваемого интервала, в частности, при необходимости заглянуть в

“ прошлое”), то есть при определении значений показателей процесса до на-

∙ в прогнозировании, то есть при получении предварительных оценок буду-

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

Если для моделирования некоторого процесса, заданного таблицей, по-

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

тода наименьших квадратов, то она называется аппроксимирующей функцией

(регрессией), а сама задача построения аппроксимирующих функций называет-

ся задачей аппроксимации.

В данной статье рассмотрены возможности пакета Excel [1–3] при реше-

нии задач аппроксимации, а именно, приведены методы и приемы построения

(создания) регрессий для таблично заданных функций, что является основой регрессионного анализа.

В Excel для построения регрессий имеются такие возможности, как:

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

2) использование встроенных статистических функций рабочего листа Excel ,

позволяющих получать регрессии (линии тренда) на основе таблицы исход-

ных данных (использование данного инструмента предварительно не связы-

вается с наличием соответствующей диаграммы).

Добавление линий тренда в диаграмму

Для таблицы данных, описывающих некоторый процесс и представленных диаграммой, в Excel имеется эффективный инструмент регрессионного анали-

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

сти моделируют исследуемый процесс;

∙ добавлять к диаграмме уравнение построенной регрессии;

∙ определять степень соответствия выбранной регрессии отображаемым на диаграмме данным.

Построенные модели процесса – линии тренда (trendlines) показывают

тенденцию изменения данных, дают возможность определять значения иссле-

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

На основе данных диаграммы Excel позволяет получать такие типы регрес-

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

пенной, экспоненциальный, которые задаются уравнением y = y(x) , где x – неза-

висимая переменная, которая часто принимает значения последовательности натурального ряда чисел (1; 2; 3; …) и производит, например, отсчет времени протекания исследуемого процесса.

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

Строится в соответствии с уравнением

где m – угол наклона линейной регрессии к оси абсцисс; b – координата точки пересечения линейной регрессии с осью ординат.

2. Полиномиальная линия тренда полезна для описания характеристик,

имеющих несколько ярко выраженных экстремумов (максимумов и миниму-

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

мум или минимум; полином третьей степени – не более двух экстремумов; по-

лином четвертой степени – не более трех экстремумов и т.д.

Строится в соответствии с уравнением

y = c 0 + c 1 x + c 2 x 2 + c 3 x 3 + c 4 x 4 + c 5 x 5 + c 6 x 6 ,

где коэффициенты c 0 , c 1 , c 2 . c 6 – константы.

3. Логарифмическая линия тренда с успехом применяется при моделирова-

нии характеристик, значения которых вначале быстро растут или убывают по величине, а затем постепенно стабилизируются.

Строится в соответствии с уравнением

где коэффициенты b, с – константы.

4. Степенная линия тренда дает хорошие результаты, если значения иссле-

дуемой зависимости характеризуются постоянным изменением скорости роста.

Примером такой зависимости может служить график равноускоренного движе-

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

Строится в соответствии с уравнением

где коэффициенты b, с – константы.

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

Строится в соответствии с уравнением

где коэффициенты b, с – константы.

При подборе линии тренда Excel автоматически рассчитывает значение величины R 2 , которая характеризует достоверность аппроксимации: чем ближе значение R 2 к единице, тем надежнее линия тренда аппроксимирует исследуе-

мый процесс. При необходимости значение R 2 всегда можно отобразить на

Определяется по формуле

Σ 1 = ∑(y j − Y j ) 2

Для добавления линии тренда к ряду данных следует:

1. Активизировать построенную на основе ряда данных диаграмму, т.е. щелк-

нуть в пределах области диаграммы. В главном меню появится пункт Диа —

2. После щелчка на этом пункте на экране появится меню, в котором следует выбрать команду Добавить линию тренда.

Пункты 1–2 легко реализуются также следующим приемом: направить ука-

затель мыши к графику, построенного на ряде данных, и щелкнуть правой кла-

вишей мыши, и в появившемся контекстном меню выбрать команду Добавить

линию тренда. На экране появится диалоговое окно Линия тренда с раскры-

той вкладкой Тип (рис.1).

Рис.1. Вкладка Тип диалогового окна Формат линии тренда

3. Выбрать на вкладке Тип необходимый тип линии тренда (по умолчанию выбирается тип Линейный). Для типа Полиномиальная в поле Степень сле-

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

4. В поле Построен на ряде перечислены все ряды данных рассматриваемой диаграммы. Для добавления линии тренда к конкретному ряду данных следует в поле Построен на ряде выбрать его имя.

5. При необходимости, перейдя на вкладку Параметры (рис.2), можно для ли-

нии тренда задать следующие параметры:

∙ Изменить название линии тренда в поле Название аппроксимирующей

∙ Задать количество периодов (вперед или назад) для прогноза в поле Про —

∙ Вывести в область диаграммы уравнение линии тренда, для чего следует ус-

тановить флажок для опции «показать уравнение на диаграмме».

∙ Вывести в область диаграммы значение достоверности аппроксимации R 2 ,

для чего следует установить флажок для опции «поместить на диаграмму ве —

личину достоверности аппроксимации (R^2) ».

∙ Задать точку пересечения линии тренда с осью Y, для чего следует устано-

вить флажок для опции «пересечение кривой с осью Y в точке : ». 6. Нажать клавишу OK .

Рис.2. Вкладка Параметры диалогового окна Линия тренда

Для редактирования уже построенной линии тренда следует:

1. Щелкнуть левой клавишей мыши по той линии тренда, которую требуется

2. Нажать в главном меню клавишу Формат, а появившемся контекстном ме-

ню выбрать команду Выделенная линия тренда.

Пункты 1–2 легко реализуются также следующим приемом: направить ука-

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

Еще легче реализуются пункты 1–2: двойным щелчком левой клавишей мыши по графику линии тренда.

3. На экране появится диалоговое окно Формат линии тренда (рис.3), содер-

жащее три вкладки: Вид, Тип, Параметры, причем содержимое вкладок Тип,

Параметры полностью совпадает с аналогичными вкладками диалогового ок-

на Линия тренда (рис.1–2).

4. При необходимости, перейдя на вкладку Вид (рис.3), можно для линии тренда задать тип линии, ее цвет и толщину.

5. Нажать клавишу OK .

Для удаления уже построенной линии тренда следует выбрать удаляемую линию тренда и нажать клавишу Delete .

Достоинствами этого инструмента регрессионного анализа являются:

∙ относительная легкость построения на диаграммах линии тренда без созда-

ния для нее таблицы данных;

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

∙ возможность прогнозирования поведения исследуемого процесса на произ-

вольное (в пределах здравого смысла) количество шагов вперед, а также назад;

∙ возможность получения уравнения линии тренда в аналитическом виде;

∙ возможность, при необходимости, получения оценки достоверности прове-

К недостаткам можно отнести следующие моменты:

∙ построение линии тренда осуществляется лишь при наличии построенной на ряде данных диаграммы;

∙ несколько загроможден процесс формирования рядов данных для исследуе-

мой характеристики на основании полученных для нее уравнений линий трен-

да, так как коэффициенты этих уравнений при каждом изменении значений ря-

да данных пересчитываются, но лишь в пределах области диаграммы;

∙ в отчетах сводных диаграмм при изменении представления диаграммы или связанного отчета сводной таблицы имеющиеся линии тренда не сохраняются,

то есть до проведения линий тренда или другого форматирования отчета свод-

ных диаграмм следует убедиться, что макет отчета удовлетворяет необходи-

Рис.3. Вкладка Вид диалогового окна Формат линии тренда

Линиями тренда можно дополнить ряды данных, представленные на гра-

фиках, гистограммах, плоских ненормированных диаграммах с областями, ли-

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

Нельзя дополнить линиями тренда ряды данных на объемных, нормиро-

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

граммы на один из вышеперечисленных, а также при изменении представления отчета сводной диаграммы или связанного отчета сводной таблицы соответст-

вующие данным линии тренда будут утеряны.

Использование встроенных функций Excel

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

В Excel имеется несколько вариантов построения линейной регрессии (ли-

нейного тренда), в частности:

∙ с помощью функции ТЕНДЕНЦИЯ;

∙ с помощью функции ЛИНЕЙН;

∙ с помощью функций НАКЛОН и ОТРЕЗОК .

В Excel имеется также несколько вариантов построения экспоненциальной линии тренда, в частности:

∙ с помощью функции РОСТ;

∙ с помощью функции ЛГРФПРИБЛ.

Следует отметить, что приемы построения регрессий с помощью функций

ТЕНДЕНЦИЯ и РОСТ практически совпадают. То же самое можно сказать и о паре функций ЛИНЕЙН и ЛГРФПРИБЛ. Для всех этих четырех функций при создании таблицы значений используются такие возможности Excel , как формулы массивов, что несколько загромождает процесс построения регрес-

сий. Заметим также, что построение (создание) линейной регрессии, на наш взгляд, легче всего осуществить с помощью функций НАКЛОН и ОТРЕЗОК,

где первая из них определяет угловой коэффициент линейной регрессии, а вто-

рая – отрезок, отсекаемый регрессией на оси ординат.

Достоинствами данного инструмента регрессионного анализа являются:

∙ достаточно простой однотипный процесс формирования рядов данных ис-

следуемой характеристики для всех встроенных статистических функций, за-

дающих линии тренда;

∙ стандартная методика построения линий тренда на основе сформированных рядов данных;

∙ возможность прогнозирования поведения исследуемого процесса на необ-

ходимое количество шагов вперед или назад.

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

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

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

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

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

тьи – на конкретных примерах показать возможности пакета Excel при реше-

нии задач аппроксимации; продемонстрировать, каким эффективными инстру-

ментами для построения регрессий и прогнозирования обладает Excel ; проил-

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

Предложенная в статье методика по овладению навыков решения средства-

ми Excel такого рода задач (см. также [4 – 7], где приведены методики решения в Excel систем линейных алгебраических уравнений, нелинейных уравнений,

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

рованные математические пакеты, как Mathematica , Maple , Matlab , Mathcad ,

обладающие более мощными возможностями для построения регрессий и про-

гнозирования, используются значительно меньшей пользовательской аудито-

Ниже приводятся решения конкретных задач с помощью перечисленных инструментов пакета Excel .

Задача 1 . Для таблицы данных о прибыли автотранспортного предприятия за 1995–2002 г.г. необходимо выполнить следующие действия:

Аппроксимация табличных функций в Excel

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

Понятие аппроксимации

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

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

Аппроксимация табличных функций в Excel

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

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

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

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

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

Рисунок 1. Параметры для построения графика. Автор24 — интернет-биржа студенческих работ

Затем будет построен следующий график:

Рисунок 2. Точечная с гладкими кривыми и маркерами. Автор24 — интернет-биржа студенческих работ

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

Рисунок 3. Добавить линию тренда на график. Автор24 — интернет-биржа студенческих работ

Имеется и другой вариант добавления линии тренда. В дополнительной группе вкладок на ленте «Работа с диаграммами» следует переместиться во вкладку «Макет». Затем в блоке инструментов «Анализ» необходимо сделать щелчок по кнопке «Линия тренда», после чего откроется список. Поскольку в нашем случае рассматривается применение линейной аппроксимации, то из предложенных позиций следует выбрать «Линейное приближение».

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

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

Рисунок 4. Отображение линии тренда. Автор24 — интернет-биржа студенческих работ

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

Для конкретного варианта, приведенного выше, формула будет иметь следующий вид:

y = ‒ 0,1156x + 72,255.

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

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

Рисунок 5. Экспоненциальный тип аппроксимации. Автор24 — интернет-биржа студенческих работ

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

Урок 4. Виды аппроксимации в Excel

Аппроксимация в Excel проще всего реализуется с помощью программы построения трендов. Для выяснения особенностей аппроксимации возьмем какой-либо конкретный пример. Например, энтальпию насыщенного пара по книге С.Л.Ривкина и А.А.Александрова «Теплофизические свойства воды и водяного пара», М., «Энергия», 1980г. В колонке P поместим значения давления в кгс/см2, в колонке i» — энтальпию пара на линии насыщения в ккал/кг и построим график с помощью опции или кнопки «Мастер диаграмм».

Щелкнем правой кнопкой по линии на рисунке, затем левой кнопкой по опции «Добавить линию тренда» и смотрим — какие услуги предлагаются нам этой опцией в части реализации аппроксимации в Excel.

Нам предлагается на выбор пять типов аппроксимации: линейная, степенная, логарифмическая, экспоненциальная и полиноминальная. Чем они хороши и чем могут нам помочь? — Нажимаем кнопку F1, затем щелкаем по опции «Мастер ответов» и в появившееся окошко вводим нужное нам слово «аппроксимация», после чего щелкаем по кнопке «Найти». Выбираем в появившемся списке раздел «Формулы для построения линий тренда».

Получаем следующую информацию в несколько измененной нами

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

где b — угол наклона и a — координата пересечения оси абсцисс (свободный член).

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

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

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

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

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

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

Используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:

где a, b1, b2, b3. b6 — константы.

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

Линейная аппроксимация дает нам R^2=0.9291 — это низкая достоверность и плохой результат.

Для перехода к степенной аппроксимации щелкаем правой кнопкой по линии тренда, затем левой кнопкой — по опции «Формат линии тренда», далее по опциям «Тип» и «Степенная». На этот раз получили R^2=0.999.

Запишем уравнение линии тренда в виде, пригодном для расчетов на листе Excel:

В результате имеем:

Максимальная погрешность аппроксимации получилась на уровне 0.23 ккал/кг. Для аппроксимации экспериментальных данных такой результат был бы чудесным, но для аппроксимации справочной таблицы это не слишком хороший результат. Поэтому попробуем проверить другие варианты аппроксимации в Excel посредством программы построения трендов.

Логарифмическая аппроксимация дает нам R^2=0.9907 — несколько хуже, чем по степенному варианту. Экспоненнта в том варианте, который предлагает программа построения трендов, вообще не подошла — R^2=0.927.

Полиноминальная аппроксимация со степенью 2 (это y=a+b1*x+b2*x^2) обеспечила R^2=0.9896. При степени 3 получили R^2=0.999, но с явным искажением аппроксимируемой кривой, в особенности при P>0.07 кгс/см2. Наконец, пятая степень нам дает R^2=1 — это, как утверждается, максимально тесная связь между исходными данными и их аппроксимацией.

Перепишем уравнение полинома в пригодном для расчетов на листе Excel виде:

и сравним результат аппроксимации с исходной таблицей:

Оказалось, что R^2=1 в данном случае лишь блестящая ложь. Реально, самый лучший результат полиноминальной аппроксимации дал самый простой полином вида y=a+b1*x+b2*x^2. Но его результат хуже, чем в варианте степенной аппроксимации y=634.16*x^0.012, где максимальная погрешность аппроксимации находилась на уровне 0.23 ккал/кг. Это все, что мы можем выжать из программы построения трендов. Посмотрим, что мы можем выжать из функции Линейн. Для нее попробуем вариант степенной аппроксимации.

Примечание. Обнаруженный дефект связан с работой программы построения трендов, но не с методом МНК.

Метод аппроксимации в Microsoft Excel

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

Выполнение аппроксимации

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

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

Но она может быть построена с применением одного из пяти видов аппроксимации:

• Линейной;
• Экспоненциальной;
• Логарифмической;
• Полиномиальной;
• Степенной.

Рассмотрим каждый из вариантов более подробно в отдельности.

Способ 1: линейное сглаживание

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

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

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

В блоке параметров «Построение линии тренда (аппроксимация и сглаживание)» устанавливаем переключатель в позицию «Линейная».
При желании можно установить галочку около позиции «Показывать уравнение на диаграмме». После этого на диаграмме будет отображаться уравнение сглаживающей функции.

Также в нашем случае для сравнения различных вариантов аппроксимации важно установить галочку около пункта «Поместить на диаграмму величину достоверной аппроксимации (R^2)». Данный показатель может варьироваться от 0 до 1. Чем он выше, тем аппроксимация качественнее (достовернее). Считается, что при величине данного показателя 0,85 и выше сглаживание можно считать достоверным, а если показатель ниже, то – нет.

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

В конкретно нашем случае формула принимает такой вид:

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

Способ 2: экспоненциальная аппроксимация

Теперь давайте рассмотрим экспоненциальный тип аппроксимации в Эксель.

1. Для того, чтобы изменить тип линии тренда, выделяем её кликом правой кнопки мыши и в раскрывшемся меню выбираем пункт «Формат линии тренда…».
2. После этого запускается уже знакомое нам окно формата. В блоке выбора типа аппроксимации устанавливаем переключатель в положение «Экспоненциальная». Остальные настройки оставим такими же, как и в первом случае. Щелкаем по кнопке «Закрыть».
3. После этого линия тренда будет построена на графике. Как видим, при использовании данного метода она имеет несколько изогнутую форму. При этом уровень достоверности равен 0,9592, что выше, чем при использовании линейной аппроксимации. Экспоненциальный метод лучше всего использовать в том случае, когда сначала значения быстро изменяются, а потом принимают сбалансированную форму.

Общий вид функции сглаживания при этом такой:

где e – это основание натурального логарифма.

В конкретно нашем случае формула приняла следующую форму:

Способ 3: логарифмическое сглаживание

Теперь настала очередь рассмотреть метод логарифмической аппроксимации.

1. Тем же способом, что и в предыдущий раз через контекстное меню запускаем окно формата линии тренда. Устанавливаем переключатель в позицию «Логарифмическая» и жмем на кнопку «Закрыть».
2. Происходит процедура построения линии тренда с логарифмической аппроксимацией. Как и в предыдущем случае, такой вариант лучше использовать тогда, когда изначально данные быстро изменяются, а потом принимают сбалансированный вид. Как видим, уровень достоверности равен 0,946. Это выше, чем при использовании линейного метода, но ниже, чем качество линии тренда при экспоненциальном сглаживании.

В общем виде формула сглаживания выглядит так:

где ln – это величина натурального логарифма. Отсюда и наименование метода.

В нашем случае формула принимает следующий вид:

Способ 4: полиномиальное сглаживание

Настал черед рассмотреть метод полиномиального сглаживания.

1. Переходим в окно формата линии тренда, как уже делали не раз. В блоке «Построение линии тренда» устанавливаем переключатель в позицию «Полиномиальная». Справа от данного пункта расположено поле «Степень». При выборе значения «Полиномиальная» оно становится активным. Здесь можно указать любое степенное значение от 2 (установлено по умолчанию) до 6. Данный показатель определяет число максимумов и минимумов функции. При установке полинома второй степени описывается только один максимум, а при установке полинома шестой степени может быть описано до пяти максимумов. Для начала оставим настройки по умолчанию, то есть, укажем вторую степень. Остальные настройки оставляем такими же, какими мы выставляли их в предыдущих способах. Жмем на кнопку «Закрыть».
2. Линия тренда с использованием данного метода построена. Как видим, она ещё более изогнута, чем при использовании экспоненциальной аппроксимации. Уровень достоверности выше, чем при любом из использованных ранее способов, и составляет 0,9724.

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

В нашем случае формула приняла такой вид:

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

Способ 5: степенное сглаживание

В завершении рассмотрим метод степенной аппроксимации в Excel.

1. Перемещаемся в окно «Формат линии тренда». Устанавливаем переключатель вида сглаживания в позицию «Степенная». Показ уравнения и уровня достоверности, как всегда, оставляем включенными. Жмем на кнопку «Закрыть».
2. Программа формирует линию тренда. Как видим, в нашем случае она представляет собой линию с небольшим изгибом. Уровень достоверности равен 0,9618, что является довольно высоким показателем. Из всех вышеописанных способов уровень достоверности был выше только при использовании полиномиального метода.

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

Общая формула, описывающая данный метод имеет такой вид:

В конкретно нашем случае она выглядит так:

Как видим, при использовании конкретных данных, которые мы применяли для примера, наибольший уровень достоверности показал метод полиномиальной аппроксимации с полиномом в шестой степени (0,9844), наименьший уровень достоверности у линейного метода (0,9418). Но это совсем не значит, что такая же тенденция будет при использовании других примеров. Нет, уровень эффективности у приведенных выше методов может значительно отличаться, в зависимости от конкретного вида функции, для которой будет строиться линия тренда. Поэтому, если для этой функции выбранный метод наиболее эффективен, то это совсем не означает, что он также будет оптимальным и в другой ситуации.

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