Microsoft Excel 2003 n Visual Basic VBA n

ΕΡΓΑΛΕΙΑ ΥΛΟΠΟΙΗΣΗΣ Microsoft Excel 2003 n Visual Basic (VBA) n

Εισαγωγή του SOLVER. xla n Στο Excel Alt+F 11 Από το μενού Tools SOLVER. xla

Μέθοδοι αριθμητικής παραγώγισης (1/4) n Προς τα μπρος διαφορές Range("K 2"). Formula = "=D$11" Range(Cells(3, 11), Cells(Num. Points + 2, 11)). Formula = "=K 2+(E$11 -D$11)/F$11" Range(Cells(2, 12), Cells(Num. Points + 2, 12)). Formula = "=" & Range("E 5"). Text Range(Cells(2, 13), Cells(Num. Points, 13)). Formula = "=(L 3 -L 2)/($E$3)" Range(Cells(Num. Points + 1, 13), Cells(Num. Points + 2, 13)). Formula = "=(RC[-1]-R[-1]C[-1])/((RC[-2]-R[-1]C[-2]))" Range(Cells(3, 15), Cells(Num. Points + 2, 15)) = "=((RC[-1]-RC[1]))^2" Range(Cells(3, 16), Cells(Num. Points + 2, 16)) = "=$H$3*K 3^$H$4+$H$5*K 3^$H$6+$H$7*K 3^$H$8+$H$9*K 3^$H$10+$H$11*K 3^$H$12+$H$13" f(x 0+h) f(x 0) x 0+h

Μέθοδοι αριθμητικής παραγώγισης (2/4) n Προς τα πίσω διαφορές Range("K 2"). Formula = "=D$11" Range(Cells(3, 11), Cells(Num. Points + 2, 11)). Formula = "=K 2+(E$11 -D$11)/F$11" Range(Cells(2, 12), Cells(Num. Points + 2, 12)). Formula = "=" & Range("E 5"). Text Range(Cells(2, 13), Cells(3, 13)). Formula = "=(L 3 -L 2)/($E$3)" Range(Cells(4, 13), Cells(Num. Points + 2, 13)). Formula = "=(L 4 -L 3)/($E$3)" Range(Cells(3, 15), Cells(Num. Points + 2, 15)) = "=((RC[-1]-RC[1]))^2" Range(Cells(3, 16), Cells(Num. Points + 2, 16)) = "=$H$3*K 3^$H$4+$H$5*K 3^$H$6+$H$7*K 3^$H$8+$H$9*K 3^$H$10+$H$11*K 3^$H$12+$H$13" f(x 0) f(x 0 -h) X 0 -h x 0

Μέθοδοι αριθμητικής παραγώγισης (3/4) n Μέθοδος τριών σημείων Range("K 2"). Formula = "=D$11" Range(Cells(3, 11), Cells(Num. Points + 2, 11)). Formula = "=K 2+(E$11 -D$11)/F$11" Range(Cells(2, 12), Cells(Num. Points + 2, 12)). Formula = "=" & Range("E 5"). Text Range(Cells(2, 13), Cells(3, 13)). Formula = "=(-3*L 2+4*L 3 -L 4)/(2*$E$3)" Range(Cells(4, 13), Cells(Num. Points, 13)). Formula = "=(L 5 -L 3)/(2*$E$3)" Range(Cells(Num. Points + 1, 13), Cells(Num. Points + 2, 13)). Formula = "=(3*RC[-1]-4*R[-1]C[-1]+R[-2]C[-1])/(2*(RC[-2]-R[-1]C[-2]))" Range(Cells(3, 15), Cells(Num. Points + 2, 15)) = "=((RC[-1]-RC[1]))^2" Range(Cells(3, 16), Cells(Num. Points + 2, 16)) = "=$H$3*K 3^$H$4+$H$5*K 3^$H$6+$H$7*K 3^$H$8+$H$9*K 3^$H$10+$H$11*K 3^$H$12+$H$13" Όπου ξ 0=ξ(x 0) f(x 0+2 h) f(x 0+h) f(x 0) x 0+h x 0+2 h

Μέθοδοι αριθμητικής παραγώγισης (4/4) n Μέθοδος πέντε σημείων Range("K 2"). Formula = "=D$11" Range(Cells(3, 11), Cells(Num. Points + 2, 11)). Formula = "=K 2+(E$11 -D$11)/F$11" Range(Cells(2, 12), Cells(Num. Points + 2, 12)). Formula = "=" & Range("E 5"). Text Range(Cells(2, 13), Cells(3, 13)). Formula = "=(-25*L 2+48*L 3 -36*L 4+16*L 5 -3*L 6)/(12*$E$3)" Range(Cells(4, 13), Cells(Num. Points, 13)). Formula = "=(L 2 -8*L 3+8*L 5 -L 6)/(12*$E$3)" Range(Cells(Num. Points + 1, 13), Cells(Num. Points + 2, 13)). Formula = "=(25*RC[-1]-48*R[-1]C[-1]+36*R[-2]C[-1]-16*R[-3]C[-1]+3*R[-4]C[-1])/(12*(RC[2]-R[-1]C[-2]))" Range(Cells(3, 15), Cells(Num. Points + 2, 15)) = "=((RC[-1]-RC[1]))^2" Range(Cells(3, 16), Cells(Num. Points + 2, 16)) = "=$H$3*K 3^$H$4+$H$5*K 3^$H$6+$H$7*K 3^$H$8+$H$9*K 3^$H$10+$H$11*K 3^$H$12+$H$13" X 0 -2 h X 0 -h x 0+h x 0+2 h

Μέθοδοι αριθμητικής ολοκλήρωσης (1/2) n Τραπεζίου Range("K 2"). Formula = "=D$11" Range(Cells(3, 11), Cells(Num. Points + 2, 11)). Formula = "=K 2+(E$11 -D$11)/F$11" Range(Cells(2, 12), Cells(Num. Points + 2, 12)). Formula = "=" & Range("E 5"). Text Range(Cells(3, 13), Cells(Num. Points + 2, 13)) = "=R[-1]C+(RC[-2]-R[-1]C[-2])*(R[-1]C[-1]+RC[-1])/2" Range(Cells(3, 15), Cells(Num. Points + 2, 15)) = "=((RC[-1]-RC[1]))^2" Range(Cells(3, 16), Cells(Num. Points + 2, 16)) = "=$H$3*K 3^$H$4+$H$5*K 3^$H$6+$H$7*K 3^$H$8+$H$9*K 3^$H$10+$H$11*K 3^$H$12+$H$13" f(x 1) f(x 0) x 0 x 1

Μέθοδοι αριθμητικής ολοκλήρωσης (2/2) n Simpson Range("K 2"). Formula = "=D$11" Range(Cells(3, 15), Cells(Num. Points + 2, 15)). Formula = "=(R[-1]C[-4]+RC[-4])/2" Range(Cells(3, 13), Cells(Num. Points + 1, 13)). Formula = "=M 2+($E$3)*(L 2+4*O 3+L 4)/3" Range(Cells(Num. Points + 2, 13), Cells(Num. Points + 2, 13)). Formula = "=R[-1]C+(RC[-2]-R[-1]C[-2])*(RC[-1]+R[-1]C[-1])/3" Range(Cells(3, 16), Cells(Num. Points + 2, 16)) = "=((RC[-2]-RC[1]))^2" Range(Cells(3, 17), Cells(Num. Points + 2, 17)) = "=$H$3*K 3^$H$4+$H$5*K 3^$H$6+$H$7*K 3^$H$8+$H$9*K 3^$H$10+$H$11*K 3^$H$12+$H$13" f(x 2) f(x 1) f(x 0) x 0 x 1 x 2

Μη γραμμικές μέθοδοι (1/3) n Μέθοδος διχοτόμησης a = Range("b 4") b = Range("c 4") Range("d 4"). Value = f 00(a) Range("e 4"). Value = f 00(b) c = 0. 5 * (a + b) Cells(3 + k, 6). Value = c Cells(3 + k, 7). Value = f 00(c) Cells(3 + k, 8). Value = f 00(b) * f 00(c) If f 00(b) * f 00(c) > 0 Then b=c Cells(4 + k, 3). Font. Bold = True Else a=c Cells(4 + k, 2). Font. Bold = True End If Cells(4 + k, 2) = a Cells(4 + k, 4) = f 00(a) Cells(4 + k, 3) = b Cells(4 + k, 5) = f 00(b) For i = 1 To 100 For j = 2 To 8 Cells(3 + i, j). Value = "" Cells(3 + i, j). Font. Bold = False Next j Next i Range("b 4"). Value = 0 Range("c 4"). Value = 1 epsilon = Range("L 16") Nmax = Range("L 17") a = Range("b 4") b = Range("c 4") Range("d 4"). Value = f 00(a) Range("e 4"). Value = f 00(b) If f 00(a) * f 00(b) > 0 Then Msg. Box ("For a = " & Str(a) & " and b = " & Str(b) & ", f(a)*f(b)>0. " _ & " Try new values of a and b. ") Exit Sub End If c = 0. 5 * (a + b) k=0 Do While Abs(f 00(c)) > epsilon And k < Nmax Call cmd. Step_Click Loop

Μη γραμμικές μέθοδοι (2/3) n Μέθοδος Newton-Raphson x = Range("b 5") Msg. Box ("Use button [Next Step] to iterate solution") k=0 Dim Dx As Single k=k+1 Cells(4 + k, 3). Value = f 00(x) Cells(4 + k, 4). Value = f. P 00(x) Dx = f 00(x) / f. P 00(x) x = x - Dx Cells(4 + k, 5). Value = Dx Cells(5 + k, 2). Value = x

Μη γραμμικές μέθοδοι (3/3) n Μέθοδος τέμνουσας x 0 = Range("b 4") x 1 = Range("c 4") Range("d 4"). Value = f 00(x 0) Range("e 4"). Value = f 00(x 1) If x 0 = x 1 Then Else Range("d 4"). Value = f 00(x 0) Range("e 4"). Value = f 00(x 1) x 2 = x 1 - f 00(x 1) * (x 1 - x 0) / (f 00(x 1) - f 00(x 0)) Range("h 4"). Value = f 00(x 2) End If k=0 ΤΟΜΗ (x 0, x 1) F(x 0)*f(x 1)<0 Ευθεία (x 0, f(x 0)) kai (x 1, f(x 1)) (Χ 0, Χ 1) -> (Χ 1, Χ 1’)->(Χ 1’, Χ 1’’)… Dim Dx As Single k=k+1 Cells(3 + k, 4). Value = f 00(x 0) Cells(3 + k, 5). Value = f 00(x 1) If Abs(f 00(x 1) - f 00(x 0)) < 0. 0000001 Then Msg. Box ("Values of x are not changing any more. ") Exit Sub End If Dx = f 00(x 1) * (x 1 - x 0) / (f 00(x 1) - f 00(x 0)) Cells(3 + k, 6). Value = Dx x 2 = x 1 - Dx Cells(3 + k, 7). Value = x 2 Cells(3 + k, 8). Value = f 00(x 2) x 0 = x 1 = x 2 Cells(4 + k, 2). Value = x 0 Cells(4 + k, 3). Value = x 1

FILL If Range("E 5"). Value <> "" Then the. Text$ = Range("E 5"). Text the. Text$ = fix. String(the. Text$) the. Text$ = Replace. Substring(the. Text$, "x", "RC[-1]") Range(Cells(2, 12), Cells(Num. Points + 2, 12)). Formula = "=" & the. Text$ Else Range(Cells(2, 12), Cells(Num. Points + 2, 12)). Formula = "" End If CLEAR Dim i As Integer, j As Integer Num. Points = Range("F 11") For i = 1 To 1000 For j = 11 To 16 Cells(2 + i, j). Value = "" Next j Next i Range("K 2") = "" Range("L 2") = "" Range("M 2") = "=H 38" Range("N 2") = "" Range("O 2") = "" Range("P 2") = "" SOLVER Solver. Reset Solver. Ok Set. Cell: ="$H$15", Max. Min. Val: =2, Value. Of: ="0", By. Change: ="$H$3: $H$13" solversolve userfinish: =False RESET Range("H 3"). Select Active. Cell. Formula. R 1 C 1 = "1" Range("H 3"). Selection. Auto. Fill Destination: =Range("H 3: H 13"), Type: =xl. Fill. Default Range("H 3: H 13"). Select Range("H 14"). Select

Κουμπιά εντολής Όνομα-τύπος FILL CLEAR SOLVER RESET Range("E 5"). Text Range("AA 1"). Value Range("K 2"). Formula Cells(2 + i, j). Value = "" Solver. Reset Range("H 3"). Select Τιμή Τύπος