Excel、数组公式、范围外不适用和 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/As 那么我希望看到的是十个数字，然后是五个 x.相反，我仍然看到十个数字，然后是五个#N/As.
谁能告诉我如何解决这个问题?我想得到十个数字，然后是五个 x.
而且不太重要，但只是出于好奇(或万一在其他情况下出现)，为什么会发生这种情况?
我为什么要这样做?这是我为这个问题简化的更大公式的一部分.
(编辑以提高简洁性和清晰度)
我相信，在这样的子句中，Excel 优先考虑人工扩展引用以匹配所选工作表范围的引用(它总是通过填充 #N/As) 而不是首先解析数组上的 IF 子句.
所以，而通常"(例如在单单元格数组公式中)，例如:
=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",不不不不"}))即:
=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";"YES";"YES";"YES";"YES";"YES"}而是作为:
=IF(ISNA({1;2;3;4;5;#N/A;#N/A;#N/A;#N/A;#N/A}),{"是";"是";"是";"是";"是";#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 参数首先根据命名范围输入的维度重新定义维度.)
即:
=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";#不适用;#不适用;#不适用;#不适用;#不适用})即:
{"NO";"NO";"NO";"NO";"NO";#N/A;#N/A;#N/A;#N/A;#N/一种}希望能有所帮助.
问候
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、数组公式、范围外不适用和 ROW()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
