☰

OFFSET 如何处理过滤后的 EXCEL 数据？

tag-icon • tag-icon microsoft-excel-2010 worksheet-function
OFFSET 如何处理过滤后的 EXCEL 数据？

考虑：

    +---------------+
    | Column A      |
+---+---------------+
| 1 | Milan         |
+---+---------------+
| 2 | Paris         |
+---+---------------+
| 3 | London        |
+---+---------------+
| 4 | Milan         |
+---+---------------+
| 5 | Firenza       |
+---+---------------+
| 6 | Napoli        |
+---+---------------+
| 7 | Amsterdam     |
+---+---------------+
| 8 | Copenhagen    |
+---+---------------+
| 9 | Amsterdam     |
+---+---------------+

假设我过滤 A 列以查找意大利的城市。将返回以下结果。

    +---------------+
    | Column A      |
+---+---------------+
| 1 | Milan         |
+---+---------------+
| 4 | Milan         |
+---+---------------+
| 5 | Firenza       |
+---+---------------+
| 6 | Napoli        |
+---+---------------+

现在，考虑以下表达式：

=IFERROR(VLOOKUP($AR5,Copy!$I$1:$K$3,2,0),OFFSET($A$1,2,0))

是否OFFSET指的是整个源数据 - 在这种情况下，如果出现错误，我的默认值将是London- 或仅指结果集 - 在这种情况下，如果出现错误，我的默认值将是Firenze？

我在工作表中看到不一致的结果，通过了解在这种情况下应该发生什么，我可以缩小调试选项的范围。

答案1

首先关于一般的调试公式：

最好的方法是使用Evaluate formula功能（在Formula选项卡中）。这为您提供了一种非常好的方式来查看每个步骤/参数中发生的情况。此外，如果您突出显示公式中的某个部分（例如完整的 OFFSET 公式），请按F9。这将评估公式栏中突出显示的语句。

OFFSET 将返回London，因为 OF​​FSET 还会考虑隐藏的单元格。

如果您想要返回第二个可见单元格，则需要一个涉及额外列的小技巧：将公式放在=SUBTOTAL(3,A1)+C1C2 中（假设您的表格从第 1 行开始并且 C 列可用）。然后向下复制公式。它现在将向您显示所有可见单元格的计数器，即数字将根据您的自动过滤器而变化。

现在公式=INDEX(A:A,MATCH(2,C:C,0))将返回第二个可见元素。

答案2

我不相信OFFSET()排除隐藏/过滤的数据。从下面的例子中，您可以看到，即使部分数据被隐藏/过滤，公式也C1指向同一个单元格。

在此处输入图片描述

要从列表中获取第 3 个可见项目，可以使用以下数组公式1：

=INDEX(xmen,
       SMALL(IF(SUBTOTAL(3,OFFSET(xmen,ROW(xmen)-ROW(A2),0,1)),ROW(xmen)),3)-1,1)

在哪里：

xmen→ 指的是不包括任何标题的数据范围（A1:A9在您的示例中，A2:A9在下面的示例中）
A2→ 指向范围内的第一个项目（非标题）

例如：

在此处输入图片描述

--
1必须在将其与 VLOOKUP 公式集成后使用Ctrl+ Shift+提交/输入Enter

答案3

保持简单：

=VLOOKUP(SUBTOTAL(5,A:A),A:B,2,FALSE)

它找到可见的最小数字（小计5最小）和vlookup值（也采用该索引号找到的第一个值）。

相关内容