身份证校验算法及 EXCEL 公式
根据公民身份号码国家标准（GB11463-1999）, 现行 18 位身份证使用同余计算校验，这篇文章主要做三件事
- 介绍居民身份证校验规则
- 基于规则编写 EXCEL 校验公式
- 提供相关 Demo 下载
国标解读
公民身份证号码中各个位置上的号码字符值应满足下列公式的校验
$$\sum_{n=1}^{18}(\alpha_n\times W_n) \equiv 1 \ (\rm mod \ 11)$$
- $n$ 表示身份证号码 从右至左 的位置序号，即从身份证第 18 位算起；
- $\alpha _n$ 表示从右至左第 n 位上的号码，$\alpha _1$ 即身份证第 18 位数字，该数字为 X 时，取值为 10；
- $W_n$ 指从右至左第 n 位上的加权因子，计算 2 的 n-1 次方然后除以 11 取余，即：$W_n=2^{n-1}(\rm mod \ 11)$；
校验规则解读：身份证上 18 位数字分别乘以相应位置上的加权因子然后求和，得出的结果 X 与 1 对模 11 同余
以国标 GB11463-1999 附录 A 提供的身份证号码为例，某 1949 年 12 月 31 日出生的北京朝阳市女性公民，其身份证号码为：11010519491231002X，按照上述公式进行校验
| n | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| $\alpha _n$ | 10 | 2 | 0 | 0 | 1 | 3 | 2 | 1 | 9 | 4 | 9 | 1 | 5 | 0 | 1 | 0 | 1 | 1 |
| $W_n$ | 1 | 2 | 4 | 8 | 5 | 10 | 9 | 7 | 3 | 6 | 1 | 2 | 4 | 8 | 5 | 10 | 9 | 7 |
| $\alpha_n\times W_n$ | 10 | 4 | 0 | 0 | 5 | 30 | 18 | 7 | 27 | 24 | 9 | 2 | 20 | 0 | 5 | 0 | 9 | 7 |
$$\sum_{n=1}^{18}(\alpha_n\times W_n)=177 \equiv 1 \ (\rm mod \ 11)$$
加权求和运算后得出数字 = 177，177÷11=16…1，满足校验条件，是正确的身份证号码。
EXCEL 公式
在 EXCEL 中，可以利用数组公式进行快速判断，假设 EXCEL 中身份证文本字段填写在 A1 单元格，对应的数组公式 为：
=IF (MOD (SUM (IFERROR (NUMBERVALUE (LEFT (RIGHT (A1,ROW (INDIRECT (“1:18”))))),10)*MOD (2^(ROW (INDIRECT (“1:18”))-1),11)),11)=1,” 正确”,” 错误”)
公式逐步运算解析
| 序号 | 公式 | 备注 |
|---|---|---|
| 1 | ROW(INDIRECT(“1:18”)) | 利用 ROW () 函数生成 1-18 的正整数序列，为了避免插入行导致引用区域变化，用 INDIRECT (“1:18”) 进行限制 |
| 2 | LEFT(RIGHT()) | 从右变算起取出 1-18 位长度字符串，再从左截取该字符串的首位字符，此公式可以实现从右到左逐个取出身份证号码 |
| 3 | IFERROR(NUMBERVALUE(),10) | EXCEL 中为了避免身份证被科学计数法截断，应使用文本格式填写身份证。将逐个位置的身份证号码转为数字，若改位置非数字（即 X），用 10 代替，此公式表示算法中的 $\alpha _n$ |
| 4 | MOD(2^(ROW(INDIRECT(“1:18”))-1),11) | 此公式表示算法中的 $W_n$ |
| 5 | MOD(SUM(),11) | 18 位数字分别乘以相应位置上的加权因子然后求和再取余，此公式表示算法为 $\sum_{n=1}^{18}(\alpha_n\times W_n) \ (\rm mod \ 11)$ |
| 6 | IF (…=1,” 正确”,” 错误”) | 若取余结果 = 1，表示校验正确，否则标记为错误 |
附件下载
适用版本： office2013 以上
演示文件： 点击下载
国标文件： 点击下载
相关文章