小小的身份证号，居然隐藏了这么多信息！

回复关键词【2020】

获取 100+ 套图表模板！

任何行业都有自己的职业基本技能要求，只有迈过这个门槛，才能算是合格的从业人员。

想做主持人，你的普通话必须得过关，讲究的就是字正腔圆。

如果是银行职员，必须要熟练使用小键盘，完成每天大量的数据输入工作。

用更直白的话来说，每个行业，都要掌握基本功。

做 HR 也要有基本功，这个基本功就是：能够熟练地从身份证号码里，提取人员信息。

这些信息包括但不限于：

生日信息

性别

计算年龄

户籍信息

接下来，我们就展开讲一讲，这些基本功在 Excel 中如何实现。

01

提取生日

从身份证号码里提取生日信息，是最常见、最基本的需求。

我们都知道，身份证号从第 7 位开始，中间的 8 位代表的是生日信息，要把它提取出来，常用的方法有两种：

函数公式

快捷键

◆ 函数公式 ◆

因为是要从中间位置提取文本，所以这里要用到 MID 函数：

MID 函数公式如下：

=MID(D2,7,8)

MID 原理很简单，有 3 个参数：

参数 1，要提取文本的单元格，本例中的身份证号码。

参数 2，从第几个位置开始提取，比如这里从第 7 位开始。

参数 3，要截取多长的文本，比如这里的生日是 8 个字符。

明白了参数的名称，公式就非常容易理解了，对吧。

◆ 快捷键 ◆

相比函数公式，更为简单的方法，是使用「快速填充」功能，一键提取生日信息。

我们再看一下具体的文字步骤：

❶ 先输入第 1 个身份证号码里的生日，给 Excel 创建一个提取规则的参考。

❷ 在第二个单元格上，按下「快速填充」的快捷键 Ctrl+E，一下子就完成了生日的提取。

02

计算年龄

提取了生日信息，自然就想到要根据生日计算年龄。

年龄怎么计算？把出生年份提取出来，然后用当前的年份，减去出生年份就好了。

公式如下：

=2019-MID(D2,7,4)

公式中用 MID 函数从第 7 位开始提取 4 个字符，提取出生的年份，然后用 2019 减去这个年份，就是年龄了。

这个对你来说，so easy，对吧？！

03

提取性别

性别的提取，和生日提取是类似的。只是我们要提取的字符位置不同。

身份证号码中，倒数第 2 为数字代表的是性别，奇数代表男，偶数代表是女。

那么方法自然和提取生日信息是类似的了，有两个方法：

函数公式

快捷键

◆ 函数公式 ◆

依然使用 MID 函数提取倒数第 2 个数字。

公式如下：

=MID(D2,17,1)

公式很容易链接，从第 17 位开始提取 1 个字符，即获取倒数第 2 位的性别标志符。

◆ 快捷键 ◆

依然是用【Ctrl+E】进行快速填充。

先填写一个数字，然后在第 2 个单元格上按下【Ctrl+E】，快速提取。

◆ 显示成男女 ◆

数字不能直观看出是男是女，把数字转成具体的性别的时候，优先选择函数公式方法，因为我们只需要在原有公式上改进，就可以实现目的。

公式如下：

=IF(MOD(MID(D2,17,1),2)=0,'女','男')

公式中的 MOD 函数，用来判断数字是奇数还是偶数；

然后用 IF 函数条件判断，如果是奇数就返回「男」，如果是偶数就返回「女」。

04

提取户籍

很少人知道，身份证号码的前 6 位代表的是籍贯信息。

根据一个籍贯编码查询表，可以快速地根据身份证号码，知道这个人是哪里人。

这个过程，使用函数公式实现起来，也不难。

◆ 提取籍贯信息 ◆

籍贯编码是身份证号码的前 6 位，所以这里用 LEFT 函数就可以。

公式如下：

=LEFT(D2,6)

公式非常简单，不是吗？

参数 1，要提取的文本，本例中的身份证号码。

参数 2，从左边开始，要提取多少个字符，本例中代表籍贯的 6 个字符。

◆ 根据籍贯编码，查询籍贯 ◆

首先你要准备一个「籍贯编码查询表」，然后用 VLOOKUP 查询获取编码对应的籍贯。

对应的 VLOOKUP 公式如下：

公式如下：

=VLOOKUP(LEFT(D2,6),地址码!A:B,2,0)

VLOOKUP 是一个有难度，但是非常基础的函数，学习 Excel 都必须要掌握。

参数 1，要查询的内容，这里的籍贯编码。

参数 2，要查询的范围，要把查询和返回列都包含进来，这里的「籍贯编码查询表」的 A 和 B 列。

参数 3，要返回哪一列，B 列相对于 A 列是第 2 列，所以这里写 2。

参数 4，精确或模糊匹配，初学者统一写 0，等你可以盲打 VLOOKUP 了，再学习 1 的用法。

本文作者：拉登老师，《和秋叶一起学 Excel》等多门课程开发老师，小报告日程管理表、公式 plus 开发者。