龙空技术网

一对多查找,还在用低效的VLOOKUP么,新公式秒杀

Excel自学成才 7522

前言:

现时你们对“多条件筛选怎么操作excel公式”都比较关心,小伙伴们都想要了解一些“多条件筛选怎么操作excel公式”的相关知识。那么小编在网上网罗了一些关于“多条件筛选怎么操作excel公式””的相关知识,希望你们能喜欢,大家快快来了解一下吧!

举个工作中一对多查找的实例,比如说我们现在有采购流水数据,里面记录了很多订单数据,但是每个订单号对应了多条不同的数据,现在我们需要制作一个查询系统,但我们输入订单号的时候,所有的数据都显示出来

1、VLOOKUP一对多

如果我们还是用VLOOKUP来进行一对多查找时,首先我们需要输入一个辅助列

=B2&COUNTIFS($B$2:B2,B2)

我们通过累计计数公式,在原有的订单号最右边添加了对应出现次数的数字,这样辅助列的数据就是唯一值

然后我们在需要查询结果的位置,输入的公式是:

=VLOOKUP($H$2&ROW(A1),$A:$E,COLUMN(C1),0)

ROW和COLUMN函数公式分别返回对应单元格的行标和列标值

所以ROW(A1)其实就是数字1,也就是查找第1个订单

COLUMN(C1)其实就是数字3,也就是返回第3列的结果

当向下填充时,ROW()值会自动的变成数字2,表示查找订单号对应的第2条结果

向右填充时,COLUMN()值会自动加1,变成4,表示查找结果列为数量的值

最后我们为了屏蔽错误值,还需要添加IFEEROR公式:

=IFERROR(VLOOKUP($H$2&ROW(A1),$A:$E,COLUMN(C1),0),"")

显然,要做到一对多查找,我们需要了解的知识体系有:

1:累计计数公式

2:文本连接公式

3:相对引用和混合引用

4:ROW和COLUMN公式应用

5:IFEEOR公式

6:VLOOKUP灵活应用

还是有点难的,但是新公式出来之后,你只需要一个知识体系

2、FILTER公式一对多

FILTER公式是一个筛选公式,它有3个参数

=FILTER(结果数据源,条件,查找不到时返回的结果)

所以上述同样的需求,我们不需要创建辅助列,直接输入公式:

=FILTER(B:D,A:A=$G$2,"")

第一参数,B:D,就是我们想要的结果列

第二参数,就是判断的条件,A:A=G2,在A列里面找到订单号对应的值

第三参数,两个英文状态下的双引号,表示如果查找不到,返回空白

是不是超级简单好用,小编使用的OFFICE365,所以会有数组自动溢出功能,只需要在F5单元格中输入一个公式,所有的结果都出来了

如果说没有溢出功能的WPS里面,那我们需要先选中输出结果范围,然后在公式编辑栏输入同样的公式,最后按数组三键:

关于这个小技巧,你学会了么?动手试试吧!

标签: #多条件筛选怎么操作excel公式