問題情境
韓老師原來講過一篇:Excel036 | 多級聯動菜單,規範數據輸入,在這一篇中,所有的“省市地區”等菜單選項都是固定的,不能隨意添加。
今天,韓老師講一種二級聯動菜單的做法,這樣做出來的菜單,不管是一級還是二級,選項都是可以隨意添加、刪除、修改的。
效果如下:
實現方法
第一步:定義名稱
1、定義一級菜單名稱:
在【公式】菜單下,選擇【定義名稱】,在【新建名稱】對話框內輸入:
名稱:一級
引用位置:=OFFSET(源數據!$A$1,,,1,COUNTA(源數據!$1:$1))
如下圖:
2、定義二級菜單名稱:
在【公式】菜單下,選擇【定義名稱】,在【新建名稱】對話框內輸入:
名稱:二級
引用位置:=OFFSET(源數據!$A$2,,MATCH(聯動菜單!$A2,一級,0)-1,COUNTA(OFFSET(源數據!$A$2,,MATCH(聯動菜單!$A2,一級,0)-1,200)))
如下圖:
第二步:數據驗證設置一級二級菜單
1、設置一級菜單:
鼠標放在要做一級菜單的單元格,在【數據】菜單中選【數據驗證】,在跳出的【數據驗證】對話框中的【設置】選擇卡中,選擇驗證條件為允許【序列】,來源為“=一級”,確定,如下圖:
2、設置二級菜單:
鼠標放在要做二級菜單的單元格,在【數據】菜單中選【數據驗證】,在跳出的【數據驗證】對話框中的【設置】選擇卡中,選擇驗證條件為允許【序列】,來源為“=二級”,確定,如下圖:
通過以上兩步的設置,不管使添加選項還是刪除選項,一級與二級菜單都跟著改變。
公式解析
1、一級菜單公式:
=OFFSET(源數據!$A$1,,,1,COUNTA(源數據!$1:$1))
該公式的含義是:以數據源表中的A2單元格為基準,偏移到1行、COUNTA(源數據!$1:$1)列的區域。其中,COUNTA(源數據!$1:$1)的返回值隨著第一行數據的增多二增多,可以實現一級菜單的動態數量變化。
2、二級菜單公式:
=OFFSET(源數據!$A$2,,MATCH(聯動菜單!$A2,一級,0)-1,COUNTA(OFFSET(源數據!$A$2,,MATCH(聯動菜單!$A2,一級,0)-1,200)))
該公式的含義是:以數據源表中的A2單元格為基準,偏移到0行、MATCH(聯動菜單!$A2,一級,0)-1列以後的單元格,然後取該單元格所在列的行數,列數為200的區域,此處200為比較的數,可以根據實際情況變化。
閱讀更多 韓老師講office 的文章