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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

y = ax + b.

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

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

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

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

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

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