让Excel帮你自动审查数据

输入数据的时候，如果Excel能提醒你对错，那要比等汇总出错了后再返回头找错误要省事得多。这要靠给Excel添加自动审查。许多设计好的Excel工作表往往不止是供给一个人操作和录入，由于使用者对表内单元格要求输入的内容熟悉程度不同，往往难免发生这样那样的输入差错。默认情况下Excel系统对用户输入的数据不具备对错识别功能。如果我们能给Excel装上一双“火眼金睛”，让它自动识别用户输入的数据的合法性，错误的发生就会降低到最低程度。本文将通过一些实际的操作案例讲述自动审查数据的实现方法。

身份证位数输入的检查

身份证输入在Excel表格处理中常常遇到，而且也很容易出错。实际工作中我们常常见到某个人的身份证少了一位或多了一位的情况，有时很难判断哪一位出了问题。如果在输入时系统能提醒，这样的错误一定不会发生。

身份证号码只能是15位和18位，我们可以通过设置让Excel具备这方面的审核能力。

假如Excel工作表B列录入的是身份证号码，B2是输入身份证号码的起始单元格。可以将该列全部选中，单击“数据”菜单下的“有效性”命令。打开“设置”选项卡（图1），在“允许”下拉列表中选择“自定义”，然后在“公式”框中输入“=AND(COUNTIF(B:B,B2)=1,OR(LEN(B2)=15,LEN(B2)=18))”。该公式检查B列中与当前数据完全相同的单元格个数，如果返回的结果等于1，则“=COUNTIF(B:B,B2)=1”返回结果TRUE，否则返回结果FALSE。接下来“=OR(LEN(B2)=15,LEN(B2)=18)”中的LEN函数检查输入B列B2、B3等单元格的数据长度是否等于15或18，只要三个LEN函数中有一个满足条件，OR函数就会返回TRUE。当COUNTIF和OR函数同时返回TRUE时，AND函数才能返回TRUE，Excel允许用户输入数据。如果单元格中的数据发生了重复，或者输入的长度不等于15或18，AND函数就会返回FALSE，Excel立刻予以制止（图2）。





小提示：从这里例子我们可以触类旁通，如果输入B列的是一个其他固定长度的数据（如8位），那么，只需将上述公式修改为“=AND(COUNTIF(B:B,B2)=1,LEN(B2)=8))”就可以了。

编号前缀正确性自动检查

有时我们会对来自不同地区的人群或产品信息赋予不同的地区号前缀。为了避免录入这种类型的数据时候发生输入不在编号范围内数据的差错，可以设置对数据的前缀进行自动检查检查。

假如三个地区的产品编号前缀分别为012xxx、017xxx和019xxx，可以按上面介绍的方法选中数据所在的列（假设F1是输入数据的起始单元格），然后打开“数据有效性”对话框的“设置”选项卡。在“允许”下拉列表中选择“自定义”，然后在“公式”框中输入“=OR(LEFT(F1,3)="012",LEFT(F1,3)="017",LEFT(F1,3)="019")”。

上述功能设置完毕以后，只要录入的考号前缀不是012、017或019，立即出示警示提示。

上述公式的工作原理是这样的：LEFT从当前单元格（例如F1）中取出三个字符，看它们是否等于012、017或019，只要三个LEFT函数关系中有一个成立，OR函数就会返回TRUE，即允许用户输入，否则立即提示超出范围。

如果输入更多数量的数据前缀，只须在公式中增加类似“LEFT(F1,3)="012"”这样的语句就可以了。

限定在有效范围内输入数据

录入的数据一般都有一个合理范围，但工作表并不知道数据是否超限。例如，在录入成绩时，要求只能输入规定长度的数据，且大小不能超过一定范围。如限定只能录入整数，且小于等于100。但你录入213这样的分数系统也不会认为你错，这时可以用如下方法限定条件。