☰

Excel 帮助 - 是否可以返回水平数组中第二个匹配项的列号？

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 帮助 - 是否可以返回水平数组中第二个匹配项的列号？

我试图返回下表中第二次出现零对应的列号：

B       C       D       E       F      G
2       0       0       3       4      9

例如，所需公式将返回代表 D 列的 3。

如果无法返回数组中的列号，那么是否可以返回 4（工作表中的列号）？

答案1

我确信有人会有更简洁的方法来做到这一点，但这是我想到的一个快速答案。

它要求您在某些公式中指定范围（示例中为 B1：G1）MATCH，并且还需要以 R1C1 格式再次指定该范围以供INDIRECT函数使用。

=MATCH(0,INDIRECT("R1C"&MATCH(0,B1:G1,0)+1&":R1C7",0),0)+MATCH(0,B1:G1,0)

演练

我们首先找到包含第一个匹配值的列，这就是公式的这一部分：MATCH(0,B1:G1,0)。

然后，一旦我们知道第一个匹配项在 C 列中，我们就可以运行另一个MATCH匹配项，该匹配项在 C 列右侧的范围内查找 - 即 D 列及以后。这就是INDIRECT正在做的事情。它使用 R1C1 引用，以便它可以将您的第一个匹配项MATCH作为输入：INDIRECT("R1C"&MATCH(0,B1:G1,0)+1&":R1C7",0)。

因此我们MATCH像这样运行第二个：MATCH(0,INDIRECT("R1C"&MATCH(0,B1:G1,0)+1&":R1C7",0),0)。

这将输出从 D 列开始计数的列数（在本例中为 1）。因此，我们需要添加左侧跳过的列数，以获得最终数字 - 这只是第一个 MATCH 的重复： +MATCH(0,B1:G1,0)。

注意 - 为了灵活性，请使用 INDEX() 或 OFFSET()，而不是 INDIRECT()

布莱姆提出了关于使用不灵活性的很好的观点INDIRECT——阅读他的回答提议INDEX或者OFFSET代替。

答案2

应大众要求...:-)，另外该版本还存在一些问题INDIRECT，我在下面进行了描述。

首先，另外两个解决方案。唯一实质性的区别在于如何计算要搜索的第二个范围。Andi 使用了INDIRECT，我分别使用了INDEX和OFFSET。您会注意到另一个小区别，我没有对要搜索的值进行硬编码，而是从单元格中获取它，这样通过改变要搜索的数字可以更轻松地测试公式。我在这里包含了这三个，因此很容易将它们放在一起看到，第一个只是从 Andi 的答案中复制而来（用我在其他答案中使用的相同单元格引用 B3 替换了硬编码的 0）：

=MATCH(B3,INDIRECT("R1C"&MATCH(B3,B1:G1,0)+1&":R1C7",0),0)+MATCH(B3,B1:G1,0)
=MATCH(B3,INDEX(B1:G1,0,MATCH(B3,B1:G1,0)+1):G1,0)+MATCH(B3,B1:G1,0)
=MATCH(B3,OFFSET(B1:G1,0,MATCH(B3,B1:G1,0)):G1,0)+MATCH(B3,B1:G1,0)

第一个参数的第二个参数（在公式中，它与搜索数字的第二次出现匹配）MATCH是区别：

INDIRECT("R1C"&MATCH(B3,B1:G1,0)+1&":R1C7",0)
INDEX(B1:G1,0,MATCH(B3,B1:G1,0)+1):G1
OFFSET(B1:G1,0,MATCH(B3,B1:G1,0)):G1

Andi 描述了第一个。第二个和第三个的工作原理是计算匹配后单元格的引用（和INDEX(...)）OFFSET(...)，然后将其扩展到以最后一个单元格结尾的范围（:G1部分）。因此，如果搜索 0，MATCH(B3,B1:G1,0)则返回 2，因此INDEX版本变为：

INDEX(B1:G1,0,2+1):G1
INDEX(B1:G1,0,3):G1
D1:G1

该OFFSET版本的行为类似。

虽然这三个函数表面上看起来是等价的，但实际上有两点让我不使用该INDIRECT版本。首先，由于它计算的是绝对列，因此在略微不同的情况下不起作用。假设表中的最终值从 9 更改为 3。然后，如果我们在 B3 中输入 3 来搜索第二个 3，则公式应该全部返回 6。但是，该INDIRECT版本返回 5。原因如下。在这种情况下，MATCH(B3,B1:G1,0)返回 4，因此INDIRECT变为：

 INDIRECT("R1C"&4+1&":R1C7",0)
 INDIRECT("R1C"&5&":R1C7",0)
 INDIRECT("R1C5:R1C7",0)

相当于 E1:G1。但是，E1:G1 的第一个单元格是包含前 3 个值的单元格，因此将MATCH再次找到前 3 个值并返回 1，将其添加到第一个MATCH值 (4)，返回 5。

问题在于，列计算是绝对的，没有考虑到被搜索的表从第二列开始的事实。因此，即使在搜索 0 时，公式INDIRECT也是“错误的”，因为它会两次找到第一个 0，但看起来它有效，因为第二个 0 紧挨着第一个 0 的右边，因此查找MATCH第二个 0 返回 1，这恰好是两个 0 的列号之差。当被搜索的两个数字的列号之差不是 1 时，公式INDIRECT将返回错误的值。

可以通过使列引用相对（并假设公式与表的开头位于同一列）来解决此问题：

=MATCH(B3,INDIRECT("R1C["&MATCH(B3,B1:G1,0)&"]:R1C7",0),0)+MATCH(B3,B1:G1,0)

现在INDIRECT是：

 INDIRECT("R1C["&4&"]:R1C7",0)
 INDIRECT("R1C[4]:R1C7",0)

即 F1:G1，因此现在第二个MATCH将从第一个匹配项右侧的单元格开始搜索，并返回 2，总共 6，这是正确的。

这可行，但仍然存在问题，与我括号中的“假设公式与表格开头位于同一列”有关。该INDIRECT版本（即使是固定版本）相当脆弱。例如，如果我在数字表格上方插入一个新行，则INDEX和OFFSET版本将继续工作，因为 Excel 会自动更新所有引用。但由于版本中除一个引用之外的所有引用INDIRECT都是文本，Excel 无法更新它们，因此它们将继续引用第一行，而第一行现在是其他内容。如果删除第一列或在表格左侧添加其他列，它会出现类似的问题。因此在这种情况下，我可能会选择INDEX或OFFSET版本，只是为了稍微“为电子表格提供未来保障”。

相关内容