excel - 计算过滤列中有多少不同的值(或获取不同值的列表)

Question

有没有办法计算 中不同值的数量？已过滤 Excel中的列？

使用 https://exceljet.net/formula/count-unique-values-in-a-range-with-countif 处的公式我可以计算一个范围内不同值的数量，但是当使用自动过滤器过滤该范围时，我仍然会得到未过滤的计数。

例如:

     A          B
1    Scarf      Blue
2    Hat        Red
3    Gloves     Green
4    Coat       Blue
5    Balloon    Red
6    Shoes      Blue

用 =SUMPRODUCT((B1:B6<>"") / COUNTIF(B1:B6,B1:B6 & "")) 计算 B 中的唯一值应该返回 3因为不同的值是 Red , Green和 Blue .

如果我自动过滤 B 列以仅选择 Red项目，结果表将如下所示:

     A          B
2    Hat        Red
5    Balloon    Red

在这种情况下，重新调整的不同值的数量应为 1 .但是上面的公式仍然返回 3 .

该公式还应处理自动过滤器中的多项选择，例如过滤 Blue和 Green应产生下表:

     A          B
1    Scarf      Blue
3    Gloves     Green
4    Coat       Blue
6    Shoes      Blue

公式应从中返回 2 (蓝绿色)。

最后，如果我在列 A 上进行过滤而不是 B ，公式应该仍然有效。所以如果我只对 Hat 感兴趣, Scarf和 Coat , 过滤栏 A因为这些值会导致:

     A          B
1    Scarf      Blue
2    Hat        Red
4    Coat       Blue

公式应从中返回 2 .

(我使用的是 Excel 2013，需要在公式中执行此操作，而不是使用 VBA 等)

我还找到了 this page在 office.com 上，我认为这可能会有所帮助，但可惜我无法让它为我工作。

Answer 1

此引用说明如何使用 AGGREGATE 排除隐藏行

Excluding hidden rows with AGGREGATE

然后，您可以使用标准方法来计算这样的唯一值

Counting unique values with FREQUENCY

因此，如果您要计算 B 列中的值，则需要一个辅助列(例如 C)，其中包含

=IF(AGGREGATE(3,5,B2),B2,"")

然后插入忽略空单元格的唯一计数形式

=SUM(IF(FREQUENCY(IF(LEN(C2:C10)>0,MATCH(C2:C10,C2:C10,0),""), IF(LEN(C2:C10)>0,MATCH(C2:C10,C2:C10,0),""))>0,1))

或者你的公式，如果你喜欢

=SUMPRODUCT((C2:C10<>"") / COUNTIF(C2:C10,C2:C10 & ""))