準(zhǔn)備工作表

首先準(zhǔn)備兩個工作表，“Data”工作表用于設(shè)置數(shù)據(jù)有效性來輸入數(shù)據(jù)，“Info”工作表用于存放下拉列表條目。兩個工作表分別如下圖所示：

注意，上表中單元格區(qū)域B1:D1中的標(biāo)題數(shù)據(jù)均為列A中的省份名稱。如果需要在列A中添加省份，則應(yīng)在列D后添加該省份名稱作為第1行的標(biāo)題。

創(chuàng)建動態(tài)命名區(qū)域

Province區(qū)域

單擊“公式”選項卡“定義名稱”按鈕，在“新建名稱”對話框中設(shè)置：

• 名稱：Province

• 引用位置：=Info!$A$2:INDEX(Info!$A:$A,COUNTA(Info!$A:$A))

這樣，就定義了一個名為“Province”的動態(tài)列表：起始于“Info”工作表中的單元格A2，終止于列A中最后一個數(shù)據(jù)單元格。上圖所示的工作表中即為單元格區(qū)域A2:A4。

Datas區(qū)域

工作表“Data”中的數(shù)據(jù)有效性下拉列表將使用工作表“Info”中的數(shù)據(jù)。為了避免隨著列數(shù)的增多，要創(chuàng)建更多的名稱，我們創(chuàng)建一個動態(tài)的區(qū)域。

在“新建名稱”對話框中設(shè)置如下：

• 名稱：Datas

• 引用位置：=Info!$A$2:INDEX(Info!$1:$100,100,COUNTA(Info!$1:$1))

這將創(chuàng)建一個名為“Datas”的單元格區(qū)域：在工作表“Info”中，起始于單元格A2，擴展到100行，列數(shù)為第1行中有數(shù)據(jù)的列數(shù)。

注：100行是一個預(yù)估的數(shù)字，估計下拉列表條目不會超過100個，你可以根據(jù)實際修改。

RowNum

由于作為下拉列表條目的“Info”工作表中各列具有不同的行數(shù)，因此需要創(chuàng)建一個名為RowNum的名字變量，用來表示動態(tài)的列數(shù)。RowNum的值隨引用的列不同而不同，例如，在上圖所示的工作表中，如果引用“Info”工作表中的列B，則為5；如果是列C，則為6。

首先，在“Data”工作表中，在單元格A2中輸入“湖北省”(這是一個臨時值，之后可刪除)。然后，選擇“Data”工作表的單元格B2，調(diào)出“新建名稱”對話框，并設(shè)置如下：

• 名稱：RowNum

• 引用位置：=COUNTA(INDEX(Datas,,MATCH(Data!A2,Info!$1:$1,0)))

注意：由于要相對引用單元格左側(cè)的值，因此在定義名稱時，一定要選擇“Data”工作表的單元格B2。

UsingList區(qū)域

最后創(chuàng)建名稱“UsingList”的動態(tài)區(qū)域。當(dāng)在“Info”工作表中添加省份行和相應(yīng)的城市列時，“Data”工作表中的下拉列表會自動更新添加這些省份和城市。

首先，在“Data”工作表中，選取單元格B2，調(diào)出“新建名稱”對話框，并設(shè)置如下：

• 名稱：UsingList

• 引用位置：=INDEX(Datas,1,MATCH(Data!A2,Info!$1:$1,0)):INDEX(Datas,RowNum,MATCH(Data!A2,Info!$1:$1,0))

注意：與上文相同，由于要相對引用單元格左側(cè)的值，因此在定義名稱時，一定要選擇“Data”工作表的單元格B2。

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

在“Data”工作表中，選擇單元格A2:A10，設(shè)置“數(shù)據(jù)有效性”如下：

選擇單元格B2:B10，設(shè)置“數(shù)據(jù)有效性”如下：

設(shè)置完成后的效果如下圖所示：

調(diào)試

我們在“Info”工作表中添加省份和相應(yīng)的城市名，如下圖所示：

在“Data”工作表中的下拉列表會自動更新，如下圖所示：

小結(jié)

• 使用Index函數(shù)，創(chuàng)建了一個動態(tài)名稱變量，并創(chuàng)建了動態(tài)區(qū)域，能夠隨著列的增減而自動調(diào)整。

• 定義名稱時一定要注意，由于采用相對引用，因此在定義名稱時必須將當(dāng)前單元格置于相對引用合適的單元格位置。

• 與OFFSET函數(shù)不同，INDEX函數(shù)是非易失函數(shù)，這意味著工作表不會經(jīng)常自動計算，提高了工作表速度。

• INDEX函數(shù)的用法詳見《Excel函數(shù)學(xué)習(xí)2：INDEX函數(shù)》，OFFSET函數(shù)的用法詳見《Excel函數(shù)學(xué)習(xí)12：OFFSET函數(shù)》。