excel表格怎么设置模糊查询(Excel表格使用条件格式制作可模糊)
Excel表格中信息的查询一直是我们会经常遇到的问题。在此之前分享过两种方法制作信息查询器(有兴趣的朋友可以阅读一下),今天为朋友们分享一种更简单的、使用条件格式制作可模糊、不同字段查找的信息查询器。
Excel表格使用公式制作信息查询器(可模糊查找、不同字段查找)
Excel用定义名称、万金油公式制作查询器,可模糊、不同字段查找
一.功能说明:
在下图中在G1单元格选择不同的查找依据字段值,在G2单元格输入要查询的内容(可简写用于模糊查找)。A:D列信息源区域的单元格如果包含查询内容,那么就会自动填充一个颜色突出显示单元格内容。
二.动态效果演示:
三.制作思路:
1.制作查找依据字段值固定的查询器。
(1)如果只是根据所有学生的姓名(固定字段值)是否包含查找值来确定是否为单元格填充颜色,那么这个制作就会变得相对简单。
(2)选择A:D列单元格区域→开始→条件格式→新建规则→使用公式确定要设置格式的单元格输入公式→输入公式=ISNUMBER(FIND($G$2,$A1))→格式→填充→选择一个颜色→确定→确定。
(3)公式解析:FING函数判断A列对应的单元格内容中是否包含G2单元格中的内容;如果包含返回出现的位置,否则返回错误值。ISNUMBER函数的功能是判断FIND函数的结果是否为数值,如果是数值返回TRUE,否则返回FALSE。
2.根据固定字段查找优化公式,制作不同字段查找。
(1)制作不同字段查找只需要将公式=ISNUMBER(FIND($G$2,$A1)中的$A1优化成可以根据不同字段变化的引用就可可以实现了。
(2)用函数INDIRECT("$" &CHAR(64 MATCH($G$1,$A$1:$D$1,0))&ROW())来代替$A1就可以实现不同字段的查找。
(3)公式解析:
①MATCH($G$1,$A$1:$D$1,0)查询到查找依据G1单元格内容在表头的位置;
②CHAR(64 MATCH($G$1,$A$1:$D$1,0))将MATCH查找到位置转换成列的位置(例如MATCH查找到的值为2,CHAR函数通过 64可以将其转化为B);
③ROW()函数返回当前单元格所在的行;
④CHAR函数与ROW函数连接在一起构造成单元格B1、B2、B3样式的地址;
⑤"$" &连接④构造成对行的绝对引用的单元格地址;
⑥最后用INDIRECT引用构造的单元格地址的内容。
四.最终制作过程:
选择A:D列单元格区域→开始→条件格式→新建规则→使用公式确定要设置格式的单元格输入公式→输入公式
=ISNUMBER(FIND($G$2,INDIRECT("$" &CHAR(64 MATCH($G$1,$A$1:$D$1,0))&ROW())))→格式→填充→选择一个颜色→确定→确定。
,
免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com