Кубическая функция

Квадратичная функция

Необходимо определить параметры функции y = a_o + a₁x + a₂x₂.

Составим функцию:

Для этой функции запишем систему уравнений (8.2):

(8.6)

Для нахождения параметров a_o, a₁, a₂ необходимо решить систему линейных алгебраических уравнений (8.6).

Необходимо определить параметры многочлена третьей степени y = a_o + a₁ x + a₂ x₂ + a₃ x₃.

Составим функцию S:

Система уравнений для нахождения параметров a_o, a₁, a₂, a₃ имеет вид:

(8.7)

Для нахождения параметров a_o, a₁, a₂, a₃ необходимо решить систему четырёх линейных алгебраических уравнений.

Если в качестве аналитической зависимости выберем многочлен k -й степени y = a_o+a₁x+...+a_k x^k, то система уравнений для определения параметров a_i принимает вид:

(8.8)

Подбор параметров функции y = a x^b

Для нахождения параметров функции y = a x^b проведем логарифмирование функции y: Ln y = Ln a + b Ln x

Сделаем замену Y = ln y; X = ln x. Получим линейную зависимость Y = A + b X. Найдем коэффициенты линии регрессии A и b. определяем a = e^A. Мы получили значение параметров функции y = ax^b.

Подбор параметров функции y = ae^bx

Прологарифмируем выражение y = ae^bx: Ln y = Ln a + bx Ln e;

Проведём замену Y = Ln y, A = Ln a. Вновь получаем линейную зависимость Y = bx+A. Найдем A и b. Затем определим a = e^A.

Ниже приведены замены переменных, которые преобразовывают функции вида y = f (x, a, b) к линейной зависимости Y = Ax+B.

Y = f (x,a,b)	Замена

Подбор параметров функции y =a^xb e^cx

Прологарифмируем выражение y = a^xb e^cx, после логарифмирования оно принимает вид:

Сделаем замену Y=Ln(y), A=Ln(a). После замены выражение (8.9) принимает вид:

Для функции (8.10) составим функцию S см. формулу (8.1):

(8.11)

Параметры A, b и c следует выбрать таким образом, чтобы функция S была минимальной. Необходимым условием минимума S являются соотношения (2). Подставим (8.11) в (8.2), и после элементарных преобразований получим систему трёх линейных алгебраических уравнений для определения коэффициентов A, b и c.

(8.12)

Решив систему (8.12), получим значения A, b, c. После чего вычисляем a=e^A.

Построение различных аппроксимирующих зависимостей в MS Excel реализовано в виде свойства диаграммы - линия тренда.

ПРИМЕР 8.2. В результате эксперимента была определена некоторая табличная зависимость. Выбрать и построить аппроксимирующую зависимость. Построить графики табличной и подобранной аналитической зависимости. Вычислить ожидаемое значение в указанных точках.

Решение задачи можно разбить на следующие этапы:

1. Ввод исходных данных и построение точечного графика (см. рис. 8.9).
2. Добавление к этому графику линии тренда.

Рассмотрим этот процесс подробно.

Рис. 8.9

Выделим экспериментальные точки на графике, щелкнем правой кнопкой мыши и воспользуемся командой Добавить линию тренда. Появившееся диалоговое окно (см. рис. 8.10) позволяет построить аппроксимирующую зависимость. На первой вкладке этого окна указывается вид аппроксимирующей зависимости (в нашем случае необходимо выбрать полиномиальную зависимость второй степени). На второй определяются параметры построения:

· Название аппроксимирующей зависимости.

· Прогноз вперед (назад) на n единиц (этот параметр определяет, на какое количество единиц вперед (назад) необходимо продлить линию тренда).

· Показывать ли точку пересечения кривой с прямой Y = const.

· Показывать аппроксимирующую функцию на диаграмме или нет (параметр показывать уравнение на диаграмме).

· Помещать ли на диаграмму величину среднеквадратичного отклонения или нет (параметр поместить на диаграмму величину достоверности аппроксимации).

На рис. 8.11 изображена полученная диаграмма.

Рис. 8.10

Рис. 8.11

Для расчета ожидаемых значений в точках 0.1539, 0.2569, 0.28 введем эти значения в ячейки B4:D4. В ячейку B5 введем формулу подобранной аппроксимирующей зависимости (=371.62*B4^2-68.093*B4+6.1891) и скопируем ее в ячейки C5, D5. Фрагмент рабочего листа примет вид:

Добавим полученные расчетные значения на диаграмму. Для этого на диаграмме выделим экспериментальные значения, щелкнем правой кнопкой мыши и выберем команду Исходные данные. Добавим туда Рассчитанные значения (см. рис. 8.12).

Рис. 8.12

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

Рис. 8.13

ПРИМЕР 8.3. В результате эксперимента получена зависимость z(t):

Подобрать коэффициенты зависимости Z(t)=At⁴+Bt³+Ct²+Dt+K методом наименьших квадратов.

Эта задача эквивалентна задаче нахождения минимума функции пяти переменных:

(8.13)

Введем табличную зависимость в рабочий лист MS Excel и построим график функции (см. рис.8.15)

Рассмотрим процесс решения задачи оптимизации (8.13). Пусть значения А, В, С, D и К хранятся в ячейках K1:K5. В ячейку B23 введем значение функции At⁴+Bt³+Ct²+Dt+K в первой точке (ячейка B1):

B23 = $K$1*B1^4 + $K$2*B1^3 + $K$3*B1^2 + $K$4*B1 + $K$5.

Получим ожидаемое значение (в начале 0) в точке B1. Затем растянем эту формулу на весь диапазон B23:J23. В ячейку B24 введем формулу, вычисляющую квадрат разности между экспериментальными и расчетными точками:

B24 = (B23-B2)^2,

и растянем ее на диапазон B24:J24. В ячейке В25 будем хранить суммарную квадратичную ошибку (см. формулу 8.13). Для этого введем формулу:

В25 = СУММ(B24:J24).

Теперь осталось с помощью решающего блока (Сервис Поиск решения) решить задачу оптимизации без ограничений, заполнив соответствующим образом появившееся диалоговое окно (рис. 8.14).

Рис. 8.14

Результатом работы решающего блока будет вывод в ячейки K1:K5 значений параметров функции At⁴+Bt³+Ct²+Dt+K. В ячейках B23:J23 получим ожидаемые значение функции в исходных точках. Поместим эти точки в виде отдельной линии на графике. В ячейке B25 будет храниться суммарная квадратичная ошибка. Рис. 8.15 отображает внешний вид рабочего листа MS Excel после проведенных вычислений.

По мнению авторов, использование решающего блока - это один из эффективных способов реализации метода наименьших квадратов с помощью MS Excel.

Рис. 8.15
u		0,5		1,5		2,5		3,5
V	3,597	4,597	5,5984	7,5987	11,269		26,5096	42,1599