Ecel数据有效性实例

什么就是数据有效性?

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

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

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

--自定义设置

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