【PConline 技巧】身份證號中蘊(yùn)含著很多信息,比方說大家都知道的生日,便是其中一個(gè)最經(jīng)典的案例。其實(shí)除此之外,還有一些數(shù)據(jù)也是日常經(jīng)常會用到的。將這些信息快速提取出來,不但能讓工作更加高效,還能大大減少輸入出錯(cuò)的機(jī)率。 1. 提取生日 函數(shù):MID(從指定單元格中提取指定數(shù)量的字符) 身份證號中第7位~14位數(shù)字就是每個(gè)人的出生日期,而MID函數(shù)的最終原理,就是將這8位數(shù)字提取出來,再通過對應(yīng)的連字符連接起來。 用法:MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2) * MID(B2,7,4)提取的是出生年份(從B2單元格中的第7位數(shù)字開始,向后提取4位) * MID(B2,11,2)提取的是出生月份(從B2單元格的第11位數(shù)字開始,向后提取2位) * MID(B2,13,2)提取的是出生日期(從B2單元格的第13位數(shù)字開始,向后提取2位) * &”-”&是將提取到的數(shù)字用“-”連接起來,而非參與計(jì)算,最終可以得到“出生年-出生月-出生日”這樣一種我們需要的格式 2. 提取性別 函數(shù):MOD(判斷兩個(gè)單元格作除法運(yùn)算后的余數(shù)) 除了出生日期外,身份證號中的第17位數(shù)字是性別標(biāo)識符。其中奇數(shù)為男性、偶數(shù)為女性。和生日提取不同,想要實(shí)現(xiàn)性別提取,僅靠單一函數(shù)是無法實(shí)現(xiàn)的,需要與IF函數(shù)搭配使用。 用法:IF(MOD(MID(B2,15,3),2),"男","女") * MID(B2,15,3)提取的是身份證號中第15-17三位數(shù)字,即性別標(biāo)識位 * MOD(MID(B2,15,3),2)是將提取出來的數(shù)字與2取余,這樣可計(jì)算出MID函數(shù)提取出來的數(shù)字到底是奇數(shù)還是偶數(shù) * IF(MOD(MID(B2,15,3),2),”男”,”女”))根據(jù)MOD函數(shù)得出的結(jié)果,奇數(shù)顯示“男”,偶數(shù)顯示“女” 3. 計(jì)算年齡 函數(shù):YEAR(提取日期數(shù)據(jù)中年份部分) 年齡計(jì)算其實(shí)應(yīng)該算身份證計(jì)算的衍生公式了,同樣無法借助單一函數(shù)實(shí)現(xiàn)。一般操作前,先利用MID函數(shù)將生日提取出來,然后借助YEAR函數(shù)得到當(dāng)前年份與生日年份之間的差值即可。 用法:YEAR(NOW())-YEAR(MID(B2,7,4)) * NOW()提取當(dāng)前日期 * YEAR(NOW())將當(dāng)前日期中年份信息提取出來,比如”1970/01/01”提取出來后就是”1970” * YEAR(MID(B2,7,4)):首先借助MID函數(shù)提取身份證號中的出生年份,然后再用YEAR函數(shù)提取當(dāng)前年份,最后將兩個(gè)年份相減,結(jié)果即為該員工當(dāng)前年齡 注:最后再來個(gè)小貼士吧,有些網(wǎng)友可能會遇到這種情況,輸入18位身份證號時(shí)出現(xiàn)“x.xxxxxE+17”這樣的提示。這主要是因?yàn)閱卧窀袷皆O(shè)置有問題,解決的方法是提前將單元格格式改為“文本”,然后再次輸入數(shù)值即可。
|