Excel如何验证身份证号合法性与真伪

在Excel中精准验证身份证号的合法性与真伪并非易事，但本文系统提供了三种高效实用的方法：无需编程的纯公式法（兼容性强、支持批量处理）、功能强大的VBA自定义函数IDCheck（可逐项校验位数、格式、出生日期、行政区划及核心校验码），以及前置防控型的数据验证方案（在录入阶段即拦截明显非法输入）。无论您是财务人员核对员工信息、HR批量导入人事数据，还是政务工作者处理居民档案，这些方法都能显著提升数据准确性与工作效率，尤其推荐结合使用公式校验与VBA函数，实现从录入到复核的全流程可信保障。

如果您在Excel中需要验证身份证号码的合法性与真实性，需同时校验位数、格式、出生日期有效性、行政区划代码是否存在以及最后一位校验码是否正确。以下是多种实现方法：

一、使用公式法（纯函数组合）

该方法不依赖VBA，通过嵌套TEXT、MID、SUMPRODUCT等函数完成18位身份证的完整性与校验码验证。适用于Excel 2013及以上版本，支持批量校验。

1、确保身份证号为文本格式：选中数据列 → 右键“设置单元格格式” → 选择“文本”，或在输入前加英文单引号（'）。

2、在空白列输入以下公式（假设身份证号在A2单元格）：

=IF(LEN(A2)=18,IF(AND(ISNUMBER(--MID(A2,1,17)),MID(A2,18,1)=CHOOSE(MOD(SUMPRODUCT({7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2},--MID(A2,ROW(INDIRECT("1:17")),1)),11)+1,"1","0","X","9","8","7","6","5","4","3","2"),"是"),"否"),IF(LEN(A2)=15,IF(AND(ISNUMBER(--MID(A2,1,15)),--MID(A2,7,4)>=1900,--MID(A2,7,4)

3、按Ctrl+Shift+Enter（若为旧版Excel数组公式）或直接回车（新版自动识别动态数组）。

4、公式返回“是”表示通过基础校验，“否”表示格式或校验码错误；注意：此公式不校验行政区划代码真伪及出生日期逻辑（如2月30日）。

二、使用自定义名称+辅助列分步校验

将复杂校验拆解为多个辅助列，提升可读性与调试能力，便于定位具体哪一项失败。

1、在B2输入：=LEN(A2)，判断长度是否为15或18。

2、在C2输入：=IF(B2=18,--MID(A2,7,8),IF(B2=15,--("19"&MID(A2,7,6)),0))，提取8位出生日期数值。

3、在D2输入：=IF(OR(B215,B218),0,IF(C2=0,0,IF(DATE(YEAR(C2),MONTH(C2),DAY(C2))=C2,1,0)))，验证日期是否真实存在。

4、在E2输入：=IF(B2=18,UPPER(MID(A2,18,1)),IF(B2=15,UPPER(RIGHT(A2,1)),""))，统一提取末位字符。

5、在F2输入：=IF(B2=18,IF(E2=CHOOSE(MOD(SUMPRODUCT({7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2},--MID(A2,ROW(INDIRECT("1:17")),1)),11)+1,"1","0","X","9","8","7","6","5","4","3","2"),"✓","✗"),"")，单独校验第18位。

关键提示：所有MID提取数字时必须配合--转换为数值，否则SUMPRODUCT计算结果为0。

三、启用VBA自定义函数（支持完整校验）

通过编写VBA函数，可调用内置字典校验前6位行政区划代码，并严格判断出生日期、性别位及校验码，覆盖全部国标GB11643-1999要求。

1、按Alt+F11打开VBA编辑器 → 插入模块 → 粘贴以下代码：

Function IDCheck(ID As String) As String

Dim arrArea, i As Integer, sum As Long, code As String

arrArea = Array("11", "12", "13", "14", "15", "21", "22", "23", "31", "32", "33", "34", "35", "36", "37", "41", "42", "43", "44", "45", "46", "50", "51", "52", "53", "54", "61", "62", "63", "64", "65", "71", "81", "82", "91")

If Len(ID) 18 Then IDCheck = "长度错误": Exit Function

If Not IsNumeric(Left(ID, 17)) Then IDCheck = "前17位含非数字": Exit Function

If InStr(Join(arrArea, "|"), Left(ID, 2)) = 0 Then IDCheck = "行政区划不存在": Exit Function

If Mid(ID, 7, 4) Year(Date) Then IDCheck = "出生年份超限": Exit Function

If Mid(ID, 11, 2) 12 Then IDCheck = "月份非法": Exit Function

If Mid(ID, 13, 2) 31 Then IDCheck = "日期非法": Exit Function

sum = 0

For i = 1 To 17: sum = sum + Val(Mid(ID, i, 1)) * Array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2)(i - 1): Next

code = Choose((sum Mod 11) + 1, "1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2")

If UCase(Right(ID, 1)) code Then IDCheck = "校验码错误": Exit Function

IDCheck = "合法"

End Function

2、关闭编辑器，返回工作表，在任意单元格输入：=IDCheck(A2)

重要说明：启用宏的工作簿需保存为.xlsm格式，且用户需允许宏运行；首次使用需信任此VBA项目。

四、使用数据验证+下拉列表限制输入

在数据录入阶段即阻止非法身份证号输入，结合正则表达式逻辑（通过公式模拟）限制单元格仅接受符合基本结构的字符串。

1、选中待输入身份证号的列（如A:A）→ 数据选项卡 → 数据验证 → 允许选择“自定义”。

2、在公式框中输入：

=AND(OR(LEN(A1)=15,LEN(A1)=18),IF(LEN(A1)=15,AND(ISNUMBER(--A1),MID(A1,15,1)="1"+MOD(SUMPRODUCT({7,9,10,5,8,4,2,1,6,3,7,9,10,5,8},--MID(A1,ROW(INDIRECT("1:15")),1)),11))),IF(LEN(A1)=18,AND(ISNUMBER(--LEFT(A1,17)),OR(RIGHT(A1,1)="X",RIGHT(A1,1)="x",ISNUMBER(--RIGHT(A1,1)))))))

3、点击“出错警告”选项卡 → 勾选“显示错误警告”，标题填“身份证格式错误”，错误信息填：“请输入15位或18位合法身份证号！”

注意：该验证仅拦截明显非法输入，不执行完整校验；15位旧号未强制校验末位，建议优先使用18位标准格式。

本篇关于《Excel如何验证身份证号合法性与真伪》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于文章的相关知识，请关注golang学习网公众号！