excel如何设置身份证号限制18位（思路比结果重要）

近日，有人遇到需要把15位的身份证号变为18位的问题。其实很早听说过身份证号数字间是有逻辑关系的，但具体是什么关系，没太弄清，正好借着这个机会，给整明白了。干脆整理记录下来，以后遇到需要核对身份证号是否正确时也能用得上。

excel如何设置身份证号限制18位（思路比结果重要）(1)

身份证号数字代表的含义

要解决15位身份证号码变18位的问题，首先得搞明白身份证号数字的意义。这方面因为一般也用不到，所以完全弄清楚的人不多。基本上，大家都知道前面6位代表所在的省市县，7到14位代表出生年月日。后面的4位代表啥，就不太了解了。而这正是解题的关键，也是本文要说的问题。身份证号第15-17位为顺序号，其中17位表示性别，男为单数，女为双数；最后一位（第18位）为校验码，数值为0-9和X。

身份证号15位变18位，或者18位身份证号的复核是否正确，都是通过计算第18位的校验码来进行处理。校验码的计算逻辑为：

用前17位的数字，分别乘以“7;9;10;5;8;4;1;9;6;3;7;9;10;5;8;4;2”用17个乘积数的和除以11求得余数，根据余数从0到10的顺序，从“1,0,"x",9,8,7,6,5,4,3,2”序列中找对应顺序的数据即为校验码数字。比如乘积和除11后的余数为0，则校验码为1，余数为3，校验码为x，以此类推。

而15位的身份证号出生年份没有19，比如1985年2月2日生的，按18位号码为：19850202，按15位则是850202，省略了前面的19；另外没有末尾的校验码。

所以15位身份证号变18位，其实就是要在身份证号的第7、8位补充进入19，然后在末尾补充进入计算出的校验码。

函数计算公式

excel如何设置身份证号限制18位（思路比结果重要）(2)

1、补充19：

将原15位的号码从第7位开始截断为两部分，left(ref,6)提取前6位，right(ref，9）提取后9位，然后再用&将其和19连起来，left(ref,6)&"19"&right(ref,9)，则完成了前面17位数据的整理。

2、计算校验码：

17位第第7、8位为19，按校验码计算规则，分别乘以2和1，相加的结果为11，对最后总合计除以11的余数不影响，所以可以直接用15位的数据与“7;9;10;5;8;4;6;3;7;9;10;5;8;4;2”相乘后合计。数组相乘求和，SUMPRODUCT正好派上用场。接下来是如何将15位身份证号设置为数组的问题，利用MID(ref,ROW($B$1:$B$15),1)可以转变为15个数字组成的数组。然后利用求余数函数mod(ref,11)，求得余数。最后根据余数在数组里取得相应的值，从一位数组取值就需要用到index(ref,n)。校验码综合计算公式为：INDEX({1,0,"x",9,8,7,6,5,4,3,2},MOD(SUMPRODUCT(MID(E5,ROW($B$1:$B$15),1)*{7;9;10;5;8;4;6;3;7;9;10;5;8;4;2}),11) 1)

其中{1,0,"x",9,8,7,6,5,4,3,2}间隔符可以为“，”，也可以为“；”；

而{7;9;10;5;8;4;6;3;7;9;10;5;8;4;2}间隔符必须为“；”。

最终，15位身份证号变18位的完整公式为：示例E5为15位身份证号所在单元格

=IF(E5<>"",LEFT(E5,6) & "19" & RIGHT(E5,9) & INDEX({1;0;"x";9;8;7;6;5;4;3;2},MOD(SUMPRODUCT(MID(E5,ROW($B$1:$B$15),1)*{7;9;10;5;8;4;6;3;7;9;10;5;8;4;2}),11) 1),"")

其中，if条件语句为避免为空时出现错误码。

18位身份证号复核

首先将前17位取出形成数组，根据验证码逻辑关系，求出对应的验证码，=INDEX({1;0;"x";9;8;7;6;5;4;3;2},MOD(SUMPRODUCT(MID(E5,ROW($B$1:$B$17),1)*{7;9;10;5;8;4;1;9;6;3;7;9;10;5;8;4;2}),11) 1)

然后用求出的验证码和身份证号第18位进行对比，相同及复核通过，不同则有误。

=if(right(e5,1)=INDEX({1;0;"x";9;8;7;6;5;4;3;2},MOD(SUMPRODUCT(MID(E5,ROW($B$1:$B$17),1)*{7;9;10;5;8;4;1;9;6;3;7;9;10;5;8;4;2}),11) 1),"","错误")

以前见过有人用自定义函数进行复核，相比而言，本文的函数公式比起用VBA编写自定义函数方法还是要简单得多。

excel如何设置身份证号限制18位（思路比结果重要）(3)