EXCEL 数据有效性

EXCEL     数据有效性

先上传两个例子数据有效性创建参照列表1.xls，

　　　　　　　数据有效性创建参照列表2.xls

本例中, 如果你在类别中选择水果, 在名称下拉列表仅显示水果类名称.

 

创建名称列表

首先命名单元格范围.
本示例中, 第一个列表定义的名称为农产品.
它包括的条目有– 水果和蔬菜. 

1. 创建第一个名称列表

a) 在工作簿的空白区域, 输入你想在下拉列表中看到的条目.它必须是一个词条，并且与所属的品名名称相匹配.
b) 选择列表包含的单元格 (不包括标题).
c) 点击公式编辑栏左侧名称框.
d) 为列表输入一个名称, 例如： 农产品.
e) 按回车键.

2. 创建对应第一个名称列表的名称列表

a) 输入你想在农产品列表类别之一下拉列表中看到的词条.
b) 选择包含这些词条的单元格列表.
c) 点击公式编辑栏左侧的名称框.
d) 为这个类别所属的品名列表定义一个名称, 例如:水果. 这个名称必须与农产品列表中所属类别名称正确匹配.
e) 按回车键.

f)用同样的方法创建类别中其它条目所属的列表 –本例中为蔬菜.

应用数据有效性

在种类（也就是上述的类别）列表的单元格右侧有一个下拉箭头显示可以选择输入的类别.
在品名列表单元格数据有效性中使用了INDIRECT函数创建了一个下拉列表..

1. 应用数据有效性

a) 选择你想在数据有效性中应用类别列表的单元格
b) 从“数据”菜单中选择“有效性”.

c) 在“允许”下拉列表中选择“序列”
d) 在“来源”框中, 输入一个等号和序列名称, 例如: =农产品
e) 点击“确定”.

2. 创建所属的数据有效性

a) 选择依附类别单元格中已经输入条目（水果或蔬菜）并与这些条目匹配输入的应用数据有效性的单元格
b) 从“数据”菜单中选择“有效性”.
c) 在“允许”下拉列表中选择“序列”
d) 在“来源”框中, 输入一个引用到类别列对应单元格的INDIRECT函数,: =INDIRECT(A2)
e) 点击“确定”.

测试数据有效性

种类列单元格将显示农产品列表.
品名列将根据种类列已经输入的类别显示水果或蔬菜列表

应用两个词条

有时你可能需要在第一个下拉列表中应用两个词条. 例如, 你可以选择’红色水果’, ‘绿色 水果’和’黄色水果’

1. 用上述方法创建第第一个名称范围和下拉列表.
2. 应用一个词条创建对应的列表,例如：红色水果, 绿色水果, 黄色水果
3. 在允许下拉框中选择序列,在来源框中使用一个公式移除名称中间隔. 例如:

=INDIRECT(SUBSTITUTE(A2,” “,”"))

在名称中使用非法字符

有时在第一个名称范围下拉列表中名称中可能你要用到定义名称不支持的非法字符, 比如连接符(&). 例如, 你选择的条目分别是’红色水果’, ‘绿色水果’ 和 ‘黄色&橙色水果’

1. 用上述方法创建第第一个名称范围和下拉列表.
2. 使用一个词条名称创建一个支持的名称列表, 例如： 红色水果, 绿色水果, 黄色或橙色水果
3. 创建一个包含第一个下拉列表名称的查询表格.
4. 在毗邻单元格输入正确的名称
5. 命名这个表格, 如：NameLookup
6. 在允许下拉框中选择序列,在来源框中使用一个公式查找正确的名称. 例如:

=INDIRECT(VLOOKUP(A2,NameLookup,2,0))

使用动态列表

因为INDIRECT函数的作用仅为引用, 并非公式, 前面的方法不能工作于动态列表. 你可以使用下面的方法替代它:

1. 用上述方法创建第第一个名称范围和下拉列表.
2. 创建支持的名称列表,并且命名第个范围的第一个单元格, 例如： 单元格 B1命名为“水果”且单元格 C1命名为“蔬菜”.
3. 用每个找到的列表命名列, 例如： B列命名为“水果Col”，C列命名为“蔬菜Col”

在允许下拉框中选择序列,在来源框中使用一个公式推算查找范围. 例如, 如果第一个下拉列表在单元格 E2。 则公式为：=OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&”Col”)),1)