如何在EXCEL中批量将15位身份证号码变更为18位身份证号码?求解

如何在EXCEL中批量将15位身份证号码变更为18位身份证号码?~

首先需要知道身份证号中各个数字的含义，以18位身份证号主讲，
前边6个数字，每2个数字一组，分别是省（自治区、直辖市）、市（盟、自治州）、县（县级市、区）的代码，
接下来的8位数字（15位身份证是6位数字）是出生年月日（15位身份证号的年是两位数，省略了19），比如1975年6月5日出生的，这数字就是19750605（15位身份证中就是750605），
再往后的3个数是顺序码，这3个数的最后一个数，奇数为男，偶数为女。
最后1位是效验码（15位身份证号没有，就是为了防止身份证重号，而增设的）
总结：15位身份证号与18位身份证号的区别：
区别一：年份少个“19”，
区别二：最后少一个验证码。加“19”很容易，关键就在最后一个效验码的计算。
效验码是根据ISO 7604：1983.MOD11-2计算的，具体的计算规则看下图

至此可以写出身份证号升级公式了=REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW($1:$17),1)*2^(18-ROW($1:$17))),11)+1,1)

上边公式中没有直接引用“加权因子”，而是使用了“2^(18-ROW($1:$17))”，用这个去乘身份证的每一个数，再求余数，结果是一样的，自己可以验证下。

18位身份证最后一位是校验位, 不那么简单.

15位的老身份证号码插入年份即可升到17位:

例如 B2 单元格中是这个17位的号码:
=IF(LEN(A2)=15,MID(A2,1,6)&"19"&MID(A2,7,9))

那么在 C2 中输入数组公式(公式输入后须同时按下 Ctrl+Shift+Enter 组合键方可完成):
=B2&LOOKUP(MOD(SUM(MID(B2,ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,"X",9,8,7,6,5,4,3,2})
即可得到18位(上述公式为函数大师 阵军 的作品).

参考资料:

公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。

校验方法：
（1）十七位数字本体码加权求和公式
S = Sum(Ai * Wi), i = 0, … , 16 ，先对前17位数字的权求和
Ai:表示第i位置上的身份证号码数字值
Wi:表示第i位置上的加权因子
Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

（2）计算模
Y = mod(S, 11)

（3）通过模得到对应的校验码

Y: 0 1 2 3 4 5 6 7 8 9 10
校验码: 1 0 X 9 8 7 6 5 4 3 2

15位的老身份证号码插入年份即可升到17位:
例如 B2 单元格中是这个17位的号码:
=IF(LEN(A2)=15,MID(A2,1,6)&19&MID(A2,7,9))
那么在 C2 中输入数组公式(公式输入后须同时按下 Ctrl+Shift+Enter 组合键方可完成):
=B2&LOOKUP(MOD(SUM(MID(B2,ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,X,9,8,7,6,5,4,3,2})
即可得到18位(上述公式为函数大师 阵军 的作品).
参考资料:公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。
校验方法：（1）十七位数字本体码加权求和公式
S = Sum(Ai * Wi), i = 0, … , 16 ，先对前17位数字的权求和
Ai:表示第i位置上的身份证号码数字值
Wi:表示第i位置上的加权因子
Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
（2）计算模
Y = mod(S, 11)（3）通过模得到对应的校验码

---

南郑县19440324556： 在excel中,如何快速录入大量身份证号码(有规律的可以序列填充的,但由于数字超于十五位) - ？
方柴青尔： 把身份证号码列设置为“文本”格式.

南郑县19440324556： 在excel表格里面,有一列身份证号,里面有18位的 也有15位的 怎么把15位的查询出来 - ？
方柴青尔： 如果你的身份证号在A列,那么在B列输入公式 =if(len(a1)=15,a1,＂＂) 公式下拉

南郑县19440324556： 在EXCEL表中有以下几项姓名、性别、身份证号,身份证号为15位或18们,请问要怎么样才能把15位或者18位身份证号的记录筛选出来呢? - ？
方柴青尔： 直接用数据筛选可以的~~按我的方法做: 选中身份证号所在的那一列~点击筛选~然后有个“等于”~~在等于的方框中输入15个“?”号(即???????????????)然后点击确定~~马上就可以筛选出来15位身份证号码~~~ 同理~要筛选18位的输入18个“?”即可~~ 以上方法叫做运用通配符查找法~~

南郑县19440324556： EXCEL表的单元格中怎样将18位身份证号码缩成15位的. - ？
方柴青尔： 假设数据在A列从A2开始,则在B2输入:=if(len(A2)=15,A2,left(left(A2,6)&right(A2,10),15))

南郑县19440324556： excel 如何把身份证号筛选出来,由于数据量比较大,有15位的、也有18位的最主要的是有些身份证号码错误？
方柴青尔： 设身份证号在a列,在b2输入: =if(or(len(a2)=15,len(a2)=18),＂＂,＂该行对应的身份证号码错误＂) 然后下拉. 之后对b列进行排序或筛选,删除错误的行数据就好了.

南郑县19440324556： 如何在Excel表格中完整显示出身份证号码的操作方法 - ？
方柴青尔： 步骤如下: 一、打开需要使用的表格.二、在身份证号码直接输入身份号码,就出现以下图的情况. 三、是在前面加一个撇号,如图.操作之后这个撇号会会消失,直接显示文本数值.但这种方法每次都需要先添加撇号,对于需要输入大...

南郑县19440324556： excel中怎样把身份证号码 - ？
方柴青尔： Excel默认对超出11位的数字以科学计数法表示,对于15位以上的数字,超出部分以0计. 1、在Excel表格中完整显示出身份证号码的操作方法是将单元格设置成文本格式,选中单元格,点击文本,再输入身份证号码即可. 2、选择数字格式为“文本” 输入身份证号,并将列宽调宽让其完整显示出来.

南郑县19440324556： 在excel中怎么把身份证号码分组 - ？
方柴青尔： 直接用排列吧,最低级的分组功能

南郑县19440324556： EXCEL 如何把身份证号的生日月份提取出来,要15位和18位都能用的 - ？
方柴青尔： 提取身份证上的出生月份公式如下,假如身份证号在A列 结果为文本型的 =MID(A1,9+(LEN(A1)=18)*2,2) =MID(A1,11-(LEN(A1)=15)*2,2) 结果为数值型的 =--MID(A1,9+(LEN(A1)=18)*2,2) =--MID(A1,11-(LEN(A1)=15)*2,2)

南郑县19440324556： 电子表格怎么打15位以上的数字我只能打15位的数字,想输入一个新 ？
方柴青尔： EXCEL中只能用文本方式输入18位身份证的号码,最快捷的办法是在输入的身份证号码前加一个'号(英文半角下),EXCEL会自动识别为文本.