Excel多級下拉列表的製作方法

07-13

【摘要】

在Excel中使用下拉列表的功能，能幫助我們限制填寫的內容，保證數據的有效無誤。然而常規的數據有效性（下拉列表）我們都會製作，可是如果要製作更多級別的數據有效性，似乎有點困難了。那麼在本文中，將教大家製作多級別的下拉列表。

【正文】

一一級下拉列表

在製作表格的時候，希望為一些具有固定選項的列（如性別、部門等），添加下拉框，製作如下圖的效果，那我們就可以利用數據有效性來完成。

設置步驟：

1、單擊【數據】選項卡中的【數據有效性】，在「數據有效性」對話框的「設置」選項卡中，在「允許」下拉列表框中選擇「序列」項。在「來源」框中直接輸入項目，項目之間用英文逗號分隔。

2、如果下拉框中的數據比較多，在一個連續的單元格區域中輸入列表中的項目，如下圖所示。

單擊【數據】選項卡中的【數據有效性】，在「數據有效性」對話框的「設置」選項卡中，在「允許」下拉列表框中選擇「序列」項。

在「來源」框中選擇部門列表下的數據，單擊「確定」按鈕。

二二級下拉列表

在填寫地址時，當確定省份後，城市一欄內自動顯示對應省份下的城市列表，方便我們進行選擇。像這樣的效果我們稱為二級下拉列表。

設置步驟：

1、為各個省份定義名稱

製作二級下拉菜單時，首先需要為各個省份的城市分別定義名稱，之後才能根據省份讀取到相應的城市。定義名稱時，先選中廣東省下所有城市（I1：I22），在【公式】選項卡下點擊「根據所選內容創建」，然後勾選「首行」並點擊「確定」，完成「廣東省」的名稱定義。以同樣的方法，定義名稱「湖南省」和「湖北省」。

2、為「省份」一列設置下拉菜單，來源可選擇I1：K1。

3、選擇「城市」一列，在「數據有效性」中選擇「序列」，並在「來源」處輸入公式：=INDIRECT(D2)，點擊「確定」。

註：①錄入公式時需要切換單元格的引用方式。②若D2單元格為空，則可能會彈出錯誤警告，點擊「是」即可。③設置成功後，若未選定「省份」，則「城市」一列也無法進行選擇。

三多級拉列表

我們除了會填寫「省份」、「城市」外，還會選擇「區」，那這種我們稱為多級下拉列表。我們可以利用Vlookup、Offset、match、countif函數共同實現該功能。

設置步驟：

先來了解這幾個函數，其語法分別為：

Match（查詢值，查找範圍，0），返回符合特定值特定順序的查詢值在數組中的相對位置；

Countif（條件範圍，條件），計算區域中滿足給定條件的單元格的個數；

Vlookup（查詢值，查找範圍，顯示序列，匹配參數），搜索表區域首列滿足條件的元素，確定待檢索單元格在區域中的行序號，再進一步返回選定單元格的值；

Offset（參考單元格，偏移的行數，偏移的列數，返回引用區域的行數，返回引用區域的列數），以指定的應用為參照系，通過給定偏移量返回新的應用。

數據源需要按如下圖排列：

在C2單元格我們藉助於Match函數，計算「廣東省」在A列中的位置，因此該函數為：=MATCH(B2,A:A,0)。隨後將該函數分別複製至C3、C6、C7、C8、C9單元格即可計算對應的項在A列中的起始位置，該數值用於指導offset函數往下偏移幾行；
接下來要計算每個項目共有幾個小項，在D2中利用countif函數計算個數，此處的公式為：=COUNTIF(A:A,B2)。該數值可以用在offset函數中的返回行數中；
最後在G列設置一級下拉列表。如圖：

對二級「市」設置數據有效性。因為我們需要根據一級G2單元格選擇的不同，設置不一樣的下拉列表，而每個一級「省」會有不一樣個數的二級「市」，所以我們藉助offset函數來完成。在H2單元格設置數據有效性的「來源」位置，輸入以下公式：=OFFSET($B$1,VLOOKUP(G2,$B:$D,2,0)-1,0,VLOOKUP(G2,$B:$D,3,0),1)。

該公式的意思為：以B1單元格為參考單元格，往下偏移幾行，往右不偏移列，返回引用區域的行數，返回一列的數據。那麼往下偏移幾行，要根據前面的G2單元格的內容變化，所以利用vlookup函數來查找G2單元格的內容，位於B:D範圍中第二列的結果，我們便可以從B1單元格往下偏移6行至B7單元格，再減去1，得到「廣州市」的B6單元格；同樣的，返回引用區域的行數，也藉助vlookup函數來得到，如此一來，二級下來列表的「市」也就完成了。

接下來，我們就用同樣的offset函數來製作三級下拉列表，因此在I2單元格的數據有效性的公式為：=OFFSET($B$1,VLOOKUP($H$2,$B:$D,2,0)-1,0,VLOOKUP($H$2,$B:$D,3,0))

最後的效果為：

那麼有了這種方法以後，我們想設置任意級別的下拉列表都可以實現了。