Метод наименьших квадратов. Основные заголовки таблицы. Коэффициенты линейной аппроксимации. Квадраты отклонений значений функций

ПРИМЕР 3

МЕТОД НАИМЕНЬШИХ КВАДРАТОВ

Постановка задачи

По имеющемуся набору экспериментальных данных о зависимости F(x) построить методом наименьших квадратов аппроксимирующие зависимости G(x) и H(x) в виде G(x)=A+Bx,    H(x)=Ce^Dx и сравнить их по критерию «средний квадрат отклонения».

Пример выполнения работы 

Основные заголовки таблицы:

A1¬’Аппроксимация зависимостей методом наименьших квадратов

A2¬’Исходные данные    Объединить клетки A2,B2

D2¬’Линейная зависимость     Объединить клетки D2:F2

H2¬’Экспоненциальная зависимость   Объединить клетки H2:J2

L2¬’Квадраты отклонений   Объединить клетки L2,M2

A3¬’X  B3¬’F(x)  D3¬’X*F  E3¬’X*X  F3¬’G(x)  H3¬’lnF  I3¬’X*lnF  J3¬’H(x)  L3¬’G  M3¬’H

В клетки A4:A23 занесем исходные данные по x , в B4:B23 - исходные данные по F(x)

Найдем средние значения X и F(x):

A24¬’Xср   A25¬=срзнач(A4:A23)  B24¬’Fcp  B25¬=срзнач(B4:B23)

Вычислим данные, необходимые для линейной аппроксимации:

D4¬=A4*B4  E4¬A4*A4  Скопируем D4:E4 в D5:E23

Найдем их средние значения :

D24¬’(X*F)ср   D25¬=срзнач(D4:D23)  E24¬’(X*X)cp  E25¬=срзнач(E4:E23)

Вычислим коэфициенты линейной аппроксимации:

D26¬’A  E26¬’B  E27¬=(A25*B25-D25)/(A25*A25-E25)  D27¬=B25-E27*A25

Вычислим при заданных значениях Х значения аппроксимирующей функции G(x)=A+Bx :

F4¬=$D$27+$E$27*A4  Скопируем F4 в F5:F23

Вычислим данные, необходимые для экспоненциальной аппроксимации:

H4¬=LN(B4)  I4¬A4*H4  Скопируем H4:I4 в H5:I23

Найдем их средние значения :

H24¬’(lnF)ср   H25¬=срзнач(H4:H23)  I24¬’(X*lnF)cp  I25¬=срзнач(I4:I23)

Вычислим коэфициенты экспоненциальной аппроксимации:

H26¬’C  I26¬’D  I27¬=(A25*H25-I25)/(A25*A25-E25)  H27¬=EXP(H25-I27*A25)

Вычислим при заданных значениях Х значения аппроксимирующей функции H(x)=Ce^Dx :

J4¬=$H$27*EXP($I$27*A4)  Скопируем J4 в J5:J23

Найдем квадраты отклонений значений функций G(x) и H(x) от значений функции F(x) :

L4¬=(F4-B4)^2  M4¬=(H4-B4)^2  Скопируем L4:M4 в L5:M23

Найдем средние квадраты отклонений:

L26¬’Средние     Объединить клетки L26,M26

L27¬=СРЗНАЧ(L4:L23)   M27¬=СРЗНАЧ(M4:M23)

Построим графики аппроксимируемой функции F(x) и аппроксимирующей функции G(x) :

Выделить диапазоны A4:B23,F4:F23 . Мастер диаграмм – Точечная - …

Построим графики аппроксимируемой функции F(x) и аппроксимирующей функции H(x) :

Выделить диапазоны A4:B23,J4:J23 . Мастер диаграмм – Точечная - …

В качестве результата получили:

1)  Линейная аппроксимирующая функция имеет вид: G(x)=1,2343+1,3529x

2)  Экспоненциальная аппроксимирующая функция имеет вид: H(x)=2,2005e^0,2731x

3)  Исходные экспериментальные данные лучше аппроксимируются экспонентой H(x).

ПРИМЕР EXCEL-ТАБЛИЦЫ РЕШЕНИЯ ЗАДАЧИ