亲们好：

下面这张图，是一个E友所提的问题：如何在C17:C19区域，根据A列班级筛选的结果（假设只有一个筛选结果），动态统计所筛选班级的考核情况。比如筛选二班，则统计二班的考核情况，筛选三班，则统计三班的考核情况……

http://ww4/large/005IRek6gw1f73paxm36vj309n0hu3zo.jpg

​

问题就是酱紫，怎么解决呢？   

​我们假设不是统计某个不确定筛选班级的考核情况，而是直接统计二班的考核情况，那这就是一个标准的条件计数题，相信很多小伙伴都会处理的：

=SUMPRODUCT(($A$2:$A$14="二班")*($C$2:$C$14=B17))

或者：

=COUNTIFS($A$2:$A$14,"二班",$C$2:$C$14,B17)

由此可见，这道题的关键在于，我们要知道，筛选的结果是哪个班级。

​知道问题所在，事儿就有门路了。

如何提取筛选的结果值？我们首先会想到SUBTOTAL函数，总所周知，它是这方面的行家。

SUBTOTAL函数只统计可见单元格的内容，通过给定不同的参数，可以完成计数、求和、平均值、乘积等多种汇总方式。

是的，SUBTOTAL不是查找引用函数，无法直接得出筛选的结果，但它可以作为一个桥梁。

比如我们把D列作为辅助列，D2输入下面的公式，并向下复制。

=SUBTOTAL(3,A$1:A2)-1

这个公式SUBTOTAL的第一个参数是3，告诉SUBTOTAL函数要执行的汇总方式是COUNTA。COUNTA函数用于计算区域中非空单元格的个数，用SUBTOTAL(3,区域)，就是计算区域中可见非空单元格的个数。

第二个参数A$1:A2，A$1使用了行绝对引用，当公式向下填充时会变成A$1:A3、A$1:A4、A$1:A5……也就是引用自A$1单元格到公式所在行的A列这样一个逐行递增的引用区域，来判断可见非空单元格的个数。

这个时候，我们再对A列班级进行任意筛选，会发现D列会自动生成始终保持连续的序号，而且，这个连续的序号，始终都是以1为开始的。

http://ww3/large/005IRek6gw1f73pbukx9tj30b604kt8v.jpg

​这里，也许会有小伙伴心生疑问，为什么D列的公式是SUBTOTAL(3,A$1:A2)-1，而不是SUBTOTAL(3,A$2:A2)？结果看起来是一样一样一样，何必费劲绕来绕去？！

这就要说到SUBTOTAL函数的坏脾气了，有图为鉴：

http://ww3/large/005IRek6gw1f73pc5fd2wj30ao05et8z.jpg

​上图D列的公式是=SUBTOTAL(3,A$2:A2)。在这种情况下，我们对班级进行筛选，比如筛选三班，结果却发现，一班的熊孩子王俊杰赖着不走；我们再筛选二班，一班的熊孩子王俊杰还是赖着不走……我靠，谁家的孩子这么牛叉，肉他。

这就是SUBTOTAL函数的坏脾气带来的后遗症，它为什么有这样的坏脾气？专家们也没有定论。我们只要记得在处理时，将SUBTOTAL函数的第二参数引用起始位置写成公式所在行的上一行，再将结果减1就可以了。

​通过上面的内容，我们知道D列SUBTOTAL函数生成的序列号，始终都是以1为开始的，那么，我们再提取A列动态筛选的结果，便是轻而易举的事儿了吧？——这简直是红太狼揍灰太狼，咣的一声平底锅，轻而易举了。

比如我们可以通过MATCH函数，判断1在D1:D14中的行号，再使用INDEX来取值：

=INDEX(A:A,MATCH(1,D$1:D$14,0))

得出了A列班级动态的筛选结果，再统计相关筛选班级的考核情况，也就水到渠成了：

=SUMPRODUCT(($A$2:$A$13=INDEX(A:A,MATCH(1,D$1:D$14,0)))*($C$2:$C$13=B17))

或者：

=COUNTIFS($A$2:$A$14,INDEX(A:A,MATCH(1,D$1:D$14,0)),$C$2:$C$14,B17)

接下来，我们换个思路，不使用辅助列来完成。

{=SUM(SUBTOTAL(3,OFFSET(A$1,ROW($1:$14),))*(B17=C$2:C$15))}

公式中使用了多个函数的嵌套组合。首先使用OFFSET函数以A1为基点，分别向下偏移1至14行，形成由A2、A3、A4、……A15这样单个单元格区域的引用，再用SUBTOTAL函数对这些单个的单元格区域分别统计可见单元格的个数，相当于判断是否为可见单元格。

如果单元格可见，则返回1，否则返回0。

再用B17=C$2:C$15产生的逻辑值，与这个1和0的数组相乘，得到筛选状态下的统计计数。

好吧，今天星光和大家分享的内容就酱紫，更多精彩内容请关注：EXCELers(网名看见星光)

​