圖1
前2位表示省份程式碼,3~6位表示城市和區縣程式碼,7~14位表示出生日期,15~17位是個人順序碼,第17位表示性別(奇數為男性,偶數為女性)
第一項、從身份證號提取:出生年月日人事部門在錄入公司員工資訊表,如下圖2所示,紅色框選的這5列如果你能利用函式來輔助錄入資料方能實現高效辦公。
圖2
如下圖3所示,在H3單元格中輸入=DATE(MID(G3,7,4),MID(G3,11,2),MID(G3,13,2)),可從G3單元格提取出生年月日。
函式解析:1、DATE日期函式語法:DATE(年,月,日)。2、MID字元長度提取函式語法:MID(要提取的字串單元格,第一個字元的位置,返回字元的個數)如本文MID(G3,7,4)意思是從身份證號的第7位開始,提取4個數。MID(G3,11,2)意思是從身份證號的第11位開始,提取2個數。MID(G3,13,2)意思是從身份證號的第13位開始,提取2個數。
第二項、從身份證號提取:性別從上面【導語】中我們知道了身份證號第17位數字是判斷性別,奇數是男性,偶數是女性。利用這一特點進行判斷性別。如下圖4所示。
圖4
在C3單元格中輸入=IF(MOD(MID(G3,17,1),2)=0,"女","男")函式解析:1、MOD返回兩數相除的餘數語法:MOD(要計算餘數的被除數,除數)
圖5
如上圖5所示。本文用MID從G3單元格中的第17位數除以2。2、IF條件判斷函式語法:IF(條件,結果1,結果2)如果條件成立返回結果1,條件不成立返回結果2。
圖6
如上圖6所示。最後用IF判斷如餘數是0,則為偶數,如餘數是1,則為奇數。3、MID函式在上面提取出生年月日中已介紹過了這裡不在講解。
第三項、從身份證號提取:年齡從上面【導語】中我們知道了身份證號第7~14位數出生年月日,我們利用這一特點進行提取年份如下圖7所示。
圖7
在D3單元格中輸入=YEAR(TODAY())-MID(G3,7,4),可從G3單元格提取年份。函式解析:1、TODAY返回當前日期函式(今天日期)語法:TODAY()2、YEAR返回日期的年份值函式語法:YEAR(要查詢的年份日期)YEAR(TODAY())-MID(G3,7,4)意思是YEAR(當前日期-出生日期)這樣就等到年齡了。
第四項、從身份證號提取:省市地區從上面【導語】中我們知道了身份證號第1~6位數是所屬省市地區,我們利用這一特點進行提取省市地區如下圖8所示。
圖8
如果沒有省市地區程式碼的小夥伴們可從百度搜索下載,做成Excel工作表儲存。
如下圖9所示,在E3單元格中輸入=VLOOKUP(--LEFT(G3,6),地區程式碼!A2:B46,2,0)
圖9
可從圖8地區程式碼工作表A2:B46單元格提取省市地區。函式解析:1、LEFT從第一個字元開始提取文字字串數量函式語法:LEFT(文字字元單元格,[要提取的字元數量]),中括號內容是可選項。如本文--LEFT(G3,6)的意思是在G3單元格中從從第一個字元開始提取6位數,剛好是身份證號前6位數了,就是省市地區程式碼。LEFT函式前加“--”兩個減號的意思如果G3單元格不是文字的話就按文字處理2、VLOOKUP查詢函式(也叫對比函式)語法:VLOOKUP(要查詢內容,要查詢區域,查詢區域中的第幾列,精確查詢為0)
圖10
如上圖10所示。用VLOOKUP以身份證號前6位數為查詢內容,在“地區程式碼“工作表A2:B46區域中查詢,如果找到以身份證號前6位數相同就返回第2列中對應資料。
第五項、從身份證號提取:生日提醒現在有很多企業在員工生日當天給員工發生日禮物,這裡我給大家寫了Excel中如何實現生日提醒函式。如下圖11所示。
圖11
在K3單元格中輸入=TEXT(TEXT(H3,"m-d")-TEXT(NOW(),"m-d"),"還有0天;;今天是我的生日")有了生日提醒了。函式解析:1、TEXT數值換成文字函式語法:TEXT(日期值,mm-dd-yy) 如本文TEXT(H3,"m-d")意思是把H3日期單元格轉換成“月(m)-日(d)“2、NOW返回當前日期和時間函式語法:NOW()NOW函式語法沒有引數先用TEXT將日期轉變成月日再進行相減,如果大於0就顯示“還有多少天“,如果小於0就不顯示,0就是今天是我的生日。
親愛的小夥伴們本期內容結束了,給小夥伴們留一題作業。經常坐火車會發現,你的火車票上身份證號碼的出生年月日8位數被替換成“********”。如下圖1所示。
圖12
小小聲提示用REPLACE函式可以了。