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