Excel 4.1 4.1.1 1 X n X,X, 1,Xn X=X X X /n 1 n Excel AVERAGE =AVERAGE Excel MEDIAN 3 =MEDIAN Excel MODE =MODE 4.1. 1 Excel MAX MIN =MAX MIN n X,X,,X X 4-1 1 n
V X1-X + X-X + + Xn-X V= n 0 0 Excel VARP 3 4.1.3 =VARP Excel STDEVP =STDEVP SQRT 1 =SQRTVARP. 100 1 3 4 5 1 0+ +0+1+ +1++ ++3+ +3+4+ +4+5+ +5 100 0*5+1*11+*40+3*+4*17+5*5 50 = = =.5 100 100 5 11 40 17 5 0,0,,0,1,1,,1,,,,,3,3,,3,4,4,,4,5,5,,5 17 56 100 50 51 4-
3 40 4.5 0-.5 *5+ 1-.5 *11+ -.5 *40+ 3-.5 *+ 4-.5 *17+ 5-.5 *5 100 6.5*5+.5*11+0.5*40+0.5*+.5*17+6.5*5 14 = = =1.4 100 100 5 1.4 =1.19. 1 3 4 5 6 7 83 39 83 310 311 339 7 61 85 33 350 150 53 58 3 348 189 86 15 8 386 340 84 67 315 390 399 173 8 90 360 88 13 78 73 43 31 199 5 393 331 35 183 350 335 1 67. 1993 m A B C D E 1 83 3 339 4 350 5 348 6 386 7 390 8 360 9 43 10 393 11 350 1 150 1 39 00 13 7 3 50 14 150 4 300 15 189 5 350 16 340 6 400 17 399 7 450 18 88 48 73 1 E =AVERAGE B:B48 E3 =MEDIAN B:B48 3 E4 4-3
=MAX B:B48 E5 =MIN B:B48 E6 =E4-E5 4 E7 =VARP B:B48 5 E8 =STDEVP B:B48 6 50m E11 E17 D11 D17 =FREQUENCYB:B48,E11:E17 SHIFT CTRL 7 D10 E17... n f ; f ; f ; X=f ; X f ; n n X,X, 1,X n X+X 1 + +Xn X= n X.. 4-4
n X,X,,X 1 n. X+X 1 + +Xn X= n. X 1- +X - + + Xn- n v= v. X1-X + X-X + + Xn-X s= n. X1-X + X-X + + Xn-X u= n-1 n n-1. v u v u Excel s =VARP =STDEVP Excel u u VAR STDEV.. =VAR =STDEV A, X A, c. X+c A +c, 4-5
. cx A c,c. X- A0,. X - / A 0,1 n i i i=1,,,n Xi X=X 1+X + +Xn 1 n 1 n 1 n. X=X+X+ +X + + + + + + 1= = = n= 1 = = = n = X n n.. n X,X, 1, Xn X X /n... n X n X N,. X N, X- Z= N 0,1 N 0,1. N, n X N, /n Z= X- / n N 0,1 Excel N 0,1 NORMSDIST z NORMSINV p N, NORMDIST z,,,true FALSE NORMINVp,, NORMSDIST z - z X z z Pr X z z z Pr z X 1-NORMSDIST z 4-6
NORMSINV p NORMSDIST z z p Pr X z=p p z NORMDISTz,,,TRUE FALSE N, TRUE z Pr X z FALSE NORMDIST z, 0, 1, TRUE NORMSDISTz NORMINVp,, NORMDIST z p Pr X z=p p z NORMINV z, 0, 1 NORMSINV z. 55 10 63 49 55 10 N 55,10 X N 55,10 55 10 normal distribution N 55,10 N 55,10 63 Excel =NORMDIST63,55,10,TRUE 63 Excel =1-NORMDIST63,55,10,TRUE 0.11855 63 0.1 1. 49 =1-NORMDIST49,55,10,TRUE 0.75747 7.6... n Z,Z,, Z N 0, 1 1 n =Z 1 +Z + +Zn n N, n X, 1 X,, Xn X=X+X+ 1 +X n /n N, /n X- Z= / n 4-7
N 0,1 X - =Z = /n X k - Z k =, k=1,,,n N 0,1 X1- X - + +Xn- =Z +Z + +Z 1 n = n. X1-X + X-X + + Xn-X = n-1 s u X-X + X-X + + X-X =ns= n-1 u 1 n ns n1u - = = n-1 Excel CHIDIST y,m CHIINV p,m CHIDISTy,m m y m X y y Pr y X y y Pr X y 1-CHIDISTy,m CHIINVp,m CHIDIST m y y p Pr y X=p p y... Z N0,1 Y m Z Y Z Y/m m N, n X s s X - / / n N 0,1 ns/ 4-8
n-1 X - / / n X- = s/ n- 1 ns / n-1 n-1 u u ns =n1u - X- u/ n n-1. N 1, N, n n X X 1 1 s1 s v= ns +n s /n+n - 1 1 1 X1-- X 1- v 1 1/n+1/n n+n 1 -. N 1, 1 N, n1 n X1 X u u w=u /n w=u /n 1 1 1 1 X1-- X 1- w+w 1 w+w 1 1 1 w / n-1 +w / n-1 Excel TDIST t, m, 1 TINV p, m TDISTt,m,1 m T t t Pr t T t m -t TDIST t,m,1 TDISTt, m, m t -t t -t Pr T -t t T t TDIST t, m, *TDIST t, m, 1 -t t Pr-t T t 1-TDIST t,m, TINVp,m TDIST m -t t -t t p Pr T -t t p p t 4-9
... X X m n 1 X/m 1 F= X/n m, n F= X/n /X/m 1 n,m F m, n Pr F b= b G=1/F n,m Pr a G = a Pr a 1/F = Pr F 1/a = b 1/a 1. N, N, n1 n u1 u u1 u F= n-1,n-1 1 Excel FDIST f,m,n FINV p,m,n FDISTf, m, n m, n f m, n F f f Pr f F f Y Pr F f 1- FDIST f,m,n FINVp,m,n FDIST m, n f f p Pr f F =p p f 4.3 X=f ; Pr a X b=1- Pr a f ; b=1- a f ; f ; b g1 a; g b; Pr g1 b; g a; =1-1- g b; g a; 1 N, X- u/ n n-1 n X u X1-X + X-X + + Xn-X u= n-1 n-1 Pr-t T t =1-4-10
Pr-t X- t u/ n =1- PrXt - u/ n X+t u/ n =1-1- Xt - u/ n,x+t u/ n Excel t TINV,n-1 =AVERAGE -TINV,n-1 *STDEV /SQRT n =AVERAGE +TINV,n-1 *STDEV /SQRT n. 165 170 170 175 Excel A1 165 A 170 A3 170 A4 175 n=4 =1-0.95=0.05 =AVERAGEA1:A4 - TINV 0.05,3 *STDEV A1:A4 /SQRT 4 =AVERAGEA1:A4 +TINV 0.05,3 *STDEV A1:A4 /SQRT 4 163.5039 176.4961 95 163.5cm176.5cm 4.4 4-11
a / b / a b a b a b a b. 00cc 10 196cc 5cc 10 N, /10 /10 =00 X- u/ n n-1 n X u =0.05=5 n=10 Excel =TINV0.05,9.6159 -.6.6 X=196 s=5 T Excel = 196-00 /5/SQRT 10 -.530. 0.5 0.01 10 0.51 N, n X N, /n n X 4-1
.5.5 N0.5,0.01 n=10 N0.5,0.01/10 N 0.5,0.01/10 Excel =NORMINV0.05,0.5,0.01/SQRT 10 =NORMINV0.975,0.5,0.01/SQRT 10 0.4938 0.506 10 0.51. 1,600 100 1,570 10 1,600 N, n X N, /n Z= X - / / n N 0,1 1/ X - /s/ n-1 T n-1 N, =1,600 n X s X1600 - s/n -1 n-1 100-1=99 Excel =TINV0.05,99-1.984 1.984 Excel =TINV0.01,99 -.66.66 1570 10 Excel = 1570-1600 /10/SQRT 99 -.487 1,600 4-13
. 100 100 75 105 1 N N n1 n u1 u 1 1 1 1 = n-1 u / = n-1u / n-1 n-1 1-1- 1 - - F= n 1 u / /n 1 = u n 1 u / /n 1 u n-1,n-1 F n=100 n=75 s=100 s=105 1 1 ns 1 1 100*100 u 1 = = =10101.0 n1-1 99 nu 75*105 u = n 1 = 74 =11174.0-1 1 u u F=u /u 74,99 74,99 Excel =FINV0.05,74,99 1.4 F u 11174.0 F= = =1.106 u 10101.0 1. 16.. 107 10 14.. 11 8.. 1 1 1 N, N, n n X X u u 1 1 1 = 1 1 1 u u F=u /u n-1,n-1 n=16 n=14 s=10 s=8 1 1 u1 ns/n 11 1-1 16*100/15 F= = = =1.548 u nu/n - 1 14*64/13 15,13 Excel =FINV0.05,15,13.533113.53 4-14
n1 n X1 X s1 s X1-- X 1- ns +n s /n+n- 1/n+1/n 1 1 1 n+n 1 - n=16 n=14 X=107 X=11s=10s=8 = 1 1 1 1.. T Excel 107-11 16*100+14*64 / 16+14-1/16+1/14 = 107-11 /SQRT 16*100+14*64 /16+14 - *SQRT1/16+1/14-1.447 8 Excel =TINV0.05,8.048409 -.048.048... 40 41 4 43 44 45 46 47 48 0 0 5 11 7 1 0 0 6 0 4 4 5 4 4 1 1 5 n=6, 1 1=5, X=44.00, 1 u 1 =0.96, s 1 =0.9 n=5, =4, X=44.04, u =3.46, s =3.3 F=u /u =3.46/0.96=3.60 1 =4, 1=5 Excel =FINV0.01,4,5.6054.6 n1 n X1 X u1 u w=u 1 1 /n1 w=u /n X1-- X 1-1 w+w w+w 1 1 1 = w / n - 1 +w / n - 1 = 1 1 1 1 w=u /n=0.96/6=0.0369w=u /n=3.46/5=0.1384 4-15
Excel w+w 1 0.0369+0.1384 = 1 1- - w / n 1 +w / n 1 0.0369/5+0.1384/4 = 0.0369+0.1384 / 0.0369/5+0.1384/4 36.05 36 5 36 Excel =TINV0.05,36.08091 -.03 X1-- X 1-44.00-44.04 = w+w 0.0369+0.1384 Excel 1 = 44.00-44.04 /SQRT 0.0369+0.1384-0.0955 4-16