|
数据有效性是强制要求输入者在对特定单元格输入数据时需要遵守特定的规则，以防止数据输错，比如强制要求身份证号为18位、手机号为11位等，否则给出提示性警告并要求重新输入。其实数据有效性不但能够起到强制要求的作用，还能够提供下拉箭头便捷输入。下边我们一起来进行探讨。 1 数据有效性界面 先选中要设置数据有效性的单元格，在“数据”菜单中（以Excel2010版为例）找到“数据有效性”命令，我们可以看到它有4个标签：设置、输入信息、出错警告和输入法模式。其中“设置”是最重要的让数据有效性生效的内容，其余只是辅助，我们先把简单的、辅助标签内容介绍后，详细解说设置标签。
输入信息标签，一目了然，不用过多解释：
 出错警告标签，样式中有三种类型，内容也一目了然，标题是指出错后给出的对话框的名称，错误信息是指出错后给出的主要提示内容，一般与数据性有效的设置内容有关，主要告诉输入者允许输入那些内容，给输入者指明方向：
 输入法模式是指当选中该单元格时，输入法是开启的还是关闭的。
2 数据有效性设置
2.1 简单设置
如果未设置，则允许任何值。其中整数、小数、日期、时间和文本长度都有最大值和最小值，只允许在此数据范围内输入，易于理解，也没什么文章好做，最有意思的莫过于序列和自定义（默认勾选忽略空值）。
序列被勾选上后，会在忽略空值下方出现一个提供下拉箭头并默认勾选上，然后在来源里给了一个输入或单元格区域选择框，对于输入内容固定且种类不多的情况下，既可以提高输入效率，又可以防止非法或另类数据，提高准确率。
比如对需要输入性别的单元格可以设置数据有效性并选择序列，在来源里输入“男,女”，如下图：
 需要注意的是每项之间必须是英文状态的逗号，否则会被认为是一个整体。或者点击来源框内最右侧的区域选择按钮，拖选表格中已有的单元格将其中的值作为输入内容。下图就是该单元格被设置有效性后的简单效果：
 然后就可以点击选择输入了，是不是很方便呢？最后把设置好有效性的这个空单元格向下复制给同一列，该列的有效性就被设好了（当然也可以先选定区域再设定）。
2.2 使用公式进行高级设置
如果你对公式有一定的了解，可以选择自定义，使用公式设定更为复杂的数据有效性。
例1：设定C列自C8开始，要求其内容为“CA0000-000”格式的数据，且有以下要求：
1、必须以“CA、CB、CX、CY、CR、GC、GD”中的任一种组合开头，不允许有其他值出现；
2、字头两个字母必须为大写；
3、总长度为10位；
4、其中第7位为“-”作为分隔，“-”与字母间是数字，“-”后还是数字。
这个要求看起来有点变态啊！但这是我们衬衫类产品正确转化为17位商品条码的有力保障，如果不强行设定，谁知道输入者会不小心输成什么东西，也就更不知道生成的17位条码是什么东西了。
在起始的C8中进行数据有效性设定，选择自定义，然后在公式输入框里输入以下公式：
=AND(LEN(C8)=10,MOD(SEARCH(LEFT(C8,2),"CACBCXCYCRGCGD"),2)=1,CODE(LEFT(C8,1))>66,CODE(LEFT(C8,1))<72,CODE(MID(C8,2,1))>66,CODE(MID(C8,2,1))<90, MID(C8,7,1)="-" ,ISNUMBER(VALUE(MID(C8,3,4))),ISNUMBER(VALUE(RIGHT(C8,3))),ISNUMBER(SEARCH(" ",C8))=FALSE)
并在出错警告的错误信息里输入以下内容：
只能输入“CA0000-000”格式数据，总长度10位、字头为大写“CA、CB、CX、CY、CR、GC、GD”、字头后为4位数字、“-”做间隔符、末3位为数字、不允许有空格！
错误信息不用再解释，详细解释一下公式含义及写法：
上述4条（实际细分下来是6条）要求同时满足，故在公式标志符“=”后立即输入and，表示要求括号内的条件同时成立。
☆ LEN(C8)=10，表示C8有10个长度的字符；
☆ MOD(SEARCH(LEFT(C8,2),"CACBCXCYCRGCGD"),2)=1
其中LEFT(C8,2)表示C8左取两位，SEARCH(LEFT(C8,2),"CACBCXCYCRGCGD")表示C8左取两位后必须在"CACBCXCYCRGCGD"字符串里能找到（这个字符串根据自己需要设定），比如“CB”在这个字符串里能找到且是从第3位开始找到的。随之我们就会发现我们设定的允许值都是在奇数位要能找到的，如果偶数位出现的就不是我们想要的结果（如“AC、BC……”），所以我们再在外边套一层求余函数，以2作为除数，要求求余结果为1（偶数以2作为除数求余，结果为0），就能保证设置正确。
☆ 但经过测试我们发现上述限定无法制约小写，也就是“ca”等也被允许，在这里我们利用大小定字母在字符集中的值不同来对其进行限定。找一任意空白单元格，输入“=code("A")回车后我们会得到65，这就是“A”的code值，同样我们测试可得“Z”的code值为90，“a”的code值为97。
因此我们分别设定左取C8的第1位字母必须大于64，但进一步观察我们发现第1位字母从“C”开始，也就是大于66即可；而以“G”（code值71）结束，也就是小于72。同理可得，第2位字母（MID(C8,2,1)）的code值大于66，小于90，于是就得到了对字母大写的限定公式，即同时满足：CODE(LEFT(C8,1))>66,CODE(LEFT(C8,1))<72,CODE(MID(C8,2,1))>66,CODE(MID(C8,2,1))<90
☆ MID(C8,7,1)="-"，表示要求第7位是“-”
☆ ISNUMBER(VALUE(MID(C8,3,4)))表示将C8的值从第3位开始截取4位转换成数值后能变成数字，isnumber是对数据判定是否为数字的函数，结果为true或false。
☆ ISNUMBER(VALUE(RIGHT(C8,3)))，同理，表示右取C8的3位转换成数值后也能变成数字。
☆ ISNUMBER(SEARCH(" ",C8))=FALSE，表示在C8 里搜索空格（两个英文双引号间有一个英文状态下的空格）的结果为false，即没有空格。
然后把以上条件全部组合在and里对C8单元格进行数据有效性设定就OK了，然后复制到同列。
这个时候，如果输入者输入的数据不满足上述条件的任一条，都会被禁止输入并给出错误警告。
3 级联数据有效性 其实在上一节里，数据有效性已经基本解说完毕了，不过在具体工作中对于数据有效性在根据序列设置有效性时，可以和公式及定义区域结合形成更高级的有效性：类似于网页那样根据前边（或某一特定单元格）的内容允许不同区域的内容，例如，前边选择陕西，后边就只能选择输入陕西的地级市；如果前边选择的是四川，后边就只能选择输入四川的地级市……我们权且将其称为级联数据有效性，一起来试试。
例2：在工作需要根据不同区域公司名称输入不同的销售分店，要求分店必须准确（以方便转为分店代码进行其他操作），不能有错字或空格，所以最理想的方法便是提供下拉箭头选择输入。鉴于可以理解的原因，这个例子改为建立一个如下图所示的资料输入表，要求省可以下拉选择、市根据省下拉选择、区根据市下拉选择（象网页里输入那样）。
 3.1 建立辅助资料表
因为要求提供下拉箭头选择输入，故只能使用序列设置数据有效性。我们以陕西、四川两个省的资料为例进行设置。
新建一个资料表，到网上搜索两省的行政区划资料，整理为如下图的格式：
 同一行第1格为市名，同行其他格子为市下辖区/县，所有市的最顶端为省。现以陕西为例进行设置。（本文配套的Excel数据源文件，请到QQ群：488925627下载）
 3.2 批量定义名称
先拖选红线单元格区域，然后点公式菜单中的名称管理器中的根据所选内容创建命令，在弹出的对话框“以下列选定区域的值创建名称”中选择最左列并确定，即将红线中的最左列设定为右侧对应内容的名称；再拖选蓝线单元格区域，同样操作，不过要勾选首行创建名称。这样市既是区/县的名称（名称框输入市名即选定该市下辖所有区/县的单元格），又是省所包含的内容（名称框输入省即选定省下辖的所有市的单元格）。
对四川资料表同样操作。
3.3 设置级联数据有效性
回到需要设置数据有效性的表，先设定省的数据有效性。在省字段下的第一个空格（图5的C2单元格，下边用单元格名进行说明），点数据-数据有效性-设置：允许序列，来源里直接输入“陕西,四川”（不含双引号，以英文逗号间隔），确定。
再设定市的数据有效性，选择D2，点数据-数据有效性-设置：允许序列，来源里输入：=indirect(C2)，确定，提示错误不要理会，因为当前C2里还没有输入值，所以报错。使用序列设置数据有效性要么手工输入序列内容，要么引用单元格，除此外不接受别的内容，这里我们引用C2的值（省名），根据C2的值来变，但我们已将各省名定义为所辖市的单元格区域名，但如果直接输入省名，就只能选输入的内容；而indirect函数可以将定义的名称转为区域，符合Excel的规范。
设定区/县的数据有效性，选择E2，点数据-数据有效性-设置：允许序列，来源里输入：=indirect(D2)，确定，提示错误不要理会。
最后选择C2:E2向下拖选填充（选中单元格后，鼠标移至最右单元格的右下角，直至鼠标变成黑实心十字状，摁住左键向下拖动鼠标）至想要到达的行即完成设定（或直接选定C2:Ei<i为想要到达的行>，按Ctrl+d完成填充）。
完成后的效果：
   3.4 辅助资料的隐藏
如果你不想让输入者看到资料表，可将其深度隐藏（有别于普通隐藏，普通隐藏右击工作表名可隐藏，也可以取消隐藏）：按Alt+F11组合键调出Visual Basic界面，在VBA窗口的资源管理器中选定资料工作表，在属性窗口中将Visible值选为2，就深度隐藏了，右击工作表名也无法取消隐藏。
 你又要说了，那别人也可以在VBA中改回去（查看），好吧，那你可以给VBA再加个密码：按Alt+F11调出VBA编辑器后点VBA窗口中的工具菜单-VBAproject属性，选保护标签，输入密码并确认，就对这个工作簿的VBA进行加密保护了。
 |