Excel数据有效性使用方法090516

1、什么是数据有效性?数据有效性一个包含帮助你在工作表中输入资料提示信息的工具.它有如下功能:-给用户提供一个选择列表-限定输入内容的类型或大小-自定义设置Excel -数据有效性-自定义条件示例防止输入重复值 防止在工作表一定范围输入重复值.本例中，在单元格B3:B10中输入的是员工编号.选择单元格B3:B10选择数据I有效性在“允许”下拉框中选择“自定义”在“公式”框中，使用COUNTIF函数统计B3出现次数，在$B$3:$B$10范围内.结果必须是1或0:=COUNTIF($B$3:$B$10,B3)=1限定总数防止一个范围数据总数超过指定值,本例中，预算不能超过$3500,预算总额统计的单

2、元格在C3:C7范围内选择单元格C3:C7选择数据I有效性在“允许”下拉框中选择“自定义”在“公式”框中，使用SUM函数统计$C$3:$C$7合计值,结果必须小于或等于$3500:=SUM($C$3:$C$7)=350匚设置输入信息出错警告|输入法模式| 有效性条件-没有前置或后置间隔防止用户在输入文本前面或后面加入空白间隔.TRIM函数移除文本前后空白间隔.选择单元格B2选择数据I有效性在“允许”下拉框中选择“自定义”在“公式”框中，输入：=B2=TRIM(B2)防止输入周末日期防止输入的日期为星期六或星期日.WEEKDAY将输入的日期返回到星期，并且不允许其值为1 (星期日)和7 (星期六

3、).选择单元格B2选择数据|有效性在“允许”下拉框中选择“自定义”在“公式”框中，输入:=AND(WEEKDAY(B2)1,WEEKDAY(B2)7)创建下拉列表选项使用数据有效性可以为一个单元格创建一个选择输入内容的下拉列表.列表数据项可以在工作表的行或列中输 入，也可以直接在数据有效性对话框中输入.创建列表数据项在一个半单行或单列中输入你想在下拉列表中看到的条目.命名列表范围如果你在一个工作表中输入了一个有效性列表条目，并且给它定义了名称，你就可以在同一工作簿的其它工作表 的数据有效性对话框中引用这个名称.选择列表单元格范围.I.FruitList =寸 名赢厂点击公式编辑栏左边的名称框(

4、Name Box)定义一个名称，如：FruitList.按回车键.应用数据有效性选择你想应用数据有效性的单元格“数据”一 “有效性”.点击“允许”框右侧的下拉箭头，在列表中选择“序列”在来源对话框中输入一个等号和列表名称，如:=FruitList输发法模式点击确定.数据有效性设置I输入信息I出错警告有效性条件:允许兔r值 一 何数数任整小时间 文本长度 自定义IF对有同样设置的所有其他单元格应用逮些更改(F) 你可以使用定义一个范围和INDIRECT函数在数据有效性列表中根据前一单元格内容 限制选择条目.本例中，如果你在类别中选择水果，在名称下拉列表仅显示水果类名称.创建名称列表 首先命名单元

5、格范围.本示例中，第一个列表定义的名称为农产品.它包括的条目有-水果和蔬菜.创建第一个名称列表a）在工作簿的空白区域，输入你想在下拉列表中看到的条目.它必须是一个词条，并 且与所属的品名名称相匹配.b）选择列表包含的单元格（不包括标题）.c）点击公式编辑栏左侧名称框.d）为列表输入一个名称，例如：农产品.e）按回车键.名称框农产品列表北果创建对应第一个名称列表的名称列表a）输入你想在农产品列表类别之一下拉列表中看到的词条b）选择包含这些词条的单元格列表.c）点击公式编辑栏左侧的名称框.d）为这个类别所属的品名列表定义一个名称，例如:水果.这个名称必须与农产品列表中所属类别名称正确匹配.e）按回

6、车键.水果列表水果 =苹果果蕉槌芋T香柠桃f）用同样的方法创建类别中其它条目所属的列表-本例中为蔬菜.应用数据有效性 在种类（也就是上述的类别）列表的单元格右侧有一个下拉箭头显示可以选择输入的 类别.在品名列表单元格数据有效性中使用了 INDIRECT函数创建了一个下拉列表.应用数据有效性a）选择你想在数据有效性中应用类别列表的单元格b）从“数据”菜单中选择“有效性”.耐s I M100%嗪:e atQ收藏夹.前往妲.国仔冒文件。编辑俱）视图世）插a a）格式但）工具堡）数据云窗口地）帮助电）以排序窈.蔬选史）1=水果i BCDE_.品名n|数据透视表和图表报告崖）.分列（!）.获取外部数据鱼

7、）有效性（!）.c）在“允许”下拉列表中选择“序列”d）在“来源”框中，输入一个等号和序列名称，例如：=农产品e）点击“确定”.2.创建所属的数据有效性a）选择依附类别单元格中已经输入条目（水果或蔬菜）并与这些条目匹配输入的应 用数据有效性的单元格b）从“数据”菜单中选择“有效性”.c）在“允许”下拉列表中选择“序列”d）在“来源”框中，输入一个引用到类别列对应单元格的INDIRECT函数，：=INDIRECT（A2）e）点击“确定”.测试数据有效性种类列单元格将显示农产品列表.品名列将根据种类列已经输入的类别显示水果或蔬菜列表应用两个词条 有时你可能需要在第一个下拉列表中应用两个词条.例如，

8、你可以选择红色水果, 绿色 水果和黄色水果用上述方法创建第第一个名称范围和下拉列表.应用一个词条创建对应的列表，例如：红色水果，绿色水果，黄色水果在允许下拉框中选择序列,在来源框中使用一个公式移除名称中间隔.例如:=INDIRECT(SUBSTITUTE(A2, ,)在名称中使用非法字符有时在第一个名称范围下拉列表中名称中可能你要用到定义名称不支持的非法字符比如连接符 (&).例如，你选择的条目分别是红色水果，绿色水果和黄色&橙色水果用上述方法创建第第一个名称范围和下拉列表.使用一个词条名称创建一个支持的名称列表，例如：红色水果，绿色水果，黄色或橙色 水果创建一个包含第一个下拉列表名称的查询表

9、格.在毗邻单元格输入正确的名称命名这个表格，如：NameLookup在允许下拉框中选择序列，在来源框中使用一个公式查找正确的名称.例如：=INDIRECT(VLOOKUP(A2,NameLookup,2,0)BD1绿色水果库果I.一一一一一iHLnI拶振僦5红色水杲苹果鲜橙日黄色俊橙邑水晃草莓柠臻狒猴桃嫁色水果红色水果. 一一.红色水果W黄色&橙色水果黄色或橙色水果11绿色水果绿:色丕果使用动态列表因为INDIRECT函数的作用仅为引用，并非公式，前面的方法不能工作于动态列表. 你可以使用下面的方法替代它：用上述方法创建第第一个名称范围和下拉列表.创建支持的名称列表，并且命名第个范围的第一个单

10、元格，例如：单元格B1 命名为“水果”且单元格C1命名为“蔬菜”.用每个找到的列表命名列，例如：B列命名为“水果Col”，C列命名为“蔬 菜 Col”在允许下拉框中选择序列，在来源框中使用一个公式推算查找范围.例如，如果 第一个下拉列表在单元格E2。则公式为：=OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&Col),1)Excel -数据有效性一在下拉列表中隐藏前面使用过的条目在数据有效性列表中你可以限制选择条目，隐藏前面选择过的条目.例如，某公司 分派员工出差，为避免重复派出，即已经派出在外的员工再次派出则会造成失误。这时，你就可以设计在有效性下拉列

11、表中，移除已经使用过的条目（即已经派出的员工不会再出现在侯选列表中）A 冬CD工香龙一 员程程B日 壬 日6 6出发日期日 6 月 6日日日 6 6 6 月月，月， 6 6- 6 5 6 7这些单元格应用 了数据有效性，|_口设置主表首先设计你想要运用数据有效性的表格布局.本例中，使用了工作表时间表中的 A1:C7单元格范围.在B列使用了数据有效性.创建列表条目创建一个你想在数据有效性下拉列表看到条目的列表.在这里，在工作表“员工”的 A1:A6,单元格输入员工姓名创建有效列表A）输入一个统计已经派出的员工名单的公式.在“员工”工作表的B1单元格输入下列公式：=IF（COUNTIF（时间表!$

12、B$2:$B$7,A1）=1,ROW（）将公式向下复制到B6单元格.这个公式统计程香宙在工作表“时间表”中B2:B7区域出现次数.如果返回结 果大于或等于1,单元格将显示为空白.否则则返回行数.B）创建一个没有使用的名称列表 下一步将使用一个数组公式将空白的单元格移动到序列的最后边在C1单元格输入数组公式（这个公式很长，但在同一行输入完成）.=IF aSNUWBER 饵 MALL (IF (JB$1:疝扼ROW (JB$1:ROW (1:1), INDIRECT. 焰 MAIL (IF (JB$1:1使用数组公式将空白 单元格移动到底部。呈香宙ROW 性斑1:$B$6),EOW (1:1),蜜

13、冰桢玲糙新=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),)按组合键Ctrl+Shift+Enter输入数组公式向下复制公式直到C6单元格.定义有效列表名称选择插入名称自定义在名称框中输入一个名称，例如NameCheck.在引用框中，输入下面公式（在一行内）：=OFFSET（员工 s!$C$1,0,0,COUNTA（员工 s!$C$1:$C$6）-COUNTBLANK （员工 s!$C$1:$C$6）,1）点击确定应用

14、数据有效性选择你要应用数据有效性的单元格选择数据菜单下的有效性从允许下拉列表中，选择序列在来源框中，输入一个等号和序列名称，例如：=NameCheck点击确定.测试数据有效性B列下拉列表中仅显示没有使用过的姓名.已经使用过的姓名已经被移除（即已经派出的员工 不会再次被派出）.Excel -数据有效性-添加提示信息你可以给使用电子表格的人员提示信息.在选择有数据有效性的单元格时显示输入信 息.如果输入无效数据则显示出错警告.输入信息a）选择你要应用数据有效性的单元格b）在设置标签下应用需要的数据有效性（什么是数据有效性?）c）点击输入信息标签d）选中选定单元格时显示输入作息设置！输入信愆:1出睛

15、典皆1输人法模式 III咨琶罗元格时显示输入信息窑选迫加涧显示下列输入蓿息:e）在标题框内输入信息标题文本.这个文本将发粗体显示在提示框的顶部.f）在输入信息框中输入提示信息.g）点击确定或进行下一项对出错警告进行设置.选定单元格的显示下列输入信息：标题要；输入信息始：认卞拉列表中辱一个类别出错警告a）点击出错警告标签b）选中输入无效数据时显示出错警告教招有效性设置输入信息出错警告|输五法模式|P输入无效数据时显示出错警告 辱入无效数据时显示下列出错警告：一c）从样式下拉列表中选择一种出错警告样式.中止：其作用是防止输入无效数据.如果点击重试按钮，则输入的无效数据突出显示，你可以重新输入.如果

16、点击取消按钮，则自动删除无效数据，单元格恢复原始的内容. 这样可禁止使用者在单元格中输入无效数据警告：为输入无效数据设置一个障碍.如果点击是按钮，则接受无效数据输入，并选择下一个单元格.如果点击否按钮,无效数据突出显示，可以重新输入.如果点击取消按钮,无效数据被自动删除，单元格恢复原始内容. 这样可使使用者选择是否在单元格中输入无效数据.作息：输入无效数据时给出提示信息.如果点击确定按钮，则接受无效数据输入，并选择下一个单元格.如果点击取消按钮，无效数据被自动删除，单元格恢复原始内容.这样可使使用者选择是否在单元格中输入无效数据.d）在标题框中输入标题文本.这个文本将以粗体形式显示在弹出的信息

17、窗口上部.f）在出错信息框中输入要显示的信息.g）点击确定注解：如果Office助手是打开的，则会弹出下面的提示Excel -数据有效性-使用源于其它工作簿的列表你可以使用其它工作簿中的列表作为数据有效性下拉列表条目.要使数据有效性能够正常运行，包含列表的工作簿必须是打开的.你可以使这个工作簿总是打 开，但可以隐藏起来，比如1.xls工作簿.创建数据源列表假定有一个名为1.xls的工作簿，其中有一个名称范围被定义为Name.引用到数据源打开你想在其中使用数据有效性的工作簿.选择插入名称 自定义输入列表名称，如顾客名单在引用到框中，输入名称范围的引用.首先是等号，接着是带单引号的工作表名和一个

18、感叹号，最后是定义的名称，例如=1.xls!name点击确定创建下拉列表选择你要使用数据有效性的单元格.选择数据 有效性在允许框中，选择序列在来源框中输入列表名称，前面有一个等号，例如:=顾客名单点击确定Excel -数据有效性-有效性条件示例整数设置或排除一定范围内的数值，也可以自定义最小值或最大值.在数据有效性对话框中输入值，或者引用到工作表中的单元格，或者使用公式设置值小数 设置或排除一定范围内的数值，也可以自定义最小值或最大值.1.2.3.1.2.3.在数据有效性对话框中输入值，或者 引用到工作表中的单元格，或者 使用公式设置值日期 设置或排除一定范围内的日期，也可以自定义最小值或最大值.在数据有效性对话框中输入日期，或者引用到工作表中的单元格，或者使用公式设置日期本例中，当前日期为起始日期，当前日期加7天为结束日期.时间 设置或排除一定范围内的时间，也可以自定义最小值或最大值.1.1.在数据有效性对话