Excel实战技巧111：自动更新的级联组合框

excelperfect

引言：本文学习整理自www.xelplus.com，很好的一个示例，演示了在不使用VBA的情形下，如何创建自动更新的级联组合框。

本文将向你展示：

• 如何创建组合框下拉列表。

• 如何创建级联组合框下拉列表。

• 如何限制组合框下拉列表以排除空白单元格。

• 如何克服级联数据验证列表的问题，即一旦第一个列表的值发生更改，其关联的列表就不会自动重置——你将学习一种替代方法来克服自动重置失败的问题（一旦第一个列表的值发生变化，将自动刷新关联的列表）

通过使用组合框表单控件，我们将能够实现级联列表。与传统的数据验证（即“数据有效性”）下拉列表相比，组合框表单控件具有许多优点。

• 下拉指示器在组合框中始终可见；而在数据验证中，用户必须单击单元格来显示下拉指示器。

• 从属组合框将自动响应在第一个组合框中所做的后续更改。

本示例中所使用的数据如下图1所示。

图1

创建的级联组合框如下图2所示。

图2

单击功能区“开发工具”选项卡“控件”组中的“插入——表单控件——组合框”，如下图3所示。

图3

在工作表合适位置拖动鼠标，放置一个组合框并调整好大小。

选择组合框，单击右键，选择“设置控件格式”命令。在“设置控件格式”的“控制”选项卡（如下图4所示）中，有两个重要的属性：

• 数据源区域：包含要在下拉列表中显示的项目的单元格。

• 单元格链接：用于保存用户从列表中选择的单元格。因为组合框位于单元格上方，所以我们需要将用户的选择存储在传统的工作表单元格中。

图4

对于本示例，设置组合框的数据源和单元格链接如下图5所示。

图5

从图5中可以看到，组合框的选择与单元格K4链接，当我们选择组合框中的下拉列表项时，将会在该单元格中放置所选项在列表中的位置值。

下面，我们来创建级联的组合框。

在刚才的组合框下面，插入第二个组合框，如下图6所示。

图6

要使用“App内容”填充第二个组合框，可以使用多种方法：

• 直接引用包含项目的单元格。

• 使用公式创建动态列表。由于组合框不接受公式作为引用，因此必须将公式存储在名称管理器中，然后在组合框中引用名称。

我们使用了中间数据，因此使用第一种方法，直接引用单元格来填充第二个组合框。

我们想根据用户从第一个组合框中所做的选择创建一个动态的“App内容”列表，在此，将使用存储第一个组合框的单元格链接(K4)中的值。

图7

使用INDEX函数创建相关App的列表。在单元格N4中，输入公式：

=INDEX(A4:C4,,$K$4)

然后，向下拖动填充数据，结果如下图8所示。

图8

设置第二个组合框的格式如下图9所示。

图9

设置第二个组合框的源数据区域为N4:N18，单元格链接到M4以存储代表所选项位置的数字。

此时，你可以试试，当你在第一个组合框中选择时，第二个组合框中的列表项也随之发生更改。

我们再增加一项数据显示，当在第二个组合框中选择列表项后，其对应的营收会显示，如下图10所示。

图10

下图11为App对应的营收表。

图11

在单元格G7中，输入公式：

=IFERROR(VLOOKUP(INDEX(N4:N18,M4),A32:B71,2,FALSE),'')

下面，我们进一步完善这个示例。

注意到，当我们选择不同部门时，由于其对应的App列表长度不同，列表底部会存在空，如下图12所示。

图12

在此，我们通过定义名称来解决。定义一个名为“combo”的名称，其引用为：

=$N$4:INDEX($N$4:$N$18,MATCH('zzzzzzzz',$N$4:$N$18,1))

然后，更改第二个组合框的格式如下图13所示。

图13

完成后的结果如下图14所示。

图14

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。