【Excel】从身份号码提取信息:性别/年龄/地区/出生日期

大家好，我是木木^^

如何能快速的批量从身份证号码中提取出生日、性别、年龄、地区等信息？我们一个一个来看：

【提取出生日期】

公式：=TEXT(MID(A2,7,8),”0000-00-00″)

这里用到2个函数：TEXT 和 MID：

TEXT函数 是将内容转换为指定格式的文本：

它的公式：=TEXT(内容，格式)

在本文中，就是把提取到的8位数字，用1980-03-23的格式展现出来

其中0000-00-00：这个0是占位符

MID函数 是从文本中提取指定位置开始的特定数量字符

它的公式：=MID(取值，第几个字符开始，取几位)

在本文中，就是从身份证号码中，从第7位开始，提取8位数值

【提取年龄】

公式：=DATEDIF(B2,TODAY(),”Y“)

这里用到DATEDIF函数，它用于计算两个日期之间的间隔：

它的公式：=DATEDIF(开始日期，结束日期，返回值)

其中返回值”Y“表示年，还可以返回月（M）、返回天数（D）

today()：此前有介绍过，代入今天的日期——还有另一个和它很像的函数now()，大家可以在excel中分别输入，看看两个函数有什么区别

这里再稍微拓展一下：

我们也可以把公式换成：=year(today())-MID(A2,7,4)

大家会发现部分结果可能跟上面相差1——原因是datedif计算结果是周岁，而这里统计的是虚岁；

【提取性别】

公式： =IF(MOD(MID(A10,17,1),2)=0,”女”,”男”)

这里利用到的规则是：身份证号码的第17位，若是偶数则为女，奇数则为男，用到了三个函数：IF、MOD、MID：

MID函数：前面提取出生日期时用过——从文本中提取指定数量字符

它的公式：=MID(取值，第几个字符开始，取几位)

MOD函数：返回的是两个数相除之后的余数

它的公式：=MOD(数值，除数)

在本文中表示：用 身份证号码的第17位数 除以 2 的余数

偶数÷2的余数为0

IF函数：根据条件返回不同的值：

它的公式：=IF(条件，符合返回值，不符合返回值)

在本文中：如果计算得出的值=0，则返回“女”，否则返回“男”

【提取省份、地区】

公式：

省份=VLOOKUP(LEFT(A2,2),全国地区表!A:B,2,0)

地区=VLOOKUP(LEFT(A2,2),全国地区表!A:B,6,0)

其中：身份证号码前两位是省份代码，前六位是地区代码——对照表可以从网上下载。

这里用到2个函数：vlookup函数，和LEFT函数：

VLOOKUP函数：竖向查找返回，非常常用的函数

它的公式：=VLOOKUP(查找值，查找范围，第几列，精确/模糊)

本文用到的是vlookup的跨表查询引用：根据2位/6位数字，查找地区表中对应的省/地区，地区在表中第二列故为2，0是精确查找

LEFT函数：是从文本左侧开始提取指定数量的字符

它的公式：=LEFT(文本，取几位)

取几位——也可省略，省略则默认为1，即：取1位

总结一下，今天练习了批量从身份证号码中提取出出生日期、性别、年龄、省份、地区：

出生日期=text(mid(A2,7,8),”0000-00-00″)

年龄=DATEDIF(B2,TODAY(),”Y”)

性别=IF(MOD(MID(A2,17,1),2)=0,”女”,”男”)

省份=VLOOKUP(LEFT(A2,2),全国地区表!A:B,2,0)

地区=VLOOKUP(LEFT(A2,2),全国地区表!A:B,6,0)