Excel表格中的身份證號如何計算年齡

2021-02-21 aplala


在工作中，將身份證輸入表格的大多是財務或是HR，本以為只要輸入數字就可以了，可是，當大家做這事兒的時候，有多少人發現，這個身份證真的很抽風……

 

如果你正做著有關工作，那麼就一起來看一下以下內容吧，讓你的Excel操作輕鬆碾壓各種有關身份證的信息。

以下是一組和身份證有關的函數公式，如果你是財務人員或是一名HR，那就一起來看一下啦：

1、查找重複身份證號碼

在Excel表格上方輸入【=IF(COUNTIF(A:A,A1&"*")>1,"重複","")】，其中「A1」可以自定義修改。

 

說明：COUNTIF函數在計算文本型數字時，會默認按數值型進行處理，但Excel中的數字精度只有15位，但身份證號碼是18位，因此會把前15位相同的身份證全部識別為相同的內容。

在第二參數後加上一個星號 &"*"，就是告訴Excel，要查找包含A1單元格內容的文本，通過這樣變通的手段，COUTNIF函數就會乖乖聽話啦。

2、出生年月來可計算年齡

在Excel表格上方輸入【=DATEDIF(A4,TODAY(),"y")】，然後按回車鍵，即可在其中「A4」可以自定義修改。

 

說明：DATEDIF函數用於計算兩個日期之間的間隔。

=DATEDIF（開始日期,結束日期,指定要返回的類型)；第三參數Y表示年，M則表示月。TODAY函數返回系統當前的日期。

DATEDIF函數是隱藏函數，輸入時沒有屏幕提示，需要手工錄入。

 

3、根據身份證號計算出生年月


在Excel表格上方輸入【=--TEXT(MID(A2,7,8),"0!/00!/00")】，

然後按回車鍵，即可在其中「A2」可以自定義修改。

 

說明：身份證號碼中的第8位到15位是出生年月信息。先用MID函數從A2單元格的第7位開始，提取出8個字符，即可得到身份證號中的出生年月日信息。

接著，使用TEXT函數將這個字符串變成日期樣式文本即可。

因為TEXT函數的結果是文本型的內容，可加上兩個小減號「--」，來計算負數的負數，通過四則運算，變成真正的日期序列。

公式也可以使用=--TEXT(MID(A2,7,8),"0-00-00")

 

4、根據身份證號碼提取性別

在Excel表格上方輸入【=IF(MOD(MID(A2,15,3),2),"男","女")】，然後按回車鍵，即可在其中「A2」可以自定義修改。

 

說明：身份證號碼中的第17位是性別信息，單數表示男，雙數表示女。知道以上內容後……

a.先用MID函數從A2單元格的第15位開始，提取出3個字符。

b.再用MOD函數計算這個字符與2相除的餘數。

c.最後用IF函數判斷，如果餘數大於0，返回男，如果餘數是0，則返回女。

我們用MID(A2,15,3)，可兼容15位的舊身份證，這部分公式也可以使用MID(A2,17,1)。

5、根據身份證號計算退休時間：

在Excel表格上方輸入【=EDATE(TEXT(MID(A2,7,8),"0!/00!/00"),MOD(MID(A2,15,3),2)*120+600)

】，然後按回車鍵，即可在其中「A2」可以自定義修改。

 

說明：首先，用TEXT(MID(A2,7,8),"0!/00!/00")，計算出生年月信息。

然後用MOD(MID(A2,15,3),2)計算性別碼和2相除的餘數，結果是1或是0。

這裡如果性別碼和2相除的餘數是1，MOD(MID(A2,15,3),2)*120+600部分就相當於1*120+600，結果是720。否則就是0*120+600，結果是600。

EDATE函數返回一個日期經過指定月份後的日期，也就是以出生年月為準，男性要經過720個月（60年），女性要經過600個月（50年）就是退休年齡了。

有沒有看暈？其實道理很簡單，將相關公式記下來，隨時備用吧。除此之外，我們還可以了解下Excel文檔的其他問題，比如：

Excel和PDF文件的相互轉換：用WPS和Microsoft office都可以將Excel轉換輸出為PDF，如果你想要將PDF表格轉換成Excel，可以用迅捷PDF轉換器。

 

Excel文檔編輯後數據丟失怎麼辦：一般情況下文檔會自動備份，先到備份文件中找找，如果沒有的話，就用強力數據恢復軟體進行掃描恢復。

相關焦點

  • 在EXCEL中利用身份證號計算年齡
    今天我們將介紹身份證提取年齡的問題。在實際的工作中，我們經常要用到一個人年齡，比如按公司所有員工的年齡來進行分組，如果手動輸入的話，會是一個相當大的工作量，而且年齡有一個特點，就是每年年齡都會漲，這意味著去年你做的年齡分組今年就不適用了。
  • Excel小知識 身份證號計算年齡
    (TODAY())—MID（C2,7,4）按回車即可計算年齡算出一個年齡後雙擊右下角，向下填充操作演示：今日教程函數功能解析：Excel 按順序的系列數保存日期，這樣就可以對其進行計算下面小編教你怎麼在excel中使用year函數，希望對你有幫助!在excel中year函數的使用方法year函數只是返回的參數的年份，他的參數可以是一個函數，也就是說year函數可以嵌套函數，這裡我們嵌套日期函數today函數excel 日期函數的使用教程日期函數使用步驟1:例如顯示今天的日期。
  • 人事用Excel從身份證號裡面自動計算年齡,一個公式實現!
    在人事工作中，對員工的身份證信息處理是經常要做的事，如何快速從身份證號中計算年齡呢？
  • Excel通過身份證號碼計算年齡
    上篇通過身份證號碼提取出生日期，聯想到如何用身份證號碼生成年齡，下面就解決下這個問題。
  • 1個公式搞定根據身份證號碼計算年齡
    | Excel函數與公式（ID：ExcelLiRui）微信個人號 | （ID：ExcelLiRui520）1個公式搞定根據身份證號碼計算年齡身份證號碼裡包含很多信息，比如年齡、性別等。今天我來結合一個實際案例，介紹1個公式搞定根據身份證號碼計算年齡。
  • WPS Excel: 從身份證中提取生日、年齡、性別、籍貫、生肖、星座
    從一串身份證號碼中，可以1秒提取出這個號碼對應的生日、年齡、性別、籍貫、生肖、星座。為一名HR，需要登記員工的許多信息，如果學會了怎麼從身份證號碼中提取這些信息，可以節省許多時間。提取年齡公式：「=DATEDIF(B2,TODAY(),"Y")」B2單元格是我們提取到的生日，DATEDIF函數中前兩個表示日期，最後一個參數表示計算方式，「Y」表示計算這兩個日期之間相差多少年。
  • 【實例】通過身份證號來提取出生年月日計算實際年齡(精確到月份和日期)
    本次，看到朋友留言，說如何提取身份證年齡。如圖：我們，就以下圖為例，說一下處理的方法和步驟：而當前的日期，時間，可以通過使用函數today來處理因為計算年齡，需要2個條件，除了判斷年以外，還要判斷生日，如果是在當日前，要按年相減，再減1，如果是在當年月的月日後，則等於年份減。
  • Excel計算表格淺談
    這些計算處理中，簡單的有兩三個數據的平均，複雜的有多步運算，涵蓋了求RSD，lg等。在實際工作中，大家基本上都是用excel來代替計算器來進行計算，否則工作效率和計算結果的準確性根本無法保證。但為了避免對excel計算表格的驗證，官方審計時大家都宣稱是用計算器來進行計算的。其實審計人員是不相信的。曾經經歷過一次FDA審計，審計官現場要求用計算器計算檢驗記錄中複雜的計算。
  • excel表格如何計算乘法?excel求積方法介紹
    作為剛學習excel表格的新手們，你們面臨的是一個巨大的表格，它包含的知識很多，包含的功能也是強大的。當你所學習的表格製作不再局限於求和求平均數的時候，那麼你就開始關注如何求得兩個甚至多個單元格相乘的方法了，現在我就跟大家分享一下如何求積。1、我們現在看如圖所示的，要求的E列的結果，看到表格的下方給到提示，每次加班費按照20元每次計算，所以我們大致可以知道用D列乘以20就是E列的結果了。
  • Excel中如何計算兩個日期之間年份、月數和天數
    近期在公眾號中有很多朋友詢問如何在excel中計算兩個日期之間天數和時間，今天我們就來講講這個問題如何解決。
  • 根據身份證號自動統計生日、出生地的 Excel花名冊
    今天在論壇看到一位老哥製作了一個Excel人員花名冊，其中最主要的功能是可以根據身份證號自動填充生日、出生地等數據。
  • 【Excel表格實例】嫣然一笑留言需要人員名字(人員名單、Excel表格通訊錄)表格格式(聯繫方式表格)
    【實例】通過身份證號來提取出生年月日計算實際年齡（精確到月份和日期）上一篇文章中，演示文件下載地址：https
  • 如何利用EXCEL中FORCAST函數計算IC50
    計算），Logit 回歸（適用於非常態分布），寇氏法，序貫法，Reed-Meuench法（累積法），直線回歸法（linear regression），還有最為準確的加權直線法（weighted linear regression，用於劑量型分析）。
  • 一組和身份證有關的函數公式
    老祝說：COUNTIF函數在計算文本型數字時，會默認按數值型進行處理，但是Excel中的數字精度只有15位，並且身份證號碼是18位，因此會把前15位相同的身份證全部識別為相同的內容。在第二參數後加上一個星號 &"*"，就是告訴Excel，要查找包含A2單元格內容的文本，通過這樣變通的手段，COUTNIF函數就聽話啦。
  • 如何使用excel表格計算RR和OR的95%置信區間
    OR（比值比）：OR指病例組中暴露和非暴露人數的比值和對照組中暴露與非暴露人數的比值的比。首先我做了個病例對照研究，我回顧性收集了過去5年內A實驗樓和B實驗室樓所有學生的頭髮情況。還應計算OR的置信區間，若區間跨1，一般說明該因素無意義。OR=(a/c)/(b/d)=ad/bc我打開一個可以計算OR值的excel表格。從結果可以看出，在A實驗樓讀博是禿頭的危險因素。但是病例對照研究只能說明暴露與結局的聯繫，而無法說明因果關係，因此我還是無法說明因為進入了A實驗室會導致禿頭，但是進一步做隊列研究能解答這個問題。
  • 巧用EXCEL中的VBA和函數結合整理比對多種分散數據
    表格）是非常零散的數據，而且其中部分單位數據欄位名不在同一列，欄位名也不盡相同，如有的欄位名為「身份證號」，有的欄位名為「身份證號碼」，有的在第2列，有的在第3列，要是用SQL整理分析數據，必須要對這些零散的excel表格進行數據整理，工作量非常大，耗時費力，效率低下，由於審計組的審計目的只是要比對需要核實的人員信息是否在這些零散的excel表格中出現，如何用最少的時間，最小的工作量完成此項任務就擺在了審計組面前
  • 如何快速合併多個Excel表格
    之前在做隆德縣村莊分類時遇到這樣一個問題，從村莊調查信息系統裡面導出的表格是以每個村級為單位的單個表格，而為了更好地統計、分析所有村莊，需要把所有表格合併為一個總表
  • Excel表格中如何把行轉成列或者說把多列轉多行,Excel置換
    在excel表格中，我們會碰到需要把行變成列，或者是把列變成行的情況，　　後續可以對置換後的內容，進行美化，比如居中，添加邊框等操作。
  • 都2021了,你還不會這樣計算日期?真的是OUT了!
    今天技巧妹跟大家分享兩個工作中比較常見的有關日期計算的實際案例，希望對大家有所啟發。
  • Excel如何計算員工的實際年齡?為啥我算出來是虛歲?
    如何才能正確計算出實際周歲呢？下圖中可以看到本例用到Y這個參數，用於計算兩個日期之間的年數，這個年數是系統按照正式的每年的周期進行計算，所以比除以365天要來得精準。可以在幫助中看到函數的用法舉例。手機觀看只需要登錄本微信公眾號，底部菜單-課程訂閱 不管付費還是免費的小夥伴們，都可以進入直接觀看。