Excel数据有效性实例教学文案Word文档下载推荐.docx
《Excel数据有效性实例教学文案Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《Excel数据有效性实例教学文案Word文档下载推荐.docx（24页珍藏版）》请在冰豆网上搜索。
防止一个范围数据总数超过指定值.本例中,预算不能超过$3500.预算总额统计的单元格在C3:
C7范围内
1.选择单元格C3:
C7
4.在“公式”框中,使用SUM函数统计$C$3:
$C$7合计值.结果必须小于或等于$3500:
=SUM（$C$3:
$C$7）<
=350
没有前置或后置间隔
防止用户在输入文本前面或后面加入空白间隔.TRIM函数移除文本前后空白间隔.
1.选择单元格B2
4.在“公式”框中,输入:
=B2=TRIM（B2）
防止输入周末日期
防止输入的日期为星期六或星期日.WEEKDAY将输入的日期返回到星期,并且不允许其值为1（星期日）和7（星期六）.
在“公式”框中,输入:
=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）为这个类别所属的品名列表定义一个名称,例如:
水果.这个名称必须与农产品列表中所属类别名称正确匹配.
f）用同样的方法创建类别中其它条目所属的列表–本例中为蔬菜.
应用数据有效性
在种类（也就是上述的类别）列表的单元格右侧有一个下拉箭头显示可以选择输入的类别.
在品名列表单元格数据有效性中使用了INDIRECT函数创建了一个下拉列表..
1.应用数据有效性
a）选择你想在数据有效性中应用类别列表的单元格
b）从“数据”菜单中选择“有效性”.
c）在“允许”下拉列表中选择“序列”
d）在“来源”框中,输入一个等号和序列名称,例如:
=农产品
e）点击“确定”.
2.创建所属的数据有效性
a）选择依附类别单元格中已经输入条目（水果或蔬菜）并与这些条目匹配输入的应用数据有效性的单元格
d）在“来源”框中,输入一个引用到类别列对应单元格的INDIRECT函数,:
=INDIRECT（A2）
测试数据有效性
种类列单元格将显示农产品列表.
品名列将根据种类列已经输入的类别显示水果或蔬菜列表
应用两个词条
有时你可能需要在第一个下拉列表中应用两个词条.例如,你可以选择'
红色水果'
'
绿色水果'
和'
黄色水果'
1.用上述方法创建第第一个名称范围和下拉列表.
2.应用一个词条创建对应的列表,例如：
红色水果,绿色水果,黄色水果
3.在允许下拉框中选择序列,在来源框中使用一个公式移除名称中间隔.例如:
=INDIRECT（SUBSTITUTE（A2,"
"
"
"
））
在名称中使用非法字符
有时在第一个名称范围下拉列表中名称中可能你要用到定义名称不支持的非法字符,比如连接符（&
）.例如,你选择的条目分别是'
绿色水果'
和'
黄色&
橙色水果'
2.使用一个词条名称创建一个支持的名称列表,例如：
红色水果,绿色水果,黄色或橙色水果
3.创建一个包含第一个下拉列表名称的查询表格.
4.在毗邻单元格输入正确的名称
5.命名这个表格,如：
NameLookup
6.在允许下拉框中选择序列,在来源框中使用一个公式查找正确的名称.例如:
=INDIRECT（VLOOKUP（A2,NameLookup,2,0））
使用动态列表
因为INDIRECT函数的作用仅为引用,并非公式,前面的方法不能工作于动态列表.你可以使用下面的方法替代它:
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:
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$6）,1）
4.点击确定
1.选择你要应用数据有效性的单元格
2.选择数据菜单下的有效性.
3.从允许下拉列表中,选择序列
4.在来源框中,输入一个等号和序列名称,例如:
=NameCheck
5.点击确定.
B列下拉列表中仅显示没有使用过的姓名.已经使用过的姓名已经被移除（即已经派出的员工不会再次被派出）.
Excel--数据有效性–添加提示信息
你可以给使用电子表格的人员提示信息.在选择有数据有效性的单元格时显示输入信息.如果输入无效数据则显示出错警告.
输入信息
a）选择你要应用数据有效性的单元格
b）在设置标签下应用需要的数据有效性（什么是数据有效性?
）
c）点击输入信息标签
d）选中选定单元格时显示输入作息.
e）在标题框内输入信息标题文本.这个文本将发粗体显示在提示框的顶部.
f）在输入信息框中输入提示信息.
g）点击确定或进行下一项对出错警告进行设置.
出错警告
a）点击出错警告标签
b）选中输入无效数据时显示出错警告.
c）从样式下拉列表中选择一种出错警告样式.
1.中止:
其作用是防止输入无效数据.
2.如果点击重试按钮,则输入的无效数据突出显示,你可以重新输入.
3.如果点击取消按钮,则自动删除无效数据,单元格恢复原始的内容.
4.这样可禁止使用者在单元格中输入无效数据
2.警告:
为输入无效数据设置一个障碍.
3.如果点击是按钮,则接受无效数据输入,并选择下一个单元格.
4.如果点击否按钮,无效数据突出显示,可以重新输入.
5.如果点击取消按钮,无效数据被自动删除,单元格恢复原始内容.
6.这样可使使用者选择是否在单元格中输入无效数据.
3.作息:
输入无效数据时给出提示信息.
4.如果点击确定按钮,则接受无效数据输入,并选择下一个单元格.
升级会员 