Excel身份证校验如何实现15位与18位兼容？

Question

在Excel中进行身份证校验时，常需兼容15位与18位身份证号码。常见问题是：如何通过公式自动识别并校验两种格式的合法性？例如，15位身份证为老版格式（无校验码），而18位包含出生日期补“19”及最后一位校验码。技术难点在于：如何用单一公式判断输入是15位或18位，并分别验证其位数、出生年月合法性及18位身份证的MOD11-2校验码是否正确？同时避免误判或公式冗长难维护。

Answer 1

一、身份证校验的背景与挑战

在企业级数据处理中，身份证号码作为核心身份标识，广泛应用于人事系统、客户管理、财务核对等场景。Excel 作为轻量级数据处理工具，常被用于初步数据清洗与验证。然而，中国身份证存在15位（旧版）与18位（新版）两种格式，导致校验逻辑复杂化。

15位身份证由6位地区码 + 6位出生年月日（无“19”前缀）+ 3位顺序码构成，无校验位；而18位身份证则包含完整的出生日期（如19900101）、补全“19”，并增加基于MOD11-2算法生成的最后一位校验码。实际应用中，用户可能混输、错输或伪造身份证号，因此需通过公式实现自动识别与合法性判断。

二、技术难点拆解

• 格式识别：如何用LEN函数动态判断输入是15位还是18位？
• 位数验证：排除非数字字符及长度异常（如16、17位）。
• 出生日期合法性：解析YYMMDD或YYYYMMDD，并校验是否为真实日期（如不能为19901301）。
• 18位校验码计算：实现MOD11-2算法，涉及加权求和与余数映射（0~10 → 0~X）。
• 单一公式集成：避免嵌套过深导致维护困难，兼顾性能与可读性。

三、解决方案设计流程图

graph TD
    A[输入身份证号码] --> B{长度是否为15或18?}
    B -- 否 --> Z[非法]
    B -- 是 --> C[提取出生年月部分]
    C --> D[校验日期有效性]
    D -- 无效 --> Z
    D -- 有效 --> E{是否为18位?}
    E -- 否 --> F[视为15位合法]
    E -- 是 --> G[执行MOD11-2校验]
    G -- 校验失败 --> Z
    G -- 成功 --> H[合法身份证]

四、关键函数与逻辑实现

功能	Excel 函数	说明
长度判断	LEN(A1)	获取字符串长度
纯数字检查	ISNUMBER(VALUE(A1))	防止字母输入
日期构建	DATE(MID(...),MID(...),MID(...))	构造日期对象
加权系数表	{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}	18位前17位权重
校验码映射	{"1";"0";"X";"9";"8";"7";"6";"5";"4";"3";"2"}	MOD11-2结果转字符

五、完整校验公式实现

以下为一个高度集成但结构清晰的数组公式（需按 Ctrl+Shift+Enter 输入）：

=IF(OR(LEN(A1)<>15,LEN(A1)<>18), "非法长度",
 IF(ISERROR(VALUE(A1)), "含非数字字符",
  LET(
   len, LEN(A1),
   id, A1,
   is15, len=15,
   year_part, IF(is15, "19"&MID(id,7,2), MID(id,7,4)),
   month_part, IF(is15, MID(id,9,2), MID(id,11,2)),
   day_part, IF(is15, MID(id,11,2), MID(id,13,2)),
   date_check, ISNUMBER(DATEVALUE(year_part&"-"&month_part&"-"&day_part)),
   mod11_2_check, IF(NOT(is15),
     LET(
      w, {7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2},
      s, SUMPRODUCT(MID(id,ROW(INDIRECT("1:17")),1)*w),
      MOD(s,11),
      INDEX({"1";"0";"X";"9";"8";"7";"6";"5";"4";"3";"2"}, MOD(s,11)+1) = RIGHT(id,1)
     ), TRUE
   ),
   IF(NOT(date_check), "日期非法",
    IF(mod11_2_check, "合法", "校验码错误")
   )
  )
 )
)

六、测试用例与验证数据

输入值	预期结果	类型	说明
11010519491231002X	合法	18位	标准有效身份证
110105491231002	合法	15位	对应上一条的旧版
11010519491331002X	日期非法	18位	月份超限
11010519491231002Y	校验码错误	18位	末尾应为X
abc12345678901234	含非数字字符	N/A	非法输入
11010519491231002X1	非法长度	N/A	19位
310105650101001	合法	15位	正常老身份证
310105196501010019	合法	18位	新身份证，校验通过
310105196501010018	校验码错误	18位	末尾错误
21000019800229	非法长度	N/A	不足15位

七、优化建议与扩展方向

尽管上述公式可在单单元格完成校验，但在大型数据集或频繁调用场景下，仍建议采用以下方式提升效率：

1. 拆分辅助列：将长度判断、日期提取、校验码计算分别放在不同列，便于调试与审计。
2. 使用LAMBDA自定义函数：在支持LAMBDA的Excel版本中，封装身份证校验为可复用函数，如：=ID_CHECK(A1)。
3. VBA增强版校验：对于高频使用场景，可用VBA编写UDF，支持正则表达式与更复杂的逻辑控制。
4. 结合Power Query：在ETL阶段进行批量清洗，提高数据质量源头控制能力。
5. 区域码合法性检查：引入行政区划代码表，进一步验证前六位是否真实存在。