判断身份证号码输入的有效性 在使用Excel工作表输入身份证号码时经常要判断输入数据是否有误,如位数是否18位,前17位是否都是数字,末尾(校验码)是否正确等。以下给出一个解决方案,除了不能判断户口所在地区外,基本可以判断身份证号码输入的有效性。对于15位的老号码,使用时先在第7位插入“19”,末尾随意加上一位数,凑足18位,程序会计算出正确的校验码。 假设在工作表的第5列输入身份证号码, 首先在这个工作表workseet里建立输入事件处理过程: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 5 Then Call ves(Target.Value, Target) End Sub ’ Target是输入数据的单元格对象 调用的ves( )过程可以放在共有模块里,参数adds是输入数据的单元格,sfz是输入单元格的内容。 Sub ves(sfz As String, adds As Range) If sfz = "" Then End If Len(sfz) <> 18 Then MsgBox "请输入18位身份证号码!" adds.Value = "" End End If Dim i, w, s, cs As Integer Dim v, vsm As String w = 1: s = 0: v = Right(sfz, 1) ‘初始变量 For i = 1 To 17 w = (w * 2) Mod 11 cs = Asc(Mid(sfz, 18 - i, 1)) - 48 If cs >= 0 And cs < 10 Then ‘如果是数字 s = s + cs * w Else MsgBox str(18 - i) + "位不是数字!" adds.Value = "" i = 20 ‘提前结束程序 End If Next If i = 18 Then ‘17位检查结束,开始计算校验码 s = (12 - s Mod 11) Mod 11 vsm = LTrim(str(s)) If s = 10 Then vsm = "X" If v <> vsm Then MsgBox "校验码不正确!应当是:" + vsm: adds.Value = "" End If End Sub 说明: 中国公民的18位身份证号码每一位都有代表的意义,前6位是户口所在地区(县),接着8位是出生日期,第15、16、17是同地同日出生的序号,第17位单数为男性,偶数为女性,最后一位是校验码,网上很多文章介绍校验码的计算,计算都很复杂,在此就不多说。 其实算法是这样:把前17位的每一个数字和一串加权因子相乘,计算这些乘积的和(S)。加权因子是自右向左,分别是2,22,23,……217,大于10的就模11。在二进制里乘2,就相当于其ASII码向左移一位,这样在和S里每个数字的信息都单独有一位。 这些乘积的和(S),再模11得到的数字0、1、2、3、4、5、6、7、8、9、10 分别对应1、0、X、9、8、7、6、5、4、3、2,作为校验码。这相当与用12减这些数字再模11。如果是10则用X表示。本文是用vba写出的计算和校验过程。
补记: 如果不使用VBA编程,可以使用名称和公式来计算校验码: 先定义3个名称 Xi ={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17} Wi={7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2}, Vs={1,0,X,9,8,7,6,5,4,3,2} 则校验码=Index(vs,mod(sumproduct(mid(E2,xi,1)*wi),11)+1) 其中E2是18位身份证号码。这应当是最简练的公式了。 选中E2:E65536(输入身份证号码的列),自定义有效性: =text( Index(vs,mod(sumproduct(mid(E2,xi,1)*wi),11)+1),"0")=RIGHT(E2) 出错警告中写上:“请检查号码输入是否18位,如果确认位数正确,请检查第18位校验码是否正确。”即可完成输入有效性设置 |