1 2 3 4 5 2
彩圖 6 彩圖 7 彩圖 8 3
彩圖 13 彩圖 14 彩圖 16 彩圖 15 5
彩圖 22 彩圖 20 彩圖 21 彩圖 23 7
24 25 26 8
31 32 33 34 10
彩圖 35 彩圖 38 彩圖 36 彩圖 39 彩圖 37 彩圖 40 11
03 1 2 3 4 10 8 6 4 2 0 3 2.1 1.2 0.3 0.6 1.5 2.4 3 1.2 0.6 2.4
Excel 1 9 1 Excel B2 C2 1 2 2007 3-2
03 A3 A4 1 2, 1 9 2 1 9 B2 J2 A3 A3 2 1 9 B2 1 C2 =B2+1 C2 D2 J2 D2 E2 J2 =C2+1 =D2+1 I2+1 1 A 1 9 A3 A4 1 =A3+1 A4 A5 A11 A 3-3
2 Examples\Example 03-01.XLS 100 a 0 d B1 B2 B5 B6 B7 A7 C5 D6 A 3-4
03 Knuth, 1984; Lamport, 1986 1. Times New Roman 2. A B 3. 0 1 2 4. sin cos log 5. + - = 6. A [A] 7. m/s kg/m 3 Symbol Times New Roman A B C D E F G H I J K L M Symbol A B C D E F G H I J K L M Times New Roman N O P Q R S T U V W X Y Z Symbol N O P Q R S T U V W X Y Z Times New Roman a b c d e f g h i j k l m Symbol a b c d e f g h i j k l m Times New Roman n o p q r s t u v w x y z Symbol n o p q r s t u v w x y z Word Excel Excel Symbol x 1/3 1/(1 + x 2 ) 3-5
Microsoft Office Excel Setup 3-6
03 Office Excel Excel Word Excel Word 10 n a n S n 12 13 A12 0 0 B12 =B1 B1 C12 =B12 3-7
A13 =A12+1 1 B13 =A12 + $B$2 C13 =C12+B13 A13 C13 A 100 A 0 100 B12 = [ ][ ] =[ ]![ ][ ] C S n = S n-1 + a n n = 3 a 0 a 3 S 3 a 0 a 2 a 3 3-8
03 A12 0 0 B12 =B1+ A12*$B$2 a n = a 0 + nd 0 1 a n = a 1 + (n 1)d C12 =SUM($B$12:B12) $B$12 B12 A12 C12 A 100 1. B12 A B A 2. C Excel Excel C12 = (A12 + 1) * $B$1 + A12 * (A12 + 1) * $B$2 / 2 S n = (n + 1)a 0 + n(n + 1) d/2 0 1 S n = n a 0 + n(n - 1) d/2 3-9
Excel B12 3-10
03 VLOOKUP S(n1, n2) n1 n2 (n2 > n1) S(2, 4) = a 2 + a 3 + a 4 = S 4 S 1 C n2 = 30 S30 n 1 = 2 S n1-1 = S 1 Example 03-01.XLS D5 D6 D5 D6 =VLOOKUP(B5-1,A12:C112,3) =VLOOKUP(B6,A12:C112,3) 3-11
VLOOKUP VLOOKUP(,, ) D5 D6 VLOOKUP A12:C112 A12 C112 n = 0 100 n1-1 n2 VLOOKUP 3 VLOOKUP 2 VLOOKUP VLOOKUP VLOOKUP FALSE 0 VLOOKUP #N/A 3-12
03 B6 29.6 30.1 D6 VLOOKUP FALSE 0 TRUE 0 1 VLOOKUP 1. 2. TRUE 0 3. FALSE 0 #N/A VLOOKUP HLOOKUP HLOOKUP(,, ) VLOOKUP IF B5 n1 0 #N/A D5 S n1-1 S -1-1 0 IF B7 =IF(B5=0,D6,D6-D5) B5 0 D6 S n2 D6- D5 S n2 S n1 3-13
IF IF(, 1, 2) 1 2 IF n1 0 B5=0 D6 S n2 S n2 S n1-1 D6-D5 = <= =< > < <> >< >= => B7 IF =B5=0 B5 n1 TRUE FALSE AND( 1, 2) OR( 1, 2) NOT( ) 1 2 TRUE FALSE 1 2 TRUE FALSE FALSE TRUE 3-14
03 Exercises\Exercise 03-01.XLS a 0 r 1 S n a n = a n-1 + d (3-1) a n = ra n-1 (3-2) d r recurrence n a n = a 0 + nd (3-3) a n = a 0 r n (3-4) Excel a 0 3-15
Fibonacci Examples\Example 03-02.XLS Fibonacci a 0 a 1 1 a n = a n-1 + a n-2 1 1 2 3 5 8 13 21 1.618033989 (3-5) Excel Fibonacci B2 1 a 0 B3 1 a 1 A8 0 B8 =B2 a 0 3-16
03 A9 =A8+1 1 B9 =B3 a 1 C9 =B9/B8 r 1 = a 1/a 0 A10 =A9+1 1 B10 =B9+B8 a 2 = a 0 + a 1 C10 =B10/B9 r 2 = a 2/a 1 r 0 = a 0/a -1 C8 10 A 30 10 B10 8 10 A9 A10 C9 C10 B8 n 20 r n 1.618033989 =(1+SQRT(5))/2 B2 B3 a 0 a 1 0 r n 1.618033989 n = 30 Excel 1.618033989 2004 3-17
Exercises\Exercise 03-02.XLS a k = na k-1 + ma k-2 n k a 0 a 1 m n a k r k = a k/a k-1 n 2 + 4m > 0 Fibonacci m = n = 1 a 0 a 1 m n a 1 a1 Examples\Example 03-03.XLS (3-6) k 3-18
03 a k a k-1 (3-7) Excel k = 20 B8 =B2 B9 =1+1/B8 B2 10 1.61803 B B2 0 3-19
Exercises\Exercise 03-03.XLS Example 03-03.XLS m n m = n = 1 m n m n m n k n Exercises\Exercise 03-04.XLS Exercise 03-03.XLS m n m n k n 3-20