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

＊＊＊＊＊＊＊＊＊

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

4、生日提醒

生日提醒的問(wèn)題，作者覺(jué)得要分兩部分來(lái)說(shuō)。

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

B43單元格函數(shù)：

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

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

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

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())&"天后慶生"

這個(gè)函數(shù)看著比較復(fù)雜（好吧，我承認(rèn)里面有一段確實(shí)復(fù)雜），但是思路其實(shí)很簡(jiǎn)單，天數(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)提取月份日期就不多說(shuō)了，TEXT函數(shù)的第二參數(shù)是代表需要轉(zhuǎn)換的格式表達(dá)式，這個(gè)參數(shù)是可以有函數(shù)參與的，例如本例，TODAY()為2019-9-17，YEAR(TODAY())就是2019，YEAR(TODAY())&"-00-00"這樣的表達(dá)的格式就是"2019-00-00"（今年的生日日期），同理YEAR(TODAY())+1&"-00-00"代表"2020-00-00"的格式（次年的生日日期），用這個(gè)方式就確定了還有多少天慶生。

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

三、性別判斷及延伸思考

相對(duì)于出生日期的話題，性別判斷的話題就顯得有點(diǎn)簡(jiǎn)單了。身份證的編碼規(guī)則，第15~16位，是各個(gè)地區(qū)戶籍公安局的編碼，這個(gè)我們就不多說(shuō)了。第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ù)返回“男”，否則為“女”（一個(gè)整數(shù)除以2的余數(shù)，除了1就是0）。說(shuō)到這里，我們可以額外多說(shuō)一句，在EXCEL函數(shù)中其實(shí)是有專門(mén)判斷奇偶性的函數(shù)——ISODD函數(shù)、ISEVEN函數(shù)。

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

當(dāng)然EXCEL從來(lái)都是一題多解的，也可以用VLOOKUP函數(shù)的方式來(lái)做，同學(xué)們會(huì)哪個(gè)，擅長(zhǎng)哪個(gè)，就隨大家方便吧，解決問(wèn)題就好。

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

四、判斷身份證號(hào)的真?zhèn)?/strong>

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

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

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

（2）第18位的效驗(yàn)碼

身份證號(hào)的第18位碼是一個(gè)計(jì)算結(jié)果值，是用前17位代碼通過(guò)復(fù)雜的一個(gè)計(jì)算得到的，了解了這個(gè)計(jì)算我們就可以驗(yàn)證身份證號(hào)碼的真?zhèn)瘟恕?/p>

計(jì)算步驟：

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

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

就是這么簡(jiǎn)單的兩步而已，但是難點(diǎn)就在于我們要分別取出各個(gè)位上的值，如果用輔助列做，這個(gè)問(wèn)題很好解決，今天我們來(lái)學(xué)習(xí)不使用輔助列的做法，如下：

問(wèn)題揭曉，此身份證號(hào)碼是假的，大家可以用真實(shí)的身份證號(hào)碼驗(yàn)證一下函數(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ù)字，形成一個(gè)常量數(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求和，通過(guò)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ù)列中索引出對(duì)照碼；最后和RIGHT(B2)（如果LEFT函數(shù)和RIGHT函數(shù)的第2參數(shù)是1，可以省略）比較，返回邏輯值TRUE就是真身份證號(hào)，F(xiàn)ALSE則是假身份證號(hào)。

＊＊＊編后語(yǔ)＊＊＊

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

＊＊＊＊部落窩教育－MID函數(shù)提取身份證信息＊＊＊＊

原創(chuàng)：E圖表述/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）

更多教程：部落窩教育（www.itblw.com）

微信公眾號(hào)：exceljiaocheng

標(biāo)簽：