excel - 如何在 Excel 中引用过滤范围

Question

我正在使用这个函数来搜索当前表的Domain值是否在Domain.List表的Domain列中找到，返回TRUE/FALSE:

=NOT(ISERROR(VLOOKUP([@Domain],Domain.List[Domain],1,FALSE)))

当这个 Domain.List 被过滤时，条目会更少。例如:

DOMAIN      INCLUDE
google.com    0
apple.com     1
amazon.com    0
alibaba.com   0

当被包含过滤时，将变为:

DOMAIN      INCLUDE
apple.com     1

现在回到原始表，我希望上面的函数仅对匹配 apple.com 的域返回 TRUE。

但实际上，无论 Domain.List 表的过滤器状态如何，函数结果都不会改变。

有没有办法告诉 Excel 我只想对 Domain.List[Domain] 列的可见部分运行公式？

Answer 1

将您的公式更改为:

=IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(Domain.List[DOMAIN],ROW(Domain.List)-MIN(ROW(Domain.List[DOMAIN])),,1)),ISNUMBER(SEARCH([@Domain],Domain.List[DOMAIN]))+0),TRUE,FALSE)

在表中:



这里有一个很好的解释: Count visible rows only with criteria

部分引用:

您使用 SUMPRODUCT 来应用 SUBTOTAL 函数(通过 OFFSET)和标准 - 例如= apple.com (第二部分 ISNUMBER(SEARCH([@Domain],Domain.List[DOMAIN]))+0 是一种用 SEARCH ISNUMBER 简写的 bool 值。您将生成一个 TRUE FALSE 列表)。

SUBTOTAL 可以排除 SUMPRODUCT 的隐藏行。由于 SUBTOTAL 返回单个数字，而 SUMPRODUCT 需要一个数组，我们使用 OFFSET 为 SUBTOTAL 每行提供一个引用，因此 OFFSET 将每行返回一个结果。

这是通过给 OFFSET 一个数组来完成的，该数组每行包含一个数字，从零开始，由以下方式生成:

ROW(Domain.List)-MIN(ROW(Domain.List[DOMAIN]))

您基本上是在将两个数组相乘，第一个说法是行可见，第二个说法是否与域名匹配。然后将其包装在 If 公式中以生成所需的 TRUE、FALSE。我依靠事实 1 = TRUE 来简写以下公式:

=IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(Domain.List[DOMAIN],ROW(Domain.List)-MIN(ROW(Domain.List[DOMAIN])),,1)),ISNUMBER(SEARCH([@Domain],Domain.List[DOMAIN]))+0)=1,TRUE,FALSE)

这是 IF(result = 1, TRUE,FALSE)简单地说 IF(result , TRUE,FALSE)即 IF(1 , TRUE,FALSE)