让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）。

编号前缀正确性自动检查

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

假如三个地区的产品编号前缀分别为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这样的分数。系统也不会认为你错，这时可以用如下方法限定条件。

假设D2是数据输入的起始单元格，可以单击“数据”菜单下的“有效性”命令，打开对话框的“设置”选项卡。在“允许”下拉列表中选择“自定义”，然后在“公式”框内输入“=AND（D2<=100，OR（LEN（D2）=1，LEN（D2）=2，LEN（D2）=3））”。

该公式中的LEN函数检查输入D列D2、D3等单元格的数据长度是否等于1、2或3，只要满足其中任意一个条件，OR函数就会返回TRUE。而“D2<=100”检查输入的数据是否小于等于100，当上面两者同时满足时AND函数返回TRUE，Excel允许用户完成输入。如果单元格中的数据长度不等于1、2或3，或者输入的数据大于100，AND函数就会返回FALSE，Excel会制止用户输入数据。

自动判断无效记录并制止输入

对工资表之类的工作表来说，后边一列数据的存在价值与主要关键字有密切关系。例如，如果一条含有“房补”“奖金”的记录里面没有“工号”和“姓名”，“房补”“奖金”就变得没有意义。我们可以让工作表对这类记录自动作出判断。

假设工作表B列存储“工号”、C列存储“姓名”，当B列中的某一单元格为空值时，其右边的单元格不允许输入“姓名”。利用Excel可以采取如下措施加以制止。

打开“数据有效性”对话框的“设置”选项卡，在“允许”下拉列表中选择“自定义”，然后在“公式”框内输入“=COUNTA（B2）=1”。确定以后如果B列中的某一单元格为空，那么它右边的单元格就不能输入数据，即使输入了数据Excel也会出面加以制止，直到在B列输入有效数据才能操作。

输入顺序错误的自动提醒

某些情况下，我们对数据录入的顺序有严格的要求，比如按日期排列处理，前面的任务没有处理完，不允许后面的任务，必须按照由小到大的顺序录入，一旦出现输入违规应当立即停止输入。如何实现？

选中将要录入数据的区域或列（如D列），再按上面介绍的方法打开对话框。在“允许”下拉列表中选择“日期”，在“数据”下拉列表中选择“大于或等于”，最后在“开始日期”框内输入“=MAX（$D$1：$D1）”（图3）。

关闭对话框后，如果从D1单元格录入的数据是“升序”的，Excel就不会警示，否则就会提示操作非法。

如果日期必须按由大到小的顺序录入，只需在“数据”下拉列表中选择“小于或等于”，在“开始日期”框内输入“==MINA（$D$1：$D1）”（“$D$1”是第一个数据所在的单元格）就可以了（图4）。

重复数据带颜色自动提醒

有时候，同一项目的输入中如果出现重复的数据，可能预示着输入有错误。

比如，正在输入的领奖人姓名上面已经输入过了，这时可以让系统用红色显示来提醒输入者判断。比如，C列出现数据重复，则以红色提醒用户注意。

实现方法为：单击“格式”菜单下的“条件格式”打开对话框，在“条件1”下拉菜单中选择“公式”，在对话框内输入公式“=COUNTIF（C：C，C2）<>1”（图5）。然后单击“格式”按钮打开对话框，在其中选择一种合适的“提醒”方式（例如选择红色字体）。此后只要COUNTIF函数在C列中发现与当前单元格相同的数据，即“=COUNTIF（C：C，C2）<>1”成立（为“真”），则当前单元格中的数据就会自动以红色字体显示。