網(wǎng)友解答: 韓老師講一種二級(jí)聯(lián)動(dòng)菜單的做法，這樣做出來的菜單，不管是一級(jí)還是二級(jí)，選項(xiàng)都是可以隨意添加、刪除、修改的。效果如下：實(shí)現(xiàn)方法第一步：定義名稱1、定義一級(jí)菜單名稱：在【公式】菜

韓老師講一種二級(jí)聯(lián)動(dòng)菜單的做法，這樣做出來的菜單，不管是一級(jí)還是二級(jí)，選項(xiàng)都是可以隨意添加、刪除、修改的。

效果如下：

實(shí)現(xiàn)方法

第一步：定義名稱

1、定義一級(jí)菜單名稱：

在【公式】菜單下，選擇【定義名稱】，在【新建名稱】對(duì)話框內(nèi)輸入：

名稱：一級(jí)

引用位置：=OFFSET(源數(shù)據(jù)!$A$1,,,1,COUNTA(源數(shù)據(jù)!$1:$1))

如下圖：

2、定義二級(jí)菜單名稱：

在【公式】菜單下，選擇【定義名稱】，在【新建名稱】對(duì)話框內(nèi)輸入：

名稱：二級(jí)

引用位置：=OFFSET(源數(shù)據(jù)!$A$2,,MATCH(聯(lián)動(dòng)菜單!$A2,一級(jí),0)-1,COUNTA(OFFSET(源數(shù)據(jù)!$A$2,,MATCH(聯(lián)動(dòng)菜單!$A2,一級(jí),0)-1,200)))

如下圖：

此處插播一則廣告：

廣告過后，繼續(xù)學(xué)習(xí)……

第二步：

數(shù)據(jù)驗(yàn)證設(shè)置一級(jí)二級(jí)菜單

1、設(shè)置一級(jí)菜單：

鼠標(biāo)放在要做一級(jí)菜單的單元格，在【數(shù)據(jù)】菜單中選【數(shù)據(jù)驗(yàn)證】，在跳出的【數(shù)據(jù)驗(yàn)證】對(duì)話框中的【設(shè)置】選擇卡中，選擇驗(yàn)證條件為允許【序列】，來源為“=一級(jí)”，確定，如下圖：

2、設(shè)置二級(jí)菜單：

鼠標(biāo)放在要做二級(jí)菜單的單元格，在【數(shù)據(jù)】菜單中選【數(shù)據(jù)驗(yàn)證】，在跳出的【數(shù)據(jù)驗(yàn)證】對(duì)話框中的【設(shè)置】選擇卡中，選擇驗(yàn)證條件為允許【序列】，來源為“=二級(jí)”，確定，如下圖：

通過以上兩步的設(shè)置，不管使添加選項(xiàng)還是刪除選項(xiàng)，一級(jí)與二級(jí)菜單都跟著改變。

公式解析

1、一級(jí)菜單公式：

=OFFSET(源數(shù)據(jù)!$A$1,,,1,COUNTA(源數(shù)據(jù)!$1:$1))

該公式的含義是：以數(shù)據(jù)源表中的A2單元格為基準(zhǔn)，偏移到1行、COUNTA(源數(shù)據(jù)!$1:$1)列的區(qū)域。其中，COUNTA(源數(shù)據(jù)!$1:$1)的返回值隨著第一行數(shù)據(jù)的增多二增多，可以實(shí)現(xiàn)一級(jí)菜單的動(dòng)態(tài)數(shù)量變化。

2、二級(jí)菜單公式：

=OFFSET(源數(shù)據(jù)!$A$2,,MATCH(聯(lián)動(dòng)菜單!$A2,一級(jí),0)-1,COUNTA(OFFSET(源數(shù)據(jù)!$A$2,,MATCH(聯(lián)動(dòng)菜單!$A2,一級(jí),0)-1,200)))

該公式的含義是：以數(shù)據(jù)源表中的A2單元格為基準(zhǔn)，偏移到0行、MATCH(聯(lián)動(dòng)菜單!$A2,一級(jí),0)-1列以后的單元格，然后取該單元格所在列的行數(shù)，列數(shù)為200的區(qū)域，此處200為比較的數(shù)，可以根據(jù)實(shí)際情況變化。

如以下表格是一個(gè)報(bào)名表，工作表名稱為sheet1，要求給列名參賽項(xiàng)目增加下拉菜單選項(xiàng)，以方便用戶快速輸入相關(guān)內(nèi)容又可以避免用戶輸入不合格數(shù)據(jù)。

步驟1、先新建一個(gè)輔助的sheet2工作表，專門放比賽項(xiàng)目；

步驟2、將sheet2工作表的內(nèi)容由區(qū)域轉(zhuǎn)換為表，即選中sheet2的數(shù)據(jù)單元格區(qū)域，點(diǎn)擊菜單的【插入】－【工作表】；

步驟3、點(diǎn)擊菜單的【公式】-【名稱管理器】，新建一個(gè)名稱，任意命名，引用位置為sheet2表的A2:A10單元格區(qū)域，點(diǎn)擊【確定】；

接下來給sheet1工作表的比賽項(xiàng)目設(shè)置數(shù)據(jù)有效性。

步驟4、選中sheet1工作表的比賽項(xiàng)目列，點(diǎn)擊菜單【數(shù)據(jù)】-【數(shù)據(jù)有效性】-【數(shù)據(jù)有效性】；

步驟5、在跳出的【數(shù)據(jù)有效性】對(duì)話框中，選擇【設(shè)置】，有效條件選擇【序列】，在激活來源時(shí)，按快捷鍵F3鍵調(diào)用出粘貼名稱菜單，選擇上一步我們新建的名稱即【比賽項(xiàng)目】；

步驟6、選擇好后，點(diǎn)擊【確定】；

這樣就可以看到，sheet1工作表的比賽項(xiàng)目自動(dòng)更新下拉菜單制作完成。

此時(shí)，如果在sheet2工作表添加一個(gè)比賽項(xiàng)目，比如拔河，可以看到在sheet1的比賽項(xiàng)目列的下拉菜單就會(huì)自動(dòng)添加更新上去。

同樣，如果我們?nèi)绻趕heet2工作表刪除一個(gè)比賽項(xiàng)目，比如乒乓球，可以看到在sheet1的比賽項(xiàng)目列的下拉菜單也會(huì)自動(dòng)刪除這個(gè)項(xiàng)目。