Excel，数组公式，超出范围的N/A和ROW() [英] Excel, Array Formulas, N/A outside of range, and ROW()
问题描述
在Excel 2013中，数组公式中的ROW()出现问题.
示例:
我创建了一个名为输入"的命名范围，例如4个单元格宽和10个高度.然后，我创建一个数组公式=ROW(input)宽一个单元格，高15个单元格.
我得到10个数字-第一个是输入的第一行，其余的从那开始计数，然后是5个#N/A.这是应该的.
如果不是=ROW(input)，请尝试以下操作之一:
=IFERROR(ROW(input),"x")
或
=IF(ISNA(ROW(input)),"x",ROW(input))
要捕获#N/A，那么我希望看到的是十个数字，然后是五个x.相反，我仍然看到十个数字，然后是五个#N/A.
谁能告诉我如何解决这个问题?我想得到十个数字，然后是五个x.
次要的是，出于好奇(或者是出于另一种情况)，为什么会发生这种情况呢?
我为什么要这样做?这是我为这个问题简化的一个较大公式的一部分.
(进行编辑以提高简洁性和清晰度)
我相信，在这样的子句中，Excel优先考虑对引用的人为扩展以匹配所选工作表范围的扩展(它将始终通过首先使用数组中的IF子句来填充#N/As).
因此，而通常"(例如，在单单元格数组公式中)，例如:
=SUM(IF(ISNA(ROW(input)),"YES","NO"))
将有效地强迫Excel将IF函数的单个 value_if_true 和 value_if_false 参数扩展为这些值的语法适当的数组，即:
=SUM(IF({FALSE;FALSE;FALSE;FALSE;FALSE},{"YES","YES","YES","YES","YES"},{"NO","NO","NO","NO","NO"}))
即:
=SUM({"NO";"NO";"NO";"NO";"NO"})
具有多单元格数组公式，例如您的:
=IF(ISNA(ROW(input)),"YES","NO")
进入10个单元格的范围内，因此不会首先解决:
=IF(ISNA({1;2;3;4;5;#N/A;#N/A;#N/A;#N/A;#N/A}),{"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES"},{"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO"})
(其中 value_if_true 和 value_if_false 参数首先根据要在其中输入数组的工作表范围的尺寸进行重新标注.)
即:
=IF({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE},{"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES"},{"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO"})
即:
={"NO";"NO";"NO";"NO";"NO";"YES";"YES";"YES";"YES";"YES"}
而是:
=IF(ISNA({1;2;3;4;5;#N/A;#N/A;#N/A;#N/A;#N/A}),{"YES";"YES";"YES";"YES";"YES";#N/A;#N/A;#N/A;#N/A;#N/A},{"NO";"NO";"NO";"NO";"NO";#N/A;#N/A;#N/A;#N/A;#N/A})
( value_if_true 和 value_if_false 参数首先根据命名范围 input 的尺寸进行重新标注.)
即:
=IF({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE},{"YES";"YES";"YES";"YES";"YES";#N/A;#N/A;#N/A;#N/A;#N/A},{"NO";"NO";"NO";"NO";"NO";#N/A;#N/A;#N/A;#N/A;#N/A})
即:
{"NO";"NO";"NO";"NO";"NO";#N/A;#N/A;#N/A;#N/A;#N/A}
希望能有所帮助.
致谢
I have a problem with ROW() in an array formula in Excel 2013.
Example:
I make a named range, called 'input', say 4 cells wide and 10 high. Then I make an array formula =ROW(input) one cell wide, 15 cells high.
I get 10 numbers - the first is the first row of input, and the rest count up from that, and then 5 #N/A follow. This is as it should be.
If instead of =ROW(input) I try one of the following:
=IFERROR(ROW(input),"x")
or
=IF(ISNA(ROW(input)),"x",ROW(input))
to catch the #N/As then what I expect to see is ten numbers, and then five x's. Instead I still see ten numbers and then five #N/As.
Can anyone tell me how to solve this problem? I want to get ten numbers, and then five x's.
And of lesser importance but just for curiosity (or in case it comes up in another case), why does this happen?
Why do I want to do this? It's part of a larger formula that I have simplified for this question.
(Edited to improve brevity and clarity)
I believe that, in such clauses, Excel gives precedence to the artificial expansion of the reference to match that of the worksheet range selected (which it will always do by filling with #N/As) over first resolving the IF clause over the array.
So, whereas "normally" (e.g in a single-cell array formula), e.g.:
=SUM(IF(ISNA(ROW(input)),"YES","NO"))
would, effectively, coerce Excel into expanding the single value_if_true and value_if_false parameters of the IF function into syntactically-appropriate arrays of those values, viz:
=SUM(IF({FALSE;FALSE;FALSE;FALSE;FALSE},{"YES","YES","YES","YES","YES"},{"NO","NO","NO","NO","NO"}))
i.e.:
=SUM({"NO";"NO";"NO";"NO";"NO"})
with multi-cell array formulas, e.g. your:
=IF(ISNA(ROW(input)),"YES","NO")
entered over a 10-cell range, is NOT first resolved thus:
=IF(ISNA({1;2;3;4;5;#N/A;#N/A;#N/A;#N/A;#N/A}),{"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES"},{"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO"})
(In which the the value_if_true and value_if_false parameters are first re-dimensioned in accordance with the dimension of the worksheet range in which the array is being entered.)
i.e.:
=IF({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE},{"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES";"YES"},{"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO";"NO"})
i.e.:
={"NO";"NO";"NO";"NO";"NO";"YES";"YES";"YES";"YES";"YES"}
but rather as:
=IF(ISNA({1;2;3;4;5;#N/A;#N/A;#N/A;#N/A;#N/A}),{"YES";"YES";"YES";"YES";"YES";#N/A;#N/A;#N/A;#N/A;#N/A},{"NO";"NO";"NO";"NO";"NO";#N/A;#N/A;#N/A;#N/A;#N/A})
(The value_if_true and value_if_false parameters first being re-dimensioned in accordance rather with the dimensions of the Named Range input.)
i.e.:
=IF({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE},{"YES";"YES";"YES";"YES";"YES";#N/A;#N/A;#N/A;#N/A;#N/A},{"NO";"NO";"NO";"NO";"NO";#N/A;#N/A;#N/A;#N/A;#N/A})
i.e.:
{"NO";"NO";"NO";"NO";"NO";#N/A;#N/A;#N/A;#N/A;#N/A}
Hope that helps a bit.
Regards
这篇关于Excel，数组公式，超出范围的N/A和ROW()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
