通过excel计算身份证号校验位

自从2004年1月1日《中华人民共和国居民身份证法》施行开始，居民身份证号码由15位升为18位，其中年份由2位扩展位4位，同时增加最后一位作为校验位。

在人事管理的实际操作过程中，曾经遇到过持假身份证入职的情况，当时我正在企业里牵头EHR系统实施，员工电子档案还只是通过Excel管理，所以只能通过Excel来校验身份证号码的真伪。

• 校验位是根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定，前面十七位数字码，按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。

计算过程如下：

1. 身份证第 1-17 位分别乘以对应的权重因子

2. 将乘积之和取模 11（即取除以11后的余数），根据下表得到对应的第 18 位校验码

其中的X，实际上是罗马数字中的10，即Ⅹ

在不同的字体里，两者可能会存在非常相似的情况，但在Excel中，用code函数可以很清楚的看出两者之间的差别。

在实际工作中，为了输入方便，往往也是直接输入大写英文字母“X”。

通过Excel数组公式求身份证号校验位：

=MID("10X98765432",MOD(SUM(MID(A1,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)

Excel中的数组公式需要使用SHIFT+CTRL+ENTER键入

通过VBA自定义函数处理身份证号校验位：

Function ID(num)
    Dim X(17)  '储存身份证号码分割后的每位字符
    Dim Y()  '储存计算相乘的系数
    Dim LastNum()  '储存身份证最后一位验证码    
    '储存身份证前17位
    For i = 0 To 16
        X(i) = Mid(num, i + 1, 1)
    Next   
    '如果第18位是字母X或x，将数字10储存到X(17)中
    '如果第18位是数字，直接储存到X(17)中
    '如果是其他情况，什么都不做
    If Mid(num, 18, 1) = "X" Or Mid(num, 18, 1) = "x" Then
        X(17) = 10
    ElseIf IsNumeric(Mid(num, 18, 1)) Then
        X(17) = Mid(num, 18, 1)
    End If    
    'Y中最后一位11是取模的除数，其余是相乘系数
    Y = Array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 11)
    LastNum = Array(1, 0, 10, 9, 8, 7, 6, 5, 4, 3, 2)    
    '判断位数，18位且前17位都是数字才可能正确，再计算校验码判断
    If Len(num) = 18 And Application.WorksheetFunction.IsNumber(Mid(num, 1, 17)) Then
        Sum = 0
        For i = 0 To 16
            Sum = Sum + X(i) * Y(i)
        Next
        Code = Sum Mod Y(17)
        If LastNum(Code) - X(17) = 0 Then
            ID = "正确"
        Else
            ID = "请检查身份证号码！"
        End If    
    '非18位情况身份证号码错误
    Else
        ID = "请检查身份证号码！"
    End If        
End Function

以下是经优化后的VBA代码：

Function ID(num)
Dim X(0 To 16) As String '储存身份证号码分割后的每位字符
Dim Y() As Integer '储存计算相乘的系数
Dim LastNum() As Integer '储存身份证最后一位验证码
Dim i As Integer
Dim Sum As Long
Dim Code As Integer

'将字符串转成字符数组并储存
X = Split(Left(num, 17), "")

'如果第18位是字母X或x，将数字10储存到X(17)中
'如果第18位是数字，直接储存到X(17)中
If Mid(num, 18, 1) Like "[0-9]" Then
    X(17) = Mid(num, 18, 1)
ElseIf LCase(Mid(num, 18, 1)) = "x" Then
    X(17) = "10"
End If

'Y中最后一位11是取模的除数，其余是相乘系数
Y = Array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 11)
LastNum = Array(1, 0, 10, 9, 8, 7, 6, 5, 4, 3, 2)

'判断位数，18位且前17位都是数字才可能正确，再计算校验码判断
If Len(num) = 18 And IsNumeric(Left(num, 17)) Then
    Sum = 0
    For i = 0 To 16
        Sum = Sum + X(i) * Y(i)
    Next
    Code = Sum Mod 11
    If LastNum(Code) = CInt(X(17)) Then
        ID = "正确"
    Else
        ID = "请检查身份证号码！"
    End If
'非18位情况身份证号码错误
Else
    ID = "请检查身份证号码！"
End If
End Function

优化说明：

将变量声明放置在函数开始处，提高代码的可读性；
将字符分割为字符数组，简化代码；
使用Like运算符代替Or，简化代码；
将字符转换为数值类型，提高代码执行效率；
简化校验码的计算过程；
若校验码计算错误，提示后缀不必添加“位身份证号码”。