用Excel从身份证号提取地区,生日,性别,年龄,生肖,星座等
身份证号非常重要,里面隐藏了很多信息,这些信息发生泄露,就很危险,今天我们用Excel来提取身份证号里面的各种信息
1、从身份证中提取地区,省市县
身份证号的前6位中就隐藏着这些信息,1-2位表示省,第3-4位表示市,第5-6位表示县,我们首先下载一份身份证地区码
然后我们对A列的身份证号进行提取数字,然后进行查找匹配地区表
在B2查找省份输入公式:
=VLOOKUP(LEFT($A2,2)&"*",身份证地区码!$A:$B,2,0)
在C2查找城市输入公式:
=VLOOKUP(LEFT($A2,4)&"*",身份证地区码!$A:$B,2,0)
在D2查找区县输入公式:
=VLOOKUP(LEFT($A2,6),身份证地区码!$A:$B,2,0)
身份证号是模拟的,倒数34位用X隐藏
2、提取生日
身份证号的第7位至第13位是表格出生的年月日,在E2单元格中输入公式:
=TEXT(MID(A2,7,8),"0-00-00")
3、提取年龄
已经把出生年月日提取出来了,我们只需要使用
=DATEDIF(E2,TODAY(),"Y")来计算年龄
4、提取性别
身份证号的倒数第2位,也就是第17位,如果是奇数,那么为男性,如果为偶数,则为女性,所以在G2单元格中输入公式:
=IF(MOD(MID(A2,17,1),2),"男","女")
5、提取生肖
每12年生肖进行一次的轮换,所以使用公式:
=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊"",MOD(MID(A2,7,4),12)+1,1)
6、提取星座
首先我们要知道星座的时间区间如下所示:
根据上面的规则,我们在Excel里面K:L列建立辅助项,如下所示:
然后我们再使用VLOOKUP函数的模糊查找功能,输入公式:
=VLOOKUP(MID(A2,11,4)*1,K:L,2,1)
如果不想有辅助列,那么数据中间的数据源K:L可以使用一个数组来替换
输入的公式是:=VLOOKUP(MID(A2,11,4)*1,{0,"摩羯座";120,"水瓶座";219,"双鱼座";321,"白羊座";420,"金牛座";521,"双子座";622,"巨蟹座";723,"狮子座";823,"处女座";923,"天秤座";1024,"天蝎座";1123,"射手座";1222,"摩羯座"},2,1)
所以仅仅根据一个身份证号,比如:33018319931224XX41,就可以知道这个人是浙江杭州市富阳区的25岁的女生,属鸡,摩羯座,生日是1993年12月24号。
关于身份证号里面的这些信息,你了解了么?
往期精彩:
Excel打印必学的小技能,一年省一坨纸!
Excel自定义格式三分号解读,发挥大作用!
Excel制作抖音很火从1990-2017年的数据变化的动态图!
加入圈子,一起学习:
守口如瓶的星座,信得过,靠得住,值得深交一辈子有人说,守口如瓶是一种品质,更是一个人最大的一种修行。简单地说,守口如瓶并不是沉默寡言,守得住秘密这么简单,它还是一种人生智慧,只有真正的智者,才能领悟它,并且真正实践它。比如,三
不太会聊天的星座金牛座金牛座忘性大,和你聊天,基本上聊着聊着就忘了自己还在和你聊天。抬头看一眼墙上的时钟,发觉自己应该去觅食了,或是该好好认真赚钱,于是转头就把你遗忘了。巨蟹座巨蟹座反应慢半拍,其
摩羯座与这个星座的幸运与巧合,绝对是上天注定!今天小编为大家带来的内容是摩羯座与这个星座的幸运与巧合,绝对是上天注定!摩羯座是十二星座中最难脱单的星座之一。他们之所以难脱单,一是因为自己条件过高,二是因为摩羯座要求对方比自己条
最适合裸婚的四星座第一种爱你没商量的狮子座颇具王者风范的狮子在面对爱情时的态度不仅坚决,而且很有牺牲自我的精神。当狮子确定自己爱上一个人时,往往会以大无畏精神去承担爱情的后果,愿意忽视掉优越的物质环
谁反对都无效,恋爱只信自己的星座谈自己的恋爱,让别人说去吧!是谁,最能用行动表示这一点?不怕任何人反对,就算顶着最大的恋爱压力,也能跟随自己的心,跟谁恋爱只能自己决定?在这问题上,摘星工厂星吧非常看好这些星座!双
5月开始,真情朝朝暮暮,未来希望满满的3星座,爱情一路走到底朝朝暮暮的真情让你会幻想到以后的甜蜜,很多人也希望自己的遗憾和爱情无关,所有向往爱情的人,都希望找到自己最爱的人,朝朝暮暮的真情一旦出现,又怎是一朝一夕就能忘记,遇到爱情的人,都会
六个星座在炎热环境中有什么样的表现?今天的天气潮热,吃个饭就满头大汗,汗流浃背,让人想去洗澡。写文章时想起这个事,觉得以星座在炎热环境中的表现为话题去写作,你觉得如何?白羊座这是个热情的人,炎热对他来说就像我们平时舒
哪些星座有孩子气行为?今天中午吃完饭去送孩子上学,他在路上蹦蹦跳跳,叽叽喳喳说个不停,让人羡慕。那么哪些星座有孩子气行为呢?双子座双子座的人像孩子般任性,说变就变,喜欢搞恶作剧,会给人带来欢乐也会给人带
星座与人因为怕被甩所以不敢恋爱的星座感情真的需要好好经营天蝎座在恋爱的时候天蝎座就非常喜欢怀疑自己的恋人,天蝎座的人总是怕对方背叛自己,甚至在这段感情开始的时候就会产生各种各样悲观的想法,生怕被对方甩了,有这些想法以后天蝎座可能会非常可
最精通男人心的星座女,就没有她们拿不下的人!又到了每日一扒环节啦今天扒婆要跟你们唠嗑的是最精通男人心的三个星座女快一起来看看都有谁吧有的女孩子天生就善解人意她们精通男人的心,懂得对症下药倒追起男人来就像隔层纱,出奇必胜毫不费
一旦生气怎么哄都没用的星座!双鱼座双鱼座人都是属于情感丰富的人,同时又极其善于伪装自己的情绪。所以即便她们内心产生了异样的情绪,她们也不会让你看出来,而是默默藏在心里,直到你感受到了她的淡漠,你才会知道原来她