很多時候在使用Excel表格時，咱們要用到下拉列表式的輸入，不僅十分方便，而且不容易出錯。
通過設(shè)置數(shù)據(jù)驗證，可以直接引用某個區(qū)域作為序列來源，完成下拉列表的效果，但是這樣生成的下拉列表中的條目不能隨數(shù)據(jù)源數(shù)量的增加或是減少而自動變化。
利用OFFSET函數(shù)能夠?qū)崿F(xiàn)動態(tài)引用的特點，能夠?qū)崿F(xiàn)下拉列表的自動擴展。
今天咱們以Excel2013版本為例，和大家一起學(xué)習(xí)動態(tài)下拉列表的制作。
題目要求：A列是省份名稱，要求在C2單元格使用數(shù)據(jù)驗證創(chuàng)建動態(tài)下拉列表。
首先完成自定義名稱
單擊C2單元格，按組合鍵，在彈出的【名稱管理器】對話框中單擊【新建】按鈕，彈出【新建名稱】對話框。在【名稱】編輯框中輸入自定義名稱“省份”，在【引用位置】編輯框中輸入以下公式：
=OFFSET($A$1,1,,COUNTA($A:$A)-1)
依次單擊【確定】和【關(guān)閉】按鈕，完成自定義名稱的設(shè)置。
接下來設(shè)置數(shù)據(jù)有效性
單擊C2單元格，在【數(shù)據(jù)】選項卡中依次單擊【數(shù)據(jù)驗證】，在彈出的【數(shù)據(jù)驗證】對話框中單擊【設(shè)置】選項卡，【允許】選擇“序列”，在【來源】編輯框中輸入以下公式：
=省份
單擊【確定】按鈕，完成設(shè)置。
此時單擊C2單元格右側(cè)的下拉箭頭按鈕，會出現(xiàn)效果如下圖所示的下拉列表。
咱們簡單說說自定義名稱公式的意思：
COUNTA($A:$A)用于計算A列不為空的單元格個數(shù)。
OFFSET函數(shù)以$A$1單元格為基點，向下偏移1行，向右偏移0列，新引用的行數(shù)為A列不為空的單元格個數(shù)減1(去掉列標(biāo)題的計數(shù))。
公式根據(jù)A列的實際數(shù)據(jù)個數(shù)，確定OFFSET函數(shù)引用的行數(shù)，實現(xiàn)對A列數(shù)據(jù)區(qū)域的動態(tài)引用。如果A列數(shù)據(jù)增加或減少，COUNTA函數(shù)的結(jié)果就會發(fā)生變化，結(jié)果傳遞給OFFSET函數(shù)，新引用的行數(shù)會發(fā)生變化了，下拉列表中的內(nèi)容也就自動進行調(diào)整。
下圖中，左側(cè)的A列有9個省份，C2的下拉列表中是9個條目。
右側(cè)的A列刪除掉了部分?jǐn)?shù)據(jù)，這時候C列的下拉列表中就自動減少了條目。
注意：使用此技巧要求A列的數(shù)據(jù)必須連續(xù)輸入，數(shù)據(jù)之間不能有空白單元格，否則的話，COUNTA函數(shù)傳遞給OFFSET函數(shù)的就是一個不準(zhǔn)確的行數(shù)信息，引用范圍就會有偏差了。你也試試吧～