Excel电子表格身份证号码是否正确验证公式

整理 懿懿念  2020-08-26 22:34:11  阅读 62 次 评论 0 条

在平时的工作中,面对大量的数据时,有时候不免会头晕眼花,=IF(LEN(A1)=18,IF(RIGHT(A1,1)="X",IF(CHOOSE(MOD(SUM(LEFT(RIGHT(A1,18))*7+LEFT(RIGHT(A1,17))*9+LEFT(RIGHT(A1,16))*10+LEFT(RIGHT(A1,15))*5+LEFT(RIGHT(A1,14))*8+LEFT(RIGHT(A1,13))*4+LEFT(RIGHT(A1,12))*2+LEFT(RIGHT(A1,11))*1+LEFT(RIGHT(A1,10))*6+LEFT(RIGHT(A1,9))*3+LEFT(RIGHT(A1,8))*7+LEFT(RIGHT(A1,7))*9+LEFT(RIGHT(A1,6))*10+LEFT(RIGHT(A1,5))*5+LEFT(RIGHT(A1,4))*8+LEFT(RIGHT(A1,3))*4+LEFT(RIGHT(A1,2))*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(A1,1)),"正确!","出错啦!"),IF(CHOOSE(MOD(SUM(LEFT(RIGHT(A1,18))*7+LEFT(RIGHT(A1,17))*9+LEFT(RIGHT(A1,16))*10+LEFT(RIGHT(A1,15))*5+LEFT(RIGHT(A1,14))*8+LEFT(RIGHT(A1,13))*4+LEFT(RIGHT(A1,12))*2+LEFT(RIGHT(A1,11))*1+LEFT(RIGHT(A1,10))*6+LEFT(RIGHT(A1,9))*3+LEFT(RIGHT(A1,8))*7+LEFT(RIGHT(A1,7))*9+LEFT(RIGHT(A1,6))*10+LEFT(RIGHT(A1,5))*5+LEFT(RIGHT(A1,4))*8+LEFT(RIGHT(A1,3))*4+LEFT(RIGHT(A1,2))*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(A1,1))*1,"正确!","出错啦!")),IF(LEN(A1)=15,"老号,请注意!",IF(LEN(A1)=0,"缺号码","位数不对!")))


本文地址:http://www.diyibiji.com/post/46.html
温馨提示:文章内容系作者个人观点,不代表懿懿念对观点赞同或支持。
版权声明:本文为整理文章,由课堂之家整理,欢迎分享本文,转载请保留出处!

评论已关闭!