Hello，各位小伙伴們大家好呀，上一期和大家分享了'Excel從入門到放棄'的一些階段，有小伙伴@melo留言說(shuō)能不能分享一期有關(guān)于身份證提取戶籍和年齡的案例。

Yogurt花了大概一天的時(shí)間把國(guó)家統(tǒng)計(jì)局2009年、2013-2016年的三級(jí)行政區(qū)劃給整理了一下。

PS：順便吐槽一下國(guó)家統(tǒng)計(jì)局的網(wǎng)站。。。不知道是和Yogurt一樣同時(shí)爬數(shù)據(jù)的人太多了呢還是來(lái)不及維護(hù)。。。

2017年天津市的二級(jí)頁(yè)面亂碼

2017年湖北省的二級(jí)界面亂碼

這里截出來(lái)的圖都是個(gè)例，其他的就沒(méi)看了，像這種情況還不少。。。早在幾個(gè)月之前，重慶市的二級(jí)界面也出現(xiàn)過(guò)亂碼的情況，那次也沒(méi)爬成功，也算了，所以，知道為嘛只爬了09年和13-16年的了吧。

當(dāng)然了，即便是這樣，還是要感謝那些在背后默默付出的國(guó)家統(tǒng)計(jì)員們，沒(méi)有你們?cè)谝痪€一點(diǎn)一滴的記錄，也不可能把全國(guó)3000多個(gè)區(qū)級(jí)單位統(tǒng)計(jì)上報(bào)，當(dāng)然往下的街道和居委會(huì)更是數(shù)不勝數(shù)了，萬(wàn)分感謝你們的辛勤付出。

截止到發(fā)稿前，網(wǎng)站恢復(fù)了，之前發(fā)生亂碼的情況應(yīng)該是由于訪問(wèn)的人次過(guò)多導(dǎo)致的。

咱們會(huì)到正題。

關(guān)于身份證里的一些計(jì)算方式，Yogurt在去年寫了一篇，

不過(guò)現(xiàn)在回頭看的時(shí)候，不知道當(dāng)時(shí)為嘛少寫了一個(gè)身份證驗(yàn)證的步驟。而且在現(xiàn)在看來(lái)，當(dāng)時(shí)涵蓋的內(nèi)容還有點(diǎn)不全面，本期Yogurt打算完善一下關(guān)于身份證的所有處理方式，將會(huì)和大家一起分享如何使用一個(gè)身份證號(hào)碼來(lái)完成大部分個(gè)人信息的填寫，減少錄入的工作量。

1

基礎(chǔ)數(shù)據(jù)準(zhǔn)備

1.1 準(zhǔn)備身份證代碼數(shù)據(jù)庫(kù)

在身份證號(hào)碼中可以很直觀的獲取各種各樣的信息，唯一一個(gè)沒(méi)法直接獲取的就是身份證號(hào)碼前6位的行政區(qū)劃代碼所代表的籍貫信息。為了能夠運(yùn)用Excel來(lái)獲取這一信息，首先我們需要先準(zhǔn)備一份盡可能完善的身份證行政區(qū)劃代碼數(shù)據(jù)庫(kù)。

這里Yogurt使用的Python 3.7.1 request庫(kù)對(duì)國(guó)家統(tǒng)計(jì)局的數(shù)據(jù)進(jìn)行多次爬取得到的數(shù)據(jù)。

python代碼如下：

由于國(guó)家統(tǒng)計(jì)局的網(wǎng)站不需要其他的驗(yàn)證，又是靜態(tài)網(wǎng)頁(yè)，所以直接從網(wǎng)頁(yè)原代碼里爬取就可以了。具體的Python用法沒(méi)法一句話兩句話講清楚，但可以和大家分享一下我的思路。

http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2009/index.html

以2009年的行政區(qū)劃為例，打開(kāi)國(guó)家統(tǒng)計(jì)局的網(wǎng)站，然后【右鍵】-【查看源代碼】，然后就可以看到這一頁(yè)的信息，我們需要的內(nèi)容就是這里選中的部分。

北京市

天津市

河北省

山西省

內(nèi)蒙古自治區(qū)

遼寧省

吉林省

黑龍江省

上海市

江蘇省

浙江省

安徽省

福建省

江西省

山東省

河南省

湖北省

湖南省

廣東省

廣西壯族自治區(qū)

海南省

重慶市

四川省

貴州省

云南省

西藏自治區(qū)

陜西省

甘肅省

青海省

寧夏回族自治區(qū)

新疆維吾爾自治區(qū)

臺(tái)灣省

香港特別行政區(qū)

澳門特別行政區(qū)

這里含的信息有網(wǎng)址和省份名稱。我們可以用正則表達(dá)式把這里的信息提取出來(lái)。

.*?(.*?)

關(guān)于正則表達(dá)式的相關(guān)內(nèi)容，后期如果有機(jī)會(huì)可以和大家多分享一些，但這一期里篇幅有限不能介紹太多，大家可以去百度了解一下。

代碼中的網(wǎng)址是縮寫，爬取信息需要完整的網(wǎng)址，所以再加上前面的網(wǎng)址內(nèi)容湊成完整的網(wǎng)址就是最終的爬取網(wǎng)址。下一級(jí)的網(wǎng)址獲取方法與這一階段一致。

大致的流程圖是上述這樣，操作起來(lái)其實(shí)稍微比流程圖畫的更簡(jiǎn)單一點(diǎn)。

接著再把這些獲取到的數(shù)據(jù)進(jìn)行匯總、去重、整合，形成完整的身份證代碼數(shù)據(jù)庫(kù)。由于經(jīng)過(guò)了多年，行政區(qū)域發(fā)生了許多變化，我們的身份證號(hào)是唯一的，從入戶開(kāi)始就沒(méi)有發(fā)生過(guò)變化，因此身份證代碼數(shù)據(jù)庫(kù)不能以最新的行政區(qū)劃為準(zhǔn)，這也就是為什么要從2009年開(kāi)始獲取并整合。

Yogurt實(shí)際測(cè)試的時(shí)候發(fā)現(xiàn)還是有很多行政代碼沒(méi)有辦法識(shí)別出來(lái)，接著就到人力資源與社會(huì)保障部的官網(wǎng)上再下載了一份截止到2018年2月5號(hào)全國(guó)行政區(qū)劃代碼表。

下載下來(lái)的表格長(zhǎng)這樣，要達(dá)到我們使用的標(biāo)準(zhǔn)還是差了一些，因此還需要對(duì)其進(jìn)行稍微進(jìn)行修飾、整合、去重，由于人力資源與社會(huì)保障部官網(wǎng)的數(shù)據(jù)相對(duì)全面一些，因此最終形成以人力資源與社會(huì)保障部官網(wǎng)數(shù)據(jù)為主，國(guó)家統(tǒng)計(jì)局?jǐn)?shù)據(jù)為輔的共4773條身份證行政代碼數(shù)據(jù)庫(kù)。

即便這份數(shù)據(jù)是Yogurt想盡了一切辦法整理出來(lái)的，但眾所周知，我國(guó)幅員遼闊，地廣人多，自改革開(kāi)放以來(lái)社會(huì)發(fā)展迅速，很多地名都發(fā)生了變化，最終根據(jù)數(shù)據(jù)庫(kù)匹配出來(lái)的地名可能會(huì)與身份證稍微會(huì)有點(diǎn)出入，這就需要在實(shí)際工作中對(duì)其進(jìn)行核對(duì)了。

1.2 準(zhǔn)備計(jì)算表

Yogurt把所有能想到從身份證號(hào)中獲取或衍生的信息內(nèi)容都列了出來(lái)，也許大家也沒(méi)想到一個(gè)身份證號(hào)里能包含這么多信息可以獲取和計(jì)算的吧，如果還有能想到的獲取信息不妨給Yogurt留言，咱們相互交流。

我們接下來(lái)的分享將會(huì)圍繞這份表格來(lái)進(jìn)行。

溫馨提示：該表格中的人名、身份證號(hào)全部都是為了此次推送隨機(jī)生成的，存在雷同的概率應(yīng)該是比較低的，如果真的雷同了，請(qǐng)私聊聯(lián)系Yogurt。

2

實(shí)操處理

2.0 前期準(zhǔn)備

一直以來(lái)我們都習(xí)慣于下拉填充公式，全部做完以后才對(duì)表格進(jìn)行排版。這次Yogurt借此機(jī)會(huì)，和大家分享一種新的制表方式，相信對(duì)大家會(huì)有所幫助。

【套用表格樣式】這個(gè)玩法在Yogurt接觸到小伙伴中，很少會(huì)這么玩的，不過(guò)真的很能夠提高寫函數(shù)的效率，大家接著往下看就知道了。

快捷鍵是【Ctrl】 【L】，快捷鍵設(shè)置的是默認(rèn)樣式，如果想直接就選好樣式的話，還是用鼠標(biāo)選吧。

用好這個(gè)方法有個(gè)前提，需要表格是100%的數(shù)據(jù)庫(kù)式制表法——即不能有任何一個(gè)合并單元格。

OK，前期準(zhǔn)備完成，接下來(lái)就可以進(jìn)入我們的實(shí)操環(huán)節(jié)了。

2.1 隱藏身份證號(hào)碼

隱藏身份證號(hào)碼有兩種方法。一種是通用的函數(shù)法，另一種則是Excel 2013版及以上的【Ctrl】 【E】大法。

2.1.1 方法一：通用函數(shù)法

=REPLACE([@身份證號(hào)碼],7,8,REPT('*',8))

=REPLACE(D3,7,8,REPT('*',8))

在【套用表格樣式】的環(huán)境下寫函數(shù)，最大的好處就是對(duì)于需要連續(xù)填充的表格來(lái)說(shuō)，只需要寫一次函數(shù)，打完回車，剩下的就自動(dòng)填充完成了。

這里的函數(shù)寫法也和傳統(tǒng)的寫法不太一樣，大家參照下面的一個(gè)公式就知道了。這是因?yàn)樵O(shè)置了【套用表格樣式】之后，表格中的計(jì)算就會(huì)基于其命名的特點(diǎn)來(lái)進(jìn)行，與數(shù)據(jù)庫(kù)中的計(jì)算方式大同小異。

官方說(shuō)明：

REPLACE(old_text,start_num,num_chars,new_text)

中文翻譯：

REPLACE(要提換的內(nèi)容,從哪開(kāi)始,要替換多少個(gè),替換成什么)

REPLACE函數(shù)是通過(guò)截取數(shù)量進(jìn)行替換的函數(shù)，對(duì)于編碼格式、字符長(zhǎng)度一致的字符串的替換優(yōu)勢(shì)相當(dāng)強(qiáng)。很多小伙伴可能更加習(xí)慣于使用SUBSTITUTE()進(jìn)行替換，但在這種情況下，SUBSTITUTE()就顯得捉襟見(jiàn)肘了。

官方說(shuō)明：

REPT(text,number_times)

中文翻譯：

REPT(需要重復(fù)的字符/字符串,重復(fù)次數(shù))

REPT函數(shù)是對(duì)指定字符或字符串進(jìn)行重復(fù)輸出的一種函數(shù)。用的人比較少，但必要時(shí)候效果還是不錯(cuò)的。

在沒(méi)有使用REPT()的情況下，REPLACE()中的第四個(gè)參數(shù)就寫成'*********'。少的時(shí)候沒(méi)什么，多了就容易出錯(cuò)。比如Yogurt在這里打了9個(gè)'*'，不仔細(xì)看真的容易看錯(cuò)。

2.1.2 方法二：【Ctrl】 【E】大法

Excel 2013版及以上中有一個(gè)非常實(shí)用的快捷鍵——【Ctrl】 【E】，它可以快速填充與第一個(gè)輸入的單元格相同的操作。對(duì)于很多一次性的操作，可以提高不少的工作效率。

兩種方法，各有各的好處，不過(guò)呢，既然是考慮到了要隱藏身份證信息的情況，因此如果是要發(fā)送出去的，記得要把發(fā)送的信息復(fù)制出去，然后粘貼為文本哦，直接使用保護(hù)工作表的安全系數(shù)是很低的。所有涉及與員工隱私信息、企業(yè)隱私信息相關(guān)的內(nèi)容，一旦是需要發(fā)送給他人的，建議全部處理并隱藏，一律不能保留原始數(shù)據(jù)源。

2.2 驗(yàn)證身份證號(hào)碼

公民身份證號(hào)碼是經(jīng)過(guò)了我國(guó)國(guó)家質(zhì)量技術(shù)監(jiān)督局的提出，多個(gè)相關(guān)單位共同起草的國(guó)家標(biāo)準(zhǔn)——《GB 11643-1999》

http://www./bzgk/gb/newGbInfo?hcno=080D6FBF2BB468F9007657F26D60013E

在公開(kāi)的國(guó)家標(biāo)準(zhǔn)中，對(duì)校驗(yàn)碼的計(jì)算有著詳細(xì)的說(shuō)明。

而我們要做的就是將國(guó)家標(biāo)準(zhǔn)中提到的計(jì)算方法轉(zhuǎn)換為Excel的計(jì)算方法即可。

2.2.1 原理解釋

身份證從二代開(kāi)始，身份證號(hào)碼總共有18位，由6位行政代碼 8位出生日期 3位順序碼 1位校驗(yàn)碼構(gòu)成。至于網(wǎng)上提到的順序碼為當(dāng)?shù)嘏沙鏊a的說(shuō)法，Yogurt暫時(shí)還沒(méi)有找到比較官方的解釋，我想《GB 11643-1999》里已經(jīng)說(shuō)得很詳細(xì)了。

上圖中，將身份證號(hào)碼前17位分別單獨(dú)取出來(lái)，一一與對(duì)應(yīng)的加權(quán)系數(shù)相乘，然后將17個(gè)結(jié)果相加，最終之和與11相除所得的余數(shù)對(duì)應(yīng)的校驗(yàn)碼就是身份證最后一位的校驗(yàn)碼。

上圖是原理，而我們應(yīng)用到實(shí)際運(yùn)算中可不能做得這么麻煩，需要將公式簡(jiǎn)化。

2.2.2 公式含義

2.2.2.1 拆分身份證號(hào)碼

將身份證號(hào)碼拆成17個(gè)單獨(dú)的數(shù)字，這里需要用到MID函數(shù)。

官方說(shuō)明：

MID(text,start_num,num_chars)

中文翻譯：

MID(待截取字符串,從哪開(kāi)始,截取多長(zhǎng))

MID函數(shù)與LEFT()、RIGHT()一起并稱'截取三劍客'，其中MID函數(shù)的靈活性相對(duì)較高，結(jié)合數(shù)組使用，能夠收獲意想不到的效果。

=--MID([@身份證號(hào)碼],COLUMN($A:$Q),1)

一般情況下使用ROW()會(huì)比較多，突然使用COLUMN函數(shù)也許大家會(huì)有點(diǎn)意外。和ROW函數(shù)一樣，都是獲取連續(xù)數(shù)字的好方法，而不同的是ROW()得到的結(jié)果是橫向數(shù)組，而COLUMN()是縱向。因此在MID中得到的結(jié)果也是不一樣的。而我們這里由于為了規(guī)避'三鍵結(jié)束'，而涉及到了SUMPRODUCT函數(shù)，因此選擇使用了COLUMN()，具體會(huì)在下面提到。

COLUMN(A:Q)，其實(shí)就是1到17。結(jié)果為，再經(jīng)過(guò)MID()截取了相關(guān)的數(shù)值之后，以上述身份證號(hào)碼為例，結(jié)果為。大家這里會(huì)發(fā)現(xiàn)中間的分隔符號(hào)有變化。如果使用ROW()的話，這里的分隔符號(hào)會(huì)恰恰相反。這里就好比橫向輸出與縱向輸出的不同。

黃色區(qū)域?qū)懗蓴?shù)組為，綠色區(qū)域?qū)憯?shù)組為。這里MID輸出結(jié)果同理。

截取的結(jié)果為文本型數(shù)字，因此需要加上--來(lái)轉(zhuǎn)換為數(shù)值，或者*1，又或者使用VALUE函數(shù)。Yogurt個(gè)人比較習(xí)慣用--，看個(gè)人喜好。

2.2.2.2 身份證號(hào)碼加權(quán)計(jì)算

每一個(gè)數(shù)字與其對(duì)應(yīng)的加權(quán)因子相乘，然后相加起來(lái)。有些小伙伴在這里可能會(huì)有點(diǎn)犯難，畢竟數(shù)組計(jì)算和單元格計(jì)算不一樣，單元格里可以逐一進(jìn)行選擇，然后每一個(gè)乘以一個(gè)對(duì)應(yīng)加權(quán)因子，最后將其加起來(lái)即可。也有些小伙伴可能會(huì)用SUM(拆分結(jié)果數(shù)組*加權(quán)因子)，然后'三鍵結(jié)束'，但這個(gè)方法有點(diǎn)麻煩，Yogurt的方法可以避免使用'三鍵'，就是SUMPRODUCT函數(shù)。

官方說(shuō)明：

SUMPRODUCT(array1,[array2],......)

中文翻譯：

SUMPRODUCT(數(shù)組1,[數(shù)組2],......)

SUMPRODUCT函數(shù)簡(jiǎn)單理解的話可以認(rèn)為是A*B C*D，也就是把所有參數(shù)里的數(shù)組對(duì)應(yīng)相乘，然后將所有乘積相加的這么一個(gè)函數(shù)。

計(jì)算前提1：數(shù)組的大小要一致。不能說(shuō)比如一個(gè)數(shù)組有15個(gè)數(shù)字，另一個(gè)是16位數(shù)字。

計(jì)算前提2：數(shù)組方向要一致，橫向數(shù)組和縱向數(shù)組之間不能進(jìn)行計(jì)算。

=SUMPRODUCT(--MID(),)

這里根據(jù)計(jì)算前提2中的限制，也就是為什么MID函數(shù)里要使用COLUMN()而不使用ROW()了。當(dāng)然，如果覺(jué)得使用ROW()比較方便的話，那就把加權(quán)因子中的逗號(hào)改為分號(hào)，將其變?yōu)榭v向數(shù)組進(jìn)行計(jì)算就可以了。

2.2.2.3 求余數(shù)

余數(shù)函數(shù)我想大家應(yīng)該都還記得——MOD()

官方說(shuō)明：

MOD(number,divisor)

中文翻譯：

MOD(被除數(shù),除數(shù))

MOD函數(shù)的原理很簡(jiǎn)單，就是小學(xué)時(shí)候?qū)W的求余數(shù)的方法。

=MOD(SUMPRODUCT(--MID()),11)

至于為啥除以11，應(yīng)該是由于這套標(biāo)準(zhǔn)所采用的ISO 7064:1983(現(xiàn)標(biāo)準(zhǔn)號(hào)為ISO 7064:2003)。這份標(biāo)準(zhǔn)在道客巴巴上可以查看，下載需要2000積分，而且以Yogurt這蹩腳的英文水平，看得有點(diǎn)吃力，所以就不詳細(xì)解釋了，如果有需要的小伙伴可以自行搜索下載。

2.2.2.4 求校驗(yàn)碼

根據(jù)上述算出來(lái)余數(shù)，輸出指定位置的校驗(yàn)碼，這計(jì)算身份證校驗(yàn)碼的工作就算是完成了。

官方說(shuō)明：

CHOOSE(index_num,value1,[value2],......)

中文翻譯：

CHOOSE(索引值,數(shù)值1,[數(shù)值2],......)

CHOOSE函數(shù)是一個(gè)比較尷尬的函數(shù)，從下面的另一種方法就可以看出，MID函數(shù)分分鐘就可以代替它，不過(guò)呢，對(duì)于不是那么規(guī)律的輸出結(jié)果來(lái)說(shuō)，CHOOSE函數(shù)的特長(zhǎng)就發(fā)揮出來(lái)了。有時(shí)候我們可能會(huì)對(duì)滿足某個(gè)條件的值進(jìn)行指定結(jié)果的輸出，一般很多小伙伴可能都會(huì)選擇使用IF()進(jìn)行嵌套，對(duì)于嵌套過(guò)多的情況，寫起來(lái)不方便，后期維護(hù)的時(shí)候也不方便，這時(shí)可以把滿足的條件轉(zhuǎn)換為索引值，然后用CHOOSE()來(lái)輸出，可以簡(jiǎn)化不少的工作量，也使函數(shù)的可維護(hù)性更高。

=CHOOSE(MOD() 1,'1','0','X','9','8','7','6','5','4','3','2')

或

這里的計(jì)算方法不止一種，Yogurt用的是CHOOSE函數(shù)，也可以使用MID函數(shù)，這樣看上去更短一點(diǎn)。這里使用CHOOSE()是借此機(jī)會(huì)給大家介紹一個(gè)新的函數(shù)。不過(guò)不管用的是哪種方法，MOD()的結(jié)果都是需要 1的。原因很簡(jiǎn)單，函數(shù)中的幾乎所有的索引值都是從1開(kāi)始的，而余數(shù)中有0，無(wú)法識(shí)別，所以需要 1。

2.2.2.5 加入判斷，完善函數(shù)

IF函數(shù)就不用多說(shuō)了，但凡是涉及到'如果'、'判斷'這種字眼的時(shí)候，第一反應(yīng)就是IF()。這里既然是要做身份證號(hào)碼校驗(yàn)，那么就需要從以下幾個(gè)方面進(jìn)行驗(yàn)證：

身份證號(hào)碼長(zhǎng)度

身份證校驗(yàn)碼不正確

可能有小伙伴會(huì)說(shuō)為什么不檢查行政代碼和出生日期是否正確？

這就是為啥身份證會(huì)有校驗(yàn)碼的原因了。校驗(yàn)碼是需要通過(guò)相對(duì)復(fù)雜的計(jì)算才能得出來(lái)的結(jié)果，除非是某些心算大神故意為難，否則只要前17位數(shù)字有一個(gè)不一樣都會(huì)影響校驗(yàn)碼的結(jié)果，因此無(wú)需再多余對(duì)行政代碼和出生日期進(jìn)行校驗(yàn)。

《GB 11643-1999》是在1999年1月19日批準(zhǔn)，同年7月1日起實(shí)施，現(xiàn)在的身份證號(hào)碼早就已經(jīng)從15位升為了18位，因此不需要再考慮15位身份證號(hào)碼的情況了。

這里的C×代表的是Code Error(校驗(yàn)碼錯(cuò)誤)，L×代表的是Length Error(長(zhǎng)度錯(cuò)誤)。

2.3 獲取性別

=TEXT(-1^MID([@身份證號(hào)碼],17,1),'女;男')

或

=IF(MOD(--MID([@身份證號(hào)碼],17,1),2)=0,'女','男')

或

=TEXT(MOD(--MID([@身份證號(hào)碼],17,1),2),'[=0]女;男')

官方說(shuō)明：

TEXT(value,format_text)

中文翻譯：

TEXT(數(shù)值,輸出格式)

在Excel中有一種操作叫做'自定義格式'，由于這個(gè)功能的存在，TEXT函數(shù)很少會(huì)被人想起。用過(guò)'自定義格式'的小伙伴們會(huì)知道其神奇之處，而TEXT函數(shù)的作用與該功能一致，但凡是可以用在'自定義格式'中的代碼，都可以在TEXT函數(shù)中使用。

TEXT函數(shù)除了可以改變數(shù)值的輸出格式以外，還有一個(gè)就是針對(duì)不同的數(shù)字輸出不同的結(jié)果，從某種程度上可以與IF()媲美。

TEXT多條件輸出格式默認(rèn)排列順序?yàn)椋赫龜?shù);負(fù)數(shù);0;文本。

在前面咱們提到了《GB 11643-1999》標(biāo)準(zhǔn)，身份證中的15-17位順序碼，其中奇數(shù)為男性，偶數(shù)為女性，而奇偶的劃分就是能否被2整除，三位數(shù)的奇偶判斷的關(guān)鍵在于最后一位是否能為2整除，因此，只要確定第17位數(shù)字的奇偶情況即可判斷性別。

上面提到了三種函數(shù)的寫法，都可以。下面兩種是比較常見(jiàn)的寫法，也方便理解。Yogurt個(gè)人比較傾向于第一種，一來(lái)是比較短，二來(lái)用的函數(shù)也是最少的。其原理很簡(jiǎn)單，往下看會(huì)有恍然大'哦~~'的感覺(jué)。

在數(shù)學(xué)中，負(fù)數(shù)的奇數(shù)冪還是負(fù)數(shù)，偶數(shù)冪為正數(shù)。因此通過(guò)對(duì)-1進(jìn)行冪計(jì)算的出來(lái)的結(jié)果就只有正數(shù)和負(fù)數(shù)的區(qū)別了，正數(shù)為女(偶)，負(fù)數(shù)為男(奇)，再根據(jù)TEXT()多條件輸出的特點(diǎn)，輸出性別就不在話下了。

2.4 獲取出生年月

=TEXT(MID([@身份證號(hào)碼],7,8),'0-00-00')

依然是經(jīng)典的TEXT函數(shù)。

生日在身份證號(hào)碼中的位置處于7-14位，因此，用MID()截取的時(shí)候，從第7位開(kāi)始，截取8位即可。截取出來(lái)的結(jié)果用TEXT()轉(zhuǎn)換為日期格式輸出即可。非常經(jīng)典的操作。

2.5 計(jì)算年齡

=DATEDIF([@出生日期],TODAY(),'Y')

官方說(shuō)明：

DATEDIF(start_date,end_date,unit)

中文翻譯：

DATEDIF(起始日期,結(jié)束日期,返回類型)

DATEDIF()是Excel中為數(shù)不多的幾個(gè)隱藏函數(shù)之一。WPS上是可以直接輸入的。這是用于計(jì)算兩個(gè)日期之間相隔時(shí)間的函數(shù)，通過(guò)對(duì)返回類型的設(shè)置，從而返回不同的計(jì)算結(jié)果。

代碼方面的官方說(shuō)明如下

'Y'：一段時(shí)期內(nèi)的整年數(shù)。

'M'：一段時(shí)期內(nèi)的整月數(shù)。

'D'：一段時(shí)期內(nèi)的天數(shù)。

'MD'：start_date 與 end_date 之間天數(shù)之差。 忽略日期中的月份和年份。重要: 不推薦使用“MD”參數(shù)，因?yàn)榇嬖谙嚓P(guān)已知限制。

'YM'：start_date 與 end_date 之間月份之差。 忽略日期中的天和年份

'YD'：start_date 與 end_date 的日期部分之差。 忽略日期中的年份。

這個(gè)函數(shù)的輸入特點(diǎn)是：起始日期一定要比結(jié)束日期小，也就是起始日期一定要在結(jié)束日期之前。挺廢話的一句，但有時(shí)出錯(cuò)的往往都在這。

這里計(jì)算年齡是以年為單位進(jìn)行計(jì)算的，所以選用'Y'。當(dāng)然，這里其實(shí)也可以用

=YEAR(TODAY())-YEAR([@出生日期])

但輸出的結(jié)果嚴(yán)格意義上來(lái)講沒(méi)有DATEDIF()的計(jì)算結(jié)果精確。

2.6 計(jì)算生肖

十二生肖是十二地支的形象化代表，即子（鼠）、丑（牛）、寅（虎）、卯（兔）、辰（龍）、巳（蛇）、午（馬）、未（羊）、申（猴）、酉（雞）、戌（狗）、亥（豬），隨著歷史的發(fā)展逐漸融合到相生相克的民間信仰觀念，表現(xiàn)在婚姻、人生、年運(yùn)等，每一種生肖都有豐富的傳說(shuō)，并以此形成一種觀念闡釋系統(tǒng)，成為民間文化中的形象哲學(xué)，如婚配上的屬相、廟會(huì)祈禱、本命年等。現(xiàn)代，更多人把生肖作為春節(jié)的吉祥物，成為娛樂(lè)文化活動(dòng)的象征。

——百度百科《十二生肖》

=MID('猴雞狗豬鼠?；⑼谬埳唏R羊',MOD(YEAR([@出生日期]),12) 1,1)

在十二生肖是每12年一輪回，因此通過(guò)將日期除以12所得到的余數(shù)一一對(duì)應(yīng)相應(yīng)的屬相即可。不過(guò)，咱們中國(guó)人習(xí)慣于用農(nóng)歷來(lái)區(qū)別屬相，而有些小伙伴的身份證號(hào)是用的陽(yáng)歷登記，因此出來(lái)的結(jié)果存在出入也是在所難免。

2.7 計(jì)算星座

=VLOOKUP(--TEXT([@出生日期],'mdd'),,2,1)

VLOOKUP()也是大家比較常用的函數(shù)了，關(guān)于精確匹配的部分Yogurt就不細(xì)說(shuō)了，這里來(lái)聊一聊關(guān)于模糊匹配方面的部分。

模糊匹配在整個(gè)Excel查詢函數(shù)中幾乎是所有入門Excel的小伙伴相對(duì)較晚才開(kāi)始接觸的部分，一般來(lái)說(shuō)都是使用精確匹配。

模糊匹配分為升序和降序兩種。除了MATCH()支持升降兩種外，其他的例如VLOOKUP()、LOOKUP()、HLOOKUP()都僅支持升序，也就是0-9，a-z等等。

比較常用的地方就是區(qū)間匹配。例如達(dá)到某個(gè)金額區(qū)間的星級(jí)評(píng)定；達(dá)到某個(gè)分?jǐn)?shù)段為優(yōu)良中差；重量達(dá)到某個(gè)范圍的計(jì)算等等。

在這里也是一樣的，在一年里，最小的日子是1月1日，最大的日子是12月31日。可能很多小伙伴會(huì)想著怎么去通過(guò)計(jì)算日期來(lái)匹配結(jié)果，其實(shí)我們可以換個(gè)思路來(lái)考慮這個(gè)問(wèn)題，馬上就能迎刃而解。

1-12個(gè)月，1-31天，實(shí)際上兩者結(jié)合轉(zhuǎn)換為數(shù)學(xué)的表達(dá)其實(shí)也不會(huì)出現(xiàn)重復(fù)的情況。例如1月1日可以寫成101；5月18日可以寫成518，12月20日，可以寫成1220等等，這樣既可以表示時(shí)間，同時(shí)又能夠滿足構(gòu)成區(qū)間的條件。

對(duì)于摩羯座的時(shí)間范圍跨越了12月和來(lái)年1月，因此，我們將摩羯座的日期區(qū)間拆成兩個(gè)部分即可。

同理生肖屬相，由于身份證號(hào)碼存在陽(yáng)歷和農(nóng)歷的不同，而星座一般來(lái)講是以陽(yáng)歷為準(zhǔn)，因此輸出的結(jié)果也會(huì)存在一定的出入。

2.8 設(shè)置生日提醒

=TEXT(TEXT([@出生日期],'mm-dd')-TODAY(),'還有0天;;今天')

生日提醒在企業(yè)對(duì)員工的關(guān)懷中顯得十分的重要，當(dāng)然，如果是農(nóng)歷生日的話可能要多花點(diǎn)心思，畢竟陽(yáng)歷與農(nóng)歷的轉(zhuǎn)換并不是那么的有規(guī)律。但不影響我們的操作。

生日提醒從計(jì)算的角度出發(fā)，本質(zhì)上就是計(jì)算出生的日期在當(dāng)前年中與當(dāng)前日期的相隔時(shí)間。在Excel中，如果輸入的只是月和日，也就是例如：12-30；1-1等這樣的格式，都會(huì)默認(rèn)轉(zhuǎn)換為當(dāng)前系統(tǒng)年的日期，例如：2018-12-30；2018-1-1等。因此我們?cè)谔崛∪掌诘臅r(shí)候，直接用'mm-dd'的格式提取月和日后，減去當(dāng)天的時(shí)間，就是兩個(gè)時(shí)間相隔的天數(shù)。此時(shí)會(huì)出現(xiàn)正數(shù)、負(fù)數(shù)和0三種情況。

還記得前面介紹TEXT函數(shù)多條件輸出是的格式排列嗎？

正數(shù)代表還沒(méi)有到生日，負(fù)數(shù)代表生日已經(jīng)過(guò)去，0代表當(dāng)天為生日。既然是提醒，因此已經(jīng)過(guò)完的生日就不需要再進(jìn)行提醒了，那么，當(dāng)相隔結(jié)果為負(fù)數(shù)的時(shí)候就不顯示了。

2.9 計(jì)算退休年齡

法定退休年齡是指1978年5月24日第五屆全國(guó)人民代表大會(huì)常務(wù)委員會(huì)第二次會(huì)議原則批準(zhǔn)，現(xiàn)在仍然有效的《國(guó)務(wù)院關(guān)于安置老弱病殘干部的暫行辦法》和《國(guó)務(wù)院關(guān)于工人退休、退職的暫行辦法》（國(guó)發(fā)【1978】104號(hào)）文件所規(guī)定的退休年齡，現(xiàn)行退休年齡是為，男性60周歲，女性55周歲。

——百度百科《法定退休年齡》

=TEXT(EDATE([@出生日期],IF([@性別]='男',60,55)*12),'yyyy-mm-dd')

官方說(shuō)明：

EDATE(start_date,months)

中文翻譯：

EDATE(開(kāi)始日期,相隔月份)

EDATE函數(shù)是通過(guò)對(duì)增加開(kāi)始時(shí)間的月份來(lái)返回日期的函數(shù)。用法就好比我們常說(shuō)的：下個(gè)月、下下個(gè)月、3個(gè)月之后等等這樣的說(shuō)法。一年有12個(gè)月，因此在對(duì)增加年進(jìn)行計(jì)算的時(shí)候，一定要記得轉(zhuǎn)換時(shí)間。

這里分享的公式是基于男60，女55的法定退休年齡進(jìn)行的計(jì)算，而實(shí)際的企業(yè)環(huán)境下的要求可能會(huì)更加具體和細(xì)化，這里沒(méi)法兼顧，只能具體問(wèn)題具體分析。但總的操作原理是一致的——在出生日期的基礎(chǔ)上增加指定的年數(shù)×12，得到的日期就是退休日期，這個(gè)日期是以身份證號(hào)上的登記日期為準(zhǔn)的，不存在陽(yáng)歷和陰歷這一說(shuō)。

2.10 獲取籍貫

=PHONETIC(OFFSET(表1[[#標(biāo)題],[代碼]],MATCH(LEFT([@身份證號(hào)碼],6),表1[代碼],0),1,1,3))

官方說(shuō)明：

PHONETIC(reference)

中文翻譯：

PHONETIC(引用區(qū)域)

PHONETIC()是一個(gè)比較特別的函數(shù)，像是為漢語(yǔ)定做的一樣。這個(gè)函數(shù)只能獲取含有拼音信息的字符串，對(duì)于一個(gè)引用區(qū)域來(lái)說(shuō)，只要是包含了拼音信息的單元格，就會(huì)將其全部合并到一個(gè)區(qū)域。一般情況下我們?cè)诤喜卧褡址臅r(shí)候往往都是使用&作為連接符，一個(gè)個(gè)單元格的點(diǎn)擊實(shí)現(xiàn)連接。而在Office 365中有TEXTJOIN，可以很方便的批量連接各種字符串，而在其他版本的Office里無(wú)法實(shí)現(xiàn)。PHONETIC()是唯一一個(gè)可以有限度的實(shí)現(xiàn)批量連接字符串的函數(shù)。

官方說(shuō)明：

OFFSET(reference,rows,cols,[height],[width])

中文翻譯：

OFFSET(引用區(qū)域或單元格,偏移行數(shù),偏移列數(shù),[返回結(jié)果行范圍],[返回結(jié)果列范圍])

OFFSET()是所有查詢函數(shù)里需要一定想象力來(lái)運(yùn)用的函數(shù)，它返回的結(jié)果是一個(gè)引用，這是其他查詢函數(shù)所做不到的。

OFFSET()是通過(guò)一個(gè)引用位置或者引用區(qū)域的左上角的位置，根據(jù)指定的偏移行數(shù)和偏移列數(shù)來(lái)返回指定行范圍和列范圍的引用單元格或引用區(qū)域。

在實(shí)際運(yùn)用過(guò)程中常與MATCH()一起搭配使用。

官方說(shuō)明：

MATCH(lookup_value,lookup_array,[match_type])

中文翻譯：

MATCH(查詢值,在哪一列或在哪一行找,[精確匹配還是模糊匹配])

MATCH()是一個(gè)很百搭的函數(shù)，幾乎可以與任何一個(gè)查詢函數(shù)搭配在一起，經(jīng)典的搭配是INDEX() MATCH()。該函數(shù)是輸出查詢值在某一行或者某一列中相對(duì)位置。通過(guò)這個(gè)位置信息來(lái)與其他查詢函數(shù)搭配使用，進(jìn)而返回最終的匹配結(jié)果。

這里為了返回的結(jié)果能夠有間隔，Yogurt給數(shù)據(jù)源的每一個(gè)單元格都加了一個(gè)空格，這樣可以使得PHONETIC()輸出的結(jié)果之間能夠間隔，看著更舒服。

這里的原理很簡(jiǎn)單，通過(guò)MATCH()來(lái)定位行政區(qū)劃代碼在數(shù)據(jù)庫(kù)中的位置，然后以數(shù)據(jù)庫(kù)中A1的位置，也就是【表1[[#標(biāo)題],[代碼]]】為參照系，向下偏移MATCH()所定位到的位置；然后向右偏移1列，也就是【省】列，行范圍為1，列范圍為3，即【省】列到【區(qū)】列；最終輸出的結(jié)果為一個(gè)含有三個(gè)文本的數(shù)組。

最終用PHONETIC()將這個(gè)數(shù)組連接起來(lái)，輸出即可。

3

后記

以上就是所有關(guān)于身份證號(hào)碼的處理思路，其他的處理方式大同小異，可以盡情發(fā)揮。

下面把成品的全圖展示給大家。

如果這是一個(gè)要我們自己來(lái)填寫的表的話，一個(gè)個(gè)打上去未免也太花時(shí)間了，還不能保證一定是正確的。而在填充好函數(shù)和公式以后，在填寫信息的時(shí)候就只要把姓名和身份證號(hào)碼填上，剩下的部分就只需要稍微的核對(duì)即可。

在制表的時(shí)候，最花時(shí)間且最容易出錯(cuò)的部分就是錄入基礎(chǔ)信息的時(shí)候，有些基礎(chǔ)信息是可以受一些規(guī)則約束而獲取或者計(jì)算出來(lái)的。就比如這里的行政區(qū)劃代碼，Yogurt并沒(méi)有一個(gè)個(gè)打上去，而是選擇去找權(quán)威部門提供的公開(kāi)信息，對(duì)其信息進(jìn)行獲取和整合得到的。錄入的身份證號(hào)碼也可以是根據(jù)各種要求而輸出各種所需的信息。

當(dāng)然，這種方法并不是說(shuō)臨時(shí)要就能直接給做出來(lái)的，Yogurt光是花在搜集行政區(qū)劃代碼上的時(shí)間就用了5、6個(gè)小時(shí)，對(duì)于一些比較著急的工作，可以根據(jù)實(shí)際情況來(lái)選擇不同的處理方式。當(dāng)然，如果這個(gè)數(shù)據(jù)庫(kù)早就已經(jīng)建立好了，那就更加不錯(cuò)了，哈哈哈哈。

好啦，本期內(nèi)容就到這里啦，如果還有什么好的想法和建議，不妨在本文下方留言，咱們一起交流一下。覺(jué)得好的話，別忘了在右下角給Yogurt點(diǎn)個(gè)贊或者贊賞支持一下，實(shí)在不行，轉(zhuǎn)發(fā)一下也是可以的，哈哈哈，要的有點(diǎn)多，就先這樣吧。

臨近年關(guān)，Yogurt的工作會(huì)比較忙，更新速度也許會(huì)受到影響，不管怎么樣還是會(huì)盡可能抓緊時(shí)間更新，感謝大家的支持哈。