☰

计算列中的唯一值，同时过滤其他列

tag-icon • tag-icon microsoft-excel
计算列中的唯一值，同时过滤其他列

我有一份物料清单，我想根据几列进行过滤，然后计算零件编号列中唯一值的数量。当我更改其他列中的过滤条件时，该计数应该会更新。我该怎么做？谢谢！

答案1

从MrExcel.com：

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A10)-ROW(A2),,1)),
    IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0))),ROW(A2:A10)-ROW(A2)+1),1))

该公式假设数据范围是 A2:A10。您需要调整单元格 A2 和范围 A2:A10 的引用以适合您的数据。

这是一个数组公式，必须使用Control- Shift-Enter组合键输入。

仔细观察该表达式，就会发现它依赖于数组与四个 Excel 函数的组合使用。下面将对此进行解释。为了便于说明，我们将重点讨论以下示例，其中单元格 A2:A10 中总共有 9 个值，但过滤后仅显示 5 个值。

在此处输入图片描述

1.  MATCH("~" & A2:A10, A2:A10 & "", 0)

该MATCH函数通常用于在另一个值范围中查找一个值，如果找到该值，则返回找到匹配值的行位置。

当数组同时用作查找值和查找范围时，MATCH返回一个数组，该数组显示范围中每个值的第一个匹配值（如果有）的行位置。

如果范围包含重复值，则每个重复值都会返回相同的行位置。使用示例数据，该MATCH函数将生成：

MATCH(A2:A10, A2:A10, 0)}

-->MATCH({11, 98, 67,  , 37, 67, 98, 56, 67},{11, 98, 67, ,37, 67, 98, 56, 67},0)

-->      { 1,  2 , 3, 4,  5,  3,  2,  8,  3}

只需稍加努力，即可使用此结果来计算每个值在原始数组中出现的次数：例如，请注意，值 67 的行位置 3 在结果数组中出现了三次，对应于该值在输入数组中出现的三次。*

2.  SUBTOTAL(3, OFFSET(A2, ROW(A2:A10)-ROW(2),,1)

这个问题的特别难点在于如何区分出现在筛选列表中的值和未出现在筛选列表中的值。SUBTOTAL应用于范围的函数在其结果中仅包含可见行，但即使以数组公式输入，也不会提供有关其输入范围内的各个单元格是否可见的信息。

但是，这里使用的函数以产生数组结果OFFSET的形式返回单元格数组。SUBTOTAL信用对于 Laurent Longre 来说，此公式得出的结果是可见单元格为 1，而隐藏单元格为 0。

SUBTOTAL(3, OFFSET(A2, ROW(A2:A10) - ROW(A2), , 1))

--> SUBTOTAL(3, OFFSET(A2, {2, 3,..., 10} - {2}, , 1))

--> SUBTOTAL(3, OFFSET(A2, {0, 1,..., 8}, , 1))

--> SUBTOTAL(3, ({A2}, {A3},...,{A9}))

--> {0, 1, 1, 0, 0, 0, 1, 1, 1}

有了这个结果，就可以在任何后续计算中仅计算过滤后可见范围内的值。**

3. FREQUENCY(IF(SUBTOTAL(3,OFFSET(...)),IF(A2:A10<>"",MATCH(...))),
             ROW(A2:A10)-ROW(A2)+1)

该FREQUENCY函数接受两个参数，一个数据值数组和一个垃圾桶数组，并返回一个数组，该数组显示范围中有多少值落入每个 bin 中。例如，如果 bin 范围中的值为 (1.5, 2.5, 3.5)，FREQUENCY则将返回小于或等于 1.5、大于 1.5 和小于或等于 2.5 等数据值的计数。

在公式中，数据数组通过 IF 语句和 SUBTOTAL/OFFSETandMATCH表达式的组合来表示。这实际上归结为对这些表达式生成的数组进行逐个元素的 AND 运算。

SUBTOTAL/OFFSET ARRAY:  {0, 1, 1, 0, 0, 0, 1, 1, 1}  [visible vs. hidden]
MATCH ARRAY:            {1, 2, 3, F, 5, 3, 2, 8, 3}  [row position of 1st match]

RESULT ARRAY:           {F, 2, 3, F, F, F, 2, 8, 3}  [row position of 1st match        
                                                      in visible cells]
'F' = FALSE

表达式生成的 bin 数组ROW(A2:A10)-ROW(A2)+1只是值 1 到 9，对应于范围 A2:10 中可能的行位置。

BIN ARRAY:        {1, 2, 3, 4, 5, 6, 7, 8, 9} 

FREQUENCIES:      {0, 2, 2, 0, 0, 0, 0, 1, 0}

这些频率与过滤范围内显示的 2 倍值 98、2 倍值 67 以及 1 倍值 56 相符。

因为目标是计算过滤范围内不同值的数量，所以最后的封闭IF- IF(FREQUENCY(...), 1)- 将频率数组中的非零值转换为 1：

DISTINCT VALUES ARRAY:  {F, 1, 1, F, F, F, F, 1, 0}

对该数组求和得出此例的最终答案为 3。

*表达式中的波浪号 (~)MATCH用于转义诸如“+”和“>”之类的字符，如果它们是匹配值中的第一个字符，则会产生错误值（因为 Excel 会将它们视为运算符而不是字符）。

**函数的第一个参数SUBTOTAL是值 3，它告诉函数返回可见单元格的数量。最常见的值 9 表示可见单元格的总和。

相关内容