МНК: Квадратичная зависимость в MS EXCEL

Метод наименьших квадратов (МНК) основан на минимизации суммы квадратов отклонений выбранной функции от исследуемых данных. В этой статье аппроксимируем имеющиеся данные с помощью квадратичной функции y=ax²+bx+с .

Метод наименьших квадратов (англ. Ordinary Least Squares, OLS) является одним из базовых методов регрессионного анализа в части оценки неизвестных параметров регрессионных моделей по выборочным данным. Основная статья про МНК - МНК: Метод Наименьших Квадратов в MS EXCEL.

Для построения графика квадратичной зависимости y=ax²+bx+с вспомним исходный критерий МНК, который необходимо минимизировать:

Теперь ŷ_i= a*x_i²+b*x_i+с и мы имеем зависимость от 3-х параметров полинома второй степени: a, b и с.

Вышеуказанное выражение примет минимальное значение при таких параметрах a, b и с, при которых соответствующие частные производные функции F равны нулю, т.е.:

В результате вычислений и преобразований получим систему из 3-х линейных уравнений:

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

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

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

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