本文介绍了Excel公式:使用INDIRECT获取具有指定范围的相同行号会导致#VALUE!错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

问候堆栈溢出居民!

我在尝试获取涉及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!错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-17 19:05