EXCEL身份证号校验公式

EXCEL身份证号校验公式

=IF(RIGHT(F3,18)=””,””,(IF(MID(“10X98765432″,MOD(SUMPRODUCT(MID(RIGHT(F3,18),ROW($1:$17),1)*2^(18-ROW($1:$17))),11)+1,1)=MID(RIGHT(F3,18),18,18),”正确”,”错误”)))

=IF(LOOKUP((LEFT(H2,1)*7+MID(H2,2,1)*9+MID(H2,3,1)*10+MID(H2,4,1)*5+MID(H2,5,1)*8+MID(H2,6,1)*4+MID(H2,7,1)*2+MID(H2,8,1)+MID(H2,9,1)*6+MID(H2,10,1)*3+MID(H2,11,1)*7+MID(H2,12,1)*9+MID(H2,13,1)*10+MID(H2,14,1)*5+MID(H2,15,1)*8+MID(H2,16,1)*4+MID(H2,17,1)*2)-ROUNDDOWN((LEFT(H2,1)*7+MID(H2,2,1)*9+MID(H2,3,1)*10+MID(H2,4,1)*5+MID(H2,5,1)*8+MID(H2,6,1)*4+MID(H2,7,1)*2+MID(H2,8,1)+MID(H2,9,1)*6+MID(H2,10,1)*3+MID(H2,11,1)*7+MID(H2,12,1)*9+MID(H2,13,1)*10+MID(H2,14,1)*5+MID(H2,15,1)*8+MID(H2,16,1)*4+MID(H2,17,1)*2)/11,0)*11,{0,1,2,3,4,5,6,7,8,9,10},{“1″,”0″,”x”,”9″,”8″,”7″,”6″,”5″,”4″,”3″,”2″})=RIGHT(H2,1),”OK”,”Wrong”)

=IF(LOOKUP((LEFT(right(F3,18),1)*7+MID(right(F3,18),2,1)*9+MID(right(F3,18),3,1)*10+MID(right(F3,18),4,1)*5+MID(right(F3,18),5,1)*8+MID(right(F3,18),6,1)*4+MID(right(F3,18),7,1)*2+MID(right(F3,18),8,1)+MID(right(F3,18),9,1)*6+MID(right(F3,18),10,1)*3+MID(right(F3,18),11,1)*7+MID(right(F3,18),12,1)*9+MID(right(F3,18),13,1)*10+MID(right(F3,18),14,1)*5+MID(right(F3,18),15,1)*8+MID(right(F3,18),16,1)*4+MID(right(F3,18),17,1)*2)-ROUNDDOWN((LEFT(right(F3,18),1)*7+MID(right(F3,18),2,1)*9+MID(right(F3,18),3,1)*10+MID(right(F3,18),4,1)*5+MID(right(F3,18),5,1)*8+MID(right(F3,18),6,1)*4+MID(right(F3,18),7,1)*2+MID(right(F3,18),8,1)+MID(right(F3,18),9,1)*6+MID(right(F3,18),10,1)*3+MID(right(F3,18),11,1)*7+MID(right(F3,18),12,1)*9+MID(right(F3,18),13,1)*10+MID(right(F3,18),14,1)*5+MID(right(F3,18),15,1)*8+MID(right(F3,18),16,1)*4+MID(right(F3,18),17,1)*2)/11,0)*11,{0,1,2,3,4,5,6,7,8,9,10},{“1″,”0″,”x”,”9″,”8″,”7″,”6″,”5″,”4″,”3″,”2″})=RIGHT(right(F3,18),1),”正确”,”错误”)