当前位置:首页 > 办公 > Excel教程 > 正文内容

用Sumifs函数查找价格

酷网1个月前 (10-24)Excel教程24

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

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/40390.html

分享给朋友:

相关文章

怎么在Excel中添加链接

怎么在Excel中添加链接

微软的Excel程序是一款多功能的电子表格编辑软件,允许用户整理、保存和分享各种类型的数据。如果电子表格中的数据需要参考其它资源和信息,或是需要连接到其它地方,你可以在表格中添加网站或其它文档的链接,也可以添加同一个电子...

INDEX+MATCH,查找神组合(二)

INDEX+MATCH,查找神组合(二)

之前INDEX+MATCH,查找神组合(一),内容仅涉及到了INDEX+MATCH的简单组合。 其实真正的数据处理中,远非这么简单。比如,如下数据样表: 查找销售业绩最高的姓名,或者销售业绩前三的姓名,就需要对MATC...

Excel统计得票最多的姓名

Excel统计得票最多的姓名

问题情境 如下样表,记录了每个人的得票情况: 求统计得票最多的姓名。 公式实现 在D2单元格输入公式: =IFERROR(INDEX(B$2:B$16,SMALL(MODE.MULT(MATCH(B$2:B$16,B$...

计算机等级考试二级office易考,常考函数,SUMIF、AVERAGEIF和COUNTIF逻辑判断函数,垂直查询函数,求和函数

计算机等级考试二级office易考,常考函数,SUMIF、AVERAGEIF和COUNTIF逻辑判断函数,垂直查询函数,求和函数

亲爱的各位粉丝们,晚上好呀,我们又见面了,最近有很多粉丝叫我分享一下二级等级考试函数的常考题,好的,亲爱的各位粉丝们,对你们呢,我绝对有求必应,这不,我来了,下面,我就给大家讲解一下几种常考的函数 各位小哥哥小姐姐们,拿...

Excel 2010怎么自定义三色刻度颜色?Excel 2010自定义三色刻度颜色的方法教学

Excel 2010怎么自定义三色刻度颜色?Excel 2010自定义三色刻度颜色的方法教学

  我们Excel表格日常办公学习的时候,有时候需要使用到Excel的三色刻度,但是有些时候我们总会系统默认的三色不美观,看起来比较别扭,那么我们怎么设置Excel表格中的三色刻度颜色...

Excel中rank函数怎么用

Excel中rank函数怎么用

你还在为Excel中rank函数怎么用而苦恼吗,今天小编教你Excel中rank函数怎么用,让你告别Excel中rank函数怎么用的烦恼。 经验主要从四方面对Excel函数进行讲解,1.函数的含义,2.函数的语法格式,3...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。