Excel能从身份证号码中提取到什么?

Hello,大家好,今天跟大家分享下身份证号码的构成以及我们利用Excel可以从身份证号码中提取哪些信息,首先我们先来了解下身份证号码的构成,下图所示,身份证号码是由三大部分构成的

1-6位是地址码,其中1-2位代表省份的代码,3-4位代表城市的代码,5-6位代表区县的代码

7-14位是出生日期的代码,它是一个8位数字的代码

15-18位是顺序代码,其中第17位是性别代码,奇数性别为男,偶数性别为女,第18位是校验码,它是根据前面的数字根据算法计算出来的,可以用于鉴别身份证号码的真伪,以上就是身份证号码的组成,下面就让我们来看下都可以利用Excel获取哪些数据

一、获取籍贯

身份证号码的前六位是地址码,我们可以通过地址码来获取籍贯。在这里我们还需要一个身份证号码前六位与籍贯的对照表,我们将其放在sheet2中,这样的话我们只需将公式设置为:=VLOOKUP(LEFT(A2,6),Sheet2!A:B,2,FALSE),然后向下填充即可获取到身份证号码对应的籍贯,跟大家简单的介绍下公式

这个函数主体上是一个vlookup函数的常规用法,

第一参数:LEFT(A2,6),left函数的作用是从左开始提取数据,在这里我们从左开始提取了6位数据,也就是将地址码提取了出来

第二参数:Sheet2!A:B,这个是放在sheet2中的地址码与籍贯的对照表,如果你需要这个表格,获取方法我会放在评论区

第三参数:2,查询的结果在数据区域的第二列

第四参数:精确匹配

二、提取出生日期

提取出生日期,我们只需要将身份证号码的第7到14位提取出来,然后再利用text函数将其设置为日期格式即可,提取身份证号码的第7到14位可以利用mid函数来实现,这样只需要将公式设置为:=--TEXT(MID(A2,7,8),"0-00-00")即可

Text函数的前面有2个减号,这个的作用是将文本格式的数据转换为常规格式,因为text是一个文本函数,它所输出结果也是文本格式的数据,这样的数据一些版本中的Excel是无法使用日期函数对它进行计算的,所以我们需要将其转换为常规格式

三、计算年龄

上一步中我们利用Excel提取到了出生日期,计算年龄就会变得非常的简单,只需要利用DATEDIF函数即可计算出年龄,将公式设置为:=DATEDIF(B2,TODAY(),"y")即可

DATEDIF函数的作用是计算两个日期之间的差值

第一参数:开始的时间,在这里我们设置为b2单元格,就是提取到的出生日期

第二参数:today(),这个函数的的作用是获取今天的日期

第三参数:y,计算的类型,y代表年份

四、计算性别

性别只与身份证号码的第17位有关,17位是偶数性别为女,为奇数性别为男,关键是如何判断第17位的奇偶性,判断奇偶性我们可以利用ISEVEN,这个函数的作用是如果数字为偶数则返回true,不是偶数则返回false,这样的话只需要将公式设置为:=IF(ISEVEN(MID(A2,17,1)),"女","男")即可计算性别

在这里我们利用MID(A2,17,1)这个函数将身份证号码的第17位提取出来,然后再利用ISEVEN函数遇到偶数返回ture,遇到奇数返回false的特性,将其嵌套在if函数的第一参数中,这样的话就会得到正确的结果

我是Excel从零到一,关注我,持续分享更多Excel技巧

觉得有用,麻烦点个在看~

(0)

相关推荐