封面

在工作中，我們經常要填寫個人信息表，有些信息反複填寫，非常反感。實際上我們是可以 使用EXCEL技巧從身份證号碼中提取籍貫、出生年月、年齡、星座、生肖、性别等信息的。

想要順利提取以上各種信息，首先，我們必須要知道身份證号碼上各個數字代表哪些意義。

第1-2位是省、自治區、直轄市的代碼；

第3-4位是地級市、盟、自治州代碼；

第5-6位是縣、縣級市、區代碼；

第7-14位代表出生年月日；

第15-17位為順序号，其中17位數字男為單數﹐女為雙數；

第18位為校驗碼，0-9和X。

【1】EXCEL根據身份證号碼快速提取籍貫信息，我用了3小時，同事隻30秒

籍貫封面

【 第1步】提取身份證前兩位字符

使用LEFT函數，提取身份證号碼前兩位字符，公式為：LEFT(D3,2)。

LEFT函數

LEFT函數：文本字符串的第一個字符開始返回指定個數的字符。

【 第2步 】将提取的兩位數字強制轉換為數字

使用VALUE函數，将提取的兩位字符強制轉換為數字，公式為：VALUE(LEFT(D3,2))。

VALUE函數

VALUE函數：将表示數字的文本字符串轉換為數字。

【 第3步 】根據提取的數字進行匹配查找

使用LOOKUP函數，将轉換的數字與數組中數字與對應的省份信息進行匹配。

LOOKUP函數

【2】EXCEL根據身份證号碼提取出生年月的4種方法

出生年月封面

【 方法1：快捷鍵 】

快捷鍵：Ctrl E

操作步驟：

首先，輸入出生年月的示例1995/10/10。

然後，在其下方單元格按CTRL E快速填充快捷鍵。

Ctrl E

【 方法2：數據分列 】

使用函數：MID

操作步驟：

首先，使用MID函數提取第7至14位數字,公式為=MID(D3,7,8)。

其次，将光标移到單元格右下角，變成黑色加号後雙擊。

再次，去掉公式：按Ctrl C 複制單元格區域，右擊，在粘貼選項中選擇值。

然後，使用菜單欄【數據】-【分列】-【日期】即可。

數據分列

【 方法3：日期函數 】

使用函數：MID、DATE

操作步驟：

首先，使用MID函數分别提取年、月、日。

然後，使用DATE函數将三個單獨的值合并為一個日期。

最後公式如下：=DATE(MID(D3,7,4),MID(D3,11,2),MID(D3,13,2))。

日期函數

【 方法4：文本函數 】

使用函數：MID、TEXT

操作步驟：

首先，使用MID函數提取第7至14位數字，公式為=MID(D3,7,8)。

其次，使用TEXT函數将數字轉換為文本，公式為=TEXT(MID(D3,7,8),"0-00-00")。

再次，去掉公式：Ctrl C 複制單元格區域，右擊，在粘貼選項中選擇值。

最後，将“-”替換為“/”： 按CTRL H，彈出替換窗口，點擊“替換，”将“-”替換為“/”,把“假日期”格式，轉換為“真日期”格式。

文本函數

技巧法适合零基礎的小白菜，公式法适合有函數功底的老油條，4種方法，各有千秋，您更喜歡哪種呢？

【3】EXCEL根據身份證号提取年齡的3種方法

【 錯誤演示 】

【 方法1】

看看我家小哥哥的不嚴謹演示：

首先，輸入MID函數，輸入截取的單元格D3。

其次，輸入從第7位開始截取的數字。

再次，輸入截取長度字符數字4，公式為=MID(D3,7,4)。

最後，再用2022減去MID函數的結果。

總結：小哥哥的操作方法挺簡單的，可是這樣操作的缺點是年齡不會随着時間變化自動更新。

那怎樣才是正确的方法呢？睜大眼睛看好。

【 正确演示 】

【 方法2 】

使用函數：NOW、YEAR、MID

操作步驟：

首先，用NOW函數返回當前日期或序列号。

其次，使用YEAR函數返回對應于某個日期的年份。

然後，再使用MID函數截取年份。

完整公式如下：=YEAR(NOW())-MID(D4,7,4)。

【 方法3 】

使用函數：TODAY、YEAR、MID

操作步驟：

首先，用TODAY函數返回當前日期的序列号。

其次，使用YEAR函數返回對應于某個日期的年份。

然後，再使用MID函數截取年份。

完整公式如下：=YEAR(TODAY())-MID(D4,7,4)

【4】EXCEL根據身份證号碼提取性别信息的3種方法

性别封面

身份證号碼中第15-17位為順序号，其中第17位是判斷性别的數字，奇數代表男性，偶數代表女性。所以需要提取身份證的第17位數字，然後再判斷是否為奇偶數。

【 方法1 】

使用函數：MID、MOD、IF

操作步驟：

首先，使用MID函數提取第17位數字，輸入公式MID(D4,17,1)。

其次，使用MOD函數求除以2以後的餘數，輸入公式MOD(MID(D4,17,1),2)。

再次，使用IF函數判斷男女，輸入公式IF(MOD(MID(D4,17,1),2),"男","女")。

最後公式如下：IF(MOD(MID(D4,17,1),2),"男","女")。

将光标移到單元格右下角，變成黑色 号後，雙擊快速填充。

方法1

【 方法2 】

使用函數：MID、TEXT

操作步驟：

首先，使用MID函數提取第17位數字，輸入公式MID(D4,17,1)。

然後，使用TEXT函數将數字轉換為文本 TEXT(-1^MID(D4,17,1),"女;男")。

最後公式如下：TEXT(-1^MID(D4,17,1),"女;男")。

方法2

【 方法3 】

使用函數：MID、MOD、VLOOKUP

操作步驟：

首先，使用MID函數提取第17位數字 MID(D4,17,1)。

其次，使用MOD函數求除以2以後的餘數 MOD(MID(D4,17,1),2)。

再次，在空白單元格輸入輔助數據，0女，1男。

最後，使用VLOOKUP函數進行查找匹配。

最後公式如下：VLOOKUP(MOD(MID(D4,17,1),2),$H$3:$I$4,2,0)。

方法3

【5】EXCEL技巧——身份證号碼如何提取出星座

星座封面

世界上沒有完全相同的兩片葉子，也找不出沒有任何差異的兩個人，十二星座中的每個星座都有其獨特的性格特點，如果可以提前了解，那麼在相處是也就會更加融洽，減少分歧，多一分理解。星座與出生月和日相關，我們如果不借助其他表來查詢，也可以用lookup與數組函數的結合。

【 操作方法 】

使用函數：LOOKUP、MID

首先，輸入MID函數，輸入截取的單元格D3，輸入從第11位開始截取的數字，輸入截取長度字符數字4，公式為=MID(D3,11,4)。

然後，使用LOOKUP函數與數組結合進行查找匹配。

最後将光标移到單元格右下角，變成黑色 号後雙擊進行快速填充即可。

操作方法

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

如果上述函數在使用中有問題，可以直接把公式複制到單元格中，再把公式中的D3單元格修改成身份證所在的單元格即可。

【6】看生肖故事學EXCEL，根據身份證号提取生肖信息

封面

大家知道十二生肖的來源麼？知道十二生肖的順序麼？

前面的文章已經介紹了從身份證号碼中提取籍貫、出生年月、年齡、星座、性别信息的操作方法。接下來分享從身份證号碼提取的生肖信息，希望對大家有所幫忙。

首先，我們來了解一下十二生肖的傳奇故事吧。

【 生肖故事 】

軒轅黃帝要選十二動物擔任宮廷衛士，貓托老鼠報名，結果老鼠忘了，從此貓見老鼠就尋仇。

生肖故事

原本推牛為首，老鼠偷偷爬上牛背占先機。虎和龍不服氣，被封為山神和海神，排在牛的後面。兔子不服，要和龍賽跑，兔子跑到龍前面。狗不樂意，一氣之下咬傷兔子，被罰倒數第一。蛇、馬、羊、猴、雞之間還經過—番較量，最後豬跑來占據末席。

生肖故事

接下來介紹兩種方法提取生肖信息

【 方法1 】

使用函數：MID、MOD

首先，生肖屬相與出生年份相關，所以提取生肖需要先提取出生年份。

輸入MID函數，單擊截取的單元格D3，輸入數字7，代表從第7位開始截取，輸入截取長度字符數字4。公式為=MID(D3,7,4)。

其次，利用MOD函數得出屬相的原理：MOD函數（被除數，除數），得到的值是餘數。根據十二生肖：【鼠牛虎兔龍蛇馬羊猴雞狗豬】的順序。餘數為1，就屬鼠，餘2屬牛，餘3屬虎，餘4屬兔，以此類推。

我們用出生年份減去4，得到數與12相除，再取餘數，再對餘數 1。

例如：（1989-4）/12=5,5 1=6，1989年屬于蛇年，十二生肖中它在第6位。＋1主要意思就是為了防止餘數為0時不能輸出屬相。

公式為= =MOD(H3-4,12) 1

最後，輸入公式=MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",單元格,1)。

解析：在生肖裡面，根據年份的餘數#，選擇餘數#位的漢字，即可得到生肖。

如果需要在生肖後面加上年字，則在公式後面加上【&“年”】，生肖後面顯示就會“年”字。如：蛇年。

=MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",單元格,1) &“年”。

完整公式如下：=MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",MOD(MID(D3,7,4)-4,12) 1,1)。

完整公式

如果上述函數在使用中有問題，可以直接把公式複制到單元格中，再把公式中的D3單元格修改成身份證所在的單元格即可。

【 方法2 】

使用函數：MID、MOD、CHOOSE

首先，生肖屬相與出生年份相關，所以提取生肖需要先提取出生年份。

輸入MID函數，輸入截取的單元格D3，輸入數字7，代表從第7位開始截取，輸入截取長度字符數字4。公式為=MID(D3,7,4)

其次，利用MOD函數得出屬相的原理：MOD函數（被除數，除數），得到的值是餘數。根據十二生肖：【鼠牛虎兔龍蛇馬羊猴雞狗豬】的順序。餘數為1，就屬鼠，餘2屬牛，餘3屬虎，餘4屬兔，以此類推。

我們用出生年份減去4，得到數與12相除，再取餘數，再對餘數 1。

例如：（1989-4）/12=5,5 1=6，1989年屬于蛇年，十二生肖中它在第6位。＋1主要意思就是為了防止餘數為0時不能輸出屬相。

公式為= =MOD(H3-4,12) 1

完整公式2如下：=CHOOSE(MOD(MID(D3,7,4)-4,12) 1,"鼠","牛","虎","兔","龍","蛇","馬","羊","猴","雞","狗","豬")

完整公式

如果上述函數在使用中有問題，可以直接把公式複制到單元格中，再把公式中的D3單元格修改成身份證所在的單元格即可。

如果您有其它更好的方法，歡迎留言，一起交流，共同進步。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!