Excel技术 | 数据有效性9：使用INDEX函数创建动态级联列表

 

准备工作表

首先准备两个工作表，“Data”工作表用于设置数据有效性来输入数据，“Info”工作表用于存放下拉列表条目。两个工作表分别如下图所示：

注意，上表中单元格区域B1:D1中的标题数据均为列A中的省份名称。如果需要在列A中添加省份，则应在列D后添加该省份名称作为第1行的标题。

创建动态命名区域

Province区域

单击“公式”选项卡“定义名称”按钮，在“新建名称”对话框中设置：

• 名称：Province

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

这样，就定义了一个名为“Province”的动态列表：起始于“Info”工作表中的单元格A2，终止于列A中最后一个数据单元格。上图所示的工作表中即为单元格区域A2:A4。

 

Datas区域

工作表“Data”中的数据有效性下拉列表将使用工作表“Info”中的数据。为了避免随着列数的增多，要创建更多的名称，我们创建一个动态的区域。

 

在“新建名称”对话框中设置如下：

• 名称：Datas

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

这将创建一个名为“Datas”的单元格区域：在工作表“Info”中，起始于单元格A2，扩展到100行，列数为第1行中有数据的列数。

注：100行是一个预估的数字，估计下拉列表条目不会超过100个，你可以根据实际修改。

 

RowNum

由于作为下拉列表条目的“Info”工作表中各列具有不同的行数，因此需要创建一个名为RowNum的名字变量，用来表示动态的列数。RowNum的值随引用的列不同而不同，例如，在上图所示的工作表中，如果引用“Info”工作表中的列B，则为5；如果是列C，则为6。

 

首先，在“Data”工作表中，在单元格A2中输入“湖北省”(这是一个临时值，之后可删除)。然后，选择“Data”工作表的单元格B2，调出“新建名称”对话框，并设置如下：

• 名称：RowNum

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

注意：由于要相对引用单元格左侧的值，因此在定义名称时，一定要选择“Data”工作表的单元格B2。

 

UsingList区域

最后创建名称“UsingList”的动态区域。当在“Info”工作表中添加省份行和相应的城市列时，“Data”工作表中的下拉列表会自动更新添加这些省份和城市。

 

首先，在“Data”工作表中，选取单元格B2，调出“新建名称”对话框，并设置如下：

• 名称：UsingList

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

注意：与上文相同，由于要相对引用单元格左侧的值，因此在定义名称时，一定要选择“Data”工作表的单元格B2。

 

设置数据有效性

在“Data”工作表中，选择单元格A2:A10，设置“数据有效性”如下：

选择单元格B2:B10，设置“数据有效性”如下：

 

设置完成后的效果如下图所示：

 

调试

我们在“Info”工作表中添加省份和相应的城市名，如下图所示：

在“Data”工作表中的下拉列表会自动更新，如下图所示：

小结

• 使用Index函数，创建了一个动态名称变量，并创建了动态区域，能够随着列的增减而自动调整。

• 定义名称时一定要注意，由于采用相对引用，因此在定义名称时必须将当前单元格置于相对引用合适的单元格位置。

• 与OFFSET函数不同，INDEX函数是非易失函数，这意味着工作表不会经常自动计算，提高了工作表速度。

• INDEX函数的用法详见《Excel函数学习2：INDEX函数》，OFFSET函数的用法详见《Excel函数学习12：OFFSET函数》。

---