Excel教程

Excel函数
Excel技巧

【EXCEL教程】用Sumifs函数查找价格

当前位置:网站首页 > Excel教程

【EXCEL教程】用Sumifs函数查找价格

* 来源: 【office教程学习】 * 作者: 【EXCEL教程】 * 发表时间: 2020-07-16 11:54:08 * 浏览: 405


嗨,各位经常做表格的表叔、表婶、表哥、表姐、表弟、表妹们有没有遇到过下面这样的多条件带区间查询单价的情况呢?图01



为方便看,我把匹配区(A1:D10)和结果区(F2:K4)放一起了,


需求是查询某个类别在对应时间段内的价格


这时间段还真是难到不少人,固定值可以用辅助列,把它们链接起来,然后再用Vlookup函数来查找。


可这是要找它在那一个时间范围内就有点难了!别怕,知道规则了,方法就比困难多了,看图中我不就列出了3个公式吗?


解法


常规逻辑,既然是查找返回值我们能用的函数VlookupHlookup和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:A10F3进行比较,要求是一样的,所以用等于比较

B3:B10G3进行比较,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)!

今天的分享就到这,如果教程对大家有用,希望大家多多分享点赞支持小编哦!你的每一次点赞和转发都是支持小篇坚持原创的动力。