身份证信息提取

184509937

身份证信息提取样例.rar (68.5 KB, 下载次数: 919)

2012-10-2 10:17 上传

点击文件名下载附件

公民身份号码是由17位数字码和1位校验码组成。排列顺序从左至右分别为：6位地址码，8位出生日期码，3位顺序码和1位校验码。

地址码（身份证地址码对照表见下面附录）和出生日期码很好理解，顺序码表示在同一地址码所标识的区域范围内，对同年同月同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。

身份证最后一位校验码算法如下：

1. 将身份证号码前17位数分别乘以不同的系数，从第1位到第17位的系数分别为：7  ， 9 ，  10 ，  5  ， 8  ， 4  ， 2 ，  1 ，  6，   3  ， 7 ，  9，   10，   5  ， 8 ，  4 ，  2

2. 将得到的17个乘积相加。

3. 将相加后的和除以11并得到余数。

4. 余数可能为0 ，  1 ，  2，   3，   4 ，  5 ， 6 ， 7 ，  8 ，  9 ，  10这些个数字，其对应的身份证最后一位校验码为
                1 ，  0 ，  X  ， 9  ， 8  ， 7   ，6  ， 5   ，4   ，3  ， 2。

“身份证信息”包含首次发证地址、校验码，vlookup查询

地区=VLOOKUP(VALUE(LEFT(B2,6)),身份证地区,2,TRUE)
出生日期==TEXT(IF(LEN(B2)=18,MID(B2,7,8),19&MID(B2,7,6)),"0000-00-00")
年龄=YEAR(TODAY())-YEAR(D2)
                           

杨小军

出生日期写成这样

1. =TEXT(IF(LEN(B2)=18,MID(B2,7,8),19&MID(B2,7,6)),"0000-00-00")

复制代码

184509937

谢谢2楼高手指点，简洁

artin88

我就一直没搞明白校验码为什么要弄一个X出来，都是数字不是很好吗

cv009

身份证号码验证公式可以直接写成这样，免了占用两个单元格进行对比。

1. =IF(AND(LEN(B2)=18,MID("10X98765432",MOD(SUM(MID(B2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17},1)*{7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2}),11)+1,1)=MID(B2,18,1)),"合法","号码非法")

复制代码

再给你一个15位身份证号码转换18位身份证号码的公式。

1. =LEFT(B2,6)&"19"&RIGHT(B2,9)&MID("10X98765432",MOD((SUM(MID(B2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1)*{7,9,10,5,8,4,6,3,7,9,10,5,8,4,2})+11),11)+1,1)

复制代码

cv009

artin88 发表于 2012-10-2 11:25
我就一直没搞明白校验码为什么要弄一个X出来，都是数字不是很好吗

身份证号码的验证码是用前17位数字和系数相乘的结果相加，然后除以11留取余数。
那么余数就会有0－1－2－3－4－5－6－7－8－9－10这11个数字，X是用来替代多出来的那个数字的。
这11个数字对应1－0－X －9－8－7－6－5－4－3－2这11个直。

artin88

cv009 发表于 2012-10-11 16:37
身份证号码的验证码是用前17位数字和系数相乘的结果相加，然后除以11留取余数。
那么余数就会有0－1－ ...

如果模10，校验位刚好从0到9，也可以满足校验的目的，为什么偏要模11呢

jeep2700

学习了
谢谢!!!

rakehell78

校验码为什么要弄一个X出来，输入也不方便，全是数字比较好。

suifeng8470

=MID("0X987654321",MOD(SUMPRODUCT(MID(LEFT(A2,17),ROW($1:$17),1),MOD(2^(18-ROW($1:$17)),11)),11),1)  运算出来就是错的         
=MID("10X98765432",1+MOD(SUMPRODUCT(MID(LEFT(A2,17),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),1)  这个就是对的，为什么必须是下一个才对