巧取身份证六大信息
巧取身份证六大信息
(申明:本文所用身份证号均为随机组合而成,非真实身份证号,如有雷同,纯属巧合)
在实际的办公应用中,大量人员信息处理是件很繁琐的事情,包括有身份证号、出生日期、性别、年龄、籍贯,甚至是个人生肖、星座等,那么如何利用身份证号快速自动的录入相关信息,实现又快又准的完成工作。下面就以我国身份证的信息特点,利用身份证号,在excel表格中巧妙提取身份证六大信息,快速轻松完成人员信息处理。
当前,我国的身份证号码为18位,如图1,其中里面的信息特点为,第1到6位为全国行政户籍区划代码,第7到14位为出生日期数,倒数第2位为性别代码,偶数为女,奇数为男。我们可以利用excel中的函数,根据已输入的身份证号码,快速提取相关信息,准确完成人员信息处理。
图1
一.身份证号提取出生日期
函数公式:=IFERROR(TEXT(MID(B2,7,8),'0-00-00'),'')
说明:
a.MID(B2,7,8)提取出生日期码
b.TEXT函数将文本处理为日期格式
c.外层嵌套IFERROR函数是为了空单元格规避错误值
如图2所示:
图2
二.身份证号提取岁数
函数公式:=IFERROR(DATEDIF(--TEXT(MID(B2,7,8),'0-00-00'),TODAY(),'y'),'')
说明:
a.MID(A2,7,8)提取出生日期码
b.TEXT函数将文本处理为日期格式,--减负运算将文本转化为日期值
c.DATEDIF函数计算出生日期和今天的差值,即得到年龄
d.外层嵌套IFERROR函数是为了空单元格规避错误值
如图3所示:
图3
三.身份证号提取性别
函数公式:=IFERROR(IF(MOD(MID(B2,17,1),2),'男','女'),'')
说明:
a.MID(B2,17,1)函数提取第17位数
b.MOD函数对2取余数,偶数余数为0,说明为女性,奇数余数为1,说明为男性,再用IF函数来判断性别
c.外层嵌套IFERROR函数是为了空单元格规避错误值
如图4所示:
图4
四.身份证号提取生肖
函数公式:=IFERROR(MID('鼠牛虎兔龙蛇马羊猴鸡狗猪',MOD(YEAR(C2)-4,12)+1,1),'')
说明:
a.MID(B2,7,4)提取出生年份
b.MOD函数对12取余数,刚好对应12生肖的序列代码,里面日期的-4和外面的 +1是为了和前面的生肖相对应
c.外层嵌套IFERROR函数是为了空单元格规避错误值
d.身份证提取生肖是按公历计算的,我国生肖多数都是以农历计算为准,所以略有偏差,实际中需做适当调整
e.上面生肖的提取是以,身份证号提取的出生年月来提取计算的,实际中也可用身份证号直接提取阳历生肖,其函数公式为:
=IFERROR(IF(LEN(B9)=15,MID('鼠牛虎兔龙蛇马羊猴鸡狗猪',MOD('19'&MID(B9,7,2)-4,12)+1,1),MID('鼠牛虎兔龙蛇马羊猴鸡狗猪',MOD(MID(B9,7,4)-4,12)+1,1)),'')
如图5所示:(利用出生年月提取生肖)
图5
如图6所示:(利用身份证号直接提取生肖)
图6
五.身份证号提取籍贯
函数公式:=IFERROR(VLOOKUP(LEFT(B2,6),户籍行政区划代码表!A:B,2,1),'')
说明:
a.籍贯信息需要有户籍行政区划代码表作为附表,通过函数代码换算为地区就可以
b.户籍行政区划代码表,可以通过网络下载最新版的区划表,根据需求做好调整使用即可。
c.外层嵌套IFERROR函数是为了空单元格规避错误值
如图7、8所示:
图7
图8
六.身份证号提取星座
函数公式:=IFERROR(LOOKUP(--TEXT(C2,'mdd'),{101,'摩羯';120,'水平';219,'双鱼';321,'白羊';420,'金牛';521,'双子';621,'巨蟹';723,'狮子';823,'处女';923,'天秤';1023,'天蝎';1122,'射手';1222,'摩羯'})&'座','')
说明:
a. C2的数字转换为日期,取其中的月和日对应的数字,然后与后面的数字模糊匹配,找到比这个数字小的数字,然后取对应的星座。
b.TEXT(C2,'mdd') 是把C2的数字转换为日期,取其中的月和日对应的数字,在Excel里日期都是以一个数字来记录的,因此数字能转换为日期,这里不要年,只保留月和日,因为计算星座只要月日。
c.TEXT(C2,'mdd') 前面加两格减号--TEXT(C2,'mdd') 是把字符串转换为纯数字,得到的数字作为lookup函数的第一个参数,表示要查找的数字。
d.后面的{}里是查询数组,逗号表示是在同一行,分号表示下一行,这里就是一个数字一个逗号一个星座一个分号为一行,其实就是两列,第一列是数字,第二列是星座,第一列的数字是两个星座分隔的日期。
使用lookup函数查询前面那个数字在后面这个数组中哪个区间,然后就得到对应的星座了。
e.外层嵌套IFERROR函数是为了空单元格规避错误值
f.上面星座的提取是以,身份证号提取的出生年月日来提取计算的,实际中也可用身份证号直接提取星座,其函数公式为:
=IFERROR(LOOKUP(--MID(B2,11,4),{101,'摩羯';120,'水瓶';219,'双鱼';321,'白羊';420,'金牛';521,'双子';621,'巨蟹';723,'狮子';823,'处女';923,'天秤';1023,'天蝎';1122,'射手';1222,'摩羯'})&'座','')如图9所示:(利用出生年月提取星座)
图9
如图10所示:(利用身份证号直接提取星座)
图10
注:
- 在提取函数中都增加了一个函数IFFRROR,目的是为了避免空单元格的错误值,方便实际的录入操作,实际中可以根据情况可以不使用此函数。
- 出现的有些函数只做了部分用途功能介绍,对于excel函数的各种用法,需读者在不断学习中理解。