下载文档原格式
E X C E L的多级数据有效性设置EXCEL的多级数据有效性设置我们经常会遇到如图中的EXCEL表的情况，第一行是一级单位，下面都是二级单位的这种EXCEL形式的小型数据库，我们需要在表中先填写一级单位后再填写二级单位，但是为了防止填写EXCEL工作表的人乱填，需要严格限定单元格内容，同时由于有些数据量非常大，就需要减轻填表人的劳动强度，因此需要在数据有效性的基础上再进一步设置联动。
在EXCEL的sheet1表中选中A列，在数据菜单下选择有效性菜单，进入数据有效性对话框进行设置。
在弹出的数据有效性对话框的有效性条件下选择允许序列，在来源里填写数据库中的一级单位“工业企业,外贸企业,商业企业,个体户”需要注意的是它们之间用英语的逗号隔开。
同时选中忽略空值和提供下拉箭头选项。
由于数据有效性不支持从另一个EXCEL工作表中直接选取单元格，因此要做定义名称的工作，用快捷键ctrl+F3打开定义名称对话框，用自己认为好记的名词做为定义后的词，在引用位置设置需要引用的单元格。
本例中使用company 来代表sheet2中的A1至D1单元格的一级企业。
使用two来代表sheet2中A2这个单元格，由于二级单位有可能很多，因此只引用一个，在后面写公式时再详细说明。
定义在对二级单位进行数据有效性性设置时在来源中写如下的公式："=OFFSET(two,,MATCH($A2,company,)-1,COUNTA(OFFSET(two,,MATCH($A2,company,)-1,65535)))"这个公式的意思是A列某单元格的内容是某个一级公司，则在本列对应的单元格内显示这个一级公司下属的二级企业的下拉菜单供人们选择，最多可以显示65535个。
=OFFSET(客户名称,,MATCH($A2,first,)-1,COUNTA(OFFSET(客户名称,,MATCH($A2,first,)-1,65535)))=OFFSET(口,,MATCH($H6,客户名称,)-1,COUNTA(OFFSET(口,,MATCH($H6, 客户名称,)-1,65535)))=INDIRECT（province）这个就是数据有效性多重下拉菜单的效果，当A2单元格选中工业企业时，B2单元格就自动列出工业企业的二级单位名称，这样就达到了即能防止填表人乱填表，又能减轻填表人的劳动强度的目的。
公式在数据有效性中应用单元格不让输入数据，可以在有效性公式输入：=type(a1)<>1例1、限制B15：B21的单元格只能输入数字A列只许输入逻辑值，那么可设为：=NOT(A1)只能数字=isnumber()只能文本=istext()如果限制A列只能输入文字则用数据有效性--公式--=type(a1)=2=count(B5)=type(B5)=1=sign(B5)RADIANS(B5)=FACT(a1)isnumber()用来判断输入的是否为数字如果是数字,返回True,如果返回false则判断为错误信息(不是有效的)例2、限制B31：B39只能输入E31：E36中的数据电信电信移动联通铁通网通卫通例3、限制单元格只能输入以E开头的文本或者是=find("E",B47)=1e2本例中find函数返回查找"E"的位置如果位置为1,公式成立,如果不为1,不成立不成立的就返回false,则被判断为不有效.例4、限制单元格里面只能输入7位的数字2222222len检查输入数据的长度例5、限制单元格不能输入小于今天的日期例6：限制重复值的录入2123例7：保护公式商品价格数量总价栗子 2.738102.6衣服 5.352275.6车8.724208.8棋子25.15125.5花生66.7352334.5瘦肉12.366811.8薯仔 2.82775.6选择区域<<编辑<<定位<<定位条件<<公式<<例8：保证身份证的唯一性和位数=COUNTIF($B$43:$B$53,$B43)=1这个公式保证了没有重复输入=OR(LEN($B43)=15,LEN($B43)=18)保证15位或是18位用and则同时符合这两个条件时才可以输入=AND(countif($B$43:$B$53,$B43)=1,OR(len($B43)=15,len($B43)=18))例9：只允许汉字输入汉字，6汉字，3=len(B65)*2=lenb(B65)例10：创建动态下拉菜单运营商城市费用姓名A1B1C1D1A2B2C2D2A3B3C3D3A4B4C4D4A5B5C5D5A6B6C6D6A7B7C7D7A8B8C8D8一级科目二级科目城市B2城市B2。
数据验证技巧EXCEL的数据有效性设置数据验证技巧：EXCEL的数据有效性设置数据验证是指通过设置规则和限制，对输入的数据进行验证，确保数据的准确性和合法性。
在使用EXCEL进行数据处理和分析的过程中，合理地运用数据有效性设置，可以提高工作效率，并保证数据的质量。
本文将介绍几种常用的数据验证技巧，帮助读者更好地应用EXCEL中的数据有效性功能。
一、基础数据验证基础数据验证通过限定单元格的输入内容，确保数据符合设定的规则。
在EXCEL中，我们可以通过以下几种方式进行基础数据验证：1. 数字验证：限制输入为特定范围的数字。
例如，我们可以设置某一单元格只允许输入1到100之间的整数。
这样一来，用户在输入数据时，系统会自动进行判断和提醒，避免输入错误的数据。
2. 文本长度验证：限制输入的文本长度不超过一定的字符数。
例如，我们可以设置某一单元格只允许输入不超过10个字符的文本内容。
这样可以避免输入过长的文本，导致数据混乱。
3. 列表验证：通过设定一个选项列表，限制输入的内容只能是列表中的选项之一。
例如，我们可以设置某一单元格只允许从下拉列表中选择性别（男、女）。
这样可以确保输入内容的准确性和规范性。
二、条件数据验证条件数据验证是指根据不同的条件，对输入的数据进行验证和限制。
在EXCEL中，我们可以通过以下几种方式进行条件数据验证：1. 日期验证：限制输入的日期必须符合特定的条件。
例如，我们可以设置某一单元格只允许输入当天之后的日期，或者只允许输入工作日日期。
通过这种方式，可以避免输入非法的日期数据。
2. 公式验证：通过设置特定的公式，对输入的数据进行验证。
例如，我们可以设置某一单元格的输入值必须大于或等于某一单元格的数值，并在输入时进行实时的计算和验证。
这样可以确保输入数据的准确性和逻辑性。
三、自定义数据验证除了以上提到的基础数据验证和条件数据验证，EXCEL还提供了自定义数据验证的功能，允许用户根据具体的需求，自定义验证条件。
技巧 1 在单元格创建下拉列表有许多新手在EXCEL 中第一次见到下图所示的下拉列表时，都以为是程序做的，当他们知道图中下拉列表只是一个普通的利用数据有效性完成的EXCEL 技巧时，他们会觉得很惊奇。
那末，现在我们一起学习一下，怎么利用数据有效性来做个下拉列表吧：第一步在一个连续的单元格区域输入列表中的项目,如图中E7:E11 有个商品名称的表第二步选中A2 单元格，单击“菜单”——“数据”——“有效性”,在“数据有效性”对话框的"设置"选项卡中,在“允许”下拉列表中选择“序列”项.第三步在"来源"框中输入“=$E$8:$E$11” (或者输入“=”号后，用鼠标选中E8:E11)第四步勾选"忽略空值"与"提供下拉箭头"复选框,如图所示第五步单击"确定"按钮,关闭"数据有效性"对话框. 这样,就能实现第一张图所示的效果了。
如果列表的内容较少,或者不方便在工作表中输入列表项目,也可以省略上述的第一步,然后将第三步的操作改为:直接在"来源"框中输入列表内容,项目之间以半角的逗号分隔.如图所示在普通情况下,数据的有效性中的序列来源,只能引用当前工作表中的单元格区域。
如果希翼能够引用其他工作表中的单元格区域,则必须先为单元格区域定义名称,然后在"来源" 框中输入名称.例如,将另一张工作表中的A2:A10 区域，名称定义为“SPMC”,然后在“数据有效性”的“来源”框中输入“=SPMC” 。
另类的批注当我们需要对表格中的项目进行特殊说明时,往往会使用 EXCEL 的批注功能。
给单元格做批注的方法，这里不多浪费时间。
而给大家介绍一下另类批注：使用批注多了，我们会发现 EXCEL 的批注也有不足之处 :一、批注框的大小尺寸会受到单元格行高变化的影响 ;二、批注框的默认情况下，是只显示标识符。
什么是数据有效性?数据有效性一个包含帮助你在工作表中输入资料提示信息的工具. 它有如下功能:--给用户提供一个选择列表--限定输入内容的类型或大小--自定义设置Excel –数据有效性–自定义条件示例防止输入重复值防止在工作表一定范围输入重复值. 本例中, 在单元格B3:B10中输入的是员工编号.1. 选择单元格B3:B102. 选择数据|有效性3. 在“允许”下拉框中选择“自定义”4. 在“公式”框中, 使用COUNTIF函数统计B3出现次数, 在$B$3:$B$10范围内. 结果必须是1或0:=COUNTIF($B$3:$B$10,B3)<=1限定总数防止一个范围数据总数超过指定值.本例中, 预算不能超过$3500.预算总额统计的单元格在C3:C7范围内1. 选择单元格C3:C72. 选择数据|有效性3. 在“允许”下拉框中选择“自定义”4. 在“公式”框中, 使用SUM函数统计$C$3:$C$7合计值. 结果必须小于或等于$3500:=SUM($C$3:$C$7)<=350没有前置或后置间隔防止用户在输入文本前面或后面加入空白间隔. TRIM函数移除文本前后空白间隔.1. 选择单元格B22. 选择数据|有效性3. 在“允许”下拉框中选择“自定义”4. 在“公式”框中, 输入:=B2=TRIM(B2)防止输入周末日期防止输入的日期为星期六或星期日. WEEKDAY将输入的日期返回到星期, 并且不允许其值为1 (星期日) 和7 (星期六).1. 选择单元格B22. 选择数据|有效性3. 在“允许”下拉框中选择“自定义”在“公式”框中, 输入: =AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7)创建下拉列表选项使用数据有效性可以为一个单元格创建一个选择输入内容的下拉列表. 列表数据项可以在工作表的行或列中输入, 也可以直接在数据有效性对话框中输入.1. 创建列表数据项a. 在一个半单行或单列中输入你想在下拉列表中看到的条目.2.命名列表范围如果你在一个工作表中输入了一个有效性列表条目，并且给它定义了名称，你就可以在同一工作簿的其它工作表的数据有效性对话框中引用这个名称.1. 选择列表单元格范围.2. 点击公式编辑栏左边的名称框(Name Box)3. 定义一个名称,如：FruitList.4. 按回车键.3. 应用数据有效性a.选择你想应用数据有效性的单元格b.“数据”→“有效性”.c.点击“允许”框右侧的下拉箭头，在列表中选择“序列”d. 在来源对话框中输入一个等号和列表名称, 如: =FruitListe. 点击确定.你可以使用定义一个范围和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.命名这个表格, 如：NameLookup6.在允许下拉框中选择序列,在来源框中使用一个公式查找正确的名称. 例如:=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. 在来源框中, 输入一个等号和序列名称,例如: =NameCheck5. 点击确定.测试数据有效性B列下拉列表中仅显示没有使用过的姓名.已经使用过的姓名已经被移除（即已经派出的员工不会再次被派出）.Excel -- 数据有效性–添加提示信息你可以给使用电子表格的人员提示信息.在选择有数据有效性的单元格时显示输入信息.如果输入无效数据则显示出错警告.输入信息a)选择你要应用数据有效性的单元格b)在设置标签下应用需要的数据有效性 (什么是数据有效性?)c) 点击输入信息标签d) 选中选定单元格时显示输入作息.e) 在标题框内输入信息标题文本. 这个文本将发粗体显示在提示框的顶部.f) 在输入信息框中输入提示信息.g) 点击确定或进行下一项对出错警告进行设置.出错警告a) 点击出错警告标签b)选中输入无效数据时显示出错警告.c) 从样式下拉列表中选择一种出错警告样式.1.中止: 其作用是防止输入无效数据.2.如果点击重试按钮, 则输入的无效数据突出显示,你可以重新输入.3.如果点击取消按钮, 则自动删除无效数据, 单元格恢复原始的内容.4.这样可禁止使用者在单元格中输入无效数据2.警告: 为输入无效数据设置一个障碍.3.如果点击是按钮, 则接受无效数据输入, 并选择下一个单元格.4.如果点击否按钮,无效数据突出显示, 可以重新输入.5.如果点击取消按钮,无效数据被自动删除,单元格恢复原始内容.6.这样可使使用者选择是否在单元格中输入无效数据.3.作息: 输入无效数据时给出提示信息.4.如果点击确定按钮, 则接受无效数据输入, 并选择下一个单元格.5.如果点击取消按钮,无效数据被自动删除,单元格恢复原始内容.6.这样可使使用者选择是否在单元格中输入无效数据.d) 在标题框中输入标题文本. 这个文本将以粗体形式显示在弹出的信息窗口上部.f) 在出错信息框中输入要显示的信息.g) 点击确定注解: 如果Office 助手是打开的，则会弹出下面的提示Excel -- 数据有效性–使用源于其它工作簿的列表你可以使用其它工作簿中的列表作为数据有效性下拉列表条目.要使数据有效性能够正常运行, 包含列表的工作簿必须是打开的. 你可以使这个工作簿总是打开, 但可以隐藏起来, 比如 1.xls 工作簿.创建数据源列表假定有一个名为1.xls的工作簿,其中有一个名称范围被定义为Name.引用到数据源1.打开你想在其中使用数据有效性的工作簿.2.选择插入>名称>自定义3.输入列表名称，如顾客名单4.在引用到框中, 输入名称范围的引用. 首先是等号, 接着是带单引号的工作表名和一个感叹号, 最后是定义的名称, 例如='1.xls'!name5.点击确定创建下拉列表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.使用公式设置长度尼康D5000完全使用指南一、尼康D5000简介尼康D5000(配18-55mm镜头) 图库评测论坛报价网购实价佳能的入门级视频数码单反500D发布不久，作为全球首推可拍视频单反相机的尼康自然不甘落伍，全新系列D5000发布，除了拥有D90一般的视频拍摄功能外，最引人瞩目的还是使用了翻转屏设计，为实时取景的拍摄方式带来了极大的便利。
