Экспоненциальная регрессия

Пример 3. а) Функция ЛГРФПРИБЛ. Условие примера 2.

Поскольку функция в табл. 2 носит явно нелинейный характер, целесообразно искать её приближение в виде не прямой линии, как в примере 2, а в виде нелинейной кривой. Из всех видов нелинейности (гипербола, парабола и др.) Excel реализует только экспоненциальное приближение вида у = b∙m^x c помощью функции ЛГРФПРИБЛ, которая рассчитывает для этого уравнения значения b и m. Функция ЛГРФПРИБЛ запускается точно также, как и функция ЛИНЕЙН.

Выделим для результата блок ячеек F8:G12, введём в строку формул функцию =ЛГРФПРИБЛ(E2:E7;D2:D7;1;1), нажмём клавиши Ctrl+Shift+ Enter, в выделенных ячейках появится результат – табл. 4.5:

Таблица 4.5

1,56628015	1,196513
0,02038299	0,07938
0,99181334	0,085268
484,599687
3,52335921	0,029083

Таким образом, коэффициент m = 1,556, а b = 1,197, т.е. уравнение приближающей кривой имеет вид:

у = 1,197∙(1,556 ^х) (13)

со стандартными ошибками для m, b и y равными 0,02, 0,07 и 0,08 соответственно. Коэффициент детерминированности r² = 0,992, т.е. полученное уравнение даёт совпадение с табличными данными с вероятностью 99,2%.

Поскольку интерполяция табл. 2 экспоненциальной кривой даёт более точное приближение (99,2%) и с меньшими стандартными ошибками для m, b и y, в качестве приближающего уравнения принимаем уравнение (13).

При х = 8 получим у = 1,197 ∙ 34,363 = 41,131± 0,085 ^○С.

б) Функция РОСТ вычисляет прогнозируемое по экспоненциальному приближению значения у для новых значений х, имеет формат:

=РОСТ(изв_знач_у;изв_знач_х;нов_знач_х;константа).

Выделим блок ячеек F14:F17, введём формулу

=РОСТ(E2:E7;D2:D7;G2:G5;ИСТИНА), в выделенных ячейках появится массив чисел {27,6696434; 43,3384133; 67,8800967; 106,319248}, т.е. при х=8 значение функции у = 43,34 град. Это значение немного отличается от вычисленного в п. а), поскольку функция РОСТ использует для расчетов линию экспоненциального тренда.

Примечание. При выборе экспоненциальной приближающей кривой следует учитывать, что интерполировать ею можно только участки, где функция монотонно возрастает или убывает (при отрицательном аргументе х), т.е. функцию, имеющую точки перегиба (например, параболу, синусоиду, кривую рис. 4.2 и др.) следует разбить на участки монотонного изменения от одной точки перегиба до другой и каждый участок интерполировать отдельно. Для рис. 4.2 функцию нужно разбить на 3 участка.