在《EXCEL告诉你身份证中的秘密》一文中,我们详细讲解了从身份证号中提取“出生日期”、“性别”、“籍贯”的方法,今天,继续用EXCEL来挖掘一组暖心数据,教大家如何通过身份证号算生肖、查星座,算生日倒数日,甚至还能算出农历年。暖心的HR可以收藏此帖,到时只要复制文中的公式就可以使用了。
废话少说,直接上图、上公式!
1、算生肖。生肖是按农历年份计算的,所以,先要从身份证号中取出公历日期、农历日期。
提取公历出生日期:
C2单元格输入公式:
=TEXT(MID(B2,7,8),"0-00-00")
从身份证号中提取公历生日
根据公历日期算农历日期,
D2单元格输入公式:
=TEXT(C2,"[$-130000]YYYY-M-D")
从公历日期中查农历日期
算生肖:
E2单元格输入公式:第一种方法:
=CHOOSE(MOD(YEAR(D2)-4,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊",
"猴","鸡","狗","猪"),第二种方法:
=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(D2)-4,12)+1,1)
从农历日期中查生肖
2、查星座。
在
I2单元格输入公式:
=VLOOKUP(--TEXT(MID(B2,11,4),"2015-00-00"),{0,"摩羯";42024,"水瓶";42054,"双鱼";42084,"白羊";42114,"金牛";42145,"双子";42177,"巨蟹";42208,"狮子";42239,"处女";42270,"天秤";42301,"天蝎";42330,"射手";42360,"摩羯"},2)
从身份证号中查星座
3、算生日倒数日
在
J2单元格中输入公式:
=IF(TEXT(C2,"M-D")-TODAY()>0,TEXT(C2,"M-D")-TODAY(),DATE(YEAR(TODAY())+1,MONTH(C2),DAY(C2))-TODAY())
从公历日期中算生日倒数日
4、算农历年
在
F2单元格中输入公式:
=MID("甲乙丙丁戊己庚辛壬癸",RIGHT(YEAR(D2))+1,1)&MID("子丑寅卯辰巳午未申酉戌亥",MOD(YEAR(D2),12)+1,1)&"年"
根据农历日期算农历年份
学习函数,学写公式,灵活应用,就能快速解决实际工作中的数据处理问题,甚至还会有意想不到的收获。愿大家每天精进!
版权所有 Copyright © 2016-2023 【紫色星座】 备案编号:皖ICP备2022002502号