《身份证正确检验及验证公式.docx》由会员分享，可在线阅读，更多相关《身份证正确检验及验证公式.docx（5页珍藏版）》请在三一文库上搜索。
1、验证公式一 =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
2、(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(R
3、IGHT(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, 正确！ , 出错啦！
4、 ),IF(LEN(H20)=15, 老号，请注意！ ,IF(LEN(H20)=0, 缺号码 , 位数不对！ )验证公式二 =MID(10X98765432,MOD(SUMPRODUCT(MID(H20,ROW(INDIRECT(1:17),1)*2A(18 -ROW(INDIRECT(1:17),11) 1,1)=RIGHT(H20,1)身份证有15位和18位两种，身份证位数是否正确，我们可以用 LEN函数判断。但 身份证上的日期是否合法：月份是否在 1-12 之间，日期是否在 1-31 之间，并且 2 月份只有 28 或 29 天，其他月份 30 或 31 天，都不能超过范围。另外一般规定
5、 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)
6、,月日错误,),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)
7、v18,位数不对 ,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,
8、3,2)=IF(ISNUMBER(RIGHT(A1,1)*1),RIGHT(A1,1)*1,X) , 正确, 错误) 公式二出处excels取身份证出生日期、性别、检验身份证号码正确性中国居民身份证号码是一组特征组合码，原为15位，现升级为18位，其编码规 那么为：15位：6位数字常住户口所在县市的行政区划代码，6位数字出生日期代码，3 位数字顺序码。18位：6位数字常住户口所在县市的行政区划代码，8位数字出生日期代码，3 位数字顺序码和1位检验码。其中3位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶 数的为女性，奇数的为男性。1、提取籍贯地区的行政区划代码(A2为身份证号
9、，下同)15与18位通用：=LEFT(A2,6)如果有一个编码和省份地区的对照表，可以用VLOOKU函数来提取地区信息。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,6IF(LEN(A2)=18,2,0),#-00-00)简化公式： =-TExT(LEN(A2)=15)*19&MID(A2,7,6 (LEN(A2)=18)*2),#-00-00)( 请将输入公式的单元格格式设置为日期格式)3、提取性别
10、信息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位数字依照规那么计算出来的，其值09或X。一般情况只要有一位数字输入错误，依照规那么计算后 就会与第 18 位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况， 但
11、这种情况出现的可能性较低。因此，对18位号码的验证采用如下公式： =MID(10X98765432,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(1:17),1)*2A(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(10
12、X98765432,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(1:1 7),1)*2A(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:1 7),1)*2A(18-ROW(INDIRECT(1:1
13、7),11) 1,1)=RIGHT(A2),LEN(A2)=15) 将上面的公式放到B2单元格，如果结果为TRUE那么身份证号是正确的，结果为 FALSE那么是错误的。你也可以将上述公式放在数据有效性中，防止录入错误的身份证号。操作方法 : 选 择需要输入身份证的全部单元格区域 , 比方 A2:A10, 点菜单数据- 有效性 , 在允 许的下拉框中选择 自定义, 在公式输入上面的 15 位和 18 位通用公式 , 确定以 后即可。注意 : 公式里的 A2 是你刚刚选定要输入身份证的单元格区域的第一个单 元格,如果你是要在C3:C20输入身份证号，那么将公式里的A2改为C3。另外，你也 可以先设
14、置好某单个单元格的数据有效性(这时公式的A2改为选定的单元格),再用 格式刷将其格式刷到其他需要相同设置的单元格。5、15位升为18位 =IF(LEN(A2)=15,REPLACE(A2,7,19)&MID(10X98765432,MOD(SUMPRODUCT(MID(RE PLACE(A2,7,19),ROW(INDIRECT(1:17),1)*2A(18-ROW(INDIRECT(1:17),11) 1,1),A2)6、18位转换为15位=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)公式四出处 使用 excel 校验身份证号码校验学生录入身份证号信
15、息一直是个很头疼的问题，现在学生的身份证号根本都是 18 位，利用 excel 数据有效性只能校验位数是否正确。而对于合法性却望尘莫及，今天查了很多资料，终于找到了一种方法，现介绍如下： 一、身份证编码原理：关于身份证第 18 是怎么计算的，原理如下：根据中华人民共和国国家标准 GB 11643-1999中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位 数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数 字地址 码，八位数字出生日期码，三位数字顺序码和一位数字校验码。 地址码身份证前六位表示编码对象常住户口所在县市、旗、区 的行政区划代码。所有区域的编码可以到这个网站
16、:/ stats.gov /tjbz/index.htm 查询到最新的县及县以上的行政编码 资料。 生日期码身份证第七位到第十四位表示编码对象出生的年、月、日，其中年份 用四位数字表示，年、月、日之间不用分隔符。例如：1981年 05 月 11日就用19810511表示。顺序码身份证第十五位到十七位为同一地址码所标识的区域范围内，对同年、 月、日出生的人员编定的顺序号。其中第十七位奇数分给男性，偶数分给女性。 校验码身份证最后一位是根据前面十七位数字码，按照 ISO 7064:1983.MOD 11-2 校验码计算出来的检验码。二、校验方法 既然第十八位是校验位，那么我们将从这里入手方能搞定。
17、 第十八位数字的计算方法为：1、将前面的身份证号码 17 位数分别乘以不同的系数。从第一位到第十七位的系数 分别为： 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 22、将这 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 位数字上出现罗马数字的X。如果余数是10,身份证的最后一位号码就是 2。例如：某男性的身份证号码是 我们要看看这
18、个身份证是不 是合法的身份证。首先：我们得出,前 17位的乘积和是 189 然后：用 189除以 11 得出的结果是 17 2/11 ,也就是说余数是 2。 最后：通过对应规那么就可以知道余数 2对应的数字是X。所以，这是一个合格的身 份证号码。到此已经柳暗花明了，接下来就是写公式了，假设 eXcel 表的 A1 单元格记录的是 身份证号码，我们在 B1 单元格输入如下公式：=IF(LEN(A1)=0,空,IF(LEN(A1)=15,老号,IF(LEN(A1)v18,位数不对 ,IF(CHOOSE(MOD(SUM(MID(A1,1,1)*7 MID(A1,2,1)*9 MID(A1,3,1)*
19、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 开头，原因是行政区划改变所致。中国各地的行政区划代码请 参考国家统计局网站 :/ stats.gov /tjbz/index.htm