1 Excel 1. [Standard] (call [Call Standard]) Excel [ ] [E ] Excel m 1 ( ) (

Size: px

Start display at page:

Download "1 Excel 1. [Standard] (call [Call Standard]) Excel [ ] [E ] Excel m 1 ( ) ("

きよたつかなり
4 years ago
Views:

1 1 Excel 1. [Standard] (call [Call Standard]) Excel [] [E ] Excel m 1 ( ) ( p Excel Excel 2019 Excel 2013 [] [R] [R x ] [] [ ] [ ] 1

2 ( 30 ) 1: ( Excel 2013) Excel A1 A30 2. C2 C6 20,40,60,80,100 D2 D6 1-20, 21-40, 41-60, 61-80, (Excel 2013 ) (1) [] [ ] [] (2) [ ] [Excel ] [ ] (3) [ ] [ ] [OK] 4. [ ] [ ] [ ] 2

3 5. $A$1:$A$30 $C$2:$C$6 $C$10 OK 1. D11 D15 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

4 C2 C6 C20 C24 2. D20 D24 3. D20 ( ) =FREQUENCY(A1:A30,C20:C24) Ctrl Shift Enter : (Sheet2 Sheet1 ) 1 Excel 2013 FREQUENCY D20 D

5 4: Excel , 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 ) : 5

6 Excel , 6, 12, 9, 2 D20 D24 E 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 G A1 A30 A36 A65 2. A1 A30 B36 B65 3. A36 B65 [ ] [ ] [ ] ( ) 2 6

7 ( ) ( ) 100m x N x 1, x 2,, x N N x 3.1 ( ) x 1,, x N x := 1 N (x 1 + x x N ) = 1 N x k (1) x A1 A30 1. F2 F G2,G3 30, =SUM(A1:A30) G3 3. G4 (1) =G3/G2 4. G5 =AVERAGE(A1:A30) a, b 7

8 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, , 3, = 1001 (3) [5, 58 1 A ] 3. 5 x 3.2 x = 1 N (x 1f 1 + x 2 f 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: , 152, 155, 149, 151 [cm] 150 7, 2, 5, 1, 1 ( )/5 = = 150 [cm] 8

9 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 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) = = 21 6 = 7 2 = 3.5 (1/6,..., 1/6) ([3, p.53, 3.11]) (4) (1) (5) 9

10 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, =K ( ()) A1 A30 B70 B99 B69 2. A70 A

11 3. B69 B99 [ ] [ ] [ ] 4. B70 [ ] [ ] = A102 ( ) B Excel 2013 MEDIAN( ) C102 ( ) D102 =MEDIAN(B70:B99) 54.5 [ ] [ ] [ ] ()

12 l Q 1 l l l Q 3 1: 1 3 ( ) 8 2,3,5, = 4 5,7 2, (6) 12

13 1. [2, 67 ] 1 25% % ( ( )) 3. [1] [4] ( ) Excel 2013 QUARTILE(, ) 0, 1, 2, 3, = 3 Excel 2013 QUARTILE 9 8 QUARTILE (6) A1 A4 2,3,5,7 3. A6 A B6 =QUARTILE($A$1:$A$4,0) 2 13

14 (7) (7) (6) ,2,3,4,5,6,7,8 (6) (7) (6) A103 A107 C103 C107 B103 A107 D103 D ( ) ( ) ( ) ( ) Excel Excel

15 1. (8) (8) ([3, p.16, 1.1]) 0, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 3, 4, 5 (9) ( )

16 ( ) ( ) B70 B99 1. A110, A B110, B111 =MAX(B70:B99), =MIN(B70:B99) 3. F8 G8 =B102-B103 16

17 ( ) x N x 1,, x N x x 1 x, x 2 x,, x N x C69 2. G4 C70 =B70-$G$4 B70 C70 3. C70 C71 C N (x k x) = 0 (10) 11Excel 2013 C100 =AVERAGE(C70:C99) (10) (10) x (10) 0 2 v s 2 17

18 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) (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) , 1003, 998 (13) a = 1 (11) (12) Excel ,2,3,4,5 (11),(12) Excel 2013 (11) x = 1 5 ( ) = 15 5 = 3 (11) s 2 = 1 5 {(1 3)2 + (2 3) (5 3) 2 } = 10 5 = 2 18

19 (12) s 2 = 1 5 ( ) 3 2 = = 2 Excel 2013 Sheet3 1. A1 D 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) ,3,5,8,12 (11),(12) 6, 13.2 Excel

20 (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 D69, E69 2. D70, E70 =C70^2, =B70^2 D70 E99 3. B100 =AVERAGE(B70:B99) C100, D100, E100 20

21 4. F9, F10, F11 / / / 5. G9 (11) =D G10 (12) =E100-G4^2 7. G11 =VARP(B70:B99) 8. H9 =SQRT(G9) H10 9. H11 =STDEVP(B70:B99) H9, H (11),(12) Excel 2013 () (x 1 x) 2, (x 2 x) 2,, (x N x) 2 x 1, x 2,, x N x 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

22 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 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

23 = (x k x)(y k ȳ) N (x k x) 2 N (y k ȳ) 2 (22) 4.2 (22) r 1 r x cm y m x y x y 6: (cm) (m) Excel 2013 x y x, y x = = 19.4, ȳ = = (x k x) 2 = = 66.40, (y k ȳ) 2 = = (x k x)(y k ȳ) = = r r = r 1 23

24 Excel 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 E F2 =D2^2 F3 F G2 =C2*D2 G3 G C12 =SUM(C2:C11) D12 G B17, B18 =E12/10, =F12/ B19 =G12/ B20 =G12/SQRT(E12*F12) 15. A22, A23, A24 B23, B24 =COVAR(A2:A11,B2:B11) =CORREL(A2:A11,B2:B11) B19, B

25 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

26 15 14 x y Excel 2013 (25) b 1 = s xy s 2 x = , b 0 = ȳ s xy x = 14.9 b s x Excel 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 [ ] [ ] ( ) ( ) 8. [] [...] [ ] [ ] 26

27 [1], I, , [2],, 2, [3],,,, [4] 13, I,, 24 [5],,,

151021slide.dvi

151021slide.dvi : Mac I 1 ( 5 Windows (Mac Excel : Excel 2007 9 10 1 4 http://asakura.co.jp/ books/isbn/978-4-254-12172-8/ (1 1 9 1/29 (,,... (,,,... (,,, (3 3/29 (, (F7, Ctrl + i, (Shift +, Shift + Ctrl (, a i (, Enter,