Excel数据多级分类汇总应用

作者：***

来源：《现代信息科技》2020年第14期

摘 要：文中对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）”;

如表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）”;