第一篇：EXCEL提取身份證信息以及鑒別身份證真?zhèn)蔚募夹g(shù)文件

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

中國居民身份證號碼是一組特征組合碼，原為１５位，現(xiàn)升級為１８位，其編碼規(guī)則為：

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

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

其中３位數(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),”男“,”女“)

４、檢驗(yàn)身份證號碼的正確性

１８位身份證號碼的最后一位是檢驗(yàn)碼，它是根據(jù)身份證前17位數(shù)字依照規(guī)則計(jì)算出來的，其值０～９或X。一般情況只要有一位數(shù)字輸入錯誤，依照規(guī)則計(jì)算后就會與第18位數(shù)不符。當(dāng)然不排除按錯誤

號碼計(jì)算后恰好與檢驗(yàn)碼相符的情況，但這種情況出現(xiàn)的可能性較低。因此，對１８位號碼的驗(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)

對于１５位身份證，由于沒有檢驗(yàn)碼，我們只能簡單地去判斷出生日期代碼是否是一個有效的日期，避免輸入一些像“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)很少了，如果對１５位的號碼不需要作進(jì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，則身份證號是正確的，結(jié)果為FALSE則是錯誤的。

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

2010-03-02 16:05 眼下是大學(xué)生求職應(yīng)聘的黃金季節(jié)，人事主管小劉忙得不亦樂乎，她負(fù)責(zé)把應(yīng)聘者的個人信息錄入Excel，確保信息真實(shí)可信是必須解決的問題。為此，小劉特地向信息部主管小張求教，學(xué)會了從身份證“挖掘”個人信息的方法，又快又好地完成了招聘的前期準(zhǔn)備工作。

可謂：“整理工作無窮盡，信息問題難小劉，Excel函數(shù)應(yīng)用，從此更上一層樓。”

一．?dāng)?shù)據(jù)錄入快又準(zhǔn)

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

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

剛開始工作小劉就碰到了難題，她輸入的身份證號碼變成了“1.10155E+17”之類。請教小張之后才知道“身份證號碼”要用“文本”格式。實(shí)現(xiàn)這一點(diǎn)的第一種方法是選中D列右擊鼠標(biāo)，選擇快捷菜單中的“設(shè)置單元格格式”，打開對話框的“數(shù)字”選項(xiàng)卡選中，選中“分類”下的“文本”然后“確定”即可。第二種方法是在輸入的身份證號碼前加一個單引號，Excel就可以把輸入的數(shù)字變?yōu)椤拔谋尽绷?。第三種方法是選中D列，單擊“格式”菜單下的“單元格”命令打開對話框，按如圖1所示選中“分類”下的“自定義”。然后在“類型”框中輸入一個“@”再“確定”即可。小劉按小張教的方法繼續(xù)操作，錄入的身份證號碼就一切正常了。

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

由于前來應(yīng)聘的大學(xué)生高達(dá)幾百人，一旦身份證號碼錄入出錯可是要扣“銀子”的，于是小劉“命令”小張拿出解決辦法。在小劉的“威逼利誘”面前，小張很快想出了“高招”：

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

第二步打開“出錯警告”選項(xiàng)卡，在“標(biāo)題”框內(nèi)輸入“數(shù)據(jù)重復(fù)”，并按如圖3所示輸入更詳細(xì)的警告信息，單擊“確定”按鈕將打開的對話框關(guān)閉。當(dāng)然，這一步是可選的，使用時(shí)可以根據(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中的“格式”按鈕打開對話框，在“字體”選項(xiàng)卡中選擇合適的顏色或刪除線等。之后如果D列中輸入的數(shù)據(jù)長度不是15位或18位，其字體就會顯示前面選擇的顏色（例如紅色）。

3．錄后檢查 萬無一失

看到這里小劉忽然問道：假如上面的操作執(zhí)行前已經(jīng)錄入了部分?jǐn)?shù)據(jù)，那么有沒有辦法檢查錄入的身份證號碼是否重復(fù)？稍微思考了一會，小張?jiān)O(shè)計(jì)了一個帶有公式的“條件格式”，圓滿解決了小劉提出的問題。

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

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

對話框關(guān)閉。

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

這時(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ù)時(shí)，公式進(jìn)行比較的范圍是從當(dāng)前單元格向上直到數(shù)據(jù)區(qū)域第一個單元格（即“$D2”），所以比較的結(jié)果只對第一個以后的相同名稱的數(shù)據(jù)應(yīng)用條件格式。

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

當(dāng)小劉將姓名和身份證號碼輸入如圖1所示的工作表以后，小張?jiān)O(shè)計(jì)的公式馬上從身份證號碼中“挖掘”出了信息。不過小劉的好學(xué)精神上來了，非要小張說清楚“挖掘”信

息的基本原理，小張只好一一給她解釋： 1．性別

根據(jù)現(xiàn)行居民身份證號碼的編碼規(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)兩種身份證號碼的，使用時(shí)只須在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不成立，這時(shí)就會在D2單元格中填入”女“，反之則會填入”男“。

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

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），”日“））”，然后讓她說出這個公式的執(zhí)行過程。

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

3．年齡

出生日期計(jì)算出來以后很容易得到“當(dāng)前年齡”，小劉在G2單元格中輸入公式

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

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

4．籍貫

從身份證號碼中“挖掘”籍貫信息是比較復(fù)雜的一項(xiàng)工作，它花費(fèi)了小張不少時(shí)間，順便了解了很多的知識：身份證號碼的第l至6位數(shù)是歸屬地代碼，其中前兩位是省、直轄市或自治區(qū)的代碼。例如“北京市居民身份證”的前兩位是“11”，“新疆維吾爾自治區(qū)居民身份證”的前兩位是“65”等。出于保留資源等方面的考慮，各省、直轄市或自治區(qū)居民身份證的歸屬地代碼不是連續(xù)的，例如“北京”、“天津”、“河北”、“山西”和“內(nèi)蒙古”五個省區(qū)市的歸屬地代碼是從11到15，而“遼寧”、“吉林”、“黑龍江”三個省的歸屬地代碼卻是從21到23。因此小張?jiān)O(shè)計(jì)的“挖掘”籍貫信息的公式就顯得比較長了，使用時(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，”上海“，”江蘇“，”浙江“，”安徽“，”福建“，”江西“，”山東“），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ù)雜，小張就給小劉仔細(xì)講解了一遍：公式一共使用了五個嵌套的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”時(shí)，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é)果。

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

上面的工作完成之后，小劉卻把小張“打擊”了一番：你設(shè)計(jì)的公式好是好，但是我怎么知道某個身份證號碼的真假？

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

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

2．驗(yàn)證軟件

“真是不錯”小劉贊嘆道，“如果我不能上網(wǎng)該怎么辦”她又問到?！笆褂蒙矸葑C號碼驗(yàn)證軟件呀”小張說到：如“身份證信息解讀7.5”（http://hbcrc.onlinedown.net:82/down/sfz75.rar）。該軟件是一款“綠色軟件”，將下載得到的壓縮包釋放到某個文件夾，執(zhí)行其中的“身份證信息解讀.exe”就可以打開如圖8所示界面。它可以查詢身份證持有人的各種信息，例如身份證歸屬地、出生日

期和性別，能夠校驗(yàn)身份證號碼的真實(shí)性。

第二篇：個人身份證識別真?zhèn)?/a>

教你識別真假居民身份證

居民身份證是國家法定的證明公民個人身份的證件。為了堵塞和制止假居民身份證的流通和使用，在查驗(yàn)和核查居民身份證時(shí)可掌握以下幾個要點(diǎn)：

居民身份證的整體識別

1.居民身份證規(guī)格為88mm×60mm，四角呈圓弧狀。式樣為聚脂薄膜密封、持證人像片和登記內(nèi)容一體化的單頁卡式，即證件是經(jīng)翻拍印制后塑封而成。

2.證件正面主要顏色為紅、綠二色，印有中華人民共和國國徽和證件名稱，以及環(huán)狀、網(wǎng)狀、團(tuán)狀花紋圖案；背面為淺綠色，印有中華人民共和國版圖、持證人標(biāo)準(zhǔn)像片，以及網(wǎng)狀花紋圖案，姓名、性別、民族、出生年月日、住址五個登記項(xiàng)目和簽發(fā)日期、有效期限、編號三個項(xiàng)目及簽發(fā)機(jī)關(guān)印章。新的防偽居民身份證背面透視全息圖象由長城峰火臺圖象ID CHINA中國等字樣組成。“ID長城峰火臺圖象CHINA中國”在“中國CHINA ID”組成的背景前由內(nèi)向外依次排列。

證件簽發(fā)機(jī)關(guān)為縣公安局、不設(shè)區(qū)的市公安局和設(shè)區(qū)的公安分局。

3.居民身份證從登記項(xiàng)目、填寫內(nèi)容和顏色上還有其他兩種形式：

a.民族自治地方頒發(fā)的居民身份證。

證件背面五個登記項(xiàng)目和簽發(fā)機(jī)關(guān)印章，同時(shí)使用漢字和相應(yīng)的少數(shù)民族文字印刷和刻制(寧夏回族自治區(qū)除外)。常見的少數(shù)民族文字有：維吾爾文、哈薩克文、蒙古文、藏文、朝鮮文、壯文、彝文等。

證件“出生年月日”的登記項(xiàng)目改為“出生日期”。如：內(nèi)地和經(jīng)濟(jì)特區(qū)頒發(fā)的證件，登記為“出生1949年10月1日”，民族自治地方頒發(fā)的證件則登記為“出生日期1949.10.1”。證件填寫內(nèi)容分為只書寫漢字(如廣西壯族自治區(qū)和內(nèi)蒙古自治區(qū)部分地區(qū))和同時(shí)書寫

漢字和少數(shù)民族文字(如新疆維吾爾自治區(qū)、西藏自治區(qū)、內(nèi)蒙古自治區(qū)部分地區(qū)、吉林省延邊朝鮮族自治州和四川、云南、青海、甘肅、黑龍江、吉林、遼寧等省部分民族自治地方)兩類。少數(shù)民族文字在上方，漢字在下方(蒙古文文字在左，漢字在右)。

對加入中國國籍的外國人，如本人的民族名稱與我國民族名稱不同，本人是什么民族就填寫什么民族，但民族名稱后應(yīng)加注“入籍”二字，如“民族烏克蘭(入籍)”。

b.經(jīng)濟(jì)特區(qū)頒發(fā)的居民身份證識別：

證件正面主體顏色為海藍(lán)色，背面為淺藍(lán)色。證件背面右上角有一個壓模全息特殊標(biāo)記，規(guī)格為9mm×12mm的全息標(biāo)志圖案。海南省經(jīng)濟(jì)特區(qū)的全息標(biāo)志圖案為五指山和太陽；深圳、珠海、汕頭和廈門經(jīng)濟(jì)特區(qū)的全息標(biāo)志圖案為有帶“T”字的盾牌符號。

居民身份證有效期限與持證人年齡、簽發(fā)日期的關(guān)系

居民身份證的有效期限分為10年、20年、長期三種。16周歲至25周歲的，發(fā)給有效期為10年的居民身份證；26周歲至45周歲的，發(fā)給有效期為20年的居民身份證；46周歲以上的，發(fā)給長期有效的居民身份證。證件有效期限從簽發(fā)之日起計(jì)算。如某人1949年9月20日出生，1984年35周歲時(shí)申領(lǐng)居民身份證，簽發(fā)日期為1984年12月31日，他屬于26至45周歲這一年齡段，證件有效期限屬于20年這一檔次，到2004年12月30日有效期滿。

查驗(yàn)或檢查時(shí)，應(yīng)對照檢查證件有效期限與持證人年齡，簽發(fā)日期三者之間的關(guān)系。居民身份證編號識別

1.證件編號由15位阿拉伯?dāng)?shù)字組成：第一至六位數(shù)字為行政區(qū)劃代碼；第七至十二位數(shù)字為出生日期代碼；第十三至十五位數(shù)字為分配順序代碼。

行政區(qū)劃代碼：只表示公民第一次申領(lǐng)居民身份證時(shí)的常住戶口所在地區(qū)。

出生日期代碼：第七至十二位數(shù)字中，第七、八位代表年份(省略年份前面二位數(shù))；第九、十位數(shù)代表月份(月份為一位數(shù)的前面加0)；第十一、十二位代表日期(日期為一位數(shù)的前面加0)。如某人1949年9月20日出生，其出生日期碼應(yīng)編為“490920”。如查驗(yàn)或核查時(shí)，應(yīng)注意核對持證人出生日期與編碼規(guī)定組成的填寫是否一致。

分配順序代碼：第十三至十五位數(shù)字中，奇數(shù)分配給男性，偶數(shù)分配給女性。查驗(yàn)或核查時(shí)，應(yīng)注意核對持證人性別是否符合男女性的分配順序代碼。

分配順序碼中“999、998、997、996”四個順序號分別為男女性百歲以上老人專用的特定編號。

2.從1999年10月1日起，全國實(shí)行公民身份證號碼制度，居民身份證編號由原15位升至18位。前6位為地址碼；第七位至14位為出生日期碼，此碼由6位數(shù)改為8位數(shù)，其中年份用4位數(shù)表示；第15位至17位為順序碼，取消了順序碼中對百歲老人使用的特定編號；第十八位為校驗(yàn)碼，主要是為了校驗(yàn)計(jì)算機(jī)輸入公民身份證號碼的前17位數(shù)字是否正確，其取值范圍是0至10，當(dāng)值等于10時(shí)，用羅馬數(shù)字符χ表示。

臨時(shí)身份證識別

臨時(shí)身份證規(guī)格、登記項(xiàng)目均與居民身份證相同。臨時(shí)身份證正面為長城、群山和網(wǎng)狀花紋圖案，顏色為藍(lán)色。背面為網(wǎng)狀圖案，顏色為黃色。臨時(shí)身份證使用與本人身份證相同的編號；常住戶口特定人員的臨時(shí)身份證編號也按居民身份證編號原則和方法編15位碼。臨時(shí)身份證有效期限分為3個月和一年兩種：尚待領(lǐng)取居民身份證或證件丟失、損壞尚待領(lǐng)補(bǔ)人員發(fā)給有效期3個月的臨時(shí)身份證，使用阿拉伯?dāng)?shù)字填寫；常住戶口特定人員發(fā)給有效期一年的臨時(shí)身份證，使用漢字填寫。

經(jīng)濟(jì)特區(qū)使用的臨時(shí)身份證不加特區(qū)全息標(biāo)記和熒光印刷圖案。

現(xiàn)有三種識別假身份證的方法：

1.每張身份證后面的圖徽上都有天安門和錦旗圖案，若天安門里面幾個小小的窗和門的布局非常均勻，并且距離相等，門窗線條很有圓滑感、呈彎形、天安門下錦旗圖案復(fù)雜但有規(guī)律，很有皺疊感和立體感的則是真的。反之，若門窗的線條很簡單，很直有棱有角，很生硬且是矩形，錦旗上的皺疊很簡單，廖廖幾筆的單線，則身份證是假的。

2.看身份證反面右上角的條紋，從第一條條紋向下數(shù)，一直數(shù)到第四條，如果第四條條紋象其它的波浪條紋交叉的就是假身份證，沒交叉的就是真身份證，因?yàn)檎嫔矸葑C反面條紋除右上角的第四條唯一的一條沒有交叉，其余的都是交叉的。

3.正面的“出生”二字的出字要比生字高一些，如出生二字在同一水平線上，這就可能是假證。

4.出生二字附件的中國地圖的邊線，應(yīng)有一個不明顯的缺口，這部份的線是不連續(xù)的。

5.看身份證左側(cè)的印章。所有的橫筆劃如果有收尾則可能是假的?！熬帧弊钟疫厬?yīng)齊頭，如果印章中的“局”字和此文中的“局”字一樣，則身份證可能是假的。

6.看手感。真身份證較有彈性，折彎后迅速還原；假身份證較軟或較厚、易折斷、沒彈性。

7.看字體。真身份證的“姓名”、“性別”、“出生”、“民族”、“編號”的字體是公安部的加密字體，體形扁長；假身份證則字體較寬或比較模糊(因是復(fù)制)。

8.看打印效果。真身份證是采用針式打印，墨色較淡；假身份證常采用噴墨打印，墨色濃重。

身份證簡易識別真?zhèn)畏椒?/p>

在一般的光線下，平視第二代身份證表面時(shí)，表面上的物理防偽膜是無色透明的；適當(dāng)上下傾斜“二代身份證”，便會觀察到證件的左上方有一個變色的長城圖案，呈橙綠色；用左眼和用右眼分別觀察，身份證上的長城圖案的顏色將呈不同顏色；將身份證旋轉(zhuǎn)90度(垂

直方向)，觀察到的長城圖案呈藍(lán)紫色。

新舊版身份證真?zhèn)巫R別方法(詳細(xì))

1、新、舊版真身份證手摸有前凸后平感，四周壓封平實(shí)、清透。假身份證手模多有前后雙凸感，四周壓封處多有膠熔性氣泡反映。

2、新版身份證：側(cè)光驗(yàn)看正面的“中國”等字樣和長城的立體浮雕圖案熒光反映是否強(qiáng)烈，尤其是反面周邊壓封處的熒光反映是否強(qiáng)烈，反映強(qiáng)烈的為真版。

3、真身份證公章上的所有文字和姓名、性別、出生、地址、編號等文字的橫筆均為平直筆劃，如“市、安”，橫筆的收筆處無三角。假身份證則不同，如“市、安”，橫筆的收筆處有三角。公章中的“局”右邊上下平齊()為真身份證；上縮下凸(局)為假身份證。

4、真身份證正面“性別”字樣下的國界線有三處未連接的缺口；假身份證此處無缺口。

5、真身份證的中國版圖海南島嶼與臺灣島嶼下方至編號處有數(shù)個點(diǎn)狀和條狀島嶼相連。假身份證大多沒有這些島嶼，有的只在臺灣島嶼下方有幾根模糊不清的短線。

6、真身份證反面右上角倒數(shù)第四根相絞的綠色網(wǎng)線上有一個香蕉狀網(wǎng)紋。假身份證沒有。

7、真身份證反面國徽中頂部，大五角星上角正指一處有麥穗相對形成的“”形缺口。假身份證“缺口”與真身份證“缺口”有所不同，即使形狀相同，但兩側(cè)麥穗形狀模糊不清，導(dǎo)致“缺口”不成形態(tài)。

8、真身份證反面國徽天安門下方的齒輪中心為圈狀。若齒輪中心圈中為“1”形狀，一定是假身份證。

9、真身份證反面，中華人民共和國的“民”字處紅色花紋圖案右上角花紋第二根紋線端部有一個未連成線的囊袋。若囊袋被連成線，則一定是假身份證。

說明：

1、以上9項(xiàng)特征肉眼觀察較易辨別，尤其是3—9項(xiàng)特征固有、穩(wěn)定、直觀明了(特殊情況除外)。其它特征，如版面花紋顏色深淺；字的大小長短、色調(diào)深淺等特征不易辨別。當(dāng)然，還有需要儀器鑒別的特征。

2、識別真假身份證要綜合多個特征。真身份證不能根據(jù)一個特征或一個面的多個特征相同而定論。假身份證只要以上3—9項(xiàng)中的一項(xiàng)特征與真身份證不同，可基本定論為假身份證

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

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

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

二、編碼規(guī)則：公民身份號碼是特征組合碼，由十七位數(shù)字本體碼和一位校驗(yàn)碼組成。排列順序從左至右依次為：六位數(shù)字地址碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位校驗(yàn)碼，可以用字母表示如為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．校驗(yàn)碼（R），一位數(shù)字，通過前17位數(shù)字根據(jù)一定計(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=大連?? 只有地級城市有獨(dú)立的城市代碼，縣級市沒有。

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ù)為女性分配碼，如遇同年同月同日有兩人以上時(shí)順延第二、第三、第四、第五個分配碼。如：007的就是個男生，而且和他同年月日生的男生至少有兩個，他們的后四位是001*和003*。分配順序碼中“999、998、997、996”四個順序號分別為男女性百歲以上老人專用的特定編號。

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

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

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；計(jì)算出總和（用S）表示。2.以11對計(jì)算結(jié)果取模

Y = mod(S, 11)

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

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

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

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

校驗(yàn)碼： 1 0 X 9 8 7 6 5 4 3 2

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

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

關(guān)于18位身份證號碼尾數(shù)是“X”的一種解釋：因?yàn)榘凑丈厦娴囊?guī)則，校驗(yàn)碼有11個，而不是10個，所以不能用0-9表示。所以如果尾號是10，那么就得用X來代替，因?yàn)槿绻?0做尾號，那么此人的身份證就變成了19位，而19位的號碼違反了國家標(biāo)準(zhǔn)，并且我國的計(jì)算機(jī)應(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ù)字校驗(yàn)碼組成。排列順序從左至右依次為：六位數(shù)字地址碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位數(shù)字校驗(yàn)碼。

第四篇：員工身份證鑒別管理制度

員工身份證鑒別管理制度

員工身份證代表員工身份，人事部日常的新員工招聘工作中，經(jīng)常遇到持假證或借他人身份證來應(yīng)聘，企圖蒙混過關(guān)。在假（借）證的人員當(dāng)中，不乏有不夠法定做工年齡、隱姓埋名之不法分子存在，為使公司正常的人事工作不受影響，同時(shí)為對員工本人、工廠及客戶三方面負(fù)責(zé)，公司特制定員工身份證鑒別管理制度。

1．人事招聘作業(yè)規(guī)范

★ 招聘人事參與并進(jìn)行面試，核實(shí)身份證及其它有效證件，符合《勞動法》務(wù)工年齡等條件的發(fā)給《職位申請表》。

★ 要求應(yīng)聘者在《職位申請表》中據(jù)實(shí)填寫個人履歷、學(xué)歷、政治背景及詳細(xì)地址。

★ 對將從事重要崗位的人員人事部要通過當(dāng)?shù)毓膊块T協(xié)助核實(shí)其身份以及背景調(diào)查（上一個單位），經(jīng)核實(shí)合格后方可上崗。

★ 入職培訓(xùn)由人事部組織安排對新招人員進(jìn)行培訓(xùn)，讓員工了解公司的品質(zhì)系統(tǒng)及政策，廠規(guī)、廠紀(jì)等，派發(fā)《員工手冊》，做好記錄，存檔于人事部。

1．身份證件真假鑒別

★ 視覺鑒別：

☆ 身份證網(wǎng)格直接印在證身上，而假身份證網(wǎng)格在過膠片上。

☆ 真身份證中公安機(jī)關(guān)印章“局”字，外形上下整齊，并且“尸”與“乛”右上方連接在一起（96年1月1日以前辦理的身份證）。

☆ 真身份證中公安機(jī)關(guān)印章字體偏長，筆劃采用類似隸書形式如“一”而假身份證中印章字體屬鉛體字“一”。

☆ 真身份證防偽標(biāo)志“長城圖案”與“中國”字樣鮮明，并會反光；而假證圖案與字樣暗淡，不明顯（96年1月1日以后辦理的證件）。☆ 真證反面國徽比較精細(xì)、清楚，假證則粗糙、簡單。

★ 使用身份證測試鑒別儀。

2． 借身份證入廠的辨別程序：

★ 考慮到借他人身份證應(yīng)聘者有三方面特點(diǎn)：

☆ 仔細(xì)審查會發(fā)現(xiàn)許多特點(diǎn)與證件不相符；

☆ 對證件內(nèi)容未熟記；

☆ 臨時(shí)借用他人證件，不能長久持有，為此我們平時(shí)在招聘驗(yàn)證時(shí)，要遵循此規(guī)定并實(shí)行二級查證管理。

★ 人事在招聘時(shí)要特別留意查對身份證：

☆ 首先辨別身份證上的年齡是否與其本人相符合；

☆ 證件相片是否與其本人相同。

★ 當(dāng)證件模糊不清或一時(shí)辨認(rèn)不清時(shí)則應(yīng)：

☆ 詢問應(yīng)聘者的家庭地址與出生年月日，有時(shí)特意報(bào)錯內(nèi)容，要應(yīng)聘者予以糾正；

☆ 尋找身份證或特征部分位置作比較，或找定臉部某一特征比較；

☆ 在身份證或應(yīng)聘者器官位置作比較，如耳朵的高度與口、鼻子比較，下頜的寬度、口、鼻的距離，眉毛的長度與生長的傾斜度，眼的形狀、額頭的寬度等都可以作比較；

☆ 檢查應(yīng)聘者其它證書，如畢業(yè)證、計(jì)生證等。

★ 入廠后根據(jù)應(yīng)聘者所提供的人事資料、身份證復(fù)印件、相片，人事部主管可做二級檢查，如有疑問重新返回下一級作重新鑒定。

3． 身份證有效性和真實(shí)性跟蹤監(jiān)控管理：

★ 員工在職期間，人事部會不定期的測試員工身份證的有效性和真實(shí)性： ☆ 發(fā)放工資時(shí)，需要員工出示其本人有效身份證；②辦理居住證時(shí)，需提供身份證復(fù)印件。

☆ 如對測試到有遺失、假證、過期或身份證寄回家的員工，人事部應(yīng)出具《催辦身份證通知書》。

☆ 一般通知期為6個月，6個月后仍未能辦理到本人身份證件者，作無證

人員處理。

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

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

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

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

其中３位數(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),”男“,”女“)４、檢驗(yàn)身份證號碼的正確性 １８位身份證號碼的最后一位是檢驗(yàn)碼，它是根據(jù)身份證前17位數(shù)字依照規(guī)則計(jì)算出來的，其值０～９或X。一般情況只要有一位數(shù)字輸入錯誤，依照規(guī)則計(jì)算后就會與第18位數(shù)不符。當(dāng)然不排除按錯誤號碼計(jì)算后恰好與檢驗(yàn)碼相符的情況，但這種情況出現(xiàn)的可能性較低。因此，對１８位號碼的驗(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)

對于１５位身份證，由于沒有檢驗(yàn)碼，我們只能簡單地去判斷出生日期代碼是否是一個有效的日期，避免輸入一些像“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)很少了，如果對１５位的號碼不需要作進(jì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，則身份證號是正確的，結(jié)果為FALSE則是錯誤的。

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

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

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

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

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

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

第二步打開“出錯警告”選項(xiàng)卡，在“標(biāo)題”框內(nèi)輸入“數(shù)據(jù)重復(fù)”，并按如圖3所示輸入更詳細(xì)的警告信息，單擊“確定”按鈕將打開的對話框關(guān)閉。當(dāng)然，這一步是可選的，使用時(shí)可以根據(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中的“格式”按鈕打開對話框，在“字體”選項(xiàng)卡中選擇合適的顏色或刪除線等。之后如果D列中輸入的數(shù)據(jù)長度不是15位或18位，其字體就會顯示前面選擇的顏色（例如紅色）。

3．錄后檢查 萬無一失

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

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

第二步是設(shè)置比較結(jié)果為“真”時(shí)應(yīng)用的條件格式，方法是單擊“格式”按鈕打開如圖6所示對話框，在“顏色”下拉列表選中條件為“真”時(shí)顯示的字體顏色（例如紅色）。也可以根據(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ù)時(shí)，公式進(jìn)行比較的范圍是從當(dāng)前單元格向上直到數(shù)據(jù)區(qū)域第一個單元格（即“$D2”），所以比較的結(jié)果只對第一個以后的相同名稱的數(shù)據(jù)應(yīng)用條件格式。

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

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

根據(jù)現(xiàn)行居民身份證號碼的編碼規(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)兩種身份證號碼的，使用時(shí)只須在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不成立，這時(shí)就會在D2單元格中填入”女“，反之則會填入”男“。

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

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)的計(jì)算語句。對15位的身份證號碼來說，左起第7至12個字符表示出生年月日，此時(shí)可以使用MID函數(shù)從身份證號碼的特定位置，分別提取出生年、月、日。然后用CONCATENATE函數(shù)將提取出來的文字合并起來，就能得到對應(yīng)的出生年月日。公式中”19“是針對早期身份證號碼中存在2000年問題設(shè)計(jì)的，它可以在計(jì)算出來的出生年份前加上19。對18位的身份證號碼的計(jì)算思路相同，只是它不存在2000年問題，公式中不用給計(jì)算出來的出生年份前加上19。

3．年齡

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

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

4．籍貫

從身份證號碼中“挖掘”籍貫信息是比較復(fù)雜的一項(xiàng)工作，身份證號碼的第l至6位數(shù)是歸屬地代碼，其中前兩位是省、直轄市或自治區(qū)的代碼。例如“北京市居民身份證”的前兩位是“11”，“新疆維吾爾自治區(qū)居民身份證”的前兩位是“65”等。出于保留資源等方面的考慮，各省、直轄市或自治區(qū)居民身份證的歸屬地代碼不是連續(xù)的，例如“北京”、“天津”、“河北”、“山西”和“內(nèi)蒙古”五個省區(qū)市的歸屬地代碼是從11到15，而“遼寧”、“吉林”、“黑龍江”三個省的歸屬地代碼卻是從21到23。因此小張?jiān)O(shè)計(jì)的“挖掘”籍貫信息的公式就顯得比較長了，使用時(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，”上海“，”江蘇“，”浙江“，”安徽“，”福建“，”江西“，”山東“），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”時(shí)，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é)果。

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

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

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

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

2．驗(yàn)證軟件

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