统计频数excel在统计中的应用.docx

1、统计频数excel在统计中的应用第十一章 Excel在统计中的应用学习目标 了解Excel是统计工作的基本工具。 理解用Excel处理统计数据及统计分析的基本程序与基本原理。 掌握用Excel进行时间序列分析、指数分析和相关与回归分析。 熟练掌握及运用Excel搜集与整理数据、计算描述统计量。统计学是一门应用性非常强的学科。而统计工作的每一个环节几乎都离不开统计计算机软件的应用。典型的统计软件有SAS、SPSS、MINITAB、STATISTICA、Excel等。其中由美国微软公司开发的Excel电子表格软件，是办公自动化中非常重要的一款软件，它不仅能够进行表格处理、图形分析、数据的自动处理和

2、计算，而且简单易用，具有一定统计基础知识的人都可以利用它进行统计工作。本章介绍Excel2003在统计中的应用及正确使用的方法。第一节 用Excel搜集与整理数据 一、用Excel搜集数据搜集数据的方法有多种，可以采用统计报表、典型调查、重点调查或抽样调查，以后我国的统计调查将以抽样为主。针对抽样调查，Excel的数据分析工具中提供了一个专门的“抽样”工具，可以帮助使用者快速完成抽样工作。使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，本节以例11-1为操作范例进行阐述。例11-1 假定有100名消费者，从中选取15人进行工资收入调查。首先

3、，将100名消费者编号为1100号，输入编号，形成总体单位编号表如图11-1所示。图11-1 总体各单位编号表完成总体单位编号清单后，利用“抽样”工具进行抽样的具体操作如下： 第一步：单击“工具”菜单，选择“数据分析”选项（若无数据分析选项，可在工具菜单下选择加载宏，在弹出的对话框中选择分析工具库，便可出现数据分析选项）。 第二步：打开“数据分析”对话框，从其对话框的“分析工具”列表中选择“抽样”选项，如图11-2所示。图11-2 “数据分析”对话框 第三步：单击“确定”按钮，打开“抽样”对话框，确定输入区域、抽样方法和输出区域，如图11-3所示。图11-3 “抽样”对话框1确定输入区域在“抽

4、样”对话框的“输入区域”框中输入总体单位编号所在的单元格区域。在本例中，输入区域为$A$1:$J$10。输入区域有两种方法：一是用手工逐字录入；二是用鼠标左键单击图11-1中的$A$1位置，出现虚线框，然后拖拉虚线框，选中表中全部数字，自动在图11-3中的“输入区域”显示出$A$1:$J$10。系统将从A列开始抽取样本，然后按顺序抽取B列至J列。如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击“标志”选框。2选定抽样方法在“抽样方法”框中，有“周期”和“随机”两种抽样模式。（1）“周期”模式即所谓的等距抽样、机械抽样或系统抽样。此种抽样方法，需要确定周期间隔，周期间隔由总体单

5、位数除以要抽取的样本数而求得。本例中，要在100个总体单位中抽取15个，则在“间隔”框中输入6。（2）“随机”模式适用于简单随机抽样、分层抽样、整群抽样和阶段抽样。简单随机抽样即纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可，本例为15。分类抽样即类型抽样，先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合。整群抽样，先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。可以看出，本例所使用的编号输入方法，只适用于等距抽样和简单随机抽样。3指定输出方向在“输出选项”框中有三种输出去向。在“输出

6、区域”框中输入总体单位编号所在的单元格区域。在本例中，输入区域为$A$12。也可以通过选择“新工作表”或“新工作簿”将抽样结果放在新工作表或新工作薄中。第四步：单击“确定”按钮后，在指定的位置给出抽样的结果。如图11-4所示。图11-4 等距抽样结果 二、用Excel整理数据通过统计调查得到的数据是杂乱的，没有规则的，因此，必须对搜集到的大量的原始数据加工整理，经过数据分析得到科学结论。统计整理包括对数据进行分类汇总并计算各类指标及利用统计图或统计表描述统计汇总结果等。Excel提供了多种数据整理工具：频数分布函数与直方图分析工具等。例11-2 根据抽样调查，某月某市50户居民购买消费品支出资

7、料如图11-5所示。（单位：元）图11-5 居民购买消费品月支出对其按800900、9001000、10001100、11001200、12001300、13001400、14001500、15001600、1600以上分为9个组。1频数分布函数Excel提供了一个专门用于统计分组的频数分布函数（FREQUENCY），它以一列垂直数组返回某个区域中的数据分布，描述数据分布状态。用频数分布函数进行统计分组的操作过程如下。首先，在使用此函数时，先将样本数据排成一列，本例中为A1：A50。然后，利用频数分布函数进行统计分组和计算频数，具体操作步骤如下：第一步：选定单元格区域，本例中选定的区域为D3：

8、D11，单击“插入”菜单，选择“函数”选项，弹出“插入函数”对话框，如图11-6所示。图11-6 插入函数对话框在“选择类别”中选择“统计”，在“选择函数”中选择“FREQUENCY”。如图11-7所示：图11-7 选择“FREQUENCY”对话框 第二步：打开“FREQUENCY”对话框，输入待分组数据与分组标志，如图11-8所示。图11-8 “FREQUENCY”对话框 在“FREQUENCY”对话框中有“Data_array”和“Bins_array”两个文本框。（1）Data_array用于计算频率的数组，或对数组单元区域的引用。本例中为A1：A50。 （2）Bins_array数据接

9、受区间，为一组数或对数组区间的引用，设定对Data_array进行频率计算的分段点。本例中为899、999、1099、1199、1299、1399、1499、1599、1699。 注意：频数分布函数要求按组距的上限分组，不接受非数值的分组标志（如“不足”或“以上”等）。在输入的数据两端必须加大括号，各数据之间用分号隔开。输入完成后，由于频数分布是数组操作，所以不能单击“确定”按钮。 第三步：按“Ctrl+Shift+Enter” 组合键，在最初选定单元格区域内得到频数分布结果，在本例中为D3：D11，如图11-9所示。图11-9 频数分布结果 至此，频数分布函数进行统计分析的功能就全部操作完成

10、了。2直方图分析工具与频数分布函数只能进行统计分组和频数计算相比，直方图分析工具可完成数据的分组、频数分布与累积频数的计、绘制直方图与累积折线图等一系列操作。仍以例11-2为操作范例，阐述直方图分析工具的统计整理功能，其操作过程如下。首先，将样本数据排成一列，最好对数据进行排序，本例中已利用排序操作排好序，为A1：A51。输入分组标志，本例中为B1：B10，分别是899、999、1099、1199、1299、1399、1499、1599、1699。如图11-10所示：图11-10 数据升序排列然后 ，利用直方图分析工具进行分析，具体操作步骤如下。 第一步：单击“工具”菜单，选择“数据分析”选项

11、；打开“数据分析”对话框，从“分析工具”列表中选择“直方图”选项，如图11-11所示。图11-11 “数据分析”对话框第二步：打开“直方图”对话框，确定输入区域、接收区域和输出区域，如图11-12所示。 图11-12 “直方图”对话框（1）“输入区域”输入待分析数据区域的单元格引用，若输入区域有标志项，则选中“标志”复选框；否则，系统自动生成数据标志。“接收区域”输入接收区域的单元格引用，该框可为空，则系统自动利用输入区域中的最小值和最大值建立平均分布的区间间隔的分组。本例中输入区域为$A$2:$A$51，接收区域为$B$2:$B$10。（2）在“输出”选项中可选择输出去向，输出去向类似于“抽

12、样”对话框的输出去向。本例中选择“输出区域”为$C$1。（3）选择“柏拉图”可以在输出表中同时按降序排列频数数据；选择“累积百分率”可在输出表中增加一列累积百分比数值，并绘制一条百分比曲线；选择“图表输出”可生成一个嵌入式直方图。第三步：单击“确定”按钮，在输出区域单元格可得到频数分布，如图11-13所示。图11-13 直方图频数分布结果 第四步：将条形图转换成标准直方图，如图11-14所示。具体做法：左键单击条形图的任一直条，再单击右键，在快捷菜单中选取“数据系列格式”，然后在“数据系列格式”对话框中选择选项标签，将间距宽度改为0，单击“确定”按钮即可。图11-14 标准直方图三、用Exce

13、l作统计图 传统的统计表格需要数据使用者自己精心地进行分析，而统计图显示资料则具有形象生动、一目了然的优点，通过图形可以方便地观察到数量之间的对比关系、总体的结构特征以及变化发展趋势。统计图在统计整理中的应用越来越广泛。Excel提供了大量的统计图形供用户根据需要和图形功能选择使用。Excel提供的图形工具有：柱形图、折线图、饼图、散点图、面积图、环形图、股价图等。各种图的作法大同小异，本章以柱形图为，介绍制作统计图的工作步骤。例11-3 根据我国1991-2003年国内生产总值构成数据制作相应的统计图。 第一步：创建工作表，即将统计资料输入到Excel中，如图11-15所示。图11-15 1

14、991-2003年国内生产总值构成数据 第二步：在想绘制图形的数据中选择任一单元格，然后在“插入”菜单中选择“图表”选项，或单击工具栏中的图表向导按钮，出现图11-16。图11-16 “图表向导-4步骤之1-图表类型”对话框第三步：选定图表类型。在弹出的“图表向导-4步骤之1-图表类型”对话框中选择所需要的图表的类型，此处在“图表类型”中选“柱形图”，在“子图表类型”中选第一个“簇状柱形图”，然后单击“下一步”按钮，如图11-17所示。图11-17 “图表向导-4步骤之2-图表源数据”对话框第四步：确定数据范围。在弹出的“图表向导-4步骤之2-图表源数据”对话框中规定数据区域。点击“系列”选项

15、，规定每一数据系列的名字和数值的区域，如图11-18所示。然后单击“下一步”按钮。图11-18 “图表向导-4步骤之2-图表源数据”数据区域选项第五步：选用图表选项。在弹出的“图表向导-4步骤之3-图表选项”对话框中可给图表命名，将“分类（X）轴”命名为“年份”；将“数值（Y）轴”命名为“百分比（%）”。然后，单击“下一步”按钮，如图11-19年示。图11-19 “图表向导-4步骤之3-图表选项”对话框 第六步：选择图表位置并显示结果。在弹出的“图表向导-4步骤之4图表位置”对话框中可为图表选择保存位置，或放在独立的工作表中，或作为一个对象放在当前工作表中。然后，单击“完成”按钮。本例中选择了

16、放在独立的工作表中，如图11-20所示。图11-20 1991-2003年国内生产总值构成图表其他图形的绘制步骤与柱形图的绘制步骤类似，只是在图形类型中选择不同的图形类型即可。第二节 用Excel计算描述统计量Excel中用于计算描述统计量的方法有两种，函数方法和描述统计工具的方法。一、用函数计算描述统计量常用的描述统计量有众数、中位数、算术平均数、调和平均数、几何平均数、极差、四分位差、标准差、方差、标准差系数等。一般来说，在Excel中求这些统计量，未分组资料可用函数计算，已分组资料可用公式计算。这里我们仅介绍如何用函数来计算未分组资料的描述统计量。用函数运算有两种方法：一是手工输入函数名

17、称及参数。这种输入形式比较简单、快捷。但需要非常熟悉函数名称及其参数的输入形式。所以，只有比较简单的函数才用这种方法输入；二是函数导入法。这是一种最为常用的办法，它适合于所有函数的使用，而且在导入过程中有向导提示，因而非常方便。函数的一般导入过程为：点菜单“插入”；找“函数”，此时出现一个“插入函数”对话框；在对话框的“选择类别”中确定函数的类别（如常用函数或统计）；在“选择函数”内确定欲选的函数名称，如SUM、MODE等；点“确定”后即可出现该函数运算的对话框向导，再按向导的提示往下运行即可。下面介绍统计中常用指标的函数运算方法。1众数 例11-4 为了解某单位职工的工资收入情况，随机抽取3

18、0人月工资如下：图11-21 职工月工资收入用函数方法求众数，应先将30个人的工资数据输入A1：A30单元格。（1）手工输入函数名称及参数单击任一单元格，输入“=MODE（A1：A30）”，回车后即可得众数为1560。如图11-22所示。图11-22 手工输入函数名称及参数（2）函数导入法点菜单“插入”；找“函数”，此时出现一个“插入函数”对话框；在对话框的“选择类别”中确定函数的类别“统计”；在“选择函数”内确定欲选的函数名称“MODE”，如图11-23所示。图11-22 函数导入法“插入函数”对话框点“确定”后即可出现该函数运算的对话框向导，在Number1处输入A1：A30或选择Exce

19、l中的A1：A30区域，如图11-23所示。按“确定”，在Excel中即得到众数1560。图11-23 函数导入法“函数参数”对话框运用函数导入法，只要知道每个函数的涵义，即可按上述程序得到相应的运算结果，为节约篇幅这里不再一一讲解，下面仅列示各函数的涵义及本例中的运行结果。2中位数单击任一空白单元格，输入“=MEDIAN（A1：A30）”，回车后得中位数为1550。3算术平均数单击任一空白单元格，输入“=AVERAGE（A1：A30）”，回车后得算术平均数为1531.666667。4几何平均数单击任一空白单元格，输入“=GEOMEAN（A1：A30）”，回车后得几何平均数为1526.3。5调

20、和平均数单击任一空白单元格，输入“=HARMEAN（A1：A30）”，回车后得调和平均数为1521.06。6截尾平均数将数据按由小到大顺序排列后，因数据两端值不够稳定，按一定比例去掉头尾两端一定数量的观察值，然后再求平均，这样得到的平均数就是截尾平均数。如果按6/30，即从30个数据中去掉最大的三个值和最小的三个值，再求平均数。单击任一空白单元格，输入“=TRIMEAN（A1：A30，6/30）”，回车后得截尾平均数为1526.257全距单击任一空白单元格，输入“=MAX（A1：A30）-MIN（A1：A30）”，回车后得全距为730。8标准差单击任一空白单元格，输入“=STDEV（A1：A3

21、0）”，回车后得标准差为132.5371。9标准差系数单击任一空白单元格，输入“=STDEV（A1：A30）/AVERAGE（A1：A30）”，回车后得标准差系数为008653110偏度系数单击任一空白单元格，输入“=SKEW（A1：A30）”，回车后得偏度系数为0.914565。11峰度系数单击任一空白单元格，输入“=KURT（A1：A30）”，回车后得峰度系数为3.808279。二、描述统计菜单项的使用仍使用上面的例子，我们已经把数据输入到A1：A30单元格，然后按以下步骤操作：第一步：在工具菜单中选择数据分析选项，从其对话框中选择描述统计，按确定后打开描述统计对话框，如图11-24所示。

22、图11-24 描述统计对话框第二步：在输入区域中输入$A$1:$A$30，在输出区域中选择$C$13，其他复选框可根据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均数估计总体平均数的置信区间；第K大值和第K小值会给出样本中第K个大值和第K个小值。第三步：单击确定，可得输出结果，如图11-25所示。图11-25 描述统计输出结果上面的结果中，平均指样本均值；标准误差指样本平均数的标准差；中值即中位数；模式指众数；标准偏差指样本标准差，自由度为n-1；峰值即峰度系数；偏斜度即偏度系数；区域实际上是极差，或全距；可以看出与我们前面用函数计算的结果完全相同。最大值为

23、1980，最小值为1250，第三个最大值为1690，第三个最小值为1350。第三节 用Excel进行时间序列分析一、测定增长量和平均增长量例11-5 根据我国1998年2003年社会消费品零售总额，计算逐期增长量、累计增长量和平均增长量。如图11-26所示。图11-26 用Excel计算增长量和平均增长量资料及结果计算步骤如下：第一步：在A列输入年份，在B列输入社会消费品零售总额。第二步：计算逐期增长量，在C3中输入公式：“=B3-B2”，并用鼠标拖曳将公式复制到C3：C7区域。第三步：计算累计增长量，在D3中输入公式：“=B3-$B$2”，并用鼠标拖曳将公式复制到D3：D7区域。(英文输入法

24、状态，按下“shift”和“4”组合键)第四步：计算平均增长量（水平法），在C10中输入公式：“=（B7-B2）/5”，按回车键，即可得以平均增长量。二、测定发展速度和平均发展速度例11-6 以1998年2003年社会消费品零售总额为例，说明如何计算定基发展速度、环比发展速度和平均发展速度。如图11-27所示。图11-27 用Excel计算发展速度和平均发展速度资料及结果第一步：在A列输入年份，在B列输入社会消费品零售总额。第二步：计算定基发展速度，在C3中输入公式：“=B3/$B$2”，并用鼠标拖曳将公式复制到C3：C7区域。第三步：计算环比发展速度，在D3中输入公式：“=B3/B2”，并用

25、鼠标拖曳将公式复制到D3：D7区域。第四步：计算平均发展速度（水平法），选中C9单元格，单击插入菜单，选中函数选项，出现插入函数对话框后，选择GEOMEAN（返回几何平均值）函数，在数值区域中输入D3：D8即可。三、计算长期趋势影响时间数列各项数值变动的因素是多方面的，主要有4种：长期趋势（T）、季节变动（S）、循环变动（C）、不规则变动（I）。本书主要就长期趋势与季节变动进行分析。以直线趋势说明长期趋势的测定与预测方法。而测定直线趋势的方法主要采用移动平均法。移动平均法按一定的间隔逐期移动，计算一系列动态平均数，从而形成一个由动态平均数组成的新的时间数列，修匀原时间数列，显示出长期趋势。在E

26、xcel中，使用移动平均法测定长期趋势，可以利用公式或AVERAGE函数，也可利用Excel提供的“移动平均”工具。由于公式或函数方法前面已讲过，而且只能获得数据，不能直接获得长期趋势图，因此长期趋势主要以“移动平均”工具来计算分析。例11-7 根据我国19902003年的国内生产总值的数据（单位：亿元），如图11-28所示，用移动平均法计算预测我国国内生产总值的长期发展趋势。图11-28 19902003年国内生产总值数据表第一步：单击“工具”菜单，选择“数据分析”选项。打开“数据分析”对话框，从其“分析工具”列表中选择“移动平均”选项，单击“确定”按钮，打开“移动平均”对话框。图11-29

27、所示。图11-29 “移动平均”对话框第二步：确定输入区域和输出区域，选中“图表输出”复选框。如图11-29所示。第三步：单击“确定”按钮后，在指定位置给出移动平均计算结果，如图11-30所示。图11-30 移动平均法计算结果四、计算季节变动季节变动是指在一年以内，受自然季节和社会习俗等因素影响而发生的有规律的、周期性的变动。测定季节变动的方法有两种：按月（季）平均法和移动平均趋势剔除法。1、按月平均法例11-8 某啤酒厂近五年全年及分月啤酒销售量数据如图11-31所示。结合五年分月数据，利用Excel按月平均法测定季节变动。图11-31 某酒厂近五年全年及分月啤酒销售量第一步：按已知数据资料

28、列出计算表，将各年同月的数值列在同一列内。第二步：计算各年合计与各年同月数值之和。计算每年的啤酒销量总数：单击N3单元格，输入“=SUM（B3：M3）”，并用鼠标拖曳将公式复制到N4：N7区域，得各年销量总数；计算各年同月销售总数：单击B8单元格，输入“=SUM（B3：B7）”，并用鼠标拖曳将公式复制到C8：N8区域，得各年同月销量总数与全部销量之和。 第三步：计算同月平均数与总的月平均数。计算同月平均数：单击B9单元格，输入“=B8/5”，并用鼠标拖曳将公式复制到C9：M9区域；计算总的月平均数：单击N9单元格，输入“=N8/60”回车得结果为43.21667。 第四步：计算季节比率。单击B

29、10单元格，输入“=B9*100/43.21667”，并用鼠标拖曳将公式复制到C10：M10区域。 第五步：计算季节比率之和，绘制季节变动曲线。单击N10单元格，输入“=SUM（B10：M10）”，回车得季节比率之和为1200。根据季节比率，可绘制季节变动曲线。图11-32 按月平均法分析季节变动数据图2移动平均趋势剔除法。直接用按月平均法忽略了长期趋势的影响，因此得出的季节比率不够精确。为了弥补这一缺点，可以采用移动平均趋势剔除法来测定季节变动。利用移动平均趋势剔除法分析季节变动有两种方法：乘法型时间数列季节变动分析和加法型时间数列季节变动分析。下面仍以例11-8的资料，采用乘法型时间数列变

30、动分析说明移动趋势剔除法的操作方法。 第一步：输入各年季度数据资料，如图11-33所示。图11-33 某酒厂近五年全年及分季啤酒销售量第二步：计算四个季度的移动平均数。计算移动平均数，可以采用移动平均工具，也可以使用公式与函数。移动平均工具在前面内容中已讲过，本例采用公式与函数方法来计算。单击D4单元格，输入“=AVERAGE（C2：C5）”，并用鼠标拖曳将公式复制到D5：D20区域。第三步：移正平均。因为本例是偶数项移动平均，所以还需将四项移动平均值再进行两项“移正”平均，如果是奇数项移动平均，则该步骤省去。单击E4单元格，输入“=AVERAGE（D4：D5）”，并用鼠标拖曳将公式复制到E5

31、：E19区域。 第四步：消除长期趋势。本例采用乘法模型，因此，将原数列除以趋势值以消除长期趋势。单击F4单元格，输入“=C4*100/E4”，并用鼠标拖曳将公式复制到F5：F19区域。图11-34 移动平均及剔除趋势数据 第五步：计算季节比率。将图11-34中Y/T*100得到的数据重新编排，得到如图11-35中左边数据表中前五行的基本数据（本例将图11-34中Y/T*100的数据四舍五入，保留两位小数）。然后利用按月平均法计算季节比率，具体步骤参见月平均法，本例省略，最终结果如图11-35所示。图11-35 利用移动平均趋势剔除法分析季节变动数据图第四节 用Excel进行指数分析指数分析法是研究社会经济现象数量变动情况的一种统计分析法。指数有总指数与平均指数之分，在这一节我们介绍如何用Excel进行指数分析与因素分析。一、用Excel计算总指数例11-9 图中是某企业甲、乙、丙三种产品的生产情况，以基期价格P作为同度量因素，计算生产量指数。如图11-36所示。图11-36 用Excel计算总指数资料及结果计算步骤：第一步：计算各个p0q0：在G2中输入“=C2*D2”，并用鼠标拖曳将公