2015-05-15
817
Пример 3. а) Функция ЛГРФПРИБЛ.
Условие примера 2.
Поскольку функция в табл. 2 носит явно нелинейный характер, целесообразно искать её приближение в виде не прямой линии, как в примере 2, а в виде нелинейной кривой. Из всех видов нелинейности (гипербола, парабола и др.) Excel реализует только экспоненциальное приближение вида у = b∙mx c помощью функции ЛГРФПРИБЛ, которая рассчитывает для этого уравнения значения b и m.
Выделим для результата блок ячеек F8:G12, введём в строку формул
Функцию =ЛГРФПРИБЛ(E2:E7;D2:D7;1;1), нажмём клавиши Ctrl+Shift+ Enter, в выделенных ячейках появится результат:
| 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 соответ-ственно. Коэффициент детерминированности r2 = 0,992, т.е.
полученное уравнение даёт совпадение с табличными данными с вероятностью 99,2%.
Поскольку интерполяция табл. 2 экспоненциальной кривой даёт более точное приближение (99,2%) и с меньшими стандартными ошибками для m,b и y, в качестве приближающего уравнения принимаем уравнение (13).
|
|
|
При х = 8 получим у = 1,197 ∙ 34,363 = 41,131 град.
б) Функция РОСТ вычисляет прогнозируемое по экспоненциальному приближению значения у для новых значений х, имеет формат:
=РОСТ (изв_знач_ у;изв_знач_ х;нов_знач_ х; константа).
Выделим блок ячеек F14:F17, введём формулу
=РОСТ(E2:E7;D2:D7;G2:G5;ИСТИНА), в выделенных ячейках появится массив чисел {27,6696434; 43,3384133; 67,8800967; 106,319248}, т.е. при х=8 значение функции у = 43,34 град. Это значение немного отличается от вычисленного в п. а), поскольку функция РОСТ использует для расчетов линию экспоненциального тренда.
Примечание. При выборе экспоненциальной приближающей кривой следует учитывать, что интерполировать ею можно только участки, где функция монотонно возрастает или убывает (при отрицательном аргументе х), т.е. функцию, имеющую точки перегиба (например, параболу, синусоиду, кривую рис. 2 – т..А и др.) следует разбить на участки монотонного изменения от одной точки перегиба до другой и каждый участок интерполировать отдельно. Для рис. 2 функцию нужно разбить на 2 участка – от начала до т..А и от т.А до конца кривой.
