用Excel作统计分析

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

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

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

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

5、8002818002000172000220072200240032400以上2 (2）直方图：（）直方图：（调整后如下图 ）二、如何运用二、如何运用Excel计算描述统计量：计算描述统计量：包括平均数、众数、中位数、方差、偏度、峰度等包括平均数、众数、中位数、方差、偏度、峰度等 下面是某保险公司160名员工的某月保险销售额，（单位：千元）要求计算相关描述统计量，了解该公司员工的保险销售额的情况8.414.2615.7616.9817.3917.6124.226.518.8114.5215.7717.0717.4117.6124.2626.79.2414.6115.8217.1417.4317

6、.6224.826.749.6414.7815.8417.1617.4317.6625.0526.9311.0514.8715.8617.1617.4817.725.2927.511.4314.9215.8817.2517.4817.7525.427.7612.3715.0815.8817.2517.5217.7525.5331.1612.7415.1315.9917.2517.5717.7925.6431.3412.9615.1516.1617.3117.5717.8425.8332.413.2215.2716.2217.3417.5717.926.0732.5513.2515.3417.9

7、318.4218.9621.0921.5322.6513.3315.3717.9318.5119.0521.1621.6422.8413.5115.4217.9618.5519.2521.1621.6623.1613.6115.4317.9718.6319.2721.1921.7223.2713.6115.4618.0218.6419.3521.2521.8123.413.6815.4818.2218.6419.4521.3121.8823.4513.815.5618.2418.6919.4621.3421.9223.513.8515.6418.2818.7319.5521.422.1323.

8、6513.9715.6518.2918.7819.9121.4222.2823.7614.1615.7118.3318.9421.0321.5322.3823.941、 操作步骤操作步骤 第一步：输入原始数据（A1：A160），并进入描述统计菜单，操作步骤如下：Excel工具数据分析描述统计 第二步：在“描述统计”子菜单下面作如下选择：“输入区域”：选择A1：A160，表示对该数据作描述统计分析。“输入区域”：选择“汇总统计”，表示输出各种描述统计量，包括平均数、 众数、中位数、方差、 偏度、峰度等各个描述统计量。选择“新工作簿”，表示将描述统计分析结果存放在新的工作表格中。2、操作结果、操作

9、结果 完成上述操作步骤后，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袋，称得重量（以克计）如下： 506 508 499 503 504 510 497 512 514 505 493496， 设袋装糖果重量近似服从正态分布，试求总体均值的置信度为0.95的置信区间 1、操作目

10、标、操作目标 要进行区间估计，需构造形如下图的工作表：其中，左边A5：A16是变量名，右边B5：B16是变量相应的计算公式，输入样本数据F2：F17。A列“样本统计量”包含三个，依次为样本个数、样本均值、样本标准差，B列给出计算三个样本统计量的计算公式（或称函数）；A列“用户输入”，输入进行区间估计的置信水平，通常为0.95；A列“计算结果”，下面包含进行区间估计所需的六个量： 抽样标准误差、自由度、t值、置信区间半径、置信区间下限、置信区间上限。B列为各自对应计算公式注：若事先构造好下表，那么任意数据进行区间，只要输入数据，直接可以得到区间估计的结果。2、操作步骤、操作步骤 第一步：在Exc

11、el表格中输入上图中A4：A16和F1：F17的内容。第二步：名称定义。（1）、进行变量定义，将A列的变量名称定义为B列各公式计算结果的变量名。 方法：选定A5：B7和A9：B9，A11：B16（先用鼠标选择第一部分，再按住CTRL键选择另外两个部分），然后选择“插入”“名称”“指定”“最左列”“确定”。如下图：（2）、为样本数据进行名称定义，即将F2：F17的数据定义为“样本数据”。方法：“插入”“名称”“指定 ”“首行”“确定” 第三步：进行区间估计。依次在B列输入A列对应位置变量的计算公式，回车可得到各个变量值。1、计算“样本个数”，在B5内编辑计算公式：“=count(F2：F16)”

12、（点中F2：F16），回车即可得到样本个数计算值16。 2、计算“样本均值”，在B6内编辑计算公式：“=average(F2：F16)”（点中F2：F16），回车即可得到样本均值计算值503.75 3、计算“样本标准差”，在B7内编辑计算公式：“=stdev(F2：F16)”（点中F2：F16），回车即可得到样本标准差6.202。4、“置信水平”，由用户自行输入，一般为0.9 5、“抽样标准误差”，在B11内编辑计算公式：“=样本标准差/sqrt(样本个数)”（方法：输入等于号，点中B7,输入“/”和函数“sqrt”，点中B5，回车即可得到抽样标准差1.55。6、“自由度”，在B12内编辑计算

13、公式：“=样本个数-1”,具体公式编辑方法同（5），回车可得自由度值为15。（或者不需要公式，直接填入自由度值）7、“t值”，在B13内编辑计算公式：“=tinv(1-B9,B12) ”,具体公式编辑方法同（5），回车t值为2.1314. 8、 “置信区间半径”，在B14内编辑计算公式：“=B13*B11 ”，具体公式编辑方法同（5），回车，可得值为3.3049、“置信区间上限”，在B15内编辑计算公式：“=B6+B14 ”，具体公式编辑方法同（5），回车，可得值为507.054910、“置信区间下限”，在B15内编辑计算公式：“=B6-B14 ”，具体公式编辑方法同（5），回车值为500.4

14、451 经过上述步骤可得区间估计的结果，上述过程看似复杂，实际上只要构造好一个区间估计的固定工作表，对任意数据进行区间估计，只要代入数据即可。 五、如何运用五、如何运用Excel作假设检验作假设检验 某车间用一台包装机包装葡萄糖，包得的袋装葡萄糖重量是一个随机变量，它服从正态分布。当机器正常时，其均值为0.5公斤，标准差为0.015公斤。某日开工后为检验包装机是否正常，随机的抽取所包装的糖9袋，称得净重为 0.497 0.506 0.518 0.524 0.498 0.511 0.520 0.515 0.512 （公斤） 问：机器是否正常？(总体标准差稳定）1、操作目标：构造形如下图的工作表：

15、、操作目标：构造形如下图的工作表：要进行假设检验，需构造形如下图的工作表：其中，左边A5：A16是变量名，右边B5：B16是变量相应的计算公式，输入样本数据E2：E10A列“样本统计量”包含两个，依次为样本个数、样本均值，B列给出计算个样本统计量的计算公式（或称函数）；A列“用户输入”，输入进行假设检验的置信水平，通常为0.95，总体标准差和总体均值假设值（通常为已知条件）；A列“计算结果”，下面包含进行区间估计所需的两个量： 抽样标准误差、计算Z值后面依次为单侧检验和双侧检验，由你自行选择。注：若事先构造好下表，那么任意数据进行假设检验，只要输入数据，直接可以得到假设检验的结果。2、操作方法

16、：、操作方法：第一步：在Excel表格中输入上图中A2：A19和E1：E10的内容第二步：名称定义。（方法基本上相同于区间估计）（1）进行变量定义，将A列的名称定义为B列各个公式计算结果的变量名。 选定A3：B4和A6：B8，A10：B11，A13：B15，A17：B19（先用鼠标选择第一部分，再按住CTRL键选择另外两个部分），然后选择“插入”名称指定最左列确定。（2）为样本数据定义。如图“插入”“名称”“指定 ”“首行”，此操作是将E2：E10的数据名称定义为“样本数据”第三步：计算依次在B列输入计算公式，形如下图，依次可得到各个函数值，(1)、计算“样本个数”，在B3内编辑计算公式：“=

17、count(E2：E10)”（点中E2：E10），回车即可得到样本个数计算值9。 （2）计算样本均值，在B4内编辑计算公式：“=average(E2：E10)”（点中E2：E10），回车即可得到样本均值计算值0.511（3）、输入假设检验所需要的参数，包括置信水平0.95，总体标准差0.015，总体均值假设值0.5 （在进行假设检验时，这些参数值一般已知）（4）、“抽样标准误差”，在B10内编辑公式，“=B7/B3”，回车可得0.002（5）“计算Z值”，在B11内编辑公式，“=NORMSINV（1-B6）”，回车可得-1.64（6）进行双侧检验，“双侧Z值”，在B16内编辑公式，“=NORM

18、SINV(1-B6)”“检验结果”，在B18内编辑公式，“=IF（ABS（B11）ABS(B17),”拒绝H0” , 接受H0”）形如下图，输入该公式后，回车即为检验结果六、如何运用六、如何运用Excel作方差分析作方差分析随机选取26位被访者，了解他们对安乐死的赞同程度。然后按不同血型进行分组，资料如下表。在显著性水平为0.05时进行方差分析，判断不同血型的人对安乐死的态度是否有显著差异性？ 现假设已把数据输入到Excel表格中，区域A2:D11OAABAB8080739278778086946992707384768579526886409173758164 （1） 操作步骤操作步骤： 第

19、一步：选择“工具”下拉菜单，然后选择“数据分析”选项 。第二步：在分析工具中选择“单因素方差分析”。如下图：第三步：当出现对话框后 ，在“输入区域”方框内键入A2：D11（直接让鼠标选中所要分析的数据即可）；在方框内输入0.05；在“输出选项”中选择输出区域为（可以任意指定空白区域），若选择“新工作表组”，方差分析结果将在另一表格中呈现；最后选择“确定”。（2）输出结果：）输出结果：完成上述操作步骤后，EXCEL输出下列分析结果：表一：样本数据描述统计组计数求和平均方差列 11080180.176.54444列 2747567.85714257.8095列 3538977.882.2列 443

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