營長說

總有HR夥伴咨詢，如何從身份證号中計算性别、生日和年齡等等信息。今天營長推薦給你身份證号在Excel中的常用計算公式和處理技巧。

我國身份證号碼是18位，其中前2位代表省/自治區/直轄市，3,4位代表市，5,6位代表縣區，7-14位代表出生年月日，15-18位分别為順序碼和校驗碼。

01 計算性别

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

MID函數獲取倒數第2位數字

MOD是取餘數函數，在這裡可以判斷奇偶性

IF函數根據奇偶性，奇數為男，偶數為女。

02 計算生日

在Excel2013及以上版本的Excel中，使用快速填充功能可以從規則的數據中提取一定樣式的數據。

快速填充需要給Excel軟件做1個或多個例子，讓電腦找規律。

快速填充的快捷鍵是【 Ctrl E】，效率更高。注意事項：

• 預先設置成兩位月份和日期格式：yyyy-mm-dd
• 可以多做幾個示例，提高準确度

2013以下的版本可以使用公式

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

單元格格式需要設置為日期格式。

03 計算年齡

說明：計算日期為2020年4月20日

F2=DATEDIF(E2,TODAY(),"Y")

DATEDIF函數可以計算日期間隔，結束日期用today()函數，可以動态獲取截止當天的年齡。

04 計算下次生日

說明：計算日期為2020年4月20日

G2=EDATE(E2,(F2 1)*12)

EDATE函數常用于合同到期日計算

下次生日計算思路是，從出生日期開始，經曆虛歲（周歲 1）*12月數的日期。

05 計算下次生日天數

說明：計算日期為2020年4月20日

H2=G2-TODAY()

單元格格式設置為常規。

06 标記重複

使用條件格式，快速标記重複值。

07 統計重複次數

I2=COUNTIF(C:C,C2)

COUNTIF函數統計單一條件出現次數

COUNTIFS函數統計多條件出現次數

08 限制輸入18位長度

使用數據驗證，限制輸入文本長度為18位。

不符合規則，禁止輸入。

Excel辦公教程

▼▼▼

購書贈送300分鐘高清視頻教程

詳見圖書封底說明

視頻教程目錄

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部