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

妙用FILTER函数实现任意关键词查询数据

 

作者:老菜鸟来源:部落窝教育发布时间:2023-05-17 13:30:24点击:1754

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

编按:

今天给大家分享用任意一个关键词皆可查找数据的最简单方法。只要输入数据源存在的任意一个关键词,即可得到符合条件的所有数据。利用该方法可以实现万能查找,在库存查询、员工信息查询、物流查询等都用得上。

 

哈喽,大家好。

今天来分享利用任意一个关键词进行数据查找的方法,如下图员工信息表所示,能够按照编号、部门、学历、性别、姓名、婚姻状况中的任意字符进行查找。

譬如可以查找编号包括7的人,所有姓赵的人,所有部门是财务的人,所有大专学历的人等等。

 


 

实现这种任意关键词查找,并不需要很复杂的步骤,或者VBA代码之类的,只需要一个公式即可。

下面就为大家详细说明,为了便于理解,分成三个阶段进行解读。

第一阶段:关键字单列查找

关键字查找,意味条件为包含关系,就是查找包含关键字字符的数据。因此,通常需要借用SEARCH或者FIND函数来确定是否包含条件字符。

关键字单列查找,就是用关键字与某一列数据进行包含判断查找。

例如,只在姓名列数据中进行查找关键字,公式为:

=FILTER(A:I,IFERROR(SEARCH(K2,C:C),),0)

 

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

 

SEARCH函数的作用是在一个文本值中查找另一个指定文本值(不区分大小写)的位置,得到结果是一个数字。

例如:=SEARCH(K2,C2:C20)得到如图结果,表示在范围C2:C20的每个单元格都找一下K2的内容(娜)是否存在,如果没有返回错误值,如果有则返回娜在对应单元格的位置(第几个字)。

 

 

再添加一个IFFERROR函数,将错误值变为0(后面有用),公式为=IFERROR(SEARCH(K2,C2:C20),0)

 

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

FILTER的第二参数本来应该是一个逻辑值(条件比较得到的就是逻辑值),但是在Excel中,逻辑值和数字之间有个对应关系,0相当于FALSE,非零数相当于TRUE。因此SEARCH得到的非零值就相当于符合查找条件的值。

所以公式=FILTER(A:I,IFERROR(SEARCH(K2,C:C),0))就能按照关键字进行查找,这一点一定要想明白,假如就是想不明白的话,记下公式套路就行吧。

至此,我们实现了关键字单列查找的结果,距离最终目标之差一步。

 

第二阶段:关键字多列查找。

关键字多列查找,就是用关键字分别与多列数据进行包含判断查找,只要关键字被多列数据的任何一列包含,就会查找到相应数据。因此各列的包含判断是“或”关系,用加号来组合它们的判断结果。

譬如:在上面姓名列查找的基础上,我们增加部门列查找。

完整的公式为:=FILTER(A:I,IFERROR(SEARCH(K2,B:B),0)+IFERROR(SEARCH(K2,C:C),0),0)

 

 

 

如果还要按照其他列查找的话,只需要继续加IFERRORSEARCH这部分即可。

 

第三阶段:解决条件为空等的查找让公式更人性化

当前公式,如果条件为空,会返回所有值;如果没有符合条件的,返回是0

所以需要调整公式,让条件为空时返回空;让没有符合条件的,显示为“无符合条件值”。

最终公式:

=IF(K2="","",FILTER(A:I,IFERROR(SEARCH(K2,A:A),0)+IFERROR(SEARCH(K2,B:B),0)+IFERROR(SEARCH(K2,C:C),0)+

IFERROR(SEARCH(K2,D:D),0)+IFERROR(SEARCH(K2,E:E),0)+IFERROR(SEARCH(K2,F:F),0),"无符合条件值"))

 

 

FILTER函数实现任意关键字查找就介绍到这里。任意关键字查找中的条件关系是包含、或,如果条件是且关系,则公式不同。有兴趣的可以查看文章《强大的筛选函数FILTER用法集

总结:

如果表格复杂列数很多,用加号形式的或关系查找公式就会很长。这时,可以在表格最前列插入一个辅助列A列。然后利用公式把每行的值合并到该列中。查找时,只比较辅助列是否包含条件字符即可完成查找。譬如上方,合并后的查找公式=IF(L2="","",FILTER(B:J,IFERROR(SEARCH(L2,A:A),0),"无符合条件值")),简洁了很多。



提示:
凭借任意一个关键词查询数据的优点是操作简单;缺点是不够精准,尤其是在包含多列数字(含日期)的表格中用数字查询,准确度低。

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

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

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

IMG_256

相关推荐:

强大的筛选函数FILTER用法集

UNIQUE函数的经典用法!新手必看,秒杀万金油公式!

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

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

版权申明:

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