Excel教程下載和軟件下載中心,Microsoft技術社區聯盟成員,全球極具影響力的Excel門戶,Office技術培訓社區

和身份證有關的5個Excel函數公式

2017-10-05  作者:ExcelHome  閱讀:

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

1、查找重復身份證號碼:
=IF(COUNTIF(A:A,A2&"*")>1,"重復","")


要點:
在COUNTIF函數的第二參數后加上一個星號 &"*"。
COUNTIF函數在計算文本型數字時,會默認按數值型進行處理,但是Excel中的數字精度只有15位,并且身份證號碼是18位,因此會把前15位相同的身份證全部識別為相同的內容。在第二參數后加上一個星號 &"*",就是告訴Excel,要查找包含A2單元格內容的文本,通過這樣變通的手段,COUTNIF函數就聽話啦。

2、用出生年月來計算年齡:
=DATEDIF(A4,TODAY(),"y")

要點:
DATEDIF函數用于計算兩個日期之間的間隔。用法是:
=DATEDIF(開始日期,結束日期,指定要返回的類型)
第三參數是Y,表示年,是M,就表示月。
TODAY函數返回系統當前的日期。
DATEDIF函數是隱藏函數,輸入時沒有屏幕提示,需要手工錄入哦。

3、根據身份證號計算出生年月:
=--TEXT(MID(A2,7,8),"0!/00!/00")


要點:
身份證號碼中的第8位到15位是出生年月信息。
首先使用MID函數從A2單元格的第7位開始,提取出8個字符,得到類似 19840727的結果。
再使用TEXT函數將這個字符串變成日期樣式的文本,得到類似1984/07/27的結果。
由于TEXT函數的結果是文本型的內容,所以加上兩個小減號 -- 就是計算負數的負數,通過四則運算,變成真正的日期序列。
公式也可以使用=--TEXT(MID(A2,7,8),"0-00-00"),原理是一樣的。

4、根據身份證號碼提取性別:
=IF(MOD(MID(A2,15,3),2),"男","女")


要點:
身份證號碼中的第17位是性別信息,單數表示男,雙數表示女。
首先使用MID函數從A2單元格的第15位開始,提取出3個字符。
再使用MOD函數計算這個字符與2相除的余數。
最后使用IF函數判斷,如果余數大于0,返回男,如果余數是0,則返回女。
這里使用MID(A2,15,3),是為了兼容15位的舊身份證,這部分公式也可以使用MID(A2,17,1)。

5、根據身份證號計算退休時間:
=EDATE(TEXT(MID(A2,7,8),"0!/00!/00"),MOD(MID(A2,15,3),2)*120+600)


要點:
先用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年)就是退休年齡了

轉載請注明來源"ExcelHome"并保留原文鏈接。
固定鏈接:http://www.fvtgnn.tw/lesson/article/excel/1876.html
分享到:
Copyright 1999 - 2020 Excel Home.All Rights Reserved.
本站特聘法律顧問:徐懷玉律師 李志群律師   滬ICP備11019229號

滬公網安備 31011702000001號

征信       
35选7摇奖器