摘要： 當(dāng)然，先拍個(gè)全家福除B列的身份證號(hào)碼由手動(dòng)錄入外，C-K列的所有信息都可以通過(guò)身份證號(hào)碼計(jì)算出來(lái)，你相信嗎？在知道Excel函數(shù)之前，我也是不信的，肯定是有什么機(jī)關(guān)……但是，不得不...

當(dāng)然，先拍個(gè)全家福

除B列的身份證號(hào)碼由手動(dòng)錄入外，C-K列的所有信息都可以通過(guò)身份證號(hào)碼計(jì)算出來(lái)，你相信嗎？

在知道Excel函數(shù)之前，我也是不信的，肯定是有什么機(jī)關(guān)……

但是，不得不信啊。

我們先以其中一個(gè)身份證為例，來(lái)講講身份證號(hào)碼中的秘密：

身份證號(hào)碼由18位組成，其中：

前1、2位數(shù)字表示：所在省（直轄市、自治區(qū)）的代碼；

第3、4位數(shù)字表示：所在地級(jí)市（自治州）的代碼；

第5、6位數(shù)字表示：所在區(qū)（縣、自治縣、縣級(jí)市）的代碼；

第7—14位數(shù)字表示：出生年、月、日；

第15、16位數(shù)字表示：所在地的派出所的代碼；

第17位數(shù)字表示性別：奇數(shù)表示男性，偶數(shù)表示女性；

第18位數(shù)字是校檢碼：也有的說(shuō)是個(gè)人信息碼，不是隨計(jì)算機(jī)的隨機(jī)產(chǎn)生，它是 用來(lái)檢驗(yàn)身份證的正確性。校檢碼可以是0—9的數(shù)字，有時(shí)也用X表示。

在錄入省、市、區(qū)縣的的公式之前，我們需要在國(guó)家統(tǒng)計(jì)局獲取到行政區(qū)劃代碼表，并處理為如下樣式：

接下來(lái)就可以開(kāi)始大干一場(chǎng)了~

省份：=VLOOKUP(LEFT(B2,2),行政區(qū)劃數(shù)據(jù)!H:I,2,FALSE)

城市：=VLOOKUP(LEFT(B2,4),行政區(qū)劃數(shù)據(jù)!K:L,2,FALSE)

區(qū)縣：=VLOOKUP(LEFT(B2,6),行政區(qū)劃數(shù)據(jù)!B:E,4,FALSE)

F-K列的信息都能根據(jù)身份證號(hào)碼中的規(guī)律來(lái)獲取

生日：=--TEXT(MID(B2,7,8),"0-00-00")

使用MID從身份證號(hào)碼中的第7位數(shù)值開(kāi)始截取8位連續(xù)數(shù)值，并利用TEXT轉(zhuǎn)化為日期樣式，利用減負(fù)運(yùn)算，將文本型日期轉(zhuǎn)化為數(shù)值日期

最后需要通過(guò)自定義格式設(shè)置為日期格式，確保生日是規(guī)范日期，可計(jì)算

年齡：=DATEDIF(F2,TODAY(),"y")

利用DATEDIF從生日計(jì)算年齡

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

提取身份證號(hào)碼中的第17位數(shù)值，并利用MOD求余判斷數(shù)值奇偶，來(lái)判定性別

以及一些非常用的信息提取：

生肖：=MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",MOD(TEXT(F2,"[$-130000]e")-4,12)+1,1)

利用MOD對(duì)修正順序后的年份求余，獲取到生肖位置，使用MID提取對(duì)應(yīng)位置的字符

星座：=LOOKUP(--TEXT(F2,"mdd"),{0,"摩羯座";120,"水瓶座";219,"雙魚(yú)座";321,"白羊座";420,"金牛座";521,"雙子座";622,"巨蟹座";723,"獅子座";823,"處女座";923,"天秤座";1024,"天蝎座";1123,"射手座";1222,"摩羯座"})

先排列出每個(gè)星座的開(kāi)始日期，利用text格式化月日為mdd樣式，使用LOOKUP獲取對(duì)應(yīng)的星座（注意摩羯座因?yàn)榭缒甑脑颍谑孜捕加谐霈F(xiàn)）

幸運(yùn)數(shù)字：=MOD(TEXT(F2,"emmdd"),9)

幸運(yùn)數(shù)字通常將年月日各位數(shù)值逐位累加，至個(gè)位為止。利用9余數(shù)特性，使用MOD取巧算出幸運(yùn)數(shù)字

快學(xué)好Excel函數(shù)，應(yīng)用到工作中吧……

關(guān)注本君頭條吧

從看不懂，到學(xué)會(huì)……

相差的只是這樣一個(gè)機(jī)會(huì)。