目前最短的身份证号码检验公式,长度:418(含等号) 1 小时前 上传
下载附件 (14.5 KB)
=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"),"正确","错误"))))
本帖最后由 qinqh_yl 于 2011-8-5 17:55 编辑
回复 雄鹰 的帖子
函数嵌套层数过多,2003版函数嵌套不能超过7层,2007以上版本就没问题。
可以改为:
=IF((A1="")+(LEN(A1)=15),IF(A1="","空号","老号"),IF(LEN(A1)=18,IF(RIGHT(A1)=MID("10X98765432",MOD(SUMPRODUCT(MID(A1,ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11)+1,1),"正确","错误"),"位数不对"))
或:
=IF((A1="")+(LEN(A1)=15),IF(A1="","空号","老号"),IF(LEN(A1)=18,IF(RIGHT(A1)=MID("10X98765432",MOD(SUM(MID(A1,ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11)+1,1),"正确","错误"),"位数不对"))
后一个为数组公式。
|