在实际的数据统计分析中，经常会遇到很多复杂的因素，例如，对隐藏的行或计算结果返回错误类型的值不予统计等等……如果此时还用常规的Sum系列、Count系列、Average系列等函数去做数据统计分析，将会是难上加难或者根本无法完成。此时，如果要一个只对“可见”单元格或区域、忽略错误等类型进行统计分析的函数，将会是“雪中送炭”……今天，小编带大家了解一下万能的分类统计汇总函数Subtotal和Aggregate。

一、分类汇总函数：Subtotal，返回指定区域的分类汇总结果。

功能：返回列表或数据库中的分类汇总。

语法结构：=Subtotal(汇总方式,数据区域1,[数据区域2]……[数据区域254])。

其中【汇总方式】分为1~11（包含隐藏值）和101~111（忽略隐藏值）两大类。具体功能请参阅下表。

注意事项：

1、参数【汇总方式】必须为数值类型或可转换为数值的数据，且必须为1~11或101~111以内的数字，否则返回错误值“#VALUE!”。

2、如果计算的区域总存在隐藏行，使用代码1~11时，隐藏的行仍然在统计的范围内，如果使用101~111时，只对“可见”区域有效，暨忽略隐藏的行。

3、Subtotal函数对隐藏列区域无效，即如果统计的数据范围内包含隐藏的列，不管使用代码1~11还是101~111，这些隐藏的列数据仍然在统计的范围内。

4、【数据区域】只支持二维引用，不支持三维引用，否则返回错误值“#VALUE!”。

（一）、分类汇总函数Subtotal：隐藏汇总。

目的：对“可见”数据区域进行汇总。

方法：

在目标单元格中输入公式：=SUBTOTAL(109,E3:E9)。

解读：

1、从示例中可以看出，未“隐藏”之前，3种形式的计算结果是相同的，但“隐藏”之后，代码“109”的计算结果和其它2种的不同，原因在于代码“109”忽略隐藏行的数据，只对“可见”数据区域有效。

2、其它代码所对应的功能和求和的用法相同。

（二）分类汇总函数Subtotal：筛选汇总。

目的：按部门统计“年薪”。

方法：

在目标单元格中输入公式：=SUBTOTAL(9,E3:E9)、=SUBTOTAL(109,E3:E9)。

解读：

1、在筛选数据后，代码“9”和代码“109”的返回结果是相同的，而且为“可见”单元格的汇总数据。

2、其它代码所对应的功能和求和的用法相同。

（三）分类汇总函数Subtotal：经典用法之保持序号的连续性。

方法：

在目标单元格中输入公式：=SUBTOTAL(3,B$2:B2)或=SUBTOTAL(103,B$2:B2)。

解读：

代码“3”或“103”代表的函数为Counta，即非空单元格的个数。而对于筛选，代码“3”或“103”都是对可见单元格有效。所以用公式=SUBTOTAL(3,B$2:B2)或=SUBTOTAL(103,B$2:B2)都统计的是从当前单元格的上一单元格开始的非空单元格的个数。

二、分类汇总函数：Aggregate，返回指定区域的分类汇总结果。

功能：返回列表或数据库中的分类汇总。

语法结构：=Aggregate(汇总方式,忽略方式,数据区域1,[数据区域2]……[数据区域254])。

其中【汇总方式】为1~19之间的数字。具体功能请参阅下表。

【忽略方式】表示要在函数的计算区域中忽略那些值，该参数为0~7之间的数字。具体功能请参阅下表。

注意事项：

1、当【汇总方式】参数为14~19时，必须制定【数据区域2】的值，否则Aggregate函数将返回错误值“#VALUE!” 。

2、如果Aggregate函数的引用中包含嵌套的Aggregate和Subtotal函数，则将忽略这两个函数。

3、Aggregate函数适用于数据列或垂直区域，不适用于数据行或水平区域。

4、Aggregate函数必须在10及以上版本中使用。

（一）、分类汇总函数Aggregate：隐藏汇总。

目的：对隐藏后的“可见”数据区域进行汇总。

方法：

在目标单元格中输入公式：=AGGREGATE(9,1,E3:E9)、=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,5,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解读：

1、如果只是对隐藏后的“可见”区域进行求和，可以使用公式=SUBTOTAL(109,E3:E9)完成。

2、上述公式中的忽略代码“1”、“3”、“5”、“7”的一个共同功能是“忽略隐藏行”。

3、其它代码所对应的功能和求和的用法相同。

（二）、分类汇总函数Aggregate：忽略错误值隐藏汇总。

目的：忽略错误值并对“可见”区域汇总。

方法：

在目标单元格中输入公式：=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解读：

1、数据区域中的值包含一个错误值“#N/A”， 此时用Sum或Subtotal函数无法完成汇总任务。所以必须对错误值#N/A 忽略，所以用Aggregate函数替代Subtotal或Sum函数。

2、上述公式中的忽略代码“3”、“7”的一个共同特点“忽略隐藏行、错误值”。

3、其它代码所对应的功能和求和的用法相同。

（三）、分类汇总函数Aggregate：筛选汇总。

目的：对筛选后的“可见”数据区域进行汇总。

方法：

在目标单元格中输入公式：=AGGREGATE(9,1,E3:E9)、=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,5,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解读：

1、如果只是对筛选后的“可见”区域进行求和，可以使用公式=SUBTOTAL(9,E3:E9)或=SUBTOTAL(109,E3:E9)完成。

2、上述公式中的忽略代码“1”、“3”、“5”、“7”的一个共同功能是“忽略隐藏行”。

3、其它代码所对应的功能和求和的用法相同。

（四）、分类汇总函数Aggregate：忽略错误值筛选汇总。

目的：忽略错误值并对“可见”区域汇总。

方法：

在目标单元格中输入公式：=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,7,E3:E9)。

解读：

1、数据区域中的值包含一个错误值“#N/A”， 此时用Sum或Subtotal函数无法完成汇总任务。所以必须对错误值#N/A 忽略，所以用Aggregate函数替代Subtotal或Sum函数。

2、上述公式中的忽略代码“3”、“7”的一个共同特点“忽略隐藏行、错误值”。

3、其它代码所对应的功能和求和的用法相同。

结束语：

从上述的示例中可以看出，Subtotal函数和Aggregate函数都是对指定的区域或数据库进行分类汇总，其中Subtotal函数在汇总的时候不能进行嵌套，同时数据源中不能有错误类型值，而Aggregate函数可以嵌套，如果数据源中有错误类型值，可以忽略不计。Subtotal函数“隐藏”和“筛选”是针对不同的代码而言的，而Aggregate函数只对“可见”区域有效，即“隐藏”和“筛选”都是同一个代码，例如求和的代码都为“9”。

Subtotal函数和Aggregate函数在学习时，数字代码较多，容易混淆，建议大家根据系统的“联想”功能有针对性的选择实用代码，不建议死记硬背哦！如果亲有更好的学习或使用技巧，欢迎在留言区留言讨论哦！

#我要上头条# #Excel函数公式#