用Sumifs函数查找价格
发布时间:2022-04-13 19:16:30
嗨,各位经常做表格的表叔、表婶、表哥、表姐、表弟、表小编们有没有遇到过下面这样的多条件带区间查询单价的情况呢?图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
公式解释:参数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
和Lookup的区别是Lookup的D3:D10是作为参数三来使用的,而Sum里依然用的*来运算,原理和Lookup参数2是一样的!
需要注意的是,这是数组公式,录入好以后要用 Ctrl + Shift + Enter 三键结束,否则结果可能不正确
使用office365(版本12725.200006)的用户不必三键,它能自动识别并运算出正确值。
看到这里,你还会说:我不要求和,我要查找吗?
解法三:
既然用Sum函数能求出来结果,那我们就能使用Sumifs函数来返回所需要的值了。
使用Sumifs函数的话,那就简单多了,来看公式图07
第5行使用了公式来返回公式字符数,可以看出Sumifs字符是最少的(少了行号和绝对引用符号)
=LEN(FORMULATEXT(J3))
这个查找问题为什么可以用求和函数来解?因为它满足了以下两个条件,
第一:我们需要返回的结果是数字;
第二:符合条件的结果是唯一的。
若是其中一点不满足那就不能使用Sumifs函数!
解决问题从特征上来解,可以得到更优的解法,若是想要做到通用(比如返回的结果可能有数字也有字符串),那公式就会复杂一些(使用Lookup)!


猜你喜欢
- 众所周知越来越多的用户喜欢使用wps这款办公软件,用户在使用时可以体验到不一样的使用感受,这是因为相较于其它办公软件来说wps软件有着很显著
- 现在很多用户使用的是Win10系统的2009版本,由于20H2版本的发布,用户想要升级最新的系统但是又不知道20H2系统是否稳定,下面给大家
- 对于职场老司机来说,总会有一些常用的Word技巧,虽不一定是最简便的,但确实好用,本期Word小编与大家分享3个Word小技巧。1、对齐选择
- Meta for Ma是适用于macOS的高级音乐标签编辑器,使用Meta Mac版非常简单,主要是因为它的布局非常直观。您可以查看:标签,
- EML文件如何打开?相信大家在操作电脑的过程中,都有碰到过EML格式的文件吧,EML文件是Outlook邮件的文件格式,很多用户会遇到EML
- 不得不说,现在的科技产品更新速度真的是太快了,首批配备Retina 5K显示屏的苹果机型已经到了要被淘汰的地步。据外媒报道,首款配备 Ret
- 随手记如何查看记账图表?如今很多用户都在使用随手记对每天开销进行记账,但其中很多用户不知道如何使用随手记中查看图表,其实操作很简单,下面小编
- 相信小伙伴们都知道,Word文档是一款非常实用的办公软件,我们需要对大量文字内容进行处理时都会选择使用Word文档。因为Word文档功能非常
- csrss.exe进程是电脑系统正常运行的程序,是微软客户端/服务端运行时子系统。该进程管理Windows图形相关任务。这个程序对你系统的正
- Win10系统怎么修复系统引导文件?引导区修复命令是什么?这些问题是不是一脸茫然,没事,今天小编整理了相关的问题,下面跟我一起来看看吧。wi
- 关于下一代 Apple TV 的传闻已经曝光了一段时间,但未知苹果打算何时发布新品。今日,外媒的最新消息指出,苹果正在为 Apple TV
- 自从win10正式版本发布以后,不少用户都忙着更新或是升级win10。但最近几天,很多win7或win8.1的用户表示,他们在升级的过程中遇
- 我们有时候在打开Photoshop的时候会遇到提示错误代码“0xc00007b”,这个错误代码会导致我们无法正常使用Photoshop,下面
- 随着微软发布Windows11系统,Windows11系统越来越受欢迎,但是目前的操作系统大部分还是Win10,那么我们什么时候才能购买到预
- WPS表格数据怎么转换成柱形图?wps表格中的数据想制作成柱形图表,该怎么制作呢?下面我们就来看看详细的转账教程,需要的朋友可以参考下作为一
- 一、废话如茶版所说,古人在没有时钟的时候,会用其他的计时方式。例如:一柱香的功夫,一盏茶的功夫,一袋烟的功夫,一顿饭的功夫等等。古人尚且知道
- 公式编辑器自然是用来编辑公式的,在以前版本的Word文档中都不是很好用,但是从Word 2010版开始,公式编辑器有了极大的提升,打出的公式
- 自从微软为win7/win8系统推送win10系统之后,很多用户都已经选择升级更新到win10系统, 然而在安装过程中难免会碰到一些问题,比
- 通过Office 2016的自动化转换工具,可以轻松将昔日作品与文件中的古老文字列表,变成动人的SmartArt 图形列表。更为有用的是,如
- 很多人对自己电脑的开机速度都很有追求,但是如果觉得开机速度太慢了怎么办,电脑系统开机的速度对于我们使用体验有着很大的影响,如果大家觉得自己的