工作中，我们经常需要收集员工的年龄、籍贯、出生日期等个人信息，便于公司进行登记管理。

而当我们在收集时，不仅需要员工仔细填写每一项信息，收集的人也需要花时间跟每个人核对信息是否有误。

其实有了身份证号码，有些信息就不需要专门去收集了，而是可以直接从身份证号码里面提取，比如性别、年龄、出生日期、籍贯（身份证所属地区）等。

如果我们掌握了一些身份证常识，再加上一点小小的技巧，就既可以减少录入的工作量，也保证了数据的准确性。

下面小 E 就教教大家如何只靠身份证号码就获取到相关信息，以后再也不怕老板嫌弃我收集信息的效率低啦~

01

编码规则

◆ ◆ ◆

想要弄清楚如何取出这些信息，首先要了解一下身份证编码的规则。第二代身份证号码共 18 位，其编排规则如下——

❶ 前 1-6 位数字表示：所在省、市、区（县、自治县、县级市）的代码。

❷ 第 7-14 位数字表示：出生年、月、日。

❸ 第 15、16 位数字表示：所在地派出所的代码。

❹ 第 17 位数字表示性别：奇数表示男性，偶数表示女性。

❺ 第 18 位数字是校检码。

了解完以上规则，接下来小 E 就用一张具体的身份证信息表格为大家演示怎样从身份证号码中取出这些信息~

02

所属地区

◆ ◆ ◆

思路：身份证号码前 ６ 位代表的是地区代码，要将身份证号码对应出所属地区，首先要有一个全国各地区代码的数据库，然后使用 VLOOKUP 函数从中查询出具体的地区名称。

Step 1 小 E 已经收集好了全国各地的地区代码，存在了附件的「证件地区」工作表中，下面只是冰山一角，总共有 3218 个代码。

Step 2 接下来需要用到 LEFT 函数取出身份证号码的前 ６ 位。

=LEFT(A2,6)

Step 3 再然后通过 VLOOKUP 函数，在前面所提的「证件地区」工作表中查询出地区名称。

B2 单元格的具体的提取公式：

=VLOOKUP(LEFT(A2,6),证件地区!A:B,2,0)

如下图所示：

注：行政区划代码历年来不断有调整变化，身份证号码中的行政区划代码查询应以当时的区划代码为准。

03

出生日期

◆ ◆ ◆

身份证中第 7 到 14 位代表了出生年月日，直接提取出它们，很多小可爱都说这没啥难度鸭~我也会！

然鹅作为日期，人家可是有规范的日期类型格式的，单纯提出来的 8 位数字显然是无法匹配人家要求的~

所以对于出生日期，不仅要提取出来，还要按规范的格式呈现出来。想实现这样的效果，跟着下面的教程做，其实并不难~

思路：先取出身份证号码的 7-14 位数值，然后再转化成规范的日期类型数据。

Step 1 使用 MID 函数提取出生年月日 8 位数字：

=MID(A2,7,8)

Step 2 再使用 TEXT 函数格式化输出日期格式：

=TEXT(MID(A2,7,8),"#-00-00")

Step 3 通过数学运算转换成日期数据，在 C2 单元格输入最终公式：

=--TEXT(MID(A2,7,8),"#-00-00")

这里使用了两个负号，表示减负运算，使日期文本转换为数值，生成了规范的日期。

如下图所示：

在第二步得到日期以后还要进行第三步的处理，就是为了转换成规范的日期格式。因为标准日期本质上是数值，所以我们通常会通过数学运算（加、减、乘、除等均可以）进行转换，让文本日期变成规范的日期数据。

04

年龄

◆ ◆ ◆

思路：年龄可以使用当前的日期与出生日期相减得到，本例中使用了 DATEDIF 函数来计算两个日期之间的差额。

Step 1 取出生日期（前一部分已讲解）公式为：

Step 2 当前的日期可以使用 TODAY 函数获得，公式为：

=TODAY

Step 3 有了起始的时间和结束的时间，使用 DATEDIF 函数中的第三个参数「 Y 」

返回两个日期之间的年份数，即为年龄。

D2 单元格的公式为：

=DATEDIF(TEXT(MID(A2,7,8),"#-00-00"),TODAY,"Y")

如下图所示：

05

性别

◆ ◆ ◆

思路：性别可以从身份证号码倒数第 2 位判断，如果是奇数，则为男性；如果是偶数，则为女性。据此可以借用取余函数 MOD 加 IF 函数来完成这个判断。

Step 1 先用 MID 函数取身份证号码倒数第 ２ 位。

=MID(A2,17,1)

Step 2 再借用 MOD 函数除以 ２，余数是 ０ 的即为偶数，余数是 １ 的即为奇数。

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

Step 3 再用 IF 函数进行余数判断，即可得到男或女的结果，单元格 E2 的公式为：

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

IF 函数的第一个参数对余数的奇偶性进行判断，当余数为 1 时，奇数，即为男，否则为女。

如下图所示：

熬夜加班成笑话？你肯定不懂数据透视表神器

《学数据透视表，做 Excel 高手》

数据录入，一键更新

↓

玩转 Excel 最强利器——数据透视表，

你就能效率如飞，甩开 Excel 小白好几条街！