在excel表格中如何使用身份证号码查询出:出生日期、年龄、性别、籍贯 这些信息呢?首先做一个如下图的表格出来

在excel表格中如何使用身份证号码查询出生日期、年龄、性别、籍贯?-趣帮office教程网

提取出生日期

当我们在B2单元格输入身份证号码时,我们希望B3单元格能够立马提取出生年月日的信息,

所以我们要在B3单元格输入公式:=TEXT(MID(B2,7,8),"0-00-00")

公式解析:用MID函数提取身份证号码中出生年月日的数字,出生年月日在身份证号码的第7位-15位,提取出来后只是一串数字,需要将它转换为日期格式,所以用TEXT函数将数字转换为日期。

在excel表格中如何使用身份证号码查询出生日期、年龄、性别、籍贯?-趣帮office教程网

计算年龄

在B4单元格输入公式:=DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"Y")

公式解析:计算年龄先要得到出生日期,所以直接复制出生日期的公式过来,然后再前面嵌套DATEDIF函数计算出生日期到今天相差了多少年即可。

在excel表格中如何使用身份证号码查询出生日期、年龄、性别、籍贯?-趣帮office教程网

判断性别

身份证号码中代表性别的数字是第17位,如果数字是奇数则为男生,偶数则为女生。

所以在B5单元格输入公式:=IF(ISEVEN(MID(B2,17,1)),"女","男")

公式解析:用MID函数提取第17位数字,然后用ISEVEN函数判断数字是否是偶数,如果是偶数则返回逻辑值true,否则返回false,最后用if函数返回性别。

在excel表格中如何使用身份证号码查询出生日期、年龄、性别、籍贯?-趣帮office教程网

提取籍贯

身份证号码前两位数代表籍贯,此外,我们需要在网上下载一份籍贯对照表,如下图。

在excel表格中如何使用身份证号码查询出生日期、年龄、性别、籍贯?-趣帮office教程网

然后在B6单元格输入公式:=VLOOKUP(--MID(B2,1,2),省份信息!$A$2:$B$35,2,0)

公式解析:用MID函数提取前2位数字,MID函数提取的数字属于文本格式的,而对照表里面的编码是数值格式,所以需要在MID函数前面加两个-(负号)进行负负运算,将文本格式转为数值格式,再用VLOOKUP函数跨表查找即可。

在excel表格中如何使用身份证号码查询出生日期、年龄、性别、籍贯?-趣帮office教程网