第一篇：2代身份證出生日期和住址的數(shù)字是什么字體

2代身份證出生日期和住址的數(shù)字是什么字體？ 出生年月日 方正黑體簡(jiǎn)體 證號(hào)碼

OCR-B 10 BT 文字 華文細(xì)黑

_______________大小 自調(diào)！慎用(確定以及肯定)字符大?。盒彰?hào)碼（11點(diǎn)）其他（9點(diǎn)）字符間距（AV）：號(hào)碼（50）字符行距：住址（12點(diǎn)）——軟件 Photoshop

第二篇：身份證推算出生日期

excel中知道身份證號(hào)碼如何算出生年月日

比如身份證號(hào)碼A1

362***2345 出生年月 ： B1輸入

=MID(A1,7,8)

計(jì)算結(jié)果：19800909 年齡：=2014-MID(A1,7,4)，今年減出生年。

經(jīng)驗(yàn)證此方法正確

=MID(A2,7,4)&“-”&MID(A2,11,2)&“-”&MID(A2,13,2)

計(jì)算結(jié)果是：1980－09－09，公式必須粘貼使用才行，直接輸入不行，如果粘貼不成功，請(qǐng)確認(rèn)單元格格式為常規(guī)

正確輸入了身份證號(hào)碼。例如：***439，以文本的方法輸入，例如在A2單元格中輸入。將光標(biāo)定位在“出生日期”單元格(E2)中，然后在單元格中輸入函數(shù)公式

出生年月=MID(A2,7,4)&“-”&MID(A2,11,2)&“-”&MID(A2,13,2)=MID(E6,7,4)&“年”&MID(E6,11,2)&“月”&MID(E6,13,2)&“日”

性別：=IF(MOD(MID(E6,17,1),2)=0,“女”,“男”)年齡：=2011-MID(A1,7,4)，今年減出生年。這些公式要粘貼才正確

關(guān)于這個(gè)函數(shù)公式的具體說(shuō)明：MID函數(shù)用于從數(shù)據(jù)中間提取字符，它的格式是:MID（text,starl_num,num_chars）。

Text是指要提取字符的文本或單元格地址（上列公式中的D2單元格）。starl_num是指要提取的第一個(gè)字符的位置（上列公式中依次為7、11、13）。num_chars指定要由MID所提取的字符個(gè)數(shù)(上述公式中，提取年份為4，月份和日期為2）。

多個(gè)函數(shù)中的“&”起到的作用是將提取出的“年”“月”“日”信息合并到一起，“/”或“-” 分隔符則是在提取出的“年”“月”“日”數(shù)據(jù)之間添加的一個(gè)標(biāo)記，這樣的數(shù)據(jù)以后就可以作為日期類型進(jìn)行年齡計(jì)算。

提取生日：=MID(A1,7,8),假設(shè)身份證號(hào)在A列，從第7位數(shù)提取，共提取8位數(shù)字。年齡：=2011-MID(A1,7,4)，今年減出生年。

第三篇：身份證套出生日期

=IF(LEN(E35)=15,MID(E35,7,2)&“-”&MID(E35,9,2)&“-”&MID(E35,11,2),MID(E35,7,4)&“-”&MID(E35,11,2)&“-”&MID(E35,13,2))查到一個(gè)簡(jiǎn)單的，試試：=TEXT(MID(E3,7,8),“0000-00-00”)

假設(shè)A1單元格為出生日期（如：1980-5-25），計(jì)算周歲：

=datedif(A1,today(),“y”)

返回結(jié)果為31

假設(shè)A2為生日例如是1980-05-04，則B2計(jì)算年齡為=YEAR(TODAY())-YEAR(A2)

對(duì)于年齡本身的計(jì)算公式有多種，為了適應(yīng)不同要求，是否精確計(jì)算周歲等等。若精確計(jì)算則： =(TODAY()-A2)/365 若虛歲則=(TODAY()-A2)/365+1

=DATEDIF($A$1,NOW(),“md”)&“歲”&DATEDIF($A$1,NOW(),“ym”)&“個(gè)月零”&DATEDIF($A$1,NOW(),“md”)&“天” 說(shuō)明：在A1單元格中輸入你的出生日期，如1907－3－8 然后在其它任意單元格中輸入（或粘貼）上面的公式，結(jié)果為 “X歲Y月零Z天”

excel中用身份證號(hào)碼生成性別、出生日期、計(jì)算年齡 2010-09-26 12:40 excel中用身份證號(hào)碼生成性別、出生日期、計(jì)算年齡 從身份證號(hào)碼中自動(dòng)生成性別和生日

生成性別：（其中B2是身份證號(hào)碼所在列）

一性別雙擊性別所在列的第二行，然后輸入下面公式，然后按ENTER鍵；再利用下拉方式將公式復(fù)制到該列的其他行中即可

1=CHOOSE(MOD(IF(LEN(B2)=18,MID(B2,17,1),IF(LEN(B2)=15,RIGHT(B2,1),“")),2)+1,”女“,”男“)2=IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,”男“,”女“)3=IF(LEN(B2)=15,IF(MOD(MID(B2,15,1),2)=1,”男“,”女“),IF(MOD(MID(B2,17,1),2)=1,”男“,”女“))二出生日期提取出生日期：（其中B2是身份證號(hào)碼所在列）雙擊出生日期所在列的第二行，然后輸入下面公式，然后按ENTER鍵；再利用下拉方式將公式復(fù)制到該列的其他行中即可

=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))

三計(jì)算年齡：（其中C3是出生日期所在列）

雙擊年齡所在列的第二行，然后輸入下面公式，然后按ENTER鍵；再利用下拉方式將公式復(fù)制到該列的其他行中即可 =YEAR(NOW())-YEAR(C3)Excel自動(dòng)從身份證中提取生日性別

出處：天空軟件作者：佚名日期：2009-09-16

每年新入學(xué)的一年級(jí)學(xué)生，都需要向上級(jí)教育部門上報(bào)一份包含身份證號(hào)、出生年月等內(nèi)容的電子表格，以備建立全省統(tǒng)一的電子學(xué)籍檔案。數(shù)百個(gè)新生，就得輸入數(shù)百行相應(yīng)數(shù)據(jù)，這可不是個(gè)輕松活兒。有沒(méi)有什么辦法能減輕一下輸入工作量、提高一下效率呢？其實(shí)，我們只需在Excel2003中將學(xué)生的身份證號(hào)完整地輸入后，它就可以幫我們自動(dòng)填好出生日期和性別。

現(xiàn)在學(xué)生的身份證號(hào)已經(jīng)全部都是18位的新一代身份證了，里面的數(shù)字都是有規(guī)律的。前6位數(shù)字是戶籍所在地的代碼，7－14位就是出生日期。第17位“2”代表的是性別，偶數(shù)為女性，奇數(shù)為男性。我們要做的就是把其中的部分?jǐn)?shù)字想法“提取出來(lái)”。

STEp1，轉(zhuǎn)換身份證號(hào)碼格式

我們先將學(xué)生的身份證號(hào)完整地輸入到Excel2003表格中，這時(shí)默認(rèn)為“數(shù)字”格式（單元格內(nèi)顯示的是科學(xué)記數(shù)法的格式），需要更改一下數(shù)字格式。選中該列中的所有身份證號(hào)后，右擊鼠標(biāo)，選擇“設(shè)置單元格格式”。在彈出對(duì)話框中“數(shù)字”標(biāo)簽內(nèi)的“分類”設(shè)為“文本”，然后點(diǎn)擊確定。

STEP2，“提取出”出生日期

將光標(biāo)指針?lè)诺健俺錾掌凇绷械膯卧駜?nèi)，這里以C2單元格為例。然后輸入“=MID(B2,7,4)&”年“&MID(B2,11,2)&”月“&MID(B2,13,2)&”日“”（注意：外側(cè)的雙引號(hào)不用輸入，函數(shù)式中的引號(hào)和逗號(hào)等符號(hào)應(yīng)在英文狀態(tài)下輸入）?；剀嚭?，你會(huì)發(fā)現(xiàn)在C2單元格內(nèi)已經(jīng)出現(xiàn)了該學(xué)生的出生日期。然后，選中該單元格后拖動(dòng)填充柄，其它單元格內(nèi)就會(huì)出現(xiàn)相應(yīng)的出生日期。如圖1。

圖1 通過(guò)上述方法，系統(tǒng)自動(dòng)獲取了出生年月日信息

小提示：MID函數(shù)是EXCEL提供的一個(gè)“從字符串中提取部分字符”的函數(shù)命令，具體使用格式在EXCEL中輸入MID后會(huì)出現(xiàn)提示。STEP3，判斷性別“男女”

選中“性別”列的單元格，如D2。輸入“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),”女“,”男“)”（注意如上）后回車，該生“是男還是女”已經(jīng)乖乖地判斷出來(lái)了。拖動(dòng)填充柄讓其他學(xué)生的性別也自動(dòng)輸入。如圖2。

圖2 性別被自動(dòng)填入指定位置

這樣，通過(guò)兩個(gè)簡(jiǎn)單的函數(shù)，我們就可以讓EXCEL從身份證號(hào)中自動(dòng)提取出生日期和性別并填充到單元格內(nèi)，極大地減輕了我們的輸入工作量。最后效果如圖3。

圖3 用EXECL可以自動(dòng)識(shí)別并填入學(xué)生信息

根據(jù)身份證號(hào)碼讓Excel自動(dòng)輸入出生日期和性別 方法一：

15位身份證號(hào)：*** 輸出出生日期1979/06/05 =CONCATENATE(”19“,MID(E2,7,2),”/“,MID(E2,9,2),”/“,MID(E2,11,2))

公式解釋：

a.MID(E2,7,2)為在身份證號(hào)碼中獲取表示年份的數(shù)字的字符串

b.MID(E2,9,2)為在身份證號(hào)碼中獲取表示月份的數(shù)字的字符串 c.MID(E2,11,2)為在身份證號(hào)碼中獲取表示日期的數(shù)字的字符串

d.CONCATENATE(”19“,MID(E2,7,2),”/“,MID(E2,9,2),”/“,MID(E2,11,2))目的就是將多個(gè)字符串合并在一起顯示。

18位身份證號(hào)：***521 輸出出生日期1979/06/05 =CONCATENATE(MID(E2,7,4),”/“,MID(E2,11,2),”/“,MID(E2,13,2))

方法二：

15位身份證號(hào)：*** 出生日期790605 =IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))

18位身份證號(hào)：***521 出生日期790605 =IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))

公式解釋：

LEN(E2)=15：檢查E2單元格中字符串的字符數(shù)目，本例的含義是檢查身份證號(hào)碼的長(zhǎng)度是否是15位。

MID(E2,7,6)：從E2單元格中字符串的第7位開(kāi)始提取6位數(shù)字，本例中表示提取15位身份證號(hào)碼的第7、8、9、10、11、12位數(shù)字。

MID(E2,9,6)：從C2單元格中字符串的第9位開(kāi)始提取6位數(shù)字，本例中表示提取18位身份證號(hào)碼的第9、10、11、12、13、14位數(shù)字。=IF(LEN(E2)=15,MID(E2,7,6),MID(E2,9,6))：IF是一個(gè)邏輯判斷函數(shù)，表示如果額E2單元格是15位，則提取第7位開(kāi)始的6位數(shù)字，如果不是15位則提取自第9位開(kāi)始的6位數(shù)字。

根據(jù)身份證號(hào)碼自動(dòng)生成性別：

方法一：

在C列輸入身份證號(hào)，在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ì)提出來(lái)的數(shù)值除以2以后所得到的余數(shù)。

IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男“,”女“)：如果除以2以后的余數(shù)是1，那么B2單元格顯示為“男”，否則顯示為“女”。

15位身份證，看最后一位，奇男偶女；18位的，看第17位數(shù)，也是奇男偶女。

方法二：

如果你是想在Excel表格中，從輸入的身份證號(hào)碼內(nèi)讓系統(tǒng)自動(dòng)提取性別，可以輸入以下公式：

=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,”男“,”女“),IF(MOD(MID(C2,17,1),2)=1,”男“,”女"))

公式內(nèi)的“C2”代表的是輸入身份證號(hào)碼的單元格。

第四篇：EXCEL提取身份證出生日期

EXCEL提取身份證出生日期、性別、檢驗(yàn)身份證號(hào)碼正確性

中國(guó)居民身份證號(hào)碼是一組特征組合碼，原為１５位，現(xiàn)升級(jí)為１８位，其編碼規(guī)則為： １５位：６位數(shù)字常住戶口所在縣市的行政區(qū)劃代碼，６位數(shù)字出生日期代碼，３位數(shù)字順序碼。

１８位：６位數(shù)字常住戶口所在縣市的行政區(qū)劃代碼，８位數(shù)字出生日期代碼，３位數(shù)字順序碼和１位檢驗(yàn)碼。

其中３位數(shù)字順序碼，是為同一地址碼的同年同月同日出生人員編制的順序號(hào)，偶數(shù)的為女性，奇數(shù)的為男性。

１、提取籍貫地區(qū)的行政區(qū)劃代碼（A2為身份證號(hào)，下同）１５與１８位通用：=LEFT(A2,6)

如果有一個(gè)編碼和省份地區(qū)的對(duì)照表，可以用VLOOKUP函數(shù)來(lái)提取地區(qū)信息。２、提取出生日期信息

１５位：=--TEXT(19&MID(A2,7,6),“#-00-00”)１８位：=--TEXT(MID(A2,7,8),“#-00-00”)１５與１８位通用：=--TEXT(IF(LEN(A2)=15,19,“")&MID(A2,7,6+IF(LEN(A2)=18,2,0)),”#-00-00“)簡(jiǎn)化公式：=--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)(請(qǐng)將輸入公式的單元格格式設(shè)置為日期格式）３、提取性別信息

１５位：=IF(MOD(RIGHT(A2),2)=1,”男“,”女“)１８位：=IF(MOD(MID(A2),17,1)=1,”男“,”女“)

１５與１８位通用：=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,”男“,”女“)簡(jiǎn)化公式：=IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“)４、檢驗(yàn)身份證號(hào)碼的正確性 １８位身份證號(hào)碼的最后一位是檢驗(yàn)碼，它是根據(jù)身份證前17位數(shù)字依照規(guī)則計(jì)算出來(lái)的，其值０～９或X。一般情況只要有一位數(shù)字輸入錯(cuò)誤，依照規(guī)則計(jì)算后就會(huì)與第18位數(shù)不符。當(dāng)然不排除按錯(cuò)誤號(hào)碼計(jì)算后恰好與檢驗(yàn)碼相符的情況，但這種情況出現(xiàn)的可能性較低。因此，對(duì)１８位號(hào)碼的驗(yàn)證采用如下公式：

=MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2,1)

對(duì)于１５位身份證，由于沒(méi)有檢驗(yàn)碼，我們只能簡(jiǎn)單地去判斷出生日期代碼是否是一個(gè)有效的日期，避免輸入一些像“731302”或“980230“等這樣不存在的日期。=ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))綜合１５位和１８位后的通用公式為：

=IF(LEN(A2)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))))

由于目前１５位身份證號(hào)碼已經(jīng)很少了，如果對(duì)１５位的號(hào)碼不需要作進(jìn)一步的判斷，則公式可以簡(jiǎn)化成：

=IF(LEN(A2)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2),LEN(A2)=15)

將上面的公式放到B2單元格，如果結(jié)果為TRUE，則身份證號(hào)是正確的，結(jié)果為FALSE則是錯(cuò)誤的。

你也可以將上述公式放在數(shù)據(jù)有效性中，防止錄入錯(cuò)誤的身份證號(hào)。操作方法:選擇需要輸入身份證的全部單元格區(qū)域,比如A2:A10,點(diǎn)菜單”數(shù)據(jù)“-”有效性“,在”允許“的下拉框中選擇”自定義“,在”公式“輸入上面的15位和18位通用公式,確定以后即可。注意:公式里的”A2“是你剛才選定要輸入身份證的單元格區(qū)域的第一個(gè)單元格,如果你是要在C3:C20輸入身份證號(hào),則將公式里的”A2“改為”C3“。另外,你也可以先設(shè)置好某單個(gè)單元格的數(shù)據(jù)有效性(這時(shí)公式的A2改為選定的單元格),再用格式刷將其格式刷到其他需要相同設(shè)置的單元格。

５、１５位升為１８位

=IF(LEN(A2)=15,REPLACE(A2,7，19)&MID(”10X98765432“,MOD(SUMPRODUCT(MID(REPLACE(A2,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1),A2)

６、１８位轉(zhuǎn)換為１５位

=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)

7、示例

表中公式:

B2 =IF(LEN(A2)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))))

C2 =IF(A2<>”“,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)

D2 =IF(A2<>”“,IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“),)

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

F2 =IF(A2<>”“,VLOOKUP(LEFT(A2,2),地區(qū)表!A:D,2,),)

H2 =IF(LEN(A2)=15,REPLACE(A2,7，19)&MID(”10X98765432“,MOD(SUMPRODUCT(MID(REPLACE(A2,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1),A2)

I2 =IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)

把應(yīng)聘者或員工的個(gè)人信息錄入Excel，確保信息真實(shí)可信是必須解決的問(wèn)題。一．?dāng)?shù)據(jù)錄入快又準(zhǔn)

負(fù)責(zé)錄入的個(gè)人信息內(nèi)容如圖1所示，除了“序號(hào)”、“姓名”和“身份證號(hào)碼”以外，其余信息設(shè)計(jì)公式從“身份證號(hào)碼”中“挖掘”。

1．別讓數(shù)據(jù)變“亂”

“身份證號(hào)碼”要用“文本”格式。實(shí)現(xiàn)這一點(diǎn)的第一種方法是選中D列右擊鼠標(biāo)，選擇快捷菜單中的“設(shè)置單元格格式”，打開(kāi)對(duì)話框的“數(shù)字”選項(xiàng)卡選中，選中“分類”下的“文本”然后“確定”即可。第二種方法是在輸入的身份證號(hào)碼前加一個(gè)單引號(hào)，Excel就可以把輸入的數(shù)字變?yōu)椤拔谋尽绷?。第三種方法是選中D列，單擊“格式”菜單下的“單元格”命令打開(kāi)對(duì)話框，按如圖1所示選中“分類”下的“自定義”。然后在“類型”框中輸入一個(gè)“@”再“確定”即可。

2．錄入校驗(yàn) 錯(cuò)誤靠邊

第一步選中存放身份證號(hào)碼的數(shù)據(jù)區(qū)域（例如“D2：D800”），單擊Excel“數(shù)據(jù)”菜單下的“有效性”命令，打開(kāi)“數(shù)據(jù)有效性”對(duì)話框的“設(shè)置”選項(xiàng)卡。在“允許”下拉列表中選擇“自定義”，接著在如圖2所示“公式”框中輸入“=COUNTIF（D:D，D2）=1”。

第二步打開(kāi)“出錯(cuò)警告”選項(xiàng)卡，在“標(biāo)題”框內(nèi)輸入“數(shù)據(jù)重復(fù)”，并按如圖3所示輸入更詳細(xì)的警告信息，單擊“確定”按鈕將打開(kāi)的對(duì)話框關(guān)閉。當(dāng)然，這一步是可選的，使用時(shí)可以根據(jù)具體情況取舍。

此后只要在當(dāng)前單元格中輸入了重復(fù)數(shù)據(jù)，Excel就會(huì)彈出“數(shù)據(jù)重復(fù)”對(duì)話框告知小劉，并拒絕接受已經(jīng)輸入的重復(fù)數(shù)據(jù)。

除了防止錄入身份證號(hào)碼出現(xiàn)重復(fù)以外，還要防止小張輸入的號(hào)碼長(zhǎng)度不足15位或18位。接下來(lái)的第三步仍然是選中錄入身份證號(hào)碼的數(shù)據(jù)區(qū)域（例如“D2：D80”），單擊“格式”菜單下的“條件格式”命令打開(kāi)如圖4所示對(duì)話框，在“條件一”下拉列表中選擇“公式”，然后在中間的框內(nèi)輸入公式“=IF（LEN（D10）<>15，LEN（D10）<>18）”。

第四步單擊如圖4中的“格式”按鈕打開(kāi)對(duì)話框，在“字體”選項(xiàng)卡中選擇合適的顏色或刪除線等。之后如果D列中輸入的數(shù)據(jù)長(zhǎng)度不是15位或18位，其字體就會(huì)顯示前面選擇的顏色（例如紅色）。

3．錄后檢查 萬(wàn)無(wú)一失

假如上面的操作執(zhí)行前已經(jīng)錄入了部分?jǐn)?shù)據(jù)，那么有沒(méi)有辦法檢查錄入的身份證號(hào)碼是否重復(fù)？可以設(shè)計(jì)一個(gè)帶有公式的“條件格式”，圓滿解決問(wèn)題。

操作的第一步是選中如圖1中的D2單元格，單擊“格式”菜單中的“條件格式”命令，打開(kāi)如圖5所示對(duì)話框。在“條件1”下拉列表選擇“公式”，然后在右邊的輸入框中輸入公式“=COUNTIF（$D:$D，D2）>1”。它的用途是計(jì)算D列單元格中的數(shù)據(jù)是否與D2相同，再進(jìn)行比較以確定這個(gè)結(jié)果是否大于1（為“真”）。如果計(jì)算結(jié)果大于1（即存在相同的身份證號(hào)碼），就應(yīng)用右邊設(shè)置的條件格式，否則保持單元格的格式不變。

第二步是設(shè)置比較結(jié)果為“真”時(shí)應(yīng)用的條件格式，方法是單擊“格式”按鈕打開(kāi)如圖6所示對(duì)話框，在“顏色”下拉列表選中條件為“真”時(shí)顯示的字體顏色（例如紅色）。也可以根據(jù)需要選擇其它字形或選中“刪除線”，連續(xù)兩次單擊“確定”按鈕將打開(kāi)的對(duì)話框關(guān)閉。

第三步是將D2單元格中的條件格式應(yīng)用于D列的其它單元格，方法是選中D2單元格單擊工具欄的“復(fù)制”按鈕。再選中D列中需要應(yīng)用條件格式的區(qū)域（例如D3：D80區(qū)域），單擊“編輯”菜單中的“選擇性粘貼”命令，打開(kāi)對(duì)話框選中“格式”單擊“確定”，那么D列中存在的重復(fù)數(shù)據(jù)就會(huì)顯示前面設(shè)置的條件格式，例如用紅色帶刪除線的字體身份證號(hào)碼。

如果只想看出D列中重復(fù)錄入的身份證號(hào)碼，那么應(yīng)當(dāng)怎樣操作？可以將如圖5所示對(duì)話框中的公式修改為“=COUNTIF（$D$2:$D2，D2）>1”。這個(gè)公式中對(duì)數(shù)據(jù)區(qū)域結(jié)束單元格（即“$D2”）的引用會(huì)隨它向下復(fù)制的過(guò)程而變化，例如格式復(fù)制到D3單元格后公式變?yōu)椤?COUNTIF（$D$2:$D3，D3）>1”。因此查找是否存在重復(fù)數(shù)據(jù)時(shí)，公式進(jìn)行比較的范圍是從當(dāng)前單元格向上直到數(shù)據(jù)區(qū)域第一個(gè)單元格（即“$D2”），所以比較的結(jié)果只對(duì)第一個(gè)以后的相同名稱的數(shù)據(jù)應(yīng)用條件格式。

二．隱藏信息充分“挖掘”

將姓名和身份證號(hào)碼輸入如圖1所示的工作表以后，設(shè)計(jì)的公式馬上從身份證號(hào)碼中“挖掘”出了信息。其基本原理是： 1．性別

根據(jù)現(xiàn)行居民身份證號(hào)碼的編碼規(guī)定，18位身份證編碼的1~6位為歸屬地（公民第一次申領(lǐng)身份證時(shí)常住戶口所在地的行政區(qū)劃，下同）代碼，7~10位為4位的出生年份，11~12位為出生月份，13~14位為出生日期，17位（也就是倒數(shù)第二位）為性別（奇數(shù)為男，偶數(shù)為女），18位（也就是最后一位）則是校驗(yàn)位。而早期使用的是15位的身份證編碼，它的1~6位為歸屬地代碼，7~8位是2位的出生年份，9~10位為出生月份，11~12位為出生日期，15位（也就是最后一位）是性別（奇數(shù)為男，偶數(shù)為女）。

為了適應(yīng)上述情況，性別計(jì)算公式要能夠適應(yīng)兩種身份證號(hào)碼的，使用時(shí)只須在C2單元格輸入“=IF（LEN（D3）=15，IF（MOD（MID（D3，15，1），2）=1，”男“，”女“），IF（MOD（MID（D3，17，1），2）=1，”男“，”女“））”?；剀嚰纯傻玫紻2單元格中存儲(chǔ)的身份證號(hào)碼的性別，而后只要把公式復(fù)制（選中D2單元格，鼠標(biāo)指向單元格右下角然后向下拖動(dòng)）到D3、D4等單元格，即可“挖掘”出其他身份證號(hào)碼中的“性別”。

上述公式是怎樣的工作的？該公式由三個(gè)IF函數(shù)構(gòu)成，其中“IF（MOD（MID（D2，15，1），2）=1，”男“，”女“）”和“IF（MOD（MID（D2，17，1），2）=1，”男“，”女“）”作為第一個(gè)函數(shù)的參數(shù)。公式中的“LEN（D2）=15”是一個(gè)邏輯判斷語(yǔ)句，LEN函數(shù)提取D2、D3等單元格中的字符長(zhǎng)度，如果該字符的長(zhǎng)度等于15，則執(zhí)行參數(shù)中的第一個(gè)IF函數(shù)，否則就執(zhí)行第二個(gè)IF函數(shù)。

在參數(shù)IF（MOD（MID（D2，15，1），2）=1，”男“，”女“）中。MID函數(shù)從D2的指定位置（第15位）提取1個(gè)字符，而MOD函數(shù)將該字符與2相除，獲取兩者的余數(shù)。如果兩者能夠除盡，說(shuō)明提取出來(lái)的字符是0（否則就是1）。邏輯條件MOD（MID（D2，15，1），2）=1不成立，這時(shí)就會(huì)在D2單元格中填入”女“，反之則會(huì)填入”男“。

如果LEN函數(shù)提取的D2等單元格中的字符長(zhǎng)度不等于15，則會(huì)執(zhí)行第2個(gè)IF函數(shù)。除了MID函數(shù)從D2的指定位置（第17位，即倒數(shù)第2位）提取1個(gè)字符以外，其它運(yùn)算過(guò)程與上面的介紹相同。

2．生日

仔細(xì)看看E2單元格中的公式“=IF（LEN（D2）=15，CONCATENATE（”19“，MID（D2，7，2），”年“，MID（D2，9，2），”月“，MID（D2，11，2），”日“），CONCATENATE（MID（D2，7，4），”年“，MID（D2，11，2），”月“，MID（D2，13，2），”日“））”

上面這個(gè)公式中的“LEN（D2）=15”仍然是邏輯判斷語(yǔ)句，它可以判斷身份證號(hào)碼是15位的還是18位，從而調(diào)用相應(yīng)的計(jì)算語(yǔ)句。對(duì)15位的身份證號(hào)碼來(lái)說(shuō)，左起第7至12個(gè)字符表示出生年月日，此時(shí)可以使用MID函數(shù)從身份證號(hào)碼的特定位置，分別提取出生年、月、日。然后用CONCATENATE函數(shù)將提取出來(lái)的文字合并起來(lái)，就能得到對(duì)應(yīng)的出生年月日。公式中”19“是針對(duì)早期身份證號(hào)碼中存在2000年問(wèn)題設(shè)計(jì)的，它可以在計(jì)算出來(lái)的出生年份前加上19。對(duì)18位的身份證號(hào)碼的計(jì)算思路相同，只是它不存在2000年問(wèn)題，公式中不用給計(jì)算出來(lái)的出生年份前加上19。

3．年齡

出生日期計(jì)算出來(lái)以后很容易得到“當(dāng)前年齡”，在G2單元格中輸入公式“=YEAR（TODAY（））-YEAR（F2）”。由于F2單元格中存儲(chǔ)著上面計(jì)算出來(lái)“出生日期”（例如“1982年03月21日”），若TODAY（）函數(shù)返回系統(tǒng)當(dāng)前日期為“2006年3月1日”，那么G2單元格中計(jì)算出來(lái)的年齡就是24歲。

上述公式計(jì)算出來(lái)的是“虛歲”，即每過(guò)一個(gè)元旦，年齡就增加一歲。“難題”：計(jì)算“實(shí)歲”，即生日過(guò)后滿12個(gè)月，年齡再增加一歲的公式怎樣設(shè)計(jì)？可以在H2單元格輸入“=TRUNC（（G2-TODAY（））/365）”。公式中的“G2-TODAY（）”計(jì)算系統(tǒng)當(dāng)前時(shí)間與“出生日期”相差的天數(shù)，“（G2-TODAY（））/365）”計(jì)算出兩個(gè)日期之間相差的年數(shù)（小數(shù)）。最后使用TRUNC去掉結(jié)果的小數(shù)部分，得到兩個(gè)日期之間相差的整數(shù)（即“實(shí)歲”）。

4．籍貫

從身份證號(hào)碼中“挖掘”籍貫信息是比較復(fù)雜的一項(xiàng)工作，身份證號(hào)碼的第l至6位數(shù)是歸屬地代碼，其中前兩位是省、直轄市或自治區(qū)的代碼。例如“北京市居民身份證”的前兩位是“11”，“新疆維吾爾自治區(qū)居民身份證”的前兩位是“65”等。出于保留資源等方面的考慮，各省、直轄市或自治區(qū)居民身份證的歸屬地代碼不是連續(xù)的，例如“北京”、“天津”、“河北”、“山西”和“內(nèi)蒙古”五個(gè)省區(qū)市的歸屬地代碼是從11到15，而“遼寧”、“吉林”、“黑龍江”三個(gè)省的歸屬地代碼卻是從21到23。因此小張?jiān)O(shè)計(jì)的“挖掘”籍貫信息的公式就顯得比較長(zhǎng)了，使用時(shí)首先在E2單元格輸入下面的公式，回車以后即可得到D2單元格中存儲(chǔ)的歸屬地信息。再將公式復(fù)制到E3、E4等單元格，即可得到對(duì)應(yīng)D3、D4等單元格的身分證歸屬地（籍貫）信息。

=IF（MID（D2，1，2）<=”15“，CHOOSE（MID（D2，1，2）-10，”北京“，”天津“，”河北“，”山西“，”內(nèi)蒙古“），IF（MID（D2，1，2）<=”23“，CHOOSE（MID（D2，1，2）-20，”遼寧“，”吉林“，”黑龍江“），IF（MID（D2，1，2）<=”37“，CHOOSE（MID（D2，1，2）-30，”上?！?，”江蘇“，”浙江“，”安徽“，”福建“，”江西“，”山東“），IF（MID（D2，1，2）<=”46“，CHOOSE（MID（D2，1，2）-40，”河南“，”湖北“，”湖南“，”廣東“，”廣西“，”海南“），IF（MID（D2，1，2）<=”54“，CHOOSE（MID（D2，1，2）-49，”重慶“，”四川“，”貴州“，”云南“，”西藏“），CHOOSE（MID（D2，1，2）-60，”陜西“，”甘肅“，”青?！?，”寧夏“，”新疆“））））））

上面這個(gè)公式的結(jié)構(gòu)比較復(fù)雜，公式一共使用了五個(gè)嵌套的IF函數(shù)，第一個(gè)IF函數(shù)中的邏輯判斷式“MID（D2，1，2）<=”15“”用來(lái)判斷身份證歸屬地代碼是否在11到15之間。如果這個(gè)邏輯判斷式成立，那么公式就執(zhí)行參數(shù)“CHOOSE（MID（D2，1，2）-10，”北京“，”天津“，”河北“，”山西“，”內(nèi)蒙古“）”。其中“（MID（D2，1，2）”返回身份證歸屬地（省市區(qū)）的代碼（即身份證號(hào)碼的前兩位），如果（MID（D2，1，2）返回的結(jié)果是11（即北京市的代碼），那么“MID（D2，1，2）-10”返回供CHOOSE函數(shù)使用的索引號(hào)。當(dāng)“MID（D2，1，2）-10”的結(jié)果是“1”時(shí)，CHOOSE函數(shù)就會(huì)返回后面參數(shù)表中的身份證歸屬地“北京”。如果第一個(gè)IF函數(shù)中的邏輯判斷式“MID（D2，1，2）<=”15“”不成立，公式就執(zhí)行的第二個(gè)IF函數(shù)，它的結(jié)構(gòu)和第一個(gè)IF函數(shù)完全相同。但是邏輯判斷式“MID（D2，1，2）<=”15“”中的條件必須修改，由于“遼寧”、“吉林”、“黑龍江”三個(gè)省的歸屬地代碼的最大值是23，所以這個(gè)邏輯判斷式改為“MID（D2，1，2）<=”23“”即可。當(dāng)這個(gè)邏輯判斷式得出“真”或“假”的結(jié)果以后，第二個(gè)IF函數(shù)要么執(zhí)行“CHOOSE（MID（D2，1，2）-20，”遼寧“，”吉林“，”黑龍江"）”，要么執(zhí)行第三個(gè)IF函數(shù)，就這樣依次執(zhí)行直至獲得正確結(jié)果。

三．身份證號(hào)碼驗(yàn)證

上面的工作完成之后，設(shè)計(jì)的公式好是好，但是怎么知道某個(gè)身份證號(hào)碼的真假？

1．驗(yàn)證網(wǎng)站

使用身份證號(hào)碼驗(yàn)證網(wǎng)站和工具就可以了，在IE地址欄輸入“http://”（中國(guó)居民身份證升級(jí)換代|中國(guó)居民身份證驗(yàn)證查詢）。該網(wǎng)站的主頁(yè)如圖7所示，只要打開(kāi)它輸入15位身份證號(hào)碼，單擊“查詢”即可得到需要的結(jié)果。

2．驗(yàn)證軟件

如果不能上網(wǎng)該怎么辦？使用身份證號(hào)碼驗(yàn)證軟件如“身份證信息解讀7.5”（http://hbcrc.onlinedown.net:82/down/sfz75.rar）。該軟件是一款“綠色軟件”，將下載得到的壓縮包釋放到某個(gè)文件夾，執(zhí)行其中的“身份證信息解讀.exe”就可以打開(kāi)如圖8所示界面。它可以查詢身份證持有人的各種信息，例如身份證歸屬地、出生日期和性別，能夠校驗(yàn)身份證號(hào)碼的真實(shí)性。

第五篇：如何通過(guò)函數(shù)提取身份證中的出生日期

如何通過(guò)函數(shù)提取身份證中的出生日期

某公司要改善公司福利，決定在每一個(gè)員工過(guò)生日時(shí)送一個(gè)深日蛋糕，為此公司經(jīng)理要求人事部主管將員工的生日統(tǒng)計(jì)下來(lái)。如果通過(guò)逐一詢問(wèn)員工生日的方式來(lái)統(tǒng)計(jì)將非常麻煩，此時(shí)該主管可以在已有員工身份證信息的基礎(chǔ)上，通過(guò)“CONCATENATE”函數(shù)來(lái)提取員工的生日日期。具體的操作步驟如下。

①選中要輸入公式的區(qū)域，在編輯欄中輸入“＝CONCATENATE(MID(F1,7,4),“年”,MID(F1,11,2),“月”,MID(F1,13,2),“日”)”。

②按下【Ctrl】＋【Enter】組合鍵即可將所有身分證中的出生日期提取出來(lái)。

“CONCATENATE”函數(shù)的操作對(duì)象必須是文本，它的作用就是將幾個(gè)文本字符串合并為一個(gè)文本字符串?！癕ID(F1,7,4)”代表的是在單元格F1中從第七位開(kāi)始向右取兩個(gè)字符，CONCATENATE(MID(F1,7,4),“年”,MID(F1,11,2),“月”,MID(F1,13,2),“日”)代表的是將提取出的字符串與“年”、“月”、“日”合并為一個(gè)字符串。