Уравнение регрессии в виде экспоненциальной функции

В общем случае уравнение экспоненциальной функции имеет вид y = a e ^bx.

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

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

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

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

Рис. 4.4.1

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

="y="&ТЕКСТ(B25;"0.00")&"e^("&ЕСЛИ(C25<0;ТЕКСТ(C25;"0.00");"+"&ТЕКСТ(C25;"0.00"))&")x"

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

Тогда, в соответствии с выбранными коэффициентами в ячейках B25=1 и C25=0.1, в ячейках A25 и F6получим результат y=1.00e^(+2.00)x.

Запишем в ячейку F7 уравнение экспоненты с коэффициентами, взятыми в абсолютной адресации из ячеек B25 и C25, то есть =$B$25*EXP($C$25*A7), в качестве аргумента X берётся значение ячейки A7исходной таблицы.

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

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

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

Рис. 4.4.2

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

Добавим на точечный рисунок исходной таблицы уравнение экспоненты y=1.00e^2x, рис. 4.4.3.

Рис. 4.4.3

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

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

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

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

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

Рис. 4.4.4

– увидеть, как в ячейке B25 установится значение 5.16, в ячейке C25 – 0.22, в ячейке F19 – 68.04196941, рис. 4.4.5;

Рис. 4.4.5

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

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

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

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

Рис. 4.4.6

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

Рис. 4.4.7

Появившаяся на графике Экспоненциальная регрессия линия тренда не совпадает с графиком построенного уравнения регрессии y = 5.16 e ^0.22x, как и само уравнение линии тренда y = 4.2912 e ^0.0316x, рис. 4.4.8.

Если полученные значения коэффициентов a = 4.2912и b = 0.0316 предложенного уравнения линии тренда подставить в ячейки B25 и C25, соответственно, то в ячейке F19 согласно запрограммированной формуле получим значение суммы квадратов разностей 73.14441113.

Так как значение суммы квадратов разностей 73.14441113при коэффициентах a = 4.2912и b = 0.0316больше значения суммы квадратов разностей 68.04196941 при коэффициентах a = 5.16и b = 0.22, то отдадим предпочтение построенному уравнению регрессии y = 5.16 e ^0.22x.

Рис. 4.4.8

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

На рис. 4.4.9 показан результирующий график использования в качестве уравнения регрессии экспоненциальной функции y = 5.16 e ^0.22x и построения линии тренда y = 4.2912 e ^0.0316x для исходного варианта задания y = f(x).

Рис. 4.4.9