嗨,各位经常做表格的表叔、表婶、表哥、表姐、表弟、表妹们有没有遇到过下面这样的多条件带区间查询单价的情况呢?图01
为方便看,我把匹配区(A1:D10)和结果区(F2:K4)放一起了,
需求是查询某个类别在对应时间段内的价格。
这时间段还真是难到不少人,若固定值可以用辅助列,把它们链接起来,然后再用Vlookup函数来查找。
可这是要找它在那一个时间范围内就有点难了!别怕,知道规则了,方法就比困难多了,看图中我不就列出了3个公式吗?
解法一:
常规逻辑,既然是查找返回值,我们能用的函数有Vlookup,Hlookup和Lookup,Hlookup这里不适用,用Vlookup“也不行”,那就用Lookup吧,毕竟Lookup还是很强大的。来看公式1,图片02
公式如下:
=LOOKUP(,0/(($A$3:$A$10=F3)*($B$3:$B$10<=g3)*($c$3:$c$10>=G3)),$D$3:$D$10) |
公式解释:参数1什么都没输,参数二则比较长了,一步一步理,理顺了就明白了
A3:A10和F3进行比较,要求是一样的,所以用等于比较
B3:B10和G3进行比较,B列是起始日期,那它肯定要小于或等于G3了,
C3:C10和G3进行比较,C列是结束日期,那它肯定要大于或等于G3了。
比较结果不是True 就是False
来看一下测试图03
为了方便对比,我对它们做了换行处理,这样更方便对比
这结果是怎么弄出来的?选中 $A$3:$A$10=F3 ,按F9就可以了,后面两个一样。
这时候你可能会有点晕,不理解,简单解释一下吧,上面不是分了三段来比较吗?
这些都是比较结果,符合条件就是True,不符合条件就是False
有了比较结果以后,要把三段的结果合起来,这里用了 * 来运算,* 是乘法运算,
仅当True * True 的时候才会返回 True(and的效果),
所以接着来看一下运算结果吧!图04
咦,怎么成了1和0 了?因为在Excel中,True可以用1表示,Fasle可以用0表示,做了数学运算后,就自动转换成数字了!
再用0来分别除这些内容,目的将0转换为错误值。再来看图05
为什么要把它转换成错误值呢?
我们的星空女神在很久之前就有发过Lookup函数的详细讲解,感兴趣的还是去搜索出来学习一下吧,毕竟Lookup也是很强大的。
Lookup的思路就讲解到这里,用它来讲解,是因为它是查找函数,用它可以返回任何值,同时能帮我们理清思路!
解法二:
这里我们要返回的值是数字,并且符合条件的结果是唯一的,那我们就可以使用求和函数!
继续刚才的思路,换个Sum函数继续测试,来看公式2,图06
公式如下:
=SUM(($A$3:$A$10=F3)*($B$3:$B$10<=g3)*($c$3:$c$10>=G3)*$D$3:$D$10) |
和Lookup的区别是Lookup的D3:D10是作为参数三来使用的,而Sum里依然用的*来运算,原理和Lookup参数2是一样的!
需要注意的是,这是数组公式,录入好以后要用 Ctrl + Shift + Enter 三键结束,否则结果可能不正确
使用office365(版本12725.200006)的用户不必三键,它能自动识别并运算出正确值。
看到这里,你还会说:我不要求和,我要查找吗?
解法三:
既然用Sum函数能求出来结果,那我们就能使用Sumifs函数来返回所需要的值了。
使用Sumifs函数的话,那就简单多了,来看公式图07
第5行使用了公式来返回公式字符数,可以看出Sumifs字符是最少的(少了行号和绝对引用符号)
=LEN(FORMULATEXT(J3)) |
这个查找问题为什么可以用求和函数来解?因为它满足了以下两个条件,
第一:我们需要返回的结果是数字;
第二:符合条件的结果是唯一的。
若是其中一点不满足那就不能使用Sumifs函数!
解决问题从特征上来解,可以得到更优的解法,若是想要做到通用(比如返回的结果可能有数字也有字符串),那公式就会复杂一些(使用Lookup)!
今天的分享就到这,如果教程对大家有用,希望大家多多分享点赞支持小编哦!你的每一次点赞和转发都是支持小篇坚持原创的动力。