Excel用定义名称、万金油公式制作查询器，可模糊、不同字段查找

之前写过一篇文章Excel表格使用公式制作信息查询器（可模糊查找、不同字段查找），有粉丝朋友说公式太长太复杂看不太明白，希望我可以分享一个更容易理解的方法制作一个信息查询器。今天就为朋友们分享一种使用定义名称和万金油公式制作信息查询器的方法。

一.效果图

还是先给朋友们看一张动态效果图看看这个信息查询器的神奇之处（与之前的效果一样只是公式更简单容易理解）。

二.主要相关函数用法介绍

1.Offset函数

(1)语法：

Offset(reference,rows,cols,height,width)

(2)各参数意义：

Reference：作为参照系的引用区域，其左上角单元格是是偏移量的起始位置；

Rows：相对于参照系左上角单元格，上下偏移的行数（向下为正，向上为负）；

Cols：相对于参照系左上角单元格，左右偏移的行数（向右为正，向左为负）；

Height：新引用区域的行数；

Width：新引用区域的列数。

（3）用法演示（本演示参考系为A1单元格，向下偏移2，向右偏移2，返回一个3行2列的单元格区域。）

2.MATCH函数（在这里只用到第三个参数为0的精确匹配）

（1）语法：

MATCH(lookup_value, lookup_array, [match_type])

（2）各参数意义：

lookup_value 查找值，参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

lookup_array 必需。 要搜索的单元格区域。

match_type 可选。 数字 -1、0 或 1。

3.INDEX函数（在这里只用到第一个参数为array形势，也就是数组形式）

（1）功能：

返回表或数组中元素的值，由行号和列号索引选择。

当函数 INDEX 的第一个参数为数组常量时，使用数组形式。

（2）语法

INDEX(array, row_num, [column_num])

(3)各参数意义：

array 必需。 单元格区域或数组常量。

如果数组只包含一行或一列，则相应的 row_num 或 column_num 参数是可选的。

（4）在这里只用到根据row_num返回一列数组里的元素。

（5）用法演示（本演示利用ROW()函数构造一个数字1、2、3...的序列返回A列对应的单元格内容）

4.SMALL函数

语法：SMALL(array,k)

功能：返回array（通常是一个数组）中第K个最小值。

5.row（）函数

语法：ROW([reference])

功能：返回引用单元所在的行

6.ISNUMBER( )函数

语法：ISNUMBER([reference])

功能：判断是否为数字

7.FIND( )函数

(1)语法：FIND(find_text,within_text,start_num)

(2)各参数意义：

Find_text 是要查找的字符串。

Within_text 是包含要查找关键字的单元格。就是说要在这个单元格内查找关键字。

Start_num 指定开始进行查找的字符数。如果忽略 start_num，则假设其为 1。

（3）说明：找不到查找内容时返回错误值#VALUE!

三.制作方法

1.根据查找依据的字段值定义一个名称

（1）在H2输入公式：

=OFFSET($A$1,0,MATCH($G$1,$A$1:$D$1,0)-1,COUNTA($A:$A),1)

（2）公式解析：

COUNTA($A:$A)函数返回A列非空单元格的个数；

MATCH($G$1,$A$1:$D$1,0)函数返回查找依据字段在原始数据表头的列数；

整个函数返回的是一个查找字段所在列所有数据构成的数组。

（3）定义名称时要在引用单元格的位置之前加上“工作表名！”

定义名称名字为：查找依据数组。

输入的公式为：

=OFFSET(信息表!$A$1,0,MATCH(信息表!$G$1,信息表!$A$1:$D$1,0)-1,COUNTA(信息表!$A:$A),1)

2.输入公式查找

（1）在H2输入公式：

=INDEX(A:A,SMALL(IF(ISNUMBER(FIND($G$2,查找依据数组)),ROW(查找依据数组),1000000),ROW(A1))) &""

（2）公式解析：

1.FIND($G$2,查找依据数组)，如果查找依据数组元素内包含G2单元格内容返回其位置，否则返回错误值，最终返回一个由数字和错误值构成的数组。

2.ISNUMBER(FIND($G$2,查找依据数组)，返回的是由逻辑值构成的数组。

3.IF(ISNUMBER(FIND($G$2,查找依据数组)),ROW(查找依据数组),1000000)，根据逻辑值构成的数组，当数值中的元素为true时，返回对应元素所在的行，当数值中的元素为FLASE时，返回对一个较大的数值1000000.（这一步构造了数组元素与查找值匹配时返回其行数，否则返回一个较大值1000000）

4.SMALL(IF(ISNUMBER(FIND($G$2,查找依据数组)),ROW(查找依据数组),1000000),ROW(A1))，ROW(A1)向下填充时返回一个数字1、2、3构成的序列，用SMALL函数依次返回数组中第1、2、3个最小值。（这一步构造了数组元素与查找值匹配时其对应行数构造成的数组）

5.INDEX(A:A,SMALL(IF(ISNUMBER(FIND($G$2,查找依据数组)),ROW(查找依据数组),1000000),ROW(A1))) ，最终由INDEX分别提取之前构造的数组所对应的所有值。

6.公式最后的&””是所有元素匹配完成后继续向下填充公式会显示数字0，&””可以避免数字0的出现。

四.注意事项：

1.这里涉及众多数组公式，所以在确定公式时要同时按住Ctrl+Shift+Enter。

2.注意单元格的引用方式，不要混淆绝对引用和相对引用。