原文標題：《拒絕加班！這份 Excel 下拉列表超全合集你值得擁有！》

今天講一講「Excel 下拉列表的那些事兒」~

一級下拉列表

一個簡單的數據驗證，搞定一級下拉列表。

制作思路是將分類項目單獨放在一個參數表中，然后通過【數據驗證】引用這些參數作為數據源。

具體的設置方法如下：

? 打開【數據驗證】窗口。

? 驗證條件選擇【序列】。

? 選擇添加數據來源。

像產品類型、部門、省市等比較固定的分類信息，都可以利用下拉列表限制輸入的內容，避免一種分類、多種寫法的情況出現。

二級下拉列表

定義名稱 + 數據驗證 + INDIRECT 函數，輕松創(chuàng)建二級下拉列表。

所謂二級下拉列表，就是第 2 級的列表選項，可以根據第 1 級的數據動態(tài)更新。

具體的設置方法如下：

? 準備二級下拉列表數據

? 定義名稱

選擇所有的列表數據，單擊【公式】選項卡，找到「定義的名稱」-【根據所選內容創(chuàng)建】：

在彈出的窗口中，勾選「首行」，單擊【確定】。

這樣就給二級內容起了個總體的名字，這個名字是「首行」單元格的內容。比如：

? 制作下拉列表

先設置好一級下拉列表，具體操作前文講過，這里就不重復了。

二級和一級下拉列表的步驟大同小異，只是在選擇來源時，需要用到 Indirect 函數：

PS：提示「源當前包含錯誤」，是因為「二級列表」引用的「一級列表」單元格中沒有數據，導致源錯誤，不用理會，點「是」。

敲黑板：

=indirect(A2)

Indirect 函數是間接引用函數，可以返回由文本字符串所指定的引用。

比如這里引用的是 A2 單元格，但返回的結果是參數表 C2:C5 單元格里的值。即：引用 C 列省份中所包含的市級。

三級下拉列表

三級下拉列表其實并沒有大多數小伙伴想象中那么難，如果你能學會一級、二級列表，我相信，這個小技巧也難不住你。

相比前兩者，三級下拉列表最大的不同，就是數據源。

三級內容的表頭，是由一二級連接在一起的?？磮D更直觀 ↓

具體的設置方法如下：

? 定義名稱

選擇列表數據，使用【定位】功能快速選擇所有的非空單元格。

再使用【公式】選項卡中的【根據所選內容創(chuàng)建】的功能，批量的創(chuàng)建省份對應城市選區(qū)的自定義名稱。

哦豁，好像翻車了……

Excel 提示「此選擇無效」。

仔細觀察一下表格，我們發(fā)現，有一處明顯沒有和其他區(qū)域連在一起，這個就是報錯的原因，Excel 重新識別并選中了一個新的區(qū)域。

這是由于 Excel 本身的機制引起的，如果出現了連續(xù)兩列行數相等且后面的列比它們的行數少，就會報錯。

不信的話，我們稍微調整一下列順序，把這兩列分開 ↓

然后再試一遍【定義名稱】。

最后檢查一下：

設置成功！

? 創(chuàng)建三級下拉列表。

這時還是需要借助【Indirect】函數，但是又稍微有點不同，公式：

=Indirect（A2&B2）

即：同時引用一級列表和二級列表。

是不是很簡單！

一二三級下拉列表，只涉及到了幾個非?；A的知識點：

? 數據驗證

? 定義名稱

? Indirect 函數

看完這篇文章，再動手練習一下，想必就掌握得七七八八了。

但我接下來要講的【搜索式下拉列表】，就稍微有那么 億  一點點難了。

搜索式下拉列表

當你在微信搜索框里輸入關鍵詞【秋葉 Excel】并確定，你可以搜索出這個平臺里所有與【秋葉 Excel】相關的內容。

而搜索式下拉列表也是如此，雖然沒有微信搜索那么強大，但是它可以實現在 Excel 通過搜索關鍵詞，找到設置好的、固定的數據，快速選擇且錄入表格。

具體設置方法：

? 根據關鍵詞創(chuàng)建輔助列

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

② 創(chuàng)建根據關鍵詞篩選的輔助列：

將下列公式填入 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，就是完整的省份列表所在單元格。

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

? 定義輔助列名稱

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

② 新建名稱，名稱區(qū)輸入「省份列表」，引用位置輸入公式：

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

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

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

? 設置下拉列表

選中需要設置下拉列表的單元格，打開數據驗證窗口，在驗證條件對話框的允許中選擇「序列」，來源填寫「= 省份列表」；

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

完成！

如果你只想知道怎么設置搜索式下拉列表，學會上面這些就夠了；

最后，能夠看到這里的同學，真的太不容易了！希望大家都能成功掌握上面這些知識點，提高效率，減輕工作壓力，擁抱生活！

本文來自微信公眾號：秋葉 Excel （ID：excel100），作者：竺蘭，編輯：竺蘭

廣告聲明：文內含有的對外跳轉鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時間，結果僅供參考，IT之家所有文章均包含本聲明。