01简介

我国大陆地区一代居民身份证号为15位，二代提为18位，位数结构由663调整为6831.6831分别为行政区码6、日期码8、顺序码3、校验码1.其中顺序码奇数为男，偶数为女，当然，用顺序码第3位直接判断也可以。校验码可以为0-10，其中仅10占位两位，为长度一致，以罗马数字X代表10.

下面，以【440302199001230134】为例，讲讲身份证验证码的计算及缺位补齐。

02校验码

通过前17位【44030219900123013】可计算出第18位的【4】，我们把第18位的【4】称为校验码。

逐位取出①【数位值】

=MID($B$1,B5,1)*1

一次取17位的数组版

=MID(B1,ROW($A$1:$A$17),1)*1

③【乘积和】=①【数位值】*②【系数】

=SUMPRODUCT(D5:D21,C5:C21)

④余数=MOD(③【乘积和】,11)

=MOD(G22,11)

【校验码】=INDEX(⑤检验码,④余数+1)

=INDEX(G10:G20,G23+1)

将多个公式嵌套为单个

②系数、⑤校验码、ROW($A$1:$A$17)为固定值，按【F9】转为常量数组。

=INDEX({1;0;"X";9;8;7;6;5;4;3;2},MOD(SUMPRODUCT(MID(B1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*1,{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1)

除X,0,1之外，余数与校验码之和为12，公式可修改为

=TEXT(TEXT(12-MOD(SUMPRODUCT(MID(B1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*1,{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),"[>11]1;[<11]0;!0"),"[<10]0;X")

检验码的计算看似复杂，但一般只有“低水平”的假证才会犯校验码错误。校验码的主要功能不是防伪，而是纠正录错。

我们计算出的校验码，与身份证号的最后一位不同，可以提醒我们录入有误。

=AND(LEN(B1)=18,EXACT(RIGHT(B1),TEXT(TEXT(12-MOD(SUMPRODUCT(MID(B1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*1,{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),"[>11]1;[<11]0;!0"),"[<10]0;X")))

公式中有3个B1,可以用LET[365]添加一个名称，方便公式移植

=LET(string,B1,AND(LEN(string)=18,EXACT(RIGHT(string),TEXT(TEXT(12-MOD(SUMPRODUCT(MID(string,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*1,{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),"[>11]1;[<11]0;!0"),"[<10]0;X")

)))

03将就补齐

第18位可以计算出来，第18位联合任意16位，可以倒挤出缺少的1位。换句话说，18位任意缺少1位，都可以通过另外17位计算出来。

【校验码】是最特殊的，上一主题计算过了。

接下来，以缺第1位的【40302199001230134】为例，讲解补位方法。

=REPLACE(B1,B2,1,0&MID(B1,B2,1))

我们在缺位的后一位前加0，记第n位为缺位，因缺位使后一位前进一位，缺位身份证号的第n位即缺位的后一位。

我们暂以0补充缺失的第1位数值，1-9是类似的，可作数组处理。计算10种情况的校验码，哪个是【4】，则是正确的数位值。

04构建数组

取出【将就】身份证号的17*1的数位值

=MID(REPLACE(B1,B2,1,0&MID(B1,B2,1)),ROW(A1:A17),1)*1

构建17*10的数组，供【将就】身份证号填充

=IF(ROW(A1:A17)=B2,TRANSPOSE(ROW(A1:A10))-1,0)

相加填充，并将ROW函数的结果【F9】为常量数组=IF({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}=B2,{0,1,2,3,4,5,6,7,8,9},0)+MID(REPLACE(B1,B2,1,0&MID(B1,B2,1)),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*1

LET一下

=LET(String,B1,MissN,B2,RNum,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},IF(RNum=MissN,{0,1,2,3,4,5,6,7,8,9},0)+MID(REPLACE(String,MissN,1,0&MID(String,MissN,1)),RNum,1)*1)

05十里挑一

我们要把17*10的矩阵变成10个数，自然想到MMULT函数。

为迎合MMULT的参数特点，把系数{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}转置为{7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2}.MMULT一下得出乘积和

=MMULT({7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2},IF({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}=B2,{0,1,2,3,4,5,6,7,8,9},0)+MID(REPLACE(B1,B2,1,0&MID(B1,B2,1)),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*1)

余数…校验码…数位值……

=MATCH(RIGHT(B1),TEXT(TEXT(12-MOD(MMULT({7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2},IF({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}=B2,{0,1,2,3,4,5,6,7,8,9},0)+MID(REPLACE(B1,B2,1,0&MID(B1,B2,1)),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*1),11),"[>11]1;[<11]0;!0"),"[<10]0;X"),0)-1

LET一下

=LET(String,B1,MissN,B2,RNum,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},MATCH(RIGHT(String),TEXT(TEXT(12-MOD(MMULT({7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2},IF(RNum=MissN,{0,1,2,3,4,5,6,7,8,9},0)+MID(REPLACE(String,MissN,1,0&MID(String,MissN,1)),RNum,1)*1),11),"[>11]1;[<11]0;!0"),"[<10]0;X"),0)-1)

06 1+17

主题2讨论了第18位的计算，主题5讨论了第1-17位的计算，两个公式合为一个。

=IF(B2=18,TEXT(TEXT(12-MOD(SUMPRODUCT(MID(B1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*1,{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),"[>11]1;[<11]0;!0"),"[<10]0;X"),MATCH(RIGHT(B1),TEXT(TEXT(12-MOD(MMULT({7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2},IF({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}=B2,{0,1,2,3,4,5,6,7,8,9},0)+MID(REPLACE(B1,B2,1,0&MID(B1,B2,1)),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)*1),11),"[>11]1;[<11]0;!0"),"[<10]0;X"),0)-1)

LET一下

=LET(String,B1,MissN,B2,RNum,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},Ratio,{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2},IF(MissN=18,TEXT(TEXT(12-MOD(SUMPRODUCT(MID(String,RNum,1)*1,Ratio),11),"[>11]1;[<11]0;!0"),"[<10]0;X"),MATCH(RIGHT(String),TEXT(TEXT(12-MOD(MMULT(TRANSPOSE(Ratio),IF(RNum=MissN,{0,1,2,3,4,5,6,7,8,9},0)+MID(REPLACE(String,MissN,1,0&MID(String,MissN,1)),RNum,1)*1),11),"[>11]1;[<11]0;!0"),"[<10]0;X"),0)-1))

07题外语

一代和二代身份证，身份证号都包含了个人信息，这是各地区信息资源不均的无奈之举。如果未来升级三代身份证或电子身份证，个人认为可以调整身份证号的编码规则，身份证号仅为索引号，不同机构根据级别授权，读取或验证不同级别信息。

示例文件:

欢迎点赞-关注-转发，我们下次见。