问与答85： 如何统计汇总筛选过的列表数据？

Q：如下图1所示，需要获取单元格区域C7:C13中出现的L的数量，及对应的分数之和，但是我们对单元格区域A6:D13应用了筛选，如果筛选的团队是“West”，那么相应的L的数量是2；如果筛选的团队是”East“，则相应的L的数量是1；如果没有筛选，则相应的L的数量是3？

图1

在图1中，单元格C15使用了公式：

=COUNTIF(C7:C13,B2)

单元格C16使用了公式：

=SUMIF(C7:C13,B2,D7:D13)

这对于没有进行数据筛选的数据表来说，是正确的。但是，如果我们对数据应用了筛选，则上述两个公式的结果就不正确了，如下图2所示，我们筛选出“East”团队后的统计：

图2

很显然，此时出现在筛选后的数据表中的L只有1次，但上述两个公式的结果没有变化，它们忽略了筛选数据而是仍然应用到原来所有的数据中。

如何使用公式，在单元格D2和D3中得到正确的结果？

（注：本案例整理自chandoo.org论坛。）

A：先给出公式，再进行分析。

在单元格D2中输入公式：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),--(C7:C13=B2))

传递给SUMPRODUCT函数的是生成的两个中间数组：一个是代表所有有效筛选的数据的列表，另一个是代表所有与条件匹配的未筛选的数据的列表，两个数组的乘积将是一个包含与条件匹配的筛选的数据的数组。

使用SUBTOTAL函数统计单元格区域中有效条目数。指定其参数function_num为3，代表使用COUNTA函数。指定其参数Ref1为：

OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)

即要分类统计的单元格区域。其中：

ROW(C7:C13)-MIN(ROW(C7:C13))

返回数组：

{0;1;2;3;4;5;6}

代入公式中：

OFFSET(C7:C13,{0;1;2;3;4;5;6},,1)

返回数组：

{“W”;”L”;0;”L”;”W”;”L”;”W”}

将其传递给SUBTOTAL函数并使用Counta函数累加所使用的数组中每个值的次数。因为SUBTOTAL函数会忽略筛选后的隐藏值，因此应用筛选后其返回的值会不同：

对于上图1中没有应用筛选的数据表，SUBTOTAL函数生成的数组为：

{1;1;0;1;1;1;1}

表示在单元格区域C7:C13中，1代表有效的条目，0代表该单元格没有文本或值。

对于上图2中应用筛选的数据表，SUBTOTAL函数生成的数组为：

{1;0;0;1;0;0;0}

表示在单元格区域有两个单元格与条件（示例中为“East”）匹配，即1所处位置的单元格。

对于公式中的：

--(C7:C13=B2)

将单元格区域C7:C13中的数值与单元格B2中的数值比较，得到数组：

{0;1;0;1;0;1;0}

将上述生成的中间数组传递给SUMPRODUCT函数，这样：

对于没有筛选的数据，公式转换为：

=SUMPRODUCT({1;1;0;1;1;1;1}, {0;1;0;1;0;1;0})

=SUMPRODUCT({1*0;1*1; 0*0; 1*1; 1*0; 1*1; 1*0})

=3

对于应用了筛选的数据，公式转换为：

=SUMPRODUCT({1;0;0;1;0;0;0}, {0;1;0;1;0;1;0})

=SUMPRODUCT({1*0;0*1; 0*0; 1*1; 0*0; 0*1; 0*0})

=1

下面，来汇总满足条件的条目的分数，在单元格D3中输入公式：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),––(C7:C13=B2),(D7:D13))

或者：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),(C7:C13=B2)*(D7:D13))

其主要运行原理与上文相同。