呂孟謙
EXCEL的動態二級下拉菜單的製作方法不少,這裡介紹1種最簡單的方法。
一、動態二級下拉菜單效果
二、製作步驟
1、 參數準備
如下圖將一級菜單和二級菜單準備好。
2、給各級菜單定義名稱
具體步驟:選擇一級菜單A2:A5→【公式】→【根據所選內容創建】→選擇【首行】→選擇二級菜單B2:B5→按F4重複上一操作→選擇二級菜單C2:C5→按F4重複上一操作→選擇二級菜單D2:D5→按F4重複上一操作即可。
通過上述操作,將A3:A5定義為【省份】,將B3:B5定義為【湖南省】,將C3:C5定義為【廣東省】,將D3:D5定義為【福建省】。名稱定義好後,可以在名稱管理器中看到已經定義好的名稱。
3、設置一級下拉菜單
選擇單元格A2:A8→【數據】→【數據驗證】→驗證條件選擇【序列】→來源錄入【=省份】→【確定】。
4、 設置二級下拉菜單
設置方法同一級菜單相似,只是來源不同,需要用到INDIRECT引用函數。選擇B2:B8→【數據】→【數據驗證】→驗證條件選擇【序列】→來源錄入【=INDIRECT($A2)】→【確定】。
這樣,動態二級下拉菜單就做好了!
在A列可以選擇省份,在B列就可以選擇對應省份的城市了!具體操作動圖如下:
我是EXCEL學習微課堂,頭條號優質教育領域創作者,分享EXCEL學習的小技巧,小經驗。目前已分享了80多個免費課程,其中有EXCEL基礎課程、函數課程和數據透視表系列課程,現在正在分享EXCEL圖表系列課程,如果分享的內容對您有用,歡迎點贊、收藏、評論、轉發,更多的EXCEL技能,可以關注今日頭條“EXCEL學習微課堂”。
EXCEL學習微課堂
在數據錄入工作的時候,為了保證數據錄入的準確性,我們可以使用二級聯動的下拉菜單,效果如下所示:
製作這種聯動的下拉菜單,大部分的教程都是我們要將數據源格式調整成下面這個樣子
第一行是省份,從第2行開始,是省份下面的城市,如下所示:
我們首先選中所有的數據,按CTRL+G鍵,定位條件,選擇常量,這樣只會選擇非空的數據源區域:
然後在公式選項卡上方,選擇根據所選內容創建,選擇首行,創建名稱。
然後需要在 輸入數據的位置,選中F列,數據有效性,數據驗證,然後選擇序列,選擇A1:D1單元格。
用同樣的方法,選擇G列,數據驗證,選擇序列,輸入的內容是:
=INDIRECT(F1)
這樣就製作完成了,在F和G列就可以二級的聯動輸入了。
拓展應用:
有一個小夥伴說,它的原始數據源並不是上面那樣的,而是兩列的源數據,那該如何製作二級下拉菜單,
當然可以將這兩列的數據源,轉換成上面的那種格式,再用上面的方法來製作。
今天教大學的方法是直接通過這種兩列式的數據源來製作下拉菜單,如果AB列添加數據,能夠實時的更新二級聯動
❶首先,是提取城市的不重複值,存放在一個空列
=OFFSET(A$2,MATCH(0,COUNTIF(H$1:H1,A$2:A$99),0)-1,,,)&""
這個公式就是提取A列中的不重複值,可以直接套用。
在H列的第2行開始輸入公式,輸入完成後按CTRL+SHIFT+ENTER鍵,然後下拉填充第20行(如果數據較多,可以多拖一點)
❷選擇D列,用數據驗證,選擇序列,輸入的公式是:
=OFFSET($H$2,0,0,COUNTA($H$2:$H$20)-COUNTBLANK($H$2:$H$20),1)
❸選擇E列,然後使用數據有效性,輸入公式:
=OFFSET($B$1,MATCH(D1,$A:$A,0)-1,0,COUNTIF($A:$A,D1),1)
這就製作完成了。
頭條號:Excel自學成才,主頁有更多精彩內容,作者:E博士,畢業於中南大學統計學Excel自學成才
Excel二級下拉菜單的應用非常普遍,比如選擇地址的時候要依次選擇“省份”和“城市”,如下圖所示。
1. 一級下拉菜單的製作
二級下拉菜單是基於一級下拉菜單的,所以先得製作一級下拉菜單。
我們先準備好所需的“省份”和對應“城市”的信息。然後定位到H3單元格位置,點擊【數據】菜單下的【數據驗證】命令。
在彈出的【數據驗證】窗口中,【允許】條件選擇“序列”,來源引用A2:A5區域,如下圖所示。
這樣就製作好了一級下拉菜單,如下圖所示。
2. 二級下拉菜單的製作
我們先選中A2:F5區域,也即包換所有省份和城市的整個區域,然後按Ctl+G快捷鍵,調出【定位】對話框,點擊【定位條件】,選擇【常量】,這樣就只包含有值的單元格,取消了所有空白單元格。
然後,切換到【公式】菜單,選擇【定義名稱】下的【根據所選內容創建】,在彈出的信息窗口勾選【最左列】(因為省份都在城市的最左列),這樣就根據省份信息創建了一系列名稱。
我們不妨打開名稱管理器看看定義的名稱信息(此步驟可省略)。
然後,最關鍵的一步。點擊激活I3單元格,切換到【數據】菜單,點擊【數據驗證】命令,【允許】條件選擇“序列”,【來源】填寫“=INDIRECT(H3)”,不要寫錯了。這樣就製作好了二級下拉菜單。
3. 更多級的下拉菜單
實際應用中可能會要求製作更多級的下拉菜單,其製作思路類似於二級下拉菜單的製作,即先定義名稱,然後用INDIRCT函數引用上級目錄對應的單元格。大家不妨動手試試製作一個“省市縣”三級聯動下拉菜單。
師說新語
案例:
怎麼把下表做成二級聯動菜單?選擇隊名,隊員的列表隨之聯動?
解決方案:
1. Ctrl+F3 新建名稱
2. 在 Name 欄位輸入對應的隊名,Refers to 欄位選擇該隊的人員。依次類推,建立八個隊的列表
3. 按基本步驟製作隊名下拉菜單,具體步驟不贅述,基礎操作,本號受眾應該都會
4. 製作隊員下拉菜單,在 Source 欄位中輸入以下公式,二級聯動菜單就已經完成:
=INDIRECT($J2)
- 這是indirect函數不加 ””的用法,相當於調用隊名的值
- 記得刪除行號 2 前面的絕對引用標識 $,這樣下拉拖動的時候,公式調用的行號會隨行數變化
Excel學習世界
Excel 二級下拉菜單的3種方法
把excelhome的論壇關於下拉菜單的貼看了不少。發現實現2級的下拉菜單的方法好像也有好幾種,我想我就做一個總結,自己也提高一下。
通過2級菜單,倒是可以比較深入熟悉 INDIRECT, INDEX, OFFSET, 這三個函數,是你要想學excel函數,估計你肯定過的關。
在論壇裡看帖子,有一點比較累,很多都是直接用excel文件裡做說明,你需要下載文件。我這裡就全部用圖來說明。
不過二級菜單的第一級的做法,都是相同的,大家可以參考上面的文章創建一個一級菜單。
第一種做法:使用函數INDIRECT
看看例子
很明顯,我們希望在一級選擇機房故障,那麼2級就出現他下面的列表。
要實現這個目標並不複雜。
1:建立“名稱
3個一級選項,我們需要建立3個“名稱”:機房故障 線路故障 用戶端故障。
這個時候,你就創建了3個名稱
還需要建立一個名稱,叫做“故障類型”這個名稱包括“機房故障 線路故障 用戶端故障”這3項。
這個時候,你就可以在“名稱管理器”看到4個名稱
2:創建1級菜單
1級菜單的辦法就簡單了
這樣就解決了一級的下拉菜單。
3:2級菜單
這個地方你需要注意,=INDIRECT($F2)
如果你寫成 =INDIRECT($F$2),
如果是這樣的話,你確定會出現一個報錯
這個時候,你就實現了2級菜單
第二種做法:使用函數INDEX
還是上面的例子,用另外一種方式來實現。這個例子裡,你只需要建立2個名稱。
1:建立名稱
建立:故障類型 名稱,方法和上面一樣。
建立:2級 名稱
創建上面兩個名稱。
2:創建下拉菜單
第一下拉菜單和上面一樣,我就不重複了,現在是演示第二級下拉菜單
=INDEX(二級,0,match(I$2,故障類型,0))
這樣你就搞定2級菜單裡。
第三種做法:使用函數OFFSET
學這個offset函數半天,才搞定。現在只是可以實現這個功能。不過還有需要改進的地方
還是上面的例子,
=OFFSET(A2,,MATCH(L2,故障類型,)-1,6,1)
這樣也是可以實現2級的下拉菜單。
以上的方式其實是不太完善的,因為我們的2級菜單剛好都是6項,那麼如果是不等的,那你就只能取最多的。
完善的做法,當然是動態的,根據2級菜單的數量來顯示。需要解決的問題,就是如果動態計算出每項2級菜單的數量。
2級菜單數量=COUNTA(OFFSET(A:A,,MATCH(L2,故障類型,0)-1))-1
那麼我們的公式就變成
=OFFSET(A2,,MATCH(L2,故障類型,)-1,COUNTA(OFFSET(A:A,,MATCH(L2,故障類型,0)-1))-1,1)
這樣就更加完美。
<strong>