咱们每个人都有一个唯一的身份证号,其实身份证号中包含了很多的个人信息,你知道怎么从身份证号中提取个人的生日、年龄、性别、籍贯等信息吗?技巧君今天和大家分享一下如何利用Excel从身份证号中提取个人信息!

身份证号的构成

身份证号有18位,排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。顺序码的奇数分给男性,偶数分给女性。

如何用身份证号码查个人信息(教你用Excel从身份证号中提取个人信息)-天天办公网

身份证号构成

从这个构成中,我们可以发现,只需要提取对应的编码就可以提取出身份证号中的个人信息!

提取性别

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

如何用身份证号码查个人信息(教你用Excel从身份证号中提取个人信息)-天天办公网

身份证号提取性别

说明:
MID(B2,17,1),提取第17位数字
MOD(MID(B2,17,1),2),对2取余,奇数余数得1,偶数余数为0

提取出生年月

=--TEXT(MID(B2,7,8),"0-00-00")

如何用身份证号码查个人信息(教你用Excel从身份证号中提取个人信息)-天天办公网

提取出身年月

说明:
MID(B2,7,8),提取第7位开始后面的8位数字,即出生年月日编码
TEXT(MID(B2,7,8),"0-00-00"),转换为年-月-日的格式
--TEXT(MID(B2,7,8),"0-00-00"),减负运算换算为真正的日期

提取实岁

=DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"Y")

如何用身份证号码查个人信息(教你用Excel从身份证号中提取个人信息)-天天办公网

提取实岁

说明:
DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"Y"),计算当前日期和出生日期的年差值,即实际岁数

提取虚岁

=(YEAR(NOW())-MID(B2,7,4))

如何用身份证号码查个人信息(教你用Excel从身份证号中提取个人信息)-天天办公网

提取虚岁

说明:
MID(B2,7,4):提取第7位数开始的4位数字,即出生年份
YEAR(NOW()):计算当前日期的年份
(YEAR(NOW())-MID(B2,7,4)):虚岁=当前年份-出生年份

提取籍贯

=VLOOKUP(--MID(B2,1,6),籍贯编码!B:C,2,0)

如何用身份证号码查个人信息(教你用Excel从身份证号中提取个人信息)-天天办公网

提取籍贯

说明:
--MID(B2,1,6),提取前六位地址编码
VLOOKUP(--MID(B2,1,6),籍贯编码!B:C,2,0),在地址编码表里找到籍贯