EXCEL数据分析工具解释.xls

《EXCEL数据分析工具解释.xls》由会员分享，可在线阅读，更多相关《EXCEL数据分析工具解释.xls（161页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、操操作作使使用用说说明明“描描述述统统计计”分分析析工工具具的的操操作作使使用用1）设置数据区域，并输入数据。如本例J5：K18为数据区。2）选取“工具”栏中“数据分析”。3）选定“描述统计”。4)“描述统计”对话框5)输入区域，输入待分析数据区域的单元格引用。本例为J5：K18。6）分组方式：指出输入区域中的数据是按行还是按列排列，请单击“逐行”或“逐列”。7）输入区域的第一行中包含标志项，选中“标志位于第一行”复选框；8）选择有关描述统计复选框。9）在“输出区域”框输入A14。10）再选择“确定”按钮。11）有关统计数据情况的结果显示如下：克山病健康人平均1.520909091 平均1.0

2、84615385标准误差0.127175287 标准误差0.117083224中值1.53 中值1.16模式1.2 模式0.64标准偏差0.421792711 标准偏差0.422149566样本方差0.177909091 样本方差0.178210256峰值-1.164078503 峰值-1.058147989偏斜度-0.082875528 偏斜度0.342063813区域1.27 区域1.33最小值0.84 最小值0.54最大值2.11 最大值1.87求和16.73 求和14.1计数11 计数13最大(1)2.11 最大(1)1.87最小(1)0.84 最小(1)0.54置信度(95.0%)0

3、.283364248 置信度(95.0%)0.255102425AB平均35.8 平均28标准误差3.401960219 标准误差1.390443574中值35.5 中值28模式#N/A模式27标准偏差10.7579428 标准偏差4.396968653样本方差115.7333333 样本方差19.33333333峰值-0.98519298 峰值0.036563615偏斜度0.094266434 偏斜度-0.333301726区域33 区域15最小值20 最小值20最大值53 最大值35求和358 求和280计数10 计数10置信度(95.0%)7.695774543 置信度(95.0%)3.

4、145404288某市1982年110名7岁男童的身高（CM）资料如下:实实际际应应用用：某克山病区测得11例克山病患者与13名健康人的血磷值（mmol/L）如下，请对两样本有关统计数据情况进行描述。克山病健康人0.840.541.050.641.20.641.20.751.390.761.530.811.671.161.81.21.871.342.071.352.111.481.561.87从A、B两个总体内各取一个随机样本，其含量都是10，结果如下，请对两样本有关统计数据情况进行描述。AB3227233048324126203129275323392930354320某市1982年110名

5、7岁男童的身高（CM）资料如下:112.4120.3122.5126.1120123.5119.8121.5130.5129.1119.7128120114.9116.3119.8126.6117.1125.2112.4120.2119112.2120.1126.2124.4124.4122.8117.4122.4112.3118.4120.7123.1127.1117.9122.8114.3120.5108.2123.8120.5119.5118.9119.6116.3123.2118.1113.2122116.8118.2120117129.3117.2114.8122.5121.8114

6、.3118.3114.3112.7124.1113.2116.8124.1117.2116121122.9121.5120.4115.4113120.8118.4121.2120.8125.2116.4125.8114.8118119.3114.9118.5120.7125132.5120.1122.1111.5116122.7119.1110.3130.8108.2116.2125.1120.6113119.7119.7125.6123114.7120.7117.7列1列2列3列4列5平均119.2864 平均120.6636 平均118.5545 平均119.6636 平均标准误差 0.8

7、15948 标准误差0.89175 标准误差0.97474 标准误差 1.054542 标准误差中值119.4 中值120.55 中值119.75 中值119.9 中值模式116.8 模式118.4 模式122.5 模式116 模式标准偏差 3.827133 标准偏差 4.182679 标准偏差 4.571936 标准偏差 4.946239 标准偏差样本方差 14.64695 样本方差 17.49481 样本方差20.9026 样本方差 24.46528 样本方差峰值-0.65221 峰值0.608647 峰值0.940662 峰值0.555917 峰值偏斜度-0.04231 偏斜度-0.53

8、781 偏斜度0.515217 偏斜度-0.04085 偏斜度区域13.3 区域17.7 区域19.3 区域22.3 区域最小值112.3 最小值110.3 最小值111.5 最小值108.2 最小值最大值125.6 最大值128 最大值130.8 最大值130.5 最大值求和2624.3 求和2654.6 求和2608.2 求和2632.6 求和计数22 计数22 计数22 计数22 计数最大(1)125.6 最大(1)128 最大(1)130.8 最大(1)130.5 最大(1)最小(1)112.3 最小(1)110.3 最小(1)111.5 最小(1)108.2 最小(1)置信度(95.

9、0%)1.696856 置信度(95.0%)1.854496 置信度(95.0%)2.027083 置信度(95.0%)2.19304 置信度(95.0%)120.33641.328908119.85#N/A6.23313338.85195-0.489660.09160324.3108.2132.52647.422132.5108.22.763617操操作作使使用用说说明明“Z-检检验验：双双样样本本均均值值分分析析”分分析析工工具具的的操操作作使使用用1）设置数据区域，并输入数据。如本例J5：J55为数据区1，K5：K55为数据区2。2）在“工具”菜单中，单击“数据分析”命令。3）在“分析工

10、具”列表框中，单击“Z-检验：双样本均值分析”。4）选择“确定”按钮。5）在“变量1的的区域”输入J5：J55。6）在“变量2的的区域”输入K5：K55。7）计算两样本组方差，J56=VARP(J6:J55)；K56=VARP(K6:K55)。8）将187.6196输入变量1的方差的数据框内，将196.57输入变量2的方差的数据框内。9）标志：输入区域的第一行或第一列中包含标志项，选中此复选框；10）：在此输入检验的置信度，0.05或0.01。11）在“输出区域”框输入A16。12）再选择“确定”按钮。13）有关统计数据情况的结果显示如下：z-检验:双样本均值分析断续服药 坚持服药平均116.

11、98100.7已知协方差 187.6196196.57观测值5050假设平均差0z5.873089P(Z=z)单尾2.15E-09z 单尾临界1.644853P(Z=z)双尾4.29E-09z 双尾临界1.959961实实际际应应用用：有二组高血压病人，发病一年来，一组病人坚持服药治疗，另一组病人断续不规则服药治疗，现测得收缩压如下，请问两组病人收缩压差别有无显著性？断续服药 坚持服药1109014612011096113110115116909310973129103133101110901008610890133131134133129108110901261101161081121031

12、0987108881101081109813011011092125105150120130116110104116909884100741401261168611611098781048611610011410011612016011410290110104122108120881128412410011611012011210492187.6196196.57操操作作使使用用说说明明“F-检检验验：双双样样本本方方差差分分析析”分分析析工工具具的的操操作作使使用用1）设置数据区域，并输入数据。如本例J5：J16为数据区1，K5：K18为数据区2。2）在“工具”菜单中，单击“数据分析”命令。

13、3）在“分析工具”列表框中，单击“F-检验：双样本方差分析”。4）选择“确定”按钮。5）在“变量1的的区域”输入J5：J16。6）在“变量2的的区域”输入K5：K18。7）标志：输入区域的第一行或第一列中包含标志项，选中此复选框；8）：在此输入检验的置信度，0.05或0.01。9）在“输出区域”框输入A14。10）再选择“确定”按钮。11）有关统计数据情况的结果显示如下：F-检验 双样本方差分析克山病健康人平均1.5209090911.084615385方差0.1779090910.178210256观测值1113df1012F0.998310055P(F=f)单尾0.506319293F 单

14、尾临界0.343291617F-检验 双样本方差分析AB平均35.828方差115.733333319.33333333观测值1010df99F5.986206897P(F=f)单尾0.006819566F 单尾临界3.178897146实实际际应应用用：某克山病区测得11例克山病患者与13名健康人的血磷值（mmol/L）如下，请对两样本方差齐性检验。克山病健康人0.840.541.050.641.20.641.20.751.390.761.530.811.671.161.81.21.871.342.071.352.111.481.561.87从A、B两个总体内各取一个随机样本，其含量都是10

15、，结果如下，试作两样本方差齐性检验。AB3227233048324126203129275323392930354320操操作作使使用用说说明明“t-检检验验：双双样样本本等等方方差差假假设设”分分析析工工具具的的操操作作使使用用1）设置数据区域，并输入数据。如本例J5：J16为数据区1，K5：K18为数据区2。2）选取“工具”栏中“数据分析”。3）选定“t-检验：双样本等方差假设”。4）选择“确定”按钮。5）在“变量1的的区域”输入J5：J16。6）在“变量2的的区域”输入K5：K18。7）标志：输入区域的第一行或第一列中包含标志项，选中此复选框；8）：在此输入检验的置信度，0.05或0.0

16、1。9）在“输出区域”框输入A14。10）再选择“确定”按钮。11）有关两个样本等方差t-检验结果显示如下：t-检验:双样本等方差假设患 者健康人平均1.5209090911.084615385方差0.1779090910.178210256观测值1113合并方差0.178073363假设平均差0df22t Stat2.523724046P(T=t)单尾0.00966852t 单尾临界1.717144187P(T=t)双尾0.01933704t 双尾临界2.073875294“t-检检验验：成成对对双双样样本本均均值值分分析析”分分析析工工具具的的操操作作使使用用1）设置数据区域，并输入数据。

17、如本例J33:J49为数据区1，K33:J49为数据区2。2）选取“工具”栏中“数据分析”。3）选定“t-检验：成对双样本均值分析”。4）选择“确定”按钮。5）在“变量1的的区域”输入J33:J49。6）在“变量2的的区域”输入K33:J49。7）在“输出区域”框输入A43。8）打开“标记”复选框。9）在临界值“”框输入0.0510）“假设的平均数差”为0，输入0，表示假设两样本平均数相等。11）再选择“确定”按钮。12）有关成对双样本均值的t-检验结果显示如下：t-检验:成对双样本均值分析冷消化法热消化法平均2.89752.82875方差9.2089133337.790918333观测值16

18、16泊松相关系数0.993321996假设平均差0df15t Stat0.662368626P(T=t)单尾0.258891705t 单尾临界1.753051038P(T=t)双尾0.517783411t 双尾临界2.131450856“t-检检验验：双双样样本本异异方方差差假假设设”分分析析工工具具的的操操作作使使用用1）设置数据区域，并输入数据。如本例J63:J72为数据区1，K63:K72为数据区2。2）选取“工具”栏中“数据分析”。3）选定“t-检验：双样本异方差假设”。4）选择“确定”按钮。5）在“变量1的的区域”输入J63:J72。6）在“变量2的的区域”输入K63:K72。7）在

19、“输出区域”框输入A72。8）打开“标记”复选框。9）在临界值“”框输入0.0510）“假设的平均数差”为0，输入0，表示假设两样本平均数相等。11）再选择“确定”按钮。12）有关双样本异方差假设的t-检验结果显示如下：t-检验:双样本异方差假设AB平均35.828方差115.733333319.33333333观测值1010假设平均差0df12t Stat2.122367134P(T=t)单尾0.027650742t 单尾临界1.782286745P(T=t)双尾0.055301484t 双尾临界2.178812792实实际际应应用用：某克山病区测得11例克山病患者与13名健康人的血磷值（m

20、mol/L）如下，问该地急性克山病患者与健康人的血磷值是否不同？患 者健康人0.840.541.050.641.20.641.20.751.390.761.530.811.671.161.81.21.871.342.071.352.111.481.561.87尿铅测定长期以来用热消化法，后改用冷消化法，试就表中资料，说明两法测得结果有无差别？冷消化法 热消化法2.412.812.0711.242.93.041.641.832.751.881.061.453.233.430.770.923.673.810.370.374.494.010.190.295.164.440.160.275.455.4

21、10.040.07从A、B两个总体内各取一个随机样本，其含量都是10，结果如下。比较两个均数，AB3227233048324126203129275323392930354320操操作作使使用用说说明明“Anova：单单因因素素方方差差分分析析”分分析析工工具具的的操操作作使使用用1）设置数据区域，并输入数据。2）选取“工具”栏中“数据分析”。3）选定“Anova：单因素方差分析”。4）选择“确定”按钮。5）在“输入区域”框输入J6：L17。6）在“分组方式”框选择“逐列”，即样本数据按列分组。7）打开“标志位于第一行”复选框。8）在显著性检验水平“”框输入0.059）在“输出区域”框输入A1

22、4。10）再选择“确定”按钮。11）有关单因素方差分析检验结果显示如下：方差分析：单因素方差分析SUMMARY组计数求和平均方差石棉患者1119.7 1.790909 0.040909可疑患者920.8 2.311111 0.033611非患者1133.9 3.081818 0.085636方差分析差异源SSdfMSFP-valueF crit组间9.2656572 4.632828 84.54378 1.36E-12 3.340389组内1.53434328 0.054798总计10.830方差分析：单因素方差分析SUMMARY组计数求和平均方差棉布512.322.4640.13478府绸5

23、12.062.4120.03092的确凉514.842.9680.03032尼龙520.144.0280.81127方差分析差异源SSdfMSFP-valueF crit组间8.433763 2.811253 11.16363 0.000336 3.238867组内4.0291616 0.251822总计12.4629219实实际际应应用用：三样本成组方差分析 某职业病防治院对31名石棉矿工中的石棉肺患者、可疑患者及非患者进行了用力肺活量（L）测定，结果见表5.1，问三组石棉矿工的用力肺活量有无差别？石棉患者 可疑患者非患者1.82.32.91.42.13.21.52.12.72.12.12.

24、81.92.62.71.72.531.82.33.41.92.431.82.43.41.83.323.5四样本成组方差分析本院劳动卫生学教研组研究棉布、府绸、的确凉、尼龙四种衣料内棉花吸附+硼氢量（）。每种衣料各做五次检验，结果见表6-1。试检验各种衣料间棉花吸附+硼氢量有没有显著差别。棉布府绸的确凉尼龙2.332.483.06422.343.065.132.932.6834.612.732.342.662.82.332.223.063.61010.047.56119.657.21129.336.93139.076.70148.866.51158.686.36168.536.23178.406

25、.11188.296.01198.185.93208.105.85218.025.78227.955.72237.885.66247.825.61257.775.57267.725.53277.685.49287.645.45297.605.42307.565.39327.505.34 1 1 成成组组设设计计方方差差分分析析甲甲组组样样本本乙乙组组样样本本1 11.82.32 21.42.13 31.52.14 42.12.15 51.92.66 61.72.57 71.82.38 81.92.49 91.82.410101.81111212121313合合计计19.720.833.9均均数

26、数1.790909 2.311111 3.081818样样本本数数11911合合计计均均方方35.6948.34105.33方方差差:0.040909 0.033611 0.085636分分子子=2.368816分分母母=卡卡方方值值=2.2598592.259859C C值值=结结果果：方方差差齐齐性性好好成成组组设设计计方方差差分分析析结结果果变变异异耒耒源源SSSSv vMSMS总总10.830组组间间9.2656572 24.632828组组内内1.53434328280.054798v1=v1=3v2=v2=28f f值值0.050.05:3.3403893.340389f f值值0

27、.010.01:P P 0.010.01结结论论P=P=1.36E-121.36E-12各各组组均均数数样样本本数数对对比比组组两两均均数数差差A与BXa-Xb3.081818111组/3组1.2909092.31111191组/2组0.7707071.790909112组/3组0.520202a1=a1=3a2=a2=20.050.05q q界界值值（1 1组组/3/3组组）：3.583.58Q Q界界值值（1 1组组/2/2组组）：2.952.95Q Q界界值值（2 2组组/3/3组组）：2.952.95 P P 值值（1 1组组/3/3组组）：0.010.01 P P 值值（1 1组组/

28、2/2组组）：0.010.01 P P 值值（2 2组组/3/3组组）：0.010.01F F界界值值表表（P=0.05)P=0.05)分母自由度1231161.45199.50215.71218.5119.0019.16310.139.559.2847.716.946.5956.615.795.4165.995.144.7675.594.744.3585.324.464.0795.124.263.86104.964.103.71114.843.983.59124.753.893.49134.673.813.41144.603.743.34154.543.683.29164.493.633.2

29、4174.453.593.20184.413.553.16194.383.523.13204.353.493.10214.323.473.07224.303.443.05234.283.423.03244.263.403.01254.243.392.99264.233.372.98274.213.352.96284.203.342.95294.183.332.93304.173.322.92324.153.292.90344.133.282.88364.113.262.87384.103.242.85404.083.232.84424.073.222.83444.063.212.82464.0

30、53.202.81484.043.192.80504.033.182.79604.003.152.76703.983.132.74803.963.112.721003.943.092.701253.923.072.681503.903.062.662003.893.042.654003.863.022.6310003.853.002.61999999993.843.002.60F F界界值值表表（P=0.01)P=0.01)分母自由度12314052.184999.345403.53298.5099.0099.16334.1230.8229.46421.2018.0016.69516.2613

31、.2712.06613.7510.929.78712.259.558.45811.268.657.59910.568.026.996.555.995.645.395.205.064.944.854.776.225.675.325.074.894.744.634.544.465.955.415.064.824.644.504.394.304.225.745.214.864.624.444.304.194.104.025.565.044.694.464.284.144.033.943.865.424.894.564.324.144.003.893.803.735.294.774.444.204.0

32、33.893.783.693.625.194.674.344.103.933.793.683.593.525.094.584.254.013.843.713.603.513.435.014.504.173.943.773.633.523.433.364.944.434.103.873.703.563.463.373.294.874.374.043.813.643.513.403.313.244.824.313.993.763.593.453.353.263.184.764.263.943.713.543.413.303.213.144.724.223.903.673.503.363.263.1

33、73.094.684.183.853.633.463.323.223.133.064.644.143.823.593.423.293.183.093.024.604.113.783.563.393.263.153.062.994.574.073.753.533.363.233.123.032.964.544.043.733.503.333.203.093.002.934.514.023.703.473.303.173.072.982.914.463.973.653.433.263.133.022.932.86例例题题丙丙组组样样本本2.93.22.72.82.733.433.43.33.574

34、.42.39461331189.360.0547981.048214178.56F FP PP P84.5437884.543780.010.011.36E-121.36E-125.4529325.452932组组距距a a q q 值值 q q 界界 值值 P P 值值结结论论0.050.050.010.013 318.2898318.289833.583.584.644.640.010.012 210.3591610.359162.952.954.024.020.010.012 26.9920966.9920962.952.954.024.020.010.01a3=a3=2v=v=2828

35、0.010.014.644.644.024.024.024.02乙乙组组样样本本成成组组设设计计方方差差分分析析结结果果分子自由度456789101112224.58230.16233.99236.77238.88240.54241.88242.98243.9019.2519.3019.3319.3519.3719.3819.4019.4019.419.129.018.948.898.858.818.798.768.746.396.266.166.096.046.005.965.945.915.195.054.954.884.824.774.744.704.684.534.394.284.21

36、4.154.104.064.034.004.123.973.873.793.733.683.643.603.573.843.693.583.503.443.393.353.313.283.633.483.373.293.233.183.143.103.073.483.333.223.143.073.022.982.942.913.363.203.093.012.952.902.852.822.793.263.113.002.912.852.802.752.722.693.183.032.922.832.772.712.672.632.603.112.962.852.762.702.652.60

37、2.572.533.062.902.792.712.642.592.542.512.483.012.852.742.662.592.542.492.462.422.962.812.702.612.552.492.452.412.382.932.772.662.582.512.462.412.372.342.902.742.632.542.482.422.382.342.312.872.712.602.512.452.392.352.312.282.842.682.572.492.422.372.322.282.252.822.662.552.462.402.342.302.262.232.80

38、2.642.532.442.372.322.272.242.202.782.622.512.422.362.302.252.222.182.762.602.492.402.342.282.242.202.162.742.592.472.392.322.272.222.182.152.732.572.462.372.312.252.202.172.132.712.562.452.362.292.242.192.152.122.702.552.432.352.282.222.182.142.102.692.532.422.332.272.212.162.132.092.672.512.402.31

39、2.242.192.142.102.072.652.492.382.292.232.172.122.082.052.632.482.362.282.212.152.112.072.032.622.462.352.262.192.142.092.052.022.612.452.342.252.182.122.082.042.002.592.442.322.242.172.112.062.031.992.582.432.312.232.162.102.052.011.982.572.422.302.222.152.092.042.001.972.572.412.292.212.142.082.03

40、1.991.962.562.402.292.202.132.072.031.991.952.532.372.252.172.102.041.991.951.922.502.352.232.142.072.021.971.931.892.492.332.212.132.062.001.951.911.882.462.312.192.102.031.971.931.891.852.442.292.172.082.011.961.911.871.832.432.272.162.072.001.941.891.851.822.422.262.142.061.981.931.881.841.802.39

41、2.242.122.031.961.901.851.811.782.382.222.112.021.951.891.841.801.762.372.212.102.011.941.881.831.791.75分子自由度4567891011125624.265763.965858.955928.335980.956022.406055.936083.406106.6899.2599.3099.3399.3699.3899.3999.4099.4199.4228.7128.2427.9127.6727.4927.3427.2327.1327.0515.9815.5215.2114.9814.801

42、4.6614.5514.4514.3711.3910.9710.6710.4610.2910.1610.059.969.899.158.758.478.268.107.987.877.797.727.857.467.196.996.846.726.626.546.477.016.636.376.186.035.915.815.735.676.426.065.805.615.475.355.265.185.114.714.604.524.414.334.254.174.124.054.404.294.214.104.023.943.863.813.744.164.053.973.863.783.

43、703.623.573.503.963.863.783.663.593.513.433.383.313.803.703.623.513.433.353.273.223.153.673.563.493.373.293.213.133.083.013.553.453.373.263.183.103.022.972.903.463.353.273.163.083.002.922.872.803.373.273.193.083.002.922.842.782.713.303.193.123.002.922.842.762.712.643.233.133.052.942.862.782.692.642.

44、573.173.072.992.882.802.722.642.582.513.123.022.942.832.752.672.582.532.463.072.972.892.782.702.622.542.482.413.032.932.852.742.662.582.492.442.372.992.892.812.702.622.542.452.402.332.962.862.782.662.582.502.422.362.292.932.822.752.632.552.472.382.332.262.902.792.722.602.522.442.352.302.232.872.772.

45、692.572.492.412.332.272.202.842.742.662.552.472.392.302.252.172.802.702.622.502.422.342.252.202.12.1416202430405075100245.36246.47248.02249.05250.10251.14251.77252.62253.0419.4219.4319.4519.4519.4619.4719.4819.4819.498.718.698.668.648.628.598.588.568.555.875.845.805.775.755.725.705.685.664.644.604.5

46、64.534.504.464.444.424.413.963.923.873.843.813.773.753.733.713.533.493.443.413.383.343.323.293.273.243.203.153.123.083.043.022.992.973.032.992.942.902.862.832.802.772.762.862.832.772.742.702.662.642.602.592.742.702.652.612.572.532.512.472.462.642.602.542.512.472.432.402.372.352.552.512.462.422.382.3

47、42.312.282.262.482.442.392.352.312.272.242.212.192.422.382.332.292.252.202.182.142.122.372.332.282.242.192.152.122.092.072.332.292.232.192.152.102.082.042.022.292.252.192.152.112.062.042.001.982.262.212.162.112.072.032.001.961.942.222.182.122.082.041.991.971.931.912.202.162.102.052.011.961.941.901.8

48、82.172.132.072.031.981.941.911.871.852.152.112.052.011.961.911.881.841.822.132.092.031.981.941.891.861.821.802.112.072.011.961.921.871.841.801.782.092.051.991.951.901.851.821.781.762.082.041.971.931.881.841.811.761.742.062.021.961.911.871.821.791.751.732.052.011.941.901.851.811.771.731.712.041.991.9

49、31.891.841.791.761.721.702.011.971.911.861.821.771.741.691.671.991.951.891.841.801.751.711.671.651.981.931.871.821.781.731.691.651.621.961.921.851.811.761.711.681.631.611.951.901.841.791.741.691.661.611.591.941.891.831.781.731.681.651.601.571.921.881.811.771.721.671.631.591.561.911.871.801.761.711.6

50、51.621.571.551.901.861.791.751.701.641.611.561.541.891.851.781.741.691.631.601.551.521.861.821.751.701.651.591.561.511.481.841.791.721.671.621.571.531.481.451.821.771.701.651.601.541.511.451.431.791.751.681.631.571.521.481.421.391.771.731.661.601.551.491.451.401.361.761.711.641.591.541.481.441.381.3