Excel表是我們工作中經(jīng)常用到的辦公室軟件之一。尤其在這個(gè)大數(shù)據(jù)時(shí)代，對(duì)我們采集和整理各種數(shù)據(jù)至關(guān)重要的作用。

Excel表中的各種函數(shù)公式讓我們?cè)趯?duì)復(fù)雜數(shù)據(jù)的提取和使用的過(guò)程中，常常會(huì)有事半功倍的效果，尤其我們結(jié)合常識(shí)后，讓我們對(duì)信息的統(tǒng)計(jì)和使用會(huì)更有效率。

眾所周知，我們的身份證號(hào)給我們提供了很多重要信息，比如性別、出生年月、身份證登記地等，那么我們?nèi)绾卧贓xcel表中合理運(yùn)用這些常識(shí)來(lái)進(jìn)行更好的數(shù)據(jù)統(tǒng)計(jì)？今天給給大家講解一下身份證號(hào)在Excel表中的巧妙運(yùn)用。

一、判斷身份證號(hào)主性別

我們的身份證倒數(shù)第二個(gè)數(shù)字，是判斷性別的重要依據(jù)，奇數(shù)（13579）為男生，偶數(shù)（02468）為女生，我們?cè)贓xcel表中使用到的公式為IF條件判斷函數(shù)和MOD函數(shù)。

公式為：IF(MOD(MID(B2,17,1),2)=1,'男','女')

函數(shù)說(shuō)明：IF函數(shù)為條件判斷函數(shù)，我們通過(guò)判斷身份證后第二個(gè)數(shù)字的奇偶數(shù)來(lái)決定是男生還是女生。MOD函數(shù)是一個(gè)求余函數(shù)，這里用來(lái)看能否被2整除來(lái)判斷奇偶數(shù)，從而通過(guò)IF函數(shù)來(lái)判斷男女性別。

二、出生年月

我們的身份證第七個(gè)數(shù)字開(kāi)始時(shí)我們的出生年月，我們需要用到文本函數(shù)TEXT函數(shù)，將我們的數(shù)值轉(zhuǎn)換成文本格式。

公式為：TEXT(MID(B2,7,8),'0000年00月00日')

函數(shù)說(shuō)明：用MID從B2單元列表從第7個(gè)數(shù)字開(kāi)始截取八個(gè)數(shù)字，即年月日的信息，然后使用TEXT文本把數(shù)字轉(zhuǎn)換成出生年月。

三、年齡

我們已經(jīng)學(xué)會(huì)了出生年月的數(shù)據(jù)采集，只需要用現(xiàn)在的年份減去出生年份既可得出年齡。

公式為：YEAR(TODAY())-MID(B2,7,4)

函數(shù)說(shuō)明：TODAY()可以獲得當(dāng)前電腦的時(shí)間，我們用當(dāng)前年份減去出生年份即可得到年齡。

四、屬相

我們的出生年月對(duì)應(yīng)著固定的一個(gè)屬相，很多時(shí)候我們也要根據(jù)一個(gè)人的出生年份算出屬相，這就需要我們用到choose函數(shù)。

公式為：=CHOOSE(MOD(MID(B2,7,4)-1900,12)+1,'鼠','牛','虎','兔','龍','蛇','馬','羊','猴','雞','狗','豬')

函數(shù)說(shuō)明：先用MID函數(shù)提取身份證的出生年份再減去1900年，看能不能被12整除。由于1900年是鼠年，所以我們以12年為一輪使用choose函數(shù)依次從屬相中選擇并返回，則可以通過(guò)身份證獲得屬相。

五、戶籍所在地

身份證號(hào)碼的前6位數(shù)是戶籍所在地的行政代碼，前12位數(shù)字是省份，34為市區(qū)，56為鄉(xiāng)鎮(zhèn)代碼。我們通過(guò)身份證前6為尋找戶籍所在地需要通過(guò)網(wǎng)絡(luò)下載全國(guó)戶籍行政代碼表，通過(guò)縱向查找vlookup函數(shù)來(lái)實(shí)現(xiàn)這個(gè)功能。

公式為：=VLOOKUP(VALUE(LEFT(B2,4)),Sheet2!A:B,2,0)

函數(shù)說(shuō)明：通過(guò)LEFT函數(shù)截取B2單元里的身份證號(hào)前四位（正常應(yīng)該截取前6位，為了快速尋找到結(jié)果，測(cè)試尋找前4位。）返回我存在戶籍行政編碼的第二張表即Sheet2!查找到A列表下的相同代碼，并導(dǎo)入對(duì)應(yīng)的地區(qū)。

很多人說(shuō)身份證號(hào)碼太長(zhǎng)，Excel表沒(méi)有辦法正常顯示，這是因?yàn)镋XCEL有效數(shù)字只有15位，過(guò)長(zhǎng)會(huì)使用默認(rèn)的科學(xué)計(jì)數(shù)來(lái)表示，這時(shí)只需要我們把表格單元格格式設(shè)置成文本即可正常顯示18位數(shù)的身份證哦！