Excel表格中身份證號碼提取出生年月、性別、年齡等的使用技巧.doc

Excel表中身份證號碼提取出生年月、性別、年齡的使用技巧方法一：1.Excel表中用身份證號碼中取其中的號碼用:MID(文本,開始字符,所取字符數(shù));2.15位身份證號從第7位到第12位是出生年月日，年份用的是2位數(shù)。18位身份證號從第7位到第14位是出生的年月日，年份用的是4位數(shù)。從身份證號碼中提取出表示出生年、月、日的數(shù)字，用文本函數(shù)MID()可以達到目的。MID()從指定位置開始提取指定個數(shù)的字符（從左向右）。對一個身份證號碼是15位或是18位進行判斷，用邏輯判斷函數(shù)IF()和字符個數(shù)計算函數(shù)LEN()輔助使用可以完成。綜合上述分析，可以通過下述操作，完成形如1978-12-24樣式的出生年月日自動提取：假如身份證號數(shù)據(jù)在A1單元格，在B1單元格中編輯公式=IF(LEN(A1)=15,MID(A1,7,2)&-&MID(A1,9,2)&-&MID(A1,11,2),MID(A1,7,4)&-&MID(A1,11,2)&-&MID(A1,13,2)回車確認即可。如果只要“年-月”格式，公式可以修改為=IF(LEN(A1)=15,MID(A1,7,2)&-&MID(A1,9,2),MID(A1,7,4)&-&MID(A1,11,2)3.這是根據(jù)身份證號碼（15位和18位通用）自動提取性別的自編公式，供需要的朋友參考：說明：公式中的B2是身份證號根據(jù)身份證號碼求性別：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3),2)=0,女,男),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1),2)=0,女,男),身份證錯)根據(jù)身份證號碼求年齡：=IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4),身份證錯)4.Excel表中用YearMonthDay函數(shù)取相應的年月日數(shù)據(jù);方法二：這是根據(jù)身份證號碼（15位和18位通用）自動提取性別和出生年月的自編公式，供需要的網(wǎng)友參考：說明：公式中的B2是身份證號1、根據(jù)身份證號碼求性別：=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3),2)=0,女,男),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3),2)=0,女,男),身份證錯)2、根據(jù)身份證號碼求出生年月：=IF(LEN(B2)=15,CONCATENATE(19,MID(B2,7,2),.,MID(B2,9,2),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),.,MID(B2,11,2),身份證錯)3、根據(jù)身份證號碼求年齡：=IF(LEN(B2)=15,year(now()-1900-VALUE(MID(B2,7,2),if(LEN(B2)=18,year(now()-VALUE(MID(B2,7,4),身份證錯)早期的身份證號碼為15位數(shù)字，現(xiàn)在使用的身份證號碼為18位數(shù)字，它們的含義如下：（1）15位：1-6位為地區(qū)代碼，7-8位為出生年份（2位），9-10位為出生月份，11-12位為出生日期，第13-15位為順序號，這3位奇數(shù)為男，偶數(shù)為女。（2）18位：1-6位為地區(qū)代碼，7-10位為出生年份（4位），11-12位為出生月份，13-14位為出生日期，第15-17位為順序號，同上，第18位為效驗位。在做員工檔案管理的工作時，有時希望通過身份證號碼，自動提取出各個員工的出生日期、性別、年齡等信息?，F(xiàn)在我們就通過幾個公式，來得到這些信息。1、出生日期=IF(LEN(A2)=15,CONCATENATE(19,MID(A2,7,2),/,MID(A2,9,2),/,MID(A2,11,2),CONCATENATE(MID(A2,7,4),/,MID(A2,11,2),/,MID(A2,13,2)LEN函數(shù)是檢測這個身份證號碼的數(shù)字位數(shù)，然后通過IF判斷函數(shù)對15位或18位的情況做出不同處理。CONCATENATE函數(shù)將提取出的分別代表“年”、“月”、“日”的信息，以及分隔符“/”等內容，合并成一個文本字符串。MID函數(shù)的做用是從身份證的某個位置開始，取出若干字符。比如MID(A2,7,2)，就是將身份證號碼（放在A2單元格中），從左邊數(shù)第七個字符開始，取出2個字符。2、性別=IF(LEN(A2)=15,IF(VALUE(RIGHT(A2,3)/2=INT(VALUE(RIGHT(A2,3)/2),女,男),IF(VALUE(MID(A2,15,3)/2=INT(VALUE(MID(A2,15,3)/2),女,男)由于代表性別的數(shù)字中，偶數(shù)為“女”，奇數(shù)為“男”，所以在這個公式中用VALUE函數(shù)將取出的字符串變成數(shù)字，再除以2，看是否能整除，因為奇數(shù)是不能整除的。這里的INT函數(shù)是“取整”的意思，把上面除2后的結果用它取整，讓IF函數(shù)判斷一下，取整前后的結果是否相同，相同為偶數(shù)，不相同為奇數(shù)。公式中的RIGHT函數(shù)是從字串的右邊（即字符串最后面）取若干符，注意與MID函數(shù)的區(qū)別。3、年齡=CONCATENATE(DATEDIF(B2,TODAY(),y),年,DATEDIF(B2,TODAY(),ym),個月)由于我們用上面的公式，將“出生日期”提取到B2單元格，所以這里的公式是通過操作B2單元格的數(shù)據(jù)來得到年齡的。函數(shù)DATEDIF是計算兩個日期或時間的差值，通過第三個參數(shù)來確定所要的結果，比如“y”會得到兩個日期相差的年；“ym”會得到除年外，所余的月數(shù)。TODAY()函數(shù)的作用是得到當前系統(tǒng)日期，即電腦上今天的日期。最終結果見下圖：如果從網(wǎng)上找到前6位數(shù)所代表的地區(qū)代碼列表，然后用VLOOKUP函數(shù)引用，就可以自動得到各員工的居住城市信息了。公式寫：=距離生日還有&IF(DATE(YEAR(TODAY(),MID(H2,11,2),MID(H2,13,2)-TODAY()0,DATE(YEAR(TODAY(),MID(H2