二维码 购物车
部落窝在线教育欢迎您!

日期没有排序,用Excel快速查询产品最新报价的7种函数公式法

 

作者:老菜鸟来源:部落窝教育发布时间:2023-03-16 14:30:19点击:7058

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

如果报价清单中的日期没有排序,怎么才能快速查到各种产品的最新报价呢?直接套用网上Lookup函数1、0结构或者2、1结构套路公式是无法查到的,必须再搭配一个函数解决排序。今天就给大家分享7种不排序就能查最新价格的方法。

 

如图所示,每种产品都有多个日期的不同报价,并且这些日期并没有从小到大升序排列。现在需要查找出对应产品最新的报价。

 

图形用户界面, 表格
描述已自动生成

 

首先分析一下这个问题。
如果日期是按升序排序了的,则就是一个简单的单条件查找,查找最后一个(日期最大)符合条件(产品名称)的价格,用LOOKUP的标准1、0结构套路公式即可查到。但是现在日期没有排序,本质上就变成多条件查找了。条件1是产品名称,条件2是产品对应的日期最大值。
理清楚这个逻辑,解决思路也就有了,以下为大家介绍7个不同的解法。

 

不排序查最新价格方法1 LOOKUP +MAXIFS函数

公式为=LOOKUP(1,0/((MAXIFS(B:B,A:A,E2)=B:B)*(A:A=E2)),C:C)

 

 

这个公式用的LOOKUP多条件匹配的标准套路:

=LOOKUP(1,0/((条件1所在列=条件1)*(条件2所在列=条件2)),结果所在列)

需要注意的是公式中用到了MAXIFS(B:B,A:A,E2),这个函数的作用是按条件返回最大值,用法与SUMIFS类似,在2016及以下的Excel中可能没这个函数,因此公式需要做对应的调整。

 

不排序查最新价格方法2 LOOKUP +MAX+IF函数

公式为=LOOKUP(1,0/(E2&MAX(IF(A:A=E2,B:B))=A:A&B:B),C:C)

 

 

与公式1有两个区别,区别1是用MAX(IF(A:A=E2,B:B))取代了MAXIFS函数,区别2是用&对两组条件进行合并,将多条件变成了单条件。

 

不排序查最新价格方法3 INDEX+MATCH+MAXIFS函数

公式为=INDEX(C:C,MATCH(E2&MAXIFS(B:B,A:A,E2),A:A&B:B,0))

 

 

这个公式用的是INDEX+MATCH这对经典组合,用MAXIFS得到产品对应的最近日期,再用&将多条件变成单条件,也算是一个常规思路了。

 

不排序查最新价格方4 FILTER+ MAXIFS函数

公式为=FILTER(C:C,(A:A=E2)*(B:B=MAXIFS(B:B,A:A,E2)))

 

 

这个公式用的两个都是新函数,FILTER函数的作用是按指定的一组条件或多组条件筛选数据,用法为FILTER(结果所在列, (条件1所在列=条件1)*(条件2所在列=条件2)),关于这个函数的详细用法可以参考之前的教程。强大的筛选函数FILTER用法集

 

不排序查最新价格方法5 VLOOKUP+SORT函数

公式为=VLOOKUP(E2,SORT(A$2:C11,2,-1),3,)

 

 

这个问题还有个特点就是数据源并不是按日期排序的,完全是乱序,所以要使用SORT函数先对数据源排序,SORT(A$2:C11,2,-1)的意思是对数据源按照第二列降序排序,这样得到结果最近的日期就拍到前面了,再用VLOOKUP匹配得到的就是最新的报价。

 

不排序查最新价格方法6 SUMIFS+ MAX+IF函数

公式为=SUMIFS(C:C,A:A,E2,B:B,MAX(IF(A:A=E2,B:B)))

 

表格
描述已自动生成

 

当满足多个条件的结果是数字且只有一条时,多条件匹配和多条件求和的结果是一致的,所以这个问题也可以用SUMIFS来解决,关于SUMIFS函数大家都很熟悉了,这就不啰嗦了。比较特殊的就是有一组条件要用MAX+IF或者MAXIFS得到。

 

不排序查最新价格方法7 SUMPRODUCT+MAXIFS函数

公式为=SUMPRODUCT((MAXIFS(B:B,A:A,E2)=B:B)*(E2=A:A),C:C)

 

表格
描述已自动生成

 

既然SUMIFS都可以解决,SUMPRODUCT更加可以了,如果之前的公式都懂了,这个公式也就没任何难度了。

以上虽然列举了7个方法,实际上搞明白原理的话,还可以组合出更多的公式来,有兴趣的同学可以自己试试,把你组合的公式分享出来。


最后,除开上面的函数公式法,也可以用数据透视表的方法获得产品的最新报价,有需要了解的伙伴可以留言联系我们。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

如何提取品牌信息?LOOKUP函数有绝招!

如何在交叉查询中使用VLOOKUP?看完就懂!

Excel教程:如何制作带有层次和透视感的图表?

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。