編按：

哈嘍，大家好！在上篇內(nèi)容中我們說到了用excel提取身份證號碼中的戶籍地址、出生日期、年齡、生肖、星座等內(nèi)容，今天我們書接上回，繼續(xù)帶大家學習excel提取身份證信息的其他操作！

引言：

我們繼續(xù)上篇生日提取的拓展思路，開始我們今天的內(nèi)容，保證開篇就有驚喜！

【正文】

4、生日提醒

生日提醒的問題，作者覺得要分兩部分來說。

（1）有的公司的管理比較人性化，可能每個月都會給當月過生日的員工發(fā)一些小禮品，但如果這些小禮品都是生日當天發(fā)送，那這一年下來，就是一個很費精力的事情，所以就在每個月中選一天，給本月過生日的員工一起慶生。這個時候，我們可以使用EXCEL進行如下操作：

B43單元格函數(shù)：

=IF(--MID(B2,11,2)=MONTH(TODAY()),"本月生日","非本月生日")

講到現(xiàn)在，想必同學們都已經(jīng)對“--MID(B2,11,2)”函數(shù)用法和意義了如指掌了吧？！它用于返回生日的月份，然后與今天的月份作對比，如果相等就返回“本月生日”，否則就返回“非本月生日”，一目了然。

（2）對于重要人物的生日提醒。關(guān)于類似的提醒，作者建議大家一定要做到精確到日，類似“倒計時”的提醒，例如下面的示范：

B45單元格函數(shù)：

=IF(--TEXT(MID(B2,11,4),YEAR(TODAY())&"-00-00")<=TODAY(),--TEXT(MID(B2,11,4),YEAR(TODAY())+1&"-00-00")-TODAY(),--TEXT(MID(B2,11,4),YEAR(TODAY())&"-00-00")-TODAY())&"天后慶生"

這個函數(shù)看著比較復雜（好吧，我承認里面有一段確實復雜），但是思路其實很簡單，天數(shù)=IF(本年生日日期<=今天日期，次年生日日期-今天日期，本年生日日期-今天日期)。

本年生日日期=--TEXT(MID(B2,11,4),YEAR(TODAY())&"-00-00")

次年生日日期=--TEXT(MID(B2,11,4),YEAR(TODAY())+1&"-00-00")

MID(B2,11,4)提取月份日期就不多說了，TEXT函數(shù)的第二參數(shù)是代表需要轉(zhuǎn)換的格式表達式，這個參數(shù)是可以有函數(shù)參與的，例如本例，TODAY()為2019-9-17，YEAR(TODAY())就是2019，YEAR(TODAY())&"-00-00"這樣的表達的格式就是"2019-00-00"（今年的生日日期），同理YEAR(TODAY())+1&"-00-00"代表"2020-00-00"的格式（次年的生日日期），用這個方式就確定了還有多少天慶生。

當然同學們也可以使用函數(shù)：=DATE(YEAR(TODAY()),MID(B2,11,2),MID(B2,13,2))來得到身份證中的生日日期，上例就是為了給大家一個其他的函數(shù)應(yīng)用思路，實際工作中，會哪個就用哪個吧。

三、性別判斷及延伸思考

相對于出生日期的話題，性別判斷的話題就顯得有點簡單了。身份證的編碼規(guī)則，第15~16位，是各個地區(qū)戶籍公安局的編碼，這個我們就不多說了。第17位代表的是性別的代碼，奇數(shù)代表男性，偶數(shù)代表女性。

B5單元格函數(shù)：

=IF(MOD(MID(B2,17,1),2)=1,"男","女")

MID(B2,17,1)提取身份證第17位的代碼，用MOD函數(shù)得到除以2的余數(shù)，再用IF函數(shù)判斷余數(shù)是否為1，為1則為奇數(shù)返回“男”，否則為“女”（一個整數(shù)除以2的余數(shù)，除了1就是0）。說到這里，我們可以額外多說一句，在EXCEL函數(shù)中其實是有專門判斷奇偶性的函數(shù)——ISODD函數(shù)、ISEVEN函數(shù)。

利用這兩個函數(shù)也是可以判斷性別的，如下圖（注意兩個函數(shù)的邏輯返回值所對應(yīng)的“男”、“女”）：

當然EXCEL從來都是一題多解的，也可以用VLOOKUP函數(shù)的方式來做，同學們會哪個，擅長哪個，就隨大家方便吧，解決問題就好。

寫了這么多的內(nèi)容，有的同學可能會說了，我們這樣一直曝光別人的身份證，應(yīng)該不太好吧？！

四、判斷身份證號的真?zhèn)?/span>

在很多情況下，我們都有可能只看到身份證號，而看不到身份證原件，即便看到了身份證原件，沒有專業(yè)的身份證讀卡機我們也不好判斷是真是假。那么本系列最后一部分內(nèi)容我們就來學習如何使用EXCEL判斷身份證號的真?zhèn)巍?/span>

（1）首先身份證的位數(shù)是我們第一步判斷身份證號錄入是否正確的標準。

這是最基本的，如果位數(shù)都不對，那就別談其他的了。

（2）第18位的效驗碼

身份證號的第18位碼是一個計算結(jié)果值，是用前17位代碼通過復雜的一個計算得到的，了解了這個計算我們就可以驗證身份證號碼的真?zhèn)瘟恕?/span>

計算步驟：

★前17位號碼，每一位乘對應(yīng)位數(shù)的系數(shù)，再相加求和，對應(yīng)碼如下：

★將得數(shù)除以11求余，余數(shù)和下面的對比表進行對比，對應(yīng)的上則為“真”，否則為“假”。

就是這么簡單的兩步而已，但是難點就在于我們要分別取出各個位上的值，如果用輔助列做，這個問題很好解決，今天我們來學習不使用輔助列的做法，如下：

問題揭曉，此身份證號碼是假的，大家可以用真實的身份證號碼驗證一下函數(shù)。

B5單元格函數(shù)：

{=VLOOKUP(MOD(SUM(MID(B2,ROW(1:17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0)=RIGHT(B2)}

輸入完成后，按數(shù)組函數(shù)的結(jié)束鍵CTRL+SHIFT+ENTER三鍵結(jié)束。

函數(shù)解析：

MID(B2,ROW(1:17),1)利用數(shù)組的方式，分別得到身份證前17位的數(shù)字，形成一個常量數(shù)組，{"5";"1";"1";"5";"0";"2";"1";"9";"9";"1";"0";"3";"2";"2";"3";"1";"8"}。

然后和{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}相乘再用SUM求和，通過MOD（值，11）得到余數(shù)，再用VLOOKUP函數(shù)在{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2}數(shù)列中索引出對照碼；最后和RIGHT(B2)（如果LEFT函數(shù)和RIGHT函數(shù)的第2參數(shù)是1，可以省略）比較，返回邏輯值TRUE就是真身份證號，FALSE則是假身份證號。

編后語：

我知道網(wǎng)絡(luò)上有很多關(guān)于身份證號的文章，但是每次寫都會有不同的感受和新的內(nèi)容出來，比如第一部分我們使用SUBSTITUTE函數(shù)精準提取市、區(qū)縣，又如生日提醒里面的TEXT函數(shù)的使用方式，更比如最后的效驗碼問題，這些都不是不好理解的東西。E圖表述的文章，力求讓大家能夠深入淺出，開拓思維，真正地在部落窩學到你想要的知識。

本文配套的練習課件請加入QQ群：109723835下載。

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

掃下方二維碼關(guān)注公眾號，可隨時隨地學習Excel：

相關(guān)推薦：

Excel提取身份證信息合集（上）《2019年全網(wǎng)最全—excel提取身份證信息合集！（建議收藏）-上篇》

檢驗身份證號碼真?zhèn)?/span>《Excel檢驗身份證號碼真假的函數(shù)公式》

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