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

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

分享到:

收藏收藏人气：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)

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

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

当前公式，如果条件为空，会返回所有值；如果没有符合条件的，返回是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),"无符合条件值"))，简洁了很多。

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