Аппроксимация функции с помощью линии тренда
Аппроксимация, или приближение - научный метод, состоящий в замене одних объектов другими, в том или ином смысле близкими к исходным, но более простыми. В задачах, рассматриваемых в данном разделе и в следующем, используются исходные данные, полученные в результате табуляции заданной функции. Следует помнить, что в реальных задачах исходными данными являются результаты наблюдений (проведение опытов, научных экспериментов, наблюдение реальных событий и т.п.), которые подвержены ошибкам измерения и другим случайным факторам. Задача исследователя - подобрать по исходным точкам (которые на первый взгляд расположены хаотично) функциональную зависимость (если это вообще возможно), которая наилучшим образом описывает распределение исходных данных и в некоторых случаях попытаться сделать прогноз дальнейшего развития (например исследование временно́го ряда изменения котировок акций).
Задание. Построить таблицу значений функции F(x)=ax²+bx+c для 11 значений аргумента x в диапазоне –1 ≤ x ≤ +1. Построить график этой функции, затем выполнить аппроксимацию линиями тренда двух типов. С помощью линий тренда построить прогноз на два периода вперёд.
Как и в предыдущих задачах вводим исходные данные: начальное значение аргумента функции Xn, конечное значение аргумента функции Xk, количество точек разбиения функции (количество строк таблицы) N, формулу для шага аргумента функции dX, коэффициенты a, b, c, затем создаем основную таблицу и строим диаграмму (все эти действия были подробно описаны в разделе Табулирование функции одной переменной):
Как видно, в этой работе в формулах используются имена для ячеек, содержащих исходные значения (работа с именами была подробно описана в разделе Присваивание имен ячейкам).
Линии тренда на диаграмме
Линии тренда позволяют графически отображать тенденции изменения данных и прогнозировать их дальнейшие изменения. Подобный анализ называется также регрессионным анализом. Используя регрессионный анализ, можно продлить линию тренда в диаграмме за пределы реальных данных для предсказания будущих значений.
Линии тренда могут быть построены на всех двухмерных диаграммах (линию тренда нельзя добавить на объемных, лепестковых, круговых, кольцевых и пузырьковых диаграммах).
Существует шесть различных видов линий тренда:
- Линейная
- Полиномиальная
- Логарифмическая
- Экспоненциальная
- Степенная
- Скользящее среднее (линейная фильтрация)
Линии тренда, добавленные к графику функции, на сами данные и исходную диаграмму никак не влияют.
Формулы для вычисления линий тренда
Линейная. Используется для линейной аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
где: m - угол наклона, b - координата пересечения оси абсцисс.
Полиномиальная. Используется для полиномиальной или криволинейной аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
где: b, c1, c2, … c6 - константы.
Можно задать степень полинома от 2 до 6.
Логарифмическая. Используется для логарифмической аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
где: c и b - константы, ln - функция натурального логарифма.
Экспоненциальная. Используется для экспоненциальной аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
где: c и b - константы, e - основание натурального логарифма.
Степенная. Используется для степенной аппроксимации данных по методу наименьших квадратов в соответствии с уравнением:
где: c и b - константы.
Примечание. Экспоненциальная и степенная виды аппроксимации недоступны, если значения функции F(x) содержат отрицательные или нулевые значения. Кроме того, логарифмическая и степенная виды аппроксимации недоступны, если значения аргумента функции x содержат отрицательные или нулевые значения. Поскольку в заданиях к лабораторным работам используется отрицательное значение нижней границы аргумента Xn (x0), не выбирайте логарифмическую и степенную виды аппроксимации!
Скользящее среднее (линейная фильтрация). Скользящее среднее - это среднее значение за определенный период:
На диаграмме линия, построенная по точкам скользящего среднего, позволяет построить сглаженную кривую, более ясно показывающую закономерность в развитии данных.
Добавление линии тренда к рядам данных
Выделяем диаграмму (щелкаем в любом пустом месте диаграммы), после чего на ленте меню появятся три дополнительные вкладки: Конструктор, Макет и Формат. На вкладке Макет в группе Анализ щелкаем по кнопке Линия тренда:
В открывшемся списке выбираем требуемый вид аппроксимации:
Через это меню удобно убирать все линии тренда с диаграммы, для этого используется первый пункт Нет.
Другой вариант. На диаграмме точно наводим курсор на любое место кривой функции, затем щелкаем правой кнопкой мыши (при этом на кривой в узлах появляются “пупырышки”). В открывшемся контекстном меню выбираем пункт Добавить линию тренда…:
Появится диалоговое окно Формат линии тренда:
На первой закладке выбираем вид аппроксимации (не выбирайте логарифмическую и степенную, если есть отрицательные значения аргумента функции x). Для того, чтобы рядом с линией тренда выводилось уравнение аппроксимирующей функции и величина достоверности аппроксимации R², поставьте галочки в соответствующих местах. Для дополнительной настройки вида линии тренда (цвет, толщина и т.п.) используйте закладки Цвет линии и Тип линии.
При выборе типа Полиномиальная в поле Степень можно указать степень аппроксимирующего полинома (от 2 до 6). При выборе типа Линейная фильтрация в поле Точки нужно задать число периодов, используемых для расчета скользящего среднего (от 2 до 10).
Величина достоверности R² показывает, насколько точно линия тренда аппроксимирует исходную функцию. При идеальном совпадении R²=1. На диаграмме видно, что экспоненциальная кривая в данном случае точнее описывает исходную функцию, чем линейная:
Для того, чтобы изменить параметры уже добавленной линии тренда, нужно точно навести курсор на любое место линии тренда, затем щелкнуть правой кнопкой мыши и в открывшемся контекстном меню выбрать пункт Формат линии тренда…:
