教你用Excel表把身份证号中提取出“出生日期”“性别”“籍贯”

身份证号码中包含每个人的“出生日期”，但大多数人还不知道这一串数字中还包含了很多隐藏的信息,今天,我们就用EXCEL从中“探囊取物”，一起看个究竟！

今天的主题：

用excel函数和公式，从身份证号中提取出“出生日期”“性别”“籍贯”，并计算“当前年龄”。

1、从身份证号中提取“出生日期”

=--Text(mid($B3,7,8),'0-00-00')

使用函数：text、mid

要点提示：

（1）利用mid函数，从18位身份证号码中，取出从第7位开始的8个数字

（2）利用text函数，将取出的8个数字，生成日期的数值形式

（3）最后，通连续的减号“--”，将数值转换成日期的文本形式

（4） 依次往下复制、填充

补充：excel中日期规范形式：“2018-5-7”、“2018/5/7”，不能写成“20180507”、'2018.5.7'、'2018、5、7'

2、从身份证号中提取“性别”

方法一：

=IF(MID($B3,17,1)/2=TRUNC(MID($B3,17,1)/2),'女','男')

方法二：

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

使用函数：if 、mid、trunc、mod

要点提示：

（1）利用if函数，判断非“男”，即“女”

（2）if函数的“判断条件”，用mid提取出的第17位数除以2，看是否等于trunc函数判断第17位数除以2的整数部分

（3）或者直接用mod函数，对第17位数除以2，求“余数”，>0为“男”，=0为“女”

（4） 依次往下复制、填充

补充知识：身份证号倒数第2位数，奇数为“男”，偶数为“女”

3、计算身份证号当事人当前“年龄”

方法一：

插入P列、Q列辅助列

“P3”单元格输入：=year(today())

“Q3”单元格输入：=mid(O3,7,4)

“R3”单元格输入：=P3-Q3

方法二：

=YEAR(TODAY())-YEAR(--TEXT(MID($B3,7,8),'0-00-00'))

使用函数：year、today、text、mid

要点提示：

1）today()指的是电脑的当前日期，再利用year函数，返回当前的年份值

2）复杂函数的应用，并不是高手的象征，而简便、快速解决问题才是王道！所以，有时辅助列的运用会更加简便。

3）依次往下复制填充

4、提取身份证号中的“籍贯”

=VLOOKUP(LEFT(B2,6),身份证籍贯代码!$A$1:$B$3000,2,0)

使用函数：vlookup、left

要点提示：

1）利用left函数，从身份证号中取“左边第1位数开始的6个数字”

2）利用vlookup，对left取出的条件数值，在身份证籍贯代码表区域，第2列，精确查找值

3） 依次往下复制、填充

补充知识：身份证号前6位数字。是按一定籍贯规则编排的，可在百度查找“身份证籍贯代码”，也可以向我“你们瞅我来气不”索要！