大美人小智慧

其實只要學會了二級下拉菜單，三級下拉菜單很容易學會！

1、我們先來看二級下拉菜單如何設置？

如圖所示，我們需要在D列輸入【產品】，然後在E列輸入每個產品對應的【型號】，他們都是通過下拉菜單進行選擇的，【產品】列就是一級菜單，【型號】是二級菜單。

Step1：創建一級菜單與二級菜單的對應關係

要創建一二級菜單，首先要有一二級菜單所用的列表，如圖所示，分別創建一級菜單列表（產品列）和二級菜單列表（產品、型號列）

Step2：創建一級下拉菜單

將鼠標定位於D列D2單元格，切換到【數據】選項卡，點擊【數據驗證】，彈出【數據驗證】對話框，在【數據驗證】對話框中，在【允許】框裡選擇【序列】，在【來源】框中寫入【=產品列表】

（這是因為我已經事前將產品清單區域定義為“產品列表”名稱）

Step3：創建二級下拉菜單

將鼠標定位於E列E2單元格，切換到【數據】選項卡，點擊【數據驗證】，彈出【數據驗證】對話框，在【數據驗證】對話框中，在【允許】框裡選擇【序列】，在【來源】框中寫入【=型號列表】

注意：型號列表到底是什麼，這是我定義的一個名稱，我們來看一下這個名稱代表什麼，以及如何定義名稱。

①在【公式】選項卡，點擊【名稱管理器】

②彈出【名稱管理器】對話框，在這裡點擊【新建】可以新建名稱

我們選中定義好的【型號列表】名稱，然後點擊【編輯】

可以看到，我們的定義的“型號列表”這個名稱，它的值為一個公式

=OFFSET(產品清單!$K$1,MATCH(進銷記錄!$D2,產品清單!$K:$K,0)-1,1,COUNTIF(產品清單!$K:$K,進銷記錄!$D2),1)

這個公式的作用就是：

當一級下拉菜單中選擇一個產品名後，通過OFFSET函數進行偏移，偏移出來一個新的區域，這個區域剛好是一級菜單對應的二級菜單列表。

比如，如果你的一級菜單中選擇的是“鞋子”，那麼這一長串公式就會返回一個區域，如圖紅色框線內的區域，這個區域剛好是“鞋子”對應的“型號”菜單。

2、三級下拉菜單如何設置？

如果你理解了二級下拉菜單，那麼你就應該明白，三級下拉菜單是在二級菜單的基礎上再做一個“二級菜單”。

如圖所示，在型號之後還有一個三級菜單為規則，那麼我們只需要再建立型號與規格的對應關係，然後依照二級菜單的構建方法，再次構建一個二級菜單，這樣就形成了：產品為一級菜單、型號是產品的二級菜單、規格是型號的二級菜單，這樣的一個三級菜單。

「精進Excel」系頭條簽約作者，關注我，如果任意點開三篇文章，沒有你想要的知識，算我耍流氓！

精進Excel

平常我們經常會見到下拉的選項，比如在填寫地址時選擇省份，接下來的菜單就自然選擇對應省份的城市，再選擇又到了對應的下級地方，這種聯動的二級或多級下拉菜單怎麼做到的呢？我們今天一起來製作下，先看動態效果圖，選擇姓名會自動下拉選擇其負責的樓盤：

具體操作方法：

1. 先做第一個序列，也就是姓名

2.接著做第二序列，姓名所負責的樓盤；有個小技巧就是光標所在的單元格會被默認為定義區域的名字，所以，我們可以先選中再定義，CTRL+F3彈出定義窗口，依次選擇區域按名字命名。之後用INDIRECT做序列

4.在工號單元格輸入公式=IF(C5>0,VLOOKUP(C5,明細表!$F$6:$H$15,3,FALSE)，向下填充好；兩個公式的區別就是VLOOKUP的位置不一樣，一個是3一個是2。

5.這樣在選擇員工姓名時就可以聯動對應的樓盤了；同樣的操作方法，可以製作多級的聯動。

雅竹齋

在回答此問題之前，小編先分享製作下拉菜單的三種方法。文章的後面，再分享製作二級和三級下拉菜單的方法：

製作下拉菜單的三種方法

方法一：用數據驗證完成

首先，選定需要設置下拉菜單的數據區域，這裡是"E"列單元格，然後，單擊【數據】-【數據驗證】，打開【數據驗證】對話框，在"允許"列表框中，選擇"序列"，在"來源"框中輸入"$D$2:$D$5"數據源區域，單擊【確定】。如下動圖演示：

溫馨提示：Excel2007之後的版本稱作"數據驗證"，Excel 2003及之前版本稱作"數據有效性"，二者功能一樣。

方法二：用組合鍵完成

把光標定位在"D6"單元格中，然後按"Alt+↓"就會出現下拉菜單。

方法三：用組合框完成

單擊【開發工具】-【插入】-【組合框】，然後拖動鼠標畫出一組合框，再右擊鼠標，選擇"設置控件格式"，打開"設置控件格式"對話框，在"數據源區域"框中輸入"$D$2:$D$5"，最後，單擊【確定】。

製作二級下拉菜單的方法（只需4步）

第1步：選定區域創建名稱

首先，選定數據區域"B3:F8"（如下圖示），單擊【公式】，在"定義的名稱"組中選擇【根據所選內容創建】，選擇"首行"，最後單擊【確定】。

第2步：創建"省份"下拉列表

首先，選定單元格"H4"，單擊【數據】，在"數據工具"組中選擇【數據驗證】，打開"數據驗證"對話框，然後再"允許"下拉列表中選擇"序列"，在"來源"文本框中輸入"=$B$3:$F$3"，最後單擊【確定】。

重點：在"來源"文本框中輸入公式"=$B$3:$F$3"，請注意，這裡要用絕對引用。

第3步：創建"城市"下拉列表

首先，選定單元格"H4"，單擊【數據】，在"數據工具"組中選擇【數據驗證】，打開"數據驗證"對話框，然後再"允許"下拉列表中選擇"序列"，在"來源"文本框中輸入"=INDIRECT($H4)"，最後單擊【確定】。

重點：①在"來源"文本框中輸入函數"=INDIRECT($H4)"，請注意，這裡要用混合引用；

②函數"INDIRECT"：表示引用單元格並顯示其內容。

第4步：拖動鼠標向下填充

溫馨提示：製作三級下拉菜單的方法與二級等同，這裡不再熬述。

鳴謝：若喜歡本篇文章，記得點贊，評論和轉發噢！

ExcelVBAHome

=============================================================

====================

|| 版本號：Excel2013. ||

====================

本篇來講一個高級的用法，也是工作中常用到的。就是製作一個一級和二級的下拉菜單，而且二級菜單要隨著

一級菜單的改變而改變。這個炫酷的功能可以用OFFSET結合MATCH函數做出來，如下：

需求是這樣的：我們想把一級部和二級部做成一個下拉菜單的形式，而且隨著我動態的增加一級部和二級部的名稱，下拉列表

也要動態的隨著改變。如下，我有增加了一個一級部和相應的二級部，那麼下拉列表依然動態隨著改變：

哈哈，是不是很炫酷，你是不是已經迫不及待的想要學習怎麼做出這樣的表格了呢？下面是教程。

==========================================================

其實主要的原理就是給單元格設置數據有效性，而數據有效性設置中就嵌套著我們用函數引用來的名稱。

具體操作如下：

（1）我們首先來將一級部的所有名稱引用到，這裡我用的公式如下

OFFSET(Sheet7!I2,0,0,COUNTA(Sheet7!I:I)-1,1)

解釋一下：OFFSET函數中使用的基點為“I2”單元格（因為我的表名字為Sheet7所以這裡自動採用了絕對引用），

然後不發生偏移，即行列方向上偏移都為0。那麼我想要的就是從 I2 開始一直到該列結束的所有非空

單元格，因此我們首先用COUNTA計算 I 列的所有非空單元格數目，再減去1（即減去了一級部那個

單元格）。那麼從 I2 開始（包括 I2 ），行高度我們就要這麼多行就可以了，然後列高度呢當然是一列

啊。因此OFFSET就構成了上面的公式，我們也就成功的把 I 列中所有一級部的名稱給引用到了。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（2）下面要做的就是我們將引用到的一級部區域形成名稱，操作如下：

首先選中我們的單元格：

接著點擊“公式”選項卡中的“名稱管理器”如下：

然後在名稱管理器中，點擊新建，如下：

最後我們輸入下面的信息：

在名稱中我們任意命名即可，在範圍選擇你的表格的有效範圍（因為我的所有數據都在Sheet7中，因此

選擇Sheet7），然後在引用位置上輸入（1）中的公式。最後點擊確定即可。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（3）已經形成了名稱，下面只要對目標單元格設置數據有效性即可。依然是選中目標單元格（與

第（2）中的目標單元格一樣），如下：

然後選擇“數據”選項可中的“數據驗證”，如下：

在“數據驗證”中，如下輸入：

主要就是來源選擇我們剛剛自定義的名稱即可。點擊確定。

（4）這樣一級部的下拉菜單已經做完，如下：

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（5）下面做二級部的下拉菜單。原理步驟與一級部的下拉菜單完全相同。只是函數公式

比較複雜，如下：

OFFSET(I1,MATCH(E3,I:I,0)-1,1,1,COUNTA(OFFSET(J1:L1,MATCH(E3,I:I,0)-1,0)))

解釋一下：a.我們首先要根據E3中一級部的內容搜素到它在 I 列中的位置，這個要藉助MATCH函數。

b.然後在OFFSET函數中，基點我們選擇 I1 單元格，只需要讓 I1 往下移到a中的位置

列上往右偏移一列即可。比如一級部先向下偏移到產品開發部，又往右移到開發管理

這個單元格上。

c. 接著我們要在b產生的新基準點上計算相應二級部有多少非空單元格。也就是行高

為1，而列高就是二級部的個數，我們用COUNTA來計算。思路就是我們發現二級部

的列區域不會變（是從J1：L1)，只要我們找到相應二級部對應的行區域即可。這個就

再次藉助OFFSET函數來產生引用。在公式中的藍色標註部分，J1：L1這個基準向下偏移

的量其實跟 a中 I1 向下偏移的量一樣，然後列不發生偏移，缺省行高和列高，那麼就會得到

引用的行高和列高與J1：L1一樣，而偏移到了相應二級部上。這樣我們再用COUNTA計算

這個引用的非空單元格數目就是相應二級部的個數，也就是最外層的OFFSET應該有的

列高了。

好了這個公式解釋完畢，如果還不懂，多看幾次即可。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（6）這樣根據一級部的引用我們獲得了二級部的引用，下面形成名稱，

首先選中單元格：

然後自定義名稱，如下：

點擊確定。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（7）然後給目標單元格設定數據有效性即可。如下：

選中單元格：

設定有效性：

點擊確定即可。

看看效果吧，如下：

=============================================================

心中有棵白楊樹

如何在EXCEL中建立一級 / 二級 / 三級聯動下拉菜單。

1，如何創建一級下拉菜單。

步驟：選擇需要創建下拉菜單的區域--- 數據 --- 數據驗證--- 設置--- 允許（序列）--- 來源--- 選擇下拉菜單的內容---確定。

2，如何創建二級下拉菜單。

步驟：選擇一級二級內容 ---公式---根據所選內容創建---只在首行打鉤---確定---選擇創建二級菜單的區域---數據 ---數據驗證--- 設置---允許（序列）---來源（=INDIRECT(A7)）---確定。

3，如何創建三級下拉菜單。

步驟：選擇二級三級內容 ---公式---根據所選內容創建---只在首行打鉤---確定---選擇創建三級菜單的區域---數據 ---數據驗證--- 設置---允許（序列）---來源（=INDIRECT(B7)）---確定。

Office高效率辦公

例如：某公司有多個部門，部門下又有幾個科室，每個科室下又有若干分部。那怎麼實現呢？

第一步：編輯好一級，二級，三級的數據（如圖2）

圖2

第二步：對一級和二級，二級和三級分別定義一下名稱（圖2中我稱之為表1，表2）

操作方法：例如表1數據，選中B10：D12區域，點擊公式---根據所選內容創建---只在首行打鉤---點擊確定，如圖3

圖3

表2中數據同理，選中B15：G18區域，點擊公式---根據所選內容創建---只在首行打鉤---點擊確定。完成後，可以在名稱管理器中可以看見剛才定義過的數據了（圖4）。

圖4

彆著急，馬上最後一步。

第三步：設置二級下拉菜單，利用INDIRECT函數引出定義過的名稱區域，三級同理

一級下拉菜單前面已經介紹過，很簡單，選中“部門“下面的一塊區域，點擊數據-----數據驗證（13版）--序列---引用的數據---點擊確定，這樣部門下就有”財務部，人事部，採購部“了（圖5）

圖5

二級下拉菜單還是先在“科室”下選中一塊區域，數據—數據驗證---序列----注意:這裡要寫一個=INDIRECT(B2)然後點確定。（圖6）

圖6

說明：因為前面已經對一級和二級定義過名稱了，所以寫=INDIRECT(B2)，就默認為引用“財務部”下面的兩個數據呢，B2相對引用就可以了。

三級甚至無限級也是同理呢，引用前面的科室下的數據

學會使用這個小技巧，可以大大提高我們的工作效率呢。

更多財稅職場學習資訊，關注秀財網

秀財網

我來說說我的做法吧

如圖我們根據左邊的數據源在右邊的表格中設置下拉菜單

第一步

在類別中先設置下拉菜單，這個很簡單根據數據有效性設置，如下圖：

第二步

第二步在商品名中設置二級下拉菜單，這步會比較複雜點。二級下拉菜單我是分兩步完成的。如下：

先進行自定義名稱設置

在名稱管理器中新建自定義名稱，用公式“=OFFSET(sheet1!$B$16,,MATCH(sheet1!$F$16,sheet1!$B$15:$C$15,1)-1,COUNTA(sheet1!$B$16:$C$20))”設置二級菜單區域。

再在數據有效性中設置下拉菜單。

我在自定義名稱中用OFFSET函數引用數據區域，這樣我們做出來的二級下拉菜單是動態的下拉菜單，當二級下拉菜單中的數據增減時，下拉菜單中的內容也會相應的自動進行增減。

如果你覺得有用可以點贊、關注支持下哦，謝謝！

向學霸進擊的胡圖圖

本節課以非對稱性聯動菜單製作為例，詳盡講解Excel聯動下拉菜單1級2級3級至多級的製作過程。（圖文精講）

例如：對省、市、區劃分為1級2級3級，製作3級聯動下拉菜單，最終制作效果如下。

一級菜單製作步驟：

1、選擇F1:G1區域，依次點擊“公式”，“定義名稱”，在“新建名稱”窗口內輸入“省份”，如下圖：

2、選擇常量區域：首先選擇F1：G4區域，依次點擊“查找和選擇”，“常量”。常量區域被選中。

3、常量區域被選中後，點擊“根據所選內容創建”，彈出窗口中勾選“首行”

4、選中A3：A14區域，依次點擊“數據”選項卡，“數字驗證”，彈出“數據驗證”窗口，按下圖標記位置填寫“序列”和“=省份”內容。

5、一級菜單製作完成，效果如下。

二級菜單製作步驟：

選擇B3：B14區域,依次點擊數據,數據驗證，在“”數據驗證“窗口對應位置輸入“序列”，“=INDIRECT($A3)”,如下圖

點擊確定後，二級下拉菜單效果如下

三級下拉菜單製作步驟：

1、選擇常量區域：首先選擇F8：I14區域，依次點擊“查找和選擇”，“常量”。常量區域被選中。

2、依次點擊“公式”，“根據所選內容創建”，勾選“首行”

3、選擇C3：C14區域,依次點擊“數據”，“數據驗證”，在“”數據驗證”窗口對應位置輸入“序列”，“=INDIRECT($B3)”,如下圖

4、三級下拉菜單效果如下

四級和多級菜單製作，重複二級菜單製作步驟即可。

更多免費教程及表格，私聊裡回覆相應的關鍵字獲取！

行業財務報表：回覆“財務報表”

項目進度表：回覆“項目進度表”

考勤表：回覆“考勤表”

HR管理系統：回覆“HR管理”

倉管表：回覆“倉管表”

回覆“分享”，獲取精美圖表模板

Office Excel2016最新版：回覆“2016”

陶澤昱

2、還需要用到EXCLE中很特別的一個函數 INDIRECT，簡單說，這個函數的作用就是講一個字符串轉換成一個變量。

這樣定義完後，默認的名稱是“表1”、“表2”類似的名稱，還需要修改名稱為對應的菜單名稱。打開名稱管理器，選擇對應的表格，編輯即可修改名稱。一定要修改哦。

康愉子

我用的WPS，下拉菜單、二級菜單、三級菜單就分別用gif演示了；

1、下拉菜單

步驟：數據—插入下拉列表—完成

2、二級菜單，步驟如下

（1）選中區域作為【指定】源數據；

（2）公式—打開名稱管理器—新建一個規則（主要為了一級下拉菜單用）

（3）數據—有效性—序列—indirect（$A2）

（4）這樣就搞定了！

知識點：1.有效性-序列的應用；2.indirect函數的使用；3.【指定】源數據的規則

3、三級菜單，我就不做了，留給你自己當做作業，原理也是一樣的！

我是零度探索，一個喜歡玩王者榮耀和玩弄office的少年，喜歡的關注一下唄！

關鍵字: Excel 職場 生活