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單元格區域，設置數據有效性情況如下圖所示：

其中 來源 =OFFSET(數據重組!$E$1,MATCH(D3,數據重組!$D$1:$D$9,0)-1,0,COUNTIF(數據重組!$D$1:$D$9,D3),1)

具體操作過程及效果如下動圖所示：

4 生成三級菜單下拉列表

因為一級菜單對應的二級菜單沒有重複值，接下來生成三級菜單下拉列表完全可以采用與二級菜單下拉列表同樣方法完成。

如果專業選擇的是“動漫設計”，首先要去确定“動漫設計”在H列當中的起始位置，然後去統計H列當中“動漫設計”的個數，接下來在I列當對應的區域就可以找到 班級的位置。

我們要得到所選專業對應的班級選區，采取 =OFFSET($I$1，偏移行數，0，選擇行數，1) 完成。

偏移行數和選擇行數，與二級菜單的方法類似。

選擇 信息登記表 中 F3:F12單元格區域，設置數據有效性情況如下圖所示：

其中，來源 =OFFSET(數據重組!$I$1,MATCH(E3,數據重組!$H$1:$H$19,0)-1,0,COUNTIF(數據重組!$H$1:$H$19,E3),1)

具體操作過程及效果如下動圖所示：

5 總結與思考

到此，給大家介紹了兩種操作多級下拉菜單聯動的方法：名稱法和公式法。

名稱法，理解簡單，操作繁瑣程度與數據選項的多少有關系；公式法，理解需要對公式進行分析，但是操作起來方便，尤其對于大批量數據。

同時請小夥伴們思考一個問題：在講解案例當中，二級學院的專業都是唯一的，也就是沒有重複值。那如果有重複值應該如何解決這樣的問題呢？

如下案例：

---

為方便小夥伴們學習，進階君将原始素材共享出來，獲取素材的方法：

第一步：關注 Excel進階課堂。

第二步：私信 Excel進階課堂，因為設定的是自動回複，所以内容一定要準确

私信内容：練一練

第三步：根據得到信息打開網盤，找到 第24講 公式法解決多級下拉菜單聯動 工作簿 自行下載

,

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

查看全部