我有一个INDEX(MATCH())
结构如下的功能:
=INDEX(INDIRECT(HLOOKUP(P13,Price,2)),MATCH(1,((INDIRECT(HLOOKUP(P13,EffDate,2))>=N13))*((INDIRECT(HLOOKUP(P13,Index,2))<=N13)),1))
具体来说,我INDEX()
引用了一个范围来输出价格值,并设置了MATCH()
一个单元格输入标准(日期),以根据开始日期和结束日期来评估引用表中的哪一行符合条件。对于和INDEX()
,MATCH()
都INDIRECT(HLOOKUP())
使用 来引用正确的范围(共四个,在单独的工作表上)。
无论出于什么原因,输出结果都是这样0
。我尝试使用文本(字符串)和数字(Excel 日期数值)格式,但结果似乎相同。知道我在这里做错了什么吗?谢谢!
答案1
出于学术上的好奇心和非常实际的关注,我认为问题在于您对该MATCH
函数的使用,特别是匹配类型。
您指定“1”以匹配...好吧,在应用于无序列表时会变得复杂...在这种情况下,最后一个值小于要匹配的值。“最后”绝对不是指第一个匹配值之前的最后一个,而是要匹配的列表中的最后一个。
您的查找会在公式内生成数组。(并且作为可分离的结果,现在我们有了 SPILL 技术。)这些是人们想要查看的值……加上它们下面的一些空行。
这些空行在公式使用的内部数组中显示“0”,并且考虑到数据的性质,除非特别说明,否则这些数组中的最后一个元素将为 0。因此,最后一个元素将是选定的元素。
因此,您INDEX
对给定范围的第 9 行提出了需求,并且该范围不包含任何数据,正如向我们显示的那样,所以它返回“0”,因为空白单元格在此用途中返回“0”。
如果你解决了这个问题,我相信你的公式会奏效。当然,这是看不见的判断,但假设一切都存在。例如,人们可能会:
填写每个可能范围的最后一个元素(所有列的第 14 行)。有风险,因为大多数填充可能会导致始终得到该行。
IF
在查找中使用测试。如果不达到现在的水平,可能很难实现。动态构建查找范围。这里有很多东西可以做。绝对是迄今为止最好的选择。
不要使用
INDIRECT
,而应使用INDEX
。动态选择填充的数据区域会变得更加容易,因为您可以直接选择它们,并且像在 3.(上文)中一样,可能会使用 来COUNT
选择在所选数据中要包含多少行。
(奇怪的是,首先想到的INDEX
和其复杂构造MATCH
是,虽然指定了一行,但没有以任何方式处理列。我的意思是,即使不打算在该参数的位置放置任何东西,仍然需要逗号来避免错误。然而在这个公式中,我们不需要,这除了奇怪之外,也很有趣。在我脑子里解决了这个问题之后,我(才)意识到,如果这是这里的问题,你会得到一个错误,而不是 0......所以无论如何这都是一条死路。)
使用 来解决问题的主要价值INDEX
在于,使用它可以轻松、自然地生成您需要的列,并且仅限于包含数据的列。这假设数据格式良好,如您的图片所示,并且还假设您在 中感觉自然INDEX
。如果您觉得创建动态字符串的工作更清晰,或者喜欢将步骤隔离,因为它可以分离不同的任务,并可能更容易理解电子表格,并且可能更容易随着时间的推移改进电子表格,那么请务必坚持使用INDIRECT
,但将所需的动态性应用于您构建字符串的范围。您绝不会听到我建议现代计算机使用(...嘘,提到了恶魔......)“易失性”函数对性能的影响最小。在构建和维护电子表格时,做您认为最适合您的事情。但请注意,还有其他选择。