身份證正確檢驗及驗證公式

1、驗證公式一 =IF(LEN(H20)=18,IF(RIGHT(H20,1)=X,IF(CHOOSE(MOD(SUM(LEFT(RIGHT(H20,18) )*7 LEFT(RIGHT(H20,17)*9 LEFT(RIGHT(H20,16)*10 LEFT(RIGHT(H20,15)*5 LEFT(RIGHT(H20,14)*8 LEFT(RIGHT(H20,13)*4 LEFT(RIGHT(H20,12)*2 LEFT(RIGHT(H20,11)*1 LEFT(RIGHT(H20,10)*6 LEFT(RIGHT(H20,9)*3 LEFT(RIGHT(H20,8)*7 LEFT(RIGHT

2、(H20,7)*9 LEFT(RIGHT(H20,6)*10 LEFT(RIGHT(H20,5)*5 LEFT(RIGHT(H20,4)*8 LEFT(RIGHT(H20,3)*4 LEFT(RIGHT(H20,2)*2),11)1,1,0,X,9,8,7,6,5,4,3,2)=LEFT(RIGHT(H20,1),正確！ , 出錯啦！),IF(CHOOSE(MOD(SUM(LEFT(RIGHT(H20,18)*7 LEFT(RIGHT(H20,17)*9 LEFT(RIGHT(H20,16)*10 LEFT(RIGHT(H20,15)*5 LEFT(RIGHT(H20,14)*8 LEFT(R

3、IGHT(H20,13)*4 LEFT(RIGHT(H20,12)*2 LEFT(RIGHT(H20,11)*1 LEFT(RIGHT(H20,10)*6 LEFT(RIGHT(H20,9)*3 LEFT(RIGHT(H20,8)*7 LEFT(RIGHT(H20,7)*9 LEFT(RIGHT(H20,6)*10 LEFT(RIGHT(H20,5)*5 LEFT(RIGHT(H20,4)*8 LEFT(RIGHT(H20,3)*4 LEFT(RIGHT(H20,2)*2),11) 1,1,0,X,9,8,7,6,5,4,3,2)=LEFT(RIGHT(H20,1)*1, 正確！ , 出錯啦！

4、 ),IF(LEN(H20)=15, 老號，請注意！ ,IF(LEN(H20)=0, 缺號碼 , 位數(shù)不對！ )驗證公式二 =MID(10X98765432,MOD(SUMPRODUCT(MID(H20,ROW(INDIRECT(1:17),1)*2A(18 -ROW(INDIRECT(1:17),11) 1,1)=RIGHT(H20,1)身份證有15位和18位兩種，身份證位數(shù)是否正確，我們可以用 LEN函數(shù)判斷。但 身份證上的日期是否合法：月份是否在 1-12 之間，日期是否在 1-31 之間，并且 2 月份只有 28 或 29 天，其他月份 30 或 31 天，都不能超過范圍。另外一般規(guī)定

5、 6 歲 以上才可以辦理身份證，也就是年份也有一個超范圍的可能性。綜合起來看，有三 類錯誤：“身份證位數(shù)不對、“月日錯誤、“年份錯誤。假定身份證號碼在 B1 單元格，下面的公式可以綜合判斷以上三種錯誤： 驗證公式三=IF(OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MONTH(DATE(1*(MID(B1,7,4),1*(MID(B1,11,2),1*(MID(B1,13,2)1*(MID(B1,11,2),DAY(DATE(1*(MID(B1,7,4),1*(MID(B1,11,2),1*(MID(B1,13,2)1*(MID(B1,13,2)

6、,月日錯誤,),IF(LEN(B1)=15,IF(OR(MONTH(DATE(1*(MID(B1,7,2)1900,1*(MID(B1,9,2),1*(MID(B1,11,2)1*(MID(B1,9,2),DAY(DATE(1*(MID(B1,7,2) 1900,1*(MID(B1,9,2),1*(MID(B1,11,2)1*(MID(B1,11,2),月 日錯誤 ,), 身份證位數(shù)不對)&IF(AND(LEN(B1)=18,1*MID(B1,7,4)YEAR(TODAY()-6), 年份錯誤 ,)驗證公式四=IF(LEN(A1)=0,空,IF(LEN(A1)=15,老號,IF(LEN(A1)

7、v18,位數(shù)不對 ,IF(CHOOSE(MOD(SUM(MID(A1,1,1)*7 MID(A1,2,1)*9 MID(A1,3,1)*10 MID(A1,4,1)*5 MID(A1,5,1)*8 MID(A1,6,1)*4 MID(A1,7,1)*2 MID(A1,8,1)*1 MID(A1,9,1)*6 MID(A1,10,1)*3 MID(A1,11,1)*7 MID(A1,12,1)*9 MID(A1,13,1)*10 MID(A1,14,1)*5 MID(A1,15,1)*8 MID(A1,16,1)*4 MID(A1,17,1)*2),11)1,1,0,X,9,8,7,6,5,4,

8、3,2)=IF(ISNUMBER(RIGHT(A1,1)*1),RIGHT(A1,1)*1,X) , 正確, 錯誤) 公式二出處excels取身份證出生日期、性別、檢驗身份證號碼正確性中國居民身份證號碼是一組特征組合碼，原為15位，現(xiàn)升級為18位，其編碼規(guī) 那么為：15位：6位數(shù)字常住戶口所在縣市的行政區(qū)劃代碼，6位數(shù)字出生日期代碼，3 位數(shù)字順序碼。18位：6位數(shù)字常住戶口所在縣市的行政區(qū)劃代碼，8位數(shù)字出生日期代碼，3 位數(shù)字順序碼和1位檢驗碼。其中3位數(shù)字順序碼，是為同一地址碼的同年同月同日出生人員編制的順序號，偶 數(shù)的為女性，奇數(shù)的為男性。1、提取籍貫地區(qū)的行政區(qū)劃代碼(A2為身份證號

9、，下同)15與18位通用：=LEFT(A2,6)如果有一個編碼和省份地區(qū)的對照表，可以用VLOOKU函數(shù)來提取地區(qū)信息。2、提取出生日期信息15位： =-TExT(19&MID(A2,7,6),#-00-00)18位： =-TExT(MID(A2,7,8),#-00-00)15與18位通用：=-TExT(IF(LEN(A2)=15,19,)&MID(A2,7,6IF(LEN(A2)=18,2,0),#-00-00)簡化公式： =-TExT(LEN(A2)=15)*19&MID(A2,7,6 (LEN(A2)=18)*2),#-00-00)( 請將輸入公式的單元格格式設(shè)置為日期格式)3、提取性別

10、信息15位： =IF(MOD(RIGHT(A2),2)=1, 男, 女)18位： =IF(MOD(MID(A2),17,1)=1, 男, 女)15與18位通用：=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,男, 女) 簡化公式： =IF(MOD(RIGHT(LEFT(A2,17),2), 男 , 女 )4、檢驗身份證號碼的正確性18位身份證號碼的最后一位是檢驗碼，它是根據(jù)身份證前17位數(shù)字依照規(guī)那么計算出來的，其值09或X。一般情況只要有一位數(shù)字輸入錯誤，依照規(guī)那么計算后 就會與第 18 位數(shù)不符。當(dāng)然不排除按錯誤號碼計算后恰好與檢驗碼相符的情況， 但

11、這種情況出現(xiàn)的可能性較低。因此，對18位號碼的驗證采用如下公式： =MID(10X98765432,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(1:17),1)*2A(18- ROW(INDIRECT(1:17),11) 1,1)=RIGHT(A2,1) 對于15位身份證，由于沒有檢驗碼，我們只能簡單地去判斷出生日期代碼是否是 一個有效的日期，防止輸入一些像“ 731302或“980230“等這樣不存在的日期。 =ISNUMBER(-TEXT(19&MID(A2,7,6),#-00-00) 綜合15位和18位后的通用公式為： =IF(LEN(A2)=18,MID(10

12、X98765432,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(1:1 7),1)*2A(18-ROW(INDIRECT(1:17),11) 1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(-TEXT(19&MID(A2,7,6),#-00-00) 由于目前15位身份證號碼已經(jīng)很少了，如果對15位的號碼不需要作進一步的判 斷，那么公式可以簡化成： =IF(LEN(A2)=18,MID(10X98765432,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(1:1 7),1)*2A(18-ROW(INDIRECT(1:1

13、7),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è)

14、置好某單個單元格的數(shù)據(jù)有效性(這時公式的A2改為選定的單元格),再用 格式刷將其格式刷到其他需要相同設(shè)置的單元格。5、15位升為18位 =IF(LEN(A2)=15,REPLACE(A2,7,19)&MID(10X98765432,MOD(SUMPRODUCT(MID(RE PLACE(A2,7,19),ROW(INDIRECT(1:17),1)*2A(18-ROW(INDIRECT(1:17),11) 1,1),A2)6、18位轉(zhuǎn)換為15位=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)公式四出處 使用 excel 校驗身份證號碼校驗學(xué)生錄入身份證號信

15、息一直是個很頭疼的問題，現(xiàn)在學(xué)生的身份證號根本都是 18 位，利用 excel 數(shù)據(jù)有效性只能校驗位數(shù)是否正確。而對于合法性卻望塵莫及，今天查了很多資料，終于找到了一種方法，現(xiàn)介紹如下： 一、身份證編碼原理：關(guān)于身份證第 18 是怎么計算的，原理如下：根據(jù)中華人民共和國國家標(biāo)準(zhǔn) GB 11643-1999中有關(guān)公民身份號碼的規(guī)定，公民身份號碼是特征組合碼，由十七位 數(shù)字本體碼和一位數(shù)字校驗碼組成。排列順序從左至右依次為：六位數(shù) 字地址 碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位數(shù)字校驗碼。 地址碼身份證前六位表示編碼對象常住戶口所在縣市、旗、區(qū) 的行政區(qū)劃代碼。所有區(qū)域的編碼可以到這個網(wǎng)站

16、:/ /tjbz/index.htm 查詢到最新的縣及縣以上的行政編碼 資料。 生日期碼身份證第七位到第十四位表示編碼對象出生的年、月、日，其中年份 用四位數(shù)字表示，年、月、日之間不用分隔符。例如：1981年 05 月 11日就用19810511表示。順序碼身份證第十五位到十七位為同一地址碼所標(biāo)識的區(qū)域范圍內(nèi)，對同年、 月、日出生的人員編定的順序號。其中第十七位奇數(shù)分給男性，偶數(shù)分給女性。 校驗碼身份證最后一位是根據(jù)前面十七位數(shù)字碼，按照 ISO 7064:1983.MOD 11-2 校驗碼計算出來的檢驗碼。二、校驗方法 既然第十八位是校驗位，那么我們將從這里入手方能搞定。

17、 第十八位數(shù)字的計算方法為：1、將前面的身份證號碼 17 位數(shù)分別乘以不同的系數(shù)。從第一位到第十七位的系數(shù) 分別為： 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 22、將這 17 位數(shù)字和系數(shù)相乘的結(jié)果相加。3、用加出來和除以 11，看余數(shù)是多少？4、余數(shù)只可能有 0 1 2 3 4 5 6 7 8 9 10這 11個數(shù)字。其分別對應(yīng)的最后一位身份證的號碼為 1 0 X 9 8 7 6 5 4 3 2 。5、通過上面得知如果余數(shù)是 2，就會在身份證的第 18 位數(shù)字上出現(xiàn)羅馬數(shù)字的X。如果余數(shù)是10,身份證的最后一位號碼就是 2。例如：某男性的身份證號碼是 我們要看看這

18、個身份證是不 是合法的身份證。首先：我們得出,前 17位的乘積和是 189 然后：用 189除以 11 得出的結(jié)果是 17 2/11 ,也就是說余數(shù)是 2。 最后：通過對應(yīng)規(guī)那么就可以知道余數(shù) 2對應(yīng)的數(shù)字是X。所以，這是一個合格的身 份證號碼。到此已經(jīng)柳暗花明了，接下來就是寫公式了，假設(shè) eXcel 表的 A1 單元格記錄的是 身份證號碼，我們在 B1 單元格輸入如下公式：=IF(LEN(A1)=0,空,IF(LEN(A1)=15,老號,IF(LEN(A1)v18,位數(shù)不對 ,IF(CHOOSE(MOD(SUM(MID(A1,1,1)*7 MID(A1,2,1)*9 MID(A1,3,1)*10 MID(A1,4,1)*5 MID(A1,5,1)*8 MID(A1,6,1)*4 MID(A1,7,1)*2 MID(A1,8,1)*1 MID(A1,9,1)*6 MID(A1,10,1)*3 MID(A1,11,1)*7 MID(A1,12,1)*9 MID(A1,13,1)*10 MID(A1,14,1)