第一篇：excel中身份證信息自動提取

excel中如何根據(jù)身份證號，自動求出出生年月？ 很好用！根據(jù)我的實際工作需要，又少作修改的方法： 假如身份證號數(shù)據(jù)在A1單元格，在B1單元格中編輯公式

=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(A1,13,2))這樣輸出格式就都是19821010這種格式了。excel中如何根據(jù)身份證號，自動求出性別？

假定身份證號在A1單元格，則在B1單元格中編輯公式 =IF(AND(LEN(A1)=15,MOD(MID(A1,15,1)*1,2)=1),“男”, IF(AND(LEN(A1)=18,MOD(MID(A1,17,1)*1,2)=1),“男”,“女”))在excel是根據(jù)身份證自動填充年齡？

假設(shè)A列從A1開始是身份證號，在B列中進行轉(zhuǎn)換，則在B1中輸入

=YEAR(TODAY())-IF(LEN(A1)=15,“19”&MID(A1,7,2),MID(A1,7,4))，然后再向下復(fù)制即可。公式解析：

一、分析身份證號碼

其實，身份證號碼與一個人的性別、出生年月、籍貫等信息是緊密相連的，無論是15位還是18位的身份證號碼，其中都保存了相關(guān)的個人信息。

15位身份證號碼：第7、8位為出生年份(兩位數(shù))，第9、10位為出生月份，第11、12位代表出生日期，第15位代表性別，奇數(shù)為男，偶數(shù)為女。

18位身份證號碼：第7、8、9、10位為出生年份(四位數(shù))，第11、第12位為出生月份，第13、14位代表出生日期，第17位代表性別，奇數(shù)為男，偶數(shù)為女。

例如，某員工的身份證號碼(15位)是***，那么表示1972年8月7日出生，性別為女。如果能想辦法從這些身份證號碼中將上述個人信息提取出來，不僅快速簡便，而且不容易出錯，核對時也只需要對身份證號碼進行檢查，肯定可以大大提高工作效率。

二、提取個人信息

這里，我們需要使用IF、LEN、MOD、MID、DATE等函數(shù)從身份證號碼中提取個人信息。如圖1所示，其中員工的身份證號碼信息已輸入完畢(C列)，出生年月信息填寫在D列，性別信息填寫在B列。

1.提取出生年月信息

由于上交報表時只需要填寫出生年月，不需要填寫出生日期，因此這里我們只需要關(guān)心身份證號碼的相應(yīng)部位即可，即顯示為“7208”這樣的信息。在D2單元格中輸入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：

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

MID(C2,7,4)：從C2單元格中字符串的第7位開始提取四位數(shù)字，本例中表示提取15位身份證號碼的第7、8、9、10位數(shù)字。

MID(C2,9,4)：從C2單元格中字符串的第9位開始提取四位數(shù)字，本例中表示提取18位身份證號碼的第9、10、11、12位數(shù)字。

IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一個邏輯判斷函數(shù)，表示如果C2單元格是15位，則提取第7位開始的四位數(shù)字，如果不是15位則提取自第9位開始的四位數(shù)字。

如果需要顯示為“70年12月”這樣的格式，請使用DATE格式，并在“單元格格式→日期”中進行設(shè)置。

2.提取性別信息

由于報表中各位員工的序號編排是按照上級核定的編制進行的，因此不可能按照男、女固定的順序進行編排，如果一個一個手工輸入的話，既麻煩又容易出錯 例如性別信息統(tǒng)一在B列填寫，可以在B2單元格中輸入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,“男”,“女”)”，其中：

LEN(C2)=15：檢查身份證號碼的長度是否是15位。

MID(C2,15,1)：如果身份證號碼的長度是15位，那么提取第15位的數(shù)字。

MID(C2,17,1)：如果身份證號碼的長度不是15位，即18位身份證號碼，那么應(yīng)該提取第17位的數(shù)字。

MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到給出數(shù)字除以指定數(shù)字后的余數(shù)，本例表示對提出來的數(shù)值除以2以后所得到的余數(shù)。

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

回車確認(rèn)后，即可在B2單元格顯示正確的性別信息，接下來就是選中填充柄直接拖曳。

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

EXCEL提取身份證出生日期、性別、檢驗身份證號碼正確性

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

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

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

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

如果有一個編碼和省份地區(qū)的對照表，可以用VLOOKUP函數(shù)來提取地區(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“)簡化公式：=--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)(請將輸入公式的單元格格式設(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,”男“,”女“)簡化公式：=IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“)４、檢驗身份證號碼的正確性 １８位身份證號碼的最后一位是檢驗碼，它是根據(jù)身份證前17位數(shù)字依照規(guī)則計算出來的，其值０～９或X。一般情況只要有一位數(shù)字輸入錯誤，依照規(guī)則計算后就會與第18位數(shù)不符。當(dāng)然不排除按錯誤號碼計算后恰好與檢驗碼相符的情況，但這種情況出現(xiàn)的可能性較低。因此，對１８位號碼的驗證采用如下公式：

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

對于１５位身份證，由于沒有檢驗碼，我們只能簡單地去判斷出生日期代碼是否是一個有效的日期，避免輸入一些像“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“))))

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

=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，則身份證號是正確的，結(jié)果為FALSE則是錯誤的。

你也可以將上述公式放在數(shù)據(jù)有效性中，防止錄入錯誤的身份證號。操作方法:選擇需要輸入身份證的全部單元格區(qū)域,比如A2:A10,點菜單”數(shù)據(jù)“-”有效性“,在”允許“的下拉框中選擇”自定義“,在”公式“輸入上面的15位和18位通用公式,確定以后即可。注意:公式里的”A2“是你剛才選定要輸入身份證的單元格區(qū)域的第一個單元格,如果你是要在C3:C20輸入身份證號,則將公式里的”A2“改為”C3“。另外,你也可以先設(shè)置好某單個單元格的數(shù)據(jù)有效性(這時公式的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)聘者或員工的個人信息錄入Excel，確保信息真實可信是必須解決的問題。一．?dāng)?shù)據(jù)錄入快又準(zhǔn)

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

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

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

2．錄入校驗 錯誤靠邊

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

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

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

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

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

3．錄后檢查 萬無一失

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

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

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

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

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

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

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

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

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

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

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

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

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），”日“））”

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

3．年齡

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

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

4．籍貫

從身份證號碼中“挖掘”籍貫信息是比較復(fù)雜的一項工作，身份證號碼的第l至6位數(shù)是歸屬地代碼，其中前兩位是省、直轄市或自治區(qū)的代碼。例如“北京市居民身份證”的前兩位是“11”，“新疆維吾爾自治區(qū)居民身份證”的前兩位是“65”等。出于保留資源等方面的考慮，各省、直轄市或自治區(qū)居民身份證的歸屬地代碼不是連續(xù)的，例如“北京”、“天津”、“河北”、“山西”和“內(nèi)蒙古”五個省區(qū)市的歸屬地代碼是從11到15，而“遼寧”、“吉林”、“黑龍江”三個省的歸屬地代碼卻是從21到23。因此小張設(shè)計的“挖掘”籍貫信息的公式就顯得比較長了，使用時首先在E2單元格輸入下面的公式，回車以后即可得到D2單元格中存儲的歸屬地信息。再將公式復(fù)制到E3、E4等單元格，即可得到對應(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，”上?！埃苯K“，”浙江“，”安徽“，”福建“，”江西“，”山東“），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，”陜西“，”甘肅“，”青?！?，”寧夏“，”新疆“））））））

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

三．身份證號碼驗證

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

1．驗證網(wǎng)站

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

2．驗證軟件

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

第三篇：身份證相關(guān)信息

一、身份證號碼執(zhí)行標(biāo)準(zhǔn)：18位身份證標(biāo)準(zhǔn)在國家質(zhì)量技術(shù)監(jiān)督局于1999年7月1日實施的GB11643-1999《公民身份號碼》。

GB11643-1999《公民身份號碼》為GB11643-1989《社會保障號碼》的修訂版，其中指出將原標(biāo)準(zhǔn)名稱“社會保障號碼”更名為“公民身份號碼”，另外GB11643-1999《公民身份號碼》從實施之日起代替GB11643-1989。

二、編碼規(guī)則：公民身份號碼是特征組合碼，由十七位數(shù)字本體碼和一位校驗碼組成。排列順序從左至右依次為：六位數(shù)字地址碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位校驗碼，可以用字母表示如為ABCDEFYYYYMMDDXXXR。其含義如下：

1.地址碼（ABCDEF）：表示編碼對象常住戶口所在縣(市、旗、區(qū))的行政區(qū)劃代碼，按GB/T2260的規(guī)定執(zhí)行。

2.出生日期碼（YYYYMMDD）：表示編碼對象出生的年、月、日，按GB/T7408的規(guī)定執(zhí)行，年、月、日分別用4位、2位（不足兩位加0）、2（同上）位數(shù)字表示，之間不用分隔符。

3.順序碼（XXX）：表示在同一地址碼所標(biāo)識的區(qū)域范圍內(nèi)，對同年、同月、同日出生的人編定的順序號，順序碼的奇數(shù)分配給男性，偶數(shù)分配給女性。

4．校驗碼（R），一位數(shù)字，通過前17位數(shù)字根據(jù)一定計算得出。

三、關(guān)于地址碼含義的詳細(xì)解釋：

身份證前六位是地區(qū)代碼，我們用ABCDEF表示。代碼的解釋規(guī)則如下：

A:國內(nèi)區(qū)域

華北三省二市

東北三省

華東六省一市

華南六省

西南四省一市 6 西北五省

臺灣

港澳

B（或者說是AB，就是前2位）:省（直轄市，自治區(qū)，特別行政區(qū)）代碼

按照A劃定的分區(qū)定義省代碼，有直轄市的，直轄市列前，其余按離直轄市的距離排序，沒有直轄市的，按離北京的遠(yuǎn)近排序。

具體省（直轄市，自治區(qū)，特別行政區(qū)）代碼如下：

11-15 京 津 冀 晉 蒙

21-23 遼 吉 黑

31-37 滬 蘇 浙 皖 閩 贛 魯

41-46 豫 鄂 湘 粵 桂 瓊

50-54 渝 川 貴 云 藏

61-65 陜 甘 青 寧 新

81-82 港 澳

CD:城市代碼

從01開始排，對于直轄市，CD=01表示市轄區(qū)，CD=02表示轄縣；省的城市代碼從省會開始排，比如2101=沈陽 2102=大連?? 只有地級城市有獨立的城市代碼，縣級市沒有。

EF:市轄區(qū)、郊區(qū)、郊縣、縣級市代碼

如果EF=00，指代這個城市，不特定區(qū)縣；對于非直轄市，如EF=01，指代市轄區(qū)（任意一個區(qū)），02開始指代特定的區(qū)。

其中：

E=0代表市轄區(qū)，E=1代表郊區(qū)，E=2代表郊縣，E=8代表縣級市。

對于直轄市，從01開始就依次排區(qū)，沒有市區(qū)和郊區(qū)的代碼區(qū)分。：

有關(guān)更詳細(xì)的資料見附錄。

四、生日期碼（YYYYMMDD）表示編碼對象出生的年、月、日，其中年份用四位數(shù)字表示，年、月、日之間不用分隔符。例如：1981年05月11日就用19810511表示。

五、順序碼（XXX）（身份證第十五位到十七位）是縣、區(qū)級政府所轄派出所的分配碼，每個派出所分配碼為10個連續(xù)號碼，例如“000-009”或“060-069”，其中單數(shù)為男性分配碼，雙數(shù)為女性分配碼，如遇同年同月同日有兩人以上時順延第二、第三、第四、第五個分配碼。如：007的就是個男生，而且和他同年月日生的男生至少有兩個，他們的后四位是001*和003*。分配順序碼中“999、998、997、996”四個順序號分別為男女性百歲以上老人專用的特定編號。

六、關(guān)于校驗碼

校驗碼是通過一系列數(shù)學(xué)計算得出來的，具體校驗的計算方式如下：

1.對前17位數(shù)字本體碼加權(quán)求和

公式為：S = Sum(Ai * Wi), i = 0,..., 16

其中Ai表示第i位置上的身份證號碼數(shù)字值，Wi表示第i位置上的加權(quán)因子，其各位對應(yīng)的值依次為： 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

通俗解釋：身份證第一位數(shù)字X7+第二位X9+第三位X10+第四位X5+第五位X5+第六位X8+第七位X4+第八位X1+第九位X6+第十位X3+十一位X7+十二位X9+十三位X10+十四位X5+十五位X8+十六位X4+十七位X2；計算出總和（用S）表示。2.以11對計算結(jié)果取模

Y = mod(S, 11)

通俗解釋：用S除以11，看最后的余數(shù)。如果除盡，為0；余數(shù)為1，則計為1；最大余數(shù)為10；全部數(shù)字為0-10共11個數(shù)字。（用Y表示）。

3.根據(jù)模的值得到對應(yīng)的校驗碼

對應(yīng)關(guān)系為：

Y值： 0 1 2 3 4 5 6 7 8 9 10

校驗碼： 1 0 X 9 8 7 6 5 4 3 2

通俗解釋：余數(shù)為0，則校驗碼為1；依次類推：余數(shù)為1，則校驗碼對應(yīng)0；以下：2--X；3--9；4--8；5--7；6--6；7--5；8--4；9-3；10-2。

如果校驗碼不符合這個規(guī)則，則肯定是假號碼。

關(guān)于18位身份證號碼尾數(shù)是“X”的一種解釋：因為按照上面的規(guī)則，校驗碼有11個，而不是10個，所以不能用0-9表示。所以如果尾號是10，那么就得用X來代替，因為如果用10做尾號，那么此人的身份證就變成了19位，而19位的號碼違反了國家標(biāo)準(zhǔn)，并且我國的計算機應(yīng)用系統(tǒng)也不承認(rèn)19位的身份證號碼。Ⅹ是羅馬數(shù)字的10，用X來代替10，可以保證公民的身份證符合國家標(biāo)準(zhǔn)。

根據(jù)〖中華人民共和國國家標(biāo)準(zhǔn) GB 11643-1999〗中有關(guān)公民身份號碼的規(guī)定，公民身份號碼是特征組合碼，由十七位數(shù)字本體碼和一位數(shù)字校驗碼組成。排列順序從左至右依次為：六位數(shù)字地址碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位數(shù)字校驗碼。

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

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

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

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

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

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

第五篇：Excel輸入出生日期、年齡自動計算、身份證提取日期、統(tǒng)計

Excel輸入出生日期、年齡自動計算、身份證提取日期、統(tǒng)計

背景：統(tǒng)計在校生需要輸入出生年月日，改動出生年月日，統(tǒng)計年齡，從身份證提取年齡等。網(wǎng)上的介紹涉及格式又?jǐn)?shù)字又文本的，有的輸入時還自動變成1905或2441年等，很煩惱，而這次總結(jié)的辦法不會出現(xiàn)上述問題，都是在格式為數(shù)字格式的情況計算的，只是涉及小數(shù)位數(shù)和是否自動插入小數(shù)點等問題。

常用公式：

1、=DATEDIF(E4,TODAY(),“y”)

（計算一個學(xué)生今年的歲數(shù)）

2、=2006-year(d1)(計算一個學(xué)生不同學(xué)的歲數(shù)）

3、擇取單元格前面的幾位數(shù)=LEFT(C15,4）取C15中前4個字符就是你要的年份了

4、提取指定位置，指定長度的字符串

=MID（A2，7,8）

即 =MID（要提取的單元格，起始位數(shù)，提取長度）

5、對如：79.1-197901、80.10-198010、2001.1-200101、2001.12-200112都適用。

方案： 找一空閑列輔助，比如原來的出生年月列在A列，E列為空閑列，在E1中輸入

=IF(LEN($A1)<4“錯誤”,CHOOSE(LEN($A1)-3,“19”&LEFT($A1,2)&“0”&RIGHT($A1,1),“19”&LEFT($A1,2)&RIGHT($A1,2),LEFT($A1,4)&“0”&RIGHT($A1,1),LEFT($A1,4)&RIGHT($A1,2)))

6、假設(shè)你A1輸入的是出生年月B1輸入=DATEDIF(A1,today(),“y”)但是這是指你的A1是輸入的標(biāo)準(zhǔn)日期，如果輸入的是文本格式的那B1輸入=year(today())-left(a1,4)

綜上所述，總結(jié)了兩種極為有效的方法，其實原理是一種而已： 方法一：

1、打開excel 2007或2003，出生年月處數(shù)據(jù)自動插入小數(shù)點，格式為數(shù)字并保留兩位小數(shù)，分隔符為“-”

2、提取前四位字符，即出生年份 =LEFT(A2,4）

3、打開2003或另一個2007程序，粘貼，格式為文本格式。

4、計算年齡： =2013-A2(年份所在單元格），拖動年齡列即可

方法二：根據(jù)第一種方法總結(jié)的經(jīng)典法

1、打開excel 2007或2003，出生日期處，格式為數(shù)字，保留小數(shù)兩位，高級選項里自動插入小數(shù)點兩位，分隔符為“-”

2、停止excel自動插入小數(shù)點。

3、把年齡單元格改為數(shù)值并不保留小數(shù)點，輸入公式 =year(today())-left(a1,4)即用今年減去年齡單元格的前四位整數(shù)，即為年齡。然后拖動年齡列即可。

4、更換學(xué)時，學(xué)生的年齡是發(fā)生變化的，只需改動年齡位置的公式，如1976年2013該學(xué)生是37歲，那么2012時該學(xué)生應(yīng)該是36歲，所以公式應(yīng)該是 =year(today())-left(a1,4)-1，注意減1即可。若是2000，則=year(today())-left(a1,4)-13即可，即為該學(xué)生2000年時的歲數(shù)。