Excel在统计整理中的应用

季凌云

DOI：10.19392/j.cnki.16717341.201720191

摘要：Excel函数库包含了种类众多的函数，在实际工作中，适当选取某些函数能显著降低工作量，提高工作效率，减少工作误差。本文以在统计整理工作中遇到的几个实际问题为例，详细阐述了Excel中Countif函数和Frequency函数在统计整理中的应用。

关键词：统计整理；统计函数；分配数列；统计图表

Excel是MicrosoftOffice软件的重要组件之一，它主要是以表格形式实现对数据进行处理、统计分析、绘制图表等功能，因其界面简明、功能全面、操作方便等特点而被广泛应用于统计、会计、金融、管理等领域。Excel函数库包含了统计、财务、文本、逻辑等种类众多的函数，在实际工作中，适当地选取某些函数能极大地提高工作效率，同时能有效地降低因人为因素而导致的误差。本文以在统计整理工作中遇到的几个实际问题为例，详细阐述Excel在统计整理中的应用。

一个完整的统计工作包括四个步骤：统计设计、统计调查、统计整理和统计分析。本文是在统计调查的基础上，利用Excel对统计资料进行整理，并对其进行简单地分析。

1 编制分配数列

分配数列是将总体按某一标志进行分组，并按一定顺序排列出每组的总体单位数，用其反映出总体单位数在各组中的分布状况的数列。按照分组标志性质不同，分配数列可以分为品质数列和变量数列，变量数列按照各组表现形式不同又可以分为单项式数列和组距式数列。

品质数列是按品质标志分组形成的分配数列，用来观察总体单位中不同属性的单位的分布情况，如人口按民族、性别等分组形成的数列就是品质数列；变量数列是按数量标志分组形成的分配数列，用来观察总体中不同变量值在各组的分布情况；单项式数列是指每组中的分组只用一个数值表示的变量数列，即一个变量值就代表一组，如家庭按照家庭人口数进行的分组；组距式数列是指数列中每一组由两个变量值所确定的一个数值范围来表示的变量数列，如学生成绩按照分数进行分组。

1.1 使用Countif函数编制品质数列和单项式数列

Excel函数库中的统计函数Countif（）适合编制品质数列和单项式数列，它主要用来计算单元格区域中满足给定条件的单元格的个数。

格式：Countif（Range，Criteria），其中range表示区域，即要计算单元格数目的区域，criteria表示条件，即单元格需要满足的条件。

实例1：某车间20名工人某日加工的零件个数见图1，现要求编制一个变量数列，反应工人加工零件的分布情况。

首先，单击存放第一组结果的单元格，如图2中I3，单击“插入函数”按钮，在“选择类别”框中找到“统计”，然后选择函数“Countif”，在“Range”框中选择所有数据所在的单元格区域“A1：E4”，且在行号和列号前加上$符号，表示绝对引用，即“$A$1：$E$4”，然后在“Criteria”框中，选择“7”所在的单元格H3，单击“确定”按钮，就得到了第一组结果3。

然后，选择数值3所在的单元格I3，按住填充柄向下拖拽至I7，这样就自动计算出加工零件个数分别为8、9、10、11的工人人数。至于“合计”项和“比重”项，使用Sum（）函数和创建公式的方法即可算出。计算结果如图3所示：

1.2 编制组距式数列

Excel函数库中的统计函数Frequency（）适合编制组距式数列。此外，还可以利用“数据分析”中“直方图”工具来完成分组、计算人数、比重、绘制直方图等操作。

实例2：某班50名学生“统计学原理”课程期末考试成绩如图4所示，现在要求编制变量数列，并对该数列做简单分析。

1.2.1 使用Frequency函数编制组距式数列

格式：Frequency（Data_array，Bins_array），其中Data_array表示需要计算单元格数目的区域，Bins_array表示数据接收区。

根据上述资料，首先应确定每一组的上限值，上限值是编制组距式数列的关键。上限值的确定其实就确定了每组的组距和组限。结合该问题的实际意义，确定各组上限值分别为59、69、79、89、100，即将这50个成绩分值分别归入0～59，60～69，70～79，80～89，90～100这5组中，将上限值输入到L2：L6中。

然后，选取结果存放的单元格区域M2：M6，单击“插入函数”按钮，在“选择类别”框中找到“统计”，选择函数“Frequency”，在“Data_array”框中选择所有数据所在的单元格区域A1：J5，在“Bins_array”框中，选择所有上限值所在的区域L2：L6，按住Ctrl+Shift+Enter组合键，即可获得各组相应的人数，见图5所示：

最后，使用求和函数Sum（）计算合计人数，通过创建公式计算出各组人数在总人数中所占的比重。计算结果如下：

1.2.2 使用数据分析编制组距式数列（以Excel2007为例说明）

如果在“数据”选项卡中没有“分析”这一组，首先要加载“分析工具库”。加载的方法是：单击“快速访问工具栏”中的“自定义快速访问工具栏”按钮，选择“其他命令”，打开“Excel选项”对话框，在左边“加载项”中选择“分析工具库”，然后选择“分析工具库”，单击下方的“转到”按钮，打开“加载宏”对话框，选择“分析工具库”，单击“确定”，这时在“数据”选项卡中就出现了“分析”组和“数据分析”按钮。

然后就可以用数据分析工具来编制组距式数列。选择“数据”选项卡中的“分析”组，选择“数据分析”按钮，打开“数据分析”对话框，从中选择“直方图”，单击“确定”，这时就打开了“直方图”对话框，见图7所示：

在“输入”中的“输入区域”选择所有分值所在的区域，在“接收区域”选择所有上限值所在区域；在“输出选项”中，选择“新工作表组”，同时勾选“柏拉图”、“累计百分率”和“图表输出”。其中“柏拉图”表示按降序排列所做的向上累计频率，“累计百分率”表示在直方图上添加累计频率折线，输入结果見图8所示。

2 绘制统计图

Excel中的统计图有多种，如柱形图、折线图、条形图、饼图、面积图、散点图等，下面以为实例2绘制条形图为例来说明统计图的绘制方法。

首先，选择单元格区域L1：N6，然后单击“插入”选项卡，选择“图表”组中的“柱形图”按钮，选择“二维柱形图”，在当前工作表中就插入了一张二维柱形图。

从该表可以看出，分数在70到90之间的学生人数占总人数的大部分，较接近正态分布，这说明此次试卷的难度相对学生的知识掌握程度合理，考试成绩比较理想。

综上所述，本文以两个实际问题为例，重点阐述了Excel在统计整理中的应用，即编制分配数列和绘制统计图表，其实，在后续的统计分析阶段，Excel函数还有更加广泛的应用。我们只要充分熟悉并掌握Excel函数的使用条件，结合具体问题灵活运用，就能大大提升工作效率并能保证统计结果的准确性。同时，本文中的两个问题还可以为高职类《计算机应用基础》课程教学提供两个比较实用的案例，启发学生在学习中举一反三，学以致用。

参考文献：

[1]范慧敏，王斌.统计学原理[M].清华大学出版社，2013.

[2]赵俊峰.Excel中函数的使用技巧与实例[J].太原城市职业技术学院学报，2010（7）：126127.