excel函數應用：如何用數位函數分段提取身份證信息*上

引言：用Excel處理身份證號，在我們日常工作中是相當普遍的，尤其是對於做人事行政工作、財務工作的同學來說，更顯得十分重要。那麼一個身份證號，能給予我們多少信息量呢？這也算是一個老生常談的問題，但是最近在群裡又總見到有同學問及，那麼今天我們就再來做一個總結，看一看Excel函數對於身份證號碼對應的各個信息的處理方式吧。

＊＊＊附加知識：

無論我們需要用Excel處理何種數據，首先都應先對該數據有一個了解，在不了解它的情況下就直接處理數據，有可能會把方向做錯，那樣的數據處理結果就令人堪憂了。 

我們都知道身份證號碼一共有18位（以前的身份證號碼是15位的，但是普及二代身份證這麼多年，可以說一代的15位身份證已經極少使用了，所以今天主要講解二代身份證的內容），身份證號碼按照「號碼段」區分大體可以分為6個信息： 

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

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

● 5~6位縣、縣級市、區代碼；

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

7~10位，代表出生年份

11~12位，代表出生月份

13~14位，代表出生日期

● 15~17位為順序號，其中17位（倒數第二位）是奇數則為男性，偶數為女性；

● 18位為校驗碼，0-9和X。 

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

應該說我們的身份證號中能有這麼多的信息量，當初研究出這套編碼規則的人也真是相當的不容易了。

＊＊＊＊＊＊＊＊＊

有了上面對於身份證的一個了解，我們就可以通過這個號碼提取很多的內容了。

一、省、市、縣自動提取

函數解析：

對於這個函數來說，本身並不複雜，關鍵是這幾張信息表。

不是每一個同學都有的，現在你也可以擁有這麼一套代碼對照表了，有需要的同學，可以在看完此文篇文章之後，加入下方提供的部落窩QQ群，下載即可。

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

MID函數語法：

=MID（text，start_num，num_chars）

翻譯成白話就是在一個字符串（text）中，從某個序號位置（start_num）開始，提取之後連續的幾個（num_chars）字符。

那我們再來看一下上面的函數：

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

MID函數在B2單元格的身份證號中，提取出從第1位開始的連續兩位字符，就是「51」；再把「51」帶入到VLOOKUP函數中，並在《省級碼對照表》中的A、B兩列處進行索引即可。B7、B9單元格的市、縣索引方式也是一樣的，但是一定要記住，索引省，用到身份證的前兩位；索引市，要用到身份證的前4位。索引出XX省XX市的格式，不能直接使用第3~4位，因為會和其他的省市碼重複；縣級代碼同理，需要索引身份證號的前6位，不可單獨索引第5~6位。

當然有些同學會問，用其他函數可以嗎？針對第一個問題，用LEFT函數也可以，=VLOOKUP(LEFT($B$2,2),省級碼對照表!A:B,2,0)

但是建議同學們在身份證處理問題上，還是只使用MID函數來提取號碼段，便於對身份證信息這一類問題的統一解決，而LEFT函數或者RIGHT函數，只適合左右兩端的取值，對於中間部分的取值相對於MID函數就顯得有些麻煩了。

＊＊＊對於市、縣提取的更高要求：

剛才我們說過了，如果直接用第3~4位，或者第5~6位的代碼去索引，由於號碼有重複，所以索引出的內容有可能就是錯誤的。但是在人事工作中，有時我們需要精準的提取出XX市、XX區縣，那我們就可以使用下面的方式來處理。

B14單元格函數：

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

聰明的你，是不是一眼就可以看出來，紅色的VLOOKUP函數，提取的是XX省XX市；藍色的VLOOKUP函數，提取的是XX省，我們再用SUBSTITUTE函數，將紅色索引值中的XX省部分替換為空文本即可。縣的提取也是同理，很簡單的思路，但是在工作中十分實用，趕緊學起來吧！

二、生日提取及應用拓展

函數解析：

同樣使用MID函數提取代表出生日期的7~14位，再用TEXT函數改變格式為「0000-00-00」，最後以減負運算將文本格式的日期改成數值形態，再調整單元格格式，就是我們需要的「出生日期」。說著很簡單，如果需要掌握這個函數的話，還是需要多運用來幫助記憶才行的。關於TEXT詳細的使用方法，小夥伴們可以參考之前的教程《如果函數有職業，TEXT絕對是變裝女皇！》，在此就不細說了。

當然，我們也可以用下面的方式提取：

 圖中「生日2」的公式分別利用MID函數在身份證號中提取出代表「出生年月日」的各個代碼段，再分別代入到DATE函數的year、month、day參數中即可。

雖說兩種方法的結果一樣，但是這是兩種思路，一個是整體提取，一個是分開提取，這在我們的實際工作中，有著很重要的作用。

＊＊＊對出生日期的應用拓展：

1、通過身份證得出年齡

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

「年齡2」的算法利用了DATEDIF函數，計算兩個日期的年差，而開始日期的表示方法【--TEXT(MID(B2,7,8),"0000-00-00")】就是我們剛才用的提取出生日期的函數。關於DATEDIF詳細的使用方法，小夥伴們可以參考之前的教程《用上DATEDIF，您永不再缺席那些重要的日子！》，在此就不細說了。

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

生肖每12年一個輪迴，大家沒有必要背上面的對照碼，教給大家一個小技巧，以自己的出生年份除以12的餘數作為基準，再順序排列其他11個生肖的對照碼就好了。

B13單元格函數：

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

又是利用MID函數提取年份的四位，再通過MOD函數得到除以12的餘數作為索引值，接著利用VLOOKUP函數得到對照碼對應的生肖。

3、判斷星座

使用MID函數提取身份證中的月份和日期四位數字的文本格式【MID(B2,11,4)】，再使用減負運算，將其變更為數值形態。例如我們這個身份證號的11~14位是0322，數值形態就是322。值得注意的是，此時我們利用的並不是日期的序列值，而是一個常規形態的數值，通過對應碼區域，利用VLOOKUP的模糊查詢得到對應的星座。

＊＊＊＊＊＊＊＊＊

編後語：介於篇幅問題，以上即為上篇，關於生日提取的拓展思路還沒有講完，下篇內容我們繼續。

＊＊＊＊部落窩教育－excel數位函數技巧＊＊＊＊

原創：E圖表述/部落窩教育（未經同意，請勿轉載）