編按：

哈嘍，大家好！多級下拉菜單網(wǎng)上有很多教程，但今天的方法是最簡單的。不需要定義名稱，只使用一個公式就可以制作二級、三級、四級甚至更多級的菜單。公式用的函數(shù)也很常見，offset、match、countif。趕緊來看看吧！

制作二級三級菜單已經(jīng)不是新問題了，關于這方面的教程咱們之前也分享過很多，比如《還不會做Excel三級下拉菜單？其實它跟復制粘貼一樣簡單》。

傳統(tǒng)的方法，要做出二級三級菜單，少不了定義名稱這個步驟，而且對于菜單內(nèi)容（數(shù)據(jù)源）的排列方式要求比較高，并且當不同選項下的內(nèi)容數(shù)量不一樣多時，下拉選項中會出現(xiàn)空白項。

今天要分享的多級菜單制作方法，在操作上大大降低了難度，而且不管制作多少級的下拉菜單，都是一個公式套路搞定。還是用一個省、市、區(qū)的數(shù)據(jù)來做介紹，數(shù)據(jù)源如下。

在進行下拉菜單的設置之前，還是需要對這個原始數(shù)據(jù)源做點處理，不過非常簡單。

第一步：將省這一列復制出來，刪除重復項。

第二步：將省市這兩列復制出來，刪除重復項。

第三步：將市區(qū)這兩列復制出來，因為數(shù)據(jù)只有三級，所以市區(qū)是不會有重復項的。

如果還有四級五級菜單，相信也知道該如何處理了吧，至此，數(shù)據(jù)源就處理完成了。

接下來進入下拉菜單的設置，同樣非常簡單。

一級菜單設置，直接使用數(shù)據(jù)驗證（數(shù)據(jù)有效性）最基本的序列即可。

注意：一級菜單的內(nèi)容相對比較固定，所以直接選擇數(shù)據(jù)源區(qū)域即可。這里下拉選項的位置和數(shù)據(jù)源的位置是為了動畫演示方便才放置到一個sheet里，實際使用中，數(shù)據(jù)源可以單獨存放在一個sheet里。下拉選項的位置根據(jù)自己的需要靈活設置即可。

二級菜單設置，這一步開始，就要用到今天的主角了，由OFFSET、MATCH和COUNTIF共同構造的一個公式套路，公式為：

=OFFSET($R$1,MATCH(G2,Q:Q,0)-1,,COUNTIF(Q:Q,G2))

千萬不要被這個公式嚇住，其實這個公式是很好理解的，以下就為大家破解這個公式的秘密。

首先我們要明白OFFSET這個函數(shù)是干什么的。

簡單來說，OFFSET是一個引用函數(shù)，可以為我們得到一個特定的單元格區(qū)域（可以理解為得到該區(qū)域中的一組數(shù)據(jù)），例如上面這個公式表面上得到的是一個錯誤值：

其實當我們在編輯欄選中公式，按F9鍵以后，看到的是這樣的結(jié)果：

之所以顯示錯誤值，是因為在一個單元格里無法顯示出一個區(qū)域（四個單元格）的內(nèi)容。

也就是說，公式得到了福建省所對應的市所在的區(qū)域，當?。?span>G2單元格）的內(nèi)容變化以后，公式結(jié)果也會隨之變化，還是通過F9鍵來看看變化后的結(jié)果。

或許大家發(fā)現(xiàn)了，這里的數(shù)據(jù)是智能調(diào)整的，也就是說，對應幾個市就顯示幾個市。

為什么會有這樣的效果呢，這就要從OFFSET的五個參數(shù)來說起了。

OFFSET（起始位置，行偏移量，列偏移量，高度，寬度），一般的教程里會這樣解釋OFFSET的五個參數(shù)，本例中，只用到了其中的1、2、4三個參數(shù)。

如果我們要得到某個省所對應的市，必定要在R列確定具體區(qū)域，因此第一參數(shù)使用$R$1就不難理解了，但是不同的省，范圍的起點是變化的，例如安徽省就要從第二行開始，福建省就要從第五行開始，這個問題就需要第二參數(shù)也就是行偏移量來起作用了。

行偏移量是個數(shù)字，當起始位置固定不變的時候，行偏移量的變化能使最終的區(qū)域發(fā)生變化。而要確定行偏移量，MATCH是最合適的。

MATCH(G2,Q:Q,0)的作用就是找到G2（某?。┰?span>Q列的第幾行首次出現(xiàn)，例如安徽省首次出現(xiàn)在第二行，但是請注意，第二行相對于第一行來說，行偏移量是1。因此OFFSET的第二參數(shù)應該是MATCH(G2,Q:Q,0)-1，如果還不清楚MATCH的用法，可以參考以往的教程《MATCH：函數(shù)哲學家，找巨人做伴。新出道必學！》。

第三參數(shù)列偏移量也是同樣的道理，本例中不涉及，所以直接逗號省略，進入第四參數(shù)。

可以說在MATCH的協(xié)助下，OFFSET準確定位到了目標區(qū)域的起點，那么目標區(qū)域到底是幾個單元格呢？每個省所對應的市不一樣多，目標區(qū)域也就不一樣大。

對于一列數(shù)據(jù)來說，區(qū)域的大小就是高度（行數(shù)），在本例中要確定這個指標用COUNTIF就非常方便了，COUNTIF(Q:Q,G2)的作用很顯然，就是確定要引用的省在Q列的個數(shù)。

同樣本例的數(shù)據(jù)都是單列，不涉及寬度（列數(shù)）的問題，第五個參數(shù)也就用不到了。

想更深入了解OFFSET函數(shù)的小伙伴，可以查看往期文章《Excel進階之路必學函數(shù)：動態(tài)統(tǒng)計之王——OFFSET（上篇）》。

至此，OFFSET已經(jīng)準確得到了區(qū)域的起點和高度，接下來只需要將這個公式應用到數(shù)據(jù)驗證（數(shù)據(jù)有效性）中即可。

方法非常簡單，在序列中將公式復制進去就好了。

至此，一個智能的二級菜單設置完畢，再次說明，這里的智能指的是可以按照選項內(nèi)容的多少自動進行調(diào)整，避免了空白選項的出現(xiàn)。

三級菜單的設置方法完全一樣，只是需要修改一下公式，由于公式的原理完全一樣，只是修改位置，所以有個直接用鼠標修改的方法，大家可以參考。

可以說，只要掌握了OFFSET-MATCH-COUNTIF這個公式套路，你就可以隨心所欲的制作多級智能菜單了。

本文配套的練習課件請加入QQ群：1043683754下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

初識數(shù)據(jù)驗證①《Excel小白的數(shù)據(jù)驗證課①用下拉菜單錄入的那些事兒》

搜索式下拉菜單《更高效的搜索式下拉菜單，你一定要懂！》

制作三級下拉菜單《還不會做Excel三級下拉菜單？其實它跟復制粘貼一樣簡單》

OFFSET函數(shù)（上）《Excel進階之路必學函數(shù)：動態(tài)統(tǒng)計之王——OFFSET（下篇）》