[北-基础技巧]数据有效性

bbwsj

数据有效性是EXCEL中使用频率较高的技巧之一
下来，我们一起学习一下“数据有效性”的各种用法：
技巧1  在单元格创建下拉列表
   有许多新手在EXCEL中第一次见到下图所示的下拉列表时，都以为是程序做的，当他们知道图中下拉列表只是一个普通的利用数据有效性完成的EXCEL技巧时，他们会觉得很惊奇。
   那么，现在我们一起学习一下，怎么利用数据有效性来做个下拉列表吧：


第一步  在一个连续的单元格区域输入列表中的项目,如图中E7:E11有个商品名称的表

第二步  选中A2单元格，单击“菜单”——“数据”——“有效性”,在“数据有效性”对话框的"设置"选项卡中,在“允许”下拉列表中选择“序列”
项.
第三步  在"来源"框中输入“=$E$8:$E$11”（或输入“=”号后，用鼠标选中E8:E11）
第四步  勾选"忽略空值"与"提供下拉箭头"复选框,如图所示

第五步  单击"确定"按钮,关闭"数据有效性"对话框.  这样,就能实现第一张图所示的效果了。
  
如果列表的内容较少,或者不方便在工作表中输入列表项目,也可以省略上述的第一步,然后将第三步的操作
改为:直接在"来源"框中输入列表内容,项目之间以半角的逗号分隔.如图所示


  在一般情况下,数据的有效性中的序列来源,只能引用当前工作表中的单元格区域。
　如果希望能够引用其他工作表中的单元格区域,则必须先为单元格区域定义名称,然后在"来源"框中输入名称.
　例如,将另一张工作表中的A2:A10区域，名称定义为“SPMC”,然后在“数据有效性”的“来源”框中输入“=SPMC”。
　（注：名称定义的方法，http://www.excelpx.com论坛有帖子，如不会请搜索一下，这里就不多加叙述了。）

bbwsj

技巧2  另类的批注
   当我们需要对表格中的项目进行特别说明时,常常会使用EXCEL的批注功能。给单元格做批注的方法，http://www.excelpx.com论坛的帖子，这里不
多浪费时间。
  使用批注多了，我们会发现EXCEL的批注也有不足之处:
　一、批注框的大小尺寸会受到单元格行高变化的影响;
　二、批注框的默认情况下，是只显示标识符。必须把光标悬停在单元格的上方批注内容才会显示出来，否则即使当单元格处于活动状态时，它也不会显示;
　三、是在上面2种情况的共同作用下，加上拆分（冻结）窗口下的插入、拖曳等工作表操作，会导致批注的位置远离原来的单元格，而被主人遗忘，并随着主人对单元格的复制或格式刷操作而被大量复制，这也是造成文件增肥的主要原因之一。我曾经为一个会员给他的文件减肥时，从表里找出3500多个远离母单元格的批注弃儿，最终我通过删除这些个“批注弃儿”，帮那个会员给文件容量缩减了2/3之多。
　言归正传，说说数据有效性
  　利用数据有效性功能，我们可以实现另类的批注效果，克服以上不足。
第一步：选定单元格，如C1。
第二步：单击菜单"数据"-"有效性",在"数据有效性"对话框的"输入信息"选项卡中,勾选"选定单元格时显示
输入信息"复选框,并在"标题"和"输入信息"文本框中输入相应内容.如图所示


第三步:单击"确定"按钮,关闭"数据有效性"对话框.
   设置完毕后,当此单元格处于活动状态时,就会显示刚才输入的内容,效果如图所示

  用鼠标左键按住上图中的提示框,可以将它移动到工作表中的任何位置.更重要的是数据有效性不管你对多少单元格设置，设置的内容有多少变化，它都只会产生一个提示框。只是选中不同的设置的单元格，显示不同的内容，不会如批注一样产生大量的对象增加文件容量。
　当然数据有效性做的批注也有它的缺点，格式单调，输入的字符有限，也欠美观性等等。

bbwsj

技巧三  结合函数完成可切换的动态下拉列表
  在通常情况下,当用户使用数据有效性时,只能处理一组数据来源。而在某些场合中,数据来源不止一组,如果能有某种方法让下拉列表根据给定的条件来展现就好了。
　现在让函数帮助数据有效性完成这可切换的动态下拉列表吧：
  如图所示，E1:G7 有3列分别为“英文”、“数字”、“中文”的3列字段

现在要根据A1单元格的值，让A3单元格的下拉列表分别展现不同的下拉列表
第一步：选定A3单元格，单击菜单“插入”——“名称”——“定义”,在“定义名称”对话框中如图所示，写入名称“SPMC”，引用位置输入公式
“=CHOOSE(MATCH($A$1,$E$1:$G$1,),$E$2:$E$7,$F$2:$F$5,$G$2:$G$4)”，单击“确定”。


第二步：选定A3单元格，单击菜单“数据”——“有效性”，在“数据有效性”对话框的“设置”选项卡中,在“允许”下拉列表框中选择“序列”；
第三步：在“来源”框中输入:“=SPMC”；
第四步：勾选“忽略空值”与“提供下拉箭头”复选框，如图所示，单击“确定”按钮。


    现在当用户在A1单元格中分别输入“英文”、“数字”、“中文”时,A3的下拉列表框中会出现不同的展现不同的下拉列表，分别对应不同的数据



  从本实例可以看出,当设置数据有效性时,序列来源不仅可以指定一个单元格区域.使用名称来引用单元格区域,还可以使用公式来完成更复杂的任务,　上述公式利用了CHOOSE函数和MATCH函数,根据A1单元格的值来动态引用数据源。
　此外还有许多函数可以和数据有效性结合使用，这里就不一一细述了。对CHOOSE函数和MATCH函数的运用如有不理解，请到http://www.excelpx.com论坛搜索相关帖子。

bbwsj

技巧四、　动态的数据有效性
  在日常工作中，数据有效性的数据源往往是不断增加的内容的单元格区域。就希望在数据有效性中设置的来源能自动同步增加这些新补充的内容，需要动态的数据有效性
　如图，要在单元格A2建立动态的下拉列表，让其下拉数据根据C列的增加而增加

　建立动态的数据有效性下拉列表有两种方法：
　1、列表法
　第一步：选中C1单元格，“菜单”——“数据”——“列表”——“创建列表”（或选中C1单元格，CTRL+L），进入创建列表窗，查看区域是否正确，勾选列表有标题后，按确定。列表创建完成。
　第二步，选中列表区域，在名称框输入“SPMC”,(或选中区域，菜单——插入——名称——定义，在定义名称框里输入名称名“SPMC”),完成给列表定义名称的步骤。
　而后重复本贴技巧三的第二、三、四步。
　2、函数法
　方法如同本贴技巧三的操作，不同是函数的公式不同，名称SMPC，引用位置输入公式是“=OFFSET($C$1,,,COUNTA($C:$C))”
  事实上，动态引用数据源的诀窍就是在数据有效性的来源设置中使用动态名称，有动态名称完成自动适应内容增长的各种变化。
　
　北觉得列表法建立的动态数据源，简便易学，建表者不需要有函数基础。但只能增加数据源，如果数据减少了，就会在下拉列表里增加很多空项目。
　而函数法建立的动态数据源，基本随内容的增减而增减，不会因数据删减了，就增加空项目。但建表者需要有一定的函数功力。

bbwsj

技巧五、　限定录入数据必须符合特定条件
  在许多场合中，为规范用户的录入，保证数据的严谨和可读性，需要限定录入数据必须符合特定条件，此时就又需要用到数据有效性了。
  如果数据的限定比较简单，比如：必须是某个区间的整数或小数；必须是某个时间段等，可以在"数据有效性"对话框中"设置"选项卡里
面通过选择不同的有效性允许条件，并配合相应的参数来实现、
但对于数据特征较为复杂的，就必须使用"自定义"条件，通过编写公式来实现

  例如，我们经常碰到人员录入，就需要录入身份证号，大家都知道我国的身份证号分老版15位，新版18位两种，另两个人的身份证号是不可能发生重复的。这就需要我们在设计表格时把这样特定限定条件放进去，避免这样明显的录入差错。
　我们现在一起来做个限定A2：A100区域的身份证不重复录入的数据有效性吧
第一步：选中A2：A100区域。
第二步：单击菜单"数据"-"有效性",在"数据有效性"对话框的"设置"选项卡中，在允许下拉列表框中选择"自定义"项。
第三步：在"公式"框中输入：=(SUMPRODUCT(--(A$1:A2=A2))=1)*(OR(LEN(A2)=15,LEN(A2)=18))
第四步：勾选"忽略空值"复选框，单击"确定"按钮，关闭"数据有效性"对话框。
  来现在，我们试试，输入正常的不重复身份证号，是否能正常录入？
  再分别：输入重复身份证号；位数不是15或18位的身份证号，是否会象下图所示一样被EXCEL拒绝？



注：在此要提醒大家，利用数据有效性来限定录入数据必须符合特定条件，是一种事前控制，它只对将要录入的数据起作用。如果单元格在设置数据有效性以前就录入了数据，或是批量复制的数据，则不会受到数据有效性的检查。此时，在设计表格时就需要配合条件格式功能，让条件格式帮你做后期检查提醒工作。

bbwsj

技巧六、 杜绝负数库存
    在一般情况下，用户在处理销售出库时需要依据库存余额来确定最大出库量，以避免产生出库数量大于库存数量，出现负数库存的后果。
　因此，在大多数的企业管理系统中，开具出库单时，填写的数量都会被限制为必须小于等于库存量。如果用户用EXCEL来管理库存与出入库业务，也可以利用数据有效性的特性，轻松实现这样的控制。
    以如图所示的工作薄中，有两张工作表，库存表记录着所有产品的库存余额，出库表用来填写每次的实际出库数量，（说明一下，这个表，只是我随手打的表，并非实际表样，仅为说

  下面我们针对销售表的F3:F7单元格设置数据有效性，实现禁止输入大于库存数量的实际出库数量的效果。
第一步：将"库存"工作表中的A3:B7定义名称为"KC"。
第二步:在"出库"工作表中，选定区域F3:F7单元格.
第三步：单击菜单"数据"-"有效性"，在"数据有效性"对话框的"设置"选项卡中，在"允许"下拉列表框中选择"自定义"项。
第四步:在"公式"框中输入：=F3<=VLOOKUP(D3,KC,2,0)
第五步：勾选"忽略空值"复选框，单击"确定"按钮，关闭"数据有效性"对话框。
如图，我在实际出库中输入了超过库存量的出库量，给EXCEL拒绝。


说明一下，以上方法只适用于进出发生少、品种少的企业。真正的企业用数据有效性是根本负担不了大数据量的。在答疑群或论坛提问中，常常看到有人希望用纯函数加技巧，来处理库存帐务工作。北个人觉得这个不很现实，库存帐还是交给VBA吧。
这里用库存帐举例，只是说明一下，数据有效性可以这么用而已。

bbwsj

技巧七、　强制序时录入
  我们在输入数据时，经常需要录入流水单证，流水单证的特定是序时性，即新录入数据的日期绝不能早于已有记录的最大日期。利用EXCEL的数据有效性可以轻松地完成这样的限制。
以在A2:A100单元格区域内需要输入流水单证的时间为例,有两个方法可以实现。
方法一
确定。
第一步：选定区域A2:A100.
第二步：单击菜单"数据"-"有效性"，在"数据有效性"对话框的"设置"选项卡中，在"允许"下拉列表框中选择"自定义"项。
第三步：在"公式"框中输入:=N(A2)>=N(A1)
第四步：勾选"忽略空值"复选框，单击"确定"按钮，关闭"数据有效性"对话框。
方法二:
第一步：选定区域A2:A100.
第二步：单击菜单"数据"-"有效性"，在"数据有效性"对话框的"设置"选项卡中，在"允许"下拉列表框中选择"日期"项。
第三步：在"数据"下拉列表框中选择"大于或等于"，在"开始日期"框中输入：=MAX($A$2:$A2)
第四步：勾选"忽略空值"复选框，如图所示。单击"确定"按钮，关闭"数据有效性"对话框。

bbwsj

技巧八、 多极选择录入
   在实际工作中，经常需要用到多级下拉列表，即先选择录入某大项，然后选择录入属于此大项的小项，再选择小项的子项。这是一项非常实用的功能，如财务工作经常碰到的一级科目、二级科目；地区输入经常碰到省、市、区；销售、仓管经常碰到货品大类、子类、单件货品的细分......
   在EXCEL表格设计时，有多级下拉列表，能够有效避免串项和项目混乱。在EXCEL中，一般利用VBA来处理多级下拉列表，但其实利用数据有效性配合自定义名称好函数公式，也能够实现两级和两级以上的选择性录入效果。
  一般用OFFSET函数+MATCH函数+counta函数来完成多级菜单对数据源的 查找、定位、定项的要求。http://www.excelpx.com论坛此类的帖子已经够多了，我就不再此细说了

bbwsj

技巧九、　输入违反数据有效性数据时的选择
　　有时候，我需要设定数据有效性来规范我的录入，但我又希望EXCEL这是提醒，我输入错误，而不是一棒子打死，全部不能录入。我能否有选择的机会尼？
　　EXCEL的数据有效性，给了我们这种选择的功能。
　　在设置数据有效性时，我们进入“数据有效性”，有个“出错警告”选项卡，在里面的样式里，我们可以选择“停止”，“警告”、“信息”，三种选项。

　　“停止”选项即我们平时用的最多的，出错了一律不能录入；
　　“警告”选项，录入出错时，给你选择是重新录入还是以错误值完成录入或不作为；
　　“信息”选项，录入出错时，给你提示，你可以选择以错误值完成录入或不作为。
　　你也可以在录入出错时的提示输入你自己的语言，来帮助更好的完成录入工作。

bbwsj

只是因为小影子的一个帖子，引发了我洋洋洒洒的写了N多，也盖了幢大楼。
虽然说了很多内容，但对于数据有效性而言只说到了冰山一角。
很多东西，需要大家在实践中体会、挖掘、发现。
大家从上面几楼里可以看见数据有效性并不是完全独立的，要把它用的精妙，所涉及的知识面很多，很杂。
希望大家能一起沉下去体会、学习，并希望把自己的心得经验好他人一起分享
上述的认识可能有认识错误，期盼大家的指正。
多占一楼，等以后想到什么再补充了