Word / Апроксимация v1
.0.doc|
|
|
|
Апроксимация |
|
|
|
|
|
|
|
|
|
|
|
i |
Xi |
Yi |
|
Ф= |
1 |
1 |
|
Фt= |
1 |
2 |
3 |
4 |
5 |
|
1 |
1 |
1 |
|
|
2 |
4 |
|
|
1 |
4 |
9 |
16 |
25 |
|
2 |
2 |
5,5 |
|
|
3 |
9 |
|
|
|
|
|
|
|
|
3 |
3 |
10,2 |
|
|
4 |
16 |
|
|
|
|
|
|
|
|
4 |
4 |
13,5 |
|
|
5 |
25 |
|
|
|
|
|
|
|
|
5 |
5 |
28 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
N=Фt*Ф= |
55 |
225 |
|
b=Ф*y= |
236,6 |
|
|
|
|
|
|
|
|
|
|
225 |
979 |
|
|
1030,8 |
|
|
|
|
|
Yexsp1= |
0,981493 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Yexsp2= |
4,111432 |
|
|
матрица |
|
|
|
|
|
|
|
|
|
|
Yexsp3= |
9,389816 |
|
шаг 1 |
55 |
225 |
236,6 |
|
|
|
|
|
|
|
|
Yexsp4= |
16,81665 |
|
|
225 |
979 |
1030,8 |
|
|
|
|
|
|
|
|
Yexsp5= |
26,39192 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
шаг 2 |
1 |
4,090909 |
4,301818 |
|
|
|
|
|
|
|
|
R= |
16,17091 |
|
|
0,229826 |
1 |
1,052911 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
шаг 3 |
1 |
0 |
-0,09273 |
|
|
|
|
|
|
|
|
|
|
|
|
0 |
1 |
1,074223 |
|
|
|
|
|
|
|
Sub aproksimacija()
Dim matr!(5, 2), F!(5, 2), Ft!(2, 5), b!(2, 1), FFt(2, 2), y!(5, 1), m2!(2, 3), m3!(2, 3)
Dim i%, j%
For i = 1 To 5
For j = 1 To 2
matr(i, j) = Cells(2 + i, 1 + j)
F(i, 1) = matr(i, 1): Cells(1 + i, 6) = F(i, 1)
F(i, 2) = F(i, 1) * F(i, 1): Cells(1 + i, 7) = F(i, 2)
Ft(j, i) = F(i, j): Cells(1 + j, 9 + i) = Ft(j, i)
FFt(j, 1) = FFt(j, 1) + Ft(j, i) * F(i, 1): Cells(7 + j, 6) = FFt(j, 1): Cells(11 + j, 5) = FFt(j, 1)
FFt(j, 2) = FFt(j, 2) + Ft(j, i) * F(i, 2): Cells(7 + j, 7) = FFt(j, 2): Cells(11 + j, 6) = FFt(j, 2)
y(i, 1) = Cells(2 + i, 3)
b(j, 1) = b(j, 1) + Ft(j, i) * y(i, 1): Cells(7 + j, 10) = b(j, 1): Cells(11 + j, 7) = b(j, 1)
Next j
Next i
Call vi4isl_matrici(m2, m3)
Call Yexsp_and_R
End Sub
Sub vi4isl_matrici(m2!(), m3!())
For i = 1 To 3
For j = 1 To 2
m3(j, i) = Cells(11 + j, 4 + i)
m2(j, i) = Cells(11 + j, 4 + i)
Next j
m2(1, i) = m2(1, i) / m3(1, 1): Cells(15, 4 + i) = m2(1, i)
Next i
For i = 1 To 3
For j = 1 To 2
m3(j, i) = Cells(11 + j, 4 + i)
m2(j, i) = Cells(11 + j, 4 + i)
Next j
m2(2, i) = m2(2, i) / m3(2, 2): Cells(16, 4 + i) = m2(2, i)
Next i
For i = 1 To 3
For j = 1 To 2
m3(j, i) = Cells(14 + j, 4 + i)
m2(j, i) = Cells(14 + j, 4 + i)
m3(2, 1) = Cells(16, 5)
Next j
m3(2, i) = m2(2, i) + m2(1, i) * (-m3(2, 1))
m2(2, i) = m3(2, i) * (1 / m3(2, 2)): Cells(19, 4 + i) = m2(2, i)
m2(1, i) = m3(1, i) + m2(2, i) * (-m3(1, 2)): Cells(18, 4 + i) = m2(1, i)
Next i
End Sub
Sub Yexsp_and_R()
Dim x!(5, 1), yexp!(5, 1), yteor!(5, 1), a!, b!, R!
a = Cells(18, 7): b = Cells(19, 7)
For i = 1 To 5
x(i, 1) = Cells(2 + i, 2): yteor(i, 1) = Cells(2 + i, 3)
yexp(i, 1) = a * x(i, 1) + b * x(i, 1) ^ 2
Cells(9 + i, 2) = yexp(i, 1)
R = R + (yteor(i, 1) - yexp(i, 1)) ^ 2: Cells(16, 2) = R
Next i
End Sub