Excel多种数据汇总方法分析

摘要：Excel提供了多种数据汇总方法，可以对数据列表中所包含的大量数据进行汇总并加以分析，从而提炼出有助于决策的信息。通过实例，介绍了几种常用的数据汇总方法的具体应用，并指出了各自的特点，可以帮助用户在实践中高效地进行数据分析。

关键词：Excel；数据汇总；数据分析

中图分类号： TP317.1 文献标识码：A 文章编号：1009-3044（2013）04-0802-03

The Analysis of Variety of Data Aggregation Methods in Excel

LI Hua

（Computer and Network Center， Communication University of China， Beijing 100024， China）

Abstract： Excel provides a variety of data aggregation methods to summarize and analyze large amounts of data. The valuable information can be extracted by these methods to help make decision. This paper introduced several general applications of data aggregation method by detail examples and pointed out the characteristics of each method. It can help users analyze data more efficient in working practice.

Key words： Excel； data aggregation； data analysis

Excel软件已广泛应用于各个行业，无论是哪一个行业，只要和数据打交道，Excel几乎是首选的工具。数据分析是Excel提供的主要功能之一，而数据汇总又是主要的数据分析工具，可以对数据进行由粗到细、由多到少的处理，为管理人员做好决策提供有用的参考信息。Excel提供了多种数据汇总方法，常用的汇总方法有“分类汇总”、“数据透视表”、“合并分析”、“模拟运算表”等。

1 分类汇总

分类汇总是将数据按照某个关键词段分类，并对关键词段值相同的记录进行汇总的方法。以下面图1“考勤应扣款计算表”为例子，用分类汇总计算每个部门的基本工资的总和以及扣款合计的总和。

操作如下：先按部门排序（和排序次序无关，可以升序排序也可以降序排序），然后单击“数据”选项卡的“分类汇总”命令，在“分类汇总”对话框中选择分类字段为部门，汇总方式为求和，汇总项为基本工资和扣款合计，将得到所需要的汇总结果。

分类汇总的特点：

1）以三级结构的形式显示出汇总结果和明细数据，既可只显示汇总数据，也可以根据需要显示明细数据。

2）不适合进行多级汇总分析。当分类字段增加或对某一个被汇总字段进行多种不同的汇总时，需要完成多级分类汇总，而分级结构的层数增加使得表的结构复杂。如在“考勤应扣款计算表”中计算不同部门、不同职位的基本工资的最大值时或计算不同部门基本工资的最大值和最小值时，需要做多级分类汇总。

3）不能直接“剥离”汇总结果。若要复制汇总结果，简单的操作方式是单击分级显示符号数字2后，选择汇总结果，在“定位条件”对话框中选择“可见单元格”，进行复制粘贴到目标区域。

4）当数据源需要更新时，只能在“分类汇总”对话框中选择“删除分类汇总”，重新完成分类汇总。

2 数据透视表

数据透视表是Excel提供的可用来快速汇总大量数据的工具，可交互式地进行数据的分析，被公认为是Excel最强大的数据分析工具。

操作如下：单击目标单元格，选择“插入”选项卡的“数据透视表”命令，在“创建数据透视表”命令中选择数据源和数据透视表的位置，在“数据透视表字段列表”窗格中设置筛选字段、行标签字段、列标签字段、数字字段以及汇总方式。

数据透视表的特点：

1）运算速度快，与函数相比，计算速度提升很快，可以认为是Excel计算速度最快的功能。

2）布局结构非常灵活，修改非常方便。可以用不同的角度对数据进行汇总，可以对多个汇总字段进行汇总，也可以对同一个被汇总字段设置不同的汇总方式，还可以增加计算字段或计算项。

3）数据透视表的数据源可以是数据列表，也可以是数据库等外部数据源，也就是说可以直接根据外部数据源创建数据透视表，这是其他汇总方式所没有的特点。

4）数据透视表不能自动更新，当需要更新时，需单击数据透视表工具中的“刷新”按钮。

数据透视表功能非常强大，是用户首选的汇总工具，但使用时也有一些缺陷。如：

1）Excel 2007开始，数据透视表创建时不能以多重合并计算数据区域为数据源。若需要使用此项功能，只能自定义功能区，自行添加“数据透视表和数据透视图向导”按钮，并单击此按钮创建数据透视表。

2）数据透视表对数据透视图的格式有一定的限制。如数据透视图的图表类型不能是XY散点图、股价图及气泡图等，当对数据透视表进行筛选或刷新时，数据透视图的有些自定义格式会受到影响。

3 合并计算

合并计算是大家不太熟悉的一个汇总工具。以图1为数据源，利用合并计算工具计算每个部门的基本工资的总和以及扣款合计项目的总和。

操作如下：先用鼠标单击目标单元格，再单击“数据”选项卡的“合并计算”按钮，在“合并计算”对话框中设置参数，如图2所示，得到的合并计算的结果如图3所示。

合并计算的特点：

1）选中“创建指向源数据的链接”复选框，结果以二级分级结构的形式显示出汇总结果和明细结果，且计算结果随着数据源的改变自动更新。若没有选中此复选框，结果将以二维表格的形式显示，不能显示明细数据，结果也不能随着数据源的改变自动更新。

2）合并计算效果有一点类似于一级分类汇总，但合并计算比分类汇总方便，不需要排序，且汇总结果和数据源是分开的。

3）适合于汇总多个独立的数据列表，可计算同一工作簿不同工作表或不同工作簿不同工作表中的数据，前提条件是这些数据所在数据列表具有相同标签，而且合并计算前工作簿文件需要打开。如汇总12个月每个部门基本工资的总和以及扣款合计的总和，而每个月的明细数据在不同的工作表内。操作时，选择目标单元格，在“合并计算”对话框中将每一张工作表的数据区域添加到引用区域中，标签位置选择“首行”和“最左列”。

操作时注意，当创建链接时，存放合并计算结果的区域不能位于数据源所在的工作表中。

4 模拟运算表（也称数据表）

模拟运算表是Excel提供的假设分析的数据分析工具，可以分析模型中参数值的改变对结果的影响。如果要考察一个参数值的改变对结果的影响，则可以使用单变量模拟运算表。如果考察两个参数值的改变对结果的影响，则可以使用双变量模拟运算表。在实践中，也可以用模拟运算表进行数据汇总。

下面以图1为数据源，用双变量模拟运算表汇总不同部门、不同职位的人数。这时部门可能的值和职位可能的值就是两个可变的参数值，公式中用数据库函数计算某一个部门某一个职位的人数。

操作如下：在数据列表区域外，创建数据库函数的条件区域。设条件区域为A18：B19，其中作为条件值的A19单元格和B19单元格的值各自输入某一部门和某一职位的值，这两个单元格在模拟运算表中是两个变量参数。选择某一目标单元格A22，输入数据库函数=DCOUNT（A2：F16， “基本工资”，A18：B19），并以A22为交叉点，在连续的行区域和列区域中输入部门和职位可能的值，在“模拟运算表”对话框的“输入引用行的单元格”中输入$B$19，“输入引用列的单元格”中输入$A$19，如图4所示。

若只需汇总每个部门的基本工资的总和以及扣款合计的总和，则可以利用单变量模拟运算表计算，公式中可以使用SUMIF函数或DSUM函数。

模拟运算表的特点：1）模拟运算表需自行设计，公式和参数的位置以及公式的内容等。结果区域为数组，不可以单独修改某一个汇总数据。2）布局上不如数据透视表灵活，但可以用来描述和分析被汇总字段的值随着汇总参考字段也就是分类字段的值变化的函数关系。当使用数据库函数进行分类统计时，不需要建立多个不同的条件区域，只需要一个条件区域即可。3）数据源改变时，结果也会自动更新。

5 结论

本文介绍的Excel数据汇总方法，各自有不同的特点，适合于不同的情况。即使是数据透视表工具也不是万能的，也有局限性。因此，在实践中用户应从实际需求出发，综合考虑运算速度、布局、数据更新、数据的图形表示等方面的因素，合理选择最适合的工具，才能达到最好的效果。

参考文献：

[1] 陈景惠. 对Excel数据汇总教学方法的探讨[J]. 硅谷，2009（4）：166：167.

[2] 王兴德. 面向决策的Excel高级数据处理[M]. 北京：清华大学出版社，2009.

[3] Excel Home. Excel数据透视表应用大全[M]. 北京：人民邮电出版社，2010.