工作中,总免不了和身份证打交道。除了用Word表格,我们遇到最多的就是要用Excel来处理身份证信息了,比如:


  1. 在Excel中录入身份证信息
  2. 用Excel安全地处理csv文件中的身份证信息
  3. 判断性别
  4. 提取生日信息
  5. 计算年龄
  6. 校验身份证信息是否正确
  7. 15位身份证升级18位
  8. 获取行政区划信息
  9. 获取身份证有效年限信息

本文将对以上六个需求在Excel中的操作事项进行演示,帮助您又快又准确地处理身份证信息,避免踩坑。


在Excel中录入身份证信息


乍一看这个标题,似乎不明就里,难道在Excel里录入身份证也值得拿出来单独说?


凡是有此疑问的,要么是您从来没用Excel处理过身份证,要么您已经在处理身份证信息方面是行家里手了。


所以这一部分是针对从未在Excel中录入身份证信息的小伙伴写的。


我们可以试试看在Excel的任意单元格录入一个15位或18位的身份证:



如上图所示,如果是15位还是18位身份证直接录入到Excel中,结果无一例外是变成类似于【5.10802E+17】这样的科学计数法。


如果选中它后,你会在公式栏看到:


  1. 15位的身份证号码可以全部显示
  2. 18位的身份证最后三位会变成0


为什么会这样?这是因为Excel的一个坑:它会把所有超过11位的数字显示为科学计数法;它只能显示15位的数字,超过15位的数字会直接把末尾几位显示为0。


一般录入时,我们很容易就会发现这个坑,从而去寻找解决办法。


在Excel中录入时避免身份证信息变成科学计数法或最后三位数字变成0的办法有两种:


1.录入身份证信息之前,把Excel的单元格样式设置为文本格式,这适合大批量录入的情况。我一般都是选择一整列,然后设置成文本格式。


2.录入一个英文状态下的单引号,将该单元格强制转变为文本。这适合录入少量身份证信息到Excel中,因为每个号码前都要加这个单引号,否则结果就会出错。



注意上图中地址栏里身份证最前面的英文单引号,并且注意单元格里身份证号码前面并没有那个单引号。如果用LEN()函数检查输入的身份证号码,是18位,也就是说刚才输入的英文单引号并不是一个字符,它仅仅是个文本标识符。


在Excel中安全地处理csv文件中的身份证信息


如果我们的身份证信息来自于某个系统的数据库,那么它很可能是csv格式保存的。


最容易踩的坑是我们习惯性地用Excel打开这个csv文件进行处理,然后随手保存为Excel文件(而不是csv文件),然后把csv文件删除了


等你再打开这个包含身份证信息的Excel文件,你就傻眼了——身份证全部变成科学计数法且18位身份证最后三个数字变成0,永远无法找回来了,除非那个csv文件还在回收站里。


那么,csv文件中的身份证要怎么处理才安全(也就是不会变成科学计数法且18位身份证最后三位不会变成0)呢?


1.首先,绝对不要用Excel直接打开包含身份证信息的csv文件,因为当你打开后身份证信息就已经永久地变成科学计数法且18位身份证最后三位变成0了。


2.正确的做法是将其导入到Excel中:在Excel(2016版、2019版或365版)中,点击【数据】选项卡,在【获取和转换数据】命令栏点击【从文本/csv】,在弹出窗口中找到存储身份证信息的csv文件点击并确定。


这时会进入导入窗口。如果中文显示异常,请选择合适的中文编码,如UTF-8或GB2313之类。



当中文显示正常后,点击底部的【转换数据】,不要点【加载数据】,【加载数据】的加载结果还是错的。


点击【转换数据】后,进入查询编辑页面,你会发现身份证信息还是科学计数法:



其原因是PowerQuery自动帮我们创建了更改格式的步骤,我们要在右侧的查询步骤列表里删除这一步:


这时查询步骤列表应该是这样子的:


然后我们就看到结果正确了:


3.将结果加载到Excel的sheet中:


选择【关闭并上载至】,将其加载到工作表:


4.如果还要对身份证进行其他操作,如校验、提取出生年月日信息、计算年龄、查找所属行政区划信息,建议加载之前一并处理后,再选择加载到工作表中。

判断性别

我们知道,身份证中的性别信息由倒数第二位确定:如果倒数第二位是奇数,则代表男性,如果是偶数,则代表女性。所以,提取性别信息也就是判断身份证倒数第二位数字的奇偶来实现。

根据前面的分析,我们知道,身份证中的性别信息提取,有两个关键:一是找到身份证倒数第二位,二是判断其奇偶。

找到身份证倒数第二位我们用Excel的MID()函数:

=MID(A2,17,1)

奇偶判断用MOD()函数:

=MOD(MID(A3,17,1),2)

然后用IF来判断,如果是奇数,就代表男性,如果是偶数,就代表女性:

=IF(MOD(MID(A2,17,1),2)=1,"男","女")

在PowerQuery中思路类似:

引入数据源之后,以新增一列的方式来提取性别:

= Table.AddColumn(更改的类型, "性别", each

if

Number.Mod(Number.From(Text.At([身份证],16)),2)=1

then

"男"

else

"女"

)

注意,我们使用的M函数是Text.At(),索引用的是16,在Excel公式中我们用的索引是17,原因就在于PowerQuery的索引是从0开始计算的。

这样一来,从Excel提取身份证中包含的信息操作就介绍完了。还有没有遗漏的呢?如果有的话请补充。

提取生日信息


当身份证在Excel中正确显示后,就可以对其进行下一步处理了。比如提取其中的生日信息。


这里有两种方法直接在Excel中提取年月日信息:


1.CTRL+E快速填充法。即先手工提取第一个生日,然后按CTRL+E,进行快速填充,则Excel会智能地将之后每一行的生日信息提取出来。

但是这种方法有个限制,就是你只能提取其中的信息,不能在提取的同时进行格式化,比如把提取出的生日信息变成日期格式(1979/8/28)。有兴趣的可以试试看在第一行输入1979/8/28,再按CTRL+E,会发生什么。


2.推荐做法是利用Excel的内置函数MID()函数。这样就可以在提取生日信息的同时将其格式化为标准日期格式:

=MID(A2,7,4)&"/"&MID(A2,11,2)&"/"&MID(A2,13,2)


原理也并不复杂:身份证信息从第7位开始的四个数字是年份信息,之后的两位是月份信息,再之后的两位是日期信息,提取出年月日之后用【&】符号将其与【/】进行连接,形成标准的日期格式。

这里也可以用分列的方式直接分列成日期格式。但多了一步操作。


3.如果需要经常提取身份证中的生日信息,那么建议在PowerQuery中进行。由于数据源可能在数据库、可能在单独的Excel文件中,也可能在CSV文件中,或者在别的地方,引入数据源的步骤我跳过。假设已经将身份证信息引入到PowerQuery且格式正确:


PowerQuery中提取生日信息比Excel简单多了,直接用Text.Middle()函数:


= Table.AddColumn(更改的类型, "生日", each Text.Middle([身份证],6,8))


这里细心的读者会发现,前面在Excel中提取生日时,说出生年信息是从身份证第7位开始,怎么在PowerQuery中,用了6?其实是因为PowerQuery中的默认index值是从0开始,所以第七位在PowerQuery中表示为6。


然后直接选中【生日】这一列,更改其格式为【日期】,就完成了:


细心的读者可能又会发现,在Excel中,【19790828】是无法直接变成日期格式的,必须借助【/】将年月日分开才行。但是PowerQuery中可以直接把【19790828】这样的识别为日期,这也是PowerQuery比Excel强大的证据之一。


计算年龄


当提取出生日信息后,计算年龄就很简单了。


1.在Excel中计算年龄:


=YEAR(NOW())-YEAR([@MID函数提取生日])


2.在PowerQuery中计算年龄与Excel中计算类似:


= Table.AddColumn(更改的类型1, "年龄", each Date.Year(DateTime.LocalNow())-Date.Year([生日]))



校验身份证


校验身份证信息的原理很简单,公式也很简单,就是很长很长(无论是Excel还是PowerQuery)。


Excel中校验身份证信息。

=IF(LEN([@身份证])=18,

IF(RIGHT(A3)=CHOOSE(MOD(

SUM((MID(A3,1,1)+MID(A3,11,1))*7

+(MID(A3,2,1)+MID(A3,12,1))*9

+(MID(A3,3,1)+MID(A3,13,1))*10

+(MID(A3,4,1)+MID(A3,14,1))*5

+(MID(A3,5,1)+MID(A3,15,1))*8

+(MID(A3,6,1)+MID(A3,16,1))*4

+(MID(A3,7,1)+MID(A3,17,1))*2

+MID(A3,8,1)+MID(A3,9,1)*6

+MID(A3,10,1)*3),11)+1,1,0,"X",9,8,7,6,5,4,3,2),"Y","N"),

"位数错误,无法校验")

还有其他公式也可以校验,原理类似,比如:

=IF(LOOKUP((LEFT(A5,1)*7+MID(A5,2,1)*9+MID(A5,3,1)*10+MID(A5,4,1)*5+MID(A5,5,1)*8+MID(A5,6,1)*4+MID(A5,7,1)*2+MID(A5,8,1)+MID(A5,9,1)*6+MID(A5,10,1)*3+MID(A5,11,1)*7+MID(A5,12,1)*9+MID(A5,13,1)*10+MID(A5,14,1)*5+MID(A5,15,1)*8+MID(A5,16,1)*4+MID(A5,17,1)*2)-ROUNDDOWN((LEFT(A5,1)*7+MID(A5,2,1)*9+MID(A5,3,1)*10+MID(A5,4,1)*5+MID(A5,5,1)*8+MID(A5,6,1)*4+MID(A5,7,1)*2+MID(A5,8,1)+MID(A5,9,1)*6+MID(A5,10,1)*3+MID(A5,11,1)*7+MID(A5,12,1)*9+MID(A5,13,1)*10+MID(A5,14,1)*5+MID(A5,15,1)*8+MID(A5,16,1)*4+MID(A5,17,1)*2)/11,0)*11,{0,1,2,3,4,5,6,7,8,9,10},{"1","0","x","9","8","7","6","5","4","3","2"})=RIGHT(A5,1),"OK","Wrong")

2.PowerQuery中校验身份证信息,详见我的百家号文章《用PowerQuery快速校验Excel表格中的身份证信息》。

将15位身份证号码升级为18位

虽然现在15位身份证号码基本绝迹,但是偶尔还会碰到。

我们可以根据身份证信息编码规则(本来这里应该有链接,便于跳转,但是百家号不允许给第三方链接,所以感兴趣的读者只有自己去百度搜索了),将15位身份证号码自动升级为18位。

假设身份证信息在A2单元格,我们首先要判断其位数是不是15位,如果是15位才需要转换。

而15位转18位身份证的转换公式很长很长(原因是身份证编码规则很长很长):

=IF(LEN(A2)=15, (REPLACE(A2,7,,19))&(INDEX({1,0,"X",9,8,7,6,5,4,3,2},1,(MOD(SUM(MID(REPLACE(A2,7,,19),1,1)*7+MID(REPLACE(A2,7,,19),2,1)*9+MID(REPLACE(A2,7,,19),3,1)*10+MID(REPLACE(A2,7,,19),4,1)*5+MID(REPLACE(A2,7,,19),5,1)*8+MID(REPLACE(A2,7,,19),6,1)*4+MID(REPLACE(A2,7,,19),7,1)*2+MID(REPLACE(A2,7,,19),8,1)*1+MID(REPLACE(A2,7,,19),9,1)*6+MID(REPLACE(A2,7,,19),10,1)*3+MID(REPLACE(A2,7,,19),11,1)*7+MID(REPLACE(A2,7,,19),12,1)*9+MID(REPLACE(A2,7,,19),13,1)*10+MID(REPLACE(A2,7,,19),14,1)*5+MID(REPLACE(A2,7,,19),15,1)*8+MID(REPLACE(A2,7,,19),16,1)*4+MID(REPLACE(A2,7,,19),17,1)*2),11))+1)),A2)

上面的公式中,我用IF判断了身份证长度,只有15位的才会升级成18位的,已经是18位的不会有任何改变。

我建议先判断身份证是否正确,然后对正确的身份证信息进行升级转换操作。

PowerQuery的操作略过。

获取身份证中的行政区划信息

从身份证获取行政区划信息,并没有难度。关键是要知道身份证中的行政区划信息包含在那一段数字中,以及在哪里可以找到行政区划信息。

1.身份证信息中的行政区划信息体现在前6个数字中,具体到区县集别。

2.获取最新的全国行政区划信息。由于百家号不允许给第三方链接,而获取最新行政区域的PowerQuery代码必须从国家民政部网站去获取,因此建议百度搜索【如何用powerbi自动获取最新的全国行政区划信息】,按照其中的操作即可。

3.如何根据身份证前六位获取行政区划信息。在Excel中用vlookup函数即可。在PowerQuery中使用Table.Join()函数,详见《处理所有Excel数据重复相关问题,看这一篇就够了》。

获取身份证有效年限信息

获取身份证有效年限信息,是很困难的一个操作。其实和Excel关系不大。

我列出两种思路供参考:

  1. 分而治之的办法。让目标对象自己上报身份证有效年限信息。建议通过腾讯在线表格或表单等在线工具,这样可以节省大量时间和精力。传统的通过发Excel表格乃至Word文档去收集的办法已经严重落伍。
  2. 如果已经有身份证的照片信息,则可利用百度云提供的OCR身份证识别接口,用Python之类的程序语言去调用,实现批量识别。
  3. 其实录入身份证号码时,如果只有身份证照片信息,也可以调用百度云提供的OCR身份证识别接口,识别非常准确。

结语

文章已经很长很长了,但是全面总结了身份证信息在Excel中处理的所有详情,如果有遗漏,请您在评论中补充。

如果此文对您有用,建议收藏和转发。

如果您希望获取更多办公效率提升的方法、工具和相关资料,请您关注我,谢谢。