身份證号，大家都不陌生，但是對于其包含的信息可能并不是特别了解，除了最常見的出生年月，性别外，還包含哪些信息？如果要在Excel中進行計算，應該怎麼操作呢？

一、正确輸入身份證号碼。

目的：在單元格中正确的輸入身份證号碼。

方法：

首先輸入“'”（單撇号）,緊接着輸入身份證号碼。

解讀：

1、Excel最長能夠保存的數字為15位，而身份證号碼為18位，所以默認情況下輸入身份證号碼後後三位數被替換為0。

2、除了在号碼前添加“'”（單撇号）外，還可以提前将單元格的格式設置為“文本”。

二、從身份證号碼中提取出生年月。

1、公式法。

方法：

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

解讀：

1、分析身份證号碼，發現從第7位開始長度為8的字符串為“出生年月”。所以需要将其從身份證号碼中提取出來。

2、Mid函數的作用為：返回文本字符串中從指定位置開始長度為指定值的字符。語法結構為：=Mid(字符串,開始位置,字符長度)。如=MID(C3,7,8)返回的結果為“19650821”。

3、如果僅用Mid函數提取身份證号碼中的8為數字，其并不是“出生年月”，因為出生年月是“年月日”的形式，所以需要對其進行格式設置，而Text函數的作用為根據指定的代碼對指定的值進行格式設置，語法結構為=Text(字符串,格式代碼)，其中“00-00-00”為日期格式代碼。

2、快捷鍵法。

方法：

1、在第一個目标單元格中輸入第一個人出生年月的8位數字。

2、選取目标單元格，包括第一個輸入内容的單元格，快捷鍵Ctrl E。

3、【數據】-【分列】-【下一步】-【下一步】，選擇【列數據格式】中的【日期(YMD)】并【完成】。

解讀：

如果不對智能填充的值【分列】，則僅僅為8為數字，并不是“出生年月”。

三、根據身份證号碼計算性别。

方法：

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

解讀：

1、分析身份證号碼不難發現，性别隐藏在第17位，如果為“奇數”，則為“男”，否則為“女”。

2、Mod函數的作用為計算兩個數字相除的餘數，語法結構為：=Mod(被除數,除數)。如=Mod(5,2)的返回結果為1。

3、公式=IF(MOD(MID(C3,17,1),2),"男","女")中首先用Mid函數提取身份證号碼的第17位，然後将返回的值作為Mod函數的被除數，除數為2，并将Mod函數的計算結果作為IF函數的判斷條件，如果條件成立，則返回“男”,否則返回“女”。

四、根據身份證号碼計算年齡。

方法：

在目标單元格中輸入公式：=DATEDIF(D3,TODAY(),"y")。

解讀：

1、Datedif函數為系統隐藏函數，其功能為按照指定的計算方式計算兩個日期之間的差值。語法結構為：=Datedif(開始日期,結束日期,統計方式)。常用的統計方式有“Y”、“M”、“D”，分别為“年”、“月”、“日”。

2、日常生活中，問“多少歲了”指的是從出生日期到今天經過了多少年。所以結束日期用Today()函數獲取，隻要你打開表格，其年齡時自動更新的，而不是不定不變的。如果你的年齡要按“月”為單位統計，則隻需将公式中的“y”替換為“m”即可。

3、如果給定的表單中沒有“出生年月”列，此時就先要從身份證号碼中提取出生年月，然後再計算年齡，此時的公式為：=DATEDIF(TEXT(MID(C3,7,8),"00-00-00"),TODAY(),"y")，一步到位。

五、利用身份證号碼計算退休年齡。

（以男60歲、女50歲退休為例）

方法：

在目标單元格中輸入公式：=EDATE(D3,MOD(MID(C3,17,1),2)*120 600)。

解讀：

1、Edate函數的作用為：返回指定日期之後數月的日期，語法結構為：=Edate(開始日期,月份)。如=Edate("2020/4/19",12)的返回結果為：2021/4/19。

2、公式=EDATE(D3,MOD(MID(C3,17,1),2)*120 600)中，首先判斷其性别，如果為男性，則在D3的基礎上 1*120 600個月，如果為女性，則在D3的基礎上 0*120 600個月。

3、如果給定的表單中沒有“出生年月”列，此時就先要從身份證号碼中提取出生年月，然後再計算退休年齡，此時的公式為：=EDATE(TEXT(MID(C3,7,8),"00-00-00"),MOD(MID(C3,17,1),2)*120 600)。

4、如果按照“男60歲，女55歲”計算，則公式為：=EDATE(D3,MOD(MID(C3,17,1),2)*60 660)。

六、判斷身份證号碼是否重複。

1、常規操作（錯誤方法）。

方法：

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

解讀：

公式中用Countif函數統計當前值在指定範圍内的個數，如果＞1，返回“重複”……其實思路沒有任何問題，但重點在于Excel中的數值精度，目前隻支持15位，15位以後的值全部按照0來對待，所以統計出的結果為“重複”。

2、正确做法。

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

解讀：

和上述的公式相比較，隻是在C3後添加了“&"*"”，但結果截然不同，Why？其實很簡單，就是加上通配符“*”之後，原來的數值變為了文本。

七、禁止錄入重複的身份證号碼。

方法：

1、選定目标單元格。

2、【數據】-【數據驗證】，選擇【允許】中的【自定義】，在【公式】中輸入：=COUNTIF(C:C,C3&“*”)=1。

3、單擊【出錯警告】标簽，錄入警告信息并【确定】。

八、根據身份證号碼查詢引用。

方法：

在目标單元格中輸入公式：=VLOOKUP(G3,C3:D9,2,0)。

解讀：

1、Vlookup函數為常見的查詢引用函數之一，其語法結構為：=Vlookup(查詢值,數據範圍,返回列數,匹配模式)。當匹配模式為“0”時為精準匹配，為“1”時為模糊匹配。

2、由于每一個人的身份證号碼是唯一的，所在身份證号碼就是“索引值”，代表唯一的數值，結合Vlookup或Lookup等獲取其他信息。

結束語：

從文中可以看出，身份證号碼中隐藏着很多信息，除了車身年月、性别外，還可以計算年齡、退休年齡、作為“索引值”等……對于使用技巧，你Get到了嗎？如果親有更多的關于身份證号碼的應用，不妨在留言區留言讨論哦！

#Excel函數公式# #我要上頭條#