【摘要】

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

【正文】

一 一級下拉列表

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

設置步驟：

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

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

• 在“來源”框中選擇部門列表下的數據，單擊“确定”按鈕。

二 二級下拉列表

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

設置步驟：

1、為各個省份定義名稱

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

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

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

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

三 多級拉列表

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

設置步驟：

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

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

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

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

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

1. 在C2單元格我們借助于Match函數，計算“廣東省”在A列中的位置，因此該函數為：=MATCH(B2,A:A,0)。随後将該函數分别複制至C3、C6、C7、C8、C9單元格即可計算對應的項在A列中的起始位置，該數值用于指導offset函數往下偏移幾行；

2. 接下來要計算每個項目共有幾個小項，在D2中利用countif函數計算個數，此處的公式為：=COUNTIF(A:A,B2)。該數值可以用在offset函數中的返回行數中；

3. 最後在G列設置一級下拉列表。如圖：

1. 對二級“市”設置數據有效性。因為我們需要根據一級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函數來得到，如此一來，二級下來列表的“市”也就完成了。

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

最後的效果為：

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

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!