身份證號(hào)碼不單單是一串簡(jiǎn)單的數(shù)字，其蘊(yùn)藏著很多重要的信息，例如，出生年月、性別等……那么，如何在Excel中提取身份證號(hào)碼中的出生年月、性別等信息呢？不妨從學(xué)習(xí)下文開始吧……

一、Text+Mid：提取出生年月。

方法：

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

解讀：

1、身份證號(hào)碼中從第7位開始，一直到第15位結(jié)束為出生年月。

2、Mid函數(shù)的作用是：從指定的字符串中提取指定長度的字符。其語法結(jié)構(gòu)為：=Mid(字符串或引用，提取開始位置，字符長度)。

3、Text函數(shù)的作用是：對(duì)指定的字符串按照指定的格式進(jìn)行設(shè)置。

二、If+Mod+Mid：提取性別。

方法：

在目標(biāo)單元格中輸入公式：=IF(MOD(MID(C3,17,1),2),"男","女")。

解讀：

1、身份證號(hào)碼中的第17位代表的為性別，如果÷2余數(shù)為1，則為“男”，如果÷2余數(shù)為0，則為“女”。

2、首先利用Mid函數(shù)提取第17位上的值，然后利用Mod函數(shù)求余，最后用If函數(shù)判斷求余的結(jié)果，如果余數(shù)為1，則返回“男”，如果余數(shù)為“0”，則返回“女”。

三、Datedif函數(shù)：計(jì)算年齡。

方法：

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

解讀：

1、Datedif函數(shù)為系統(tǒng)隱藏函數(shù)。其作用為按照指定的統(tǒng)計(jì)方式計(jì)算兩個(gè)日期之間的差值。其中“y”為“年”，暨計(jì)算兩個(gè)日期相差的年份。

2、語法結(jié)構(gòu)：=Datedif(開始日期，結(jié)束日期，統(tǒng)計(jì)方式)。

3、示例中用Today()函數(shù)替代“結(jié)束日期”。

四、Edate函數(shù)：計(jì)算退休年齡。

方法：

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

解讀：

1、Edate函數(shù)的作用為：返回一串日期，指示起始日期之前或之后的月份。

2、公式：=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)是按照男同志60歲退休，女同志50歲退休來計(jì)算的，具體過程為：首先判斷性別，如果為“男”，則在生日的基礎(chǔ)上加上1*120+600=720（月），暨60年；如果為“女”，則在生日的基礎(chǔ)生加上0*120+600=600（月），暨50年；然后用Edate函數(shù)返回生日之后60年或50年的日期。

五、If+Countif：判斷身份證號(hào)碼是否重復(fù)。

1、一般方法（錯(cuò)誤方法）。

方法：

在目標(biāo)單元格中輸入公式：=IF(COUNTIF(C$3:C$9,C3)>1,"重復(fù)","")。

解讀：

1、從單元格中的身份證號(hào)碼中可以看出并不重復(fù)，而且公式也沒有錯(cuò)誤，但返回的結(jié)果為“重復(fù)”，Why？？？

2、在Excel中，數(shù)值的有效位數(shù)為15位，暨超過15位的值被視為0處理，而單元格中的身份證號(hào)只有后3位不同，被視為0處理，所以返回的結(jié)果為“重復(fù)”。

2、正確做法：添加“*”。

方法：

在目標(biāo)單元格中輸入公式：=IF(COUNTIF(C$3:C$9,C3&"*")>1,"重復(fù)","")。

解讀：

對(duì)兩個(gè)公式進(jìn)行比較，發(fā)現(xiàn)只是在計(jì)數(shù)條件的后面添加了“*”，其實(shí)在Excel中，“*”被稱為通配符，加上通配符的作用就是將當(dāng)前的數(shù)值內(nèi)容強(qiáng)制轉(zhuǎn)換為文本，然后進(jìn)行對(duì)比得到正確的結(jié)果。

六、If+Countif：防止重復(fù)。

方法：

1、選定目標(biāo)數(shù)據(jù)源，【數(shù)據(jù)】-【數(shù)據(jù)驗(yàn)證】。

2、選擇額【允許】中的【自定義】，并在【公式】中輸入：=COUNTIF(C$3:C$9,C3&"*")=1。

3、完善警告信息！

結(jié)束語：
此文從提取出生年月、性別、計(jì)算年齡及退休年齡，防止身份證號(hào)重復(fù)等方面做了詳細(xì)的介紹，是職場(chǎng)辦公人員必備的技能之一哦！

學(xué)習(xí)過程中遇到任何問題，歡迎大家在留言區(qū)留言討論哦！

【精彩推薦】

【5個(gè)常用且實(shí)用的函數(shù)公式，提高工作效率分分鐘哦！】

【Excel經(jīng)典函數(shù)組合實(shí)用技巧解讀！】

【基礎(chǔ)高效的數(shù)據(jù)打印技巧都不掌握，那就真的Out了！】

【單條件求和函數(shù)Sumif實(shí)用技巧解讀！】

【職場(chǎng)必備的9個(gè)Excel函數(shù)公式，絕對(duì)的硬核干貨哦！】

【Ctrl及Ctrl+Shift組合鍵在Excel中的11個(gè)實(shí)用技巧，掌握50%的都是超級(jí)高手！】

【查詢引用，你喜歡用Lookup、Vlookup還是Index+Match組合？】

【含金量100%的10個(gè)硬核操作技巧，100%掌握的都是高手！】

【以一敵百的4個(gè)多條件統(tǒng)計(jì)函數(shù)，100%掌握的都是超級(jí)高手！】

【隨機(jī)抽取相關(guān)人員參加活動(dòng)，在Excel中如何實(shí)現(xiàn)？】

【如何保護(hù)含有公式的單元格，你真的會(huì)嗎？】

【萬能函數(shù)Sumproduct實(shí)用技巧解讀！】

【實(shí)用干貨、自動(dòng)更新的圖片等硬核技巧解讀！】

【條件計(jì)數(shù)函數(shù)Countif實(shí)用技巧解讀！】

【多條件計(jì)數(shù)、求和、判斷、查詢都不掌握，還怎么成為Excel達(dá)人？】

【四組超級(jí)變態(tài)的Excel函數(shù)公式，簡(jiǎn)直好用到哭！】

【關(guān)于Alt鍵的6個(gè)實(shí)操技巧，不知道真的虧大了！】

【30個(gè)硬核實(shí)操技巧，掌握50%的就是大咖！】