Параболическое уравнение регрессии

В общем случае параболическое уравнение имеет вид y = a x² + b x + c.

Пусть исходная функция y = f(x) варианта задания имеет вид, показанный в таблице в диапазоне ячеек A7:B18, рис. 4.2.1.

Построим точечный график функции Параболическая регрессия y = f(x).

Рис. 4.2.1

В строке 21 создадим шапку таблицы, как показано на рис. 4.2.1.

В ячейку B23 запишем произвольную константу 1, в ячейку C23 – произвольную константу 2, а в ячейку D23 – произвольную константу 3.

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

="y="&ТЕКСТ(B23;"0.00")&"x^2"&ЕСЛИ(C23<0;ТЕКСТ(C23;"0.00");"+"&ТЕКСТ(C23;"0.00"))&"x"&ЕСЛИ(D23<0;ТЕКСТ(D23;"0.00");"+"&ТЕКСТ(D23;"0.00"))

Ячейке D6 присвоим такое же значение, какое приобретает ячейка A23, то есть D6 = A23.

Тогда, в соответствии с выбранными коэффициентами в ячейках B23=1, С23=2 и D23=3, в ячейках A23 и D6получим результат y=1.00x^2+2.00x+3.00.

Запишем в ячейку D7 уравнение параболы с коэффициентами, взятыми в абсолютной адресации из ячеек B23, D23 и C23, то есть =$B$23*A7^2+$C$23*A7+$D$23, в качестве аргумента X берётся значение ячейки A7исходной таблицы.

Скопируем закон преобразования информации ячейки D7 до ячейки D18 включительно.

В результате получим спектр значений функции y=1.00x²+2.00x+3.00на спектре аргументов X в диапазоне значений ячеек A7:A18, рис. 4.2.1.

В ячейку D19, используя мастер функций f_x, запишем результат вычисления функции =СУММКВРАЗН(B7:B18;D7:D18), рис. 4.2.2.

Рис. 4.2.2

Примечание: обозначение Массив_x и Массив_y, рис. 4.2.2, математическое и не совпадает с обозначениями выполняемого задания.

Добавим на точечный рисунок исходной таблицы уравнение параболы y=1.00x²+2.00x+3.00, рис. 4.2.3.

Рис. 4.2.3

Заметим, что это уравнение параболической регрессии, с произвольными значениями коэффициентов a=1, b=2 и c=3.

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

Для определения оптимальных значений коэффициентов a, b и c воспользуемся функцией Поиск решения:

– установим курсор в ячейку C19;

– последовательно, выбирая Разработчик, Данные, Поиск решения, вызвать окно Параметры поиска решения, в котором установить параметры, как показано на рис. 4.2.4, и нажать кнопку Найти решение;

Рис. 4.2.4

– увидеть, как в ячейке B23 установится значение -0.22, в ячейке C23 – 2.97, в ячейке D23 – -1.70, в ячейке D19 – 4.764985223, рис. 4.2.5;

Рис. 4.2.5

Это означает, что парабола с коэффициентами a =-0.2, b = 2.97и c = -1.70 отобразится на графике Параболическая регрессия, как показано на рис. 4.2.5, при этом значение суммы квадратов разностей будет минимально и равно4.764985223.

Таким образом, коэффициенты для параболического уравнения регрессии определены и для исходного задания уравнение имеет вид y =-0.2 x² + 2.97 x - 1.70.

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

В появившемся окне выберем раздел Добавить линию тренда, рис. 4.2.6.

Рис. 4.2.6

В появившемся окне Формат линии тренда выбрать параметры Полиномиальная, Степень 2, показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2) и нажать кнопку Закрыть, рис. 4.2.7.

Рис. 4.2.7

Появившаяся на графике Параболическая регрессия линия тренда полностью совпадает с графиком построенного уравнения регрессии y =-0.2 x² + 2.97 x - 1.70, как и уравнение линии тренда y =-0.216 x² + 2.9728 x - 1.7045, что является доказательством правильности решения, рис. 4.2.8.

Рис. 4.2.8

Следует отметить, что значение коэффициента детерминации R² = 0.9328 свидетельствует о том, что выбранный вид уравнения регрессии (параболическая функция) лучше, чем линейная подходит к исходному заданию, так как максимальное значение коэффициента детерминации R² = 1.

На рис. 4.2.9 показан результирующий график использования в качестве уравнения регрессии параболической функции y =-0.2 x² + 2.97 x - 1.70 и построения линии тренда y =-0.216 x² + 2.9728 x - 1.7045для исходного варианта задания y = f(x).

Рис. 4.2.9