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

這是函數說的第25篇教程。

多級下拉菜單聯動，進階君已經做了兩篇教程，分别采取名稱法和公式法進行實現。如果錯過的小夥伴，可以分别點擊下面的鍊接進行學習。

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

「函數說 24」多級下拉菜單聯動，名稱太多容易暈？高級招數：一個公式輕松搞定

在用公式法解決多級菜單聯動的教程中，進階君留下了一個尾巴，不知道小夥伴們有沒有進行思考。隻有把這個尾巴割掉，公式法才算完美了。

1 問題引入

有這樣一個案例，數據如下圖所示：

要求用公式法實現學校、二級學院、專業之間的三級下拉菜單聯動。

2 問題分析

通過上篇教程我們可以知道，用公式法實現多級下拉菜單聯動是從第二級菜單開始。

公式法的本質：将上一級菜單的選擇項，在當前級菜單的數據對應項去查找，查找上一級選項的開始位置和個數，然後運用OFFSET函數去獲取當前級菜單數據項的區域。

于是，在當前級的數據對應項中，上一級選項對應的數據區域不應該有重複，否則，就不能正确的查找到開始位置和個數。

如要完成的案例中，三級菜單對應的數據區域如下圖所示：

我們要根據上一級菜單“二級學院”的選項來确定本級菜單“專業”的數據選項區域，按公式法通常做法，是根據上一級菜單的選項來确定它所在起始位置和個數，但是我們發現，如果我們選擇軟件學院的話，在對應的區域中會有2個數據區域與之對應，這樣就無法确定起始位置和個數了。

怎麼處理呢？将多個對應的數據區域由多個變成一個。

處理思路：更改三級菜單對應的數據區域，将三級菜單對應的上一級菜單變成一級菜單和二級菜單的聯合。

這樣處理後，專業對應的上一級菜單就是學校和二級學院的聯合，如京東大學軟件學院，在數據區域裡面就隻會有一個區域與之對應。由多個區域變成一個區域後，公式法就可以完美實現了。

3 問題解決

在實際工作當中，我們得到的數據表最有可能是上圖所示。我們要完成多級菜單聯動，需要先形成各級菜單對應的數據關系表，然後再利用公式法完成。

（一）形成各級菜單對應的數據關系表

（1）巧用删除重複項，形成一級菜單數據表

第一步：複制表中“學校”這列數據到單獨一列。

第二步：運用 數據菜單 下的 删除重複項 命令，得到一級菜單數據表。這種方法非常簡單且高效。

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

（2）巧用删除重複項，形成二級菜單數據對應表

第一步：複制表中“學校”和“二級學院”兩列數據到單獨區域。

第二步：運用 數據菜單 下的 删除重複項 命令，得到二級菜單數據對應表。

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

（3）巧用公式，形成三級菜單數據對應表

第一步：運用公式将“學校”和“二級學院”兩列數據聯合在一起，放到單獨一列中。

表中“學校”數據從B2開始往下，“二級學院”數據從C2開始往下，公式：=B2&C2，表示将兩個單元格的内容聯合在一起。其它區域采取公式複制的方式完成。

第二步：複制表中“專業”數據列到第一步位置右側的區域。

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

（二）運用數據有效性，完成一級菜單設定

根據一級菜單數據表，運有數據有效性完成一級菜單設定。具體操作過程及效果如下動圖所示：

（三）運用數據有效性 公式，完成二級菜單設定

選中H3單元格，設置數據有效性，設定内容如下圖所示：

其中 來源：=OFFSET($J$6,MATCH(G3,$I$6:$I$13,0)-1,0,COUNTIF($I$6:$I$13,G3),1)

對于公式不是很理解的，請查看進階君的上一篇教程：

「函數說 24」多級下拉菜單聯動，名稱太多容易暈？高級招數：一個公式輕松搞定

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

（四）運用數據有效性 公式，完成三級菜單設定

在前面，我們已經得到了處理後的三級菜單數據對應表，如下圖所示：

此時我們要去找專業的話，應該根據一級和二級菜單的選擇項的聯合值去查找。在案例當中，一級和二級菜單的選擇項的聯合值可以用 G3&H3 得到。

選中I3單元格，設置數據有效性，設定内容如下圖所示：

其中 來源：=OFFSET($M$6,MATCH(G3&H3,$L$6:$L$21,0)-1,0,COUNTIF($L$6:$L$21,G3&H3),1)

最終完成效果如下動圖所示：

總結與思考

在本篇教程中，講解了對于不同的一級菜單選項，二級菜單選項出現重複值的情況，我們用了一種基本思想：将一級菜單選項和二級菜單選項進行聯合，從而形成不出去現多個值相同區域。

這種方法學習後，在處理以後的多級下拉菜單聯動時，都可以完美解決。

---

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

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

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

私信内容：練一練

第三步：根據得到信息打開網盤，找到 第25講 解決重複問題的多級下拉菜單聯動 工作簿 自行下載。

,

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

查看全部