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

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

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

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

Рис. 4.2.1

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

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

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

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

Ячейке D 6 присвоим такое же значение, какое приобретает ячейка A 23, то есть D 6 = A 23.

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

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

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

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

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

Рис. 4.2.2

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

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

Рис. 4.2.3

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

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

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

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

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

Рис. 4.2.4

– увидеть, как в ячейке B 23 установится значение - 0.22, в ячейке C 23 – 2.97, в ячейке D 23 – -1. 70, в ячейке D 19 – 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