第十章 Excel 在统计中的应用

《统计基础》第十章Excel在统计中的应用学习目标能力目标：能进行Excel的基本操作能运用Excel进行简单随机抽样。能熟练运用Excel进行数据排序、编制频数分布表并绘制统计图。能运用Excel进行各类统计量的计算能运用Excel进行长期趋势的计算能利用Excel软件，对简单直线回归参数进行确定，并确定回归方程立的回归方程进行分析和预测。知识目标：掌握Excel的基本知识和基本操作方法了解函数公式或统计功能掌握Excel的统计功能应用技巧《统计基础》第一节中文Excel概述•一、中文Excel简介•二、Excel的启动与退出•三、Excel工作界面简介图10—1Excel工作界面《统计基础》第二节用Excel进行统计数据的搜集•用Excel进行数据的搜集，需要使用Excel扩展功能，如果Excel初次使用尚未安装数据分析功能，必须依次选择“工具”、“加载宏”，在安装光盘中加载数据“分析工具库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项。如图10—2、图10—3、图10—4所示。《统计基础》•图10—2安装加载宏图10—2安装加载宏《统计基础》•图10—3添加分析工具库选项《统计基础》•图10—4数据分析选项《统计基础》•【例10—1】假定某专业学生为一个总体，人数有100人，则总体单位数为100，利用Excel数据搜集功能随机从中抽取20人进行调查，了解该专业学生的学习情况。•操作步骤：启动Excel2003，新建一个工作簿，根据需要可以对工作簿进行命名，如数据搜集1或Book1。•第一步，使用Excel进行抽样，首先要对每个学生编号，即对100个总体单位进行编号。按随机原则编号后(也可以按有关标志或无关标志编号)，将编号输入工作表，如图10—5所示。《统计基础》图10—5输入总体单位编号《统计基础》•第二步，单击工具菜单，选择“数据分析”选项，打开数据分析对话框，从中选择“抽样”，如图10—6所示。图10—6工具—数据分析—抽样《统计基础》•第三步，选中“抽样”，单击“确定”按钮。弹出抽样对话框，如图10—7所示。图10—7抽样对话框《统计基础》•第四步，在抽样对话框中：①在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1：$E$20，系统将从A列开始抽取样本单位，然后按顺序抽取B列至E列。如果输入区域的第一行或第一列为标志项(横行标题或纵列标题)，可单击标志复选框；②在抽样方法选项下，有周期和随机两种抽样模式，采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可，本例是20个；③指定输出区域，在这里输入$H$2（或输入$H$2：$H$16），单击“确定”按钮后，即可得到抽样结果，如图10—8、图10—9所示。《统计基础》图10—8输入“抽样”对话框数据《统计基础》图10—9输出随机抽样结果《统计基础》图10—10进行等距抽样并输出结果《统计基础》第三节利用Excel进行数据分组和制作统计图表•一、用Excel进行数据排序和统计分组•1．数据排序•2．数据分组•【例10—2】某车间50名工人加工零件日产量资料如下：•122124129139106134127123118112•117130122125117112134127123119•108131125117122113122123127135•133126122118108137114121128124•111118123126133115139128124121•根据资料编制组距为5的分配数列，编制次数分布表并绘制次数分布直方图。《统计基础》•操作步骤：•（本例介绍直方图工具的操作方法）•第一步，数据输入及排序。启动Excel，新建一个工作簿命名为数据整理1。在工作表Sheet1上的（A2—A51）矩形区域内输入50名工人的日产量数据资料，选中数据资料，进行数据排序，如图10—11，图10—12所示。（图见教材）《统计基础》•第二步，选择“直方图”工具。在工具菜单中单击“数据分析”选项，从其对话框的分析工具列表中选择“直方图”，打开“直方图”对话框，如图10—13所示。•第三步，填写“直方图”各项。如图10—14所示。•在“直方图”对话框中，需要填写以下各项，其中：•输入区域：在此输入待分析数据区域的单元格范围。本例中输入$A$2：$A$51。•接收区域（可选）：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值。这些值应当按升序排列。本例中输入$B$2:$B$8，各上限值为110，115，120，125，130，135，140。•输出区域：选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格(代表输出区域的左上角)，推荐只输人一个单元格(本例为$C$1)，因为往往事先并不知道具体的输出区域有多大。如果输出表将覆盖已有的数据，Excel会自动确定输出区域的大小并显示信息。•（图见教材）《统计基础》•第四步，选择图表输出，可以得到直方图；选择累计百分率，系统将在直方图上添加累积频率折线；选择柏拉图，可得到按降序排列的直方图。现只选择了图表输出，单击“确定”后，显示如图10—15所示。•第五步，图形调整。应当注意，图3—15实际上是一个“草图”，还需要做调整和修改：《统计基础》图10—16调整过的直方图《统计基础》•二、用Excel绘制统计图•【例10—3】根据我国2001—2005年国内生产总值资料绘制统计图。•操作步骤：启动Excel2003，新建一个工作簿Bookl。•第一步，把数据输入到工作表中，如图10—17所示。•第二步，选中某一单元格，单击插入菜单，选择图标选项，弹出图表向导对话框（此步骤也可通过直接点工具栏选项中“图表向导”），如图10—18所示。•第三步，在图表类型中选择柱形图，然后在子图表类型中选择一种类型，这里选用系统默认的方式，然后单击下一步按钮，打开图表数据源对话框，如图10—19所示。•（图见教材）《统计基础》•第四步，在图表数据源对话框中填入数据所在的区域，单击下一步按钮，即可得到图形，如图10—20所示。•第五步，在图表选项对话框中依次填入需要的项目，单击下一步和完成按钮，调整后即可得柱状图，如图10—21所示。•（图见教材）《统计基础》图10—21柱形图《统计基础》第四节用ExceI计算描述统计量•一、利用“描述统计”分析工具计算描述统计量•一、利用“描述统计”分析工具计算描述统计量•【例10—5】根据某企业甲车间40名工人日产量资料，计算各类平均数和标准差，日产量资料如下(件)：•3443412934354229303536434037304128374238•3545333740322736423040434336352846363229•操作步骤：启动Excel2003，新建一个工作簿命名为计算描述统计量。《统计基础》•第一步，输入工人日产量资料，如图10—27所示。•第二步，在工具菜单中选择“数据分析”选项，从其对话框中选择“描述统计”，单击“确定”，打开“描述统计”对话框，在输人区域中输人$A$1：$A$40，在输出区域中选择$C$1，其他复选框可根据需要选定，如图10—28，图10—29所示。•第三步，选择汇总统计，可给出一系列描述统计量，单击确定按钮，可得输出结果，如图10—30所示。•（图见教材）《统计基础》图10—30“描述统计”计算结果输出《统计基础》二、利用统计函数计算描述统计量【例10—6】仍使用上面的例子，已经把数据输入到A1：A40单元格，根据Excel提供的公式，用函数计算描述统计量。（一）用函数计算各种简单平均数操作步骤：在输出区域输入要计算的“算术平均数”、“调和平均数”、“几何平均数”等各统计量指标名称，对应各指标名称输入函数，回车即得到各统计量计算结果。如在算术平均数右边空格内输入“=AVERAGE(A1：A40)”，回车即得到算术平均数为36.225。其他依次操作，即可得到结果。如图10—31，图10—32所示。•（图见教材）《统计基础》•表10—1指标与函数对应关系表指标名称函数算术平均数=AVERAGE(A1：A40)算术平均数=HARMEAN(A1：A40)几何平均数=GEOMEAN(A1：A40)中位数=MEDIAN(A1：A40)众数=MODE(A1：A40)全距=MAX(A1：A40)-MIN(A1：A40)标准差=STDEV(A1：A40)标准差=STDEV(A1：A40)/AVERAGE(A1：A40)•指标及函数对应见表10—1所示。注意：函数中的“（A1：A40）”是源数据单元格范围，计算时根据资料不同具体输入。《统计基础》（二）用函数计算加权平均数和标准差•【例10—7】根据某车间工人日产量资料，计算平均数、标准差及标准差系数。•操作步骤：启动Excel2003，新建一个工作簿命名为计算统计量。•第一步，将原始数据输人Excel工作簿并列出计算平均数和标准差需要的栏目标题，如图10—33，图10—34所示。•第二步，计算各组日产量xf：在C2中输入“=A2*B2”，回车，以下各行数据使用填充功能，然后在C7中输入“=SUM（C2:C6）”求和。•第三步，计算平均数：在输出位置（本例为B11）输入“平均数”，在其右边空格内输入“=C7/B7”回车即得到平均数为49.2。•第四步，计算：在D2中输入“B2*(A2-＄C＄11)^2”，回车即得到871.2，以下各行使用填充功能，然后在D7中输入“=SUM（D2:D6）”求和。•第五步，计算标准差：在输出位置（本例为B12）输入“标准差”，在其右边空格内输入“=SQRT(D7/B7)”，回车即得到标准差为9.11921。•第六步，计算标准差系数：在输出位置（本例为B13）输入“标准差系数(%)”，在其右边空格内输入“=C12/C11*100”，回车即得到标准差系数为18.53%。•计算结果如图10—35所示。fx2x•（图见教材）《统计基础》图10—35输出计算结果《统计基础》第五节用Excel进行动态数列分析•【例10—8】设有某企业1990—2007年A产品产量资料如表10—2：•表10—2某企业历年A产品产量资料年份产量年份产量年份产量199021199615200230199123199719200333199222199817200439199324199920200543199426200019200647199518200125200746《统计基础》•操作步骤：启动Excel2003，新建一个工作簿命名为计算移动平均数。•第一步，将18个产量数据资料输入到A2：A19单元格，如图10—36所示。•第二步，单击“工具”菜单，选择“数据分析”选项。打开“数据分析”对话框，从其“分析工具”列表中选择“移动平均”选项，如图10—37所示，单击“确定”按钮。•第三步，打开“移动平均”对话框后，在输人区域处填写“$B$2：$B$19”、输出区域处填写“$C$2”、间隔处填写5，选中“图表输出”复选框，点确定即得到五年移动平均数和长期趋势图。如图10—38、图10—39所示。•注意：四年移动平均及四年移正平均方法与五年移动平均相同，只在输入区域处依次填写“$B$2：$B$19和“$D$5：$D$19”；输出区域处依次填写$D$2和$E$5；间隔处依次填写4和2，点“确定”即可。如图10—40所示。•要输出长期趋势图，同时选中“图表输出”即可。•（图见教材）《统计基础》图10—40各移动平均数结果输出《统计基础》图10—40各移动平均数结果输出•一、利用Excel计算相关系数•【例10—9】某地区历年城镇居民家庭人均收入和人均消费资料如表10—3所示。根据资料利用Excel的统计功能和函数计算人均收入和人均消费之间的相关系数。《统计基础》表10—3某地区历年城镇居民家庭人均收入和人均消费资料单位：元年份人均收入人均消费年份人均收入人均消费199119921993199419951996199719981999812935