Excel表格中根據(jù)身份證號(hào)碼自動(dòng)填出生日期、計(jì)算年齡

1、Excel表格中根據(jù)身份證號(hào)碼自動(dòng)填出生日期、計(jì)算年齡18位身份證號(hào)碼轉(zhuǎn)換成出生日期的函數(shù)公式：如果E2中是身份證，在F2中求出出生日期，F(xiàn)2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2)自動(dòng)錄入男女：=IF(MOD(IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1),2)=0,"女","男")15/18位都可以的公式：轉(zhuǎn)換出生日期：=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#-00-00"),"19"&TEX

2、T(MID(e2,7,6),"#-00-00")自動(dòng)錄入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0," 女","男")計(jì)算年齡(新舊身份證號(hào)都可以)：=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",107-MIDB(E2,9,2),107-MIDB(E2,7,2)WPS表格提取身份證詳細(xì)信息前些天領(lǐng)導(dǎo)要求統(tǒng)計(jì)所有員工的性別、岀生日期、年齡等信息，并且要得

3、很急。而我們單位員工人數(shù)眾多，短時(shí)間內(nèi)統(tǒng)計(jì)相關(guān)信息并且輸入計(jì)算機(jī)幾乎是不太可能的。幸好在以前的一份金山表格中我們?cè)?jīng)統(tǒng)計(jì)有所有員工的身份證號(hào)碼，而身份證中正有我們所需要的性別、岀生日期、年齡等信息的。所以，干脆，還是直接在金山表格中從身份證號(hào)碼提取相關(guān)的信息吧。身份證號(hào)放在A2單元格以下的區(qū)域。我們需要從身份證號(hào)碼中提取性別、岀生日期、年齡等相關(guān)信息。由于現(xiàn)在使用的身份證有15位和18位兩種。所以，在提取相關(guān)信息時(shí)，首先應(yīng)該判斷身份證號(hào)碼的數(shù)字個(gè)數(shù)，然后再區(qū)別不同情況進(jìn)行相關(guān)處理。一、身份證號(hào)的位數(shù)判斷在B2單元格輸入如下公式“=LEN($A2”回車后即可得到 A2單元格身份證號(hào)碼的數(shù)字位數(shù)，

4、如圖1所示。LEN($A2)公式的含義是求出 A2單元格字符串中字符的個(gè)數(shù)。由于當(dāng)初身份證輸入時(shí)就是以文本形式輸入的，所以用此函數(shù)正可以很方便地求到身份證號(hào)碼的位數(shù)。總用身掛證號(hào)提瑕潔息一 et *B2*毘=LEN(JA2)A疔C1身粉證號(hào)碼數(shù)舟數(shù)識(shí)別數(shù):2*177081100131*1318 |4*710811001155156+*7科斗甘* g 39102415371 18*2 41J二、判斷男女性別第一步：提取性別識(shí)別數(shù)字。如果是18位身份證，那么倒數(shù)第二位就是性別判斷的識(shí)別數(shù)字，奇數(shù)為男，偶數(shù)則為女。而如果是15位身份證，那么倒數(shù)第一位則是性別識(shí)別數(shù)字，同樣奇

5、數(shù)為男，偶數(shù)為女。所以首先應(yīng)該提取這兩種身份證的性別識(shí)別數(shù)字。在C2單元格輸入公式“ =IF(B2=18,MID(A2,17,1),RIGHT(A2,1)，回車后，立刻可以得到相應(yīng)的識(shí)別數(shù)字了。如圖2所示。J用身餡證號(hào)»詔=1&2=18, HIDCA2,1L 1), RIGHT(A2,1BABE1身粉證號(hào)碼數(shù)字個(gè)數(shù)識(shí)別數(shù)羞性別出注日期HH42+*+4+197708110013IS1 1 o-Ju3=K+tl98901020026182aQ15151546*1956111210241827*+*198910241537183圖2 WPS中鍵入函數(shù)函數(shù)MID(A2,17,1)的作

6、用是從A2單元格字符串的第17個(gè)字開始截取1個(gè)字符，這正好是倒數(shù) 第二個(gè)字符；函數(shù) RIGHT(A2,1)的作用則是從A2單元格字符串的右側(cè)開始截取1個(gè)字符，也就是倒數(shù)第一個(gè)字符。至于IF函數(shù)，則進(jìn)行相應(yīng)的判斷。公式“=IF(B2=18,MID(A2,17,1),RIGHT(A2,1)”的作用是如果B2單元格等于18，那么從A2單元格字符串的第17位開始截取1個(gè)字符，否則從A2單元格字符串的右側(cè)截取1個(gè)字符”第二步：根據(jù)識(shí)別數(shù)字判斷男女性別，在D2單元格輸入公式“=IF(ISEVEN($C2)=TRUE,"女"，”男")”回車后就可以得到的結(jié)果了。如圖3所示。崎朋

7、身份麻號(hào)接取信息“ *ABC 1Df E1身份證號(hào)碼數(shù)字個(gè)數(shù)識(shí)別數(shù)字1性別/出生日期2羋和和羋1 7708110013181 |L31801020028152 :4710811001151男5*1=980602124154女6羋* 和羋 1 56111210241S2 .7羋卅卅電1 5910241537133 t圖3 WPS中鍵入函數(shù)判斷男、女性別此處函數(shù)ISEVEN($C2)的作用是判斷C2單元格的數(shù)字是否為偶數(shù)，如果是，則返回結(jié)果TRUE，否則返回結(jié)果 FALSE。因此，公式 “ =IF(ISEVEN($C2)=TRUE,"女","男")的含義就是

8、如果 C2單元格數(shù)字為偶數(shù)，那么在當(dāng)前單元格顯示女”，否則顯示c0E22L55q女271- .-l rf1-10-?.女乜用身悌證粵舉啟耳-H |噸T 飪 =IF (JB2n, HIDtJAi 7, S),氣9慚HD($隔爲(wèi)：If7m茁IE竝沆U2.人時(shí)站-VJIM$込It議*輯10仃竝$21 “旳話!兩 卑口盒2)F-%11 D仔總.玄2冷41 "0MI嘰$ 耳納)丿3 *+19390102002&卅舉*書戲創(chuàng)2!2419980&0?i3mr圖4 WPS中鍵入函數(shù)判斷出生日期圖5 WPS中鍵入函數(shù)判斷特殊日期T號(hào)碼固訓(xùn)血主日期個(gè)數(shù)識(shí)別數(shù)字岀生年月日芽|：19 了曲亦

9、*+419770811001319890102*710811001197108116林材林ig盹111丑0245 W鶉桝購(gòu)価1盜齊1帕廖n2B* *g的1024153T申村顯號(hào)碼銚*卄卄札幻1螞迤垃1T -.111勺加10昌*71(13111*«9£0«02£24*2 55111 £102 4*1985117H譏眸111561112翊剛：三,汀-；臨-篦lW13三、岀生日期提取第一步：提取數(shù)字型出生日期。在E2單元格輸入公式“ =IF($B2=18,MID($A2,7,8),"19"&MID($A2,7,6)并回車，

10、如圖4所示。這種形式的出生日期我們并不常用此處提取出來只是為了我們下一步計(jì)算年齡時(shí)要用，也是一個(gè)輔助列。"19"&MID($A2,7,6)的意思是把字符“19和MID($A2,7,6)截取到的6個(gè)字符合并在一起成為一個(gè)新的字符串。其它的，就不用再解釋了吧？四、公式計(jì)算年齡在G2單元格輸入公式“=YEAR(NOW()-LEFT(E2,4)”并回車，年齡計(jì)算就這么簡(jiǎn)單，用當(dāng)前的年份減去出生的年份就行了 。如圖6所示。函數(shù)YEAR(NOW()可以返回當(dāng)前的年數(shù) “2007,而LEFT(E2,4) 則從E2單元格的左側(cè)截取 4個(gè)字符，也就是出生年份。第二步：提取帶連接符的岀

11、生日期。在F2單元格輸入公式“ =IF($B2=18,MID($A2,7,4)&' -"&MID($A2,11,2)&"-"&MID($A2,13,2),"19"&MID($A2,7,2)&"-"&MID($A2,9,2)&"-"&MID($A2,11,2) 并回車，如圖5所示，可以得到我們常用的如“197708-11”的格式。此處用到的函數(shù)在前面已經(jīng)有過解釋，所以不再羅嗦了。我EAR（伽0）LEFT（鷗 4）日2男1S3C7511W總小111 : j r 疋” .；14 r lx££A 女圖6 WPS中用公式計(jì)算年齡3 些些和鴕鈾比舛。那ii *Jt*3!7108Ll 0012 * 19?T081l.001319710811ln-oa-u5+*+* 杓 g 06 021199S06026 襯齢科訶昵111210247 *+19831024153?殖證號(hào)碼戦字個(gè)縱識(shí)別數(shù)字性別出主日期197T0811197T-08-il19的01021585-01-02最后，選