（申明：本文所用身份證号均為随機組合而成，非真實身份證号，如有雷同，純屬巧合）

在實際的辦公應用中，大量人員信息處理是件很繁瑣的事情，包括有身份證号、出生日期、性别、年齡、籍貫，甚至是個人生肖、星座等，那麼如何利用身份證号快速自動的錄入相關信息，實現又快又準的完成工作。下面就以我國身份證的信息特點，利用身份證号，在excel表格中巧妙提取身份證六大信息，快速輕松完成人員信息處理。

　　當前，我國的身份證号碼為18位，如圖1，其中裡面的信息特點為，第1到6位為全國行政戶籍區劃代碼，第7到14位為出生日期數，倒數第2位為性别代碼，偶數為女，奇數為男。我們可以利用excel中的函數，根據已輸入的身份證号碼，快速提取相關信息，準确完成人員信息處理。

圖1

　　一.身份證号提取出生日期

函數公式：=IFERROR(TEXT(MID(B2,7,8),"0-00-00"),"")

說明：

a.MID(B2,7,8)提取出生日期碼

b.TEXT函數将文本處理為日期格式

c.外層嵌套IFERROR函數是為了空單元格規避錯誤值

如圖2所示：

圖2

二．身份證号提取歲數

函數公式：=IFERROR(DATEDIF(--TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"y"),"")

說明：

a.MID(A2,7,8)提取出生日期碼

b.TEXT函數将文本處理為日期格式，--減負運算将文本轉化為日期值

c.DATEDIF函數計算出生日期和今天的差值，即得到年齡

d.外層嵌套IFERROR函數是為了空單元格規避錯誤值

如圖3所示：

　　圖3

三．身份證号提取性别

函數公式：=IFERROR(IF(MOD(MID(B2,17,1),2),"男","女"),"")

說明：

a.MID(B2,17,1)函數提取第17位數

b.MOD函數對2取餘數，偶數餘數為0，說明為女性，奇數餘數為1，說明為男性，再用IF函數來判斷性别

c.外層嵌套IFERROR函數是為了空單元格規避錯誤值

如圖4所示：

圖4

四．身份證号提取生肖

函數公式：=IFERROR(MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",MOD(YEAR(C2)-4,12) 1,1),"")

說明：

a.MID(B2,7,4)提取出生年份

b.MOD函數對12取餘數，剛好對應12生肖的序列代碼，裡面日期的-4和外面的 1是為了和前面的生肖相對應

c.外層嵌套IFERROR函數是為了空單元格規避錯誤值

d.身份證提取生肖是按公曆計算的，我國生肖多數都是以農曆計算為準，所以略有偏差，實際中需做适當調整

e.上面生肖的提取是以，身份證号提取的出生年月來提取計算的，實際中也可用身份證号直接提取陽曆生肖，其函數公式為：

=IFERROR(IF(LEN(B9)=15,MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",MOD("19"&MID(B9,7,2)-4,12) 1,1),MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",MOD(MID(B9,7,4)-4,12) 1,1)),"")

如圖5所示：(利用出生年月提取生肖)

圖5

如圖6所示：(利用身份證号直接提取生肖)

圖6

五．身份證号提取籍貫

函數公式：=IFERROR(VLOOKUP(LEFT(B2,6),戶籍行政區劃代碼表!A:B,2,1),"")

說明：

a.籍貫信息需要有戶籍行政區劃代碼表作為附表，通過函數代碼換算為地區就可以

b.戶籍行政區劃代碼表，可以通過網絡下載最新版的區劃表，根據需求做好調整使用即可。

c.外層嵌套IFERROR函數是為了空單元格規避錯誤值

如圖7、8所示：

圖7

圖8

六．身份證号提取星座

函數公式：=IFERROR(LOOKUP(--TEXT(C2,"mdd"),{101,"摩羯";120,"水平";219,"雙魚";321,"白羊";420,"金牛";521,"雙子";621,"巨蟹";723,"獅子";823,"處女";923,"天秤";1023,"天蠍";1122,"射手";1222,"摩羯"})&"座","")

說明：

a. C2的數字轉換為日期，取其中的月和日對應的數字，然後與後面的數字模糊匹配，找到比這個數字小的數字，然後取對應的星座。

b.TEXT(C2,"mdd") 是把C2的數字轉換為日期，取其中的月和日對應的數字，在Excel裡日期都是以一個數字來記錄的，因此數字能轉換為日期，這裡不要年，隻保留月和日，因為計算星座隻要月日。

c.TEXT(C2,"mdd") 前面加兩格減号--TEXT(C2,"mdd") 是把字符串轉換為純數字，得到的數字作為lookup函數的第一個參數，表示要查找的數字。

d.後面的{}裡是查詢數組，逗号表示是在同一行，分号表示下一行，這裡就是一個數字一個逗号一個星座一個分号為一行，其實就是兩列，第一列是數字，第二列是星座，第一列的數字是兩個星座分隔的日期。

使用lookup函數查詢前面那個數字在後面這個數組中哪個區間，然後就得到對應的星座了。

e.外層嵌套IFERROR函數是為了空單元格規避錯誤值

f.上面星座的提取是以，身份證号提取的出生年月日來提取計算的，實際中也可用身份證号直接提取星座，其函數公式為：

=IFERROR(LOOKUP(--MID(B2,11,4),{101,"摩羯";120,"水瓶";219,"雙魚";321,"白羊";420,"金牛";521,"雙子";621,"巨蟹";723,"獅子";823,"處女";923,"天秤";1023,"天蠍";1122,"射手";1222,"摩羯"})&"座","")如圖9所示：（利用出生年月提取星座）

圖9

如圖10所示：（利用身份證号直接提取星座）

圖10

注：

1. 在提取函數中都增加了一個函數IFFRROR，目的是為了避免空單元格的錯誤值，方便實際的錄入操作，實際中可以根據情況可以不使用此函數。
2. 出現的有些函數隻做了部分用途功能介紹，對于excel函數的各種用法，需讀者在不斷學習中理解。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!