摘要： 當然，先拍個全家福除B列的身份證號碼由手動錄入外，C-K列的所有信息都可以通過身份證號碼計算出來，你相信嗎？在知道Excel函數之前，我也是不信的，肯定是有什么機關……但是，不得不...

當然，先拍個全家福

除B列的身份證號碼由手動錄入外，C-K列的所有信息都可以通過身份證號碼計算出來，你相信嗎？

在知道Excel函數之前，我也是不信的，肯定是有什么機關……

但是，不得不信啊。

我們先以其中一個身份證為例，來講講身份證號碼中的秘密：

身份證號碼由18位組成，其中：

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

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

第5、6位數字表示：所在區（縣、自治縣、縣級市）的代碼；

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

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

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

第18位數字是校檢碼：也有的說是個人信息碼，不是隨計算機的隨機產生，它是 用來檢驗身份證的正確性。校檢碼可以是0—9的數字，有時也用X表示。

在錄入省、市、區縣的的公式之前，我們需要在國家統計局獲取到行政區劃代碼表，并處理為如下樣式：

接下來就可以開始大干一場了~

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

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

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

F-K列的信息都能根據身份證號碼中的規律來獲取

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

使用MID從身份證號碼中的第7位數值開始截取8位連續數值，并利用TEXT轉化為日期樣式，利用減負運算，將文本型日期轉化為數值日期

最后需要通過自定義格式設置為日期格式，確保生日是規范日期，可計算

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

利用DATEDIF從生日計算年齡

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

提取身份證號碼中的第17位數值，并利用MOD求余判斷數值奇偶，來判定性別

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

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

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

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

先排列出每個星座的開始日期，利用text格式化月日為mdd樣式，使用LOOKUP獲取對應的星座（注意摩羯座因為跨年的原因，在首尾都有出現）

幸運數字：=MOD(TEXT(F2,"emmdd"),9)

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

快學好Excel函數，應用到工作中吧……

關注本君頭條吧

從看不懂，到學會……

相差的只是這樣一個機會。