Аппроксимация в Excel статистических данных аналитической функцией.
Производственный участок изготавливает строительные металлоконструкции из листового и профильного металлопроката. Участок работает стабильно, заказы однотипные, численность рабочих колеблется незначительно. Есть данные о выпуске продукции за предыдущие 12 месяцев и о количестве переработанного в эти периоды времени металлопроката по группам: листы, двутавры, швеллеры, уголки, трубы круглые, профили прямоугольного сечения, круглый прокат. После предварительного анализа исходных данных возникло предположение, что суммарный месячный выпуск металлоконструкций существенно зависит от количества уголков в заказах. Проверим это предположение.
Прежде всего, несколько слов об аппроксимации. Мы будем искать закон – аналитическую функцию, то есть функцию, заданную уравнением, которое лучше других описывает зависимость общего выпуска металлоконструкций от количества уголкового проката в выполненных заказах. Это и есть аппроксимация, а найденное уравнение называется аппроксимирующей функцией для исходной функции, заданной в виде таблицы.
1. Включаем Excel и помещаем на лист таблицу с данными статистики.
2. Далее строим и форматируем точечную диаграмму, в которой по оси X задаем значения аргумента – количество переработанных уголков в тоннах. По оси Y откладываем значения исходной функции – общий выпуск металлоконструкций в месяц, заданные таблицей.
О том, как построить подобную диаграмму, подробно рассказано в статье «Как строить графики в Excel?».
3. «Наводим» мышь на любую из точек на графике и щелчком правой кнопки вызываем контекстное меню (как говорит один мой хороший товарищ — работая в незнакомой программе, когда не знаешь, что делать, чаще щелкай правой кнопкой мыши…). В выпавшем меню выбираем «Добавить линию тренда…».
4. В появившемся окне «Линия тренда» на вкладке «Тип» выбираем «Линейная».
5. Далее на вкладке «Параметры» ставим 2 галочки и нажимаем «ОК».
6. На графике появилась прямая линия, аппроксимирующая нашу табличную зависимость.
Мы видим кроме самой линии уравнение этой линии и, главное, мы видим значение параметра R2 – величины достоверности аппроксимации! Чем ближе его значение к 1, тем наиболее точно выбранная функция аппроксимирует табличные данные!
7. Строим линии тренда, используя степенную, логарифмическую, экспоненциальную и полиномиальную аппроксимации по аналогии с тем, как мы строили линейную линию тренда.
Лучше всех из выбранных функций аппроксимирует наши данные полином второй степени, у него максимальный коэффициент достоверности R2.
Однако хочу вас предостеречь! Если вы возьмете полиномы более высоких степеней, то, возможно, получите еще лучшие результаты, но кривые будут иметь замысловатый вид…
Здесь важно понимать, что мы ищем функцию, которая имеет физический смысл
Что это означает? Это означает, что нам нужна аппроксимирующая функция, которая будет выдавать адекватные результаты не только внутри рассматриваемого диапазона значений X, но и за его пределами, то есть ответит на вопрос: «Какой будет выпуск металлоконструкций при количестве переработанных за месяц уголков меньше 45 и больше 168 тонн!» Поэтому я не рекомендую увлекаться полиномами высоких степеней, да и параболу (полином второй степени) выбирать осторожно!
Итак, нам необходимо выбрать функцию, которая не только хорошо интерполирует табличные данные в пределах диапазона значений X=45…168, но и допускает адекватную экстраполяцию за пределами этого диапазона. Я выбираю в данном случае логарифмическую функцию, хотя можно выбрать и линейную, как наиболее простую. В рассматриваемом примере при выборе линейной аппроксимации в excel ошибки будут больше, чем при выборе логарифмической, но не на много.
8. Удаляем все линии тренда с поля диаграммы, кроме логарифмической функции. Для этого щелкаем правой кнопкой мыши по ненужным линиям и в выпавшем контекстном меню выбираем «Очистить».
9. В завершении добавим к точкам табличных данных планки погрешностей. Для этого правой кнопкой мыши щелкаем на любой из точек на графике и в контекстном меню выбираем «Формат рядов данных…» и настраиваем данные на вкладке «Y-погрешности» так, как на рисунке ниже.
10. Затем щелкаем по любой из линий диапазонов погрешностей правой кнопкой мыши, выбираем в контекстном меню «Формат полос погрешностей…» и в окне «Формат планок погрешностей» на вкладке «Вид» настраиваем цвет и толщину линий.
Аналогичным образом форматируются любые другие объекты диаграммы в Excel!
Окончательный результат диаграммы представлен на следующем снимке экрана.
МНК: Приближение полиномом в EXCEL
Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью полинома (до 6-й степени включительно).
В основной статье про МНК было рассмотрено приближение линейной функцией. В этой статье рассмотрим приближение полиномиальной функцией (с 3-й до 6-й степени) следующего вида: y=b +b 1 x+b 2 x 2 +b 3 x 3 +…+b 6 x 6
Примечание : В инструменте MS EXCEL Линия тренда , который доступен для диаграмм типа Точечная и График , можно построить линию тренда на основе полинома с максимальной степенью 6. В файле примера продемонстрировано полное совпадение линии тренда диаграммы и линии, вычисленной с помощью формул.
Покажем, как вычислить коэффициенты b линии тренда, заданной полиномом.
Как известно, квадратичная зависимость y=b +b 1 x+b 2 x 2 , подробно рассмотренная в статье МНК: Квадратичная зависимость в MS EXCEL , является частным случаем полиномиальной y=b +b 1 x+b 2 x 2 +b 3 x 3 +… зависимости (в этом случае степень полинома равна 2). Соответственно, используя тот же подход (приравнивание к 0 частных производных), можно вычислить коэффициенты любого полинома.
Примечание : Существует еще один метод вычисления коэффициентов – замена переменных, который рассмотрен в конце статьи.
Для нахождения m+1 коэффициента полинома m-й степени составим систему из m+1 уравнения и решим ее методом обратной матрицы . Для квадратного уравнения (m=2) нам потребовалось вычислить сумму значений х с 1-й до 4-й степени, а для полинома m-й степени необходимо вычислить значения х с 1-й до 2*m степени.
Примечание : Для удобства суммы степеней значений х можно вычислить в отдельном диапазоне ( файл примера столбцы К:М).
В файле примера создана универсальная форма для вычисления коэффициентов полиномов.
Выбрав с помощью элемента управления Счетчик нужную степень полинома, автоматически получим аппроксимацию наших данных выбранным полиномом (будет построен соответствующий график).
Примечание: При использовании полиномов высокой степени необходимо следить за тем, чтобы количество пар значений (х i ; y i ) превышало степень полинома хотя бы на несколько значений (для обеспечения точности аппроксимации). Кроме того, график функции полинома степени m имеет m-1 точку перегиба. Понятно, что точек данных должно быть гораздо больше, чем точек перегиба, чтобы такой изменчивый тренд стал очевидным (если утрировать, то бессмысленно строить по двум точкам параболу, логичнее построить прямую).
Как видно из расчетов, в MS EXCEL этот путь является достаточно трудоемким. Гораздо проще в MS EXCEL реализовать другой подход для вычисления коэффициентов полинома — с помощью замены переменных.
С помощью замены переменных x i =x i полиномиальную зависимость y=b +b 1 x+b 2 x 2 +b 3 x 3 +… можно свести к линейной. Теперь переменная y зависит не от одной переменной х в m разных степенях, а от m независимых переменных x i . Поэтому для нахождения коэффициентов полинома мы можем использовать функцию ЛИНЕЙН() . Этот подход также продемонстрирован в файле примера .
Некоторые частные случаи применения МНК на практике
Аппроксимация линейной зависимости
Рассмотрим случай, когда в результате изучения зависимости некоторой скалярной величины от некоторой скалярной величины (Это может быть, например, зависимость напряжения от силы тока : , где — постоянная величина, сопротивление проводника) было проведено измерений этих величин, в результате которых были получены значения и соответствующие им значения . Данные измерений должны быть записаны в таблице.
Таблица.
Результаты измерений.
| № измерения | ||
|---|---|---|
| 1 | ||
| 2 | ||
| 3 | ||
| 4 | ||
| 5 | ||
| 6 |
Вопрос звучит так: какое значение коэффициента можно подобрать, чтобы наилучшим образом описать зависимость ? Согласно МНК это значение должно быть таким, чтобы сумма квадратов отклонений величин от величин
была минимальной
Сумма квадратов отклонений имеет один экстремум — минимум, что позволяет нам использовать эту формулу . Найдём из этой формулы значение коэффициента . Для этого преобразуем её левую часть следующим образом:
Последняя формула позволяет нам найти значение коэффициента , что и требовалось в задаче.
Сущность МНК
Пусть задана некоторая (параметрическая) модель вероятностной (регрессионной) зависимости между (объясняемой) переменной y
и множеством факторов (объясняющих переменных) x
где — вектор неизвестных параметров модели
Пусть также имеются выборочные наблюдения значений указанных переменных. Пусть — номер наблюдения (). Тогда — значения переменных в -м наблюдении. Тогда при заданных значениях параметров b можно рассчитать теоретические (модельные) значения объясняемой переменной y:
Величина остатков зависит от значений параметров b.
Сущность МНК (обычного, классического) заключается в том, чтобы найти такие параметры b, при которых сумма квадратов остатков (англ. Residual Sum of Squares
) будет минимальной:
В общем случае решение этой задачи может осуществляться численными методами оптимизации (минимизации). В этом случае говорят о нелинейном МНК
(NLS или NLLS — англ. Non-Linear Least Squares
). Во многих случаях можно получить аналитическое решение. Для решения задачи минимизации необходимо найти стационарные точки функции , продифференцировав её по неизвестным параметрам b, приравняв производные к нулю и решив полученную систему уравнений:
Если случайные ошибки модели имеют нормальное распределение , имеют одинаковую дисперсию и некоррелированы между собой, МНК-оценки параметров совпадают с оценками метода максимального правдоподобия (ММП) .
МНК в случае линейной модели
Пусть регрессионная зависимость является линейной:
Пусть y
— вектор-столбец наблюдений объясняемой переменной, а — матрица наблюдений факторов (строки матрицы — векторы значений факторов в данном наблюдении, по столбцам — вектор значений данного фактора во всех наблюдениях). Матричное представление линейной модели имеет вид:
Тогда вектор оценок объясняемой переменной и вектор остатков регрессии будут равны
соответственно сумма квадратов остатков регрессии будет равна
Дифференцируя эту функцию по вектору параметров и приравняв производные к нулю, получим систему уравнений (в матричной форме):
Решение этой системы уравнений и дает общую формулу МНК-оценок для линейной модели:
Для аналитических целей оказывается полезным последнее представление этой формулы. Если в регрессионной модели данные центрированы
, то в этом представлении первая матрица имеет смысл выборочной ковариационной матрицы факторов, а вторая — вектор ковариаций факторов с зависимой переменной. Если кроме того данные ещё и нормированы
на СКО (то есть в конечном итоге стандартизированы
), то первая матрица имеет смысл выборочной корреляционной матрицы факторов, второй вектор — вектора выборочных корреляций факторов с зависимой переменной.
Немаловажное свойство МНК-оценок для моделей с константой
— линия построенной регрессии проходит через центр тяжести выборочных данных, то есть выполняется равенство:
В частности, в крайнем случае, когда единственным регрессором является константа, получаем, что МНК-оценка единственного параметра (собственно константы) равна среднему значению объясняемой переменной. То есть среднее арифметическое, известное своими хорошими свойствами из законов больших чисел, также является МНК-оценкой — удовлетворяет критерию минимума суммы квадратов отклонений от неё.
Линейная зависимость
Нас интересует связь 2-х переменных х и y . Имеется предположение, что y зависит от х по линейному закону y = ax + b . Чтобы определить параметры этой взаимосвязи исследователь провел наблюдения: для каждого значения х i произведено измерение y i (см. файл примера ). Соответственно, пусть имеется 20 пар значений (х i ; y i ).
Для наглядности рекомендуется построить диаграмму рассеяния.
Примечание: Если шаг изменения по х постоянен, то для построения диаграммы рассеяния можно использовать тип График , если нет, то необходимо использовать тип диаграммы Точечная .
Из диаграммы очевидно, что связь между переменными близка к линейной. Чтобы понять какая из множества прямых линий наиболее «правильно» описывает зависимость между переменными, необходимо определить критерий, по которому будут сравниваться линии.
В качестве такого критерия используем выражение:
где ŷ i = a * x i + b ; n – число пар значений (в нашем случае n=20)
Вышеуказанное выражение представляет собой сумму квадратов расстояний между наблюденными значениями y i и ŷ i и часто обозначается как SSE ( Sum of Squared Errors ( Residuals ), сумма квадратов ошибок (остатков) ) .
Метод наименьших квадратов заключается в подборе такой линии ŷ = ax + b , для которой вышеуказанное выражение принимает минимальное значение.
Примечание: Любая линия в двухмерном пространстве однозначно определяется значениями 2-х параметров: a (наклон) и b (сдвиг).
Считается, что чем меньше сумма квадратов расстояний, тем соответствующая линия лучше аппроксимирует имеющиеся данные и может быть в дальнейшем использована для прогнозирования значений y от переменной х. Понятно, что даже если в действительности никакой взаимосвязи между переменными нет или связь нелинейная, то МНК все равно подберет «наилучшую» линию. Таким образом, МНК ничего не говорит о наличии реальной взаимосвязи переменных, метод просто позволяет подобрать такие параметры функции a и b , для которых вышеуказанное выражение минимально.
Проделав не очень сложные математические операции (подробнее см. статью про квадратичную зависимость ), можно вычислить параметры a и b :
Как видно из формулы, параметр a представляет собой отношение ковариации и дисперсии , поэтому в MS EXCEL для вычисления параметра а можно использовать следующие формулы (см. файл примера лист Линейная ):
= КОВАР(B26:B45;C26:C45)/ ДИСП.Г(B26:B45) или
Также для вычисления параметра а можно использовать формулу = НАКЛОН(C26:C45;B26:B45) . Для параметра b используйте формулу = ОТРЕЗОК(C26:C45;B26:B45) .
И наконец, функция ЛИНЕЙН() позволяет вычислить сразу оба параметра. Для ввода формулы ЛИНЕЙН(C26:C45;B26:B45) необходимо выделить в строке 2 ячейки и нажать CTRL + SHIFT + ENTER (см. статью про формулы массива, возвращающими несколько значений ). В левой ячейке будет возвращено значение а , в правой – b .
Примечание : Чтобы не связываться с вводом формул массива потребуется дополнительно использовать функцию ИНДЕКС() . Формула = ИНДЕКС(ЛИНЕЙН(C26:C45;B26:B45);1) или просто = ЛИНЕЙН(C26:C45;B26:B45) вернет параметр, отвечающий за наклон линии, т.е. а . Формула = ИНДЕКС(ЛИНЕЙН(C26:C45;B26:B45);2) вернет параметр, отвечающий за пересечение линии с осью Y, т.е. b .
Вычислив параметры, на диаграмме рассеяния можно построить соответствующую линию.
Еще одним способом построения прямой линии по методу наименьших квадратов является инструмент диаграммы Линия тренда . Для этого выделите диаграмму, в меню выберите вкладку Макет , в группе Анализ нажмите Линия тренда , затем Линейное приближение .
Поставив в диалоговом окне галочку в поле «показывать уравнение на диаграмме» можно убедиться, что найденные выше параметры совпадают со значениями на диаграмме.
Примечание : Для того, чтобы параметры совпадали необходимо, чтобы тип у диаграммы был Точечная, а не График . Дело в том, что при построении диаграммы График значения по оси Х не могут быть заданы пользователем (пользователь может указать только подписи, которые не влияют на расположение точек). Вместо значений Х используется последовательность 1; 2; 3; … (для нумерации категорий). Поэтому, если строить линию тренда на диаграмме типа График , то вместо фактических значений Х будут использованы значения этой последовательности, что приведет к неверному результату (если, конечно, фактические значения Х не совпадают с последовательностью 1; 2; 3; …).
СОВЕТ : Подробнее о построении диаграмм см. статьи Основы построения диаграмм и Основные типы диаграмм .