问题描述
问候堆栈溢出居民!
我在尝试获取涉及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 OWNERIDAs 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!错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!