Excel如何設定多級聯動下拉選單（Excel多級下拉選單怎麼設定）

2023-10-08 12:14:33

EXCEL進階課堂 · 函式說持續更新！我們將為各位小夥伴提供更加專業、更加精煉、更加實用的EXCEL操作技能，幫助大家輕鬆解決工作任務，提高工作效率，不再做不停加班的表哥，表姐。歡迎各位小夥伴轉發、點贊、討論，更歡迎私信獲取練習素材，刻意練習才能學有收穫。

這是函式說的第24篇教程。

在上一篇教程中，我們用名稱法解決了多級下拉選單聯動的問題，總體比較簡單，大家的學習成本很低。

「函式說 23」多級下拉選單聯動？一種簡單方法：名稱 INDIRECT，分分鐘搞定

但是，正如文章最後提到：如果每級涉及的選項很多時，需要去做的名稱就會很多，反而變得操作繁瑣了。

有沒有更好的地解決辦法呢？當然有。進階君接下來給大家解講一種高階的方法——公式法來解決這個問題。為了讓小夥伴們有一個比對，我們仍然使用這個例項。

1 生成三級選單對應資料關係表

根據填表說明，我們增加一個工作表，取名為“資料重組”，形成三級選單對應資料關係，如圖所示：

觀察可以發現，在二級選單對應區裡面，專業是沒有重複的情況，接下來要講的方法對於二級選單對應資料沒有重複的情況是有通用性的。如果有重複怎麼辦，進階君可以用特殊方法處理。

2 生成一級選單下拉選單

生成一級選單下拉選單非常簡單，直接用資料有效性來實現。

（1）選擇單元格區域：用滑鼠拖動的方法，選擇“資訊登記表”中的D3:D12單元格區域

（2）設定資料有效性：利用資料選單中的資料有效性，設定資料有效性情況如下圖所示

需要提醒的是：來源 =資料重組!$B$2:$B$5 ，是取的 資料重組 工作表中的 一級選單對應區。具體操作過程及效果如下動圖所示：

3 生成二級選單下拉選單

根據二級選單對應關係可以知道，二級選單的專業選項與一級選單二級學院的密切相關。我們通過例子，來分析工作表當中，專業與學院的之間對應關係。

如果二級學院選擇的是“軟體學院”，首先要去確定“軟體學院”在D列當中的起始位置，然後去統計D列當中“軟體學院”的個數，接下來在E列當對應的區域就可以找到專業的位置。

我們的目的是要得到二級學院對應的專業，也就是選項區域。在我們選學的函式當中，OFFSET函式的結果就是一個區域。

OFFSET函式的格式：=OFFSET(參照單元格，偏移行數，偏移列數，選擇行數，選擇列數)

要得到二級學院對應專業所在選區：=OFFSET($E$1，偏移行數，0，選擇行數，1)

於是問題就落腳在如何得到偏移行數和選擇列數？

（1）確定偏移行數

偏移行數：是不是選擇的二級學院在D列當中的起始位置-1。為什麼要減1，因為我們要的偏移數，E1單元格，向下偏移1行，就得到了E2單元格。圖中，軟體學院在D列當中的起始位置是4，4-1=3，E列當中，以E1單元格向下偏移3行，於是得到了E4單元格，而E4單元格恰恰是“軟體學院”的第一個專業——“軟體工程”。

怎樣得到偏移行數呢？用MATCH函式。

=MATCH（選擇的二級學院，資料重組!$D$1:$D$10）-1

（2）確定選擇行數

選擇行數：是不是選擇的二級學院在D列當中的個數。圖中，軟體學院在D列當中的個數為2，於是在E列當中，從E4單元格開始，選取2行，即 E4：E5，恰恰是軟體學院對應的專業所在區域。

怎樣得到選擇行數？用COUNTIF函式。

=COUNTIF（資料重組!$D$1:$D$10，選擇的二級學院）

（3）運用資料有效性，設定二級選單下拉選單

選擇 資訊登記表 中 E3:E12單元格區域，設定資料有效性情況如下圖所示：