HR的数据库分毫不差，统计表也被玩得出神入化；画的一手好表格，深受boss喜欢。以下总结与身份证相关公式EXCEL技能分享给大家：
方法/步骤
1、獠钉桂赐身份证验证公式=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("稆糨孝汶;1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),IF(LEN(C3)=15,ISNUMBER(--TEXT(19&MID(C3,7,6),"#-00-00"))))
2、提取性别公式=CHOOSE(MOD(MID(A2,LEN(A2)/2+8,1),2)+1,"女","男")；或公式=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,"男","女")。
3、判断生肖公式=CHOOSE(MOD(MID(A2,LEN(A2)/2,2),12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")；以上A2为身份证数据源
4、提取出生日期公式=TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"#-##-##")；
5、提取年龄（整岁）公式=INT(DAYS360(TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"#-##-##"),TODAY())/360)；
6、判断星座公式倦虺赳式=VLOOKUP(VALUE("1900-"&TEXT(MID(A2,LEN(A2)/2+2,4),"#-##")),{1,&q锇栀劐箨uot;摩羯座";21,"水瓶座";50,"双鱼座";81,"白羊座";112,"金牛座";143,"双子座";174,"巨蟹座";205,"狮子座";236,"处女座";268,"天秤座";298,"天蝎座";328,"人马座";357,"摩羯座"},2,TRUE)；
7、15位转换为18位公式=IF(LEN(C2)=15,REPLACE(C2,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(C2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),C2)。