Excel大数据有效性实例.docx
《Excel大数据有效性实例.docx》由会员分享，可在线阅读，更多相关《Excel大数据有效性实例.docx（23页珍藏版）》请在冰豆网上搜索。
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.点击公式编辑栏左边的名称框（NameBox）
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））
使用动态列表
因为INDIRECT函数的作用仅为引用,并非公式,前面的方法不能工作于动态列表.你可以使用下面的方法替代它:
1.用上述方法创建第第一个名称范围和下拉列表.
2.创建支持的名称列表,并且命名第个范围的第一个单元格,例如：
单元格B1命名为“水果”且单元格C1命名为“蔬菜”.
3.用每个找到的列表命名列,例如：
B列命名为“水果Col”，C列命名为“蔬菜Col”
在允许下拉框中选择序列,在来源框中使用一个公式推算查找范围.例如,如果第一个下拉列表在单元格E2。
则公式为：
=OFFSET（INDIRECT（$E2）,0,0,COUNTA（INDIRECT（E2&"Col"））,1）
Excel--数据有效性—在下拉列表中隐藏前面使用过的条目
在数据有效性列表中你可以限制选择条目,隐藏前面选择过的条目.例如,某公司分派员工出差，为避免重复派出，即已经派出在外的员工再次派出则会造成失误。
这时，你就可以设计在有效性下拉列表中，移除已经使用过的条目（即已经派出的员工不会再出现在侯选列表中）
.
设置主表
首先设计你想要运用数据有效性的表格布局.本例中,使用了工作表'时间表'中的A1:
C7单元格范围.在B列使用了数据有效性.
创建列表条目
创建一个你想在数据有效性下拉列表看到条目的列表.在这里,在工作表“员工”的A1:
A6,单元格输入员工姓名
创建有效列表
A）输入一个统计已经派出的员工名单的公式.
1.在“员工”工作表的B1单元格输入下列公式:
=IF（COUNTIF（时间表!
$B$2:
$B$7,A1）>=1,"",ROW（））
2.将公式向下复制到B6单元格.
这个公式统计"程香宙"在工作表“时间表”中B2:
B7区域出现次数.如果返回结果大于或等于1,单元格将显示为空白.否则则返回行数.
B）创建一个没有使用的名称列表
下一步将使用一个数组公式将空白的单元格移动到序列的最后边.
1.在C1单元格输入数组公式（这个公式很长，但在同一行输入完成）.
=IF（ISNUMBER（SMALL（IF（$B$1:
$B$6="","",ROW（$B$1:
$B$6））,ROW（1:
1）））,
INDIRECT（"A"&SMALL（IF（$B$1:
$B$6="","",ROW（$B$1:
$B$6））,ROW（1:
1）））,""）
2.按组合键Ctrl+Shift+Enter输入数组公式
3.向下复制公式直到C6单元格.
定义有效列表名称
1.选择插入>名称>自定义
2.在名称框中输入一个名称，例如NameCheck.
3.在引用框中,输入下面公式（在一行内）:
=OFFSET（员工s!
$C$1,0,0,COUNTA（员工s!
$C$1:
$C$6）-COUNTBLANK（员工s!
$C$1:
$C$6）,1）
4.点击确定
应用数据有效性
1.选择你要应用数据有效性的单元格
2.选择数据菜单下的有效性.
3.从允许下拉列表中,选择序列
4.在来源框中,输入一个等号和序列名称,例如:
=NameCheck
5.点击确定.
测试数据有效性
B列下拉列表中仅显示没有使用过的姓名.已经使用过的姓名已经被移除（即已经派出的员工不会再次被派出）.
Excel--数据有效性–添加提示信息
你可以给使用电子表格的人员提示信息.在选择有数据有效性的单元格时显示输入信息.如果输入无效数据则显示出错警告.
输入信息
a）选择你要应用数据有效性的单元格
b）在设置标签下应用需要的数据有效性（什么是数据有效性?
）
c）点击输入信息标签
d）选中选定单元格时显示输入作息.
e）在标题框内输入信息标题文本.这个文本将发粗体显示在提示框的顶部.
f）在输入信息框中输入提示信息.
g）点击确定或进行下一项对出错警告进行设置.
出错警告
a）点击出错警告标签
b）选中输入无效数据时显示出错警告.
c）从样式下拉列表中选择一种出错警告样式.
1.中止:
其作用是防止输入无效数据.
如果点击重试按钮,则输入的无效数据突出显示,你可以重新输入.
如果点击取消按钮,则自动删除无效数据,单元格恢复原始的内容.
这样可禁止使用者在单元格中输入无效数据
2.警告:
为输入无效数据设置一个障碍.
如果点击是按钮,则接受无效数据输入,并选择下一个单元格.
如果点击否按钮,无效数据突出显示,可以重新输入.
如果点击取消按钮,无效数据被自动删除,单元格恢复原始内容.
这样可使使用者选择是否在单元格中输入无效数据.
3.作息:
输入无效数据时给出提示信息.
如果点击确定按钮,则接受无效数据输入,并选择下一个单元格.
如果点击取消按钮,无效数据被自动删除,单元格恢复原始内容.
这样可使使用者选择是否在单元格中输入无效数据.
d）在标题框中输入标题文本.这个文本将以粗体形式显示在弹出的信息窗口上部.
f）在出错信息框中输入要显示的信息.
g）点击确定
注解:
如果Office助手是打开的，则会弹出下面的提示
Excel--数据有效性–使用源于其它工作簿的列表
你可以使用其它工作簿中的列表作为数据有效性下拉列表条目.
要使数据有效性能够正常运行,包含列表的工作簿必须是打开的.你可以使这个工作簿总是打开,但可以隐藏起来,比如1.xls工作簿.
创建数据源列表
假定有一个名为1.xls的工作簿,其中有一个名称范围被定义为Name.
引用到数据源
1.打开你想在其中使用数据有效性的工作簿.
2.选择插入>名称>自定义
3.输入列表名称，如顾客名单
4.在引用到框中,输入名称范围的引用.首先是等号,接着是带单引号的工作表名和一个感叹号,最后是定义的名称,例如='1.xls'!
name
5.点击确定
创建下拉列表
1.选择你要使用数据有效性的单元格.
2.选择数据>有效性
3.在允许框中,选择序列
4.在来源框中输入列表名称，前面有一个等号,例如:
=顾客名单
5.点击确定
Excel--数据有效性–有效性条件示例
整数
设置或排除一定范围内的数值,也可以自定义最小值或最大值.
1.在数据有效性对话框中输入值,或者
2.引用到工作表中的单元格,或者
3.使用公式设置值
小数
设置或排除一定范围内的数值,也可以自定义最小值或最大值.
1.1. 在数据有效性对话框中输入值,或者
2.2. 引用到工作表中的单元格,或者
3.3. 使用公式设置值
日期
设置或排除一定范围内的日期,也可以自定义最小值或最大值.
1.在数据有效性对话框中输入日期,或者
2.引用到工作表中的单元格,或者
3.使用公式设置日期
本例中,当前日期为起始日期,当前日期加7天为结束日期.
时间
设置或排除一定范围内的时间,也可以自定义最小值或最大值.
1.1. 在数据有效性对话框中输入时间,或者
2.2. 引用到工作表中的单元格,或者
3.3. 使用公式设置时间
本例公式中使用当前时间作为终止时间:
=TIME（HOUR（NOW（））,MINUTE（NOW（））,SECOND（NOW（）））
文本长度
设置或排除一定范围内的长度,也可以自定义最小值或最大值.
1.1. 在数据有效性对话框中输入长度,或者
2.2. 引用到工作表中的单元格,或者
3.3. 使用公式设置长度
升级会员 