分类统计函数Subtotal和Aggregate应用技巧解读

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

一、分类汇总函数：Subtotal，返回指定区域的分类汇总结果。功能：返回列表或数据库中的分类汇总。语法结构：=Subtotal(汇总方式,数据区域1,[数据区域2]……[数据区域254])。其中【汇总方式】分为1~11（包含隐藏值）和101~111（忽略隐藏值）两大类。具体功能请参阅下表。

（一）、分类汇总函数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”的一个共同功能是“忽略隐藏行”。

（二）、分类汇总函数Aggregate：忽略错误值隐藏汇总。目的：忽略错误值并对“可见”区域汇总。方法：在目标单元格中输入公式：=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,7,E3:E9)。解读：1、数据区域中的值包含一个错误值“#N/A”， 此时用Sum或Subtotal函数无法完成汇总任务。所以必须对错误值#N/A 忽略，所以用Aggregate函数替代Subtotal或Sum函数。2、上述公式中的忽略代码“3”、“7”的一个共同特点“忽略隐藏行、错误值”

（三）、分类汇总函数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”的一个共同功能是“忽略隐藏行”。

（四）、分类汇总函数Aggregate：忽略错误值筛选汇总。目的：忽略错误值并对“可见”区域汇总。方法：在目标单元格中输入公式：=AGGREGATE(9,3,E3:E9)、=AGGREGATE(9,7,E3:E9)。解读：1、数据区域中的值包含一个错误值“#N/A”， 此时用Sum或Subtotal函数无法完成汇总任务。所以必须对错误值#N/A 忽略，所以用Aggregate函数替代Subtotal或Sum函数。2、上述公式中的忽略代码“3”、“7”的一个共同特点“忽略隐藏行、错误值”

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