科技改變生活 · 科技引領未來
(申明:本文所用身份證號均為隨機組合而成,非真實身份證號,如有雷同,純屬巧合)
在實際的辦公應用中,大量人員信息處理是件很繁瑣的事情,包括有身份證號、出生日期、性別、年齡、籍貫,甚至是個人生肖、星座等,那么如何利用身份證號快速自動的錄入相關信息,實現又快又準的完成工作。下面就以我國身份證的信息特點,利用身份證號,在excel表格中巧妙提取身份證六大信息,快速輕松完成人員信息處理。
當前,我國的身份證號碼為18位,如圖1,其中里面的信息特點為,第1到6位為全國行政戶籍區劃代碼,第7到14位為出生日期數,倒數第2位為性別代碼,偶數為女,奇數為男。我們可以利用excel中的函數,根據已輸入的身份證號碼,快速提取相關信息,準確完成人員信息處理。
圖1
一.身份證號提取出生日期
函數公式:=IFERROR(TEXT(MId(B2,7,8),&34;0-00-00&34;),&34;&34;)
說明:
a.MId(B2,7,8)提取出生日期碼
b.TEXT函數將文本處理為日期格式
c.外層嵌套IFERROR函數是為了空單元格規避錯誤值
如圖2所示:
圖2
二.身份證號提取歲數
函數公式:=IFERROR(DATEDIF(--TEXT(MId(B2,7,8),&34;0-00-00&34;),TODAY(),&34;y&34;),&34;&34;)
說明:
a.MId(A2,7,8)提取出生日期碼
b.TEXT函數將文本處理為日期格式,--減負運算將文本轉化為日期值
c.DATEDIF函數計算出生日期和今天的差值,即得到年齡
d.外層嵌套IFERROR函數是為了空單元格規避錯誤值
如圖3所示:
圖3
三.身份證號提取性別
函數公式:=IFERROR(IF(MOD(MId(B2,17,1),2),&34;男&34;,&34;女&34;),&34;&34;)
說明:
a.MId(B2,17,1)函數提取第17位數
b.MOD函數對2取余數,偶數余數為0,說明為女性,奇數余數為1,說明為男性,再用IF函數來判斷性別
c.外層嵌套IFERROR函數是為了空單元格規避錯誤值
如圖4所示:
圖4
四.身份證號提取生肖
函數公式:=IFERROR(MId(&34;鼠牛虎兔龍蛇馬羊猴雞狗豬&34;,MOD(YEAR(C2)-4,12)+1,1),&34;&34;)
說明:
a.MId(B2,7,4)提取出生年份
b.MOD函數對12取余數,剛好對應12生肖的序列代碼,里面日期的-4和外面的 +1是為了和前面的生肖相對應
c.外層嵌套IFERROR函數是為了空單元格規避錯誤值
d.身份證提取生肖是按公歷計算的,我國生肖多數都是以農歷計算為準,所以略有偏差,實際中需做適當調整
e.上面生肖的提取是以,身份證號提取的出生年月來提取計算的,實際中也可用身份證號直接提取陽歷生肖,其函數公式為:
=IFERROR(IF(LEN(B9)=15,MId(&34;鼠牛虎兔龍蛇馬羊猴雞狗豬&34;,MOD(&34;19&34;&MId(B9,7,2)-4,12)+1,1),MId(&34;鼠牛虎兔龍蛇馬羊猴雞狗豬&34;,MOD(MId(B9,7,4)-4,12)+1,1)),&34;&34;)
如圖5所示:(利用出生年月提取生肖)
圖5
如圖6所示:(利用身份證號直接提取生肖)
圖6
五.身份證號提取籍貫
函數公式:=IFERROR(VLOOKUP(LEFt(B2,6),戶籍行政區劃代碼表!A:B,2,1),&34;&34;)
說明:
a.籍貫信息需要有戶籍行政區劃代碼表作為附表,通過函數代碼換算為地區就可以
b.戶籍行政區劃代碼表,可以通過網絡下載最新版的區劃表,根據需求做好調整使用即可。
c.外層嵌套IFERROR函數是為了空單元格規避錯誤值
如圖7、8所示:
圖7
圖8
六.身份證號提取星座
函數公式:=IFERROR(LOOKUP(--TEXT(C2,&34;mdd&34;),{101,&34;摩羯&34;;120,&34;水平&34;;219,&34;雙魚&34;;321,&34;白羊&34;;420,&34;金牛&34;;521,&34;雙子&34;;621,&34;巨蟹&34;;723,&34;獅子&34;;823,&34;處女&34;;923,&34;天秤&34;;1023,&34;天蝎&34;;1122,&34;射手&34;;1222,&34;摩羯&34;})&&34;座&34;,&34;&34;)
說明:
a. C2的數字轉換為日期,取其中的月和日對應的數字,然后與后面的數字模糊匹配,找到比這個數字小的數字,然后取對應的星座。
b.TEXT(C2,&34;mdd&34;) 是把C2的數字轉換為日期,取其中的月和日對應的數字,在Excel里日期都是以一個數字來記錄的,因此數字能轉換為日期,這里不要年,只保留月和日,因為計算星座只要月日。
c.TEXT(C2,&34;mdd&34;) 前面加兩格減號--TEXT(C2,&34;mdd&34;) 是把字符串轉換為純數字,得到的數字作為lookup函數的第一個參數,表示要查找的數字。
d.后面的{}里是查詢數組,逗號表示是在同一行,分號表示下一行,這里就是一個數字一個逗號一個星座一個分號為一行,其實就是兩列,第一列是數字,第二列是星座,第一列的數字是兩個星座分隔的日期。
使用lookup函數查詢前面那個數字在后面這個數組中哪個區間,然后就得到對應的星座了。
e.外層嵌套IFERROR函數是為了空單元格規避錯誤值
f.上面星座的提取是以,身份證號提取的出生年月日來提取計算的,實際中也可用身份證號直接提取星座,其函數公式為:
=IFERROR(LOOKUP(--MId(B2,11,4),{101,&34;摩羯&34;;120,&34;水瓶&34;;219,&34;雙魚&34;;321,&34;白羊&34;;420,&34;金牛&34;;521,&34;雙子&34;;621,&34;巨蟹&34;;723,&34;獅子&34;;823,&34;處女&34;;923,&34;天秤&34;;1023,&34;天蝎&34;;1122,&34;射手&34;;1222,&34;摩羯&34;})&&34;座&34;,&34;&34;)如圖9所示:(利用出生年月提取星座)
圖9
如圖10所示:(利用身份證號直接提取星座)
圖10
注:
劉夕一