編按：

哈嘍，大家好！今天我們來(lái)聊聊excel提取身份證號(hào)碼信息的問(wèn)題。這已經(jīng)是一個(gè)老生常談的話題了，但是最近發(fā)現(xiàn)市面上相關(guān)的教程都不怎么全面，要么就是提取出的信息不全，要么就是直接給個(gè)公式，沒(méi)有任何解釋。剛好群里也有同學(xué)問(wèn)到這個(gè)問(wèn)題，就干脆寫(xiě)一篇教程，算是統(tǒng)一回答了。一起來(lái)看看吧！

引言：

用Excel處理身份證號(hào)，在我們?nèi)粘９ぷ髦惺窍喈?dāng)普遍的，尤其是對(duì)于做人事行政工作、財(cái)務(wù)工作的同學(xué)來(lái)說(shuō)，更顯得十分重要。那么一個(gè)身份證號(hào)，能給予我們多少信息量呢？這也算是一個(gè)老生常談的問(wèn)題，但是最近在群里又總見(jiàn)到有同學(xué)問(wèn)及，那么今天我們就再來(lái)做一個(gè)總結(jié)，看一看Excel函數(shù)對(duì)于身份證號(hào)碼對(duì)應(yīng)的各個(gè)信息的處理方式吧。

附加知識(shí)：

無(wú)論我們需要用Excel處理何種數(shù)據(jù)，首先都應(yīng)先對(duì)該數(shù)據(jù)有一個(gè)了解，在不了解它的情況下就直接處理數(shù)據(jù)，有可能會(huì)把方向做錯(cuò)，那樣的數(shù)據(jù)處理結(jié)果就令人堪憂了。

我們都知道身份證號(hào)碼一共有18位（以前的身份證號(hào)碼是15位的，但是普及二代身份證這么多年，可以說(shuō)一代的15位身份證已經(jīng)極少使用了，所以今天主要講解二代身份證的內(nèi)容），身份證號(hào)碼按照“號(hào)碼段”區(qū)分大體可以分為6個(gè)信息：

● 1~2位省、自治區(qū)、直轄市代碼；

● 3~4位地級(jí)市、盟、自治州代碼；

● 5~6位縣、縣級(jí)市、區(qū)代碼；

● 7~14位出生年月日；其中

7~10位，代表出生年份

11~12位，代表出生月份

13~14位，代表出生日期

● 15~17位為順序號(hào)，其中17位（倒數(shù)第二位）是奇數(shù)則為男性，偶數(shù)為女性；

● 18位為校驗(yàn)碼，0-9和X。

我們輔以圖示更加直觀的記憶一下：

應(yīng)該說(shuō)我們的身份證號(hào)中能有這么多的信息量，當(dāng)初研究出這套編碼規(guī)則的人也真是相當(dāng)?shù)牟蝗菀琢恕?/span>

正文：

有了上面對(duì)于身份證的一個(gè)了解，我們就可以通過(guò)這個(gè)號(hào)碼提取很多的內(nèi)容了。

一、省、市、縣自動(dòng)提取

示例：

函數(shù)解析：

對(duì)于這個(gè)函數(shù)來(lái)說(shuō)，本身并不復(fù)雜，關(guān)鍵是這幾張信息表。

不是每一個(gè)同學(xué)都有的，現(xiàn)在你也可以擁有這么一套代碼對(duì)照表了，有需要的同學(xué)，可以在看完此文篇文章之后，加入下方提供的部落窩QQ群，下載即可。

公式中所使用的MID函數(shù)是典型的字符串分段取值的函數(shù)。

MID函數(shù)語(yǔ)法：

=MID（text，start_num，num_chars）

翻譯成白話就是在一個(gè)字符串（text）中，從某個(gè)序號(hào)位置（start_num）開(kāi)始，提取之后連續(xù)的幾個(gè)（num_chars）字符。

那我們?cè)賮?lái)看一下上面的函數(shù)：

B5單元格=VLOOKUP(MID($B$2,1,2),省級(jí)碼對(duì)照表!A:B,2,0)

MID函數(shù)在B2單元格的身份證號(hào)中，提取出從第1位開(kāi)始的連續(xù)兩位字符，就是“51”；再把“51”帶入到VLOOKUP函數(shù)中，并在《省級(jí)碼對(duì)照表》中的A、B兩列處進(jìn)行索引即可。B7、B9單元格的市、縣索引方式也是一樣的，但是一定要記住，索引省，用到身份證的前兩位；索引市，要用到身份證的前4位。索引出XX省XX市的格式，不能直接使用第3~4位，因?yàn)闀?huì)和其他的省市碼重復(fù)；縣級(jí)代碼同理，需要索引身份證號(hào)的前6位，不可單獨(dú)索引第5~6位。

當(dāng)然有些同學(xué)會(huì)問(wèn)，用其他函數(shù)可以嗎？針對(duì)第一個(gè)問(wèn)題，用LEFT函數(shù)也可以，=VLOOKUP(LEFT($B$2,2),省級(jí)碼對(duì)照表!A:B,2,0)

但是建議同學(xué)們?cè)谏矸葑C處理問(wèn)題上，還是只使用MID函數(shù)來(lái)提取號(hào)碼段，便于對(duì)身份證信息這一類(lèi)問(wèn)題的統(tǒng)一解決，而LEFT函數(shù)或者RIGHT函數(shù)，只適合左右兩端的取值，對(duì)于中間部分的取值相對(duì)于MID函數(shù)就顯得有些麻煩了。

對(duì)于市、縣提取的更高要求：

剛才我們說(shuō)過(guò)了，如果直接用第3~4位，或者第5~6位的代碼去索引，由于號(hào)碼有重復(fù)，所以索引出的內(nèi)容有可能就是錯(cuò)誤的。但是在人事工作中，有時(shí)我們需要精準(zhǔn)的提取出XX市、XX區(qū)縣，那我們就可以使用下面的方式來(lái)處理。

B14單元格函數(shù)：

=SUBSTITUTE(VLOOKUP(MID($B$2,1,4),省市碼對(duì)照表!A:B,2,0),VLOOKUP(MID($B$2,1,2),省級(jí)碼對(duì)照表!A:B,2,0),"")

聰明的你，是不是一眼就可以看出來(lái)，紅色的VLOOKUP函數(shù)，提取的是XX省XX市；藍(lán)色的VLOOKUP函數(shù)，提取的是XX省，我們?cè)儆?span>SUBSTITUTE函數(shù)，將紅色索引值中的XX省部分替換為空文本即可?？h的提取也是同理，很簡(jiǎn)單的思路，但是在工作中十分實(shí)用，趕緊學(xué)起來(lái)吧！

二、生日提取及應(yīng)用拓展

示例：

函數(shù)解析：

同樣使用MID函數(shù)提取代表出生日期的7~14位，再用TEXT函數(shù)改變格式為“0000-00-00”，最后以減負(fù)運(yùn)算將文本格式的日期改成數(shù)值形態(tài)，再調(diào)整單元格格式，就是我們需要的“出生日期”。說(shuō)著很簡(jiǎn)單，如果需要掌握這個(gè)函數(shù)的話，還是需要多運(yùn)用來(lái)幫助記憶才行的。關(guān)于TEXT詳細(xì)的使用方法，小伙伴們可以參考之前的教程《如果函數(shù)有職業(yè)，TEXT絕對(duì)是變裝女皇！》，在此就不細(xì)說(shuō)了。

當(dāng)然，我們也可以用下面的方式提?。?/span>

圖中“生日2”的公式分別利用MID函數(shù)在身份證號(hào)中提取出代表“出生年月日”的各個(gè)代碼段，再分別代入到DATE函數(shù)的year、month、day參數(shù)中即可。

雖說(shuō)兩種方法的結(jié)果一樣，但是這是兩種思路，一個(gè)是整體提取，一個(gè)是分開(kāi)提取，這在我們的實(shí)際工作中，有著很重要的作用。

對(duì)出生日期的應(yīng)用拓展：

1、通過(guò)身份證得出年齡

“年齡1”的算法依然使用了MID函數(shù)，今年的年份【YEAR(TODAY())】減去出生的年份【MID(B2,7,4)】就是年齡了；

“年齡2”的算法利用了DATEDIF函數(shù)，計(jì)算兩個(gè)日期的年差，而開(kāi)始日期的表示方法【--TEXT(MID(B2,7,8),"0000-00-00")】就是我們剛才用的提取出生日期的函數(shù)。關(guān)于DATEDIF詳細(xì)的使用方法，小伙伴們可以參考之前的教程《用上DATEDIF，您永不再缺席那些重要的日子！》，在此就不細(xì)說(shuō)了。

2、判斷生肖（以身份證公歷生日判斷）

生肖每12年一個(gè)輪回，大家沒(méi)有必要背上面的對(duì)照碼，教給大家一個(gè)小技巧，以自己的出生年份除以12的余數(shù)作為基準(zhǔn)，再順序排列其他11個(gè)生肖的對(duì)照碼就好了。

B13單元格函數(shù)：

=VLOOKUP(MOD(MID(B2,7,4),12),A16:B27,2,0)

又是利用MID函數(shù)提取年份的四位，再通過(guò)MOD函數(shù)得到除以12的余數(shù)作為索引值，接著利用VLOOKUP函數(shù)得到對(duì)照碼對(duì)應(yīng)的生肖。

3、判斷星座

使用MID函數(shù)提取身份證中的月份和日期四位數(shù)字的文本格式【MID(B2,11,4)】，再使用減負(fù)運(yùn)算，將其變更為數(shù)值形態(tài)。例如我們這個(gè)身份證號(hào)的11~14位是0322，數(shù)值形態(tài)就是322。值得注意的是，此時(shí)我們利用的并不是日期的序列值，而是一個(gè)常規(guī)形態(tài)的數(shù)值，通過(guò)對(duì)應(yīng)碼區(qū)域，利用VLOOKUP的模糊查詢得到對(duì)應(yīng)的星座。

編后語(yǔ)：

介于篇幅問(wèn)題，以上即為上篇，關(guān)于生日提取的拓展思路還沒(méi)有講完，下篇內(nèi)容我們繼續(xù)。

本文配套的練習(xí)課件請(qǐng)加入QQ群：109723835下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

TEXT函數(shù)的應(yīng)用《如果函數(shù)有職業(yè)，TEXT絕對(duì)是變裝女皇！》

DATEDIF函數(shù)的應(yīng)用《用上DATEDIF，您永不再缺席那些重要的日子！》

身份證號(hào)碼的驗(yàn)證《Excel小白的數(shù)據(jù)驗(yàn)證課②身份證的雙重驗(yàn)證設(shè)置等》