搜档网
当前位置:搜档网 › 身份证正确检验及验证公式

身份证正确检验及验证公式

验证公式一
=IF(LEN(H20)=18,IF(RIGHT(H20,1)="X",IF(CHOOSE(MOD(SUM(LEFT(RIGHT(H20,18))*7+LEFT(RIGHT(H20,17))*9+LEFT(RIGHT(H20,16))*10+LEFT(RIGHT(H20,15))*5+LEFT(RIGHT(H20,14))*8+LEFT(RIGHT(H20,13))*4+LEFT(RIGHT(H20,12))*2+LEFT(RIGHT(H20,11))*1+LEFT(RIGHT(H20,10))*6+LEFT(RIGHT(H20,9))*3+LEFT(RIGHT(H20,8))*7+LEFT(RIGHT(H20,7))*9+LEFT(RIGHT(H20,6))*10+LEFT(RIGHT(H20,5))*5+LEFT(RIGHT(H20,4))*8+LEFT(RIGHT(H20,3))*4+LEFT(RIGHT(H20,2))*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(H20,1)),"正确!","出错啦!"),IF(CHOOSE(MOD(SUM(LEFT(RIGHT(H20,18))*7+LEFT(RIGHT(H20,17))*9+LEFT(RIGHT(H20,16))*10+LEFT(RIGHT(H20,15))*5+LEFT(RIGHT(H20,14))*8+LEFT(RIGHT(H20,13))*4+LEFT(RIGHT(H20,12))*2+LEFT(RIGHT(H20,11))*1+LEFT(RIGHT(H20,10))*6+LEFT(RIGHT(H20,9))*3+LEFT(RIGHT(H20,8))*7+LEFT(RIGHT(H20,7))*9+LEFT(RIGHT(H20,6))*10+LEFT(RIGHT(H20,5))*5+LEFT(RIGHT(H20,4))*8+LEFT(RIGHT(H20,3))*4+LEFT(RIGHT(H20,2))*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(H20,1))*1,"正确!","出错啦!")),IF(LEN(H20)=15,"老号,请注意!",IF(LEN(H20)=0,"缺号码","位数不对!")))

验证公式二
=MID("10X98765432",MOD(SUMPRODUCT(MID(H20,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(H20,1)




身份证有15位和18位两种,身份证位数是否正确,我们可以用LEN函数判断。但身份证上的日期是否合法:月份是否在1-12之间,日期是否在1-31之间,并且2月份只有28或29天,其他月份30或31天,都不能超过范围。另外一般规定6岁以上才可以办理身份证,也就是年份也有一个超范围的可能性。综合起来看,有三类错误:“身份证位数不对”、“月日错误”、“年份错误”。
假定身份证号码在B1单元格,下面的公式可以综合判断以上三种错误:

验证公式三
=IF(OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MONTH(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,11,2)),DAY(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,13,2))),"月日错误",""),IF(LEN(B1)=15,IF(OR(MONTH(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,9,2)),DAY(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,11,2))),"月日错误",""))),"身份证位数不对")&IF(AND(LEN(B1)=18,1*MID(B1,7,4)>YEAR(TODAY())-6),"年份错误","")


验证公式四
=IF(LEN(A1)=0,"空",IF(LEN(A1)=15,"老号",IF(LEN(A1)<>18,"位数不对",IF(CHOOSE(MOD(SUM(MID(A1,1,1)*7+MID(A1,2,1)*9+MID(A1,3,1)*10+MID(A1,4,1)*5+MID(A1,5,1)*8+MID(A1,6,1)*4+MID(A1,7,1)*2+MID(A1,8,1)*1+MID(A1,9,1)*6+MID(A1,10,1)*3+MID(A1,11,1)*7+MID(A1,12,1)*9+MID(A1,13,1)*10+MID(A1,14,1)*5+MID(A1,15,1)*8+MID(A1,16,1)*4+MID(A1,17,1)*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=IF(ISNUMBER(RIGHT(A1,1)*1),RIGHT(A1,1)*1,"X"),"正确","错误"))))


公式二出处

EXCEL提取身份证出生日期、性别、检验身份证号码正确性

中国居民身份证号码是一组特征组合码,原为15位,现升级

为18位,其编码规则为:
15位:6位数字常住户口所在县市的行政区划代码,6位数字出生日期代码,3位数字顺序码。
18位:6位数字常住户口所在县市的行政区划代码,8位数字出生日期代码,3位数字顺序码和1位检验码。
其中3位数字顺序码,是为同一地址码的同年同月同日出生人员编制的顺序号,偶数的为女性,奇数的为男性。
1、提取籍贯地区的行政区划代码(A2为身份证号,下同)
15与18位通用:=LEFT(A2,6)
如果有一个编码和省份地区的对照表,可以用VLOOKUP函数来提取地区信息。
2、提取出生日期信息
15位:=--TEXT(19&MID(A2,7,6),"#-00-00")
18位:=--TEXT(MID(A2,7,8),"#-00-00")
15与18位通用:=--TEXT(IF(LEN(A2)=15,19,"")&MID(A2,7,6+IF(LEN(A2)=18,2,0)),"#-00-00")
简化公式:=--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")
(请将输入公式的单元格格式设置为日期格式)
3、提取性别信息
15位:=IF(MOD(RIGHT(A2),2)=1,"男","女")
18位:=IF(MOD(MID(A2),17,1)=1,"男","女")
15与18位通用:=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,"男","女")
简化公式:=IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女")
4、检验身份证号码的正确性
18位身份证号码的最后一位是检验码,它是根据身份证前17位数字依照规则计算出来的,其值0~9或X。一般情况只要有一位数字输入错误,依照规则计算后就会与第18位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况,但这种情况出现的可能性较低。因此,对18位号码的验证采用如下公式:
=MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2,1)
对于15位身份证,由于没有检验码,我们只能简单地去判断出生日期代码是否是一个有效的日期,避免输入一些像“731302”或“980230“等这样不存在的日期。
=ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))
综合15位和18位后的通用公式为:
=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))))
由于目前15位身份证号码已经很少了,如果对15位的号码不需要作进一步的判断,则公式可以简化成:
=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),LEN(A2)=15)
将上面的公式放到B2单元格,如果结果为TRUE,则身份证号是正确的,结果为FALSE则是错误的。
你也可以将上述公式放在数据有效性中,防止录入错误的身份证号。操作方法:选择需要输入身份证的全部单元格区域,比如A2:A10,点菜单"数据"-"有效性",在

"允许"的下拉框中选择"自定义",在"公式"输入上面的15位和18位通用公式,确定以后即可。注意:公式里的"A2"是你刚才选定要输入身份证的单元格区域的第一个单元格,如果你是要在C3:C20输入身份证号,则将公式里的"A2"改为"C3"。另外,你也可以先设置好某单个单元格的数据有效性(这时公式的A2改为选定的单元格),再用格式刷将其格式刷到其他需要相同设置的单元格。
5、15位升为18位
=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)
6、18位转换为15位
=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)


公式四出处

使用excel校验身份证号码
校验学生录入身份证号信息一直是个很头疼的问题,现在学生的身份证号基本都是18位,利用excel数据有效性只能校验位数是否正确 。而对于合法性却望尘莫及,今天查了很多资料,终于找到了一种方法,现介绍如下:
一、身份证编码原理:
关于身份证第18是怎么计算的,原理如下:根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定,公民身份号码是特征组合码,由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为:六位数 字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。
地址码(身份证前六位)表示编码对象常住户口所在县(市、旗、区)的行政区划代码。(所有区域的编码可以到这个网站 https://www.sodocs.net/doc/a23946187.html,/tjbz/index.htm 查询到最新的县及县以上的行政编码资料。)
生日期码(身份证第七位到第十四位)表示编码对象出生的年、月、日,其中年份用四位数字表示,年、月、日之间不用分隔符。例如:1981年05月11日就用19810511表示。
顺序码(身份证第十五位到十七位)为同一地址码所标识的区域范围内,对同年、月、日出生的人员编定的顺序号。其中第十七位奇数分给男性,偶数分给女性。
校验码(身份证最后一位)是根据前面十七位数字码,按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。

二、校验方法
既然第十八位是校验位,那么我们将从这里入手方能搞定。
第十八位数字的计算方法为:
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这11个数字。其分别对应的最后一位身份证的号码为1 0 X 9 8 7 6 5 4 3 2。
5、通过上面得知如果余数是2,就会在身份证的第18位数字上出现罗马

数字的Ⅹ。如果余数是10,身份证的最后一位号码就是2。

例如:某男性的身份证号码是340524************。我们要看看这个身份证是不是合法的身份证。
首先:我们得出,前17位的乘积和是189
然后:用189除以11得出的结果是17 2/11,也就是说余数是2。
最后:通过对应规则就可以知道余数2对应的数字是x。所以,这是一个合格的身份证号码。

到此已经柳暗花明了,接下来就是写公式了,假设excel表的A1单元格记录的是身份证号码,我们在B1单元格输入如下公式:

=IF(LEN(A1)=0,"空",IF(LEN(A1)=15,"老号",IF(LEN(A1)<>18,"位数不对",IF(CHOOSE(MOD(SUM(MID(A1,1,1)*7+MID(A1,2,1)*9+MID(A1,3,1)*10+MID(A1,4,1)*5+MID(A1,5,1)*8+MID(A1,6,1)*4+MID(A1,7,1)*2+MID(A1,8,1)*1+MID(A1,9,1)*6+MID(A1,10,1)*3+MID(A1,11,1)*7+MID(A1,12,1)*9+MID(A1,13,1)*10+MID(A1,14,1)*5+MID(A1,15,1)*8+MID(A1,16,1)*4+MID(A1,17,1)*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=IF(ISNUMBER(RIGHT(A1,1)*1),RIGHT(A1,1)*1,"X"),"正确","错误"))))
好了,搞定!!!
备注说明:关于大陆身份证有的人会发现前几位为什么变化了。这主要出现在中国的重庆。原有的重庆人的身份证多数以51开头。以前隶属于四川的原因。但新办的身份证可能是50开头,原因是行政区划改变所致。中国各地的行政区划代码请参考国家统计局网站https://www.sodocs.net/doc/a23946187.html,/tjbz/index.htm







相关主题