Excel身份证号验证：三种实用方法详解

创作时间:

作者:

@小白创作中心

Excel身份证号验证：三种实用方法详解

引用

来源

https://docs.pingcode.com/baike/4934522

在Excel中验证身份证号的真实性是一项常见的数据处理任务。本文将详细介绍三种主要方法：使用公式检查格式、使用数据验证限制输入、以及利用VBA宏进行高级验证。通过这些方法，您可以确保身份证号数据的准确性和有效性。

要在Excel中验证身份证号的真实性，可以使用公式、数据验证和宏等方法。以下是具体步骤：使用公式检查格式、使用数据验证限制输入、利用宏进行高级验证。其中，使用公式检查格式是最常用且高效的方法。通过公式，可以快速验证身份证号的长度和结构是否符合标准，确保数据的准确性和有效性。

一、使用公式检查身份证号的格式

在Excel中，我们可以通过自定义公式来检查身份证号的格式。这种方法主要用于验证身份证号是否符合基本要求，如长度、结构等。以下是具体步骤：

1、创建验证公式

在Excel中，可以使用以下公式来验证身份证号的长度和结构：

=IF(AND(LEN(A1)=18, ISNUMBER(VALUE(MID(A1, 1, 17))), OR(RIGHT(A1, 1)="X", ISNUMBER(VALUE(RIGHT(A1, 1))))), "有效", "无效")

上述公式解释：

LEN(A1)=18：身份证号的长度必须为18位。
ISNUMBER(VALUE(MID(A1, 1, 17)))：前17位必须是数字。
OR(RIGHT(A1, 1)="X", ISNUMBER(VALUE(RIGHT(A1, 1))))：最后一位可以是数字或字母“X”。

2、应用验证公式

将上述公式复制到需要验证的列旁边，然后拖动填充柄应用到所有需要验证的单元格。结果将显示“有效”或“无效”。

3、解释验证结果

通过观察公式的结果，可以快速判断出哪些身份证号是有效的，哪些是无效的。对于显示“无效”的身份证号，可以进一步检查并纠正错误。

二、使用数据验证限制输入

Excel的数据验证功能可以帮助我们限制用户输入符合特定规则的身份证号。这种方法可以在数据录入阶段就防止错误输入。

1、设置数据验证规则

在Excel中，选中需要验证的单元格或列，然后依次点击“数据”->“数据验证”->“数据验证”。在弹出的对话框中，设置以下规则：

允许：自定义
公式：
=AND(LEN(A1)=18, ISNUMBER(VALUE(MID(A1, 1, 17))), OR(RIGHT(A1, 1)="X", ISNUMBER(VALUE(RIGHT(A1, 1)))))

2、设置输入信息和出错警告

在“输入信息”标签中，可以设置提示信息，帮助用户输入正确的身份证号。在“出错警告”标签中，可以设置错误提示，阻止用户输入不符合规则的身份证号。

3、验证数据输入

设置完成后，用户在输入身份证号时，如果不符合规则，将会收到错误提示，防止错误数据的输入。

三、利用宏进行高级验证

对于更复杂的身份证号验证需求，可以使用VBA宏进行高级验证。宏可以实现更复杂的逻辑，如校验码验证、地区码验证等。

1、编写VBA宏

打开Excel的VBA编辑器（按Alt+F11），然后在“插入”菜单中选择“模块”来创建一个新的模块。在模块中输入以下代码：

Function CheckIDCard(IDCard As String) As Boolean  

    Dim i As Integer  
    Dim sum As Long  
    Dim checkCode As String  
    Dim weight As Variant  
    weight = Array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2)  
    checkCode = "10X98765432"  
    If Len(IDCard) <> 18 Then  
        CheckIDCard = False  
        Exit Function  
    End If  
    For i = 1 To 17  
        If Not IsNumeric(Mid(IDCard, i, 1)) Then  
            CheckIDCard = False  
            Exit Function  
        End If  
        sum = sum + CInt(Mid(IDCard, i, 1)) * weight(i - 1)  
    Next i  
    If Mid(checkCode, (sum Mod 11) + 1, 1) = Right(IDCard, 1) Then  
        CheckIDCard = True  
    Else  
        CheckIDCard = False  
    End If  
End Function