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

验证公式一=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提取身份证出生日期、性别、检验身份证号码正确性中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为：１５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码。１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码，３位数字顺序码和１位检验码。其中３位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶数的为女性，奇数的为男性。１、提取籍贯地区的行政区划代码（A2为身份证号，下同）１５与１８位通用：=LEFT(A2,6)如果有一个编码和省份地区的对照表，可以用VLOOKUP函数来提取地区信息。２、提取出生日期信息１５位：=--TEXT(19&MID(A2,7,6),#-00-00)１８位：=--TEXT(MID(A2,7,8),#-00-00)１５与１８位通用：=--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)(请将输入公式的单元格格式设置为日期格式）３、提取性别信息１５位：=IF(MOD(RIGHT(A2),2)=1,男,女)１８位：=IF(MOD(MID(A2),17,1)=1,男,女)１５与１８位通用：=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,男,女)简化公式：=IF(MOD(RIGHT(LEFT(A2,17)),2),男,女)４、检验身份证号码的正确性１８位身份证号码的最后一位是检验码，它是根据身份证前17位数字依照规则计算出来的，其值０～９或X。一般情况只要有一位数字输入错误，依照规则计算后就会与第18位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况，但这种情况出现的可能性较低。因此，对１８位号码的验证采用如下公式：=MID(10X98765432,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(1:17)),1)*2^(18-ROW(INDIRECT(1:17)))),11)+1,1)=RIGHT(A2,1)对于１５位身份证，由于没有检验码，我们只能简单地去判断出生日期代码是否是一个有效的日期，避免输入一些像“731302”或“980230“等这样不存在的日期。=ISNUMBER(--TEXT(19&MID(A2,7,6),#-00-00))综合１５位和１８位后的通用公式为：=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))))由于目前１５位身份证号码已经很少了，如果对１５位的号码不需要作进一步的判断，则公式可以简化成：=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改为选定的单元格),再用格式刷将其格式刷到其他需要相同设置的单元格。５、１５位升为１８位=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)６、１８位转换为１５位=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)公式四出处使用excel校验身份证号码校验学生录入身份证号信息一直是个很头疼的问题，现在学生的身份证号基本都是18位，利用excel数据有效性只能校验位数是否正确。而对于合法性却望尘莫及，今天查了很多资料，终于找到了一种方法，现介绍如下：一、身份证编码原理：关于身份证第18是怎么计算的，原理如下：根据〖中华人民共和国国家标准GB11643-1999〗中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。地址码（身份证前六位）表示编码对象常住户口所在县(市、旗、区)的行政区划代码。（所有区域的编码可以到这个网站查询到最新的县及县以上的行政编码资料。）生日期码（身份证第七位到第十四位）表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。例如：1981年05月11日就用19810511表示。顺序码（身份证第十五位到十七位）为同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。其中第十七位奇数分给男性，偶数分给女性。校验码（身份证最后一位）是根据前面十七位数字码，按照ISO7064:1983.MOD11-2校验码计算出来的检验码。二、校验方法既然第十八位是校验位，那么我们将从这里入手方能搞定。第十八位数字的计算方法为：1、将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为：79105842163791058422、将这17位数字和系数相乘的结果相加。3、用加出来和除以11，看余数是多少？4、余数只可能有012345678910这11个数字。其分别对应的最后一位身份证的号码为10X98765432。5、通过上面得知如果余数是2，就会在身份证的第18位数字上出现罗马数字的Ⅹ。如