求EXCEL中如何從身份證號(hào)碼求出生年月日及年齡公式

PAGEPAGE1求EXCEL中如何從身份證號(hào)碼求出生年月日及年齡公式第一篇：求EXCEL中如何從身份證號(hào)碼求出生年月日及年齡公式求EXCEL中如何從身份證號(hào)碼求出生年月日及年齡公式提取身份證出生年月=“MID(B1,7,4)&”年“&MID(B1,11,2)&”月“&MID(B1,13,2)&”日“（看情況自己改寫）提取身份證年齡=YEAR(NOW())-YEAR(IF(LEN(B1)=18,DATE(MID(B1,7,4),MID(B1,11,2),MID(B1,13,2)),DATE(MID(B1,7,2),MID(B1,9,2),MID(B1,11,2))))提取身份證性別=IF(LEN(B1)=15,IF(MOD(MID(B1,15,1),2)=1,”男“,”女“),IF(MOD(MID(B1,17,1),2)=1,”男“,”女“))公式內(nèi)的“B1”代表的是輸入身份證號(hào)碼的單元格。___________________________詳·解____________________一、分析身份證號(hào)碼其實(shí)，身份證號(hào)碼與一個(gè)人的性別、出生年月、籍貫等信息是緊密相連的，無論是15位還是18位的身份證號(hào)碼，其中都保存了相關(guān)的個(gè)人信息。15位身份證號(hào)碼：第7、8位為出生年份(兩位數(shù))，第9、10位為出生月份，第11、12位代表出生日期，第15位代表性別，奇數(shù)為男，偶數(shù)為女。18位身份證號(hào)碼：第7、8、9、10位為出生年份(四位數(shù))，第11、第12位為出生月份，第13、14位代表出生日期，第17位代表性別，奇數(shù)為男，偶數(shù)為女。例如，某員工的身份證號(hào)碼(15位)是***，那么表示1972年8月7日出生，性別為女。如果能想辦法從這些身份證號(hào)碼中將上述個(gè)人信息提取出來，不僅快速簡(jiǎn)便，而且不容易出錯(cuò)，核對(duì)時(shí)也只需要對(duì)身份證號(hào)碼進(jìn)行檢查，肯定可以大大提高工作效率。二、提取個(gè)人信息這里，我們需要使用IF、LEN、MOD、MID、DATE等函數(shù)從身份證號(hào)碼中提取個(gè)人信息。如圖1所示，其中員工的身份證號(hào)碼信息已輸入完畢(C列)，出生年月信息填寫在D列，性別信息填寫在B列。1.提取出生年月信息由于上交報(bào)表時(shí)只需要填寫出生年月，不需要填寫出生日期，因此這里我們只需要關(guān)心身份證號(hào)碼的相應(yīng)部位即可，即顯示為“720XX這樣的信息。在D2單元格中輸入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：LEN(C2)=15：檢查C2單元格中字符串的字符數(shù)目，本例的含義是檢查身份證號(hào)碼的長(zhǎng)度是否是15位。MID(C2,7,4)：從C2單元格中字符串的第7位開始提取四位數(shù)字，本例中表示提取15位身份證號(hào)碼的第7、8、9、10位數(shù)字。MID(C2,9,4)：從C2單元格中字符串的第9位開始提取四位數(shù)字，本例中表示提取18位身份證號(hào)碼的第9、10、11、12位數(shù)字。IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一個(gè)邏輯判斷函數(shù)，表示如果C2單元格是15位，則提取第7位開始的四位數(shù)字，如果不是15位則提取自第9位開始的四位數(shù)字。如果需要顯示為“70年12月”這樣的格式，請(qǐng)使用DATE格式，并在“單元格格式→日期”中進(jìn)行設(shè)置。2.提取性別信息由于報(bào)表中各位員工的序號(hào)編排是按照上級(jí)核定的編制進(jìn)行的，因此不可能按照男、女固定的順序進(jìn)行編排，如果一個(gè)一個(gè)手工輸入的話，既麻煩又容易出錯(cuò)例如性別信息統(tǒng)一在B列填寫，可以在B2單元格中輸入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男“,”女“)”，其中：LEN(C2)=15：檢查身份證號(hào)碼的長(zhǎng)度是否是15位。MID(C2,15,1)：如果身份證號(hào)碼的長(zhǎng)度是15位，那么提取第15位的數(shù)字。MID(C2,17,1)：如果身份證號(hào)碼的長(zhǎng)度不是15位，即18位身份證號(hào)碼，那么應(yīng)該提取第17位的數(shù)字。MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到給出數(shù)字除以指定數(shù)字后的余數(shù)，本例表示對(duì)提出來的數(shù)值除以2以后所得到的余數(shù)。IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男“,”女")：如果除以2以后的余數(shù)是1，那么B2單元格顯示為“男”，否則顯示為“女”。第二篇：EXCEL中如何從身份證號(hào)碼求出生年月日及年齡公式EXCEL中如何從身份證號(hào)碼求出生年月日及年齡公式，性別信息方法？注：今天幫助一位網(wǎng)友處理一些表格的問題，也使自己重新又溫習(xí)了一下電子表格的相關(guān)內(nèi)容，在解決這個(gè)問題的過程中，借鑒了一位網(wǎng)友的文章，感覺里面的信息非常全面，可能許多朋友都有可能遇到，特轉(zhuǎn)載到自己的博客當(dāng)中。也對(duì)這位網(wǎng)友表示感謝。excel中如何根據(jù)身份證號(hào)，自動(dòng)求出出生年月？很好用！根據(jù)我的實(shí)際工作需要，又少作修改的方法：假如身份證號(hào)數(shù)據(jù)在A1單元格，在B1單元格中編輯公式=IF(LEN(A1)=15,“19”&MID(A1,7,2)&MID(A1,9,2)&MID(A1,11,2),MID(A1,7,4)&MID(A1,11,2)&MID(A1,13,2))這樣輸出格式就都是19821010這種格式了。excel中如何根據(jù)身份證號(hào)，自動(dòng)求出性別？假定身份證號(hào)在A1單元格，則在B1單元格中編輯公式=IF(AND(LEN(A1)=15,MOD(MID(A1,15,1)*1,2)=1),“男”,IF(AND(LEN(A1)=18,MOD(MID(A1,17,1)*1,2)=1),“男”,“女”))在excel是根據(jù)身份證自動(dòng)填充年齡？假設(shè)A列從A1開始是身份證號(hào)，在B列中進(jìn)行轉(zhuǎn)換，則在B1中輸入=YEAR(TODAY())-IF(LEN(A1)=15,“19”&MID(A1,7,2),MID(A1,7,4))，然后再向下復(fù)制即可。精確核算年齡=DATEDIF(TEXT(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”),“e-mm-dd”),TODAY(),“y”)注：A1為身份證號(hào)所在單元格公式解析：一、分析身份證號(hào)碼其實(shí)，身份證號(hào)碼與一個(gè)人的性別、出生年月、籍貫等信息是緊密相連的，無論是15位還是18位的身份證號(hào)碼，其中都保存了相關(guān)的個(gè)人信息。15位身份證號(hào)碼：第7、8位為出生年份(兩位數(shù))，第9、10位為出生月份，第11、12位代表出生日期，第15位代表性別，奇數(shù)為男，偶數(shù)為女。18位身份證號(hào)碼：第7、8、9、10位為出生年份(四位數(shù))，第11、第12位為出生月份，第13、14位代表出生日期，第17位代表性別，奇數(shù)為男，偶數(shù)為女。例如，某員工的身份證號(hào)碼(15位)是***，那么表示1972年8月7日出生，性別為女。如果能想辦法從這些身份證號(hào)碼中將上述個(gè)人信息提取出來，不僅快速簡(jiǎn)便，而且不容易出錯(cuò)，核對(duì)時(shí)也只需要對(duì)身份證號(hào)碼進(jìn)行檢查，肯定可以大大提高工作效率。二、提取個(gè)人信息這里，我們需要使用IF、LEN、MOD、MID、DATE等函數(shù)從身份證號(hào)碼中提取個(gè)人信息。如圖1所示，其中員工的身份證號(hào)碼信息已輸入完畢(C列)，出生年月信息填寫在D列，性別信息填寫在B列。1.提取出生年月信息由于上交報(bào)表時(shí)只需要填寫出生年月，不需要填寫出生日期，因此這里我們只需要關(guān)心身份證號(hào)碼的相應(yīng)部位即可，即顯示為“720XX這樣的信息。在D2單元格中輸入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：LEN(C2)=15：檢查C2單元格中字符串的字符數(shù)目，本例的含義是檢查身份證號(hào)碼的長(zhǎng)度是否是15位。MID(C2,7,4)：從C2單元格中字符串的第7位開始提取四位數(shù)字，本例中表示提取15位身份證號(hào)碼的第7、8、9、10位數(shù)字。MID(C2,9,4)：從C2單元格中字符串的第9位開始提取四位數(shù)字，本例中表示提取18位身份證號(hào)碼的第9、10、11、12位數(shù)字。IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一個(gè)邏輯判斷函數(shù)，表示如果C2單元格是15位，則提取第7位開始的四位數(shù)字，如果不是15位則提取自第9位開始的四位數(shù)字。如果需要顯示為“70年12月”這樣的格式，請(qǐng)使用DATE格式，并在“單元格格式→日期”中進(jìn)行設(shè)置。2.提取性別信息由于報(bào)表中各位員工的序號(hào)編排是按照上級(jí)核定的編制進(jìn)行的，因此不可能按照男、女固定的順序進(jìn)行編排，如果一個(gè)一個(gè)手工輸入的話，既麻煩又容易出錯(cuò)例如性別信息統(tǒng)一在B列填寫，可以在B2單元格中輸入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,“男”,“女”)”，其中：LEN(C2)=15：檢查身份證號(hào)碼的長(zhǎng)度是否是15位。MID(C2,15,1)：如果身份證號(hào)碼的長(zhǎng)度是15位，那么提取第15位的數(shù)字。MID(C2,17,1)：如果身份證號(hào)碼的長(zhǎng)度不是15位，即18位身份證號(hào)碼，那么應(yīng)該提取第17位的數(shù)字。MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到給出數(shù)字除以指定數(shù)字后的余數(shù)，本例表示對(duì)提出來的數(shù)值除以2以后所得到的余數(shù)。IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,“男”,“女”)：如果除以2以后的余是1，那么B2單元格顯示為“男”，否則顯示為“女”?；剀嚧_認(rèn)后，即可在B2單元格顯示正確的性別信息，接下來就是選中填充柄直接拖曳。在EXCEL中根據(jù)身份證號(hào)碼計(jì)算年齡、出生年月、性別1、根據(jù)身份證號(hào)計(jì)算年齡（周歲）=IF(A2<>“",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“),TODAY(),”y“),)注意：A2單元格為身份證號(hào)，且為文本格式。虛歲=20XX-RIGHT(LEFT(C3,10),4)2、根據(jù)身份證號(hào)計(jì)算出生年月日第一種，計(jì)算出來的格式為××年××月××日=IF(LEN(A2)=15,”19“&MID(A2,7,2)&”年“&MID(A2,9,2)&”月“&MID(A2,11,2)&”日“,MID(A2,7,4)&”年“&MID(A2,11,2)&”月“&MID(A2,13,2)&”日“)第二種，計(jì)算出來的格式為××年××月=IF(LEN(A2)=15,”19“&MID(A2,7,2)&”年“&MID(A2,9,2)&”月“,MID(A2,7,4)&”“&MID(A2,11,2)&”月“)3、根據(jù)身份證號(hào)計(jì)算性別=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,”男“,”女")年第三篇：身份證號(hào)碼提出生年月性別年齡方法公式一、提取出生日期如果我們要從一個(gè)人的身份證號(hào)碼中批量提取其出生年月日，并表示成“yyyy-mm-dd”形式，可以這樣做，假設(shè)身份證號(hào)碼在C列，在D列中輸入公式=IF(LEN(C6)=15,“19”&MID(C6,7,2)&“-”&MID(C6,9,2)&“-”&MID(C6,11,2),MID(C6,7,4)&“-”&MID(C6,11,2)&“-”&MID(C6,13,2))這個(gè)公式的含義就是，當(dāng)其檢查到C6單元格中的數(shù)據(jù)是15位的時(shí)，就顯示“19”&MID(C6,7,2)&“-”&MID(C6,9,2)&“-”&MID(C6,11,2)的計(jì)算結(jié)果，否則就顯示MID(C6,7,4)&“-”&MID(C6,11,2)&“-”&MID(C6,13,2)的計(jì)算結(jié)果。如：若C6單元格中是***，在D6單元格中計(jì)算出的結(jié)果是“1950-01-12”；若C6單元格中是***794，在D6單元格中計(jì)算出的結(jié)果是“1945-11-16”。二、提取性別在E6單元格輸入公式=IF(LEN(C6)=15,IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),“女”,“男”),IF(MID(C6,17,1)/2=INT(MID(C6,17,1)/2),“女”,“男”))這個(gè)公式的含義是如果C6單元格是一個(gè)15位數(shù)，就顯示IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),“女”,“男”)的計(jì)算結(jié)果；否則，顯示IF(MID(C6,17,1)/2=INT(MID(C6,17,1)/2),“女”,“男”))的計(jì)算結(jié)果。最后把D6和E6單元格的公式向下拉下來，在C6列輸入身份證號(hào)碼后，出生日期和性別可自動(dòng)輸入了，這樣就減少了用戶輸入數(shù)據(jù)工作量，提高了辦事效率！三、計(jì)算年齡excel中通過15位或18位身份證算年齡,精確到年假設(shè)C6為身份證號(hào)碼，保證身份證號(hào)碼輸入正確情況下，使用如下公式：15位用如下函數(shù)：=(TODAY()-DATE(MID(C6,7,2),MID(C6,9,2),MID(C6,11,2)))/36518位使用如何函數(shù)：=(TODAY()-DATE(MID(C6,7,4),MID(C6,11,2),MID(C6,13,2)))/365如果既含有15位又含有18位可以用如下綜合運(yùn)用。即增加增加判斷函數(shù)，可使用如下函數(shù)：=(TODAY()-IF(LEN(C6)=18,DATE(MID(C6,7,4),MID(C6,11,2),MID(C6,13,2)),DATE(MID(C6,7,2),MID(C6,9,2),MID(C6,11,2))))/365上述函數(shù)是根據(jù)天數(shù)算出來的年齡，如果根據(jù)年的話可用如下函數(shù)=DATEDIF(TEXT(((LEN(C6)=15)*19)&MID(C6,7,INT(LEN(C6)/2-1)),“#-00-00”),“20XX-12-31”,“Y”)四、根據(jù)出生年月計(jì)算年齡excel表格輸入了出生年月怎么自動(dòng)生成年齡，詳細(xì)方法如下輸入的出生年月日必須為19840501或1984.5.1這兩種格式設(shè)日期在A2單元格，那么在B2單元=DATEDIF(DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)),TODAY(),“y”)&“周歲”此函數(shù)計(jì)算，錄入出生年月日到至今的歲數(shù)。格內(nèi)輸入20XX/3/22隨手記第四篇：由身份證號(hào)碼求出生日期、年齡、性別excel中由身份證號(hào)求出生日期、性別、年齡（1）根據(jù)身份證號(hào)碼求性別：=IF(VALUE(RIGHT(B2,3))/2=INT(VALUE(RIGHT(B2,3))/2),“女”,“男”)（2）根據(jù)身份證號(hào)碼求出生日期：=CONCATENATE(MID(B2,7,4),“年”,MID(B2,11,2),“月”,MID(B2,13,2),“日”)（3）根據(jù)身份證號(hào)碼求出年齡：=CONCATENATE(DATEDIF(D2,TODAY(),“y”),“周歲”)或=DATEDIF(D3,TODAY(),“y”)1、身份證號(hào)碼相關(guān)知識(shí)在了解如何實(shí)現(xiàn)自動(dòng)從身份證號(hào)碼中提取出生年月、性別信息之前，首先需要了解身份證號(hào)碼所代表的含義。我們知道，當(dāng)今的身份證號(hào)碼有15/18位之分。早期簽發(fā)的身份證號(hào)碼是15位的，現(xiàn)在簽發(fā)的身份證由于年份的擴(kuò)展（由兩位變?yōu)樗奈唬┖湍┪布恿诵?yàn)碼，就成了18位。這兩種身份證號(hào)碼將在相當(dāng)長(zhǎng)的一段時(shí)期內(nèi)共存。兩種身份證號(hào)碼的含義如下：（1）15位的身份證號(hào)碼：1~6位為地區(qū)代碼，7~8位為出生年份(2位)，9~10位為出生月份，11~12位為出生日期，第13~15位為順序號(hào)，并能夠判斷性別，奇數(shù)為男，偶數(shù)為女。（2）18位的身份證號(hào)碼：1~6位為地區(qū)代碼，7~10位為出生年份(4位)，11~12位為出生月份，13~14位為出生日期，第15~17位為順序號(hào)，并能夠判斷性別，奇數(shù)為男，偶數(shù)為女。18位為效驗(yàn)位。2、應(yīng)用函數(shù)在此例中為了實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)提取，應(yīng)用了如下幾個(gè)Excel函數(shù)。（1）IF函數(shù)：根據(jù)邏輯表達(dá)式測(cè)試的結(jié)果，返回相應(yīng)的值。IF函數(shù)允許嵌套。語法形式為：IF（logical_test,value_if_true,value_if_false）（2）CONCATENATE：將若干個(gè)文字項(xiàng)合并至一個(gè)文字項(xiàng)中。語法形式為：CONCATENATE(text1,text2……)（3）MID：從文本字符串中指定的起始位置起，返回指定長(zhǎng)度的字符。語法形式為：MID(text,start_num,num_chars)（4）TODAY：返回計(jì)算機(jī)系統(tǒng)內(nèi)部的當(dāng)前日期。語法形式為：TODAY（）（5）DATEDIF：計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)或年數(shù)。語法形式為：DATEDIF(start_date,end_date,unit)（6）VALUE：將代表數(shù)字的文字串轉(zhuǎn)換成數(shù)字。語法形式為：VALUE(text)（7）RIGHT：根據(jù)所指定的字符數(shù)返回文本串中最后一個(gè)或多個(gè)字符。語法形式為：RIGHT(text,num_chars)（8）INT：返回實(shí)數(shù)舍入后的整數(shù)值。語法形式為：INT(number)4、公式寫法及解釋（以員工Andy為例說明）說明：為避免公式中過多的嵌套，這里的身份證號(hào)碼限定為15位的。如果您看懂了公式的話，可以進(jìn)行簡(jiǎn)單的修改即可適用于18位的身份證號(hào)碼，甚至可適用于15、18兩者并存的情況。（1）根據(jù)身份證號(hào)碼求性別=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)公式解釋：a.RIGHT(E4,3)用于求出身份證號(hào)碼中代表性別的數(shù)字，實(shí)際求得的為代表數(shù)字的字符串b.VALUE(RIGHT(E4,3)用于將上一步所得的代表數(shù)字的字符串轉(zhuǎn)換為數(shù)字c.VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2用于判斷這個(gè)身份證號(hào)碼是奇數(shù)還是偶數(shù)，當(dāng)然你也可以用Mod函數(shù)來做出判斷。d.=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)及如果上述公式判斷出這個(gè)號(hào)碼是偶數(shù)時(shí)，顯示“女”，否則，這個(gè)號(hào)碼是奇數(shù)的話，則返回“男”。（2）根據(jù)身份證號(hào)碼求出生日期=CONCATENATE(“19”,MID(E4,7,2),“/”,MID(E4,9,2),“/”,MID(E4,11,2))公式解釋：a.MID(E4,7,2)為在身份證號(hào)碼中獲取表示年份的數(shù)字的字符串b.MID(E4,9,2)為