excel身份证信息提取公式

  1. 根据身份证号判断性别
    =IFERROR(IF(ISODD(MID(身份证号,17,1)),"男","女"),"")
  2. 提取年龄
    =IFERROR(DATEDIF(TEXT(MID(身份证号,7,8),"0-00-00"),TODAY(),"Y")+1,"")
  3. 提取生日
    =IFERROR(TEXT(MID(身份证号,7,8),"0-00-00"),"")
  4. 根据生日判断是否成年
    =IFERROR(IF(DATEDIF(生日,TODAY(),"Y")>=18,"已成年","未成年"),"")
  5. 根据性别、年龄判断是否适婚
    =IFERROR(IF(性别="男",IF(年龄-1>=22,"适婚","不适婚"),IF(年龄-1>=20,"适婚","不适婚")),"")
  6. 根据性别、生日提取退休时间
    =IFERROR(IF(性别="男",YEAR(生日)+60&RIGHT(生日,6),YEAR(生日)+55&RIGHT(生日,6)),"")
  7. 根据身份证号提取生肖
    =IFERROR(MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(身份证号,7,4),12)+1,1),"")
  8. 根据身份证号提取星座
    =IFERROR(IF(身份证号="","",LOOKUP(--TEXT(MID(身份证号,11,4),"0-00"),--{"1/21","2/20","3/21","4/21","5/22","6/22","7/23","8/22","9/24","10/24","11/23","12/23"},{"水瓶","双鱼","牧羊","金牛","双子","巨蟹","狮子","处女","天秤","天蝎","射手","摩羯"}&"座")),"")
  9. 生日提醒
    =IFERROR("距离生日还有"&365-VALUE(DATEDIF(生日,TODAY(),"YD"))&"天","")
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容