【一點資訊】Excel告訴你，身份證號碼里藏著多少秘密

07-21

Excel告訴你，身份證號碼里藏著多少秘密

一個18位數的身份證號碼，藏著我們每個人的很多秘密。

今天，小編就帶大家揭開身份證號碼的神秘面紗，希望大家都能保護好自己的個人信息。

1、出生日期

每個人都出生日期即為身份證號碼中的8位數字，日期需要利用DATE函數。

這8組Excel函數，幫您解決工作中80%的難題文章中已經詳細介紹了字元串截取的三個函數，提取日期用到的就是從中間截取的MID函數。

輸入公式：DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))

從身份證號碼中提取出生日期（方法1）

還可以通過「分列」來提取出生日期。

從身份證號碼中提取出生日期（方法2）

2、年齡

當前年份減去出生年份即為我們每個人的年齡。

公式：YEAR(TODAY)-MID(A2,7,4)

從身份證號碼中提取年齡

3、性別

我們每個人18位身份證號碼中第17位是判斷性別的數字，奇數代表男性，偶數代表女性。

首先，我們還是用MID函數將第17位數字提取出來，然後利用MOD函數判斷奇偶（能否被2整除），再用if函數判斷男女。

公式：IF(MOD(MID(A2,17,1),2),"男","女")

從身份證號碼中提取性別

4、籍貫

18位身份證號碼中，前6位是表示地址信息的，其中1-2位代表省，3-4位代表地（市），5-6位代表縣（市）。

最好的方法就是找到原始籍貫信息匹配表（可以問度娘），可以通過vlookup函數進行匹配。

罌粟姐姐這裡提供一種直接可以匹配到省的方法，將代表身份的前兩位數字提取出來，然後使用lookup進行匹配，代表身份的數字與身份用數組公式表示。

公式：LOOKUP(VALUE(LEFT(A2,2)),{11,"北京市";12,"天津市";13,"河北省";14,"山西省";15,"內蒙古自治區";21,"遼寧省";22,"吉林省";23,"黑龍江省";31,"上海市";32,"江蘇省";33,"浙江省";34,"安徽省";35,"福建省";36,"江西省";37,"山東省";41,"河南省";42,"湖北省";43,"湖南省";44,"廣東省";45,"廣西壯族自治區";46,"海南省";50,"重慶市";51,"四川省";52,"貴州省";53,"雲南省";54,"西藏自治區";61,"陝西省";62,"甘肅省";63,"青海省";64,"寧夏回族自治區";65,"新疆維吾爾自治區";71,"台灣省";81,"香港特別行政區";82,"澳門特別行政區";"","0"})

從身份證號碼中提取籍貫

5、星座

星座與我們每個人的出生月和日相關，所以提取星座需要先提取出生的月份和具體日期。

同樣，我們可以先編製一個出生日期與星座對照表，然後用vlookup函數進行匹配。

姐姐這裡提供一種直接計算的方法，也是用lookup與數組函數的結合。

公式：LOOKUP(--MID(A2,11,4),{100;120;219;321;421;521;622;723;823;923;1023;1122;1222},{"摩羯座";"水瓶座";"雙魚座";"白羊座";"金牛座";"雙子座";"巨蟹座";"獅子座";"處女座";"天秤座";"天蠍座";"射手座";"摩羯座"})

（注意：此處將日期變成數值進行計算，例如6月23日，為623，在622和723之間，所以為巨蟹座。）

從身份證號碼中提取星座

6、生肖屬相

生肖屬相與我們每個人的出生年份相關，所以提取生肖需要先提取在哪一年出生的。

同樣，我們可以先編製一個出生年份與生肖屬相對照表，然後用vlookup函數進行匹配。

姐姐這裡同樣提供兩種直接計算的方法，一是使用CHOOSE函數。

公式：CHOOSE(MOD(MID(A2,7,4)-2008,12)+1,"鼠","牛","虎","兔","龍","蛇","馬","羊","猴","雞","狗","豬")

（注意： Choose(index_num, value1, [value2], ...)，當index_num為1時，計算結果為value1，當index_num為2時，計算結果為value2，依次類推。2008年為鼠年，每12年為一輪，與12相除的餘數加1，得到的結果即為後面對應的屬相。）

使用CHOOSE函數提取屬相

二是使用MID函數。

公式：MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",MOD(MID(A2,7,4)-2008+1,12),1)

直接使用MID函數提取屬相

大家發現沒，一個小小的身份證號碼竟然藏著這麼多秘密，所以我們一定要保護好自己的信息，不要再到處填寫身份證號碼了。

喜歡的，不要忘記點贊哦。