动态统计筛选结果

王志军

同事小秦前来求助，如例所示的教职员T考核表，现在需要动态统计所筛选部门的考核情况，例如筛选机电部，则统计机电部的考核情况，筛选信息部，则统计信息部的考核情况。这里的关键是需要知道筛选的结果是哪一个部门，由于实际的数据量非常大，手工统计显然是比较麻烦，有没有简单一些的实现方法呢？

第1步：创建辅助列

例如将D列作为辅助列，选择D2单元格，在编辑栏输入公式“=SUBTOTAL（3，A$I：A2）-1”，向下拖拽或双击填充柄。这里的SUTTOTAL函数用来统计可见单元格的内容，使用的第一个参数是3，告诉SUBTOTAL函数需要执行的汇总方式是COUNTA，COUNTA函数用来计算区域非空单元格的个数，第二个参数“A$I：A2”，“A$1”使用了行的绝对引用，也就是引用白A$l单元格到公式所在行的A列这样一个逐行递增的引用区域，来判断可见非空单元格的个数。这里之所以是“-1”，是因为Excel是将带有SUBTOTAL函数的表格最后一行作为汇总行，因此需要将SUBTOTAL函数的第二参数引用起始位置写成公式所在行的上一行，再将结果减去1，否则会出现筛选结果多余的错误。

第2步：获得动态筛选结果

接下来我们可以通过MATCH函数，判断1在D1：D14的行号，再使用INDEX进行取值，使用公式“=INDEX（A：A，MATCH（1，D$I：D$14，0））”，这样可以获得A列班级动态的筛选结果。

第3步：获取动态统计结果

选择C17单元格，在编辑栏输入公式“=SUMPRODUCT（（$A$2：$A$14=INDEX（A：A，MATCH（1，D$1：D$14，0）））*（$C$2：$C$14=B17））”，向下拖拽或雙击填充柄，即可获得图3所示的统计结果。或者也可以使用公式“=COUNTIFS（$A$2：$A$14，INDEX（A：A.MATCH（1，D$1：D$14，0）），$C$2：$C$14.B17）”，统计结果完全相同。

进阶：或者也可以不使用辅助列，例如使用数组公式实现。选择C17单元格，在编辑栏输入公式“=SUM（SUBTOTAL（3.OFFSET（A$1.ROW（$1：$14），》*（B17=C$2：C$15））”，注意最后按下“Ctrl+Shift+Enter”组合键转换为数组公式。这里使用了多个函数的嵌套组合，首先使用OFFSET函数以Al为基点，分别向下偏移1至14行，形成由A2、A3、A4-A15这样单个单元格区域的引用。再使用SUBTOTAL函数对这些单个的单元格区域分别统计可见单元格的个数，相当于判断是否为可见单元格。如果单元格可见，则返回1，否则返回0。使用“B17=C$2：C$15”产生的逻辑值，与这个1和0的数组相乘，即可得到筛选状态下的统计计数。