Excel乾貨:身份證號提取生日、年齡、性別、籍貫,建議收藏!

2020-12-01 EXCEL精選技巧

咱們每個人都有一個唯一的身份證號，其實身份證號中包含了很多的個人信息，你知道怎麼從身份證號中提取個人的生日、年齡、性別、籍貫等信息嗎？技巧君今天和大家分享一下如何利用Excel從身份證號中提取個人信息！

身份證號的構成

身份證號有18位，排列順序從左至右依次為：六位數字地址碼，八位數字出生日期碼，三位數字順序碼和一位數字校驗碼。順序碼的奇數分給男性，偶數分給女性。

身份證號構成

從這個構成中，我們可以發現，只需要提取對應的編碼就可以提取出身份證號中的個人信息！

提取性別

=IF(MOD(MID(B2,17,1),2),"男","女")

身份證號提取性別

說明：

MID(B2,17,1)，提取第17位數字

MOD(MID(B2,17,1),2)，對2取餘，奇數餘數得1，偶數餘數為0

提取出生年月日

=--TEXT(MID(B2,7,8),"0-00-00")

說明：MID(B2,7,8)，提取第7位開始後面的8位數字，即出生年月日編碼

TEXT(MID(B2,7,8),"0-00-00")，轉換為年-月-日的格式

--TEXT(MID(B2,7,8),"0-00-00")，減負運算換算為真正的日期

提取實歲

=DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"Y")

提取實歲

說明：DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"Y")，計算當前日期和出生日期的年差值，即實際歲數

提取虛歲

=(YEAR(NOW())-MID(B2,7,4))

提取虛歲

說明：MID(B2,7,4)：提取第7位數開始的4位數字，即出生年份

YEAR(NOW())：計算當前日期的年份(YEAR(NOW())-MID(B2,7,4))：虛歲=當前年份-出生年份

提取籍貫

=VLOOKUP(--MID(B2,1,6),籍貫編碼！B:C,2,0)

提取籍貫

說明：--MID(B2,1,6)，提取前六位地址編碼

VLOOKUP(--MID(B2,1,6),籍貫編碼！B:C,2,0)，在地址編碼表裡找到籍貫

示例文件獲取：關注Excel精選技巧，發送私信【身份證號提取個人信息】即可獲取

關注Excel精選技巧，每天學習3分鐘，堅持一個月，你將大不同！

相關焦點

  • WPS Excel: 從身份證中提取生日、年齡、性別、籍貫、生肖、星座
    從一串身份證號碼中，可以1秒提取出這個號碼對應的生日、年齡、性別、籍貫、生肖、星座。作為一名HR，需要登記員工的許多信息，如果學會了怎麼從身份證號碼中提取這些信息，可以節省許多時間。提取年齡公式：「=DATEDIF(B2,TODAY(),"Y")」B2單元格是我們提取到的生日，DATEDIF函數中前兩個表示日期，最後一個參數表示計算方式，「Y」表示計算這兩個日期之間相差多少年。
  • 身份證號提取生日、年齡、性別、籍貫,最簡單的方法!
    上一期，Amy跟大家分享了如何從身份證號中提取出生日期。今天我們繼續來講如何提取年齡、性別、籍貫。身份證號提取年齡✦思路：在E2單元格輸入公式「=DATEDIF(D2,TODAY(),"Y")」，下拉填充，即可得到年齡。
  • Excel技巧:只需身份證號,批量秒取個人生日、年齡、性別信息
    今天和大家分享的技巧是怎麼從excel裡提取個人信息，如果只有一串身份證號該怎麼提取呢？前提在這裡順便說一下Excel怎麼輸入身份證，如果不設置的話輸入數字太長會變成小數點，這裡需要設置一下單元格-數字分類-文本，就能打出一串長長的身份證數字了，因為我記得我當年剛入門的時候就是被這小小的問題難住還上網查了，記憶深刻，太笨了。好了下面我們來看看怎麼提取。1.身份證號提取生日的方法我們只要選中區域，在框內輸入： =TEXT(MID(B3,7,8),"0!
  • Excel技巧篇:技巧3,從身份證號碼提取員工性別,生日,年齡
    大家好我是雨果，這節課我們繼續學習EXCEL技巧篇3：從身份證號碼提取員工性別，生日，年齡我們以下面這張表格為例：1.快速提取及判斷員工性別很多人都知道，身份證號碼倒數第二位其實就是表示性別的意思，奇數為男性，偶數為女性，這裡面需要用到=IF(MOD(
  • 用Excel提取身份證號的年齡,不滿生日的減一歲
    在人事工作中，經常需要根據身份證號來提取信息，最經典的就是要用身份證號來提取年齡，我們舉例，下表是模擬的公司員工身份證號數據 1、簡單提取年齡 我們知道身份證號的第7-14位是我們出生的年月日，我們可以用MID函數來提取出年份，然後用一種簡單的公式來計算年齡，就是使用今年的年份減去出生的年份，公式為： =2020-MID(B2,7,4)
  • excel小知識第27期:excel中身份證號碼中的生日提取
    每天進步一點點，大家好，歡迎收看excel小知識第27期：excel中身份證號碼中的生日提取在前面跟大家分享了如何提取excel表格中的數據，不知道小夥伴們是否在實際應用中使用呢？其實大家要理解這些快捷鍵的運用方法之後，就可以用在不同的地方，不知道有沒有小夥伴們遇到需要提取身份證號碼中的生日這項工作呢？如果不會excel表格中的數據提取，這項工作是不是就會讓你浪費很多的時間呢？今天就和大家分享一下數據提取在這項工作的方便之處。
  • excel函數應用:如何用數位函數分段提取身份證信息*下
    在上篇內容中我們說到了用excel提取身份證號碼中的戶籍地址、出生日期、年齡、生肖、星座等內容，今天我們書接上回，繼續帶大家學習excel提取身份證信息的其他操作！＊＊＊＊＊＊＊＊＊引言：我們繼續上篇生日提取的拓展思路，開始我們今天的內容，保證開篇就有驚喜！4、生日提醒生日提醒的問題，作者覺得要分兩部分來說。
  • 利用身份證號判斷性別、周歲、虛歲,提取生日——函數皆可盤
    本文主要講解利用函數判斷性別，提取生日，判斷周歲，虛歲，以及如何對身份證號中的出生日期部分隱藏為"********"01判斷性別身份證號的第17位數字，奇數為男性，偶數為女性，所以我們利用這一特點進行性別判斷，公式如圖1所示，下面我們進行公式分解講解；
  • 如何用EXCEL提取身份證號中的出生日期和年齡
    在使用excel表格處理人員信息時，經常會遇到人員信息中既有身份證號，又有出生日期和年齡的情況，其實身份證號中就包含出生日期和年齡信息，如果挨個錄入，即費時費力，又不能保證數據前後一致。下面我就介紹如何用身份證號提取出生日期和年齡。
  • 如何用EXCEL自動提取身份證號中的出生日期和年齡?
    身份證號以一敵四第二步：如何在身份證號中提取出出生日期？在18位的身份證號中，第7位開始到第14位，這8位數字代表了這個人的出生日期。在這裡，可以用MID和TEXT兩個函數來製作公式。MID函數詳解：一、作用：此函數為文本函數，用於在文本字符串中，從指定起始位置起返回指定長度的字符。
  • WPS中怎麼快速提取身份證生日和性別?
    在開學或者入職的時候我們經常要填寫一些個人信息表，這上面有我們的身份證號碼，在輸入了身份證號碼之後，如何能快速地將身份證號裡包含的出生年月日迅速提取出來呢？在Excel中我們會需要輸入各種函數，最後還不一定能正確操作。但WPS中要達到這個目的卻十分快速，讓我們一起來看一下具體怎麼操作吧。
  • 如何提取身份證生日和年齡,幾行VBA代碼教你學會
    No.1身份證裡包含了個人生日信息，根據生日也可以計算出年齡。辦公過程中，如果一個一個信息錄入，是不是顯得十分疲勞。如果遇到這樣有規律的工作，還是交給電腦來做更有優勢。下面介紹一下，如何在身份證中提取生日信息的方法。No.2在做這個功能之前，首先要了解一下身份證的代碼構成，也就是編碼規則。
  • 從身份證號碼中提取年齡,沒到生日的自動減1歲
    公司人事部門工作中，經常填寫身份證號、年齡、生日、性別等信息，其實這些信息都可以從身份證號碼中提取。今天小編分享提取年齡的方法，還可以設置沒到生日的自動減1歲。情況一：提取年齡身份證號碼的第7-14位是出生年月日，用MID函數提取出年份，再用今年的年份減去出生年份就是年齡：在D4單元格輸入公式：=2020-MID(C4,7,4)，回車鍵確認公式再雙擊D4單元格右下角向下填充公式，完成整列年齡提取。
  • 如何從身份證號碼中提取出生年月日及性別
    在工作單位有許多的員工需要填寫身份證，到辦公室又要一個一個的輸入，太麻煩了，我們如何用一個簡單的方法填寫呢，下面就介紹一下， 打開excel表格，如圖，輸入身份證號碼， 如果想提取全部的 ****年**月**日，只要在身份證的後面框內輸入=MID(A1,7,8)，
  • 不用公式從excel表格中提取年齡、性別和出生日期新方法
    從身份證中提取性別、年齡、出生日期就變得超級簡單了，選中身份證列，確定後再再選中存放列即可完成，無需再硬記公式了。下面簡單演示：　　一、點開菜單欄上的「提取年齡」　　 　　點開菜單欄上的「提取年齡」　　二、選取身份證號碼所在列後點確定
  • 身份證號碼處理技巧大全
    （文章最後有工具和演示文件的下載地址，可以下載下來同步操作）1、提取籍貫作用：提取身份證號碼中的籍貫信息。操作步驟：打開超級處理器菜單->點擊提取籍貫->選擇身份證號碼->點擊確定->完成。動畫演示如下：
  • 從身份證號碼中提取出生年月、性別、年齡等信息都不會,還想加薪
    大家都知道，身份證號中包含著出生年月，性別等信息，但是在實際的工作中，當我們需要出生年月，性別等信息時，好多小夥伴無從下手，不知道如何從身份證號中提取相關信息。今天，我們將對常用的信息提取方法做一一的講解。一、提取出生年月。方法：在目標單元格中輸入公式：=TEXT(MID(C3,7,8),"00-00-00")。
  • Excel教學:怎樣從身份證號碼中批量提取年齡、性別、出生日期
    在人員的管理中，經常需要錄入身份證號碼，人員的性別、年齡、出生日期等，這些繁瑣的數據非常令人頭疼。那麼有沒有一種簡單的方法讓自己只需要輸入身份證號碼，其他信息自動生成的呢？Excel賦予了我們這個功能，只要有身份證號碼，人員性別、年齡、出生日期等其他基本信息一鍵生成，無須一個個輸入，即使處理幾千人、幾萬人也是分分鐘的事情。第一步，將人員身份證號碼放在電子表格的同一欄，並建立好相應表頭。第二步，藉助Excel函數進行數據生成，首先在第一個人的相應位置建立函數。1.生成實時更新的年齡。
  • 暖心HR收藏帖:從身份證中算生肖、查星座,算倒數日
    在《EXCEL告訴你身份證中的秘密》一文中，我們詳細講解了從身份證號中提取「出生日期」、「性別」、「籍貫」的方法，今天，繼續用EXCEL來挖掘一組暖心數據，教大家如何通過身份證號算生肖、查星座，算生日倒數日，甚至還能算出農曆年。暖心的HR可以收藏此帖，到時只要複製文中的公式就可以使用了。廢話少說，直接上圖、上公式！
  • Excel技巧:一鍵識別身份證裡面的性別、出生年月、年齡、生肖!
    如何在Excel裡面提取身份證中的性別、出生年月、年齡、生肖，這絕對要使用到Excel函數，下面小編一一為大家進行分享，有需要的朋友可以直接將函數進行直接套用即可！1、 識別證件男女其實這裡只需要一個函數就可以幫你一鍵搞定，很多人會好奇這是怎麼實現的呀，其實身份證數字裡面隱藏著很多秘密，比如第17位數字如果是奇數那就是男的，偶數就是女的！