最新excel数据有效性实例培训讲学

什么是数据有效性?

数据有效性一个包含帮助你在工作表中输入资料提示信息的工具. 它有如下功能:

--给用户提供一个选择列表

--限定输入内容的类型或大小

--自定义设置

Excel –数据有效性–自定义条件示例

防止输入重复值

防止在工作表一定范围输入重复值. 本例中, 在单元格B3:B10中输入的是员工编号.

1. 选择单元格B3:B10

2. 选择数据|有效性

3. 在“允许”下拉框中选择“自定义”

4. 在“公式”框中, 使用COUNTIF函数统计B3出现次数, 在$B$3:$B$10范围内. 结果必须是1或0:

=COUNTIF($B$3:$B$10,B3)<=1

限定总数

防止一个范围数据总数超过指定值.本例中, 预算不能超过$3500.预算总额统计的单元格在C3:C7范围内

1. 选择单元格C3:C7

2. 选择数据|有效性

3. 在“允许”下拉框中选择“自定义”

4. 在“公式”框中, 使用SUM函数统计$C$3:$C$7合计值. 结果必须小于或等于$3500:

=SUM($C$3:$C$7)<=350

没有前置或后置间隔

防止用户在输入文本前面或后面加入空白间隔. TRIM函数移除文本前后空白间隔.

1. 选择单元格B2

2. 选择数据|有效性

3. 在“允许”下拉框中选择“自定义”

4. 在“公式”框中, 输入:

=B2=TRIM(B2)

防止输入周末日期

防止输入的日期为星期六或星期日. WEEKDAY将输入的日期返回到星期, 并且不允许其值为1 (星期日) 和7 (星期六).

1. 选择单元格B2

2. 选择数据|有效性

3. 在“允许”下拉框中选择“自定义”

在“公式”框中, 输入: =AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7)

创建下拉列表选项

使用数据有效性可以为一个单元格创建一个选择输入内容的下拉列表. 列表数据项可以在工作表的行或列中输入, 也可以直接在数据有效性对话框中输入.

1. 创建列表数据项

a. 在一个半单行或单列中输入你想在下拉列表中看到的条目.

2.命名列表范围

如果你在一个工作表中输入了一个有效性列表条目，并且给它定义了名称，你就可以在同一工作簿的其它工作表的数据有效性对话框中引用这个名称.

1. 选择列表单元格范围.

2. 点击公式编辑栏左边的名称框(Name Box)

3. 定义一个名称,如：FruitList.

4. 按回车键.

3. 应用数据有效性

a.选择你想应用数据有效性的单元格

b.“数据”→“有效性”.

c.点击“允许”框右侧的下拉箭头，在列表中选择“序列”

d. 在来源对话框中输入一个等号和列表名称, 如: =FruitList

e. 点击确定.

你可以使用定义一个范围和INDIRECT函数在数据有效性列表中根据前一单元格内容限制选择条目.

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

创建名称列表

首先命名单元格范围.

本示例中, 第一个列表定义的名称为农产品.

它包括的条目有-- 水果和蔬菜.

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))