|
感謝邀請(qǐng)，其實(shí)身份證號(hào)中蘊(yùn)藏著大量的信息，詳情請(qǐng)看下文！ 從身份證號(hào)中提取出生年月、性別、年齡等信息都不會(huì)，還想加薪？ 大家都知道，身份證號(hào)中包含著出生年月，性別等信息，但是在實(shí)際的工作中，當(dāng)我們需要出生年月，性別等信息時(shí)，好多小伙伴無(wú)從下手，不知道如何從身份證號(hào)中提取相關(guān)信息。今天，我們將對(duì)常用的信息提取方法做一一的講解。 一、提取出生年月。 方法： 在目標(biāo)單元格中輸入公式：=TEXT(MID(C3,7,8),'00-00-00')。 解讀： 1、Mid函數(shù)的主要作用是從指定字段的指定位置提取指定長(zhǎng)度的字符串。語(yǔ)法結(jié)構(gòu)為：=Mid(字符串，起始位置，字符串長(zhǎng)度)。 2、Text函數(shù)的主要作用是將字符串設(shè)置為指定的格式。 二、提取性別。 方法： 在目標(biāo)單元格中輸入公式：=IF(MOD(MID(C3,17,1),2),'男','女')。 解讀： 1、首先用Mid函數(shù)提取第17位上的數(shù)字。 2、用Mod函數(shù)求模取余。 3、用If函數(shù)判斷求模取余的結(jié)果，如果為計(jì)數(shù)，返回“男”，如果為偶數(shù)，返回“女”。 三、計(jì)算年齡。 方法： 在目標(biāo)單元格中輸入公式：=DATEDIF(D3,TODAY(),'y')。 解讀： Datedif函數(shù)為系統(tǒng)隱藏函數(shù)，其功能為按照指定的方式統(tǒng)計(jì)兩個(gè)時(shí)間之間的差。其語(yǔ)法結(jié)構(gòu)為：=Datedif(開始時(shí)間，結(jié)束時(shí)間，統(tǒng)計(jì)方式)。其中公式中用到的統(tǒng)計(jì)方式“y”指的是按“年”進(jìn)行計(jì)算。 四、計(jì)算退休年齡。 方法： 在目標(biāo)單元格中輸入公式：=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)。 解讀： 1、Edate函數(shù)的主要功能為：返回一串日期，指示起始日期之前或之后的月數(shù)。 2、此處的計(jì)算規(guī)則為：男工作60年退休，女50年退休。首先用Mod函數(shù)判斷性別，如果為“男”，則在出生日期的基礎(chǔ)上加上1*120+600=720個(gè)月，也就是60年。如果為“女”，則在出生日期的及出生加上0*120+600=600個(gè)月，也就是50年。 五、判斷身份證號(hào)是否重復(fù)。 1、一般方法（錯(cuò)誤方法）。 方法： 在目標(biāo)單元格中輸入公式：=IF(COUNTIF($C$3:$C$9,C3)>1,'重復(fù)','')。 分析： 1、從單元格中我們可以觀察出兩個(gè)身份證號(hào)并不重復(fù)，公式也沒有錯(cuò)誤，但為什么最后的判斷結(jié)果是“重復(fù)”呢？ 2、在Excel中，超過(guò)15位的數(shù)字，有效保留位數(shù)為15位，其余位數(shù)全部視為0處理。而單元格中的身份證號(hào)只有最后兩位不同，但在實(shí)際的處理中視為0，所以判斷結(jié)果為“重復(fù)”。 2、正確判斷。 方法： 在目標(biāo)單元格中輸入公式：=IF(COUNTIF($C$3:$C$9,C3&'*')>1,'重復(fù)','')。 解讀： 1、比較兩個(gè)公式，發(fā)現(xiàn)只是在C3的后面添加了*（星號(hào)）。但是得到了正確的結(jié)果。 2、其實(shí)在Excel中，*（星號(hào)）一般被稱為通配符，加上通配符的作用就是講當(dāng)前的數(shù)字內(nèi)容強(qiáng)制轉(zhuǎn)換為文本，然后進(jìn)行對(duì)比從而得到了正確的結(jié)果。 六、防止重復(fù)。 方法： 1、選中數(shù)據(jù)源，【數(shù)據(jù)】-【數(shù)據(jù)驗(yàn)證】。 2、選擇【允許】中的【自定義】，并在【公式】中輸入：=COUNTIF($C$3:$C$9,C3)=1。 3、單擊【輸入信息】和【出錯(cuò)警告】標(biāo)簽，分別錄入信息并【確定】。 4、測(cè)試有效性 結(jié)束語(yǔ)： 此文從提取出生年月、性別、計(jì)算年齡及退休年齡，如果防止身份證號(hào)重復(fù)等方面做了詳細(xì)的介紹，是一篇很不錯(cuò)的關(guān)于“身份證號(hào)”相關(guān)技巧的文章哦！ 如果各位親在學(xué)習(xí)的過(guò)程中遇到或發(fā)現(xiàn)任何困難或問題，歡迎在留言區(qū)留言討論哦！ |
|
|
