Степенное уравнение регрессии

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

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

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

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

В ячейку B27 запишем произвольную константу 1, а в ячейку C27 - произвольную константу 2.

Рис. 4.6.1

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

="y="&ТЕКСТ(B27;"0.00")&"x^("&ЕСЛИ(C27<0;ТЕКСТ(C27;"0.00");"+"&ТЕКСТ(C27;"0.00"))&")"

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

Тогда, в соответствии с выбранными коэффициентами в ячейках B27=1 и C27=2, в ячейках A27 и H6получим результат y=1.00 x^(+2.00).

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

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

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

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

Рис. 4.6.2

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

Добавим на точечный рисунок исходной таблицы уравнение логарифмической функции y=1.00x², рис. 4.6.3.

Рис. 4.6.3

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

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

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

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

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

Рис. 4.6.4

– увидеть, как в ячейке B27 установится значение 4.03, в ячейке C27 – 0.22, в ячейке H19 – 58.40875563, рис. 4.6.5;

Рис. 4.6.5

Это означает, что степенная функция с коэффициентами a = 4.03 и b = 0.22 отобразится на графике Степенная регрессия, как показано на рис. 4.6.5, при этом значение суммы квадратов разностей будет минимально и равно 58.4086725.

Таким образом, коэффициенты для степенного уравнения регрессии определены и для исходного задания уравнение имеет вид y=4.03 x ^0.22.

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

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

Рис. 4.6.6

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

Рис. 4.6.7

Появившаяся на графике Степенная регрессия линия тренда не совпадает с графиком построенного уравнения регрессии y=4.03 x ^0.22, как и уравнение линии тренда y=3.007 x ^0.3367, рис. 4.6.8.

Рис. 4.6.8

Если полученные значения коэффициентов a = 3.007и b = 0.3367 предложенного уравнения линии тренда подставить в ячейки B27 и C27, соответственно, то в ячейке H19 согласно запрограммированной формуле получим значение суммы квадратов разностей 63.25460832.

Так как значение суммы квадратов разностей 63.25460832при коэффициентах a = 3.007и b = 0.3367 больше значения суммы квадратов разностей 58.4086725при коэффициентах a = 4.03 и b = 0.22, то отдадим предпочтение построенному уравнению регрессии y = 4.04 x ^0.22.

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

На рис. 4.6.9 показан результирующий график использования в качестве уравнения регрессии степенной функции y=4.03 x ^0.22 и построения линии тренда y=3.007 x ^0.3367для исходного варианта задания y = f(x).

Рис. 4.6.9