工作中，需要規範收集有固定選項的一類信息時，肯定首選用【數據驗證】製作下拉列表。

下拉列表不僅可以規範單元格錄入的內容，而且還快捷。看下圖，是不是很快！

可是，我在用下拉列表收集員工戶籍信息的時候，居然翻車了！！！ 因為戶籍列表太長，而慘遭嫌棄！！

於是，拋棄了我的表格之後，同事們就開始「花樣」填表。 其中，就一個廣西省，都可以填成這樣。

廣西的同胞們是想氣死我呢？還是笑死我？

敢情你們知道自己省份全稱的人不多呀，只知道是廣西。

既然你們只記得關鍵詞，那我就做個帶關鍵詞搜索的下拉列表吧！就像下圖這樣的：

有沒有覺得很高級~

想知道怎麼做的嗎？緊跟我的步伐，三步就能做出搜索式下拉菜單！

根據關鍵詞創建輔助列

❶ 在 A 列填寫完整的省份列表；

❷ 創建根據關鍵詞篩選的輔助列：

將下列公式填入 B2 單元格，使用【CTRL+SHIFT+ENTER】組合鍵結束公式，向下填充。

公式：

=IFERROR(INDEX($A$2:$A$35,SMALL(IFERROR(MATCH(IF(FIND(CELL(“contents”),$A$2:$A$35)>0,$A$2:$A$35,””),$A$2:$A$35,0),””),ROW(A1))),””)

上面的公式這麼長，是不是把你嚇到了？

公式雖然很長很難，但直接套用即可。

套用方法很簡單：

因為公式中 4 處標藍的部分是完全一樣的：$A$2:$A$35，就是完整的省份列表所在單元格。

所以，直接把標藍的部分換成你要做的列表區域就可以了！

如果簡單地套用公式，並不能滿足你的求知慾。

而是想知道這個公式，是怎麼得出篩選列表的！

FOLLOW ME！

（着急看下一步的同學，也可以直接滑到 02。）

下面要開始高能套娃了，準備好了嗎？

好的，我知道你們準備好了！接着往下看吧！

公式：

=IFERROR(INDEX([完整列表區域],SMALL(IFERROR(MATCH(IF(FIND(CELL(“contents”),[完整列表區域])>0,[完整列表區域],””),[完整列表區域],0),””),ROW(A1))),””)

公式解析：

理解很長很長的嵌套公式，最好是把它拆分出來逐步理解。

❶ CELL 函數

公式：

=CELL(“contents”)

使用這個公式可以獲取最後編輯的單元格內容，就是我們要搜索的動態關鍵詞。

為了更好理解，這裡先不使用 CELL 函數，直接以搜索包含關鍵詞”北”為例，我們把公式拆分出來看看。

輔助列 1：

公式：

B3=FIND(“北”,A3,1)

目標：判斷是否含有關鍵詞。

解析：FIND 函數的作用，是從 A3 單元格「河北省」的第 1 個字開始查找字符串”北”字，找到後就返回「北」字的位置。

「河北省」的第 2 個字符是”北”所以 B3 單元格顯示 2，如果找不到關鍵詞則返回#VALUE!。

輔助列 2：

公式：

C3=IF(B3>0,A3,””)

目標：將 FIND 的結果數字轉換為省份名稱。

解析：IF 函數的作用是，判斷條件 B3 單元格 2 是否大於零。

如果是，顯示 A3「河北省」；如果不是，顯示空值「」。

到這裡，我們其實已經得到了含有關鍵詞「北」的省份列表（輔助列 2）。

但是，它不能直接作為下拉菜單的列表，因為還包含了很多#VALUE!。

所以，接下來我們要去掉錯誤值，並且給含有關鍵詞「北」的省份列表重新排序。

輔助列 3：

公式：

D3=MATCH(C3,$A$3:$A$19,0)

目標：根據省份名稱，找到該原始列表的相對位置。

解析：MATCH 的作用是返回 C3 單元格「河北省」，在數組 A3 到 A19 單元格（即原始列表）中的相對位置，匹配方式是 0（即精確匹配）。

因為河北省在是原始列表 A3:A19 的第一個值，所以結果為 1。

輔助列 4：

公式：

E3=IFERROR(D3,1048765)

目的：去除錯誤值。

解析：因為輔助列 5 使用 SMALL 函數進行排順序，但是該函數不支持錯誤值。

所以，這一步先使用 IFEERROR 函數將錯誤值替換為空值””。

輔助列 5：

公式：

F3=SMALL($E$3:$E$19,ROW(A1))

目的：對列表進行排序，使有關鍵詞的省份排在前面。

解析：ROW(A1)函數的作用是獲取單元格的行號，結果是 1，這裡的作用是構建一個隨行號遞增的數列 1、2、3……

SMALL 函數的作用是返回數組 E3 至 E19 單元格（輔助列 4）中第 1 小的值，結果是 1。

輔助列 6：

公式：

G3=NDEX($A$3:$A$19,F3)

目的：根據相對行號找到對應省份。

解析：INDEX 函數的作用是在數組 A3 至 A19 單元格（原始列表）中找到第 1（F3 單元格）個單元格的內容，結果是河北省。

這一步也會有很多錯誤值（#NUM!），同樣可以使用 IFERROR 將其替換為空值。

到輔助列 6 位置，我們已經獲得了含關鍵詞的省份列表。

如果想要使用一列搞定的話，就是把套（函）娃（數）給組（嵌）裝（套）起來！

嵌套要將公式稍作改動，改成數組公式，這裡就不展開啦~

定義輔助列名稱

❶ 點擊【公式】選項卡-【名稱管理器】-新建名稱。

❷ 新建名稱，名稱區輸入「省份列表」，引用位置輸入公式：

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$35)-COUNTIF(Sheet1!$B$2:$B$35,””),1)

公式解析：

Sheet1!$B$2:$B$35 就是輔助列。

❶ COUNTA(Sheet1!$B$2:$B$35)

是獲取輔助列非空單元格的個數。儘管 B5 單元格的公式結果是空值「」，但是仍然屬於非空單元格。

❷ COUNTIF(Sheet1!$B$2:$B$35,””)

是獲取輔助列空值「」的個數。

❸ 使用 OFFSET 函數

構建一個動態的列表區域。以 B2 單元格為起點，向下偏移 0 個單元格，向下偏移 0 個單元格，長度為②-①（即關鍵詞的匹配數），寬度是 1。

這裡關鍵詞是「北」，匹配數是 3，所以整個公式得到的結果就是 B2 到 B4 這個區域。

根據關鍵詞，區域會動態變化。

設置下拉列表

❶ 選中需要設置下拉列表的單元格，點擊【數據】選項卡-【數據驗證】–「數據驗證」；

❷ 在驗證條件對話框的允許中選擇「序列」，來源填寫「=省份列表」；

❸ 點擊「出錯警告」選項卡，取消勾選「輸入無效數據時顯示出錯警告(S)」。

完成！

總結一下

❶ 搜索式下拉列表和多級下拉列表一樣，本質都是利用輔助列，創建動態的下拉選項。

❷ 需要注意的是，因為設置下拉列表時取消了出錯警告，所以數據驗證無法限制填寫內容。

❸ 本文使用的公式雖然很長，但好處是對 Excel 的版本沒有太高要求，Office2007 以上的版本和 WPS 都可以使用。

PS：如果是 WPS2019 及以上的版本，則自帶「搜索式下拉菜單」~

❹ 本文涉及的函數知識點相當多，有興趣延伸學習的同學可以看看我們往期的文章。覺得腦殼疼的同學也可以直接複製公式。