|
利用数据有效性，可以创建级联列表。参见下图，在列A的单元格中选取条目后，列B的单元格下拉列表中将出现与列A单元格内容相关的条目。 如何创建级联列表 第1步：命名列表中的条目 如下图所示，命名含有列表中条目的单元格区域。注意，第二级列表区域的名称应是第一级列表条目名称。具体来说，单元格区域A2:A3为一级列表的条目内容，命名为“省份”，包含“湖北省”和“江苏省”；单元格区域C2:C5和 E2:E5为二级列表的条目内容，分别命名为“湖北省”和“江苏省”，即一级列表的条目名称。 第2步：创建第一级下拉列表 选择需要创建第一级列表的单元格区域A1:A5，在“数据有效性”的“允许”下拉框中选择“序列”，在“来源”框中输入：=省份。 第3步：创建第二级下拉列表 选择需要创建第二级列表的单元格区域B1:B5，在“数据有效性”的“允许”下拉框中选择“序列”，在“来源”框中输入：=INDIRECT(A1)。 注：INDIRECT函数返回由文本字符串指定的引用，本例中为列A中单元格内容指定的名称引用。关于INDIRECT函数的详细讲解参见《Excel函数学习14：INDIRECT函数》。
如果列A中的单元格为空，那么在单击“确定”后，会弹出如下图所示的信息提示消息，单击“是”即可。
至此，本文开头的级联列表创建完成。在列A单元格的下拉列表中选取某条目后，在列B单元格下拉列表中会出现相关联的条目，选取后即可完成输入。
你可以按照前面的思路，以第二级列表中的条目作为第三级条目的名称，创建第三级列表。如下图所示：
保证级联列表的有效性 创建级联列表后，如果用户要修改已经通过级联列表输入的单元格数据，相关联的单元格内容不会发生变化。如下图所示，将单元格A3中的内容改为“江苏省”后，B3中的内容并不会自动变化。 可以通过修改数据有效性设置，来保证用户在完成级联列表输入的情况下，不能修改列A中的单元格，除非先将相关联的列B单元格修改为空。 选择列A中要设置数据有效性的单元格区域，在“数据有效性”框中“允许”下拉框中选择“序列”，在“来源”框中输入公式： =IF(B1='''',省份,INDIRECT(''NotChange'')) 单击“确定”后，会弹出一个信息提示框，单击“是”即可。 公式中INDIRECT(''NotChange'')的参数值NotChange是一个假的单元格区域名称，没有表示任何单元格区域。因此，如果列B中的单元格不为空，那么公式的结果为错误，列A中的数据有效性下拉列表不会响应。这样，就阻了用户在修改列A后，导致列B的值不一致的问题。（当然，也可以使用VBA代码来实现，我们会在后续讲解数据有效性的VBA代码时详细介绍）
根据前两个单元格的内容创建第三级列表 如下图所示，要根据列A和列B中的内容组合创建列C中的下拉列表。 如上文所述，命名列表单元格区域如下图所示： 按上文创建级联列表的方法创建列A和列B中的下拉列表。列C下拉列表项要根据列A和列B组合后的值而定，设置列C中相应单元格区域的数据有效性如下：选取单元格区域，在“数据有效性”框中“允许”下拉框中选择“序列”，在“来源”框中输入公式： =INDIRECT(SUBSTITUTE(A2&B2,'''',''''))
注：Excel规定名称中不能有空格，因此使用SUBSTITUTE函数去除代表名称的字符串中可能会有的空格。
避开名称规则 在创建级联列表过程中，我们使用了Excel的定义名称功能。然而，名称的命名有许多限制，譬如名称必须以字母或下划线开头，不能包含空格或其他无效字符，不能与Excel内部名称或工作簿中其他名称冲突，不允许使用字母r和c作为区域名称，不能以数字开头，等等。
在上文中，我们举例说明了使用SUBSTITUTE函数来避开代表名称字符串中的空格。这里，介绍通过查找来避开名称规则的技巧。
如下图所示的工作表，列A中包含以数字开头的单元格内容，若以此作为代表名称的字符串来为第二级列表的条目创建名称，Excel会提示错误。
此时，我们可以建立一个查找表（如下图所示），使用VLOOKUP函数在查找表中查找列A中的内容并转换为符合Excel名称命名规则的字符串，并以这些字符串为相应的列表命名，从而创建级联列表中的条目。
如下图所示，将列G中单元格内容作为各条目区域的名称。
然后，选择要创建级联列表的单元格区域，设置“数据有效性”如下：在“允许”下拉框中选择“序列”，在“来源”框中输入公式： =INDIRECT(VLOOKUP(A2,F1:G4,2,0))
扩展
|
|
|
