Excel中怎樣實現二級、多級（三、四級）聯動下拉選單

Excel中下拉選單功能可以幫助我們節省輸入時間，透過選取下拉選單中的值來實現輸入資料，非常快捷、方便。但是日常工作中，我們常需要一個下拉選單，讓後面的下拉選單依據前面的下拉選單的內容的改變而改變（也就是聯動的下拉選單）。

一、二級聯動下拉選單製作方法

首先看一下原始資料，原始資訊在一張工作表，第一行是省市名稱，下面的若干行為對應省市下面的地名和區名，如圖1。需要在另外一張工作表中A列和B列建立聯動的二級下拉選單，如圖2。

圖1

圖2

操作步驟如下：

Step1：選中原始表所有資料，按快捷鍵F5調出定位對話方塊，定位條件選擇“常量”，點選“確定”按鈕，這樣所有非空單元格被選中。如圖3。

圖 3

Step2：點選功能區選單“公式”→“根據所選內容建立”，如圖4，因為標題在首行，所以選擇“首行”作為名稱，點選“確定”按鈕，如圖5。操作完畢後在選單“公式”下的“名稱管理器”就可以看到定義的名稱了，如圖6。

圖 4

圖 5

圖 6

Step3：在另外一張工作表建立標題行，省/直轄市和市/區，選中A2單元格，點選選單“資料”→“資料驗證”（注：2013版本的“資料驗證”在2003、2007、2010版本是“資料有效性”），驗證條件選擇“序列”，來源選中原始資料表的首行資料，如圖7。這樣，在A2選單就生成了省市下拉選單，如圖8。如果需要在更多的單元格區域設定下拉選單，就選中更多的單元格區域，比如A2：A20，切忌選中整列區域，如果選中整列，會導致在很多沒有用的區域設定了資料有效性，增加了檔案的虛擬記憶體，使得檔案變大，檔案變大會導致開啟和各種操作都會非常慢。

圖 7

圖 8

Step4：同樣的方法，選中B2單元格，點選資料驗證，在“來源”處輸入公式=INDIRECT（$A$2），點選“確定”按鈕。設定完畢後，A2單元格選擇“深圳市”時B2的下拉選單返回“深圳市”的資訊，如圖10；A2單元格選擇“北京市”時B2的下拉選單返回“北京市”的資訊，如圖11。

注意：

上述二級下拉選單設定的公式採取了行列都絕對引用，如果要使二級下拉選單對更多的單元格區域均可用，將公式更改為：=INDIRECT（$A2）即可。

圖 9

圖10

圖11

indirect函式功能是返回並顯示指定引用的內容，可引用其他工作簿的名稱、工作表名稱和單元格引用。製作多級下拉選單的原理就是利用定義名稱，然後在單元格輸入與定義名稱相同的字元，再對含有這種字元的單元格用Indirect作引用。

操作動畫如下：

二、多級（三、四級）聯動下拉選單製作方法

下面介紹多級別的下拉列表的製作方法。

資料來源按下面的順序排序：

圖12

在製作多級下拉選單之前我們先來了解這幾個函式，其語法和功能分別是：

Match（找什麼，在哪裡找，0）：返回符合特定值特定順序的查詢值在陣列中的相對位置；

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

Vlookup（找什麼，在哪裡找，顯示序列，匹配引數）：搜尋表區域首列滿足條件的元素，確定待檢索單元格在區域中的行序號，再進一步返回選定單元格的值；

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

瞭解了函式的功能，接下來按以下步驟操作：

Step1：在C2單元格藉助於Match函式，計算“廣東省”在A列中的位置，因此該公式為：=MATCH（B2，A：A，0）。隨後將該公式分別複製至C3、C6、C7、C8、C9單元格即可計算對應的項在A列中的起始位置，該數值用於指導offset函式往下偏移幾行；

Step2：接下來要計算每個專案共有幾個小項，在D2中利用countif函式計算個數，此處的公式為：=COUNTIF（A：A，B2）。該數值可以用在offset函式中的返回行數中；

Step3：最後在G列設定一級省份下拉列表，如圖13：

圖 13

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

圖 14

該公式的意思為：

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

圖15

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