1、從身份證號中提取出生日期=TEXT(MID(A2,7,8),"0-00-00")
2、從身份證號中提取性別=IF(MOD(MID(A2,17,1),2),"男","女")
3、通過出日期計算年齡=DATEDIF(A2,TODAY(),"y")
4、計算入職時長=DATEDIF(A2,NOW(),"y")&"年"&DATEDIF(A2,NOW(),"ym")&"個月"
5、手機號隱藏中間四位=LEFT(A2,3)&"****"&RIGHT(A2,4)
手機號隱藏後四位=LEFT(A2,7)&"****"&RIGHT(A2,0)
6、部門員工人數統計=COUNTIF(B:B,"部門A")
7、考勤統計表假設上班時間為8：00，下班時間為17：00上班情況=IF(B2="","未打卡",IF(B2>TIME(8,0,0),"遲到"," "))下班情況=IF(C4="","未打卡",IF(C4<TIME(17,0,0),"早退"," "))
8、合並多列數據=CONCATENATE(A2,B2)
9、退休日期計算=TEXT(EDATE(A2,12*(5*(B2="男")+55)),"yyyy/mm/dd aaaa")
10、按權重計算績效得分=SUMPRODUCT(B$2:E$2*B3:E3)