編按：哈嘍，大家好！多級(jí)下拉菜單網(wǎng)上有很多教程，但今天的方法是最簡(jiǎn)單的。不需要定義名稱，只使用一個(gè)公式就可以制作二級(jí)、三級(jí)、四級(jí)甚至更多級(jí)的菜單。公式用的函數(shù)也很常見(jiàn)，offset、match、countif。趕緊來(lái)看看吧！學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。

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

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

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

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

第一步：將省這一列復(fù)制出來(lái)，刪除重復(fù)項(xiàng)。

第二步：將省市這兩列復(fù)制出來(lái)，刪除重復(fù)項(xiàng)。

第三步：將市區(qū)這兩列復(fù)制出來(lái)，因?yàn)閿?shù)據(jù)只有三級(jí)，所以市區(qū)是不會(huì)有重復(fù)項(xiàng)的。

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

接下來(lái)進(jìn)入下拉菜單的設(shè)置，同樣非常簡(jiǎn)單。

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

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

二級(jí)菜單設(shè)置，這一步開(kāi)始，就要用到今天的主角了，由OFFSET、MATCH和COUNTIF共同構(gòu)造的一個(gè)公式套路，公式為：

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

千萬(wàn)不要被這個(gè)公式嚇住，其實(shí)這個(gè)公式是很好理解的，以下就為大家破解這個(gè)公式的秘密。

首先我們要明白OFFSET這個(gè)函數(shù)是干什么的。學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。

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

其實(shí)當(dāng)我們?cè)诰庉嫏谶x中公式，按F9鍵以后，看到的是這樣的結(jié)果：

之所以顯示錯(cuò)誤值，是因?yàn)樵谝粋€(gè)單元格里無(wú)法顯示出一個(gè)區(qū)域（四個(gè)單元格）的內(nèi)容。

也就是說(shuō)，公式得到了福建省所對(duì)應(yīng)的市所在的區(qū)域，當(dāng)省（G2單元格）的內(nèi)容變化以后，公式結(jié)果也會(huì)隨之變化，還是通過(guò)F9鍵來(lái)看看變化后的結(jié)果。

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

為什么會(huì)有這樣的效果呢，這就要從OFFSET的五個(gè)參數(shù)來(lái)說(shuō)起了。

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

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

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

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

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

可以說(shuō)在MATCH的協(xié)助下，OFFSET準(zhǔn)確定位到了目標(biāo)區(qū)域的起點(diǎn)，那么目標(biāo)區(qū)域到底是幾個(gè)單元格呢？每個(gè)省所對(duì)應(yīng)的市不一樣多，目標(biāo)區(qū)域也就不一樣大。

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

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

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

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

方法非常簡(jiǎn)單，在序列中將公式復(fù)制進(jìn)去就好了。

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

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

可以說(shuō)，只要掌握了OFFSET-MATCH-COUNTIF這個(gè)公式套路，你就可以隨心所欲的制作多級(jí)智能菜單了。學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。

本文配套的練習(xí)課件請(qǐng)加入QQ群：1043683754下載。

＊＊＊＊部落窩教育-excel多級(jí)下拉菜單＊＊＊＊

原創(chuàng)：老菜鳥(niǎo)/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）

更多教程：部落窩教育（http://www.itblw.com）

微信公眾號(hào)：exceljiaocheng，+v：blwjymx2