1 Excel 1. [Standard] (call [Call Standard]) Excel [] [E ] Excel 2. 2 2.1 50m 1 ( ) 30 1. 2019 (http://news.fukuoka-edu.ac.jp/form/guide/navi2019.pdf), p.7 8 2. Excel 2019 4 Excel 2019 Excel 2013 [] [R] [R x64 3.5.1] [] [ ] [ ] 1
( 30 ) 1: 1 2 3 4 5 6 7 8 9 10 46 30 24 49 39 9 75 29 54 30 11 12 13 14 15 16 17 18 19 20 78 65 42 75 57 53 57 46 62 56 21 22 23 24 25 26 27 28 29 30 69 92 96 55 44 66 40 54 66 65 ( Excel 2013) Excel 2013 1. A1 A30 2. C2 C6 20,40,60,80,100 D2 D6 1-20, 21-40, 41-60, 61-80, 81-100 3. (Excel 2013 ) (1) [] [ ] [] (2) [ ] [Excel ] [ ] (3) [ ] [ ] [OK] 4. [ ] [ ] [ ] 2
5. $A$1:$A$30 $C$2:$C$6 $C$10 OK 1. D11 D15 2: 20 1 40 6 60 12 80 9 100 2 0 2. [ ] [] [2-D ] [] 3. 1,2,3,4,5 1 [ ] [ ] [ ] $D$2:$D$6 OK 1. D11 D15 2. [ ] [ ] [ ] 3. 1,2,3,4,5 1 [ ] [ ] [ ] $D$2:$D$6 OK Excel 2013 FREQUENCY 3
3 4 1. C2 C6 C20 C24 2. D20 D24 3. D20 ( ) =FREQUENCY(A1:A30,C20:C24) Ctrl Shift Enter 1 1 30 3: 1 2 3 4 5 6 7 8 9 10 71 72 4 49 82 100 64 93 31 37 11 12 13 14 15 16 17 18 19 20 100 71 23 50 72 48 55 86 34 84 21 22 23 24 25 26 27 28 29 30 2 100 77 63 57 52 43 44 62 47 (Sheet2 Sheet1 ) 1 Excel 2013 FREQUENCY D20 D24 2.2 2 1 2 4
4: 20 1 40 7 60 19 80 28 100 30 Excel 2013 1, 6, 12, 9, 2 D20 D24 1. E20 =D20 2. E21 =E20+D21 () 3. E21 E22,E23,E24 (E24 30 ) 4. C27, D27 C20 C24 E20 E24 C28 C32, D28 D32 ( C27, D27 ) 2 1 2.3 1 3 1 2 4: 5
20 0.033 40 0.200 60 0.400 80 0.300 100 0.067 Excel 2013 1, 6, 12, 9, 2 D20 D24 E24 30 1. F20 =D20/$E$24 2. F20 F21, F22, F23, F24 (F21, F22, F23, F24 1 ) 3. F27, G27 C20 C24 F20 F24 F28 F32, G28 G32 3 1 2.4 2 4 1 1 1 3 1. A1 A30 A36 A65 2. A1 A30 B36 B65 3. A36 B65 [ ] [ ] [ ] ( ) 2 6
( ) ( ) 100m 3 3.1 x N x 1, x 2,, x N N x 3.1 ( ) x 1,, x N x := 1 N (x 1 + x 2 + + x N ) = 1 N x k (1) 5 1 1 x A1 A30 1. F2 F5 1 2 2. G2,G3 30, =SUM(A1:A30) G3 3. G4 (1) =G3/G2 4. G5 =AVERAGE(A1:A30) 4 5 5 1 3 a, b 7
1. ax 1 + b, ax 2 + b,, ax N + b ax + b x (1) ax + b = ax + b (2) 2. a = 1 x + b = x + b (3) 1002, 1003, 998 1000 1000 2, 3, 2 1 1000 + 1 = 1001 (3) [5, 58 1 A ] 3. 5 x 3.2 x = 1 N (x 1f 1 + x 2 f 2 + + x n f n ) = 1 n x k f k (4) N f 1 = x 1 N + x f 2 2 N + + x f n n n N = f k x k (5) N N = n f i i=1 5: 3. 5 143, 152, 155, 149, 151 [cm] 150 7, 2, 5, 1, 1 ( 7 + 2 + 5 1 + 1)/5 = 0 150 + 0 = 150 [cm] 8
f x 1 f 1 1 N f x 2 f 2 2 N... x n f n f n N N 1 (4) (5) N N 4. 6 1 1. I27, J27, K27 2. I28 I32 10,30,50,70,90 3. J28 =I28*D11 J28 J32 4. (5) (f 1 /N,..., f n /N) X X E(X) E(X) = 1 1 6 + 2 1 6 + + 6 1 6 = 21 6 = 7 2 = 3.5 (1/6,..., 1/6) ([3, p.53, 3.11]) (4) (1) (5) 9
J33 J33 =SUM(J28:J32) ( 1600) 4. 3 K28 =I28*G28 K28 K32 K33 K33 =SUM(K28:K32) ( 53.3) 5. F6,F7 G6, G7 =J33/G2, =K33 6 1 3.2 3.3 ( ()) 7 1 1. A1 A30 B70 B99 B69 2. A70 A99 1 30 10
3. B69 B99 [ ] [ ] [ ] 4. B70 [ ] [ ] 5. 15 54 16 55 54 + 55 2 = 54.5 6. A102 ( ) B102 54.5 Excel 2013 MEDIAN( ) C102 ( ) D102 =MEDIAN(B70:B99) 54.5 [ ] [ ] [ ] 7 1 3.3 4 1 2 3 3.4 () 1 3 11
l Q 1 l l l Q 3 1: 1 3 ( ) 8 2,3,5,7 1 3 4 3.3 3+5 2 = 4 5,7 2,3 2.5 1 2.5 3 6 1 3 2 2.5 4 6 7 (6) 12
1. [2, 67 ] 1 25% 1 8 25% 3 1 2.5 2. 3.4 4 3.4 ( ( )) 3. [1] [4] ( ) Excel 2013 QUARTILE(, ) 0, 1, 2, 3, 4 1 2 = 3 Excel 2013 QUARTILE 9 8 QUARTILE (6) 1. 2. A1 A4 2,3,5,7 3. A6 A10 3 4. B6 =QUARTILE($A$1:$A$4,0) 2 13
1 3 2 2.75 4 5.5 7 (7) (7) (6) 8 1. 1,2,3,4,5,6,7,8 (6) (7) 9 2. 1 (6) A103 A107 C103 C107 B103 A107 D103 D107 1 1 3 ( ) 9 42 54.5 66 96 ( ) 9 42.5 54.5 65.75 96 ( ) 2 44 59.5 77 100 ( ) 2 44.75 59.5 75.75 100 Excel 2013 5. 5. Excel 2013 http://www.stat.go.jp/koukou/howto/process/graph/graph5.htm 14
1. (8) (8) ([3, p.16, 1.1]) 0, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 5 (9) 1 3 0 2 2.5 3 5 2. 3. 3.4 3.5 ( ) 1 40 60 50 9 1 15
21 549.6 500 56.6 2000 1 438 100 ( ) 4 4.1 4.1 ( ) 1 10 1 B70 B99 1. A110, A111 2. B110, B111 =MAX(B70:B99), =MIN(B70:B99) 3. F8 G8 =B102-B103 16
10 1 4.2 4.2 ( ) x N x 1,, x N x x 1 x, x 2 x,, x N x 11 1 1. C69 2. G4 C70 =B70-$G$4 B70 C70 3. C70 C71 C99 11 1 N (x k x) = 0 (10) 11Excel 2013 C100 =AVERAGE(C70:C99) (10) (10) x 0 4.3 (10) 0 2 v s 2 17
4.3 ( ) x 1, x 2,, x N s 2 s 2 = 1 N (x k x) 2 = 1 { (x1 x) 2 + (x 2 x) 2 + + (x N x) 2} (11) N 4.1 ( ) (11) x 1, x 2,, x N s 2 s 2 = x 2 ( x) 2 (12) [ ] (12) (11) s 2 = 1 { x 2 N k 2x k x + ( x) 2} = 1 N = x 2 2 x x + ( x) 2 = x 2 ( x) 2 x 2 k 2 x 1 N x k + ( x) 2 (11) 2 12 s 2 (x) s 2 5 ax 1 + b, ax 2 + b,, ax N + b s 2 (ax + b) 1. s 2 (ax + b) = a 2 s 2 (x) (13) 2. 1002, 1003, 998 (13) a = 1 (11) (12) Excel 2013 12 5 1,2,3,4,5 (11),(12) Excel 2013 (11) x = 1 5 (1 + 2 + + 5) = 15 5 = 3 (11) s 2 = 1 5 {(1 3)2 + (2 3) 2 + + (5 3) 2 } = 10 5 = 2 18
(12) s 2 = 1 5 (12 + 2 2 + + 5 2 ) 3 2 = 55 5 9 = 2 Excel 2013 Sheet3 1. A1 D1 2 2 2. A2 A6 1,, 5 A7 A8 =SUM(A2:A6) 3. F1 F5 G1, G2 5, =A8/G1 4. B2 =A2-$G$2 C2 =B2^2 D2 =A2^2 B2 D6 A8 B8,C8,D8 5. G3 (11) =C8/G1 6. G4 (12) =D8/G1-G2^2 7. G5 Excel 2013 =VARP(A2:A6) =VAR(A2:A6) 13 5 2,3,5,8,12 (11),(12) 6, 13.2 Excel 2013 19
(11) (11) u 2 := 1 N 1 (x k x) 2 (14) (11) (14) (11) (14) Excel 2013 =VAR(A2:A6) (14) =VARP P Population s 2 (x x) 2 km km 2 x s s 4.4 () x 1, x 2,, x N s s = 1 (x k x) N 2 = 1 x 2 k N ( x)2 = x 2 ( x) 2 (15) 13 1 (11),(12) Excel 2013 1. D69, E69 2. D70, E70 =C70^2, =B70^2 D70 E99 3. B100 =AVERAGE(B70:B99) C100, D100, E100 20
4. F9, F10, F11 / / / 5. G9 (11) =D100 6. G10 (12) =E100-G4^2 7. G11 =VARP(B70:B99) 8. H9 =SQRT(G9) H10 9. H11 =STDEVP(B70:B99) H9, H10 14 13 1 (11),(12) Excel 2013 () (x 1 x) 2, (x 2 x) 2,, (x N x) 2 x 1, x 2,, x N x 4.4 2 1 1 3 30 N k x k y k (16) 2 x y (x 1, y 1 ), (x 2, y 2 ),, (x N, y N ) (16) x = 1 N x k, ȳ = 1 N y k (17) 21
s 2 x = 1 N (x k x) 2, s 2 y = 1 N (y k ȳ) 2 (18) s x = 1 (x k x) N 2, s y = 1 (y k ȳ) N 2 (19) x y 4.5 ( ) x y s xy = 1 (x k x)(y k ȳ) (20) N 15 ( s xy = 1 N ) x k y k N xȳ (21) N 4.1 2 x y 2 x y [ ] (20) (x k x)(y k ȳ) > 0 k x k, y k (20) (x k x)(y k ȳ) < 0 k x k, y k 4.6 () {x k } N, {y k} N r xy r r = r xy = s xy s x s y = 1 N 1 N (x k x)(y k ȳ) (x k x) 2 1 N 22 (y k ȳ) 2
= (x k x)(y k ȳ) N (x k x) 2 N (y k ȳ) 2 (22) 4.2 (22) r 1 r 1 14 10 x cm y m x y x y 6: 1 2 3 4 5 6 7 8 9 10 (cm) 20 22 18 21 20 16 24 15 18 20 (m) 15 17 15 14 17 12 20 10 14 15 Excel 2013 x y x, y x = 194 149 = 19.4, ȳ = 10 10 = 14.9 2 10 10 (x k x) 2 = 0.36 + 6.76 + + 0.36 = 66.40, (y k ȳ) 2 = 0.01 + 4.41 + + 0.01 = 68.90 10 (x k x)(y k ȳ) = 0.06 + 5.46 + + 0.06 = 61.40 r r = 61.4 66.4 68.9 0.907 r 1 23
Excel 2013 1. 2. A1 G1 3. A2 A11 B2 B11 4. A15 A20 5. B15, B16 =SUM(A2:A11)/10, =SUM(B2:B11)/10 6. C2 =A2-$B$15 C3 C11 7. D2 =B2-$B$16 D3 D11 8. E2 =C2^2 E3 E11 2 9. F2 =D2^2 F3 F11 2 10. G2 =C2*D2 G3 G11 11. C12 =SUM(C2:C11) D12 G12 12. B17, B18 =E12/10, =F12/10 13. B19 =G12/10 14. B20 =G12/SQRT(E12*F12) 15. A22, A23, A24 B23, B24 =COVAR(A2:A11,B2:B11) =CORREL(A2:A11,B2:B11) B19, B20 16 1 1 1 3 24
4.5 2 N (16) x y ( ) x y y x y = f(x) f {ε i } y i = f(x i ) + ε i, (i = 1, 2,, N) f(x) f(x) β 0, β 1 f(x) = β 0 + β 1 x 2 2 l(β 0, β 1 ) = {y k (β 0 + β 1 x k )} 2 b 0 = β 0, b 1 = β 1 l(b 0, b 1 ) = min β 0,β 1 l(β 0, β 1 ) 4.3 (16) f(x) = b 0 + b 1 x (23) s 2 x > 0. (24) (23) b 0, b 1 b 1 = s xy, b s 2 0 = ȳ b 1 x = ȳ s xy x. (25) x s 2 x s 2 x (18) s xy (20) x, ȳ (17) 25
15 14 x y Excel 2013 (25) b 1 = s xy s 2 x = 61.4 66.4 0.925, b 0 = ȳ s xy x = 14.9 b s 2 1 19.4 3.039. x Excel 2013 1. 2. D15 D17 b1 b0 3. E16, E17 =B19/B17, =B16-E16*B15 ( 2 ) 4. D22, D23, D24 b1 b0 E23, E24 =SLOPE(B2:B11,A2:A11) =INTERCEPT(B2:B11,A2:A11) E19, E20 ( 2 ) 5. A1 B11 [ ] [ ] ( ) 6. 0.0 10.0 7. ( ) 8. [] [...] [ ] [ ] 26
[1], I, 2013 1, [2],, 2, [3],,,, 2017. [4] 13, I,, 24 [5],,, 20 9. 27