筛选列表Excel后计数

Question

我想看看如何让这个公式正确运行的工作示例

=消费品（小计（3，偏移量（$p$7，行（$p#8:$p$5500）-行（$p$7），，，1）），--（$p#7:$p$5500="74"）

我试图实现的是对数值大于的范围内的所有单元格进行计数零，但我需要在应用过滤器后使其工作。我看过几个例子，但还没有用小计fx。

提前感谢您的帮助！

Answer 1

工作示例：

    O       P
4
5           6
6   
7   Filter  Data
8   1       74
9   0       74
10  1       74
11  0       74
12  1       74
13  0       74
Formula in P5:
=SUMPRODUCT(SUBTOTAL(103,OFFSET($P$7,ROW($P$8:$P$20)-ROW($P$7),,1)),--($P$8:$P$20=74))

现在您可以过滤列O，P5只计算可见的74。

使用SUBTOTAL中的函数103，我实际上只计算可见的单元格。这意味着，手动隐藏的单元格也不会被计算在内。对于函数3，只有滤出的细胞不被计数。如果手动隐藏单元格，即使它们不可见，也会对它们进行计数。

我使用了值74，而不是文本"74"。所以在我的例子中，列P包含数字，而不是文本。

工作原理：

SUMPRODUCT中的公式位于数组（矩阵）上下文中。这意味着它们被当作在数组公式中使用一样处理。

在数组上下文中，{ROW($P$8:$P$20)-ROW($P$7)}得到ROW($P$8)-ROW($P$7) = 1, ROW($P$9)-ROW($P$7) = 2, ROW($P$10)-ROW($P$7) = 3，依此类推

OFFSET($P$7,1,,1)将获得$p$7+1行=$p$8，OFFSET($P$7,2,,1)将获得$p$7+2行=$p$9，依此类推

所以{OFFSET($P$7,ROW($P$8:$P$20)-ROW($P$7),,1)}得到{$P$8,$P$9,$P$10,...}

在SUBTOTAL(103, {$P$8,$P$9,$P$10,...})中，如果{$P$8,$P$9,$P$10,...}可见则计数1，如果不可见则计数0。

因此SUMPRODUCT导致

SUMPRODUCT({1,0,1,0,1,0,0,...}, --($P$8:$P$20=74))

{($P$8:$P$20=74)}导致{TRUE,TRUE,TRUE,FALSE,TRUE,...}取决于$p$8:$p$20=74。--（*-1*-1）在数值上下文中获取此值，因此TRUE=1，FALSE=0。

因此，SUMPRODUCT最终导致

SUMPRODUCT({1,0,1,0,1,0,0,...}, {1,1,1,1,1,1,0,...})

问候

Axel