Excel公式:使用INDIRECT获取具有指定范围的相同行号会导致#VALUE！错误 [英] Excel Formula: Using INDIRECT to get same row number with named range causes #VALUE! error
问题描述
问候堆栈溢出居民！
我在尝试获取涉及INDIRECT的公式以使其与INDEX结合使用时遇到问题.当我使用特定的行值时，该数组可以正常工作，但是当我尝试使用INDIRECT获取当前行号时，我得到一个#VALUE！错误.
I am having an issue trying to get a formula involving INDIRECT to behave itself in conjunction with INDEX. When i use specific row values, the array works fine, but when i try and use INDIRECT to get current row number i get a #VALUE! error.
例如:我在R108单元格中有此图标，它可以正常工作:
For example: I have this in cell R108, and it works as it should:
=IF(OR(ISERROR(INDEX(MODEL,SMALL(IF(OWNERID=N108,ROW(OWNERID)-ROW(INDEX(OWNERID,1,1))+1),1))),N108=""),"",INDEX(MODEL,SMALL(IF(OWNERID=N108,ROW(OWNERID)-ROW(INDEX(OWNERID,1,1))+1),1)))
N108是指与OWNERID匹配的值，该值是同一工作簿中另一张工作表上的命名范围.
N108 refers to a value that is matched to OWNERID, which is a named range on another sheet in the same workbook.
但是我要做的是让公式引用它所在的同一行，所以这就是我要工作的:
But what i want to do is for the formula to reference the same row that it resides on, so this is what i want to work:
=IF(OR(ISERROR(INDEX(MODEL,SMALL(IF(OWNERID=INDIRECT("N"&ROW()),ROW(OWNERID)-ROW(INDEX(OWNERID,1,1))+1),1))),INDIRECT("N"&ROW())=""),"",INDEX(MODEL,SMALL(IF(OWNERID=INDIRECT("N"&ROW()),ROW(OWNERID)-ROW(INDEX(OWNERID,1,1))+1),1)))
为清楚起见，仅需提及；在一张纸上，我有一个计算机信息列表，其中包含两个命名范围:MODEL和OWNERID 由于公式是一个数组，因此我记得要按CTRL + SHIFT + ENTER.
Just to mention for clarity's sake; on one sheet i have a list of computer information which has two named ranges: MODEL and OWNERID As the formula is an array i am remembering to CTRL + SHIFT + ENTER.
我缺少什么导致INDIRECT无法为我提供行号以供参考?据我所知，它根本不喜欢它被用作命名范围查找的一部分的事实.
What am i missing that is causing INDIRECT to not provide me the the row number for reference purposes? As far as i can tell, it's simply not liking the fact that its being used as part of a named range lookup.
提前谢谢！
关于， 迪伦.
推荐答案
这是INDIRECT在数组上下文中的已知行为.由于INDIRECT将返回引用而不是值，因此数组上下文似乎无法从该引用获取值.也许这也是由INDIRECT的不稳定行为引起的.
This is a known behavior of INDIRECT in array context. Since INDIRECT will return a reference and not a value the array context seems not able to get the value from that reference. Maybe this is also caused from the volatile behavior of INDIRECT.
简单示例:
公式:
D1 = {=SUM(IF(A1:A10=INDIRECT("A"&{3,5,7}),B1:B10))}
D3 = {=SUM(IF(A1:A10=N(INDIRECT("A"&{3,5,7})),B1:B10))}
D5 = {=SUM(IF(A1:A10=T(INDIRECT("A"&{3,5,7})),B1:B10))}
如您所见，INDIRECT是否包裹在N中，它可以按预期工作.
As you see if INDIRECT is wrapped within N it works as expected.
但是为此，我们必须知道值是数字的还是文本的.如果是文本格式，例如A列中的Z1, Z2, Z3, ... , Z10，则必须使用T代替N.
But for this we must know whether the values are numeric or textual. If it is textual, for example Z1, Z2, Z3, ... , Z10 in column A, then T must be used instead of N.
这篇关于Excel公式:使用INDIRECT获取具有指定范围的相同行号会导致#VALUE！错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
