《EXCEL统计分析》12.1 拟合优度检验

正态分布拟合优度检验 组段组段组中值实际频数f2正态分布理论频数卡方分量 下限上限X 概率Px -T) 0.750.800.775 0.7750.6010.009021 0.800.850.825 2 16501.3610.027821 0.454 0.850.900.875 11.37599530.075840 9.1 0.900.950.92515 13.87512.8340.149640 18.0 0.487 0.951.000.975 25.35024.7160.213748 25.60.005 1.001.051.0 26 26.65027.3160.221060 26.50.010 1.051.101.07518 1935020.8010.165530 19.90.175 1.101.151.12515 1687518.9840.089735 10.81.663 1.151.201.1753 3.5254.1420.035212 4.2 0.513 1.20 1.200 1.2001.4400.012393 120120.625122.1491.000000 120.0 均数1.005 标准差0.087

正态分布拟合优度检验 组段 下限 组段 上限 组中值 X 实际频数 f fx fx 2 正态分布 概率PX 理论频数 T 卡方分量 (f-T ) 2 /T 0.75 0.80 0.775 1 0.775 0.601 0.009021 1.1 0.80 0.85 0.825 2 1.650 1.361 0.027821 3.3 0.454 0.85 0.90 0.875 13 11.375 9.953 0.075840 9.1 0.90 0.95 0.925 15 13.875 12.834 0.149640 18.0 0.487 0.95 1.00 0.975 26 25.350 24.716 0.213748 25.6 0.005 1.00 1.05 1.025 26 26.650 27.316 0.221060 26.5 0.010 1.05 1.10 1.075 18 19.350 20.801 0.165530 19.9 0.175 1.10 1.15 1.125 15 16.875 18.984 0.089735 10.8 1.663 1.15 1.20 1.175 3 3.525 4.142 0.035212 4.2 0.513 1.20 1.200 1 1.200 1.440 0.012393 1.5 合计 120 120.625 122.149 1.000000 120.0 3.308 均数 1.005 标准差 0.087

卡方统计量3.308 自由度 5 检验水准a005 P值0653 临界值11070

卡方统计量 3.308 自由度 5 检验水准α 0.05 P值 0.653 临界值 11.070

组段下限组段上限 组中值x 实际频数ffx fx 0.75 0.8 =AVERAGE(A3: B3)I =C3*D3=D3*C3^2 0.85 =AVERAGE(A4: B4)2 =C4*D4=D4°C42 0.85 0.9 =AVERAGE(A5: B5)13 =C5*D5D5*C5^2 0.9 0.95 =AVERAGE (A6: B6)15 =C6*D6=D6C6^2 0.95 AVERAGE (A7: B7) 26 7*D AVERAGE(A8: B8)26 =C8*D8=D8*C8^2 1.05 VERAGE (A9: B9)18 =C9*D9=D9*C92 1.15 =AVERAGE(A10: B10 15 =C10*D10=D10*C102 1.15 1.2 =AVERAGE(All: B113 =Cl1Dl1=D11*C11^2 1.2 AvEraGe(A12 B121 =C12*D12=D12*C122 SUMD3: DI=SUM(E3 El=SUM(F3: F 均数=E1313 标准差=SQRT((F13-E13^2/D13)/(

正态分布拟合优度检验（续） 组段下限 组段上限 组中值X 实际频数f fx fx 2 0.75 0.8 =AVERAGE(A3:B3) 1 =C3*D3 =D3*C3^2 0.8 0.85 =AVERAGE(A4:B4) 2 =C4*D4 =D4*C4^2 0.85 0.9 =AVERAGE(A5:B5) 13 =C5*D5 =D5*C5^2 0.9 0.95 =AVERAGE(A6:B6) 15 =C6*D6 =D6*C6^2 0.95 1 =AVERAGE(A7:B7) 26 =C7*D7 =D7*C7^2 1 1.05 =AVERAGE(A8:B8) 26 =C8*D8 =D8*C8^2 1.05 1.1 =AVERAGE(A9:B9) 18 =C9*D9 =D9*C9^2 1.1 1.15 =AVERAGE(A10:B10)15 =C10*D10 =D10*C10^2 1.15 1.2 =AVERAGE(A11:B11)3 =C11*D11 =D11*C11^2 1.2 =AVERAGE(A12:B12)1 =C12*D12 =D12*C12^2 合计 =SUM(D3:D12=)SUM(E3:E12=)SUM(F3:F12) 均数 =E13/D13 标准差 =SQRT((F13-E13^2/D13)/(D13-1))

正态分布概率Px 理论频数T -NORMDIST(B3, $B$15, $E$15, 1) =G3*$DS13 -NORMDIST(B4, $B$15, $E$15, 1)-NORMDIST(B3, $B$15, $E=G4*SDS13 -NORMDIST(B5, $B$15, $E$15, 1)-NORMDIST(B4, $B$15, SE=G5*SD$13 -NORMDIST(B6, $B$15, $E$15, 1)-NORMDIST(B5, $B$15, E=G6*SD$13 -NORMDIST(B7, SB15, $E$15, 1)-NORMDIST(B6, $B$15, SE=G7*$$13 -NORMDIST(B8, $B$15 SE$15, 1 )-NORMDIST(B7, $B$15, SE=G8*SDS13 -NORMDIST(B9, SB$15, SE$15, 1)-NORMDIST(B8, B$15,SE =G9*SD$13 -NORMDIST(B10, $$15, SES15, 1)-NORMDIST(B9 $BS15 $=G10*SD$13 -NORMDIST(B11 SB$15, $ES15, 1)-NORMDIST(B10, $B$15.=G1l*SD$13 1-NORMDIST(B11, SB$15SES15, 1) =G12*$D$13 =SUM(G3: G12) =SUM(H3: H12) D13-1)

正态分布拟合优度检验（续） 正态分布概率PX 理论频数T =NORMDIST(B3,$B$15,$E$15,1) =G3*$D$13 =NORMDIST(B4,$B$15,$E$15,1)-NORMDIST(B3,$B$15,$E=$1G54,*1)$D$13 =NORMDIST(B5,$B$15,$E$15,1)-NORMDIST(B4,$B$15,$E=$1G55,*1)$D$13 =NORMDIST(B6,$B$15,$E$15,1)-NORMDIST(B5,$B$15,$E=$1G56,*1)$D$13 =NORMDIST(B7,$B$15,$E$15,1)-NORMDIST(B6,$B$15,$E=$1G57,*1)$D$13 =NORMDIST(B8,$B$15,$E$15,1)-NORMDIST(B7,$B$15,$E=$1G58,*1)$D$13 =NORMDIST(B9,$B$15,$E$15,1)-NORMDIST(B8,$B$15,$E=$1G59,*1)$D$13 =NORMDIST(B10,$B$15,$E$15,1)-NORMDIST(B9,$B$15,$E=$G1150,1*)$D$13 =NORMDIST(B11,$B$15,$E$15,1)-NORMDIST(B10,$B$15,$=EG$1115*,1$)D$13 =1-NORMDIST(B11,$B$15,$E$15,1) =G12*$D$13 =SUM(G3:G12) =SUM(H3:H12) =SQRT((F13-E13^2/D13)/(D13-1))

正态分布拟合优度检验(续) 卡方分量(f-7)2/T 卡方统计量13 (SUM (D3: D5)-SUM(H3: H5))2/SUM (H3: H5) 自由度= COUNT(3122 (H6-D6)2/H6 检验水准a0.05 =(H7-D7)2/H7 P值= CHIDIST(K4K5) =(H8-D8)2/H8 临界值= CHIINV(K6K5 =(H10-D10)2/H10 SUMD11 D12)-SUM(HIl: HI2)2/SUM(HIl: H12) =SUM(3112)

正态分布拟合优度检验（续） 卡方分量(f -T )2 /T =(SUM(D3:D5)-SUM(H3:H5))^2/SUM(H3:H5) 卡方统计量 =I13 自由度 =COUNT(I3:I12)-2 =(H6-D6)^2/H6 检验水准α 0.05 =(H7-D7)^2/H7 P值 =CHIDIST(K4,K5) =(H8-D8)^2/H8 临界值 =CHIINV(K6,K5) =(H9-D9)^2/H9 =(H10-D10)^2/H10 =(SUM(D11:D12)-SUM(H11:H12))^2/SUM(H11:H12) =SUM(I3:I12)

二项分布拟合优度检验 每户发病人数观察家庭数发病人二项分布概率理论频数卡方分量 A 数 P(X=x) A-T)IT 167 00.43954312661290卡方统计量 01234 51040l118115.536.04 自由度 0.137270 39.5 277检验水准a 0.020878 6.0 29.29 P值 3 0.001191 0.3 临界值 28821410000028808100 发病率n0.185763889

二项分布拟合优度检验 每户发病人数 X 观察家庭数 A 发病人 数 二项分布概率 P(X =x ) 理论频数 T 卡方分量 (A -T ) 2 /T 0 167 0 0.439543 126.6 12.90 卡方统计量 1 51 51 0.401118 115.5 36.04 自由度 2 50 100 0.137270 39.5 2.77 检验水准α 3 17 51 0.020878 6.0 29.29 P值 4 3 12 0.001191 0.3 临界值 合计 288 214 1.000000 288.0 81.00 发病率π 0.185763889

81.00 0.05 3E-18 9915

81.00 2 0.05 3E-18 5.9915

二项分布拟合优度检验 人数观庭数发病人数二项分布概率PX=)理论频数r卡方分量(4-7r =A3*B3 0123 =BINOMDIST(A3, SA$7, $BS10,0)=D3*SBS8 =(B3-B3y2/E3 =A4*B4 =BINOMDIST(A4, SAS7, SBS10,0)=D4"SBS8 =(B4-E4r2/E4 =A5"B5 =BINOMDIST(A5, SA$7, $BS10,0)=D5*SBS8 =(B5-E5M2/E5 17 =A6 B6 =BINOMDIST(A6, SAS7, $BS10,0)=D6*SBS8 金计uMB =BINOMDIST(A7,SAS7, $BS10,0)=D7*SB$8 =(B6+B7-E6-E7r2/(E6+E7) B7=SUM(C3 C7)=SUMD3: D7) =SUM(E3: E7)=SUMF3: F7 发病率=C8/(B8*4)

每户 二项分布拟合优度检验 发病 人数 X 观察家庭数 A 发病人数 二项分布概率P(X =x) 理论频数T 卡方分量(A -T ) 2 /T 0 167 =A3*B3 =BINOMDIST(A3,$A$7,$B$10,0) =D3*$B$8 =(B3-E3)^2/E3 1 51 =A4*B4 =BINOMDIST(A4,$A$7,$B$10,0) =D4*$B$8 =(B4-E4)^2/E4 2 50 =A5*B5 =BINOMDIST(A5,$A$7,$B$10,0) =D5*$B$8 =(B5-E5)^2/E5 3 17 =A6*B6 =BINOMDIST(A6,$A$7,$B$10,0) =D6*$B$8 4 3 =A7*B7 =BINOMDIST(A7,$A$7,$B$10,0) =D7*$B$8 =(B6+B7-E6-E7)^2/(E6+E7) 合计 =SUM(B3:B7) =SUM(C3:C7) =SUM(D3:D7) =SUM(E3:E7) =SUM(F3:F7) 发病率=πC8/(B8*4)

卡方统 计量=F8 自由度= COUNT(F3:F7)-2 验水准a0.05 P值= CHIDIST(H3,H4) 临界值= CHIINV(H5,H4)

卡方统 计量 =F8 自由度 =COUNT(F3:F7)-2 检验水准α 0.05 P值 =CHIDIST(H3,H4) 临界值 =CHIINV(H5,H4)