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.注意單元格的引用方式，不要混淆絕對引用和相對引用。