Уравнение регрессии в виде полинома третьей степени

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

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

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

Рис. 4.3.1

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

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

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

="y="&ТЕКСТ(B24;"0.00")&"x^3"&ЕСЛИ(C24<0;ТЕКСТ(C24;"0.00");"+"&ТЕКСТ(C24;"0.00"))&"x^2"&ЕСЛИ(D24<0;ТЕКСТ(D24;"0.00");"+"&ТЕКСТ(D24;"0.00"))&"x"&ЕСЛИ(E24<0;ТЕКСТ(E24;"0.00");"+"&ТЕКСТ(E24;"0.00"))

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

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

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

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

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

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

Рис. 4.3.2

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

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

Рис. 4.3.3

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

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

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

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

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

Рис. 4.3.4

– увидеть, как в ячейке B 24 установится значение - 0.02, в ячейке C 24 – 0.11, в ячейке D 24 – 1.21, в ячейке E 24 – 0.58,в ячейке E 19 – 1.532467553, рис. 4.3.5;

 

Рис. 4.3.5

Это означает, что полином третьей степени с коэффициентами a = - 0.02, b = 0.11, c = 1.21 и c = 0.58 отобразится на графике Кубическая регрессия, как показано на рис. 4.3.5, при этом значение суммы квадратов разностей будет минимально и равно 1.532467553.

Таким образом, коэффициенты для уравнения регрессии в виде полинома третьей степени определены и для исходного задания уравнение имеет вид y =-0.02 x ³ +0.11 x ² +1.21 x +0.58.

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

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

Рис. 4.3.6

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

Рис. 4.3.7

Появившаяся на графике Кубическая регрессия линия тренда полностью совпадает с графиком построенного уравнения регрессии y =-0.02 x ³ +0.11 x ² +1.21 x +0.58, как и уравнение линии тренда y = - 0.0167 x ³ + 0.1097 x ² + 1.2103 x + 0.5758, что является доказательством правильности решения, рис. 4.3.8.

 

Рис. 4.3.8

 

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

На рис. 4.3.9 показан результирующий график использования в качестве уравнения регрессии полинома третьей степени y =-0.02 x ³ +0.11 x ² +1.21 x +0.58 и построения линии тренда y = - 0.0167 x ³ + 0.1097 x ² + 1.2103 x + 0.5758 для исходного варианта задания y = f (x).

Рис. 4.3.9