實(shí)用技巧：用Excel從身份證號(hào)碼中提取信息

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

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

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

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

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

領(lǐng)導(dǎo)除了關(guān)心年齡，更關(guān)心的應(yīng)該就是性別了

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

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

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

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

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

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

身份證號(hào)碼的前六位表示戶籍所在地，接下來(lái)咱們就看看如何根據(jù)身份證號(hào)碼判斷所在籍貫。判斷籍貫時(shí)，咱們要先準(zhǔn)備一份對(duì)照表，百度就有很多，我下載的這個(gè)對(duì)照表里的戶籍代碼好像不太全。

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

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

首先用LEFT(B2,6)得到身份證號(hào)碼的前六位，然后使用VLOOKUP函數(shù)在代碼對(duì)照表里查找對(duì)應(yīng)的籍貫信息。因?yàn)長(zhǎng)EFT函數(shù)得到的是文本型的數(shù)字，而咱們的代碼表里是數(shù)值型的，所以用乘以1的方法轉(zhuǎn)換為數(shù)值，否則查詢會(huì)出錯(cuò)了。
 

最后咱們?cè)倏纯床檎抑貜?fù)身份證號(hào)碼的問(wèn)題。I2輸入以下公式，向下復(fù)制。

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

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