《统计》-第九章 Excel在统计中的应用

在一家财产保险公司的董事会上，董事们就公司的发展战略问题展开了激烈讨论，其中一个引人关注的问题就是如何借鉴国外保险公司的先进管理经验，提高自身的管理水平。有的董事提出，2006年公司的各项业务与去年相比没有太大增长，除经济环境和市场竞争等因素外，对家庭财产保险的业务开展得不够，公司在管理方式上也存在问题。他认为，中国的家庭财产保险市场潜力巨大，应加大扩展这一业务的力度，同时，应对公司家庭财产推销员实行目标管理，并根据目标完成情况建立相应的奖惩制度。董事长认为该董事的建议有一定道理，准备采纳。会后，他责成计划部经理尽快拿出具体的实施方案。计划部经理接到任务后感到有些头痛。如果目标定得过高，多数实例导入下一页推销员完不成任务，会使推销员失去信心;如果定得过低，将不利于充分挖掘员工的工作潜力，提高公司的业绩水平。怎么才能制定出一个合理的目标呢?于是，计划部经理决定将公司所有推销员的销售额资料拿来进行统计分析。面对这些大量的、分散的，只能表明每个推销员销售情况的原始资料，计划部经理借助于Excel工具，很方便地进行了数据整理，并在此基拙上进行了统计分析，最终制定出了各方都能接受的一个合理的目标销售额。分析:

计划部经理是怎样借助于Excel工具，将这些大量的、分散的原始资料进行科学的整理，使它系统化、条理化，反映总体的综合数量实例导入下一页上一页实例导入特征，揭示事物的本质和规律呢?同时计划部经理又是怎样借助Excel工具，计算相关的统计指标，进行统计分析，以符合统计研究的需要呢?通过本章的学习，便能解决这些问题。

MicrosoftExcel是美国微软公司开发的Windows环境下的电子表格系统，它是目前应用最为广泛的办公室表格处理软件之一。本章主要介绍Excel系统、Excel系统在统计中具有的功能及其应用，重点介绍Excel在统计图表的制作、统计变量的计算、动态数列、统计指数、抽样推断、相关分析等中的应用。返回上一页第一节Excel系统及其在统计中具有的功能一、中文Excel概述

Excel是目前最流行的电子办公软件，利用它可以很方便地进行数据的整理、计算、分析和制作统计图表，实现数据的自动处理。自诞生以来Excel历经了Exce15.0,Exce195,Exce197和Exce12000等不同版本，直至最新的Exce12007版本。随着版本的不断提高，Excel软件强大的数据处理功能和操作的简易性逐渐走人了一个新的境界，整个系统的智能化程度也不断提高，它甚至可以在某些方面判断用户的下一步操作，使用户操作大为简化。Excel具有强有力的数据库管理功能、丰富的宏命令和函数、强有力的决策支持工具、图表绘制功能、宏语言功能、样式功能、对象连接和嵌人下一页上一页功能、连接和合并功能，且操作简捷，这些特性已使Excel成为现代办公软件重要的组成部分。二、Excel在统计中的功能由于大家对Excel的常用办公功能都比较熟悉，这里主要介绍Excel在统计分析中用到的一些功能。

1.分析功能

Excel除了可以做一些一般的计算工作外，还有400多个函数，用来做统计、财务、数学、字符串等操作以及各种工程上的分析与计算。Excel还专门提供了一组现成的数据分析工具，称为“分析工具库”。下一页上一页第一节Excel系统及其在统计中具有的功能在进行复杂的统计分析时，利用现成的数据分析工具，可以节省很多时间。使用者只需为每一个分析工具提供必要的数据和参数，该工具就会使用适宜的统计或数学函数，在输出表格中显示相应的结果。其中一些工具在生成输出表格时还能同时产生图表。如果要浏览已有的分析工具，可以单击“工具”菜单中的“数据分析”命令。如果“数据分析”命令没有出现在“工具”菜单上，则必须运行“安装”程序来加载“分析工具库”。安装完毕之后，必须通过“工具”菜单中的“加载宏”命令，在“加载宏”对话框中选择并启动它。这些分析工具为建立复杂的统计和计量分析工作带来极大的方便。当我们运行工具——加载宏后，在工具下拉菜单则会出现“数据分析”下一页上一页第一节Excel系统及其在统计中具有的功能子菜单，点击它可以完成以下统计计算，见图9-1。2.图表功能在Excel系统中，大约有100多种不同格式的图表可供选用，用户只要做几个简单的按键动作，就可以制作出精美的图表。通过图表指南一步一步地引导，选用不同的选项，得到所需的结果。满意的话就继续，不满意则后退一步，重新修改选项，直到最后出现完美的图表。

3.数据库管理功能对于一个公司，每大都会产生许多新的业务数据，例如，销售数据、库存的变化、人事变动的数据资料等。这些数据必须加以处理，才能知道每段时间的销售金额、某个时候的存货量、要发多少薪水给每个下一页上一页第一节Excel系统及其在统计中具有的功能下一页上一页第一节Excel系统及其在统计中具有的功能员工等。要对这些数据进行有效的处理，就离不开数据库系统。所谓数据库系统，就是一组有组织的信息。例如，将每位职员的简历写在一张卜片上，卜片放在盒子内，盒子内的数据通常组成列和行。再如，每种产品的产地、规格、单位、单价、数量组成一列，每一行都包含同一属性的数据，每列都包含同一品种的数据，即每列都有产地、规格、单位、单价、数量。管理数据库可用专门的数据库管理软件，如FoxPro,Access,Clipper,Sybase等。在Excel中提供了类似的数据库管理功能，保存在工作表内的数据都是按照相应的行和列存储的，这种数据结构再加上Excel提供的有关处理数据库的命令和函数，使得Excel具备了能组织和管理大量数据的能力。

4.宏语言功能利用Excel中的宏语言功能，用户可以将经常操作的全过程记录下来，并将此过程用一简单的组合按键或工具按钮保存起来。这样，在下一次操作中，只需按下所定义的宏功能的相应按键或工具按钮即可，而不必重复整个过程。例如，可以定义一个打开最后编辑文件且可以自动执行的宏，以后当用户打开Excel后，将自动打开上一次编辑的工作簿。在Excel中，高级用户可使用VisualBasic语言，进行宏命令的开发。利用宏命令，用户可以将Excel的下拉菜单和对话框更改或将第一节Excel系统及其在统计中具有的功能下一页上一页图形按钮的说明更换，使它们更适合于用户的工作习惯和特殊要求。三、Excel常用函数

Excel为用户提供了数学和三角函数、统计函数、数据库函数、财务函数、工程函数、逻辑函数、文本函数、日期和时间函数、信息函数、查找和引用函数等10类300多种函数，可以满足多方面的计算要求。其中，以统计函数为最多，达78种;此外还有14种数据库函数可从数据库提取数据进行计算，以及在统计中经常使用的数学、统计函数20种，合计112种。现将一些常用统计函数及数学函数列表如下:1.统计函数第一节Excel系统及其在统计中具有的功能下一页上一页函数名称功能简介

FREQUENCY以垂直数组形式求频率分布

MODE求一组数据的众数

MEDIAN求一组数据的中位数

AVERAGE求一组数据的均值

AVERAGEA求数据清单中数据的均值

HARMEAN求调和平均数

GEOMEAN求几何平均数

TRIMMEAN求去掉最大值和最小值的平均数

MAX求数据清单中的最大值下一页上一页第一节Excel系统及其在统计中具有的功能

MAXA求数据清单中包含逻辑值和字符串的最大值

MIN求数据清单中的最小值

MINA求数据清单中包含逻辑值和字符串的最小值

QUARTILE求一组数据的四分位差

AVEDEV求样本数据与其均值的平均离差

DEVSQ求样本数据与其均值离差的平方和

STDEV求样本标准差

STDEVA求包含逻辑值和字符串的样本标准差

STDEVP求兑体标准差STDEVPA求包含逻辑值和字符串的总体标准差下一页上一页第一节Excel系统及其在统计中具有的功能VAR求样本方差VARA求包含逻辑值和字符串的样本方差VARP求总体方差VARPA求包含逻辑值和字符串的总体方差KURT求一组数据的峰度SKEW求一组数据的偏度CONFIDENCE求总体均值的置信区间COVRB求协方差CORREL求相关系数LINEST建立直线方程下一页上一页第一节Excel系统及其在统计中具有的功能INTERCEPT求直线方程的截距SLOPE求直线方程的斜率FORECAST求线性趋势值(预测值)TREND求线性趋势值(预测值)STEYX求趋势值的标准误差COUNT求数组中数据的个数(只计算数字型数据)2.统计中常用的数学函数函数名称功能简介SUM参数求和SUMIF将符合指定条件的单元格相加下一页上一页第一节Excel系统及其在统计中具有的功能PRODUCT参数相乘QUOTIENT求两数相除的整数部分MOD求两数相除的余数POWER求数值的乘幂SQRT求平方根RAND求0一1之间的随机数FACT求某数的阶乘LN求某数的自然对数LOG求某数以指定数为底的对数LOG10求某数以10为底的对数下一页上一页第一节Excel系统及其在统计中具有的功能SUMPRODUCT求两组对应值的乘积之和SUMSQ求参数的平方和SUMXMY2求两数组对应值之差的平方和SUMX2MY2求两数组对应值平方差之和SUMX2PY2求两数组对应值平方和之和返回上一页第一节Excel系统及其在统计中具有的功能使用Excel可以完成很多专业软件才能完成的数据统计、分析工作，本节将介绍Excel在统计图表的制作、统计变量的计算、动态数列、抽样推断、相关分析等中的具体应用。一、Excel制图统计图显示资料具有形象生动、一口了然的优点，通过图形可以很方便地观察到数量之间的对比关系、总体的结构特征以及变化发展趋势等。在统计分析中常用的统计图有条形图、直方图、曲线图、圆形图等，这些图都可以很简便地在Excel中完成。下面我们用具体实例来说明Excel中条形图和直方图的制作。

1.用“图标向导”工具绘制统计图第二节Excel在统计中的具体应用下一页

【例9.1】根据我国按产业结构分类的就业人员的构成资料制作统计图。步骤一:创建工作表。将统计资料输入到Excel中，如图9-2所示。步骤二:选定图标类型。单击图表向导，在弹出的“图表向导一4步骤之1一图表类型”对话框中选择所需要的图表类型，本例选择柱形图中的簇状柱形图，单击下一步，如图9-3所示。步骤三:确定数据范围。在弹出的“图表向导一4步骤之2一图表源数据”对话框中选择数据区域、定义分类轴，并给系列命名，单击下一步。步骤四:定义图表选项。在弹出的“图表向导一4步骤之3一图表第二节Excel在统计中的具体应用下一页上一页选项”对话框中给图表标题及每个坐标轴命名，单击下一步。步骤五:选择图表保存位置并显示结果。在弹出的“图表向导一4步骤之4一图表位置”对话框中选择图表保存位置，单击完成。最后，得到如下图形，如图9-4所示。

2.使用直方图工具分组并绘制统计图使用“直方图”工具，可以将调查得到的数据分组，计算各组中出现的频数和频率，并绘制次数分布直方图和累计频率折线图。

【例9.2]某月某市50户居民购买消费品支出资料如图9-5所示。(单位:元)对其按800~900、900~1000、1000~1100、1100~1200、1200~1300、1300~1400、1400~1500,下一页上一页第二节Excel在统计中的具体应用第二节Excel在统计中的具体应用1500~1600,1600以上分为9个组。采用手工分析的步骤是先将各数值段的人数分别统计出来，即进行统计分组，编制统计表，再以此统计表为基础绘制统计直方图。使用Excel中的“数据分析”功能可以直接完成此任务。步骤一:打开原始数据表格，制作本实例的原始数据要求单列，确认数据的范围，对数据进行排序，如图9-6所示。步骤二:在右侧输入数据接受序列。所谓“数据接受序列”，就是分段统计的数据间隔，该区域包含一组可选的用来定义接收区域的边界值。需要注意的是，Excel是按照左开右闭的方式对落在各区间的数据进行频数统计的，所以本例中，各组上限值设定为899,999,下一页上一页第二节Excel在统计中的具体应用1099,1199,1299,1399,1499,1599,1699，并按升序排列。如图9-6所示。步骤三:选择“工具”一“数据分析”一“直方图”后，出现属性设置框，依次选择:

输入区域:原始数据区域;

接受区域:数据接受序列;

标志:如果输入区域的第一行或第一列中包含标志项，请选中此复选框;如果输入区域没有标志项，请清除此复选框，MicrosoftExcel将在输出表中生成适宜的数据标志;

如果选择“输出区域”，则新对象直接插入当前表格中;下一页上一页第二节Excel在统计中的具体应用

选中“柏拉图”，此复选框可在输出表中按降序来显示数据;如果此复选框被清除，MicrosoftExcel将只按升序来显示数据并省略最右边包含排序数据的三列数据;若选择“累计百分率”，则会在直方图上叠加累计频率曲线;所有选项如图9-7所示。步骤四:输入完毕后，则可立即生成相应的直方图，如图9-8所示。这张图还需要做比较大的调整。主要是:横、纵坐标的标题、柱形图的间隔以及各种数据的字体、字号、字意等。如图中的“频率”实际上是频数，“累计(%)”实际上是累计频率。系列框中的频率和累计(%)须单击鼠标右键选择“数据源”进行修改。同时为了达到柱形图之间无缝的紧密排列，需要双击图柱区域，就出现“数据系列下一页上一页第二节Excel在统计中的具体应用格式”选项框，需要将其中的“选项”中“分类间距”调整为“0”。其余细节，请双击要调整的对象按照常规方法进行调整，这里不再赘述。调整后的直方图见图9-9。二、Excel描述统计分析数据统计分析，首先需要对统计数据本身有一个总体的了解，这对选用统计分析方法、作出正确的统计结果非常重要。一般的统计分析结果都包含一些常见的数据的描述结果。对数据进行描述统计，其结果一般分为两类:一是数据集中的情形，这称为集中指标，主要包括平均数、众数和中位数等;另外一种是数据分散的情形，称为变异指标，主要包括范围四分位距、方差、标准差和标准差系数。下一页上一页下一页第二节Excel在统计中的具体应用对话框内各选项的含义如下:

输入区域:在此输入待分析数据区域的单元格范围。分组方式:如果需要指出输入区域中的数据是按行还是按列排列，则单击“行”或“列”。标志位于第一行/列:如果输入区域的第一行中包含标志项(变量名)，则选中“标志位于第一行”复选框;如果输入区域的第一列中包含标志项，则选中“标志位于第一列”复选框;如果输入区域没有标志项，则不选任何复选框，Excel将在输出表中生成适宜的数据标志。平均数置信度:若需要输出由样本均值推断总体均值的置信区间，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度。上一页第二节Excel在统计中的具体应用第K大/小值:如果需要在输出表的某一行中包含每个区域的数据的第K大/小值，则选中此复选框，然后在右侧的编辑框中，输入K的数值。输出区域:在此框中可填写输出结果表左上角单元格地址，用于控制输出结果的存放位置。新工作表组:单击此选项，可在当前工作簿中插入新工作表，并由新工作表的A1单元格开始存放计算结果。如果需要给新工作表命名，则在右侧编辑框中键入名称。新工作簿:单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算结果。下一页上一页汇总统计:指定输出表中生成下列统计结果，则选中此复选框。统计结果如图9-11所示。如果只要求计算上述指标中的某一个或某几个，在Excel中点击函数关，选择相应的函数即可。三、Excel在动态数列中的应用进行动态数列分析，需要计算动态分析指标和进行动态数列趋势分析。动态分析指标用Excel中的一般统计函数就可以求得，动态数列趋势分析用Excel中专门的数据分析工具即可解决。下面结合实例进行阐述。

1.用Excel计算动态数列水平指标第二节Excel在统计中的具体应用下一页上一页第二节Excel在统计中的具体应用

(1)用Excel计算平均发展水平用Excel计算平均发展水平，要根据动态数列的类型和对应的计算公式来计算。

【例9.4】现根据某省2002-2007年工业总产值和年末人口数资料，用Excel计算该省这几年平均工业总产值和平均人口数。要用Excel计算相关动态分析指标，首先需要把动态数列的数据输入到Excel工作表中，如图9-12所示。1)用Excel计算时期数列的平均发展水平。图9-12表格中的工业总产值数列是时期数列。计算时期数列的平均发展水平，一般采用简单算术平均法，因此用Excel中的下一页上一页第二节Excel在统计中的具体应用AVERAGE函数计算。单击H2单元格，输入“=AVERAGE(B2:G2)”，按Enter键得到结果9057.83亿元，即某省2002-2007年平均工业总产值为9057.83亿元，如图9-12所示。

2)用Excel计算时点数列的平均发展水平。计算时点数列的平均发展水平，需要根据时点数列的四种情况选择不同的计算公式，然后在Excel中利用求和函数SUM来进行计算。图9-12表格中的“年末人口数”数列是间隔相等的时点数列，故用“首末折半法”公式。单击H3单元格，输入“=SUM(B3/2,C3,D3,E3,F3,G3/2)/(6-1)”，按Enter键得到1.19千万人，即某省2002-2007年末平均人口数为1.19千万人，如图9-12所示。下一页上一页第二节Excel在统计中的具体应用

(2)用Excel计算增长量和平均增长量

【例9.5]现用图9-12的部分资料为例来介绍用Excel计算逐期增长量、累计增长量、和平均增长量的方法和步骤，见表9-2。C3单元格，输入“=B3-B2"，按Enter键得到2002年的逐期增长量2177亿元，然后利用填充柄功能按住鼠标左键不放向下拖至C7放开，即可得到2003-2007年的逐期增长量(见图9-13中C列数据);单击D3单元格，输入“=B3-4840"，按Enter键得到2002年的逐期增长量2177亿元，然后利用填充柄功能按住鼠标左键不放向下拖至C7放开，即可得到2003-2007年的累计增长量(见图9-13中D列数据)。下一页上一页计算出各期增长量后，用Excel中的AVERAGE函数就可以计算平均增长量了。单击C8单元格，输入“=AVERAGE(C3:C7)"，按Enter键即可得到某省工业总产值的平均增长量为1425.8，如图9-13所示。

2.用Excel计算动态数列速度指标

【例9.6]现仍用表9-2的资料计算动态数列速度指标。

(1)用Excel计算发展速度和增长速度单击E3单元格，输入“=B3/B2x100"，按Enter键得到2002年的环比发展速度144.98%，然后利用填充柄功能按住鼠标左键向下拖至E7放开，即可得到2003-2007年的环比发展速度(见第二节Excel在统计中的具体应用下一页上一页第二节Excel在统计中的具体应用图9-13中E列数据)。单击F3元格，输入“=B3/4840x100"，按Enter键得到2002年的定基发展速度144.98%，然后利用填充柄功能按住鼠标左键向下拖至F7放开，即可得到2003-2007年的定基发展速度(见图9-13中F列数据)。单击G3单元格，输入“=E3-100"，按Enter键得到2002年的环比增长速度44.98%，然后利用填充柄功能按住鼠标左键向下拖至G7放开，即可得到2003-2007年的环比增长速度(见图9-13中G列数据)。单击H3单元格，输入“=F3一100"，按Enter键得到2002年的定基增长速度44.98%，然后利用填充柄功能按住鼠标左键向下拖至H7放开，即可得到2003-2007年的定基增长速度(见图9-13中H列数据)。下一页上一页第二节Excel在统计中的具体应用

(2)用Excel计算平均发展速度和平均增长速度用Excel中的GEOMEAN函数可计算平均发展速度。单击E8单元格，输入"=GEOMEAN(E3:E7)"，按Enter键即可得到某省工业总产值平均发展速度为119.85%和平均增长速度19.85%。如图9-13所示。

3.用移动平均法测定长期趋势移动平均法是一种简单平滑预测技术，它的基本思想是:根据动态数列资料，逐项推移，依次计算包含一定项数的序时平均值，以反映长期趋势的方法。因此，当动态数列的数值由于受周期变动和随机波动的影响，起伏较大，不易显示出事件的发展趋势时，使用移动下一页上一页第二节Excel在统计中的具体应用平均法可以消除这些因素的影响，显示出事件的发展方向与趋势(即趋势线)，然后依趋势线分析预测序列的长期趋势。Excel在“数据分析”宏中提供了三种时间数列计算方法，即常用的移动平均法、指数平滑法和回归法，利用这些宏可以计算出估计值、标准差、残差和拟合图。下面举例介绍移动平均法的应用。

【例9.7】已知某商场2007年的月销售额如表9-3所示，试预测该商场的月销售额趋势。操作步骤:1)在Excel工作表中B2:B13区域中输入“某商场2007年各月销售额”资料。下一页上一页第二节Excel在统计中的具体应用

2)在Excel“工具栏”中选择“数据分析”，并单击“移动平均’，过程。

3)在移动平均菜单的“输入区域”中输入“B1:B13"(也可输入“B2:B13"，则不需选中标志位于第一行复选框)，在“间隔”中输入“3”表示进行3项移动平均，选择“输出区域”，并选择输出“图表输出”和“标准差”输出，如图9-14所示。在输入区域框中指定统计数据所在区域B1:B13;因指定的输入区域包含标志行，所以选中标志位于第一行复选框;

在间隔框内键入移动平均的项数3(根据数据的变化规律，本例选取移动平均项数N=3)。下一页上一页第二节Excel在统计中的具体应用在输出选项框内指定输出选项。可以选择输出到当前工作表的某个单元格区域、新工作表或是新工作簿。本例选定输出区域，并键入输出区域左上角单元格地址C2;选中图表输出复选框。若需要输出实际值与一次移动平均值之差，还可以选中标准误差复选框。4)输入完毕后，Excel给出移动平均的计算结果及实际值与移动平均值的曲线图，如图9-15所示。

图9-15还绘制出实际观察值与3项移动平均估计值之间的拟合曲线，可以看出，移动平均值削弱了上下波动，如果这种波动不是季节波动而是不规则变动的话，显然，移动平均可以削弱不规则变动。对于该例进行4项移动平均的结果与3项移动角明显不同。下一页上一页也就是说，当数列有季节周期时，只要移动平均的项数和季节波动的周期长度一致，则移动平均值可以消除季节周期，并在一定程度上消除不规则变动，从而揭示出数列的长期趋势。四、Excel在指数分析中的应用指数分析法是研究社会经济现象数量变动情况的一种统计分析法。指数有总指数与平均指数之分，在这一节我们介绍如何用Excel进行指数分析。Excel中没有专门的指数函数，因此，只能通过自己构造公式来实现指数的计算和分析。

【例9.8】图9-16是某企业甲、乙、丙三种产品的生产情况，计算三种产品的产量综合指数和单位成本综合指数。第二节Excel在统计中的具体应用下一页上一页计算步骤:

第一步:计算各产品的p0q0。在G2中输入“=C2xD2"，并用鼠标拖曳将公式复制到G2:G4区域。第二步:计算各产品的p0q1。在H2中输入“=C2xF2"，并用鼠标拖曳将公式复制到H2:H4区域。第三步:计算各产品的p1q1。在I2中输入“=E2xF2"，并用鼠标拖曳将公式复制到I2:I4区域。第四步:计算∑p0q0、∑p0q1，和∑p1q1选定G2:G4区域，单击工具栏上的“∑”按钮，在G5出现该列的求和值;选定H2:H4区域，单击工具栏上的“∑”按钮，在H5出现该列的求和值;选定I2:I4区域，第二节Excel在统计中的具体应用下一页上一页第二节Excel在统计中的具体应用单击工具栏上的“∑”按钮，在I5出现该列的求和值。第五步:计算生产量和单位成本综合指数。生产量综合指数

，在C6中输入“=H5/G5”便可得到生产量综合指数1.3188，即131.88%;单位成本综合指数再E6中输入“=I5/H5”便可得到单位成本综合指数0.7915即79.15%。注意:在输入公式的时候，不要忘记等号，否则就不会出现数值。最终计算结果如图9-17所示。五、Excel在抽样推断中的应用抽样推断是根据部分单位实际调查结果来推断总体数量特征的一下一页上一页种统计调查方法，属于非全面调查的范畴。它是按照科学的原理和计算，从若干单位组成的事物总体中，抽取部分样本单位来进行调查、观察，用所得到的样本指标的数据以代表总体，推断总体。

1.样本单位的选取

【例9.9】仍然以表9-1所示的xx中学期中考试成绩表为例。省教育厅派专家组进行来校检查学生考试试卷，专家组拟对总体进行抽样调查，对学校某班的体同学随机抽取20名作为调查样本。为了保证结果的非人为性，利用Excel帮助专家组作出抽查的结果。操作步骤:

第一步:打开原始数据表格，全班总共有56个同学，现在对这56第二节Excel在统计中的具体应用下一页上一页第二节Excel在统计中的具体应用个总体单位进行编号为1-56。如图9-18所示。第二步:选择“工具”——“数据分析”——“抽样”后，出现对话框，依次选择:如图9-19所示;第三步:在输入区域框中输入总体单位编号所在的单元格区域，在本例是$C$3:$C$58，如果输入区域的第一行或第一列为标志项(横行标题或纵列标题)，可单击标志复选框。第四步:在抽样方法项下，有周期和随机两种抽样模式:“周期”模式即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。假如我们要在80个总体单位中抽取20个，则在“间隔”框中输入4。下一页上一页第二节Excel在统计中的具体应用“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可;若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合;整群抽样也要先将总体单位分群编号，然后按随机原则抽取若干群作为样本，对抽中的群的所有单位全部进行调查。可以看出，此例的编号输入方法，只适用于等距抽样和纯随机抽样。第五步:指定输出区域，在这里我们输入$E$3:$E$23或任一空白处，单击确定后，即可得到抽样结果。如图9-20所示。由于随机抽样时总体中的每个数据都可以被多次抽取，所以在样本中下一页上一页第二节Excel在统计中的具体应用的数据一般都会有重复现象，解决此问题有待于程序的完善。可以使用“筛选”功能对所得数据进行筛选。选中样本数据列，依次执行“数据”一“筛选”一“高级筛选”，如图9-21所示。最后的样本结果如下图9-22所示，我们可以根据经验适当调整在数据样本选取时的颧量设置，以使最终所得样本数量不少于所需数量。2.抽样估计

【例9.10】假定该班同学的考试成绩服从正态分布，我们从全班同学里面随机抽20名同学作为样本，试以95%的置信水平估计该班考试平均分的置信区间。下一页上一页第二节Excel在统计中的具体应用在Excel中，总体平均数的估计是通过调用CONFIDENCE统计函数来实现的。以本例来说，利用AVERAGE和STDEVP统计函数先计算出样本平均数和标准差，然后再调用CONFIDENCE进行估计。第一步，将样本数据录入Excel，引用AVERAGE和STDEVP统计函数算出样本平均数和标准差分别是75、19.18(每次抽样结果不一)。第二步，进人“统计”函数，选择CONFIDENCE后出现如下对话框，如图9-23所示，选择临界值a为0.05，标准差为19.18，样本容量为20，立即给出了极限误差△:的估计值为8.41。由此，在可靠性为95%的情况下，可以认为该班考试平均分的置信区间为下一页上一页下一页上一页第二节Excel在统计中的具体应用75±8.41。六、Excel在相关与回归分析中的应用相关分析和回归分析是研究社会经济现象之间相互关系的统计分析方法。统计上，一般是通过编制相关表、绘制相关图和计算相关系数来判断和确定相关关系的表现形态和密切程度的。回归分析方法则是通过经济现象的相关分析，建立被解释变量(因变量)和解释变量(自变量)之间的统计模型，以研究各变量之间彼此作用和影响的关系。在此，介绍一元线性回归分析的应用。

【例9.11】某市房产公司通过市场调查，获得表9-4所示的住宅居住面积(平方英尺，SqFt)与销售价格(千美元，Price)的数据。下一页上一页第二节Excel在统计中的具体应用1.相关图的绘制相关图又称散点图，利用表9-4的资料制作相关图的步骤如下:1)将数据建立Excel工作表，并选择区域;2)单击Excel图表向导，在“图表类型”中选择“XY散点图”;3)在“子图表类型”中选择第一种散点图，并单击“下一步”，即可得到图9-24。2.计算相关系数在Excel数据分析宏中，专门提供了计算相关系数的宏过程。利用此过程，可以计算多个变量之间的相关矩阵。以表9-4中的数据为例，相关系数宏计算相关系数矩阵的过程如下:第二节Excel在统计中的具体应用1)用鼠标单击表中待分析数据的任一单元格;2)选择“工具”菜单的“数据分析”子菜单;3)用鼠标双击数据分析工具中的“相关系数”选项;4)填写完“相关系数”对话框，单击“确定”按钮即可得到各个变量的相关系数矩阵，结果如图9-25所示。图中三角矩阵