|
还在为如何实现输入表格中的任一关键字就能查询出符合条件的所有员工信息而烦恼吗？快来看看全表查询吧，输入姓名关键字、性别关键字、学历关键字、部门关键字等都能查出你想要的员工信息，而且不用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,'',''),'') 公式解析： FIND(查询表!$C$2,C2)，查找查询的关键字在合并辅助列的位置。 IF(FIND(查询表!$C$2,C2)>0,'','')，如果查询到辅助列中有关键字，那FIND(查询表!$C$2,C2)查询的位置肯定是>0，如果>0，就显示三角形，否则就显示空。 IFERROR(IF(FIND(查询表!$C$2,C2)>0,'',''),'')如果辅助列中没有关键字就会出错，用IFERROR函数检查是否为错误，如果是错误就显示空，否则就显示 公式分步演示图： 3．计算关键字出现的次数 在A2单元格输入公式：=COUNTIF(B$2:B2,'') 公式解析：计算在匹配辅助列出现的次数 二、在查询表中建立查询并用条件格式设置关键字为红色字体 1、在查询表中输入序号（序号最好要多一点，能保证符合条件的记录最多时够用） 2、在F4单元格输入公式 =IFERROR(VLOOKUP($E4,员工基本情况表!$A:$J,MATCH(查询表!F$3,员工基本情况表!$A$1:$J$1,0),0),'') 3、向右填充后，再向下填充至出现空白即可。 4、选中查询表，点【开始】【条件格式】【突出显示单元格规则】【文本包含】，设置单元格包含关键字中突出显示单元格的规则为浅红填充色深红色文本。 公式解析： 1、MATCH (目标值，查找区域，精确匹配0)，MATCH(查询表!F$3,员工基本情况表!$A$1:$J$1,0)，是通过MATCH找出F3姓名在查询表的第1行的位置，即第几列。因为姓名等标题行都是第1行，所以要锁定行，而基本情况表A1到J1是固定的，所以用绝对引用。 2、VLOOKUP（找谁，在哪找，返回第几列，0精确匹配），VLOOKUP($E4,员工基本情况表!$A:$J,MATCH(查询表!F$3,员工基本情况表!$A$1:$J$1,0),0)，根据E列的序号用Vlookup从员工基本情况表中查询符合条件的信息。 动态演示图： 小伙伴们，是不是你最想要的全表格查询效果呢，赶紧试试吧！ 如果我的分享对您有帮助，欢迎点赞、收藏、评论、转发，更多的EXCEL技能，大家可以关注“EXCEL学习微课堂”。如果需要全表格查询实例原文件的可留下邮箱或者私信联系我！ EXCEL学习微课堂分享的与全表查询相关的课程有： 1. Excel查找函数FIND，帮你从复杂的地址中提取城市、区和街道名！ 2. Excel的IF函数还可以这样用，你知道吗？ 比Vlookup好用10倍的自定义函数VLOOKUPS，解决VLOOKUP的难题！ |
|
|
