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

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

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

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

Рис. 4.3.1

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

В ячейку B24 запишем произвольную константу 1, в ячейку C24 – произвольную константу 2, в ячейку D24 – произвольную константу 3,а в ячейку E24 – произвольную константу 4.

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

="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"))

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

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

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

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

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

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

Рис. 4.3.2

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

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

Рис. 4.3.3

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

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

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

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

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

Рис. 4.3.4

– увидеть, как в ячейке B24 установится значение -0.02, в ячейке C24 – 0.11, в ячейке D24 – 1.21, в ячейке E24 – 0.58,в ячейке E19 – 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.02x³+0.11x²+1.21x+0.58.

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

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

Рис. 4.3.6

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

Рис. 4.3.7

Появившаяся на графике Кубическая регрессия линия тренда полностью совпадает с графиком построенного уравнения регрессии y=-0.02x³+0.11x²+1.21x+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.02x³+0.11x²+1.21x+0.58 и построения линии тренда y =-0.0167 x³ + 0.1097 x² + 1.2103 x + 0.5758для исходного варианта задания y = f(x).

Рис. 4.3.9