|
一、在excel中如何從身份證號(hào)碼里提取出出生年月日及性別等 假如，A1是姓名，B1是身份證號(hào)碼，C1是出生年月 可以用以下公式求出 ＝IF(LEN(B2)=15,"19"&MID(B2,7,2)&"年"&MID(B2,9,2)&"月"&MID(B2,11,2)&"日",MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日") 在C2設(shè)置單元格格式為“日期”即可在C2中正確提取出其出生年月 2EXCEL從身份證號(hào)碼求出生年月日、性別及年齡公式2007-11-26 16:40EXCEL中用身份證號(hào)碼求出生年月日及年齡公式2007年11月21日 星期三 17:17一、分析身份證號(hào)碼 其實(shí)，身份證號(hào)碼與一個(gè)人的性別、出生年月、籍貫等信息是緊密相連的，無(wú)論是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位)是320521720807024，那么表示1972年8月7日出生，性別為女。如果能想辦法從這些身份證號(hào)碼中將上述個(gè)人信息提取出來(lái)，不僅快速簡(jiǎn)便，而且不容易出錯(cuò)，核對(duì)時(shí)也只需要對(duì)身份證號(hào)碼進(jìn)行檢查，肯定可以大大提高工作效率。 二、如何從在excel中身份證號(hào)碼里提取個(gè)人信息 這里，我們需要使用IF、LEN、MOD、 MID、DATE等函數(shù)從身份證號(hào)碼中提取個(gè)人信息。如圖1所示，其中員工的身份證號(hào)碼信息已輸入完畢(C列)，出生年月信息填寫(xiě)在D列，性別信息填寫(xiě)在B列。 1. 提取出生年月日信息 由于上交報(bào)表時(shí)只需要填寫(xiě)出生年月，不需要填寫(xiě)出生日期，因此這里我們只需要關(guān)心身份證號(hào)碼的相應(yīng)部位即可，即顯示為“720807”這樣的信息。在D2單元格中輸入公式=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(A 1,13,2))其中： LEN(A1)=15：檢查C2單元格中字符串的字符數(shù)目，本例的含義是檢查身份證號(hào)碼的長(zhǎng)度是否是15位。 MID(A1,7,4)：從C2單元格中字符串的第7位開(kāi)始提取四位數(shù)字，本例中表示提取15位身份證號(hào)碼的第7、8、9、10位數(shù)字。 MID(A1,9,4)：從C2單元格中字符串的第9位開(kāi)始提取四位數(shù)字，本例中表示提取18位身份證號(hào)碼的第9、10、11、12位數(shù)字。 IF(LEN(A1)=15,MID(A1,7,4),MID(A1,9,4))：IF是一個(gè)邏輯判斷函數(shù)，表示如果A1單元格是15位，則提取第7位開(kāi)始的四位數(shù)字，如果不是15位則提取自第9位開(kāi)始的四位數(shù)字。 ＆為連接符“”中的字符為原樣輸出 2. 提取性別信息 由于報(bào)表中各位員工的序號(hào)編排是按照上級(jí)核定的編制進(jìn)行的，因此不可能按照男、女固定的順序進(jìn)行編排，如果一個(gè)一個(gè)手工輸入的話，既麻煩又容易出錯(cuò) 例如性別信息統(tǒng)一在B列填寫(xiě)，可以在B2單元格中輸入公式“=IF(MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2 )=1,"男","女")”，其中： LEN(A1)=15：檢查身份證號(hào)碼的長(zhǎng)度是否是15位。 MID(A1,15,1)：如果身份證號(hào)碼的長(zhǎng)度是15位，那么提取第15位的數(shù)字。 MID(A1,17,1)：如果身份證號(hào)碼的長(zhǎng)度不是15位，即18位身份證號(hào)碼，那么應(yīng)該提取第17位的數(shù)字。 MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2)：用于得到給出數(shù)字除以指定數(shù)字后的余數(shù)，本例表示對(duì)提出來(lái)的數(shù)值除以2以后所得到的余數(shù)。 IF(MOD(IF(LEN(A1)=15,MID(C2,15,1),MID(A1,17,1)),2) =1,"男","女")：如果除以2以后的余數(shù)是1，那么B2單元格顯示為“男”，否則顯示為“女”。 回車(chē)確認(rèn) 3.提取年齡 =YEAR(TODAY())-VALUE(IF(LEN(A1)=15,19&MID(A1,7,2), MID(A1,7,4))) -------------------------------------------------- --------------------------------------------------- ----- Excel函數(shù)提取身份證數(shù)據(jù)公式 出生年月：（身份證為18位的） E2=MID(A2,7,4)&"年"&MID(A2,11,2)&"月"MID(A2,13,2)&"日"E2=MID（A2，7，8） 答案說(shuō)明： 1.Excel表中用身份證號(hào)碼中取其中的號(hào)碼用:MID(文本,開(kāi)始字符,所取字符數(shù)); 2.15位身份證號(hào)從第7位到第12位是出生年月日，年份用的是2位數(shù)。 18位身份證號(hào)從第7位到第14位是出生的年月日，年份用的是4位數(shù)。 從身份證號(hào)碼中提取出表示出生年、月、日的數(shù)字，用文本函數(shù)MID()可以達(dá)到目的。MID()——從指定位置開(kāi)始提取指定個(gè)數(shù)的字符（從左向右）。 對(duì)一個(gè)身份證號(hào)碼是15位或是18位進(jìn)行判斷，用邏輯判斷函數(shù)IF()和字符個(gè)數(shù)計(jì)算函數(shù)LEN()輔助使用可以完成。綜合上述分析，可以通過(guò)下述操作，完成形如1978-12-24樣式的出生年月日自動(dòng)提?。杭偃缟矸葑C號(hào)數(shù)據(jù)在C2單元格 =IF(LEN(C2)=15,MID(C2,7,2)&"-"&MID(C2,9,2)&"-"&MID (C2,11,2),MID(C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,1 3,2)) 根據(jù)身份證號(hào)碼（15位和18位通用）自動(dòng)提取性別的自編公式： 說(shuō)明：公式中的C2是身份證號(hào) 根據(jù)身份證號(hào)碼求性別： =IF(LEN(C2)=15,IF(MOD(VALUE(RIGHT(C2,3)),2)=0,"女","男"),IF(LEN(C2)=18,IF(MOD(VALUE(MID(C2,17,1)),2)=0,"女","男"),"身份證錯(cuò)")) 18位的還可以直接輸入公式提取性別：=IF（MOD（MID（C2，17，1），2）=0，"女","男"）根據(jù)身份證號(hào)碼求年齡： =IF(LEN(C2)=15,2007-VALUE(MID(C2,7,2)),if(LEN(C2)= 18,2007-VALUE(MID(C2,7,4)),"身份證錯(cuò)")) 4.Excel表中用Year\Month\Day函數(shù)取相應(yīng)的年月日數(shù)據(jù); 5．籍貫 公式一共使用了五個(gè)嵌套的IF函數(shù)，第一個(gè)IF函數(shù)中的邏輯判斷式“MID（C2，1，2）<="15"”用來(lái)判斷身份證歸屬地代碼是否在11到15之間。如果這個(gè)邏輯判斷式成立，那么公式就執(zhí)行參數(shù)“CHOOSE（MID（C2，1，2）-10，"北京"，"天津"，"河北"，"山西"，"內(nèi)蒙古"）”。其中“（MID（C2，1，2）”返回身份證歸屬地（省市區(qū)）的代碼（即身份證號(hào)碼的前兩位），如果（MID（C2，1，2）返回的結(jié)果是11（即北京市的代碼），那么“MID（C2，1，2）-10”返回供CHOOSE函數(shù)使用的索引號(hào)。當(dāng)“MID（C2，1，2）-10”的結(jié)果是“1”時(shí)，CHOOSE函數(shù)就會(huì)返回后面參數(shù)表中的身份證歸屬地“北京”。另外一個(gè)根據(jù)身份證提取出生年月很簡(jiǎn)單的一個(gè)公式： =TEXT(MID(a1,7,8),"0000-00-00") excel如何在身份證里提取出生年月和年齡？ 如何用Excel，從18位身份證號(hào)碼中提取出生日期要0000-00-00格式 根據(jù)身份證號(hào)碼求出生日期，=CONCATENATE(MID(E4,7,4),"/",MID(E4,11,2),"/",MID(E4,13,2)) 公式解釋?zhuān)篴. MID(E4,7,4)為在身份證號(hào)碼中獲取表示年份的數(shù)字的字符串 文章“如何從在excel中身份證號(hào)碼里提取需要的信息”的相關(guān)問(wèn)答集錦 以下是與本文標(biāo)簽 身份證號(hào)碼、 出生年月、 相關(guān)的幾篇文章，希望對(duì)您有所幫助： M跟我說(shuō)做身份證號(hào)碼查詢系統(tǒng)要根據(jù)輸入的身份證號(hào)碼得到出生日期和性別以減輕前臺(tái)MM錄入員工資料時(shí)的工作量，原來(lái)身份證號(hào)碼里面的信息大有乾坤，以18位的身份證來(lái)說(shuō)，前面六位代表了你戶籍所在地，第七位到第十四位代表了你的出生年月，第十五位到第十七為代表了你的性別（偶數(shù)為女，奇數(shù)為男），根據(jù)這一信息，我在系統(tǒng)開(kāi)發(fā)的錄入員工的身份證后控件焦點(diǎn)轉(zhuǎn)移時(shí)根據(jù)身份證號(hào)碼獲得生日和性別，用C#寫(xiě)的代碼如下： { MessageBox.Show(“身份證號(hào)碼不能為空！”);//身份證號(hào)碼不能為空，...... 知道身份證號(hào)碼可以查到具體地址么，知道名字出生年月年月。. 回答者：-四級(jí)2010-1-2123:02相關(guān)內(nèi)容知道她（他）名字和出生年月和地區(qū)，能查到她（他）的身份證號(hào)碼嗎？謝謝。92009-3-16知道對(duì)方香港身份證號(hào)碼，能夠查得對(duì)方出生年月以及地址信息嗎242009-1-28知道了姓名地址出生年月如何才能知道身份證號(hào)碼2008-9-19我想知道：一個(gè)人的身份證怎么由他的出生年月和他的家庭地址知道他的身份證號(hào)碼謝謝42008-11-8知道姓名，地址，出生年月，怎么查詢身份證號(hào)碼2010-1-20查看同主題問(wèn)題：其他回答共3條只能查詢到身份證上面...... |
|
|
