户口所在地代码查询(单位所在地代码怎么查)
户口所在地代码查询(单位所在地代码怎么查)原创千年古月杨2021-04-25 23:03:24巧取身份证六大信息
(申明:本文所用身份证号均为随机组合而成,非真实身份证号,如有雷同,纯属巧合)
在实际的办公应用中,大量人员信息处理是件很繁琐的事情,包括有身份证号、出生日期、性别、年龄、籍贯,甚至是个人生肖、星座等,那么如何利用身份证号快速自动的录入相关信息,实现又快又准的完成工作。下面就以我国身份证的信息特点,利用身份证号,在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函数的各种用法,需读者在不断学习中理解。
收藏举报0条评论
林峰的妹妹(林峰家里成员)林峰的妹妹(林峰家里成员)中国网20190918093744熟悉林峰这个演员的人应该都知道,林峰还有一个妹妹在娱乐圈,叫做林夏薇。出演过很多香港电视剧,也是一位大美女。一开始大家还
春秋饕餮螭龙纹(看清早期十类螭龙纹造型)春秋饕餮螭龙纹(看清早期十类螭龙纹造型)张辉,毕业于山东大学历史系考古专业,先后任职河北省博物馆河北教育出版社。1994年后,在北京多家出版社任策划组稿编辑,并创建北京紫都苑图书发
12星座电影(十二星座图案大全)12星座电影(十二星座图案大全)原创临时书架20200918180113世界上的电影千千万读者也有千千万当老天赋予我们不一样的灵魂那就注定我们会获得不一样的审美十二个星座十二部电影
天生抬头纹(抬头纹是怎么来的)天生抬头纹(抬头纹是怎么来的)额头上一旦形成皱纹,就叫做抬头纹,抬头纹一旦形成,就感觉衰老了好几岁,那么抬头纹是怎么造成的?下面就和小编一起看看吧。抬头纹是什么抬头纹指额部皱纹。抬
省内快递哪个便宜(全国物流价格明细表)文路世明编辑大风今年的双十一来得格外早。有位新疆网友在社交媒体上晒单表示自己在淘宝买的手机壳,价格只有13。9元,但包裹从广东深圳运到新疆伊犁,跨越了大半个中国,居然还能包邮!据央
夏商制度与西周封建(高中必修一夏商制度与西周封建)夏商制度与西周封建(高中必修一夏商制度与西周封建)回想起来高中的学习生活,真是感慨万千,希望用最简洁的方式,记录我当时学习历史的思路与思维。先秦时代夏朝是第一个国家政权,产生了公共
蚂蚁庄园今日问题藕断丝连的丝作用是什么支付宝的蚂蚁庄园小鸡问答是每天都会有不同的答题出现,一方面丰富我们的知识,另一方面也增加了养护小鸡的乐趣,就像今天支付宝蚂蚁庄园小鸡问答就出现了藕断丝连的丝作用是什么,那么这样的问
身躯凛凛相貌堂堂(武松的外貌特征)身躯凛凛相貌堂堂(武松的外貌特征)武松长什么样,水浒传中写得很清楚。但是,要完整地描述这个广为人知的人物形象,准确地概括武松的外貌特征,还真不太容易。尤其是按照现在的标准去看武松,
想查一个人入住酒店记录(怎么查在哪住过宾馆)想查一个人入住酒店记录(怎么查在哪住过宾馆)实践日子中酒店的入住记载怎样查?假定说你有入住宾馆记载,那么就能够直接进入处理网络去查询自己的一个个人信息,所以在日常日子傍边,人们仍是
数码知识vivoy70s有没有红外线有红外线功能吗如今使用IT数码设备的小伙伴们是越来越多了,那么IT数码设备当中是有很多小技巧的,这些技巧很多小伙伴一般都是不知道如何来实用的,就好比最近就有很多小伙伴们想要知道vivoy70s有
小s钢管舞(女星钢管舞)小s钢管舞(女星钢管舞)原创晋说晋20210315185735最近汪小菲陪大S回台北丈母娘家,小S也回娘家和姐姐姐夫凑热闹。活泼的小姨子看着姐夫温文尔雅,缠着汪小菲跳热辣性感舞,搞