身份證號還能這麼玩?利用Excel把身份證號玩出花

2020-12-12 太平洋電腦網

【PConline 技巧】身份證號中蘊含著很多信息，比方說大家都知道的生日，便是其中一個最經典的案例。其實除此之外，還有一些數據也是日常經常會用到的。將這些信息快速提取出來，不但能讓工作更加高效，還能大大減少輸入出錯的機率。

把身份證號碼玩出花的Excel小技巧

1. 提取生日

函數：MID（從指定單元格中提取指定數量的字符）

身份證號中第7位~14位數字就是每個人的出生日期，而MID函數的最終原理，就是將這8位數字提取出來，再通過對應的連字符連接起來。

用法：MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)

* MID(B2,7,4)提取的是出生年份（從B2單元格中的第7位數字開始，向後提取4位）

* MID(B2,11,2)提取的是出生月份（從B2單元格的第11位數字開始，向後提取2位）

* MID(B2,13,2)提取的是出生日期（從B2單元格的第13位數字開始，向後提取2位）

* &」-」&是將提取到的數字用「-」連接起來，而非參與計算，最終可以得到「出生年-出生月-出生日」這樣一種我們需要的格式

提取生日示例

2. 提取性別

函數：MOD（判斷兩個單元格作除法運算後的餘數）

除了出生日期外，身份證號中的第17位數字是性別標識符。其中奇數為男性、偶數為。和生日提取不同，想要實現性別提取，僅靠單一函數是無法實現的，需要與IF函數搭配使用。女性

用法：IF(MOD(MID(B2,15,3),2),"男","女")

* MID(B2,15,3)提取的是身份證號中第15-17三位數字，即性別標識位

* MOD(MID(B2,15,3),2)是將提取出來的數字與2取餘，這樣可計算出MID函數提取出來的數字到底是奇數還是偶數

* IF(MOD(MID(B2,15,3),2),」男」,」女」))根據MOD函數得出的結果，奇數顯示「男」，偶數顯示「女」

提取性別示例

3. 計算年齡

函數：YEAR（提取日期數據中年份部分）

年齡計算其實應該算身份證計算的衍生公式了，同樣無法藉助單一函數實現。一般操作前，先利用MID函數將生日提取出來，然後藉助YEAR函數得到當前年份與生日年份之間的差值即可。

用法：YEAR(NOW())-YEAR(MID(B2,7,4))

* NOW()提取當前日期

* YEAR(NOW())將當前日期中年份信息提取出來,比如」1970/01/01」提取出來後就是」1970」

* YEAR(MID(B2,7,4)):首先藉助MID函數提取身份證號中的出生年份,然後再用YEAR函數提取當前年份,最後將兩個年份相減,結果即為該員工當前年齡

計算年齡

註：最後再來個小貼士吧，有些網友可能會遇到這種情況，輸入18位身份證號時出現「x.xxxxxE+17」這樣的提示。這主要是因為單元格格式設置有問題，解決的方法是提前將單元格格式改為「文本」，然後再次輸入數值即可。

這種情況曾經讓很多新入手Excel的網友頭疼

其實解決的方法，只要一步就可以！簡單吧

相關閱讀:怎麼求差？Excel求差函公式使用方法Excel表格Excel教程：Excel文件如何快速轉換為文檔？Word如何將excel文本轉換為數字？這些技巧請收下超簡單一招！用Excel十秒快速製作工資條http://pcedu.pconline.com.cn/875/8755800.htmlhttp://pcedu.pconline.com.cn/875/8755520.htmlhttp://pcedu.pconline.com.cn/873/8736900.htmlhttp://pcedu.pconline.com.cn/817/8170320.html

相關焦點

  • 身份證號、手機號批量加星號脫敏,excel幾分鐘搞定
    請注意，faker生成的姓名、身份證號、手機號完全是隨機生成的，僅用於測試使用，如有雷同，純屬巧合。為了防止雷同，我把身份證號和手機號的最後四位全都換成了1111。最終的數據就像下圖這樣。需求上傳到公網的信息，因為涉及個人隱私，所以不能帶身份證號和手機號，處理的方法就是將其中的幾位數字替換成*號。難點身份證號可以使用replace，mid，right，left等公式進行脫敏處理，非常簡單。
  • 你知道你的身份證號藏了多少小秘密嗎？
    身份證號碼前6位表示的是籍貫地區，比如身份證前6位為「450521」，前面兩位「45」表示的是省，自治區或直轄市，中間兩位「05」表示的是所在地市，最後兩位「21」表示的是所在縣區，所以通過excel技巧，我們可以從身份證號提取出籍貫信息，如圖所示。公式：VLOOKUP(LEFT(B3,6),全國各省份戶籍行政區劃代碼表！
  • excel輸入身份證號變成0 數字亂碼解決方法詳解
    excel輸入身份證號變成0 數字亂碼解決方法詳解時間：2017-03-22 08:29   來源：系統天堂   責任編輯：玲玲 川北在線核心提示：原標題：excel輸入身份證號變成0 數字亂碼解決方法詳解 excel輸入身份證號變成0怎麼辦呢?
  • 用條件格式對雙胞胎身份證號查重,excel錯的很明顯,難道是bug嗎
    最近又有一個小夥伴問到關於身份證號碼查重的問題，他發現一個比較奇怪的問題，兩個雙胞胎只有身份證號碼的最後一位不一樣，當使用條件格式對重複值進行突出顯示顏色的時候，這兩個身份證號被判定為重複(如下圖，魯班與劉備的身份證號碼，只有最後一位不一樣)，excel錯的這麼明顯難道是bug嗎？
  • 網絡「身份證生成器」一次可取500身份證號
    輕輕按動滑鼠，輸入若干信息，一次就可輕鬆地套取500個身份證號碼。最近，一種名為「身份證生成器」的軟體在網上迅速躥紅。這讓市民憂慮不已：「身份信息外洩後被用於不法交易怎麼辦？」　　身份證號唾手可得 　　記者日前得知，眼下越來越多的網路遊戲和網上論壇開始實行「實名制」註冊，但「身份證生成器」的廣泛流傳讓「實名制」形同虛設。記者用Google搜索了關鍵詞「身份證生成器」，查詢結果竟然有124000個。 　　記者隨後在某網站上成功下載了一款名為「身份證號碼生成器」的軟體。
  • Excel教程:一個身份證號就能扒出您7條隱私(下篇)
    在上篇文章中我們說到了用excel提取身份證號碼中的戶籍地址、出生日期、年齡、生肖、星座等內容，今天我們書接上回，繼續帶大家學習excel提取身份證信息的其他操作！ 引言： 我們繼續上篇生日提取的拓展思路，開始我們今天的內容，保證開篇就有驚喜！
  • Excel文檔表格身份證號格式怎麼寫
    小編教你怎麼操作，Excel表格不僅方便統計數據，還能簡歷各種檔案，幫助人事、行政、運營等辦公人員大大提高工作效率，但是在統計人身份證號碼時如何在excel表格中將身份證的18位數字顯示出來。第1步：首先右鍵一個excel單元格格，選擇【設置單元格格式】。
  • excel輸入身份證號變成0怎辦 excel輸入數字亂碼問題解決方法
    excel輸入身份證號變成0怎麼辦呢?在使用excel時，很多童鞋都會碰到excel輸入數字亂碼的現象，特別是excel輸入身份證號變成0現象最為常見;下面小編就來教你怎麼辦。　　excel輸入身份證號變成0怎麼辦：　　究其原因，就是Excel目前最多支持15位數字，超過的數字全部默認為「0」，解決方法很簡單，將格式由「數值」改為「文本」。因為身份證號作為一串固定的數字並沒有運算需求，應該歸屬於規規矩矩的文本。
  • Excel告訴你身份證中的秘密
    今天的主題：用excel函數和公式，從身份證號中提取出「出生日期」「性別」「籍貫」，並計算「當前年齡」。>1）利用mid函數，從18位身份證號碼中，取出從第7位開始的8個數字2）利用text函數，將取出的8個數字，生成日期的數值形式3）最後，通連續的減號「--」，將數值轉換成日期的文本形式4） 依次往下複製、填充補充：excel
  • excel怎麼輸入身份證號
    Excel怎么正確的輸入身份證號在輸入身份證號時長會出現這樣的問題，因為數值大於15位，電腦自動轉化為科學計數法很不方便，因為我們要的就是18位的數字，那有沒有有沒有辦法可以保證可以正確的輸入身份證號號碼呢？
  • 輸入身份證號自動顯示戶籍所在地,Excel身份證歸屬地識別
    相信不少人力資源管理者都會頻繁的錄入人員身份信息，包括身份證號碼、歸屬地、出生日期和年齡等，在之前的文章裡我們講解了如何利用Excel自動提取身份證內的出生日期和計算年齡。今天給大家介紹Excel的又一實用功能，在Excel內實現輸入身份證號自動識別戶籍所在地功能。
  • Excel知道身份證號怎麼計算年齡(周歲)?
    今天我們來解決知道身份證號怎麼計算年齡（周歲）的問題。一、18位身份證號我們先來介紹最常見的情況，身份證號全部是18位，如下圖。步驟如下：1.18位身份證號的編碼規則18位身份證號從第7位開始的連續8位代表本人的出生日期，比如：「513436198205079000」。
  • Excel輸入身份證號自動生成性別,詳解
    朋友們好，今天和大家分享Excel利用身份證號自動生成性別的函數，人員信息表是辦公人員經常接觸的表格，那麼自動生成性別，我們就節省了一部分時間，看下圖：用到的函數及效果大家都知道，身份證號裡面藏有玄機，其中倒數第二位可以判斷性別，奇數為男，偶數為女，朋友們可以看看自己的身份證號，我們只需要把倒數第二位提取出來，判斷是奇數還是偶數就可以了
  • 看看身份證號能提取出多少信息吧
    身份證號共18位，利用函數可以從中判斷出很多信息。本文主要講解利用函數判斷性別，提取生日，判斷周歲，虛歲，以及如何對身份證號中的出生日期部分隱藏為"********"1、判斷性別身份證號的第17位數字，奇數為男性，偶數為女性，所以我們利用這一特點進行性別判斷，公式如圖1所示，下面我們進行公式分解講解；
  • EXCEL通過身份證號提取性別信息
    通過身份證號，不僅能提取出生日期，更能提取性別信息，在面對大量的員工身份證號需要提取性別信息時，用Excel通過簡單幾步操作就能輕鬆提取性別信息。首先打開一個包含身份證信息的表格，新建一個「性別」列。注意：B2是身份證所在的列，請根據實際情況靈活更改。
  • 如何修改QQ號綁定的防沉迷身份證?
    玩QQ遊戲難免要綁定身份證來通過防沉迷驗證，很多人在給QQ綁定身份證的時候，可能自己身份證未成年，所以使用的別人的身份證，或者是通過其他渠道購買的一些QQ號，綁定了別人的身份證。那麼這個身份證如何修改呢？
  • 用Excel快速提取身份證號的信息
    01身份證號中都隱藏了哪些信息？我們現在的身份證號有18位構成，每1位都有其特殊的含義：前6位表示地區碼：第1、2位表示：戶籍所在省份的代碼；第3、4位表示：戶籍所在地區(城市)的代碼；第15、16位表派出所的代碼第17位表示性別：奇數表示男性，偶數表示女性第18位是校驗碼：用來驗證前17位的輸入是否正確，實現原理及如何用Excel驗證身份證號的正確性
  • Excel如何從身份證號中提取出生日期
    如果你只有員工身份證號碼，要從員工身份證號中提取身份證號該怎麼辦呢？這時候你可以通過Excel用公式提取出出生日期。打開含有身份證信息的Excel表格。在身份證號後面新建一個「出生日期」列，在「出生日期」列的第一個單元格中輸入公式=TEXT(MID(B2,7,8),"00-00-00")公式中（B2,7,8）中的B2是公式前面身份證號所在的單元格的坐標
  • Excel小知識 身份證號計算年齡
    算出一個年齡後雙擊右下角，向下填充下面小編教你怎麼在excel中使用year函數，希望對你有幫助!在excel中year函數的使用方法year函數只是返回的參數的年份，他的參數可以是一個函數，也就是說year函數可以嵌套函數，這裡我們嵌套日期函數today函數excel 日期函數的使用教程日期函數使用步驟1:例如顯示今天的日期。
  • 辦公軟體操作技巧48:如何在excel中提取身份證號中的出生日期
    在日常工作中，我們經常需要在excel表格內輸入職員個人基本信息，有時會需要提取身份證號中的出生日期，今天就為大家分享幾種從身份證號中提取出生日期的方法。我們首先來了解一下18位身份證號碼的結構和含義：1、前6位是行政區劃代碼，一般為出生後報戶口時戶籍地的代碼；2、第7至第14位是出生日期碼，是按年月日排列的；3、第15至第17位是順序碼，是由公安機關編訂的號碼，其中第17位是性別信息，奇數為男性