用Excel作统计分析

1、 实训五实训五 用用Excel作统计分析作统计分析 如何运用如何运用 Excel 作统计图（直方图）作统计图（直方图） 下列数据是80个工人的月收入（单位：元），绘制直方图， 观察月收入的分布状况。 10271302157316581705179419202040 10501356157816651712180019342070 11091435157916701730183019402076 11541452160016701750185419582140 12001452163016781753186019652143 12351543163016791758187019732270 12

2、371543164516901779187019872300 12601546164916901780187019982354 12801560165216991784189020002478 12981570165217001793189320382595 1、数据处理、数据处理 注意：对任意数据绘制直方图时，首先必须进行简单 的数据处理，即对数据进行排序，分组，找分界点。 具体表现为： （1）组数的确定，为数据总量（80个），可计算拟分 为8组 （2）组距的确定，为数据最大值与最小值 （3）结合组数和组距以及原始数据确定各组的起始点， 即为 第一组1200以下，第二组12001400，依次

3、类推，第八 组为2400以上。 （4）在Excel空白区域输入各组分界点，依次为， 1199.99，1399.99，1599.99，1799.99，1999.99， 2199.99，2399.99，即区域B2:B8。 2、操作步骤、操作步骤 （1）、输入原始数据（区域A1:A80，B2:B8），并对 该数据排序。 操作方法：EXCEL数据排序。 （2）绘直方图操作过程： 第一步：选择第一步：选择“工具工具”“数据分析数据分析”，见下图：，见下图： 第二步：选择第二步：选择“数据分析数据分析”“直方图直方图”，见下图，见下图 第三步：在第三步：在“直方图直方图”字对话框依次执行下列操作：字对话框

4、依次执行下列操作： 在“输入区域”用鼠标点击A1:A80得到如图所示结果； 在“分组区域”用鼠标点击B2:B8得到如图所示结果； 在“输出选项”子菜单下面，选择“图表输出”可得到直方图， 选 择“新工作簿”表示将频数分布表存放在新的工作表中，也可选 择“输出区域”存放在你认为合适的地方 3、操作结果、操作结果 完成上述操作后，EXCEL会相应输出下列操作结果。 （1）表格：）表格： 根据上述操作可得到下列表格，左图为Excel原始输出， 右图为对应的含义解释。 接收频率 1199.994 1399.998 1599.9911 1799.9928 1999.9917 2199.997 2399.

5、993 其他2 按月收入分组频率 1200以下4 120014008 1400160011 1600180028 1800200017 200022007 220024003 2400以上2 (2）直方图：（）直方图：（调整后如下图 ） 二、如何运用二、如何运用Excel计算描述统计量：计算描述统计量： 包括平均数、众数、中位数、方差、偏度、峰度等包括平均数、众数、中位数、方差、偏度、峰度等 下面是某保险公司160名员工的某月保险销售额，（单位：千元）要 求计算相关描述统计量，了解该公司员工的保险销售额的情况 8.414.2615.7616.9817.3917.6124.226.51 8.81

6、14.5215.7717.0717.4117.6124.2626.7 9.2414.6115.8217.1417.4317.6224.826.74 9.6414.7815.8417.1617.4317.6625.0526.93 11.0514.8715.8617.1617.4817.725.2927.5 11.4314.9215.8817.2517.4817.7525.427.76 12.3715.0815.8817.2517.5217.7525.5331.16 12.7415.1315.9917.2517.5717.7925.6431.34 12.9615.1516.1617.3117.57

7、17.8425.8332.4 13.2215.2716.2217.3417.5717.926.0732.55 13.2515.3417.9318.4218.9621.0921.5322.65 13.3315.3717.9318.5119.0521.1621.6422.84 13.5115.4217.9618.5519.2521.1621.6623.16 13.6115.4317.9718.6319.2721.1921.7223.27 13.6115.4618.0218.6419.3521.2521.8123.4 13.6815.4818.2218.6419.4521.3121.8823.45

8、13.815.5618.2418.6919.4621.3421.9223.5 13.8515.6418.2818.7319.5521.422.1323.65 13.9715.6518.2918.7819.9121.4222.2823.76 14.1615.7118.3318.9421.0321.5322.3823.94 1、 操作步骤操作步骤 第一步：输入原始数据（A1：A160），并进入描述统计菜单，操作步骤 如下： Excel工具数据分析描述统计 第二步：在“描述统计”子菜单下面作如下选择： “输入区域”：选择A1：A160，表示对该数据作描述统计分析。 “输入区域”： 选择“汇总统计”，

9、表示输出各种描述统计量，包括平均数、 众数、中 位数、方差、 偏度、峰度等各个描述统计量。 选择“新工作簿”，表示将描述统计分析结果存放在新的工作表格中。 2、操作结果、操作结果 完成上述操作步骤后，Excel相应输出下列分析结果 统计量计算结果 平均18.77194 标准误差0.354332 中位数17.915 众数17.25 标准差4.48198 方差20.08814 峰度0.678236 偏度0.608222 区域24.15 最小值8.4 最大值32.55 求和3003.51 观测数160 三、如何运用三、如何运用Excel作区间估计作区间估计 现有一大批糖果，先从重随机地取16袋，称得

10、重量 （以克计）如下： 506 508 499 503 504 510 497 512 514 505 493 496， 设袋装糖果重量近似服从正态分布，试求总 体均值的置信度为0.95的置信区间 1、操作目标、操作目标 要进行区间估计，需构造形如下图的工作表：其中，左边A5：A16是变 量名，右边B5：B16是变量相应的计算公式，输入样本数据F2：F17。 A列“样本统计量”包含三个，依次为样本个数、样本均值、样本标准 差，B列给出计算三个样本统计量的计算公式（或称函数）； A列“用户输入”，输入进行区间估计的置信水平，通常为0.95； A列“计算结果”，下面包含进行区间估计所需的六个量：

11、抽样标准误 差、自由度、t值、置信区间半径、置信区间下限、置信区间上限。B列 为各自对应计算公式 注：若事先构造好下表，那么任意数据进行区间，只要输入数据，直接 可以得到区间估计的结果。 2、操作步骤、操作步骤 第一步：在Excel表格中输入上图中A4：A16和F1：F17的内容。 第二步：名称定义。 （1）、进行变量定义，将A列的变量名称定义为B列各公式计算 结果的变量名。 方法：选定A5：B7和A9：B9，A11：B16（先用鼠标选择第一部 分，再按住CTRL键选择另外两个部分），然后选择“插入” “名称”“指定”“最左列”“确定”。如下图： （2）、为样本数据进行名称定义，即将F2：F1

12、7的数据定义为“样本数据”。 方法：“插入”“名称”“指定 ”“首行”“确定” 第三步：进行区间估计。依次在B列输入A列对应位置变量的计 算公式，回车可得到各个变量值。 1、计算“样本个数”，在B5内编辑计算公式：“=count(F2： F16)”（点中F2：F16），回车即可得到样本个数计算值16。 2、计算“样本均值”，在B6内编辑计算公式：“=average(F2：F16)” （点中F2：F16），回车即可得到样本均值计算值503.75 3、计算“样本标准差”，在B7内编辑计算公式：“=stdev(F2： F16)”（点中F2：F16），回车即可得到样本标准差6.202。 4、“置信水平

13、”，由用户自行输入，一般为0.9 5、“抽样标准误差”，在B11内编辑计算公式：“=样本标准差/sqrt(样本个数)” （方法：输入等于号，点中B7,输入“/”和函数“sqrt”，点中B5，回车即可得到 抽样标准差1.55。 6、“自由度”，在B12内编辑计算公式：“=样本个数-1”,具体公式 编辑方法同（5），回车可得自由度值为15。（或者不需要公式，直 接填入自由度值） 7、“t值”，在B13内编辑计算公式：“=tinv(1-B9,B12) ”, 具体公式编辑方法同（5），回车t值为2.1314. 8、 “置信区间半径”，在B14内编辑计算公式：“=B13*B11 ”， 具体公式编辑方法同

14、（5），回车，可得值为3.304 9、“置信区间上限”，在B15内编辑计算公式：“=B6+B14 ”，具体 公式编辑方法同（5），回车，可得值为507.0549 10、“置信区间下限”，在B15内编辑计算公式：“=B6-B14 ”，具 体公式编辑方法同（5），回车值为500.4451 经过上述步骤可得区间估计的结果，上述过程看似复杂，实际上 只要构造好一个区间估计的固定工作表，对任意数据进行区间估 计，只要代入数据即可。 五、如何运用五、如何运用Excel作假设检验作假设检验 某车间用一台包装机包装葡萄糖，包得的袋装葡萄糖 重量是一个随机变量，它服从正态分布。当机器正常 时，其均值为0.5公斤

15、，标准差为0.015公斤。某日开工 后为检验包装机是否正常，随机的抽取所包装的糖9袋， 称得净重为 0.497 0.506 0.518 0.524 0.498 0.511 0.520 0.515 0.512 （公斤） 问：机器是否正常？(总体标准差稳定） 1、操作目标：构造形如下图的工作表：、操作目标：构造形如下图的工作表： 要进行假设检验，需构造形如下图的工作表： 其中，左边A5：A16是变量名，右边B5：B16是变量相应的计算公式，输入样本 数据E2：E10 A列“样本统计量”包含两个，依次为样本个数、样本均值，B列给出计算个样本 统计量的计算公式（或称函数）； A列“用户输入”，输入进行

16、假设检验的置信水平，通常为0.95，总体标准差和总 体均值假设值（通常为已知条件）； A列“计算结果”，下面包含进行区间估计所需的两个量： 抽样标准误差、计算Z 值 后面依次为单侧检验和双侧检验，由你自行选择。 注：若事先构造好下表，那么任意数据进行假设检验，只要输入数据，直接可以 得到假设检验的结果。 2、操作方法：、操作方法： 第一步：在Excel表格中输入上图中A2：A19和E1：E10的内容 第二步：名称定义。（方法基本上相同于区间估计） （1）进行变量定义，将A列的名称定义为B列各个公式计算结果的变量名。 选定A3：B4和A6：B8，A10：B11，A13：B15，A17：B19（先

17、用鼠标 选择第一部分，再按住CTRL键选择另外两个部分），然后选择“插入” 名称指定最左列确定。 （2）为样本数据定义。如图“插入”“名称”“指定 ”“首 行”，此操作是将E2：E10的数据名称定义为“样本数据” 第三步：计算 依次在B列输入计算公式，形如下图，依次可得到各个函数值， (1)、计算“样本个数”，在B3内编辑计算公式：“=count(E2：E10)” （点中E2：E10），回车即可得到样本个数计算值9。 （2）计算样本均值，在B4内编辑计算公式：“=average(E2：E10)” （点中E2：E10），回车即可得到样本均值计算值0.511 （3）、输入假设检验所需要的参数，包括

18、置信水平0.95，总体标准 差0.015，总体均值假设值0.5 （在进行假设检验时，这些参数值一 般已知） （4）、“抽样标准误差”，在B10内编辑公式，“=B7/B3”，回车可得 0.002 （5）“计算Z值”，在B11内编辑公式，“=NORMSINV（1-B6）”， 回车可得-1.64 （6）进行双侧检验，“双侧Z值”，在B16内编辑公式， “=NORMSINV(1-B6)” “检验结果”，在B18内编辑公式，“=IF（ABS（B11）ABS(B17),” 拒绝H0” , 接受H0”）形如下图，输入该公式后，回车即为检验结果 六、如何运用六、如何运用Excel作方差分析作方差分析 随机选取

19、26位被访者，了解他们对安乐死的赞同程度。然后按不同血型进行 分组，资料如下表。在显著性水平为0.05时进行方差分析，判断不同血型的人 对安乐死的态度是否有显著差异性？ 现假设已把数据输入到Excel表格中，区域A2:D11 OAABAB 80807392 78778086 94699270 73847685 795268 8640 9173 75 81 64 （1） 操作步骤操作步骤： 第一步：选择“工具”下拉菜单，然后选择“数据分析”选项 。 第二步：在分析工具中选择“单因素方差分析”。如下图： 第三步：当出现对话框后 ，在“输入区域”方框内键入A2：D11 （直接让鼠标选中所要分析的数据

20、即可）；在方框内输入0.05；在 “输出选项”中选择输出区域为（可以任意指定空白区域），若选 择“新工作表组”，方差分析结果将在另一表格中呈现；最后选择 “确定”。 （2）输出结果：）输出结果： 完成上述操作步骤后，EXCEL输出下列分析结果： 表一：样本数据描述统计 组计数求和平均方差 列 11080180.176.54444 列 2747567.85714257.8095 列 3538977.882.2 列 4433383.2587.58333 表二：方差分析结果 差异源SSdfMSFP-valueF crit 组间840.07753280.02582.1789530.1192323.04

21、9124 组内2827.30722128.514 总计3667.38525 从上表中可以看出， 049124. 3)22, 3(178953. 2 05. 0 FF ， 所以说明不同血型的人对安乐死的态度没有显著差异。 七、如何运用七、如何运用Excel作相关分析作相关分析 下表为年龄（X）与脂肪百分比 （Y）的数据，试计算两者的相 关系数。 年龄2323272739414549 脂肪的百 分比9.527.97.817.831.425.927.425.2 (1)、操作步骤：、操作步骤： 第一步：选择“工具”“数据分析”“相关系数”“确定” 第二步：在“相关系数”子对话框中，作如下选择： “输入区域”：选中原数据即可，如图所示。“输出选项”：若选 择“输出区域”（任意选定空白区域即可，则相关系数计算结果显 示在该区域。若选则“新工作表”，则相关系数显示在该工作表中。 最后选择“确定”进行相关分析。 （2）结果：）结果： 完成上述步骤后，显示结果如下：