頭條資訊 - 為您提供最新最全的新聞資訊,每日實時更新

保護好自己的身份證號碼,僅用Excel就能提取到4類信息息

科技數碼 Excel從零到一

Hello,大家好,今天跟大家分享下身份證號碼的構成以及我們利用Excel可以從身份證號碼中提取哪些信息,首先我們先來了解下身份證號碼的構成,下圖所示,身份證號碼是由三大部分構成的

保護好自己的身份證號碼,僅用Excel就能提取到4類信息息

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),然後向下填充即可獲取到身份證號碼對應的籍貫,跟大家簡單的介紹下公式

保護好自己的身份證號碼,僅用Excel就能提取到4類信息息

這個函數主體上是一個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")即可

保護好自己的身份證號碼,僅用Excel就能提取到4類信息息

Text函數的前面有2個減號,這個的作用是將文本格式的數據轉換為常規格式,因為text是一個文本函數,它所輸出結果也是文本格式的數據,這樣的數據一些版本中的Excel是無法使用日期函數對它進行計算的,所以我們需要將其轉換為常規格式

三、計算年齡

上一步中我們利用Excel提取到了出生日期,計算年齡就會變得非常的簡單,只需要利用DATEDIF函數即可計算出年齡,將公式設置為:=DATEDIF(B2,TODAY(),"y")即可

保護好自己的身份證號碼,僅用Excel就能提取到4類信息息

DATEDIF函數的作用是計算兩個日期之間的差值

第一參數:開始的時間,在這裡我們設置為b2單元格,就是提取到的出生日期

第二參數:today(),這個函數的的作用是獲取今天的日期

第三參數:y,計算的類型,y代表年份

四、計算性別

性別只與身份證號碼的第17位有關,17位是偶數性別為女,為奇數性別為男,關鍵是如何判斷第17位的奇偶性,判斷奇偶性我們可以利用ISEVEN,這個函數的作用是如果數字為偶數則返回true,不是偶數則返回false,這樣的話只需要將公式設置為:=IF(ISEVEN(MID(A2,17,1)),"女","男")即可計算性別

保護好自己的身份證號碼,僅用Excel就能提取到4類信息息

在這裡我們利用MID(A2,17,1)這個函數將身份證號碼的第17位提取出來,然後再利用ISEVEN函數遇到偶數返回ture,遇到奇數返回false的特性,將其嵌套在if函數的第一參數中,這樣的話就會得到正確的結果

我是Excel從零到一,關注我,持續分享更多Excel技巧

覺得有用,麻煩點個在看~

轉載請超鏈接註明:頭條資訊 » 保護好自己的身份證號碼,僅用Excel就能提取到4類信息息
免責聲明
    :非本網註明原創的信息,皆為程序自動獲取互聯網,目的在於傳遞更多信息,並不代表本網贊同其觀點和對其真實性負責;如此頁面有侵犯到您的權益,請給站長發送郵件,並提供相關證明(版權證明、身份證正反面、侵權鏈接),站長將在收到郵件24小時內刪除。
加載中...