用Excel從身份證號碼中提取信息

Excel是一個功能非常強大的應用，今天和大家分享的是從身份證號碼中提取有用信息的方法和技巧，跟小編一起來看看吧。

如下圖所示，是一份模擬的員工信息表，A列是姓名，B列是身份證號碼，C列是職級。

首先咱們來提取出生年月，D2輸入以下公式，向下復制。=--TEXT(MID(B2,7,8),"0-00-00")

身份證號碼中的第8位到15位是出生年月信息。首先使用MID函數從A2單元格的第7位開始，提取出8個字符，得到字符 "19820102"。 再使用TEXT函數將這個字符串變成日期樣式的文本，得到字符串“1982-01-02”。由于TEXT函數的結果是文本型的內容，所以加上兩個小減號 -- ，也就是計算負數的負數，通過四則運算，變成真正的日期序列。這樣做的目的是為了便于后續的統計匯總。如果公式結果顯示為5位數字，別忘了設置一下單元格格式為日期。

接下來咱們要計算年齡，計算年齡時，可以借助剛剛已經提取出的出生年月，E2單元格輸入以下公式，向下復制。=DATEDIF(D2,"2020-1-1","y")

DATEDIF函數用于計算兩個日期之間的間隔。用法是：=DATEDIF(起始日期,截止日期,指定間隔類型)本例中起始日期是D2的出生年月，截止日期是2020年1月1日。指定間隔類型為“Y”，表示計算間隔整年份。如果是“M”，就表示計算間隔的整月份了。DATEDIF函數是隱藏函數，輸入時沒有屏幕提示，需要手工錄入哦。如果不借助D列的出生年月，公式可以這樣寫：

=DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),"2020-1-1","y")

，使用以下公式，可以提取出員工的性別信息：=IF(MOD(MID(B2,17,1),2),"男","女")

身份證號碼中的第17位是性別信息，單數表示男，雙數表示女。首先使用MID函數，從B2單元格的第17位開始，提取出1個字符。再使用MOD函數計算這個字符與2相除的余數。最后使用IF函數判斷，如果余數不等于0，返回男，如果余數是0，則返回女。

接下來，咱們要根據提取出的出生年月和性別以及職級信息，來計算退休年月，根據現有法規，男性退休年齡為60歲，女性干部退休年齡為55歲，女性年退休為50歲。G2輸入以下公式，向下復制。=EDATE(D2,IF(F2="男",720,IF(F2&C2="女干部",660,600)))

EDATE函數的作用是根據指定的起始日期和間隔月份，計算從指定日期開始，間隔N個月后的日期。本例中，指定起始日期是D2單元格的出生年月。

間隔月份的公式部分是：IF(F2="男",720,IF(F2&C2="女干部",660,600))這部分看起來有點小復雜，咱們慢慢拆解一下：

先用IF函數判斷F2單元格的性別是不是等于"男"，如果是，返回720（720個月，也就是60歲）。如果不等于"男"，就繼續判斷F2的性別連接C2的職級，是不是等于"女干部"，如果是，返回660（660個月，也就是55歲），如果不等于"女干部"，就返回600（600個月，即50歲）。

身份證號碼的前六位表示戶籍所在地，接下來咱們就看看如何根據身份證號碼判斷所在籍貫。判斷籍貫時，咱們要先準備一份對照表，百度就有很多，我下載的這個對照表里的戶籍代碼好像不太全。

在信息表的G2單元格輸入以下公式，向下復制。

=VLOOKUP(LEFT(B2,6)*1,身份證代碼對照表!A:B,2,0)

首先用LEFT(B2,6)得到身份證號碼的前六位，然后使用VLOOKUP函數在代碼對照表里查找對應的籍貫信息。因為LEFT函數得到的是文本型的數字，而咱們的代碼表里是數值型的，所以用乘以1的方法轉換為數值，否則查詢會出錯了。

最后咱們再看看查找重復身份證號碼的問題。I2輸入以下公式，向下復制。

=IF(COUNTIF(B:B,B2&"*")>1,"有重復","無重復")

身份證號碼的查重和常規的數據查重不太一樣，在COUNTIF函數的第二參數后要加上一個星號 &"*"。COUNTIF函數在計算文本型數字時，會默認按數值型進行處理，但是Excel中的數字精度只有15位，而身份證號碼是18位，這樣就會把前15位相同的身份證全部識別為相同的內容。咱們利用Excel中的數值不支持通配符的特點，在第二參數后連接上一個通配符星號 &"*"。就是相當于告訴Excel，要查找以B2單元格內容開頭的文本，通過這樣變通的手段，COUNTIF函數就聽話了。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。