<返回更多

Excel数据校验有办法

2019-06-20    
加入收藏

白领在做Excel表格时,最容易出现的错误,就是数据输入错误。可人不是机器,尤其是面对枯燥,且没有什么意义的数据时,输入错误、缺位或多位,是最容易出现的问题。那么,如何减少输入错误的问题呢?对数据进行校验是个不错的办法。

使用数据有效性进行校验

其实,很多数据本身就有天然的限制,身高13米、体重650kg、年龄200岁,这样的人是不可能存在的,因此在Excel表格中输入数据时,加入一些限制条件,就可以将不少输入错误,如常见的多输入一位或少输入一位等排出在外。

减少错误,Excel数据校验有办法

图1


减少错误,Excel数据校验有办法

图2

其实Excel已经为用户准备了数据有效性的校验方式,只要打开“菜单→数据→数据有效性”,就可以设置对输入数据进行有效性验证(图1)。用户可以根据需求,在整数、小数、序列、日期、时间、文本长度和自定义中,设置不同的校验条件,如输入数据的范围,数据要小于或大于某个特定数据(图2),或者是不能出现某些数据等。同时还可以在出错警示中,设定输入超范后的提示信息,在合理设置后,就可以在很大程度上避免少输入小数点,或多/少输入一位等常见的输入错误。

自定义校验

当然,现成的校验条件,在很多时候是无法满足需求的,因此Excel还提供了自定义校验选项。在输入数据时,用户常犯的一个错误就是看错行,这样会在两行输入一样的数据,同时一些数据是有唯一性的,如身份证、工号、驾驶证号等,这样用户就可以通过验证这一数据在这一列中是否是唯一的,以避免输入错误和重复输入,其方法同样是打开“菜单→数据→数据有效性”,选择自定义数据(图3)。随后,在公式中输入“=COUNTIF(D:D,D1)=1”(其中,D为当前列,D1为当前单元格),点击确定后,拖动单元格下方的小点,让这一公式在所在列中应用。

减少错误,Excel数据校验有办法

图3

同时,在自定义公式中,还可使用多条件设置,如在身份证的数据验证中,输入的公式为“=AND COUNTIF(D:D,D1)=1,OR LEN((D1=15), LEN(D1=18))”(其中,D为当前列,D1为当前单元格)时,除了可以校验数据的唯一性,还可对数据的位长进行检测,如数据不是15位(对应老版身份证)和18位,则说明数据错误。

身份证校验

像身份证这种一大串无意义的数字,输入起来是很容易出错的。更糟糕的是,还有一些人会出于各种目的,随便报出一串数字作为身份证号码,那么如何避免这样的问题呢?

实际上,我们的身份证号码本身就自带校验机制,其最后一位是校验码,而这一校验码是通过将身份证号码前17位数分别乘以不同的系数:从第一位到第十七位的系数分别为:7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 。随后,将这17位数字和系数相乘的结果相加后再除以11,看余数是多少;由于余数只可能有0 1 2 3 4 5 6 7 8 9 10这11个数字。此时,再做一个变换,分别对应的最后一位身份证的号码为1 0 X 9 8 7 6 5 4 3 2,知道这一原理,我们只要在Excel中,使用函数来代替这一计算,就可以进行简单的校验。这里直接给出公式“=IF(RIGHT(A1)=TEXT(CHOOSE(MOD(SUMPRODUCT((--MID(A1,COLUMN(A1:Q1),1))*{7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2}),11)+1,1,0,"X",9,8,7,6,5,4,3,2),0),"校验正确","校验错误")”,其中,A1为身份证输入的单元格,此时不能通过Excel的数据有效性校验来实现,而是要新开一列,输入这一公式后,再下拉,让这一公式在所在列中应用,当用户输入的身份证号码符合正确编码规则时,此列会显示验证正确,而一旦不符合这一规则,就会显示验证错误。

这样就可以在相当程度上,避免输入错误,还可以消除假身份证号码的影响。当然,这样的校验还比较粗略,对于“精致”的假身份证号码无效,当要求较高时,好需要通过其他手段进行验证。
 

声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多资讯 >>>