Дробно-линейное уравнение регрессии

В общем случае уравнение дробно-линейной функции имеет вид y = 1/(ax + b).

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

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

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

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

Рис. 4.7.1

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

="y="&"1/("&ТЕКСТ(B28;"0.00")&"x"&ЕСЛИ(C28<0;ТЕКСТ(C28;"0.00");"+"&ТЕКСТ(C28;"0.00"))&")"

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

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

Запишем в ячейку I7 уравнение дробно-линейной функции с коэффициентами, взятыми в абсолютной адресации из ячеек B28 и C28, то есть =1/($B$28*A7+$C$28), в качестве аргумента X берётся значение ячейки A7исходной таблицы.

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

В результате получим спектр значений функции y = 1/(x + 2) на спектре аргументов X в диапазоне значений ячеек A7:A18, рис. 4.7.1.

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

Рис. 4.7.2

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

Добавим на точечный рисунок исходной таблицы уравнение дробно-линейной функции y = 1/(x + 2), рис. 4.7.3.

Рис. 4.7.3

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

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

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

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

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

Рис. 4.7.4

– увидеть, как в ячейке B28 установится значение -0.0028, в ячейке C28 – 0.19, в ячейке I19 – 68.63295607, рис. 4.7.5;

Рис. 4.7.5

Это означает, что дробно-линейная функция с коэффициентами a = -0.0028 и b = 0.19отобразится на графике Дробно-линейная регрессия, как показано на рис. 4.7.5, при этом значение суммы квадратов разностей будет минимально и равно 68.63295607.

Таким образом, коэффициенты для дробно-линейного уравнения регрессии определены и для исходного задания уравнение имеет вид y = 1/(-0.0028 x + 0.19).

Так как в библиотеке линий тренда отсутствует дробно-линейная функция, то проверить правильность решения добавлением линии тренда не представляется возможным.

На рис. 4.7.6 показан результирующий график использования в качестве уравнения регрессии дробно-линейной функции y = 1/(-0.0028 x + 0.19)для исходного варианта задания y = f(x).

Рис. 4.7.6