Excel之一在Excel中根據(jù)身份證號(hào)碼利用公式自動(dòng)生成出生年月日

1、【Excel之一】在Excel中根據(jù)身份證號(hào)碼利用公式自動(dòng)生成出生年月日一、先給實(shí)例，不用搞懂原理假設(shè)：A1欄內(nèi)有身份證號(hào)碼，要在B1欄中自動(dòng)生成年月日，那么就在B1欄中輸入以下公式。（由于A1是假定的，可以任意替換，所以在下面的公式中用紅色醒目標(biāo)出）【情況1】公式如下：=TEXT(LEN(A1)=15)*19&MID(F2,7,6+(LEN(A1)=18)*2),"#-00-00") 顯示的效果是： 年年年年-月月-日日，如1984-04-28 【情況2】 公式如下： =IF(LEN(A1)=18,MID(A1,7,8),19&MID(A1,7,6) )

2、顯示的效果是： 年年年年月月日日，如19840428 【情況3】 公式如下： =IF(LEN(A1)=18,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,11,2)&"日","無(wú)效身份證號(hào)碼") 顯示的效果如下： XXX

3、X年XX月XX日，如1984年04月28日 【情況4】 公式如下： =IF(LEN(A1)=18,MID(A1,7,4)&"."&MID(A1,11,2),IF(LEN(A1)=15,MID(A1,7,2)&"."&MID(A1,9,2),"無(wú)效身份證號(hào)碼") 顯示的效果如下： 年年年年.月月，如1984.04   二、有興趣就拓展一下根據(jù)以上四種情況，在不需要理解公式含義的情況下，就可以派生出其他幾種不同的顯示格式出來(lái)?！厩闆r5】公式如下：=IF(LEN(A1)=18,MID(A1,7,4)

4、&"-"&MID(A1,11,2),IF(LEN(A1)=15,MID(A1,7,2)&"."&MID(A1,9,2),"無(wú)效身份證號(hào)碼") 顯示的效果如下： 年年年年-月月，如1984-04依次類推，基本上需要的格式都能改出來(lái)。 三、還有興趣就總結(jié)一下以上運(yùn)用到的函數(shù)1. Len函數(shù)（1）函數(shù)作用：用來(lái)對(duì)單元格內(nèi)容的長(zhǎng)度進(jìn)行測(cè)量。（2）使用語(yǔ)法：LEN(text)說(shuō)明：text 是要查找其長(zhǎng)度的文本?？崭駥⒆鳛樽址M(jìn)行計(jì)數(shù)。2. MID函數(shù)（1）函數(shù)作用：根據(jù)用戶自行指定的開(kāi)始位置和字符長(zhǎng)度

5、對(duì)某單元格進(jìn)行字符提取。（2）使用語(yǔ)法：MID(text,start_num,num_chars)說(shuō)明：Text 是包含要提取字符的文本字符串，可以直接輸入含有目標(biāo)文字的單元格名稱。Start_num 是文本中要提取的第一個(gè)字符的位置。文本中第一個(gè)字符的 start_num 為 1，以此類推。Num_chars 指定希望 MID 從文本中返回字符的個(gè)數(shù)。注意： 如果 start_num 大于文本長(zhǎng)度，則 MID 返回空文本 ()。 如果 start_num 小于文本長(zhǎng)度，但 start_num 加上 num_chars 超過(guò)了文本的長(zhǎng)度，則 MID 只返回至多直到文本末尾的字符。 如果 sta

6、rt_num 小于 1，則 MID 返回錯(cuò)誤值 #VALUE!。 如果 num_chars 是負(fù)數(shù)，則 MID 返回錯(cuò)誤值 #VALUE!。 3. IF函數(shù)（1）函數(shù)作用：根據(jù)指定的條件來(lái)計(jì)算結(jié)果為 TRUE 或 FALSE，返回不同的結(jié)果。可以使用 IF 對(duì)數(shù)值和公式執(zhí)行條件檢測(cè)。（2）使用語(yǔ)法：IF(logical_test,value_if_true,value_if_false)說(shuō)明：Logical_test   表示計(jì)算結(jié)果為 TRUE 或 FALSE 的任意值或表達(dá)式。Value_if_true   是 logical_test 為 TRUE

7、 時(shí)返回的值。Value_if_false   是 logical_test 為 FALSE 時(shí)返回的值。 =分割線=一、以上大部分內(nèi)容來(lái)源于網(wǎng)絡(luò)，并非原創(chuàng)，我只是按照自己的愛(ài)好做了整理。二、最近寫(xiě)“類型文”寫(xiě)煩了，想寫(xiě)寫(xiě)日志，卻囿于內(nèi)容限制，只好以此充數(shù)，騙騙自己。三、有可能寫(xiě)二，也可能永遠(yuǎn)只有一。反正不重要。說(shuō)明：公式中的A1是身份證號(hào) 身份證號(hào)碼為15位的,最后一位號(hào)碼能決定性別,奇數(shù)為男性,偶數(shù)為女性;身份證號(hào)碼為18位的,倒數(shù)第二位號(hào)碼能決定性別,奇數(shù)為男性,偶數(shù)為女性。 1、根據(jù)身份證號(hào)碼求性別： =IF(LEN(A1)=15,IF(MOD(VALUE

8、(RIGHT(A1,1),2)=0,"女","男"),IF(LEN(A1)=18,IF(MOD(VALUE(MID(A1,17,1),2)=0,"女","男"),"身份證錯(cuò)") 身份證號(hào)碼為15位的,第七、八位為出生年，第九、十位為出生月,第十一、十二位為出生日;身份證號(hào)碼為18位的,第七、八、九、十位為出生年，第十一、十二位為出生月,第十三、十四位為出生日。 2、根據(jù)身份證號(hào)碼求出生年月： =IF(LEN(A1)=15,CONCATENATE("19",MID(A1,7,2

9、),".",MID(A1,9,2),IF(LEN(A1)=18,CONCATENATE(MID(A1,7,4),".",MID(A1,11,2),"身份證錯(cuò)") 3、根據(jù)身份證號(hào)碼求年齡： =IF(LEN(A1)=15,year(now()-1900-VALUE(MID(A1,7,2),if(LEN(A1)=18,year(now()-VALUE(MID(A1,7,4),"身份證錯(cuò)")葉子  11:04:27=MID(A2,3,6)  葉子  11:04:

10、55考生號(hào)中截取其中的生源地代碼：=MID(A2,3,6)六然學(xué)童  11:05:06謝謝葉子  11:06:37生源地代碼與生源地名稱查找比對(duì)命令：VLOOKUP(B2,'生源地代碼表'!A:B,2,FALSE)用syszddm在“生源地代碼表”取SYSZDMC。葉子  11:06:47在excel使用哪個(gè)函數(shù)能在同一個(gè)工作簿下的一個(gè)工作表中引用另一個(gè)工作表中的數(shù)據(jù),以及函數(shù)ISNA,VLOOKUP的使用方法？直接寫(xiě)=sheet1！A1即可ISNA(value)判斷值是否為錯(cuò)誤值 #N/A（值不存在）。VLOOKUP

11、(lookup_value,table_array,col_index_num,range_lookup)在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。Lookup_value 為需要在數(shù)組第一列中查找的數(shù)值。Lookup_value 可以為數(shù)值、引用或文本字符串。Table_array 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。可以使用對(duì)區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫(kù)或列表。Col_index_num 為 table_array 中待返回的匹配值的列序號(hào)。Col_index_num 為 1 時(shí)，返回 table_array 第一列中的數(shù)值；col_index_num 為 2，返回 table_array 第二列中的數(shù)值，以此類推。如果 col_index_num 小于 1，函數(shù) VLOOKUP 返回錯(cuò)誤值值 #VALUE!；如果 col_index_num 大于 table_array 的列數(shù)，函數(shù) VLOOKUP 返回錯(cuò)誤值 #REF!。Range_lookup 為一邏輯值，指明函