单个单元格范围的 Excel 索引 + 匹配失败

Question

我希望尝试在可以包含一个或多个值的列表中找到第一个负数。我正在使用索引/匹配。当范围(或命名引用)具有两个或多个单元格时，它会正确找到该值。但是，当范围只有一个单元格时，它会失败。

例如
K1 和 K2 列的 Excel 数据分别为 -10.00 和 20.00

当我在下面尝试时，它按预期返回 -10.00

=INDEX(K1:K2, MATCH(TRUE,K1:K2<0,0),)

但是，当我只搜索一个单元格范围时，它会失败并显示“#N/A”。我希望它返回-10.00。

=INDEX(K1:K1, MATCH(TRUE,K1:K1<0,0),)

使用包含单个单元格的命名引用时会出现同样的问题。

非常感谢

Answer 1

这是一个非常有趣的问题，并且需要相当技术性的答案。

基本上， MATCH 要求 lookup_array 是某种“类型”。这并不是说这个数组不能只包含一个元素(它可以)，而是说它的形式与 MATCH 期望为其第二个参数传递的形式兼容。

这种可接受的形式可以是值数组，也可以是对(连续)一维工作表单元格范围的引用。

当你执行:
=INDEX(K1:K2,MATCH(TRUE,K1:K2<0,0),)
这解决了:
=INDEX(K1:K2,MATCH(TRUE,{TRUE;FALSE},0),)
所以lookup_array，即:
{TRUE;FALSE}
是一个数组，即可接受的形式。

但是，当您执行时:
=INDEX(K1:K2,MATCH(TRUE,K1:K1<0,0),)
然后这解决为:
=INDEX(K1:K2,MATCH(TRUE,TRUE,0),)
而这次lookup_array (TRUE) 是一个单一的 bool 值，它不满足对该参数的类型施加的要求。

但是，我们可以人为地强制该单个值，使其具有正确的类型，即:
=INDEX(K1:K2,MATCH(TRUE,IF({1},K1:K1)<0,0),)
这一次解决为:
=INDEX(K1:K2,MATCH(TRUE,{-10})<0,0),)
这是:
=INDEX(K1:K2,MATCH(TRUE,{TRUE},0),)
现在，单个 bool 值在技术上是数组的一部分(尽管它只包含一个值)，上面的内容可以根据需要进行解析。

作为此行为的另一个示例，假设单元格 A1 包含值 1。然后:
=MATCH(1,A1,0)
正确返回 1，因为即使 lookup_array 在这里是一个
单元格，它(就像所有工作表范围一样)在技术上仍然是数组类型。

但是，如果我们做一个小改动，例如:
=MATCH(1,N(A1),0)
然后，即使这解决为:
=MATCH(1,1,0)
所以看起来什么都没有改变，事实上，通过将范围引用 A1“取消引用”到它的实际值，我们现在已经使它成为一个无效的类型，作为 lookup_array 传递，上面因此导致了一个错误.

在我看来，MATCH 的这种行为充其量是不一致的，最坏的情况是设计错误。

的结果:
=INDEX(K1:K2,MATCH(TRUE,K1:K1<0,0),)
应该:
=INDEX(K1:K2,MATCH(TRUE,{TRUE},0),)
无需我们通过额外的人为手段强制它。

出于某种原因，如果要传递的数组仅包含一个值，则首先将该值“解析”为非数组类型，这与包含多个值的数组不同，其数组类型为保留。

问候