Excel身份证号校验函数怎么写 Excel如何验证身份证号码的合法性和真实性【验证】

Excel中验证身份证号需校验位数、格式、出生日期、行政区划码及校验码，方法包括公式法（嵌套TEXT/MID/SUMPRODUCT）、VBA自定义函数（IDCheck）和数据验证结合正则逻辑。

如果您在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

标签： excel 正则表达式