文章目录
- 前言
- 身份证号校验
- 校验规则
- EXCEL实现
- 性别提取
- 籍贯提取
- 出生年月提取
前言
我们的居民身份证作为我们国家每个人唯一的身份标识，其设计十分巧妙，18位的身份证号包含了许多信息，并且为了防止身份证录入错误，还提供了一种可靠的校验方式。
顺带介绍一下我们身份证的构成。
我们以500225199203026858为例
号码 | 含义 |
500225 | 行政区域代码 |
19920302 | 出生年月 |
685 | 同一地区同一天出生的顺序码（奇数男；偶数女） |
8 | 校验码 |
身份证号校验
校验规则
身份证号码位数 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 |
系数 | 7 | 9 | 10 | 5 | 8 | 4 | 2 | 1 | 6 | 3 | 7 | 9 | 10 | 5 | 8 | 4 | 2 | 校验码 |
计算公式：
根据余数区查询对应得校验码表
余数 | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
校验码 | 1 | 0 | X | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 |
以上就是身份证的校验。
EXCEL实现
我们需要在EXCEL中实现这个校验，在输入身份证号后能够去校验并且能够进行提示
如图所示：
在身份证验证结果中输入公式：
=IF(MID("10X98765432",(MOD(SUMPRODUCT(MID(A14,ROW(INDIRECT("1:17")),1)*
{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1),1)=RIGHT(A14),TRUE,FALSE)首先解释一下几个关键的函数：
INDIRECT(引用区域，引用格式）：
- 解释: 用于引用，一般分为两种情况，引用文本或者引用内容，第二个参数缺省值为True。
- 例如 对于B2 (此单元格的内容为A1)，如果在某个单元格输入=INDIRECT（B2 ），则得到A1的实际值；如果输入=INDIRECT（“B2” ）,则得到B2这个文本值
- 这里主要是用于以文本格式
MID(text, start_num, num_chars):
- 从一个字符串中截取出指定数量的字符
ROW(refrence):
- 获取引用的单元格的行号
- 这里的用法相当巧妙，ROW(INDIRECT(“1:17”)生成了1…17的行号，生成一个数组{1；2；…17}
SUMPRODUCT(array1, [array2], [array3], …):
- 数组对应相乘再求和。
MOD(a,b)
-a/b求余
IF函数之流不必解释了，进行一顿操作之后，将计算出来的校验码与身份证号中的最后一位RIGHT(身份证号)进行相等判断，即可得出身份证是否争取输入身份证号。
性别提取
身份号中的性别是顺序码的奇偶性进行判断的，奇数分配给男性，偶数分配给女性，于是，只需要提取第17位号码，进行2的求余，若为1，则为男性，反之，则为女性。
上代码：
=IF(MOD(MID(A10,17,1),2),"男","女")籍贯提取
身份证号的前5位为行政区域代码，直接查表即可。但是存在一个不好的地方就是，此代码随着行政区域的变动而变动，例如在，1997年前，重庆未直辖，大足的代码就是510230，后来直辖，变成了500225，再到前几年合并双桥区升为大足区，改为500111。所以如果登记不同年龄阶段的人，那么我们就不能用同一张代码表。
我介绍一下怎么提取。
首先下载一张比较完全的行政区域代码表链接: 示例文档 提取码: tpgu ，此文档中的存在一张17年的表，可直接使用。
利用vlookup函数进行查询填充。
具体代码：
=VLOOKUP(MID(A14,1,6),行政区域表!A:B,2,TRUE)出生年月提取
出生年月提取就很好理解了，直接利用DATE函数，分别提取年/月/日进行提取。
