Excel ではじめる数値解析 サンプルページ この本の定価 判型などは, 以下の URL からご覧いただけます. http://www.morikita.co.jp/books/mid/009631 このサンプルページの内容は, 初版 1 刷発行時のものです.
Excel URL http://www.morikita.co.jp/books/mid/009631
i Microsoft Windows Excel VBA Excel 2013 Excel Excel 2014 6
ii 1 1 1.1 1 1.2 VBA 6 14 2 17 2.1 17 2.2 18 2.3 20 2.4 26 27 3 29 3.1 29 3.2 35 44 4 46 4.1 46 4.2 56 61 5 63 5.1 63 5.2 66 5.3 68 5.4 Excel 69 70
iii 6 72 6.1 72 6.2 74 6.3 81 90 7 92 7.1 92 7.2 104 110 8 114 8.1 Excel 114 8.2 120 8.3 127 130 9 132 9.1 132 9.2 140 9.3 146 154 10 158 10.1 158 10.2 159 10.3 162 10.4 165 10.5 167 171 174 198
1 spreadsheet Microsoft Excel Excel 1 1.1 1.1.1 Excel = 1 2 A B C 2 C2 Enter 1.1
2 1 1.1.2 A1 B1 =A1+B1 1 =a1+b1 A1 B1 1.1 ( ) 1.1 1+1 =1+1 2 1 =2-1 2 3 =2*3 4 2 =4/2 2 3 =2^3 3 {1+2 (2 + 3)} =3*(1+2*(2+3)) SUM( ) AVERAGE( ) A1 A10 10 =SUM(A1:A10) : 1.2 E1 B1C1 E1 E2 F3 E1 1 A1 example number
1.1 3 1.3 Ctrl C Ctrl+C E2 Ctrl+V =B2+C2F3 =C3+D3 1.4 E1 B1B1 E1 E2 E2 B2 F3 F3 B3 Excel $ B1D3 1 9 A1 1.1 G1I3 B1D3 A1
4 1 G1 =B1/$A$1 A1 G1 H1 I1 I1 1.5 G2I3 G1I1 I3 G1I3 Ctrl D Ctrl+DCtrl+DD Down Ctrl+RR Right G1I3 = /$A$1A1 1.6 $A$1 A$1 $A1 F4 $A$1 F4 A1 $A$1 A$1 $A1 A1 1.2 J1L3 B1D3 B J1 =B1/$B1 K1L1 J1L1 J3L3 J2 =B2/$B2
1.1 5 J3 =B3/$B3 B K1 =C1/$B1L3 =D3/$B3 B 1.7 1.3 B4D6 B1D3 1 B4 =B1/B$1 B5B6 C4D6 B5 =B2/B$1D6 =D3/D$1 1 1.8 1.1.3 # =2/0 #DIV/0!0 1.2 #### # A1 B1 B1 A1 A1 =A1
6 1 1.2 #DIV/0! 0 0 #NAME? A1 A #VALUE! =A1+B1 A1 #N/A Not Available #### 1.2 VBA 1.2.1 1 1 10 100 1000 Excel Visual Basic for ApplicationsVBA VBA VBA BASIC Excel Word Microsoft 1.9
3 2 29 3.1 x x, x 2,x 3, 3.1.1 f(x) x = a 1.0002 20 1.0002 1 1.0002 20 1+20 0.0002 = 1.004 1.004007609... f (n) n f(x)=f(a)+ 1 1! f (a)(x a)+ 1 2! f (a)(x a) 2 + 1 3! f (a)(x a) 3 + + 1 n! f (n) (a)(x a) n + = f(a)+ i=1 1 i! f (i) (a)(x a) i (3.1) x = a x = a Brook Taylor1685 1731 1.0002 20 f(x) =x 20, a =1 2 f(1) = 1 20 =1, f (x) =20x 19
30 3 f (1) = 20 1 19 =20, x a =1.0002 1=0.0002 f(1.0002) f(1) + f (1) 0.0002 = 1 + 20 0.0002 = 1.004 (3.1) 2 2.3.2 (3.1) 2 x 1 3 x 2 n +1 x n 3.1.2 (3.1) f(x) =b 0 + b 1 (x a)+b 2 (x a) 2 + b 3 (x a) 3 + (3.2) Δx = x a x = a (3.2) f(a) =b 0 2 (x a) a a =0 b 0 = f(a) (3.2) x f (x) =b 1 +2b 2 (x a)+3b 3 (x a) 2 + (3.3) x = a (3.3) f (a) =b 1 2 b 1 = f (a) (3.3) 1 f (x) =2b 2 +3 2b 3 (x a)+4 3b 4 (x a) 2 + (3.4) x = a (3.4) f (a) =2b 2 2 b 2 = f (a) 2 b 3 = f (a) 3 2, b 4 = f (4) (a) 4 3 2,, b n = f (n) (a) n! 2 f(x) f(a)+ 1 1! f (a)(x a) (3.5) 3.1 (a, f(a)) f(x) f(x) A x a
3.1 31 3.1 f(x) 3.2 (a, f(a)) x a f (x) f (x) 3.1 f (a) (x, f(x)) f (x), f (a) 3.2 (x, f(x)) f (x) 3.1 f(x), f(a) f (x), f (a) f (x) =f (a)+f (a)(x a) (3.6) f(x) f(x) f(a)+ f (a)+f (x) (x a) 2 = f(a)+ f (a)+{f (a)+f (a)(x a)} (x a) 2 = f(a)+ f (a) 1! (x a)+ f (a) (x a) 2 (3.7) 2! 3 f (x) 3.1.3 x = a Excel
32 3 3.1 f(x) =e x x =0 f(x) =e x e x (3.1) f(x) =e a + 1 1! ea (x a)+ 1 2! ea (x a) 2 + 1 3! ea (x a) 3 + (3.8) x =0 (3.8) a =0e 0 =1 f(x) =1+x + 1 2! x2 + 1 3! x3 + (3.9) x =0Colin Maclaurin 1698 1746 (3.9) x =0 Excel Excel 1 A x B f(x) =e x CE A2, A3-1, -0.9 A2, A3 A3 A22 A -11 0.1 3.3 B e x Excel EXP( ) C x 1 1+x D x 2 1+x + x 2 /2! C x 2 /2! E x 3 1+x + x 2 /2! + x 3 /3! D x 3 /3! Excel FACT( ) B2E2 3.4
3.1 33 B2E22 B2 Shift E22 Ctrl+D 3.5 3.5 x =0 1 B AE AE 3.6 e x x =01 f(x) =1+x x 1 e x x = ±0.5 3 x = 0.51 e x x =0.5=ABS(C17-B17)/B17 1 =ABS(D17-B17)/B17 2 =ABS(E17-B17)/B17 3 ABS( )
34 3 3.6 1 OK1 9.0%2 1.4%3 0.2% 3 e 0.5 1+0.5+ (0.5)2 2 + (0.5)3 6 (3.10) x =0.11 e 0.1 1+0.1 =1.1 3.2 f(x) =sinx x =0 (3.1) f(x) =sinx, a =0 f(x) =sinx, f (x) =cosx, f (x) = sin x, f (x) = cos x, f (4) (x) =sinx, x =0sin 0 = 0, cos 0 = 1 x 2 x 4 x sin 0 = 0 (3.1) f(x) x 1 6 x3 + 1 120 x5 Excel B ππ x A -11 0.1 π B A 3.1 3.1 B2 A π π PI( ) C2 B sin 3.7
3.2 35 D2 x 1 E2 x 3 F2 x 5 x Excel 2 B2 B2F2 B22F22 BF 3.85 π/2π/2 3.8 3.2 3.2.1 x 1 3.9
92 7 simulation 7.1 7.1.1 0.1 sleonhard Euler 1707 1783 7.1 A 0, 0 100 m B Excel x y g t [s] m (x, y) m d2 x =0 dt2 (7.1) m d2 y dt = mg 2 (7.2) m d 2 x =0 dt2 (7.3) d 2 y dt = g 2 (7.4) x, y Δt =0.1s Δt [s]
7.1 93 Δt [s] ẋ dx/dt, ẍ d 2 x/dt 2 x(t + Δt) = x(t) +ẋ(t) Δt (7.5) y(t + Δt) = y(t) +ẏ(t) Δt (7.6) ẋ(t + Δt) = ẋ(t) +ẍ(t) Δt (7.7) ẏ(t + Δt) = ẏ(t) +ÿ(t) Δt (7.8) 40 km 2 80 km Δt [s] =+ = Δt Δt [s] =+ = Δt Δt [s] (7.5)(7.8) (7.3), (7.4) ẍ(t) =ẍ(t + Δt) = 0 (7.9) ÿ(t) = ÿ(t + Δt) = g (7.10) t + Δt (7.7)(7.9) 1 (7.10) (7.8) ẏ(t + Δt) =ẏ(t) g Δt (7.11) A 0, 0 7.1 v θ
94 7 7.1 x(0) = 0, y(0) = 0, ẋ(0) = v cos θ, ẏ(0) = v sin θ (7.12) AB L = 100 m B h =10m 7.2 D2E4 7.2 120 0 120 120 0 120 7.3 D5 Enter X D6D7 Enter Y E6E7 Enter D8 Enter X D9 Enter Y E9 Enter OK
7.1 95 7.3 7.4 7.5 7.4 7.5 7.6 OK 7.7 game VBA VBA 1y<=0 B x>l+10 0.01 s 2 B <=h2 >=h1 1 ForNext Do WhileLoop
96 7 7.6 7.7 B Boolean Boolean TrueFalse 2 Dim A BAnd If ( A And B) Then yl yl h1 yl h2 >=<= yl xbybxy B x>=l B 7.8 3.2.1 7.8 B
7.1 Dim hit As Boolean Dim arrival As Boolean hit = False: arrival = False v = [B1] theta = 3.14 * [B2] / 180 L = [B3] h1 = [B4] h2 = [B5] g = 9.8 vx = v * Cos(theta) vy = v * Sin(theta) x = 0 : y = 0 xb = 0 : yb = 0 Calculate dt = 0.01 Do While (y >= 0) x = x + vx * dt y = y + vy * dt vy = vy - g * dt [D9] = x [E9] = y Calculate If (arrival = False And x >= L) Then arrival = True yl = yb + (y - yb) * (L - xb) / (x - xb) If ( yl <= h2 And yl >= h1 ) Then hit = True Exit Do End If End If If ((x > L + 10) Or (x < 0)) Then Exit Do End If xb = x: yb = y Loop If hit Then MsgBox("") Else MsgBox("") End If 7.1 97 B False v B1 theta B2 π 180 L B h1 h2 g 9.8 m/s 2 (7.12) x (7.12) y (7.12) (x, y) dt Δt [s] y>=0 (7.5) x (7.6) y (7.11) vy D9E9 B B yl hit True Do B L+10 Do hit True
98 7 (7.5)(7.6) t + Δt [s] t [s] = 1 x=0 : y=0 1 Excel game B1 B2 (7.5)(7.10) 2 B1 B2 Enter 7.1.2 7.9 7.9 m [kg] k [N/m] t x [m]1 = ẋ = dx/dt [m/s]1 = ẍ = d 2 x/dt 2 [m/s 2 ] kx 2 mẍ = kx (7.13) mẍ + kx = 0 (7.13) x = A cos ωt + B sin ωt (7.14) ω = k/ma, B (7.14)
1984 1984 1993 1993 2001 2004 1986 1986 1998 1998 2002 2006 COE 2007 2012 2013 Excel C 2014 2014 8 29 1 1 1 4 11102 0071 03 3265 8341 FAX 03 3264 8709 http://www.morikita.co.jp/ Printed in JapanISBN978 4 627 09631 8