AGGREGATE函数忽略错误值或者隐藏值
SUBTOTAL函数1-11101-111自然整数包含隐藏值进行统计不包含隐藏值进行统计筛选状态下没有任何区别可见单元格
图1
图1-1
图1-2一、忽略错误值进行求和或计数
#N/ASUMIF函数
如图2所示，如果要忽略错误值进行计数，那么该如何统计呢？小包在Excel速成视频课程的第五节第六节中，给大家详细讲解了COUNT函数与COUNTA函数的区别，COUNTA对非空单元格进行计数统计，不管数据本身是数值型数据、文本型数据、错误值还是逻辑值，它都能进行统计！而COUNT函数只统计数值型数据，也就是说COUNT函数会自动忽略错误值。基于COUNT函数的这种特性，我们可以直接用COUNT函数对D列销量进行统计，其公式为：=COUNT(D2:D20)。也可以用COUNTIF函数忽略错误值进行计数，其第二参数条件判断与上文说的SUMIF函数一致，其公式为:=COUNTIF(D2:D20,"<>#N/A")，如果有多种错误值，可以借用COUNTIFS函数，其公式为：=COUNTIFS(D2:D20,"<>#n/a",D2:D20,"<>#ref!",D2:D20,"<>#null!")。当然，也可以直接用COUNT函数进行统计，它会自动忽略多种错误值！
图2同理，忽略多种错误值统计总销量，也可以用SUMIFS函数，如图2所示，其公式为：=SUMIFS(D2:D20,D2:D20,"<>#n/a",D2:D20,"<>#ref!",D2:D20,"<>#null!")。
如图2-2所示，求筛选状态下的产品个数，用count系列函数就做不到了，这里只能使用AGGREGATE函数或者SUBTOTAL函数，这里以SUBTOTAL函数为例，它会忽略筛选下的隐藏行数值，只统计可见单元格的数据，其公式为：=SUBTOTAL(3,C4:C18)，也可以写作：=SUBTOTAL(103,C4:C18)，结果都是11，需要注意的是，SUBTOTAL函数第一参数用3或者103表示计数函数。
图2-2二、筛选状态下忽略隐藏值进行求和
接下来，再看一个案例。图3是筛选所有销量大于35的数据，图4是未进行筛选操作的原数据。在图3的筛选状态下，如果要求产品总销量，直接用SUM函数，会统计到筛选后部分隐藏的数据。如图5所示，未筛选状态下，总销量为775，而筛选状态下的总销量却是672！这个672销量并不是筛选后可见单元格的销量，它包含隐藏的数据但又没有全部包含，而可见单元格的总销量为571。那么，在筛选状态下，如何忽略隐藏值只对可见单元格进行销量统计呢？前面已经讲过，直接用SUM函数不行，会统计到部分隐藏数据。这里我们需要用SUBTOTAL函数或者AGGREGATE函数，AGGREGATE函数第三参数提供忽略错误值或隐藏值的能力，后面小包会为小伙伴们专门讲解一下这个函数，这里就不多作介绍了。对于筛选后的数据来说，SUBTOTAL函数有一个重要的特性，它会忽略筛选下的隐藏行数值，只统计可见单元格的数据，如图7所示，其公式为：=SUBTOTAL(9,D4:D18)，也可以写作：=SUBTOTAL(109,D4:D18)，结果都是571。（SUBTOTAL函数中的第一个参数9或109都代表求和）
图3
图4
图5
图6
图7三、隐藏行或隐藏列后（非筛选）对数据进行计数或求和
如图8所示，未对数据样本进行任何隐藏行或列的操作，要进行销量求和以及产品个数统计，除了用SUM函数以及COUNTA函数这类常规用法外，还可以使用SUBTOTAL函数进行求和或计数，其求和公式为：=SUBTOTAL(9,D2:D20)，或者：=SUBTOTAL(109,D2:D20)，因为是未进行隐藏行的操作，因此9和109指代的求和函数并无统计差异！其计数公式为：=SUBTOTAL(3,C2:C20)，或者：=SUBTOTAL(103,C2:C20)。
图8如图9所示，我们手动隐藏11-13行，要求不统计隐藏行的数据，只统计可见单元格的数据。若使用SUM函数或者COUNTA函数都会默认统计到隐藏行的数据，这里可以使用SUBTOTAL函数，忽略隐藏行的数值进行求和，其公式为：=SUBTOTAL(109,D2:D20)，不忽略隐藏行的数值进行求和，其公式为：=SUBTOTAL(9,D2:D20)。忽略隐藏行的数值进行计数，其公式为：=SUBTOTAL(103,C2:C20)，不忽略隐藏行的数值进行计数，其公式为：=SUBTOTAL(3,C2:C20)。
图9总而言之，对于筛选状态下的数据分类汇总来说，SUBTOTAL函数第一参数无论是1-11区间数字还是101-111区间数字，都默认统计可见单元格数据，而对于隐藏行(非筛选方式）的数据分类汇总，1-11区间的数字指代的函数分类汇总时会包含隐藏值，101-111区间的数字指代的函数进行分类汇总则不会包含隐藏值。需要注意的是，手动隐藏列不会影响SUBTOTAL函数的结果，如图11和图12所示，对B2:C20区域进行计数，隐藏C列与否，最终计数结果一致，都是38。
图10
图11关注小包