Hello，各位小伙伴們大家好呀，上一期和大家分享了'Excel從入門到放棄'的一些階段，有小伙伴@melo留言說能不能分享一期有關(guān)于身份證提取戶籍和年齡的案例。
Yogurt花了大概一天的時間把國家統(tǒng)計局2009年、2013-2016年的三級行政區(qū)劃給整理了一下。
PS：順便吐槽一下國家統(tǒng)計局的網(wǎng)站。。。不知道是和Yogurt一樣同時爬數(shù)據(jù)的人太多了呢還是來不及維護(hù)。。。
2017年天津市的二級頁面亂碼
2017年湖北省的二級界面亂碼
這里截出來的圖都是個例，其他的就沒看了，像這種情況還不少。。。早在幾個月之前，重慶市的二級界面也出現(xiàn)過亂碼的情況，那次也沒爬成功，也算了，所以，知道為嘛只爬了09年和13-16年的了吧。
當(dāng)然了，即便是這樣，還是要感謝那些在背后默默付出的國家統(tǒng)計員們，沒有你們在一線一點一滴的記錄，也不可能把全國3000多個區(qū)級單位統(tǒng)計上報，當(dāng)然往下的街道和居委會更是數(shù)不勝數(shù)了，萬分感謝你們的辛勤付出。
截止到發(fā)稿前，網(wǎng)站恢復(fù)了，之前發(fā)生亂碼的情況應(yīng)該是由于訪問的人次過多導(dǎo)致的。
咱們會到正題。
關(guān)于身份證里的一些計算方式，Yogurt在去年寫了一篇，
不過現(xiàn)在回頭看的時候，不知道當(dāng)時為嘛少寫了一個身份證驗證的步驟。而且在現(xiàn)在看來，當(dāng)時涵蓋的內(nèi)容還有點不全面，本期Yogurt打算完善一下關(guān)于身份證的所有處理方式，將會和大家一起分享如何使用一個身份證號碼來完成大部分個人信息的填寫，減少錄入的工作量。
1
基礎(chǔ)數(shù)據(jù)準(zhǔn)備
1.1 準(zhǔn)備身份證代碼數(shù)據(jù)庫
在身份證號碼中可以很直觀的獲取各種各樣的信息，唯一一個沒法直接獲取的就是身份證號碼前6位的行政區(qū)劃代碼所代表的籍貫信息。為了能夠運(yùn)用Excel來獲取這一信息，首先我們需要先準(zhǔn)備一份盡可能完善的身份證行政區(qū)劃代碼數(shù)據(jù)庫。
這里Yogurt使用的Python 3.7.1 request庫對國家統(tǒng)計局的數(shù)據(jù)進(jìn)行多次爬取得到的數(shù)據(jù)。
python代碼如下：
由于國家統(tǒng)計局的網(wǎng)站不需要其他的驗證，又是靜態(tài)網(wǎng)頁，所以直接從網(wǎng)頁原代碼里爬取就可以了。具體的Python用法沒法一句話兩句話講清楚，但可以和大家分享一下我的思路。
http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2009/index.html
以2009年的行政區(qū)劃為例，打開國家統(tǒng)計局的網(wǎng)站，然后【右鍵】-【查看源代碼】，然后就可以看到這一頁的信息，我們需要的內(nèi)容就是這里選中的部分。
北京市
天津市
河北省
山西省
內(nèi)蒙古自治區(qū)
遼寧省
吉林省
黑龍江省
上海市
江蘇省
浙江省
安徽省
福建省
江西省
山東省
河南省
湖北省
湖南省
廣東省
廣西壯族自治區(qū)
海南省
重慶市
四川省
貴州省
云南省
西藏自治區(qū)
陜西省
甘肅省
青海省
寧夏回族自治區(qū)
新疆維吾爾自治區(qū)
臺灣省
香港特別行政區(qū)
澳門特別行政區(qū)
這里含的信息有網(wǎng)址和省份名稱。我們可以用正則表達(dá)式把這里的信息提取出來。
.*?(.*?)
關(guān)于正則表達(dá)式的相關(guān)內(nèi)容，后期如果有機(jī)會可以和大家多分享一些，但這一期里篇幅有限不能介紹太多，大家可以去百度了解一下。
代碼中的網(wǎng)址是縮寫，爬取信息需要完整的網(wǎng)址，所以再加上前面的網(wǎng)址內(nèi)容湊成完整的網(wǎng)址就是最終的爬取網(wǎng)址。下一級的網(wǎng)址獲取方法與這一階段一致。
大致的流程圖是上述這樣，操作起來其實稍微比流程圖畫的更簡單一點。
接著再把這些獲取到的數(shù)據(jù)進(jìn)行匯總、去重、整合，形成完整的身份證代碼數(shù)據(jù)庫。由于經(jīng)過了多年，行政區(qū)域發(fā)生了許多變化，我們的身份證號是唯一的，從入戶開始就沒有發(fā)生過變化，因此身份證代碼數(shù)據(jù)庫不能以最新的行政區(qū)劃為準(zhǔn)，這也就是為什么要從2009年開始獲取并整合。
Yogurt實際測試的時候發(fā)現(xiàn)還是有很多行政代碼沒有辦法識別出來，接著就到人力資源與社會保障部的官網(wǎng)上再下載了一份截止到2018年2月5號全國行政區(qū)劃代碼表。
下載下來的表格長這樣，要達(dá)到我們使用的標(biāo)準(zhǔn)還是差了一些，因此還需要對其進(jìn)行稍微進(jìn)行修飾、整合、去重，由于人力資源與社會保障部官網(wǎng)的數(shù)據(jù)相對全面一些，因此最終形成以人力資源與社會保障部官網(wǎng)數(shù)據(jù)為主，國家統(tǒng)計局?jǐn)?shù)據(jù)為輔的共4773條身份證行政代碼數(shù)據(jù)庫。
即便這份數(shù)據(jù)是Yogurt想盡了一切辦法整理出來的，但眾所周知，我國幅員遼闊，地廣人多，自改革開放以來社會發(fā)展迅速，很多地名都發(fā)生了變化，最終根據(jù)數(shù)據(jù)庫匹配出來的地名可能會與身份證稍微會有點出入，這就需要在實際工作中對其進(jìn)行核對了。
1.2 準(zhǔn)備計算表
Yogurt把所有能想到從身份證號中獲取或衍生的信息內(nèi)容都列了出來，也許大家也沒想到一個身份證號里能包含這么多信息可以獲取和計算的吧，如果還有能想到的獲取信息不妨給Yogurt留言，咱們相互交流。
我們接下來的分享將會圍繞這份表格來進(jìn)行。
溫馨提示：該表格中的人名、身份證號全部都是為了此次推送隨機(jī)生成的，存在雷同的概率應(yīng)該是比較低的，如果真的雷同了，請私聊聯(lián)系Yogurt。
2
實操處理
2.0 前期準(zhǔn)備
一直以來我們都習(xí)慣于下拉填充公式，全部做完以后才對表格進(jìn)行排版。這次Yogurt借此機(jī)會，和大家分享一種新的制表方式，相信對大家會有所幫助。
【套用表格樣式】這個玩法在Yogurt接觸到小伙伴中，很少會這么玩的，不過真的很能夠提高寫函數(shù)的效率，大家接著往下看就知道了。
快捷鍵是【Ctrl】 【L】，快捷鍵設(shè)置的是默認(rèn)樣式，如果想直接就選好樣式的話，還是用鼠標(biāo)選吧。
用好這個方法有個前提，需要表格是100%的數(shù)據(jù)庫式制表法——即不能有任何一個合并單元格。
OK，前期準(zhǔn)備完成，接下來就可以進(jìn)入我們的實操環(huán)節(jié)了。
2.1 隱藏身份證號碼
隱藏身份證號碼有兩種方法。一種是通用的函數(shù)法，另一種則是Excel 2013版及以上的【Ctrl】 【E】大法。
2.1.1 方法一：通用函數(shù)法
=REPLACE([@身份證號碼],7,8,REPT('*',8))
=REPLACE(D3,7,8,REPT('*',8))
在【套用表格樣式】的環(huán)境下寫函數(shù)，最大的好處就是對于需要連續(xù)填充的表格來說，只需要寫一次函數(shù)，打完回車，剩下的就自動填充完成了。
這里的函數(shù)寫法也和傳統(tǒng)的寫法不太一樣，大家參照下面的一個公式就知道了。這是因為設(shè)置了【套用表格樣式】之后，表格中的計算就會基于其命名的特點來進(jìn)行，與數(shù)據(jù)庫中的計算方式大同小異。
官方說明：
REPLACE(old_text,start_num,num_chars,new_text)
中文翻譯：
REPLACE(要提換的內(nèi)容,從哪開始,要替換多少個,替換成什么)
REPLACE函數(shù)是通過截取數(shù)量進(jìn)行替換的函數(shù)，對于編碼格式、字符長度一致的字符串的替換優(yōu)勢相當(dāng)強(qiáng)。很多小伙伴可能更加習(xí)慣于使用SUBSTITUTE()進(jìn)行替換，但在這種情況下，SUBSTITUTE()就顯得捉襟見肘了。
官方說明：
REPT(text,number_times)
中文翻譯：
REPT(需要重復(fù)的字符/字符串,重復(fù)次數(shù))
REPT函數(shù)是對指定字符或字符串進(jìn)行重復(fù)輸出的一種函數(shù)。用的人比較少，但必要時候效果還是不錯的。
在沒有使用REPT()的情況下，REPLACE()中的第四個參數(shù)就寫成'*********'。少的時候沒什么，多了就容易出錯。比如Yogurt在這里打了9個'*'，不仔細(xì)看真的容易看錯。
2.1.2 方法二：【Ctrl】 【E】大法
Excel 2013版及以上中有一個非常實用的快捷鍵——【Ctrl】 【E】，它可以快速填充與第一個輸入的單元格相同的操作。對于很多一次性的操作，可以提高不少的工作效率。
兩種方法，各有各的好處，不過呢，既然是考慮到了要隱藏身份證信息的情況，因此如果是要發(fā)送出去的，記得要把發(fā)送的信息復(fù)制出去，然后粘貼為文本哦，直接使用保護(hù)工作表的安全系數(shù)是很低的。所有涉及與員工隱私信息、企業(yè)隱私信息相關(guān)的內(nèi)容，一旦是需要發(fā)送給他人的，建議全部處理并隱藏，一律不能保留原始數(shù)據(jù)源。
2.2 驗證身份證號碼
公民身份證號碼是經(jīng)過了我國國家質(zhì)量技術(shù)監(jiān)督局的提出，多個相關(guān)單位共同起草的國家標(biāo)準(zhǔn)——《GB 11643-1999》
http://www.gb688.cn/bzgk/gb/newGbInfo?hcno=080D6FBF2BB468F9007657F26D60013E
在公開的國家標(biāo)準(zhǔn)中，對校驗碼的計算有著詳細(xì)的說明。
而我們要做的就是將國家標(biāo)準(zhǔn)中提到的計算方法轉(zhuǎn)換為Excel的計算方法即可。
2.2.1 原理解釋
身份證從二代開始，身份證號碼總共有18位，由6位行政代碼 8位出生日期 3位順序碼 1位校驗碼構(gòu)成。至于網(wǎng)上提到的順序碼為當(dāng)?shù)嘏沙鏊a的說法，Yogurt暫時還沒有找到比較官方的解釋，我想《GB 11643-1999》里已經(jīng)說得很詳細(xì)了。
上圖中，將身份證號碼前17位分別單獨取出來，一一與對應(yīng)的加權(quán)系數(shù)相乘，然后將17個結(jié)果相加，最終之和與11相除所得的余數(shù)對應(yīng)的校驗碼就是身份證最后一位的校驗碼。
上圖是原理，而我們應(yīng)用到實際運(yùn)算中可不能做得這么麻煩，需要將公式簡化。
2.2.2 公式含義
2.2.2.1 拆分身份證號碼
將身份證號碼拆成17個單獨的數(shù)字，這里需要用到MID函數(shù)。
官方說明：
MID(text,start_num,num_chars)
中文翻譯：
MID(待截取字符串,從哪開始,截取多長)
MID函數(shù)與LEFT()、RIGHT()一起并稱'截取三劍客'，其中MID函數(shù)的靈活性相對較高，結(jié)合數(shù)組使用，能夠收獲意想不到的效果。
=--MID([@身份證號碼],COLUMN($A:$Q),1)
一般情況下使用ROW()會比較多，突然使用COLUMN函數(shù)也許大家會有點意外。和ROW函數(shù)一樣，都是獲取連續(xù)數(shù)字的好方法，而不同的是ROW()得到的結(jié)果是橫向數(shù)組，而COLUMN()是縱向。因此在MID中得到的結(jié)果也是不一樣的。而我們這里由于為了規(guī)避'三鍵結(jié)束'，而涉及到了SUMPRODUCT函數(shù)，因此選擇使用了COLUMN()，具體會在下面提到。
COLUMN(A:Q)，其實就是1到17。結(jié)果為，再經(jīng)過MID()截取了相關(guān)的數(shù)值之后，以上述身份證號碼為例，結(jié)果為。大家這里會發(fā)現(xiàn)中間的分隔符號有變化。如果使用ROW()的話，這里的分隔符號會恰恰相反。這里就好比橫向輸出與縱向輸出的不同。
黃色區(qū)域?qū)懗蓴?shù)組為，綠色區(qū)域?qū)憯?shù)組為。這里MID輸出結(jié)果同理。
截取的結(jié)果為文本型數(shù)字，因此需要加上--來轉(zhuǎn)換為數(shù)值，或者*1，又或者使用VALUE函數(shù)。Yogurt個人比較習(xí)慣用--，看個人喜好。
2.2.2.2 身份證號碼加權(quán)計算
每一個數(shù)字與其對應(yīng)的加權(quán)因子相乘，然后相加起來。有些小伙伴在這里可能會有點犯難，畢竟數(shù)組計算和單元格計算不一樣，單元格里可以逐一進(jìn)行選擇，然后每一個乘以一個對應(yīng)加權(quán)因子，最后將其加起來即可。也有些小伙伴可能會用SUM(拆分結(jié)果數(shù)組*加權(quán)因子)，然后'三鍵結(jié)束'，但這個方法有點麻煩，Yogurt的方法可以避免使用'三鍵'，就是SUMPRODUCT函數(shù)。
官方說明：
SUMPRODUCT(array1,[array2],......)
中文翻譯：
SUMPRODUCT(數(shù)組1,[數(shù)組2],......)
SUMPRODUCT函數(shù)簡單理解的話可以認(rèn)為是A*B C*D，也就是把所有參數(shù)里的數(shù)組對應(yīng)相乘，然后將所有乘積相加的這么一個函數(shù)。
計算前提1：數(shù)組的大小要一致。不能說比如一個數(shù)組有15個數(shù)字，另一個是16位數(shù)字。
計算前提2：數(shù)組方向要一致，橫向數(shù)組和縱向數(shù)組之間不能進(jìn)行計算。
=SUMPRODUCT(--MID(),)
這里根據(jù)計算前提2中的限制，也就是為什么MID函數(shù)里要使用COLUMN()而不使用ROW()了。當(dāng)然，如果覺得使用ROW()比較方便的話，那就把加權(quán)因子中的逗號改為分號，將其變?yōu)榭v向數(shù)組進(jìn)行計算就可以了。
2.2.2.3 求余數(shù)
余數(shù)函數(shù)我想大家應(yīng)該都還記得——MOD()
官方說明：
MOD(number,divisor)
中文翻譯：
MOD(被除數(shù),除數(shù))
MOD函數(shù)的原理很簡單，就是小學(xué)時候?qū)W的求余數(shù)的方法。
=MOD(SUMPRODUCT(--MID()),11)
至于為啥除以11，應(yīng)該是由于這套標(biāo)準(zhǔn)所采用的ISO 7064:1983(現(xiàn)標(biāo)準(zhǔn)號為ISO 7064:2003)。這份標(biāo)準(zhǔn)在道客巴巴上可以查看，下載需要2000積分，而且以Yogurt這蹩腳的英文水平，看得有點吃力，所以就不詳細(xì)解釋了，如果有需要的小伙伴可以自行搜索下載。
2.2.2.4 求校驗碼
根據(jù)上述算出來余數(shù)，輸出指定位置的校驗碼，這計算身份證校驗碼的工作就算是完成了。
官方說明：
CHOOSE(index_num,value1,[value2],......)
中文翻譯：
CHOOSE(索引值,數(shù)值1,[數(shù)值2],......)
CHOOSE函數(shù)是一個比較尷尬的函數(shù)，從下面的另一種方法就可以看出，MID函數(shù)分分鐘就可以代替它，不過呢，對于不是那么規(guī)律的輸出結(jié)果來說，CHOOSE函數(shù)的特長就發(fā)揮出來了。有時候我們可能會對滿足某個條件的值進(jìn)行指定結(jié)果的輸出，一般很多小伙伴可能都會選擇使用IF()進(jìn)行嵌套，對于嵌套過多的情況，寫起來不方便，后期維護(hù)的時候也不方便，這時可以把滿足的條件轉(zhuǎn)換為索引值，然后用CHOOSE()來輸出，可以簡化不少的工作量，也使函數(shù)的可維護(hù)性更高。
=CHOOSE(MOD() 1,'1','0','X','9','8','7','6','5','4','3','2')
或
這里的計算方法不止一種，Yogurt用的是CHOOSE函數(shù)，也可以使用MID函數(shù)，這樣看上去更短一點。這里使用CHOOSE()是借此機(jī)會給大家介紹一個新的函數(shù)。不過不管用的是哪種方法，MOD()的結(jié)果都是需要 1的。原因很簡單，函數(shù)中的幾乎所有的索引值都是從1開始的，而余數(shù)中有0，無法識別，所以需要 1。
2.2.2.5 加入判斷，完善函數(shù)
IF函數(shù)就不用多說了，但凡是涉及到'如果'、'判斷'這種字眼的時候，第一反應(yīng)就是IF()。這里既然是要做身份證號碼校驗，那么就需要從以下幾個方面進(jìn)行驗證：
身份證號碼長度
身份證校驗碼不正確
可能有小伙伴會說為什么不檢查行政代碼和出生日期是否正確？
這就是為啥身份證會有校驗碼的原因了。校驗碼是需要通過相對復(fù)雜的計算才能得出來的結(jié)果，除非是某些心算大神故意為難，否則只要前17位數(shù)字有一個不一樣都會影響校驗碼的結(jié)果，因此無需再多余對行政代碼和出生日期進(jìn)行校驗。
《GB 11643-1999》是在1999年1月19日批準(zhǔn)，同年7月1日起實施，現(xiàn)在的身份證號碼早就已經(jīng)從15位升為了18位，因此不需要再考慮15位身份證號碼的情況了。
這里的C×代表的是Code Error(校驗碼錯誤)，L×代表的是Length Error(長度錯誤)。
2.3 獲取性別
=TEXT(-1^MID([@身份證號碼],17,1),'女;男')
或
=IF(MOD(--MID([@身份證號碼],17,1),2)=0,'女','男')
或
=TEXT(MOD(--MID([@身份證號碼],17,1),2),'[=0]女;男')
官方說明：
TEXT(value,format_text)
中文翻譯：
TEXT(數(shù)值,輸出格式)
在Excel中有一種操作叫做'自定義格式'，由于這個功能的存在，TEXT函數(shù)很少會被人想起。用過'自定義格式'的小伙伴們會知道其神奇之處，而TEXT函數(shù)的作用與該功能一致，但凡是可以用在'自定義格式'中的代碼，都可以在TEXT函數(shù)中使用。
TEXT函數(shù)除了可以改變數(shù)值的輸出格式以外，還有一個就是針對不同的數(shù)字輸出不同的結(jié)果，從某種程度上可以與IF()媲美。
TEXT多條件輸出格式默認(rèn)排列順序為：正數(shù);負(fù)數(shù);0;文本。
在前面咱們提到了《GB 11643-1999》標(biāo)準(zhǔn)，身份證中的15-17位順序碼，其中奇數(shù)為男性，偶數(shù)為女性，而奇偶的劃分就是能否被2整除，三位數(shù)的奇偶判斷的關(guān)鍵在于最后一位是否能為2整除，因此，只要確定第17位數(shù)字的奇偶情況即可判斷性別。
上面提到了三種函數(shù)的寫法，都可以。下面兩種是比較常見的寫法，也方便理解。Yogurt個人比較傾向于第一種，一來是比較短，二來用的函數(shù)也是最少的。其原理很簡單，往下看會有恍然大'哦~~'的感覺。
在數(shù)學(xué)中，負(fù)數(shù)的奇數(shù)冪還是負(fù)數(shù)，偶數(shù)冪為正數(shù)。因此通過對-1進(jìn)行冪計算的出來的結(jié)果就只有正數(shù)和負(fù)數(shù)的區(qū)別了，正數(shù)為女(偶)，負(fù)數(shù)為男(奇)，再根據(jù)TEXT()多條件輸出的特點，輸出性別就不在話下了。
2.4 獲取出生年月
=TEXT(MID([@身份證號碼],7,8),'0-00-00')
依然是經(jīng)典的TEXT函數(shù)。
生日在身份證號碼中的位置處于7-14位，因此，用MID()截取的時候，從第7位開始，截取8位即可。截取出來的結(jié)果用TEXT()轉(zhuǎn)換為日期格式輸出即可。非常經(jīng)典的操作。
2.5 計算年齡
=DATEDIF([@出生日期],TODAY(),'Y')
官方說明：
DATEDIF(start_date,end_date,unit)
中文翻譯：
DATEDIF(起始日期,結(jié)束日期,返回類型)
DATEDIF()是Excel中為數(shù)不多的幾個隱藏函數(shù)之一。WPS上是可以直接輸入的。這是用于計算兩個日期之間相隔時間的函數(shù)，通過對返回類型的設(shè)置，從而返回不同的計算結(jié)果。
代碼方面的官方說明如下
'Y'：一段時期內(nèi)的整年數(shù)。
'M'：一段時期內(nèi)的整月數(shù)。
'D'：一段時期內(nèi)的天數(shù)。
'MD'：start_date 與 end_date 之間天數(shù)之差。 忽略日期中的月份和年份。重要: 不推薦使用“MD”參數(shù)，因為存在相關(guān)已知限制。
'YM'：start_date 與 end_date 之間月份之差。 忽略日期中的天和年份
'YD'：start_date 與 end_date 的日期部分之差。 忽略日期中的年份。
這個函數(shù)的輸入特點是：起始日期一定要比結(jié)束日期小，也就是起始日期一定要在結(jié)束日期之前。挺廢話的一句，但有時出錯的往往都在這。
這里計算年齡是以年為單位進(jìn)行計算的，所以選用'Y'。當(dāng)然，這里其實也可以用
=YEAR(TODAY())-YEAR([@出生日期])
但輸出的結(jié)果嚴(yán)格意義上來講沒有DATEDIF()的計算結(jié)果精確。
2.6 計算生肖
十二生肖是十二地支的形象化代表，即子（鼠）、丑（牛）、寅（虎）、卯（兔）、辰（龍）、巳（蛇）、午（馬）、未（羊）、申（猴）、酉（雞）、戌（狗）、亥（豬），隨著歷史的發(fā)展逐漸融合到相生相克的民間信仰觀念，表現(xiàn)在婚姻、人生、年運(yùn)等，每一種生肖都有豐富的傳說，并以此形成一種觀念闡釋系統(tǒng)，成為民間文化中的形象哲學(xué)，如婚配上的屬相、廟會祈禱、本命年等。現(xiàn)代，更多人把生肖作為春節(jié)的吉祥物，成為娛樂文化活動的象征。
——百度百科《十二生肖》
=MID('猴雞狗豬鼠牛虎兔龍蛇馬羊',MOD(YEAR([@出生日期]),12) 1,1)
在十二生肖是每12年一輪回，因此通過將日期除以12所得到的余數(shù)一一對應(yīng)相應(yīng)的屬相即可。不過，咱們中國人習(xí)慣于用農(nóng)歷來區(qū)別屬相，而有些小伙伴的身份證號是用的陽歷登記，因此出來的結(jié)果存在出入也是在所難免。
2.7 計算星座
=VLOOKUP(--TEXT([@出生日期],'mdd'),,2,1)
VLOOKUP()也是大家比較常用的函數(shù)了，關(guān)于精確匹配的部分Yogurt就不細(xì)說了，這里來聊一聊關(guān)于模糊匹配方面的部分。
模糊匹配在整個Excel查詢函數(shù)中幾乎是所有入門Excel的小伙伴相對較晚才開始接觸的部分，一般來說都是使用精確匹配。
模糊匹配分為升序和降序兩種。除了MATCH()支持升降兩種外，其他的例如VLOOKUP()、LOOKUP()、HLOOKUP()都僅支持升序，也就是0-9，a-z等等。
比較常用的地方就是區(qū)間匹配。例如達(dá)到某個金額區(qū)間的星級評定；達(dá)到某個分?jǐn)?shù)段為優(yōu)良中差；重量達(dá)到某個范圍的計算等等。
在這里也是一樣的，在一年里，最小的日子是1月1日，最大的日子是12月31日?？赡芎芏嘈』锇闀胫趺慈ネㄟ^計算日期來匹配結(jié)果，其實我們可以換個思路來考慮這個問題，馬上就能迎刃而解。
1-12個月，1-31天，實際上兩者結(jié)合轉(zhuǎn)換為數(shù)學(xué)的表達(dá)其實也不會出現(xiàn)重復(fù)的情況。例如1月1日可以寫成101；5月18日可以寫成518，12月20日，可以寫成1220等等，這樣既可以表示時間，同時又能夠滿足構(gòu)成區(qū)間的條件。
對于摩羯座的時間范圍跨越了12月和來年1月，因此，我們將摩羯座的日期區(qū)間拆成兩個部分即可。
同理生肖屬相，由于身份證號碼存在陽歷和農(nóng)歷的不同，而星座一般來講是以陽歷為準(zhǔn)，因此輸出的結(jié)果也會存在一定的出入。
2.8 設(shè)置生日提醒
=TEXT(TEXT([@出生日期],'mm-dd')-TODAY(),'還有0天;;今天')
生日提醒在企業(yè)對員工的關(guān)懷中顯得十分的重要，當(dāng)然，如果是農(nóng)歷生日的話可能要多花點心思，畢竟陽歷與農(nóng)歷的轉(zhuǎn)換并不是那么的有規(guī)律。但不影響我們的操作。
生日提醒從計算的角度出發(fā)，本質(zhì)上就是計算出生的日期在當(dāng)前年中與當(dāng)前日期的相隔時間。在Excel中，如果輸入的只是月和日，也就是例如：12-30；1-1等這樣的格式，都會默認(rèn)轉(zhuǎn)換為當(dāng)前系統(tǒng)年的日期，例如：2018-12-30；2018-1-1等。因此我們在提取日期的時候，直接用'mm-dd'的格式提取月和日后，減去當(dāng)天的時間，就是兩個時間相隔的天數(shù)。此時會出現(xiàn)正數(shù)、負(fù)數(shù)和0三種情況。
還記得前面介紹TEXT函數(shù)多條件輸出是的格式排列嗎？
正數(shù)代表還沒有到生日，負(fù)數(shù)代表生日已經(jīng)過去，0代表當(dāng)天為生日。既然是提醒，因此已經(jīng)過完的生日就不需要再進(jìn)行提醒了，那么，當(dāng)相隔結(jié)果為負(fù)數(shù)的時候就不顯示了。
2.9 計算退休年齡
法定退休年齡是指1978年5月24日第五屆全國人民代表大會常務(wù)委員會第二次會議原則批準(zhǔn)，現(xiàn)在仍然有效的《國務(wù)院關(guān)于安置老弱病殘干部的暫行辦法》和《國務(wù)院關(guān)于工人退休、退職的暫行辦法》（國發(fā)【1978】104號）文件所規(guī)定的退休年齡，現(xiàn)行退休年齡是為，男性60周歲，女性55周歲。
——百度百科《法定退休年齡》
=TEXT(EDATE([@出生日期],IF([@性別]='男',60,55)*12),'yyyy-mm-dd')
官方說明：
EDATE(start_date,months)
中文翻譯：
EDATE(開始日期,相隔月份)
EDATE函數(shù)是通過對增加開始時間的月份來返回日期的函數(shù)。用法就好比我們常說的：下個月、下下個月、3個月之后等等這樣的說法。一年有12個月，因此在對增加年進(jìn)行計算的時候，一定要記得轉(zhuǎn)換時間。
這里分享的公式是基于男60，女55的法定退休年齡進(jìn)行的計算，而實際的企業(yè)環(huán)境下的要求可能會更加具體和細(xì)化，這里沒法兼顧，只能具體問題具體分析。但總的操作原理是一致的——在出生日期的基礎(chǔ)上增加指定的年數(shù)×12，得到的日期就是退休日期，這個日期是以身份證號上的登記日期為準(zhǔn)的，不存在陽歷和陰歷這一說。
2.10 獲取籍貫
=PHONETIC(OFFSET(表1[[#標(biāo)題],[代碼]],MATCH(LEFT([@身份證號碼],6),表1[代碼],0),1,1,3))
官方說明：
PHONETIC(reference)
中文翻譯：
PHONETIC(引用區(qū)域)
PHONETIC()是一個比較特別的函數(shù)，像是為漢語定做的一樣。這個函數(shù)只能獲取含有拼音信息的字符串，對于一個引用區(qū)域來說，只要是包含了拼音信息的單元格，就會將其全部合并到一個區(qū)域。一般情況下我們在合并單元格字符串的時候往往都是使用&作為連接符，一個個單元格的點擊實現(xiàn)連接。而在Office 365中有TEXTJOIN，可以很方便的批量連接各種字符串，而在其他版本的Office里無法實現(xiàn)。PHONETIC()是唯一一個可以有限度的實現(xiàn)批量連接字符串的函數(shù)。
官方說明：
OFFSET(reference,rows,cols,[height],[width])
中文翻譯：
OFFSET(引用區(qū)域或單元格,偏移行數(shù),偏移列數(shù),[返回結(jié)果行范圍],[返回結(jié)果列范圍])
OFFSET()是所有查詢函數(shù)里需要一定想象力來運(yùn)用的函數(shù)，它返回的結(jié)果是一個引用，這是其他查詢函數(shù)所做不到的。
OFFSET()是通過一個引用位置或者引用區(qū)域的左上角的位置，根據(jù)指定的偏移行數(shù)和偏移列數(shù)來返回指定行范圍和列范圍的引用單元格或引用區(qū)域。
在實際運(yùn)用過程中常與MATCH()一起搭配使用。
官方說明：
MATCH(lookup_value,lookup_array,[match_type])
中文翻譯：
MATCH(查詢值,在哪一列或在哪一行找,[精確匹配還是模糊匹配])
MATCH()是一個很百搭的函數(shù)，幾乎可以與任何一個查詢函數(shù)搭配在一起，經(jīng)典的搭配是INDEX() MATCH()。該函數(shù)是輸出查詢值在某一行或者某一列中相對位置。通過這個位置信息來與其他查詢函數(shù)搭配使用，進(jìn)而返回最終的匹配結(jié)果。
這里為了返回的結(jié)果能夠有間隔，Yogurt給數(shù)據(jù)源的每一個單元格都加了一個空格，這樣可以使得PHONETIC()輸出的結(jié)果之間能夠間隔，看著更舒服。
這里的原理很簡單，通過MATCH()來定位行政區(qū)劃代碼在數(shù)據(jù)庫中的位置，然后以數(shù)據(jù)庫中A1的位置，也就是【表1[[#標(biāo)題],[代碼]]】為參照系，向下偏移MATCH()所定位到的位置；然后向右偏移1列，也就是【省】列，行范圍為1，列范圍為3，即【省】列到【區(qū)】列；最終輸出的結(jié)果為一個含有三個文本的數(shù)組。
最終用PHONETIC()將這個數(shù)組連接起來，輸出即可。
3
后記
以上就是所有關(guān)于身份證號碼的處理思路，其他的處理方式大同小異，可以盡情發(fā)揮。
下面把成品的全圖展示給大家。
如果這是一個要我們自己來填寫的表的話，一個個打上去未免也太花時間了，還不能保證一定是正確的。而在填充好函數(shù)和公式以后，在填寫信息的時候就只要把姓名和身份證號碼填上，剩下的部分就只需要稍微的核對即可。
在制表的時候，最花時間且最容易出錯的部分就是錄入基礎(chǔ)信息的時候，有些基礎(chǔ)信息是可以受一些規(guī)則約束而獲取或者計算出來的。就比如這里的行政區(qū)劃代碼，Yogurt并沒有一個個打上去，而是選擇去找權(quán)威部門提供的公開信息，對其信息進(jìn)行獲取和整合得到的。錄入的身份證號碼也可以是根據(jù)各種要求而輸出各種所需的信息。
當(dāng)然，這種方法并不是說臨時要就能直接給做出來的，Yogurt光是花在搜集行政區(qū)劃代碼上的時間就用了5、6個小時，對于一些比較著急的工作，可以根據(jù)實際情況來選擇不同的處理方式。當(dāng)然，如果這個數(shù)據(jù)庫早就已經(jīng)建立好了，那就更加不錯了，哈哈哈哈。
好啦，本期內(nèi)容就到這里啦，如果還有什么好的想法和建議，不妨在本文下方留言，咱們一起交流一下。覺得好的話，別忘了在右下角給Yogurt點個贊或者贊賞支持一下，實在不行，轉(zhuǎn)發(fā)一下也是可以的，哈哈哈，要的有點多，就先這樣吧。
臨近年關(guān)，Yogurt的工作會比較忙，更新速度也許會受到影響，不管怎么樣還是會盡可能抓緊時間更新，感謝大家的支持哈。
聯(lián)系客服
