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