第一篇：在excel中巧妙運(yùn)用身份證號碼輸入身份證號如何做到自動顯示姓別_年齡及出生年月日

1、在excel中輸入身份證號如何做到自動顯示姓別,年齡及出生年月日

15位和18位的身份證號都合適的了。設(shè)內(nèi)容如下：

........A列......B列......C列......D列....1 身份證號 性別 出生年月 年齡

2（輸入身份證號）

算年齡的：=IF(OR(LEN(A2)=15,LEN(A2)=18),RIGHT(IF(IF(LEN(A2)=15,MID(A2,9,2),MID(A2,11,2))*100+IF(LEN(A2)=15,MID(A2,11,2),MID(A2,13,2))>MONTH(TODAY())*100+DAY(TODAY()),YEAR(TODAY())-IF(LEN(A2)=15,MID(A2,7,2),MID(A2,9,2))-1,YEAR(TODAY())-IF(LEN(A2)=15,MID(A2,7,2),MID(A2,9,2))),2),“證號錯誤”)

算性別的：=IF(LEN(A2)=15,(IF(OR(ABS(RIGHT(A2))=1,ABS(RIGHT(A2))=3,ABS(RIGHT(A2))=5,ABS(RIGHT(A2))=7,ABS(RIGHT(A2))=9),“男”,“女”)),(IF(OR(ABS(MID(A2,17,1))=1,ABS(MID(A2,17,1))=3,ABS(MID(A2,17,1))=5,ABS(MID(A2,17,1))=7,ABS(MID(A2,17,1))=9),“男”,“女”)))

算出生年月的：=DATE(IF(LEN(A2)=15,MID(A2,7,2),MID(A2,9,2)),IF(LEN(A2)=15,MID(A2,9,2),MID(A2,11,2)),IF(LEN(A2)=15,MID(A2,11,2),MID(A2,13,2)))

【身份證信息提取】

從身份證號碼中提取出生年月日

=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)+0 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)*1 =IF(A2<>“",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)顯示格式均為yyyy-m-d。（最簡單的公式，把單元格設(shè)置為日期格式）

=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))

顯示格式為yyyy-mm-dd。（如果要求為“1995/03/29”格式的話，將”-” 換成”/”即可）=IF(D4=”“,”“,IF(LEN(D4)=15,TEXT((”19“&MID(D4,7,6)),”0000年00月00日“),IF(LEN(D4)=18,TEXT(MID(D4,7,8),”0000年00月00日“))))顯示格式為yyyy年mm月dd日。（如果將公式中“0000年00月00日”改成“0000-00-00”,則顯示格式為yyyy-mm-dd）

=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),”19“&MID(A1:A2,7,6))顯示格式為yyyymmdd。

=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)+0 =IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2))=MID(A1,7,4)&”年“&MID(A1,11,2)&”月“&MID(A1,13,2)&”日“ =IF(A1<>”“,TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“))從身份證號碼中提取出性別

=IF(MOD(MID(A1,15,3),2),”男“,”女“)

（最簡單公式）=IF(MOD(RIGHT(LEFT(A1,17)),2),”男“,”女“)=IF(A2<>” ”,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),)=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,”男“,”女“)從身份證號碼中進(jìn)行年齡判斷

=IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),TODAY(),”Y”),)

=DATEDIF（A1，TODAY（），“Y”）

（以上公式會判斷是否已過生日而自動增減一歲）=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900 =YEAR(TODAY())-IF(LEN(A1)=15,”19“&MID(A1,7,2),MID(A1,7,4))=YEAR(TODAY())-VALUE(MID(B1,7,4))&”歲“ =YEAR(TODAY())-IF(MID(B1,18,1)=”“,CONCATENATE(”19“,MID(B1,7,2)),MID(B1,7,4))按身份證號號碼計(jì)算至今天年齡

=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“),TODAY(),”y“)以2006年10月31日為基準(zhǔn)日,按按身份證計(jì)算年齡(周歲)的公式

=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)*1,”2006-10-31“,”y“)按身份證號分男女年齡段

按身份證號分男女年齡段，身份證號在K列，年齡段在J列（身份證號為18位）男性16周歲以下為男性16周歲（含16周歲）以上至50周歲為男性50周歲（含50周歲）以上至60周歲為男性60周歲（含60周歲）以上為女性16周歲以下為女性16周歲（含16周歲）以上至45周歲為女性45周歲（含45周歲）以上至55周歲為女性55周歲（含55周歲）以上為=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),”y“),{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1)))=SUM(--(DATEDIF(MID(K1,7,4)&”/“&MID(K1,11,2)&”/“&MID(K1,13,2),TODAY(),”y“)>={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2)))

在網(wǎng)站上查到關(guān)于如何使用該函數(shù)的詳解，感覺蠻復(fù)雜的。關(guān)于VLOOKUP函數(shù)的用法

“Lookup”的漢語意思是“查找”，在Excel中與“Lookup”相關(guān)的函數(shù)有三個：VLOOKUP、HLOOKUO和LOOKUP。下面介紹VLOOKUP函數(shù)的用法。

一、功能

在表格的首列查找指定的數(shù)據(jù)，并返回指定的數(shù)據(jù)所在行中的指定列處的數(shù)據(jù)。

二、語法 標(biāo)準(zhǔn)格式：

VLOOKUP(lookup_value,table_array,col_index_num , range_lookup)

三、語法解釋

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以寫為：

VLOOKUP(需在第一列中查找的數(shù)據(jù),需要在其中查找數(shù)據(jù)的數(shù)據(jù)表,需返回某列值的列號,邏輯值True或False)1.Lookup_value為“需在數(shù)據(jù)表第一列中查找的數(shù)據(jù)”，可以是數(shù)值、文本字符串或引用。2.Table_array 為“需要在其中查找數(shù)據(jù)的數(shù)據(jù)表”，可以使用單元格區(qū)域或區(qū)域名稱等。

⑴如果 range_lookup 為 TRUE或省略，則 table_array 的第一列中的數(shù)值必須按升序排列，否則，函數(shù) VLOOKUP 不能返回正確的數(shù)值。

如果 range_lookup 為 FALSE，table_array 不必進(jìn)行排序。

⑵Table_array 的第一列中的數(shù)值可以為文本、數(shù)字或邏輯值。若為文本時，不區(qū)分文本的大小寫。3.Col_index_num 為table_array 中待返回的匹配值的列序號。Col_index_num 為 1 時，返回 table_array 第一列中的數(shù)值；

Col_index_num 為 2 時，返回 table_array 第二列中的數(shù)值，以此類推。如果Col_index_num 小于 1，函數(shù) VLOOKUP 返回錯誤值 #VALUE!；

如果Col_index_num 大于 table_array 的列數(shù)，函數(shù) VLOOKUP 返回錯誤值 #REF!。4.Range_lookup 為一邏輯值，指明函數(shù) VLOOKUP 返回時是精確匹配還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value 的最大數(shù)值；如果 range_value 為 FALSE，函數(shù) VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值 #N/A。

四、應(yīng)用例子

A B C D 1 編號

姓名

工資

科室 2 2005001 周杰倫 2870 辦公室 3 2005002 蕭亞軒 2750 人事科 4 2005006 鄭智化 2680 供應(yīng)科 5 2005010 屠洪剛 2980 銷售科 6 2005019 孫楠 2530 財(cái)務(wù)科 7 2005036 孟庭葦 2200 工 會

A列已排序（第四個參數(shù)缺省或用TRUE）VLOOKUP(2005001,A1:D7,2,TRUE)等于“周杰倫” VLOOKUP(2005001,A1:D7,3,TRUE)等于“2870” VLOOKUP(2005001,A1:D7,4,TRUE)等于“辦公室” VLOOKUP(2005019,A1:D7,2,TRUE)等于“孫楠” VLOOKUP(2005036,A1:D7,3,TRUE)等于“2200” VLOOKUP(2005036,A1:D7,4,TRUE)等于“工 會” VLOOKUP(2005036,A1:D7,4)

等于“工 會”

若A列沒有排序，要得出正確的結(jié)果，第四個參數(shù)必須用FALAE VLOOKUP(2005001,A1:D7,2,FALSE)等于“周杰倫” VLOOKUP(2005001,A1:D7,3,FALSE)等于“2870” VLOOKUP(2005001,A1:D7,4,FALSE)等于“辦公室” VLOOKUP(2005019,A1:D7,2,FALSE)等于“孫楠” VLOOKUP(2005036,A1:D7,3,FALSE)等于“2200” VLOOKUP(2005036,A1:D7,4,FALSE)等于“工 會”

五、關(guān)于TRUE和FALSE的應(yīng)用

先舉個例子，假如讓你在數(shù)萬條記錄的表格中查找給定編號的某個人，假如編號已按由小到大的順序排序，你會很輕松地找到這個人；假如編號沒有排序，你只好從上到下一條一條地查找，很費(fèi)事。

用VLOOKUP查找數(shù)據(jù)也是這樣，當(dāng)?shù)谝涣幸雅判?，第四個參數(shù)用TRUE（或確省），Excel會很輕松地找到數(shù)據(jù)，效率較高。當(dāng)?shù)谝涣袥]有排序，第四個參數(shù)用FALSE，Excel會從上到下一條一條地查找，效率較低。

筆者覺得，若要精確查找數(shù)據(jù)，由于計(jì)算機(jī)運(yùn)算速度很快，可省略排序操作，直接用第四個參數(shù)用FALSE即可。

提取相同人名字的身份證號碼

在A列中有所有人的姓名（有同名不同地址的人），B列中有身份證號碼，C列中有這些人的住址，D列中是部分人的姓名，現(xiàn)在要在E列表格內(nèi)填上D列中對應(yīng)人的身份證號碼（有七八千人）（A列中包含D列中的全部人名）

如無一同名同姓的話 E1輸入公式

=VLOOKUP(D1,A:C,2,)下拉即可

這個是如果有同姓名的，會出現(xiàn)首次出現(xiàn)人姓名的身份證號碼

如果沒有同名同姓，就可以在E2單元格中輸入=VLOOKUP(D2,A2:C3000,2,0)注意哦是放在E2哦，也是會出現(xiàn)首次姓名

excel中如何將表格中的身份證號碼后幾位以*號代替

雖然現(xiàn)在基本上都是新的身份證了（18位），但不排除可能出現(xiàn)的老身份證（15位）。因此，應(yīng)用下面這個方法。

設(shè)身份證數(shù)據(jù)在c列，d列為隱藏后4的值。在d2輸入：

=left(c2,len(c2)-4)&”****" 你需要有幾個*，就將公式中的4改成幾，然后在后面補(bǔ)幾個*就好了。