数据有效性9：使用INDEX函数创建动态级联列表 / 开普饭

在前面的文章中，我们介绍了使用OFFSET函数来创建动态更新的级联列表，本文介绍使用INDEX函数创建动态级联列表的方法和技巧。

准备工作表

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

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

创建动态命名区域

Province区域

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

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

Datas区域

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

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

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

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

RowNum

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

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

注意：由于要相对引用单元格左侧的值，因此在定义名称时，一定要选择“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”工作表中的下拉列表会自动更新，如下图所示：

小结