Excel数据多级分类汇总应用

摘  要：文中对Excel数据分类汇总进行探索，运用7个Excel内置函数组合成数据筛选公式和汇总公式，实现数据分类汇总的自动化应用，计算生成统计汇总表，解决数据的多级分类汇总应用。此外，这种函数组合方式也带来一种思考：函数组合应用有类似编程的效果。文章对上述思考进行了细致的分析与验证，在不借助编程的情况下，实现了数据分类汇总的自动化应用。

关键词：数据汇总;数据分类汇总;数据多级分类汇总

Abstract：In this paper，Excel data classification and aggregation are explored. Seven excel built-in functions are combined to form data filtering formula and aggregation formula. The automatic application of data classification and aggregation is realized，and the statistical aggregation table is calculated and generated to solve the multi-level classification and aggregation application of data. In addition，this kind of function combination method also brings a kind of thinking：the function combination application has the similar programming effect. This paper makes a detailed analysis and verification of the above thinking，and realizes the automatic application of data classification and aggregation without the aid of programming.

Keywords：data aggregation;data classification and aggregation;data multi-level classification and aggregation

0  引  言

Excel是常用的电子表办公软件，它表面是制作电子表格的工具，其实还有强大的计算功能，通过内置的函数扩展了其功能应用，可以完成许多复杂的数据运算，是管理公司用户和个人财务统计数据、绘制各种专业化表格等工作的得力助手。笔者长期从事与林业相关的数据处理工作，如二类调查、编制森林经营方案、项目规划、调查设计和检查验收等数据的处理工作，工作中发现Excel只提供了简单的数据分类汇总功能，而对数据的多级分类汇总，却没有提供直接支持，只能通过一步步筛选记录来统计数据或用编程方式解决该问题。笔者经过一段时间的摸索，对Excel内置函数进行分析研究，终于利用相关函数组合成筛选公式和汇总公式，生成统计汇总表，实现数据分类汇总的自动化应用。现在把函数组合汇总数据的方法整理，提供一种解决问题的思路，以期对同行或有数据多级分类汇总需求的用户有所帮助。下文对数据分类汇总的概念做陈述，以及介绍公式的函数组合方式和录入步骤，最后验证数据汇总的正确性。各函数的详细语法和举例应用，限于篇幅，文中未做详述，可以通过百度搜索或相关书籍中查看，当然，该方法可能也存在一定缺陷，在今后的工作中会进一步对该方法存在的不足进行研究。

1  提出问题

Excel数据汇总：对表数据进行累加或汇总;

Excel数据分类汇总：对表数据的某个字段或列，按类别来统计数据;

Excel数据多级分类汇总：涉及表数据的多个字段（或列）、字段（或列）的多个类别，进行数据分类统计汇总，举例：

一级分类汇总（简称“一级汇总”）：如某林场要统计各[工区][面积];

二级分类汇总（简称“二级汇总”）：如某林场要統计各[工区]分[起源]面积;

三级分类汇总（简称“三级汇总”）：如某林场要统计各[工区]分[起源]分[龄级]面积;

四级分类汇总（简称“四级汇总”）：如某林场要统计各[工区]分[起源]分[龄级]分[立地质量等级]面积;

五级分类汇总、数据六级分类汇总，依次类推。

用自动筛选的方法，统计数据所需次数的情况为（以工区10个，起源2种，龄级5个，立地质量等级4个为例），一级汇总统计次数：10次;二级汇总统计次数：10×2=20次;三级汇总统计次数：10×2+2×5=30次;四级汇总面积的统计次数：10×2+2×5+5×4=50次;五级、六级统计的次数将会更多。

从上文统计次数据可以看出，统计数据量少时，一级汇总的工作量尚可接受，二级汇总时，统计次数明显增多，三级汇总时统计次数便达到100次，工作量过大;以上列出的统计次数只是获得结果的次数，其实操作过程中步骤更加烦琐，并且有的数据不能直接生成表，效率低下。由此，笔者产生了一个设想：能否通过Excel函数组合成公式，在单元格中输入公式，实现数据的分类汇总？

2  分析问题

2.1  数据分类汇总的过程分析

Excel内置函数功能非常强大，通过组合方式可以方便、快速、有效地解决数据的多级分类汇总的问题。以三级汇总为例，对比汇总数据前的表和汇总数据后的表，进行分析实现过程。

2.1.1  必须先筛选出符合多条件的不重复记录

如表1所示，同时具有相同字段“乡镇”“培育树种”“作业类型”类别的各有2条记录，分类汇总之后，只需要保留各一行记录，如表2所示。

2.1.2  要对符合多条件的记录进行面积汇总

如表1所列的块号A、B的作业面积“3.3”“2.0”汇总到表2所列的序号1的作业面积“5.3”中，表1所列的块号C、D的作业面积“4.0”“3.3”汇总到表2所列的序号2的作业面积“7.3”中。

通过对上面数据多级分类汇总过程简单分析，汇总过程分为两个步骤：第一步，先用函数组合公式筛选出符合多条件的不重复记录;第二步，用函数组合公式对符合多条件的记录进行数据汇总。

2.2  函数组合应用

2.2.1  函数组合应用将用到7个函数

（1）index（）函数，语法：INDEX（array，row_num，column_num）;

（2）small（）函数，语法：SMALL（array，k）;

（3）match（）函数，语法：MATCH（lookup_value，lookup _array，match_type）;

（4）sum（）函数，语法：SUM（number1，number2，

（5）offset（）函数，语法：OFFSET（reference，rows，cols，height，width）;

（6）if（）函数，语法：IF（logical_test，value_if_true，value_if_false）;

（7）row（）函数，语法：ROW（reference）。

2.2.2  函数组合应用：筛选公式和汇总公式

（1）筛选公式：“=INDEX（xyz0，SMALL（IF（MATCH（xyza&"|"&xyzb&"|"&xyzc，xyza&"|"&xyzb&"|"&xyzc，0）=ROW（xyzb），4^8），ROW（xyz1）））”;用于筛选记录，筛选出符合多条件的不重复记录，其中，xyz0、xyza、xyzb、xyzc、xyz1为变量。

说明：SMALL（）、IF（）、MATCH（）组合返回同时符合三条件：yza&"|"&xyzb&"|"&xyzc的最小当前值，用于筛选不重复记录，如得到表1所列的相同名称（“乡镇”“培育树种”“作业类型”）的排序的第一条记录：“AAA镇、香樟、改培”，向下填充得到第二条记录：“BBB镇、火力楠、新造”，直到出现“0”为止，完成所有不重复记录筛选，此公式功能是筛选出不重复记录;

（2）汇总公式：“=SUM（IF（（xyza2=三级汇总！A2）* （xyzb2=三级汇总！B2）*（xyzc2=三级汇总！C2），（xyzd2）， 0））”;用于第二步数据汇总，对符合多条件的记录进行数据汇总，其xyza2、xyzb2、xyzc2、xyzd2为变量，而“三级汇总！A2”“三级汇总！B2”“三级汇总！C2”为单元格引用。

说明：对同时符合三条件：（xyza2=三级汇总！A2）、（xyzb2=三级汇总！B2）、（xyzc2=三级汇总！C2）的（xyzd2）列数据进行自动汇总，如表2所列汇总面积数据“5.3”，向下填充直到完成所有数据汇总，如“7.3”，此公式功能是汇总符合条件的数据。

小结：用筛选公式可以筛选出符合三个条件的不重复记录;用汇总公式可以对符合条件的数据进行汇总;二个公式可以实现数据分类汇总，汇总结果生成新表。

3  解决问题

上面公式看起来，奥斯简单，其实是为了公式的函数组合直观明了，把部分中间计算值或引用，采用变量的方法来分解处理了，下面用定义名称的方式输入公式，也以三级汇总为例。

3.1  新建命名表

建立两个Excel空表：命名为表一、三级汇总，如图1所示。

3.2  定义名称及输入引用

3.2.1  “定义名称”输入公式的方法、步骤

以Excel2003为例，讲解“定义名称”输入公式的方法、步骤（其他版本的相应操作差异不大）：

第一步：按顺序点击Excel主窗口菜单→插入→名称→定义，打开定义名称窗口;

第二步：“在当前工作簿中的名称”，输入定义名称，如：“xyz0”;

第三步：“引用位置”，输入公式，如：“=表一！A：A”;

第四步：点击添加，完成一项定义名称添加，最后点击确定，继续下一名称输入。

需要注意的是：输定义名称“unabc”前，先单击"三级汇总"表A1单元格，目的是与公式引用相对应。输定义名称“sum”前，先单击"三级汇总"表D2单元格，目的是与公式引用相对应;

3.2.2  定义名称及引用输入

（1）录入数据筛选公式：

B11，定义名称：“unabc”;引用位置输入：“=INDEX（xyz0，SMALL（IF（MATCH（xyza&"|"&xyzb&"|"&xyzc，xyza&"|"&xyzb&"|"&xyzc，0）=ROW（xyzb），4^8），ROW（xyz1）））”;

B12，定義名称：“xyz0”;引用位置输入：“=表一！A：A”;

B13，定义名称：“xyz1”;引用位置输入：“=表一！A1”;

B14，定义名称：“xyza”;引用位置输入：“=OFFSET（（表一！$A$1），0，0，COUNTA（表一！$A：$A），1）”;

B15，定义名称：“xyzb”;引用位置输入：“=OFFSET（（表一！$B$1），0，0，COUNTA（表一！$B：$B），1）”;

B16，定义名称：“xyzc”;引用位置输入：“=OFFSET（（表一！$C$1），0，0，COUNTA（表一！$C：$C），1）”;

需要注意：unabc为多条件筛选不重复记录公式（如"乡镇"、"培育树种"、"作业类型"）。

（2）录入数据汇总公式：

B21，定义名称：“sum”;引用位置输入：“=SUM （IF（（xyza2=三级汇总！A2）*（xyzb2=三级汇总！B2）*（xyzc2=三级汇总！C2），（xyzd2），0））”;

B22，定义名称：“xyza2”;引用位置输入：“=OFFSET （（表一！$A$2），0，0，COUNTA（表一！$A：$A），1）”;

B23，定义名称：“xyzb2”;引用位置输入：“=OFFSET （（表一！$B$2），0，0，COUNTA（表一！$B：$B），1）”;

B24，定义名称：“xyzc2”;引用位置输入：“=OFFSET （（表一！$C$2），0，0，COUNTA（表一！$C：$C），1）”;

B25，定義名称：“xyzd2”;引用位置输入：“=OFFSET （（表一！$D$2），0，0，COUNTA（表一！$D：$D），1）”;

公式输入完成后，如图2所示，可检查、更正输入的错误。

3.3  “三级汇总”表单元格输入公式

在“三级汇总”表中的A1、B1、C1、D1单元格中分别输入：“=unabc”，D2单元格输入：“=sum”，并按回车键，A1、B1、C1和D2单元格分别下拉（暂定30行，计算时，筛选或汇总结果出现值为“0”时为止），表中单元格输入公式后的情况如图3所示（不是按回车键后的结果）。小结：当“表一”输入完数据后，在“三级汇总”表中会自动汇总数据。

4  数据分类汇总结果验证和应用

在“表一”表中A1、B1、C1、D1单元格开始，各列添加数据，如表3所示。

“表一”输入完数据后（也可以复制粘贴数据到表中），打开“三级汇总”表，可以看到自动完成了数据的分类汇总，如表4所示。

5  函数组合扩展应用

5.1  “一级汇总”应用

使用“一级汇总”，比Excel内置的分类汇总功能更加方便实用，可直接生成统计表。

5.2  “二级汇总”应用

使用“三级汇总”的筛选条件改为2个即可。

5.3  “四级汇总”应用

“四级汇总”用4个条件，“五级汇总”用5个条件。简单的理解就是几级汇总就改为几个筛选条件就可以完成。

5.4  多列数据要求同时分别汇总应用

多列数据要求同时分别汇总应用可以使一列数据汇总做一个汇总公式，如“=sum”，多列要汇总数据做多个汇总公式，如“=sum1”“=sum2”“=sum3”等以此类推。

6  结  论

Excel只提供了简单的数据分类汇总功能，而对数据的多级分类汇总，没有提供直接支持，在工作中通过一步步筛选的方法，获得统计结果，效率低下。但同时，Excel提供了丰富的内置函数，运用函数组合方式，在不借助编程的情况下，完全可以解决复杂的数据分类汇总应用难题，实现数据分类汇总的自动化应用。本文验证了应用函数组合实现类似于编程功能的思路，希望可以为从事数据统计工作的相关人员提供借鉴。

参考文献：

[1] 凌弓创作室.妙“技”轻松学：Excel公式与函数实战经典技巧 [M].北京：科学出版社，2012.

[2] 邓芳.Excel高效办公：数据处理与分析：修订版 [M].北京：人民邮电出版社，2012.

[3] 周庆麟，胡子平.Excel数据分析思维、技术与实践 [M].北京：北京大学出版社，2019.

[4] 李东博.中文版EXCEL2007宝典 [M].北京：电子工业出版社，2008.

[5] 姬丽霞，张丽君.ExcelVBA高效办公全能手册 [M].北京：中国铁道出版社，2009.

作者简介：连春来（1970—），男，汉族，福建武平人，工程师，研究方向：数据处理（Visual FoxPro、Excel、ArcGIS等）。