Апроксимація методом найменших квадратів

Часто відомо, що між величинами і існує лінійна залежність або , і треба знайти коефіцієнти і та стандартні похибки і отриманих значень коефіцієнтів. Так, наприклад, коефіцієнт місцевого опору ζ визначаємо експериментально, скориставшись формулою Вейсбаха . Іноді функціональну залежність можна лінеаризувати, тобто представити у вигляді лінійної функції. Так, логарифмуючи формулу витрати через трикутний водозлив

(36)

отримуємо

(37)

Розглянемо спочатку випадок, коли відомо, що функціональна залежність має вигляд . Маємо пар значень . Тоді

;

(38)

;

(39)

(40)

Розглянемо тепер випадок, коли відомо, що функціональна залежність має вигляд . Маємо пар значень . Тоді

;

(41)

;

(42)

;

(43)

;

(44)

;

(45)

Розглянемо на прикладі, як можна здійснити апроксимацію за допомогою MS Excel. Експериментальні точки залежності витрати Q через трикутний водозлив від напору H наведені в табл. 2.4.

Таблиця 2.4

Експериментальні дані залежності витрати Q від напору H

H, m	0,05	0,10	0,15	0,20	0,25
Q, m³/s	0,78·10^-3	4,5·10^-3	12·10^-3	26·10^-3	44·10^-3

Відомо, що залежності витрати Q від напору H описується формулою (35), яка після лінеаризації має вигляд (36) або . Треба знайти константи α (показник степеня) і M.

У вікні MS Excel уводимо експериментальні значення з табл. 2.4 у блок комірок B1:F2, як це показано на рис. 2.7. У комірку В3 вводимо формулу = LN(B1), у комірку В4 - формулу =LN(B2). Виділяємо діапазон комірок В3:В4 і протягуванням заповнюємо діапазон комірок С3:F4.

Виділяємо діапазон комірок A3:F4 і виконуємо команду Вставка | Диаграмма…. У вікні Мастер диаграмм установлюємо параметри, як показано на рис. 2.8.

Клацнувши кілька разів кнопку Далее, а потім Готово, отримуємо графік, який після деяких додаткових дій набуває вигляду показаного на рис. 2.7. Найголовніші з цих дій такі. Клацаємо правою клавішею миші по одній із точок на графіку і в контекстному меню вибираємо команду Добавить линию тренда… . У вікні Линия тренда виконуємо команду Тип | Линейная | OK. Клацаємо правою клавішею миші по лінії тренда на графіку і в контекстному меню вибираємо команду Формат линии тренда. У вікні Формат линии тренда виконуємо команду Параметры | показывать уравнение на диаграмме | OK. На діаграмі з’являється рівняння з числовими значеннями коефіцієнтів.

У комірку І3 заносимо значення , у комірку І4 - значення . Уводимо формули в комірки: І6 - =I3, I7 =EXP(I4), B5 - =B4-$I3*B3-$I4 {(43)} і протягуємо її на діапазон B5:F5, K1 - =СУММКВ(B5:F5), K2 - =СРЗНАЧ(B3:F3) {(40)}, B6 - =B3-$K2 і протягуємо її на діапазон B6:F6, K5 - =СУММКВ(B6:F6) {(41)}, K3 - =КОРЕНЬ(K1/(5-2)/K5) {(44)}, K4 - КОРЕНЬ((1/5+K2*K2/K5)*K1/(5-2)) {(44)}, K6 - K3*СТЬЮДРАСПРОБР(1-0,9;5-1)/КОРЕНЬ(5) {(8), (9)}, K7 - =I7*K4* СТЬЮДРАСПРОБР(1-0,9;5-1)/КОРЕНЬ(5) {(8), (9); оскільки , то }, M6 - =K6/I6, N7 - =K7/I7 {(15)}. Формули MS Excel показані на рис. 2.9.

У комірках I6 та І7 з’являються значення і , у комірках K6 та K7 - значення і , у комірках M6 та M7 - значення і .

Рис. 2.7. Лінеаризація з використанням MS Excel.

Рис. 2.8. Побудова графіка з використанням MS Excel.

Рис. 2.9. Формули в MS Excel.