Excel索引+匹配单细胞范围失败 [英] Excel Index + Match failing for single cell range
问题描述
我正在寻找一个可以包含一个或多个值的列表中的第一个负数。我正在使用Index / Match。当范围（或命名引用）有两个或多个单元格时，它会正确找到该值。但是，当范围只有一个单元格时，它会失败。
例如
列K1和K2中的Excel数据分别为-10.00和20.00
当我尝试下面，它按预期的方式返回-10.00
= INDEX（K1：K2，MATCH（TRUE，K1：K2 < ，0），）
但是，当我只搜索一个单元格范围时，它失败，#N /一个。我希望它返回-10.00。
= INDEX（K1：K1，MATCH（TRUE，K1：K1 < 0），）
当使用包含单个单元格的命名引用时，会发生相同的问题。
非常感谢
这是一个非常有趣的问题，一个这需要相当的技术答案。
基本上，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））
则此解析为： / p>
= INDEX（K1：K2，MATCH（TRUE，TRUE，0），）
，而这次是这样的okup_array （TRUE）是一个单一的布尔值，不符合对该参数的类型的要求。
然而，我们可以人为地强制这个单一的值使得它是正确类型的，即：
= INDEX（K1：K2，MATCH （TRUE，IF（{1}，K1：K1）<0,0），）/ / code>
这次解析为： / p>
= INDEX（K1：K2，MATCH（TRUE，{ - 10}）< 0,0），）
它是：
= INDEX（K1：K2，MATCH ，{TRUE}，0），）
现在，单个布尔在技术上是数组的一部分（尽管只包含一个单个值），以上根据需要进行解析。
作为这种行为的另一个例子，假设单元格A1包含值1.然后：
= MATCH（1，A1,0）
正确返回1， ，即使这里是一个单独的
单元格，它也是（所有工作表范围但是，如果我们做一个小的更改，例如：
p> = MATCH（1，N（A1），0）
然后，即使这样解析为：
= MATCH（1,1,0）
所以似乎根本没有改变，实际上，通过将范围参考A1取消引用到其实际值，现在我们已经将它作为一个无效类型作为 lookup_array ，以上因此导致错误。
在我看来，MATCH的这种行为在最坏情况下最不一致，设计错误最差。
结果如下：
= INDEX（K1：K2，MATCH（TRUE，K1： 0,0），）
应该：
= INDEX（K1：K2，MATCH（TRUE，{TRUE}，0））
没有我们必须通过额外的人工手段强制它。
由于某种原因，如果正在传递的数组只包含一个值，那么这个值首先被解析为一个非数组类型，不同于由多个值组成的数组，它们的数组类型被保留。
关心
I am looking to try and find the first negative number in a list that can contain one or more values. I am using Index/Match. When the range (or named referenced) has two or more cells, it find the value correctly. However, when the range has only one cell, it fails.
eg Excel data in column K1 and K2 are -10.00 and 20.00, respectively
When I try below, it returns -10.00 as expected
=INDEX(K1:K2, MATCH(TRUE,K1:K2<0,0),)
However, when I search only one cell range, it fails with "#N/A". I would expect it to return -10.00.
=INDEX(K1:K1, MATCH(TRUE,K1:K1<0,0),)
The same issues occurs when using named reference that contain a single cell.
Many thanks
It's a very interesting question, and one which requires quite a technical answer.
Basically, MATCH requires that the lookup_array be of a certain "type". This is not to say that this array cannot contain just a single element (it can), but rather that it be of a form which is compatible with that which MATCH is expecting to be passed for its second parameter.
And this acceptable form can be either an array of values or a reference to a (contiguous) one-dimensional range of worksheet cells.
When you perform:
=INDEX(K1:K2,MATCH(TRUE,K1:K2<0,0),)
this resolves to:
=INDEX(K1:K2,MATCH(TRUE,{TRUE;FALSE},0),)
and so the lookup_array, i.e.:
{TRUE;FALSE}
is an array, i.e. of an acceptable form.
However, when you perform:
=INDEX(K1:K2,MATCH(TRUE,K1:K1<0,0),)
then this resolves to:
=INDEX(K1:K2,MATCH(TRUE,TRUE,0),)
and this time the lookup_array (TRUE) is a single Boolean value, which does not meet the requirements imposed upon the type for this parameter.
We can, however, artificially coerce that single value so that it is of the correct type, viz:
=INDEX(K1:K2,MATCH(TRUE,IF({1},K1:K1)<0,0),)
which this time resolves to:
=INDEX(K1:K2,MATCH(TRUE,{-10})<0,0),)
which is:
=INDEX(K1:K2,MATCH(TRUE,{TRUE},0),)
and now that the single Boolean is technically part of an array (albeit one which contains only a single value), the above resolves as required.
As another example of this behaviour, imagine that cell A1 contains the value 1. Then:
=MATCH(1,A1,0)
correctly returns 1, since, even though the lookup_array is here a single cell, it is (as all worksheet ranges are) still technically of an array-type.
However, if we make a small change, e.g.:
=MATCH(1,N(A1),0)
then, even though this resolves as:
=MATCH(1,1,0)
and so it would appear that nothing at all has changed, in fact, by "dereferencing" the range reference A1 to its actual value, we have now made it of an invalid type to pass as the lookup_array, the above thus resulting in an error.
This behaviour of MATCH is, in my opinion, inconsistent at best and a design fault at worst.
The result of:
=INDEX(K1:K2,MATCH(TRUE,K1:K1<0,0),)
should be:
=INDEX(K1:K2,MATCH(TRUE,{TRUE},0),)
without us having to coerce it via additional, artificial means.
For some reason, if the array being passed comprises just a single value, then this value is first "resolved" into a non-array-type, unlike with those arrays which consist of more than one value, for which their array-type is retained.
Regards
这篇关于Excel索引+匹配单细胞范围失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
