所有栏目 | 云社区 美国云服务器[国内云主机商]
你的位置:首页 > 云社区 » 正文

在EXCEL中,如何实现输入任一关键字都可以查询到所有符合条件的记录信息?

发布时间:2020-04-15 16:51:34

资讯分类:关键字  excel  任一  查询  关键字  辅助  员工
在EXCEL中,如何实现输入任一关键字都可以查询到所有符合条件的记录信息?

要实现输入表格中的任一关键字就能查询出符合条件的所有员工信息,这个问题确实有点难度,一种方法是用VBA,但VBA不是每一个用EXCEL的人都会的,有难度,二是用数组公式,但数组公式遇到数据量大时,运行会有点卡,这里介绍一种不用VBA,不用数组公式,简单易学,只用基本的函数就能实现的方法,可以实现输入姓名关键字、性别关键字、学历关键字、部门关键字等都能查出你想要的员工信息,而且可以高亮显示关键字。具体效果如下图:

设计思路:

1、用辅助列将所有员工信息合并到一起,通过find函数查找搜索的关键字是否包含在合并信息中,通过contif统计包含关键字的记录有多少条,再根据序号,用vlookup一对多查询,将所有符合条件的信息查询出来。

2、最后再用条件格式将包含查询关键字用红色字体标识出来。

实现步骤:

一、在员工基本情况表中增加合并辅助列、匹配判断辅助列和关键字出现次数辅助列

1.在员工基本情况表中增加合并辅助列。如下图所示,增加一个辅助列,将员工的姓名、员工编号、性别等都合并到辅助列。

Excel 2016版本可用快速填充法或新增函数CONCAT合并D2到J2,也可以用&连接符合并。其它版本可用&连接符合并。

C2单元格公式:=CONCAT(D2:J2)或=D2&E2&F2&G2&H2&I2&J2

2、在员工基本情况表中增加辅助列“匹配”判断

在B2单元格输入公式:=IFERROR(IF(FIND(查询表!$C$2,C2)>0,"▲ 在EXCEL中,如何实现输入任一关键字都可以查询到所有符合条件的记录信息?

题主的问题,使用高级筛选是十分方便的。

1、数据区:

2、条件区:

3、若要查询某个姓名的所有信息,可在姓名下输入想查询的姓名。

3.1、查询一个姓名,比如,张飞,高级筛选设置如下:

结果:

3.2、若要查询多个姓名,比如刘备、关羽、张飞三兄弟的全部信息。

在条件区域,姓名下输入三兄弟的姓名,同时调整条件区域的范围即可。

3.3、若要根据某个关键字查找,比如查找姓名中包含 “乔”这个字的所有姓名信息。

条件区域设置如下:

结果:

其他性别和部门关键字的处理,和姓名一样的道理。需要的时候,设置相应的条件即可实现目的。


除了用“高级筛选”的方法,借助函数辅助列的方式,也是十分有用的。

1、获取性别为“女”的所有人员信息。

添加辅助列,对“女”记性计数。

公式为:=B2&COUNTIF(B$2:B2,B2)

查询公式为:index+match组合,公式为:

=IFERROR(INDEX(A$2:A$13,MATCH("女"&ROW(A1),$E$2:$E$13,0),),"")

2、查询性别为“男”的所有人员信息。公式为:

=IFERROR(INDEX(A$2:A$13,MATCH("男"&ROW(A1),$E$2:$E$13,0),),"")


综上,推荐用函数辅助列的方法来实现“高级筛选”。

辅助列在实际应用中是比较有效率又灵活的强大方法。往往会比数组公式更加实用。

欢迎关注套路Excel

留言与评论(共有 0 条评论)
   
验证码:
Top