第一篇：EXCEL提取身份證信息以及鑒別身份證真偽的技術文件

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

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

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

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

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

１、提取籍貫地區的行政區劃代碼（A2為身份證號，下同）

１５與１８位通用：=LEFT(A2,6)

如果有一個編碼和省份地區的對照表，可以用VLOOKUP函數來提取地區信息。

２、提取出生日期信息

１５位：=--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“)(請將輸入公式的單元格格式設置為日期格式）

３、提取性別信息

１５位：=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),”男“,”女“)

４、檢驗身份證號碼的正確性

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

號碼計算后恰好與檢驗碼相符的情況，但這種情況出現的可能性較低。因此，對１８位號碼的驗證采用如下公式：

=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“))))

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

=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單元格，如果結果為TRUE，則身份證號是正確的，結果為FALSE則是錯誤的。

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

５、１５位升為１８位

=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)

６、１８位轉換為１５位

=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),地區表!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 眼下是大學生求職應聘的黃金季節，人事主管小劉忙得不亦樂乎，她負責把應聘者的個人信息錄入Excel，確保信息真實可信是必須解決的問題。為此，小劉特地向信息部主管小張求教，學會了從身份證“挖掘”個人信息的方法，又快又好地完成了招聘的前期準備工作。

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

一．數據錄入快又準

小劉負責錄入的個人信息內容如圖1所示，除了“序號”、“姓名”和“身份證號碼”以外，其余信息由小張設計公式從“身份證號碼”中“挖掘”。

1．別讓數據變“亂”

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

2．錄入校驗 錯誤靠邊

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

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

第二步打開“出錯警告”選項卡，在“標題”框內輸入“數據重復”，并按如圖3所示輸入更詳細的警告信息，單擊“確定”按鈕將打開的對話框關閉。當然，這一步是可選的，使用時可以根據具體情況取舍。

此后只要在當前單元格中輸入了重復數據，Excel就會彈出“數據重復”對話框告

知小劉，并拒絕接受已經輸入的重復數據。

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

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

3．錄后檢查 萬無一失

看到這里小劉忽然問道：假如上面的操作執行前已經錄入了部分數據，那么有沒有辦法檢查錄入的身份證號碼是否重復？稍微思考了一會，小張設計了一個帶有公式的“條件格式”，圓滿解決了小劉提出的問題。

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

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

對話框關閉。

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

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

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

當小劉將姓名和身份證號碼輸入如圖1所示的工作表以后，小張設計的公式馬上從身份證號碼中“挖掘”出了信息。不過小劉的好學精神上來了，非要小張說清楚“挖掘”信

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

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

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

看到這里，小劉問到上述公式是怎樣的工作的？小張說：該公式由三個IF函數構成，其中“IF（MOD（MID（D2，15，1），2）=1，”男“，”女“）”和“IF（MOD（MID（D2，17，1），2）=1，”男“，”女“）”作為第一個函數的參數。公式中的“LEN（D2）=15”是一個邏輯判斷語句，LEN函數提取D2、D3等單元格中的字符長度，如果該字符的長度等于15，則執行參數中的第一個IF函數，否則就執行第二個IF函數。

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

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

2．生日

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

3．年齡

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

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

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

4．籍貫

從身份證號碼中“挖掘”籍貫信息是比較復雜的一項工作，它花費了小張不少時間，順便了解了很多的知識：身份證號碼的第l至6位數是歸屬地代碼，其中前兩位是省、直轄市或自治區的代碼。例如“北京市居民身份證”的前兩位是“11”，“新疆維吾爾自治區居民身份證”的前兩位是“65”等。出于保留資源等方面的考慮，各省、直轄市或自治區居民身份證的歸屬地代碼不是連續的，例如“北京”、“天津”、“河北”、“山西”和“內蒙古”五個省區市的歸屬地代碼是從11到15，而“遼寧”、“吉林”、“黑龍江”三個省的歸屬地代碼卻是從21到23。因此小張設計的“挖掘”籍貫信息的公式就顯得比較長了，使用時首先在E2單元格輸入下面的公式，回車以后即可得到D2單元格中存儲的歸屬地信息。再將公式復制到E3、E4等單元格，即可得到對應D3、D4等單元格的身分證歸屬地（籍貫）信息。

=IF（MID（D2，1，2）<=”15“，CHOOSE（MID（D2，1，2）-10，”北京“，”天津“，”河北“，”山西“，”內蒙古“），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，”陜西“，”甘肅“，”青海“，”寧夏“，”新疆“））））））

由于上面這個公式的結構比較復雜，小張就給小劉仔細講解了一遍：公式一共使用了五個嵌套的IF函數，第一個IF函數中的邏輯判斷式“MID（D2，1，2）<=”15“”用來判斷身份證歸屬地代碼是否在11到15之間。如果這個邏輯判斷式成立，那么公式就執行參數“CHOOSE（MID（D2，1，2）-10，”北京“，”天津“，”河北“，”山西“，”內蒙古“）”。其中“（MID（D2，1，2）”返回身份證歸屬地（省市區）的代碼（即身份證號碼的前兩位），如果（MID（D2，1，2）返回的結果是11（即北京市的代碼），那么“MID（D2，1，2）-10”返回供CHOOSE函數使用的索引號。當“MID（D2，1，2）-10”的結果是“1”時，CHOOSE函數就會返回后面參數表中的身份證歸屬地“北京”。

如果第一個IF函數中的邏輯判斷式“MID（D2，1，2）<=”15“”不成立，公式就執行的第二個IF函數，它的結構和第一個IF函數完全相同。但是邏輯判斷式“MID（D2，1，2）<=”15“”中的條件必須修改，由于“遼寧”、“吉林”、“黑龍江”三個省的歸屬地代碼的最大值是23，所以這個邏輯判斷式改為“MID（D2，1，2）<=”23“”即可。當這個邏輯判斷式得出“真”或“假”的結果以后，第二個IF函數要么執行“CHOOSE（MID（D2，1，2）-20，”遼寧“，”吉林“，”黑龍江"）”，要么執行第三個IF函數，就這樣依次執行直至獲得正確結果。

三．身份證號碼驗證

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

1．驗證網站

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

2．驗證軟件

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

期和性別，能夠校驗身份證號碼的真實性。

第二篇：個人身份證識別真偽

教你識別真假居民身份證

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

居民身份證的整體識別

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

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

證件簽發機關為縣公安局、不設區的市公安局和設區的公安分局。

3.居民身份證從登記項目、填寫內容和顏色上還有其他兩種形式：

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

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

證件“出生年月日”的登記項目改為“出生日期”。如：內地和經濟特區頒發的證件，登記為“出生1949年10月1日”，民族自治地方頒發的證件則登記為“出生日期1949.10.1”。證件填寫內容分為只書寫漢字(如廣西壯族自治區和內蒙古自治區部分地區)和同時書寫

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

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

b.經濟特區頒發的居民身份證識別：

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

居民身份證有效期限與持證人年齡、簽發日期的關系

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

查驗或檢查時，應對照檢查證件有效期限與持證人年齡，簽發日期三者之間的關系。居民身份證編號識別

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

行政區劃代碼：只表示公民第一次申領居民身份證時的常住戶口所在地區。

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

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

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

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

臨時身份證識別

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

經濟特區使用的臨時身份證不加特區全息標記和熒光印刷圖案。

現有三種識別假身份證的方法：

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

2.看身份證反面右上角的條紋，從第一條條紋向下數，一直數到第四條，如果第四條條紋象其它的波浪條紋交叉的就是假身份證，沒交叉的就是真身份證，因為真身份證反面條紋除右上角的第四條唯一的一條沒有交叉，其余的都是交叉的。

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

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

5.看身份證左側的印章。所有的橫筆劃如果有收尾則可能是假的。“局”字右邊應齊頭，如果印章中的“局”字和此文中的“局”字一樣，則身份證可能是假的。

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

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

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

身份證簡易識別真偽方法

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

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

新舊版身份證真偽識別方法(詳細)

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

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

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

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

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

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

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

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

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

說明：

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

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

第三篇：身份證相關信息

一、身份證號碼執行標準：18位身份證標準在國家質量技術監督局于1999年7月1日實施的GB11643-1999《公民身份號碼》。

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

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

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

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

3.順序碼（XXX）：表示在同一地址碼所標識的區域范圍內，對同年、同月、同日出生的人編定的順序號，順序碼的奇數分配給男性，偶數分配給女性。

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

三、關于地址碼含義的詳細解釋：

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

A:國內區域

華北三省二市

東北三省

華東六省一市

華南六省

西南四省一市 6 西北五省

臺灣

港澳

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

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

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

11-15 京 津 冀 晉 蒙

21-23 遼 吉 黑

31-37 滬 蘇 浙 皖 閩 贛 魯

41-46 豫 鄂 湘 粵 桂 瓊

50-54 渝 川 貴 云 藏

61-65 陜 甘 青 寧 新

81-82 港 澳

CD:城市代碼

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

EF:市轄區、郊區、郊縣、縣級市代碼

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

其中：

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

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

有關更詳細的資料見附錄。

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

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

六、關于校驗碼

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

1.對前17位數字本體碼加權求和

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

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

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

Y = mod(S, 11)

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

3.根據模的值得到對應的校驗碼

對應關系為：

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

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

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

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

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

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

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

員工身份證鑒別管理制度

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

1．人事招聘作業規范

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

★ 要求應聘者在《職位申請表》中據實填寫個人履歷、學歷、政治背景及詳細地址。

★ 對將從事重要崗位的人員人事部要通過當地公安部門協助核實其身份以及背景調查（上一個單位），經核實合格后方可上崗。

★ 入職培訓由人事部組織安排對新招人員進行培訓，讓員工了解公司的品質系統及政策，廠規、廠紀等，派發《員工手冊》，做好記錄，存檔于人事部。

1．身份證件真假鑒別

★ 視覺鑒別：

☆ 身份證網格直接印在證身上，而假身份證網格在過膠片上。

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

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

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

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

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

★ 考慮到借他人身份證應聘者有三方面特點：

☆ 仔細審查會發現許多特點與證件不相符；

☆ 對證件內容未熟記；

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

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

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

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

★ 當證件模糊不清或一時辨認不清時則應：

☆ 詢問應聘者的家庭地址與出生年月日，有時特意報錯內容，要應聘者予以糾正；

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

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

☆ 檢查應聘者其它證書，如畢業證、計生證等。

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

3． 身份證有效性和真實性跟蹤監控管理：

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

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

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

人員處理。

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

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

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

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

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

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

如果有一個編碼和省份地區的對照表，可以用VLOOKUP函數來提取地區信息。２、提取出生日期信息

１５位：=--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“)(請將輸入公式的單元格格式設置為日期格式）３、提取性別信息

１５位：=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),”男“,”女“)４、檢驗身份證號碼的正確性 １８位身份證號碼的最后一位是檢驗碼，它是根據身份證前17位數字依照規則計算出來的，其值０～９或X。一般情況只要有一位數字輸入錯誤，依照規則計算后就會與第18位數不符。當然不排除按錯誤號碼計算后恰好與檢驗碼相符的情況，但這種情況出現的可能性較低。因此，對１８位號碼的驗證采用如下公式：

=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“))))

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

=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單元格，如果結果為TRUE，則身份證號是正確的，結果為FALSE則是錯誤的。

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

５、１５位升為１８位

=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)

６、１８位轉換為１５位

=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),地區表!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)

把應聘者或員工的個人信息錄入Excel，確保信息真實可信是必須解決的問題。一．數據錄入快又準

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

1．別讓數據變“亂”

“身份證號碼”要用“文本”格式。實現這一點的第一種方法是選中D列右擊鼠標，選擇快捷菜單中的“設置單元格格式”，打開對話框的“數字”選項卡選中，選中“分類”下的“文本”然后“確定”即可。第二種方法是在輸入的身份證號碼前加一個單引號，Excel就可以把輸入的數字變為“文本”了。第三種方法是選中D列，單擊“格式”菜單下的“單元格”命令打開對話框，按如圖1所示選中“分類”下的“自定義”。然后在“類型”框中輸入一個“@”再“確定”即可。

2．錄入校驗 錯誤靠邊

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

第二步打開“出錯警告”選項卡，在“標題”框內輸入“數據重復”，并按如圖3所示輸入更詳細的警告信息，單擊“確定”按鈕將打開的對話框關閉。當然，這一步是可選的，使用時可以根據具體情況取舍。

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

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

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

3．錄后檢查 萬無一失

假如上面的操作執行前已經錄入了部分數據，那么有沒有辦法檢查錄入的身份證號碼是否重復？可以設計一個帶有公式的“條件格式”，圓滿解決問題。

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

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

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

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

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

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

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

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

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

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

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

2．生日

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

3．年齡

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

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

4．籍貫

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

=IF（MID（D2，1，2）<=”15“，CHOOSE（MID（D2，1，2）-10，”北京“，”天津“，”河北“，”山西“，”內蒙古“），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，”陜西“，”甘肅“，”青海“，”寧夏“，”新疆“））））））

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

三．身份證號碼驗證

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

1．驗證網站

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

2．驗證軟件

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