用Sumifs函数查找价格

网友投稿 396 2022-11-23

用Sumifs函数查找价格

嗨,各位经常做表格的表叔、表婶、表哥、表姐、表弟、表小编们有没有遇到过下面这样的多条件带区间查询单价的情况呢?图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)!

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:这个函数,财务会计出纳岗谨慎使用!
下一篇:中国移动智能测速服务系统(中国移动官方测速网站)
相关文章