關于身份證及其号碼，大家都不陌生，而且也都知道身份證号碼中包含出生年月、性别等信息，但是如何提取和計算，并不是每位親都掌握的。今天，小編就帶大家了解身份證号碼中的這些秘密。

一、身份證号碼解讀。

從上圖中可以看出，每個人的身份證号碼由18位組成，其中：

1-2位：代表省。

3-4位：代表市。

5-6位：代表區。

7-14位：代表出生年月。

15-17位：代表順序碼。

18位：為識别碼。

我們今天重點要讨論的是如何提取出生年月、判斷性别、計算年齡、防止重複、判别身份證号碼的正确性等。

二、技巧解讀。

（一）、提取出生年月。

函數：Text Mid。

功能及語法結構：

Text：将指定的值設置為指定的指定的形式。

語法結構：=Text(值或單元格引用,格式代碼)。

Mid：從指定字符串的指定位置提取指定長度的值。

語法結構：=Mid(字符串,開始位置,字符長度)。

目的：提取身份證号碼中的出生年月，并設置為日期格式。

方法：

在目标單元格中輸入公式：=TEXT(MID(F3,7,8),"00-00-00")。

解讀：

首先利用Mid函數提取F3單元格中從第7位開始，長度為8的8位數字，然後用Text函數将其設置為日期格式。

（二）判斷性别。

函數：IF Mod Mid。

功能及語法結構：

IF：判斷是否滿足某個條件，如果滿足指定條件，返回一個值，否則返回另一個值。

語法結構：=IF(判斷條件,條件成立時的返回值,條件不成立時的返回值)。

Mod：返回兩個數相除的餘數。

語法結構：=Mod(被除數,除數)。

目的：根據身份證号碼判斷相應的性别。

方法：

在目标單元格中輸入公式：=IF(MOD(MID(F3,17,1),2),"男","女")。

解讀：

1、在身份證号中，第17位代表性别，如果為奇數，則為男，如果為偶數，則為女。

2、公式中首先用Mid函數提取指定身份證号碼（F3）中的第17位，作為Mod函數的被除數，然後用Mod函數計算餘數，并将餘數返回給IF函數的第一個參數，然後用IF函數判斷，如果為奇數，則返回“男”，如果為偶數，則返回“女”。

（三）計算年齡。

函數：Datedif。

功能：以指定的方式統計兩個日期之間的差值。

語法結構：=Datedif(開始日期,終止日期,統計方式)。常見的“統計方式”有“Y”、“M”、“D”三種，即“年”、“月”、“日”。

目的：根據身份證号碼計算對應的年齡。

方法：

在目标單元格中輸入公式：=DATEDIF(TEXT(MID(F3,7,8),"00-00-00"),TODAY(),"y")。

解讀：

1、如果數據表中沒有出生年月，則可以用Text Mid函數進行提取，如果有，則可以直接使用。

2、結束日期用Today()函數替代，其目的在于保持年齡的自動更新。

（四）查重及禁止重複錄入

1、常規（錯誤）做法。

函數：COUNTIF。

功能：計算指定區域中滿足指定條件的單元格個數（單條件計數）。

語法結構：=Countif(條件範圍,條件)。

目的：判斷身份證号碼是否有重複值。

方法：

在目标單元格中輸入公式：=IF(COUNTIF(F$3:F$12,F3)>1,"重複","")。

解讀：

從公式的執行結果中發現有4個人的身份證号碼重複，但經過分析，發現并不重複，Why？公式錯誤？分析公式，并沒有錯誤，其實根本的原因在于在Excel系統中，超過15位的數值全部按0存儲。所以用=IF(COUNTIF(F$3:F$12,F3)>1,"重複","")判斷的結果就會顯示重複。

2、正确做法。

目的：判斷身份證号碼是否有重複值。

方法：

在目标單元格中輸入公式：=IF(COUNTIF(F$3:F$12,F3&"*")>1,"重複","")。

解讀：

1、對比兩個公式，發現隻是在新公式的對比條件中增加了一個*，但得到了正确的結果。

2、在Excel中，*被稱為通配符，加上通配符的作用就是将當前的數字内容轉換為文本，然後進行對比，從而得到正确的結果。

3、禁止重複錄入。

方法：

1、選定目标單元格區域。

2、【數據】-【數據驗證】，打開【數據驗證】對話框。

3、選擇【允許】中的【自定義】，在【公式】中輸入：=COUNTIF(F$3:F$12,F3&"*")=1。

4、單擊【出錯警告】标簽，輸入【标題】和【錯誤信息】并【确定】。

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