例 題 で 学 ぶ Excel 統 計 入 門 第 2 版 サンプルページ この 本 の 定 価 判 型 などは, 以 下 の URL からご 覧 いただけます. http://www.morikita.co.jp/books/mid/084302 このサンプルページの 内 容 は, 第 2 版 発 行 当 時 のものです.
i 2 9 2 Web 2 Excel Excel Excel 11 Excel 2 2012 1
ii Excel Excel ( ) Excel Excel 1 2 Excel Excel Excel Excel Excel Excel, 2001 3
iii 2 Web i ii vi 1 2 1.1... 2 1.2... 4 1.3... 6 1.4... 8 1... 10 2... 11 2 12 2.1... 12 2.2... 14 2.3... 16 2.4... 18 3 (1)... 20 4 (2)... 21 3 22 3.1... 22 3.2... 24 3.3... 26 3.4... 28 5... 31 6... 32 7... 33 4 34 4.1... 34
iv 4.2... 36 4.3... 38 4.4... 40 4.5... 42 8... 43 5 44 5.1... 44 5.2... 46 5.3... 48 5.4... 50 5.5... 52 9... 54 10 (1)... 55 11 (2)... 56 12 CSV... 57 13... 58 6 2 60 6.1... 60 6.2... 62 6.3 2... 64 6.4 2... 66 6.5 2... 68 6.6... 70 6.7... 72 6.8... 74 14 n... 76 15... 77 16... 78 7 80 7.1 2... 80 7.2... 82 7.3... 84
v 7.4... 86 7.5... 88 7.6 χ 2 t F... 90 7.7... 94 17... 96 8 97 8.1... 97 8.2... 100 8.3... 102 8.4... 104 8.5... 107 8.6... 109 8.7... 111 8.8... 114 8.9... 117 8.10... 120 8.11... 122 18... 124 19... 125 A 126 A.1... 126 A.2... 127 A.3... 127 A.4 2... 128 A.5... 129 A.6 2... 130 A.7... 130 A.8... 131 A.9 n χ 2... 131 A.10 n t... 132 A.11 (m, n) F... 132 B 133 C Excel 134 143
vi Web 2 Web http://www.morikita.co.jp/soft/84302/ Web
1 Excel Excel Excel (autofill, ) $ $ Excel Excel C average(x) =average( x ) Enter Excel Excel 3 chap1.xlsx 1 1 x y Web F1
2 1 1.1 1.1 10 1, 4, 1, 3, 2, 9, 1, 1, 7, 1 =average(a3:a12) =median(a3:a12) =mode(a3:a12) =average( ) Enter n x 1,x 2,,x n (mean, average, ) x x = x 1 + x 2 + + x n = 1 n x i n n i=1 average A3 A12 B18 =average(a3:a12) Enter =
1.1 3 Excel : (median) 1, 1, 1, 1, 1, 2, 3, 4, 7, 9 (1 + 2)/2 =1.5 median (mode) 1 1 1 mode (A2:A13) ( ) average(a3:a12) A3 A12 median(a3:a12) A3 A12 mode(a3:a12) A3 A12 1.1.2 10 ( ) 1300 10 1 1 (outlier) 1 10 400 ( 4, 3, 2, 1, 0, 1, 2, 3, 4) 1.1 1. 1.1 ( sum ) C 2. 1.1 4, 2, 5, 3, 4, 5, 100, 2, 4, 4
4 1 1.2 1.2 10 3, 1, 4, 1, 5, 9, 2, 6, 5, 3 =sum(e$3:e3) 1 Ctrl (sort) Ctrl 1, 1, 2, 3, 3, 4, 5, 5, 6, 9 1, 1+1= 2, 2+2 = 4, 4+3 = 7, 7+3 = 10,
1.2 5 ( cummulative sum ) 1, 2, 4, 7, 10, 14, 19, 24, 30, 39 sum G3 =sum($e$3:e3) ( ) (autofill) G4,G5,G6, =sum($e$3:e4), =sum($e$3:e5), =sum($e$3:e6) =sum($e$3:e3) $ $E$3 $ E3 E4, E5, sum $E$3 E3 ($ 2 ) [ 1] 2 [ 2] (E2:E12) (G2:G12) & sum(n:m) n m ( ) ( ) 1.2 1. 1.2 ( ) 2. 1 ( 1 )
6 1 1.3 1.3 5 x y (time series data) x y y y (moving average method) x 1,x 2,x 3,,x n x 1 + x 2 + x 3 3, x 2 + x 3 + x 4 3, x 3 + x 4 + x 5,, x n 2 + x n 1 + x n 3 3 3
1.3 7 1 2 3 4 OK 3 3 n x 1,x 2,,x n x 1 x 2 x n n n x 1 n x 2 n x n (geometric mean) 3 1 x 9 x/1 =9/x x 2 =1 9, x = 1 9=3 geomean(1,9) 3 n x 1,x 2,,x n x 1 1,,x 1 n (1/x 1 + +1/x n )/n (harmonic mean) 6km 4km 5km 4.8 km harmean(6,4) 4.8 (trimmed mean ) 5 3 ( C trimmean ) y f f(y) ={f(x 1 )+f(x 2 )+f(x 3 )+ + f(x n )}/n f f(x) =x, f(x) = log x, f(x) =1/x, f(x) =x 2 y 1.3 1. 1.3 2 4 2. ( ) 3. 3 4.
8 1 1.4 2009 (http://www.nta.go.jp/kohyo/tokei/kokuzeicho/minkan2009/minkan.htm) 500 400 400 500 (5 ) (http://www.stat.go.jp/data/jinsui/index.htm) http://www.ipss.go.jp/ D C D2 =-c2 (2011) 50 60 64 35 39
1.4 9 (statistics) (state)
10 1 1 1 D4 =c4/$c$11 D4 D11 OK C11 =sum(c4:c10) SUM C 11 $ $C$11 C$11 11 $ /
2 11 2 C10 =$b10*c$9 * a b a b a/b a b ( ) B $ A4 A 4 $ A4, A$4, $A4, $A$4 4 $ F4 $ A4, A$4, $A4, $A$4
8 ( ) t χ 2 F 97 8.1 8.1. 0 9 900, 4 40 a 40 ( ) X b 40 X E( X) V ( X) c 40 X 900 (E3:AH32(30 30) ) ( ) (population) ( ).
98 8 4 40 (B38:AO41 ) ( ) ( ) N n X 1,X 2,,X n N C n X 1,X 2,,X n 1/ N C n X 1,X 2,,X n ( )
8.1 99 X 1,X 2,,X n ( ) x 1,x 2,,x n 900 4 40 (900 ) 0 9 0.1 ( ) ( ) 30 ( ) 30 ( 4 40 ) 4 ( ) X 1,X 2,X 3,X 4 ( ) 30 30 randbetween (i, j) index 40 40 (=average(b38:b41) =average(ao38:ao41)) 4.34 ( =average(b42:ao42)) 4.37 ( =average(e3:ah32)) 1.69 ( =varp(b42:ao42)) 8.31 ( =varp (e3:ah32)) =countif($b$42:ao42,a62)-countif($b$42:ao42,a63) 8.2 randbetween(0,9) 0 9 1 index(,i, j) i j (i, j) 8.1 1. 8.1 2 10 (8.10 2 )
100 8 8.2 8.2 8.1 10, 20, 30 60. 30 60 =countif($d$69:$bk$69,a75) countif ($d$69:$bk$69,a76)
n =10, 20, 30 X = X 1 + X 2 + + X 10 10 X = X 1 + X 2 + + X 10 + + X 20 20 X = X 1 + X 2 + + X 10 + + X 20 + + X 30 30 8.2 101 X m m m E( X) =m m, σ 2 n n σ 2 /n 0 X m (law of large numbers) n X N(m,σ 2 /n) ( 6.8 ) E( X) =m X X m (unbiased estimator) m σ 2 n X N(m, σ 2 /n) 30 60 10 n =10 20 n =20 E( X) n =10,n=20,n=30 4.33, 4.33, 4.3 V ( X) 0.98, 0.31, 0.27 n V ( X) σ 2 /n 8.2 1. 8.2 80 V ( X)
102 8 8.3 8.3 8.1 40 s 2 u 2. 40 S 2 S 2 = (X 1 X) 2 +(X 2 X) 2 +(X 3 X) 2 +(X 4 X) 2 = 1 4 (X i 4 n X) 2 i=1 4 40 S 2
8.3 103 (2, 4) x =(2+4)/2 =3 s 2 = ((2 3) 2 +(4 3) 2 )/2 =1 m x m x (2 < 3) ((2 2) 2 +(4 2) 2 )/2 =2 1 (3 < 4) ((2 4) 2 +(4 4) 2 )/2 =2 1 (2.4 ) E(S 2 ) σ 2 4/3 4/3=1.33 ( n/(n 1) ) S 2 4 3 U 2 = (X 1 X) 2 +(X 2 X) 2 +(X 3 X) 2 +(X 4 X) 2 3 U 2 E(U 2 )=σ 2 n n n 1 S 2 = U 2 m σ 2 n E( X) =m ( X m ) E(U 2 )=σ 2 (U 2 σ 2 ) 8.3 1. (7,2,4,8) s 2 u 2 u 2 /s 2 2. X m 2 So = 1 n m 8.1 4 (X i m) 2 i=1
104 8 8.4 8.4 60 10 900 4 40 [α, β] n X N(m,σ 2 /n) Z =( X m)/(σ/ n)
N(0,1) ( σ 2 ) 8.4 105 Z 1.96 Z 1.96 0.95 1.96 ( X m)/(σ/ n) 1.96 m X 1.96σ/ n m X +1.96σ/ n [ X 1.96 σ ] σ, X +1.96 n n m 0.95 (7.2 7.3 ) 95 % (confidence interval) (interval estimation) (58.8, 64.1, 63.7, 41.3) x = 57, σ =10 95 % [ x 1.96 4 σ, x +1.96 4 σ ] [ = 57 1.96 10 ] 10, 57 + 1.96 =[47.2, 66.8] 2 2 95 % 90 %, 99 % 99 % [ ] X 2.58σ/ n, X +2.58σ/ n 90 % σ/ n 1.645 σ 2 m σ 2 σ 2 S 2 U 2 95 % [ X 1.96 S ] S, X +1.96 n n
106 8 σ U T = ( X m)/(u/ n)=( X m)/(s/ n 1) t n =4 n 1=3 t 95 % [ X 3.18 U ] U, X +3.18 4 4 (D AQ) 4 ( ) 4.37 t D43 =average(d38:d41) D57 D58 =d43-3.18*$d$46/2 =d43+3.18*$d$46/2 95 % 100 5 40 1.96 3.18 1.96 N(0,1) 5% 3.18 3 t 5% 1% 1.96 2.58, 3.18 5.84 8.4 1. 95 % 2. 8.4 10 99 % 9 t 1% 3.25 ( n = 1)
2012 Printed in Japan ISBN978-4-627-84302-8