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

考据公式一

=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*(M

ID(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,"男","女")