excel技巧：與身份證有關的5個Excel函數公式

今天小編帶大家了解一組和身份證有關的函數，如果你是財務人員或是一名公司hr，那就一起來看一下吧！

函數公式解釋：（看圖）

DATEDIF函數用於計算兩個日期之間的間隔

用法是： =DATEDIF(開始日期,結束日期,指定要返回的類型)

其中第三參數是Y，表示年，是M，就表示月；

TODAY函數返回系統當前的日期。

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

2、根據身份證號計算退休時間： =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年）就是退休年齡了

3、根據身份證號碼提取性別： =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)

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

函數公式解釋：（看圖）

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

5.根據身份證號計算出生年月： =--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")。

大家學會了嗎？有問題歡迎到評論區騷擾哦！後期不斷更新軟體學習技巧幹貨，保證一看就會！如果覺得有用可以轉發一下，謝謝大家支持！

推薦閱讀：