Excel 下拉菜單的製作雖說大多數人都會,但是一旦對下拉菜單有進一步的需求,難度係數提高得就不止一星半點。
有關下拉菜單的案例,我寫過不少有意思的內容,具體請參見:
- Excel ActiveX 控制項到底有啥用?如此美貌的下拉菜單,非它不可
- 製作一個可以用下拉菜單控制的 Excel 動態圖表
- Excel中可以按關鍵字搜索的下拉菜單,兩種做法任你選
- Excel–這才是三級聯動下拉菜單的正確做法,不同於二級聯動菜單
- Excel – 如何始終顯示下拉菜單右邊的小箭頭?
- Excel下拉菜單選一個少一個
- 去除Excel下拉菜單中的空值和重複值
今天再教一個新技巧:當數據源中新增菜單項時,如何讓下拉菜單的選項隨之自動增加?
案例 :
在下圖 1 中的 A 列製作下拉菜單,菜單項內容在 E 列。要求:當 E 列新增內容時,A 列的下拉菜單選項會隨之自動增加新選項。效果如下圖 2 所示。
解決方案:
先看一下用普通方式製作下拉菜單,是否能實現案例需求。
1. 選中 A2:A14 區域 --> 選擇菜單欄的「數據」-->「數據驗證」-->「數據驗證」
2. 在彈出的菜單中選擇「設置」選項卡,按入如下方式設置 --> 點擊「確定」:
- 允許:序列
- 來源:=$E$1:$E$3
現在下拉菜單已經設置好。
在 E4 單元格新增「四班」,但是下拉菜單選項並沒有增加這個新選項。如此看來,我們得另闢蹊徑。
下面就來學習一下正確的設置方法。
1. 選中 A2:A14 區域 --> 選擇菜單欄的「數據」-->「數據驗證」-->「數據驗證」
2. 在彈出的菜單中選擇「設置」選項卡,按入如下方式設置 --> 點擊「確定」:
- 允許:序列
- 來源:=OFFSET($E$1,,,COUNTA($E:$E),)
公式主要用到了 offset 函數,有關該函數的案例,可參見:
- Excel 二維表查詢,不得不學會經典組合公式 offset+match
- Excel – 當offset遇上F9,圖表動起來了
- Excel – 用offset函數將一列數據首尾倒置
公式釋義:
- OFFSET 函數語法:OFFSET(要偏移的起始坐標單元格, 要偏移多少行, 要偏移多少列, [需要返回的引用的行高], [需要返回的引用的列寬])
- OFFSET($E$1,,,COUNTA($E:$E),):從 E1 單元格開始,引用的行高為 E 列的非空個數;也就是將普通下拉菜單公式中的截止單元格變成了動態的,根據 E 列的內容自動變化。
現在在 E 列增加新單元格,下拉菜單會自動新增選項;反之亦然,刪除 E 列中的選項,下拉菜單也會自動減少。
很多同學會覺得 Excel 單個案例講解有些碎片化,初學者未必能完全理解和掌握。不少同學都希望有一套完整的圖文教學,從最基礎的概念開始,一步步由簡入繁、從入門到精通,系統化地講解 Excel 的各個知識點。
現在終於有了,以下專欄,從最基礎的操作和概念講起,用生動、有趣的案例帶大家逐一掌握 Excel 的操作技巧、快捷鍵大全、函數公式、數據透視表、圖表、列印技巧等……學完全本,你也能成為 Excel 高手。