Excel函式公式2018-12-11 11:55:04

感謝邀請，其實身份證號中蘊藏著大量的資訊，詳情請看下文！

從身份證號中提取出生年月、性別、年齡等資訊都不會，還想加薪？

大家都知道，身份證號中包含著出生年月，性別等資訊，但是在實際的工作中，當我們需要出生年月，性別等資訊時，好多小夥伴無從下手，不知道如何從身份證號中提取相關資訊。今天，我們將對常用的資訊提取方法做一一的講解。

一、提取出生年月。

方法：

在目標單元格中輸入公式：=TEXT（MID（C3，7，8），“00-00-00”）。

解讀：

1、Mid函式的主要作用是從指定欄位的指定位置提取指定長度的字串。語法結構為：=Mid（字串，起始位置，字串長度）。

2、Text函式的主要作用是將字串設定為指定的格式。

二、提取性別。

方法：

在目標單元格中輸入公式：=IF（MOD（MID（C3，17，1），2），“男”，“女”）。

解讀：

1、首先用Mid函式提取第17位上的數字。

2、用Mod函式求模取餘。

3、用If函式判斷求模取餘的結果，如果為計數，返回“男”，如果為偶數，返回“女”。

三、計算年齡。

方法：

在目標單元格中輸入公式：=DATEDIF（D3，TODAY（），“y”）。

解讀：

Datedif函式為系統隱藏函式，其功能為按照指定的方式統計兩個時間之間的差。其語法結構為：=Datedif（開始時間，結束時間，統計方式）。其中公式中用到的統計方式“y”指的是按“年”進行計算。

四、計算退休年齡。

方法：

在目標單元格中輸入公式：=EDATE（D3，MOD（MID（C3，17，1），2）*120+600）。

解讀：

1、Edate函式的主要功能為：返回一串日期，指示起始日期之前或之後的月數。

2、此處的計算規則為：男工作60年退休，女50年退休。首先用Mod函式判斷性別，如果為“男”，則在出生日期的基礎上加上1*120+600=720個月，也就是60年。如果為“女”，則在出生日期的及出生加上0*120+600=600個月，也就是50年。

五、判斷身份證號是否重複。

1、一般方法（錯誤方法）。

方法：

在目標單元格中輸入公式：=IF（COUNTIF（$C$3：$C$9，C3）>1，“重複”，“”）。

分析：

1、從單元格中我們可以觀察出兩個身份證號並不重複，公式也沒有錯誤，但為什麼最後的判斷結果是“重複”呢？

2、在Excel中，超過15位的數字，有效保留位數為15位，其餘位數全部視為0處理。而單元格中的身份證號只有最後兩位不同，但在實際的處理中視為0，所以判斷結果為“重複”。

2、正確判斷。

方法：

在目標單元格中輸入公式：=IF（COUNTIF（$C$3：$C$9，C3&“*”）>1，“重複”，“”）。

解讀：

1、比較兩個公式，發現只是在C3的後面添加了*（星號）。但是得到了正確的結果。

2、其實在Excel中，*（星號）一般被稱為萬用字元，加上萬用字元的作用就是講當前的數字內容強制轉換為文字，然後進行對比從而得到了正確的結果。

六、防止重複。

方法：

1、選中資料來源，【資料】-【資料驗證】。

2、選擇【允許】中的【自定義】，並在【公式】中輸入：=COUNTIF（$C$3：$C$9，C3）=1。

3、單擊【輸入資訊】和【出錯警告】標籤，分別錄入資訊並【確定】。

4、測試有效性

結束語：

此文從提取出生年月、性別、計算年齡及退休年齡，如果防止身份證號重複等方面做了詳細的介紹，是一篇很不錯的關於“身份證號”相關技巧的文章哦！

如果各位親在學習的過程中遇到或發現任何困難或問題，歡迎在留言區留言討論哦！