Hello,大家好,今天跟大家分享下身份證號碼的構成以及我們利用Excel可以從身份證號碼中提取哪些信息,首先我們先來了解下身份證號碼的構成,下圖所示,身份證號碼是由三大部分構成的
1-6位是地址碼,其中1-2位代表省份的代碼,3-4位代表城市的代碼,5-6位代表區縣的代碼
7-14位是出生日期的代碼,它是一個8位數字的代碼
15-18位是順序代碼,其中第17位是性別代碼,奇數性別為男,偶數性別為女,第18位是校驗碼,它是根據前面的數字根據算法計算出來的,可以用於鑑別身份證號碼的真偽,以上就是身份證號碼的組成,下面就讓我們來看下都可以利用Excel獲取哪些數據
一、獲取籍貫
身份證號碼的前六位是地址碼,我們可以通過地址碼來獲取籍貫。在這裡我們還需要一個身份證號碼前六位與籍貫的對照表,我們將其放在sheet2中,這樣的話我們只需將公式設置為:=VLOOKUP(LEFT(A2,6),Sheet2!A:B,2,FALSE),然後向下填充即可獲取到身份證號碼對應的籍貫,跟大家簡單的介紹下公式
這個函數主體上是一個vlookup函數的常規用法,
第一參數:LEFT(A2,6),left函數的作用是從左開始提取數據,在這裡我們從左開始提取了6位數據,也就是將地址碼提取了出來
第二參數:Sheet2!A:B,這個是放在sheet2中的地址碼與籍貫的對照表,如果你需要這個表格,獲取方法我會放在評論區
第三參數:2,查詢的結果在數據區域的第二列
第四參數:精確匹配
二、提取出生日期
提取出生日期,我們只需要將身份證號碼的第7到14位提取出來,然後再利用text函數將其設置為日期格式即可,提取身份證號碼的第7到14位可以利用mid函數來實現,這樣只需要將公式設置為:=--TEXT(MID(A2,7,8),"0-00-00")即可
Text函數的前面有2個減號,這個的作用是將文本格式的數據轉換為常規格式,因為text是一個文本函數,它所輸出結果也是文本格式的數據,這樣的數據一些版本中的Excel是無法使用日期函數對它進行計算的,所以我們需要將其轉換為常規格式
三、計算年齡
上一步中我們利用Excel提取到了出生日期,計算年齡就會變得非常的簡單,只需要利用DATEDIF函數即可計算出年齡,將公式設置為:=DATEDIF(B2,TODAY(),"y")即可
DATEDIF函數的作用是計算兩個日期之間的差值
第一參數:開始的時間,在這裡我們設置為b2單元格,就是提取到的出生日期
第二參數:today(),這個函數的的作用是獲取今天的日期
第三參數:y,計算的類型,y代表年份
四、計算性別
性別只與身份證號碼的第17位有關,17位是偶數性別為女,為奇數性別為男,關鍵是如何判斷第17位的奇偶性,判斷奇偶性我們可以利用ISEVEN,這個函數的作用是如果數字為偶數則返回true,不是偶數則返回false,這樣的話只需要將公式設置為:=IF(ISEVEN(MID(A2,17,1)),"女","男")即可計算性別
在這裡我們利用MID(A2,17,1)這個函數將身份證號碼的第17位提取出來,然後再利用ISEVEN函數遇到偶數返回ture,遇到奇數返回false的特性,將其嵌套在if函數的第一參數中,這樣的話就會得到正確的結果
我是Excel從零到一,關注我,持續分享更多Excel技巧
覺得有用,麻煩點個在看~
轉載請超鏈接註明:頭條資訊 » 保護好自己的身份證號碼,僅用Excel就能提取到4類信息息
免責聲明 :非本網註明原創的信息,皆為程序自動獲取互聯網,目的在於傳遞更多信息,並不代表本網贊同其觀點和對其真實性負責;如此頁面有侵犯到您的權益,請給站長發送郵件,並提供相關證明(版權證明、身份證正反面、侵權鏈接),站長將在收到郵件24小時內刪除。