用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年的数据变化的动态图!
加入圈子,一起学习:
心情至上的五大星座文西阿兔1巨蟹座巨蟹座无法战胜心情对自己的影响和判断,有时候会让自己的情商变的很低,心情不好的时候,敢跳起来挑战权威,丝毫不顾全大局,幸好当巨蟹座的坏心情被释放以后,TA们有能力去
十二星座谁最暖?十二星座暖男大排行,你的男友排第几?十二星座谁最暖?十二星座暖男大排行,你的男友排第几?在恋爱当中,每个女孩子都希望自己的男朋友很暖,对自己很宠爱,但是暖一定要有个限度,毕竟暖过头,可就是渣了。那大家想不想知道十二星
每个星座都有一首歌,快来看看属于你的专属歌曲!(一)每个星座都有他们各自的性格特点,他们或阳光或含蓄,或热情似火,或害羞腼腆。这里有属于十二个星座的十二首歌曲,还不来看看属于你这个星座的是啥歌曲。摩羯座(开不了口)说起摩羯座,怪侠有
表面嘻嘻哈哈,内心却十分孤独的四大星座表面嘻嘻哈哈,内心却十分孤独的四大星座人性的复杂要远远超出我们的想象,很多人都是表面一样,内心世界却又是另一番景象。生活中的不易让我们很多人都戴上了面具,外表看似靓丽光鲜,其实内心
十二星座代表的电视剧,一首歌概括十二星座的性格十二星座代表的电视剧十二星座代表的电视剧,白羊座一法医秦明,金牛座一漂亮的李慧珍,双子座大唐荣耀,巨蟹座一起来看流星雨,狮子座锦绣未央,处女座麻辣变形计,秤座微微一笑根倾城,天蝎座
没勇气表白的星座金牛座金牛座人性格胆怯,特别是在面对爱情的时候。就算非常喜欢一个人,他们也只是有胆量暗恋,却没勇气告白!明明已经关注很久,爱慕很久,却总是在遇到TA的时候,无法开口说出那简简单单却
3月下旬,这几个痴情的星座,终于等来了旧爱求复合的好消息!射手座。射手向往自由自在的生活,同时他们对于被爱情所束缚的自己也会有些期待,射手并不是不会改变,只看他们是不是遇到了对的人。有些失去让射手看清楚了自己的心,原来这就是真正的爱情,一
不会拍马屁的3大星座女不会拍马屁的3大星座女TOP3摩羯女摩羯女说话从来就不知道委婉,也绝对不会拐弯抹角,她们觉得嘴巴太舔的人就是谎话精,不值得深交,所以她们不屑用甜言蜜语去维护感情。不会拍马屁的3大星
保护欲跟占有欲都很强烈的星座最幸福的事情就是被保护,被喜欢的人保护,时时刻刻的担心你的安危,在你遇到危险的时候他总是可以第一时间出来,这确实很让人感动,保护欲太强烈了没什么不好,至少说明你在他心目中的地位不一
3月下旬,良缘靠近,难忘过往,朝思暮想挽旧情的星座!当深爱的人慢慢离去,过往的爱情也随风飘散,内心的坎坷也出卖了自己看似平淡的心情,一段消失的爱情自然会让很多人感觉到痛苦,但是曾经的真爱,毕竟也是深爱过的人,即使分手了,可是内心仍然
温柔体贴,最值得托付终身的星座男谁都会希望自己的另一半可以体贴自己,特别是女生,对于女孩子来说一个温柔体贴的男朋友非常难得,因为温柔体贴一般都是男生对女生的要求。今天我们就来看看12星座中,哪个星座男既温柔又体贴