動態(tài)下拉列表的制作方法

首先完成自定義名稱

單擊C2單元格，按<Ctrl+F3>組合鍵，在彈出的【名稱管理器】對話框中單擊【新建】按鈕，彈出【新建名稱】對話框。在【名稱】編輯框中輸入自定義名稱“省份”，在【引用位置】編輯框中輸入以下公式：

=OFFSET($A$1,1,,COUNTA($A:$A)-1)

依次單擊【確定】和【關(guān)閉】按鈕，完成自定義名稱的設(shè)置。

接下來設(shè)置數(shù)據(jù)有效性

單擊C2單元格，在【數(shù)據(jù)】選項(xiàng)卡中依次單擊【數(shù)據(jù)驗(yàn)證】，在彈出的【數(shù)據(jù)驗(yàn)證】對話框中單擊【設(shè)置】選項(xiàng)卡，【允許】選擇“序列”，在【來源】編輯框中輸入以下公式：

=省份

單擊【確定】按鈕，完成設(shè)置。

此時(shí)單擊C2單元格右側(cè)的下拉箭頭按鈕，會出現(xiàn)效果如下圖所示的下拉列表。

咱們簡單說說自定義名稱公式的意思：

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)對A列數(shù)據(jù)區(qū)域的動態(tài)引用。如果A列數(shù)據(jù)增加或減少，COUNTA函數(shù)的結(jié)果就會發(fā)生變化，結(jié)果傳遞給OFFSET函數(shù)，新引用的行數(shù)會發(fā)生變化了，下拉列表中的內(nèi)容也就自動進(jìn)行調(diào)整。

下圖中，左側(cè)的A列有9個(gè)省份，C2的下拉列表中是9個(gè)條目。

右側(cè)的A列刪除掉了部分?jǐn)?shù)據(jù)，這時(shí)候C列的下拉列表中就自動減少了條目。

注意：使用此技巧要求A列的數(shù)據(jù)必須連續(xù)輸入，數(shù)據(jù)之間不能有空白單元格，否則的話，COUNTA函數(shù)傳遞給OFFSET函數(shù)的就是一個(gè)不準(zhǔn)確的行數(shù)信息，引用范圍就會有偏差了。你也試試吧～