Excel 公式：用简单的英语来说，INDEX(MATCH()) 中的这个数组是什么？

Question

我正在尝试帮助一位朋友处理她在工作中继承的 Excel INDEX(MATCH())，但它让我很为难。有人能帮助我更好地了解它内部发生了什么吗？格式如下：

=INDEX(
    '[Spreadsheet2.xlsx]TabOfInterest'!$B$B
    ,
    MATCH(
        1
        ,
        (
            (D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M)
            *
            (F34='[Spreadsheet2.xlsx]TabOfInterest'!$P:$P)
            *
            (K34='[Spreadsheet2.xlsx]TabOfInterest'!$Y:$Y)
        )
        ,
        0
    )
    ,
    1
)

我可以看到，如果数字“1”（在这里似乎代表“TRUE”）出现在某种由 TRUE/1 和 FALSE/0 组成的数组中的任何地方，该数组是通过与其他 BOOLEAN 填充数组进行 AND 运算而构建的（但是这应该在 Excel 中起作用...），则 INDEX 将获取“行号”无论那是什么混乱导致其中存在“1”，并且 INDEX 将返回位于该行和 B 列交叉点的电子表格 2 的单元格的值。

但我不知道 MATCH 的第二个参数发生了什么。

我试着用空白电子表格来操作，但甚至无法推断出=(SingleCell=RangeOfCells) 做——或者就此而言，=RangeOfCells返回值似乎取决于公式输入到哪个单元格（其与正在检查的数据的行偏移量），并且$在这些公式中添加行号之前没有任何区别。

我需要了解哪些关于引用范围以及在单元格和范围之间进行“相等”比较的基本知识，以便更好地理解此公式中发生的情况以及最终INDEX(MATCH())试图查找的内容？

到底是什么在这个数组？

(D34='[Spreadsheet2.xlsx]TabOfInterest'!$M:$M)
*
(F34='[Spreadsheet2.xlsx]TabOfInterest'!$P:$P)
*
(K34='[Spreadsheet2.xlsx]TabOfInterest'!$Y:$Y)

此外，如果我不得不猜测的话，我想我会说整体功能是这样做的，但令我困扰的是，我无法解释为什么：

如果有一个单排在电子表格2中，以下三个条件全部为真：

• 此 INDEX-MATCH 所在的电子表格的单元格 D34 出现在电子表格 2 的“M”列中，并且
• 此 INDEX-MATCH 所在的电子表格的单元格 F34 出现在电子表格 2 的“P”列中，并且
• 此 INDEX-MATCH 所在的电子表格的单元格 K34 出现在 Spreadsheet2 的“Y”列中

然后返回电子表格2、B 列中单元格的值，无论电子表格2 中哪一行的复合条件为真。

Answer 1

让我们稍微简化一下，看看我们是否能弄清楚发生了什么。在下面的图片中，我设置了 D1、E1 和 F1 来模仿公式中的 D34、F34 和 K34，并用值填充它们。为了好玩，我在前三个单元格下面添加了两行值。稍后会详细介绍。

然后在 H、I 和 J 列中，我添加了与 M、P 和 Y 列等效的数组。我还将它们缩短了一点，主要是因为如果它们是整列，下面的调试技巧将不起作用。

在 G7 中，我只输入了上面公式的 MATCH() 部分。从包围它的花括号可以看出，这是一个数组公式。输入方法是按 CTRL-Shift Enter，Excel 会添加花括号。完成后，您可以再填写两行。很明显，MATCH() 函数正在给出一些答案。

您说得对，逻辑值相乘相当于 AND() 运算，只不过结果为 1 和 0，而不是 True 和 False。例如，您可以通过在单元格中输入=True*True或来检查这一点。=True*False

您问这个相当奇怪的数组中可能包含什么（是的，它是一个数组）。那么让我们看看 Excel 认为它的值是什么。

单击其中一个公式，然后单击要查看其值的公式元素 - 在本例中为“lookup_array”。这将突出显示 lookup_array，如下图所示。

现在按 F9。这会使 Excel 显示价值突出显示元素的。

Excel 告诉我们公式突出显示部分的值是数组 {1;0;0;0;0}。如果对 G8 和 G9 执行此操作，您将看到值分别为 {0;0;0;0;1} 和 {0;0;0;1;0}。因此，MATCH() 函数会找到这些数组中 1 的位置，即 1、5 和 4。

您可以这样想：lookup_array 的第一部分检查 D1 是否等于 H1:H5 - 该部分返回 {1;1;0;0;0}。然后检查 I1:I5 中的 E1 得出 {1;0;1;0;0}。检查 J1:J5 中的 F1 得出 {1;0;1;0;0}。将这三个进行 AND 运算得出 {1;0;0;0;0}，这是 Excel 为整个（确实很奇怪）数组显示的结果。类似地，G8 中的公式找到 {0;0;0;0;1}、{0;1;0;0;1} 和 {0;0;0;0;1)，然后对它们进行 AND 运算得出 {0;0;0;0;1}。

你对发生的事情的猜测是正确的：MATCH() 找到了第一的H、I 和 J 中的行（匹配类型 = 0）具有与 D、E 和 F 中的单元格匹配的值，然后 INDEX() 返回 B 列中的该位置。

您可以摆弄上面的值并亲自检查一下。请记住使用 CTRL-Shift Enter 输入数组公式 - 您会经常这样做，因为 Excel 有一个错误/功能，即单击公式然后按回车键或 Tab 键会出现 #VALUE! 错误。CTRL-Z 将撤消该操作。

希望这会有所帮助并祝你好运。

Answer 2

这是写成数组函数吗？即两边都有花括号，使用 ctrl/enter 输入？如果是这样，那么中间部分就是比较数组中与其所在行对应的值。考虑到乘法，我认为你说得对，它正在寻找所有 3 个值都为真的东西。

我怀疑，在没有看到您的电子表格的情况下（当然，我也没有仔细阅读您帖子的详尽细节），有一个范围具有此函数，而您恰好向我们展示了其中一个。我怀疑前一行和后一行（以及许多其他行）具有相同的公式，行号会相应调整。如果是这样，那么我加倍期待这是一个数组函数，编写一次并应用于一系列单元格，使用 ctrl/enter 使其在运行时遍历整个单元格。

如果是这种情况，编辑公式并使用 Enter 保存（即使您没有进行任何实际编辑或更改）也会改变行为，从而改变结果。

如果这些都没有意义，请随时将电子表格发送给我，我会看一下。