EXCEL验证身份证号码真伪的公式，请问图中完整的公式应该是怎样的？

Question

如题所述

Answer 1

身份证校验码是根据前16位对16个系数乘积之和除以11的余数来确定的，10余数对10个校验码：0,1,2,3,4,5,6,7,8,9,10对应1,0,"X",9,8,7,6,5,4,3,2。计算校验码公式，比如身份证号码在A2，B2输入=LOOKUP(MOD(SUMPRODUCT(--MID(A2,COLUMN(A1:Q1),1),{7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2}),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,"X",9,8,7,6,5,4,3,2})，要判定对还是错，可以在B2输入=IF(LEN(A2)<16,"不是18位身份证号",IF(--RIGHT(A2)=LOOKUP(MOD(SUMPRODUCT(--MID(A2,COLUMN(A1:Q1),1),{7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2}),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,"X",9,8,7,6,5,4,3,2}),"对","错"))

Answer 2

假设身份证填在A1
=IF(RIGHT(A1,1)=LOOKUP(MOD(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),{0;1;2;3;4;5;6;7;8;9;10},{"1";"0";"X";"9";"8";"7";"6";"5";"4";"3";"2"}),"真","假")

Answer 3

=IF(LEN(A2)<18,"一代",IF(TEXT(CHOOSE(MOD(SUMPRODUCT(MID(A2,ROW($1:$1),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1,1,0,"X",9,8,7,6,5,4,3,2),0)=RIGHT(A2),"真","假"))