我有個要好的姐妹在某旅遊景區工作，她對我埋怨，她說有一天一個五十多歲的老同志帶著一個教師資格證來打折進旅遊區，但老同志的教師資格證書上的身份證號碼是15位的，但他本人的身份證號碼卻是18位的，當時她不讓進，因為公司明確規定，如果懷疑他人借用別人證件打折，卻不阻止的話，會受到嚴重的懲罰的。她擔心老同志拿的證件不是同一個人的話，之後被查出來的話公司會給她處罰，老同志老伴死活在那爭辯，你們那赫然寫著教師憑教師資格證打折，為何拿證卻不給打折。她左右為難，跟小編訴苦，小編說可以到網上進行轉換，對照是不是他本人身份證號就知道了，但他們旅遊景點的電腦卻不連網，沒有辦法到網上進行轉換。那麼，為了驗證證件上的號碼到底是不是他本人的號碼，小編教她用Excel進行轉換，然後與現在的身份證號碼進行對比，就能達到目的了。那麼，大家就來學學小編是如何做到的吧！
工具/原料
Microsoft Excel 2003及以上
REPLACE函數
MID函數
MOD函數
SUM函數
INDEX函數
方法/步驟
首先咱們分析一下18位公民身份證號碼的組成（百度百科搜索“居民身份證號碼”得出的參考，以圖為證）：
編號識別
1、身份證編碼規則如下：根據〖中華人民共和國國家標準GB11643-1999〗中有關公民身份號碼的規定，公民身份號碼是特徵組合碼，由十七位數字本體碼和一位數字校驗碼組成。
順序碼（身份證第十五位到十七位）是縣、區級政府所轄派出所的分配碼，每個派出所分配碼為10個連續號碼，例如“000-009”或“060-069”，其中單數為男性分配碼，雙數為女性分配碼，如遇同年同月同日有兩人以上時順延第二、第三、第四、第五個分配碼。如：007的就是個男生，而且和他同年月日生的男生至少有兩個，他們的後四位是001*和003*。分配順序碼中“999、998、997、996”四個順序號分別為男女性百歲以上老人專用的特定編號。
校驗碼（身份證最後一位）是根據前面十七位數字碼，按照ISO7064:1983.MOD11-2校驗碼計算出來的檢驗碼。
2、從1999年10月1日起，全國實行公民身份證號碼制度，居民身份證編號由原15位升至18位。前6位為地址碼；第七位至14位為出生日期碼，此碼由6位數改為8位數，其中年份用4位數表示；第15位至17位為順序碼，取消了順序碼中對百歲老人使用的特定編號；第十八位為校驗碼，主要是為了校驗計算機輸入公民身份證號碼的前17位數字是否正確，其取值範圍是0至10，當值等於10時，用羅馬數字符χ表示。
為了說明這一點，我特意翻了我某親戚的老身份證得出一個15位的老身份證號碼522634520829128來進行分析，果不其然，522634就是貴州省雷山縣的行政編碼，520829是他的出生日期，後面3位我想應該就是順序碼了：
那麼，我們只需將原來的6位出生日（如上面這個號碼的出生日期是520829）期變為現在的8位出生日期（把出生年改成4位，變為19520829），號碼就變成17位的號碼，即52263419520829128，再在末位加上校驗碼就變為18位身份證號碼了。那麼，我們再到百科去看一下校驗碼的計算方法：
咱們複製出“校驗碼的計算方法”出來，認真研究下是怎麼回事：
1、將前面的身份證號碼17位數分別乘以不同的係數。從第一位到第十七位的係數分別為：7－9－10－5－8－4－2－1－6－3－7－9－10－5－8－4－2。
2、將這17位數字和係數相乘的結果相加。
3、用加出來和除以11，看餘數是多少？
4、餘數只可能有0－1－2－3－4－5－6－7－8－9－10這11個數字。其分別對應的最後一位身份證的號碼為1－0－X －9－8－7－6－5－4－3－2。
5、通過上面得知如果餘數是3，就會在身份證的第18位數字上出現的是9。如果對應的數字是10，身份證的最後一位號碼就是羅馬數字x。
例如：某男性的身份證號碼為【53010219200508011x】， 我們看看這個身份證是不是合法的身份證。
首先我們得出前17位的乘積和【(5*7)+(3*9)+(0*10)+(1*5)+(0*8)+(2*4)+(1*2)+(9*1)+(2*6)+(0*3)+(0*7)+(5*9)+(0*10)+(8*5)+(0*8)+(1*4)+(1*2)】是189，然後用189除以11得出的結果是189/11=17----2，也就是說其餘數是2。最後通過對應規則就可以知道餘數2對應的檢驗碼是X。所以，可以判定這是一個正確的身份證號碼。
那麼，我們按照百科所講的方法，絕對能夠算出校驗碼，那麼，我們就到Excel中考慮一下怎麼用函數實現這一過程吧：
咱們就以我拿的這個15位身份證號碼為例，來一步一步分析怎麼實現，最後再思考怎麼用一個公式實現吧！
首先用公式來實現第3步的插入“19”，咱們只需用Excel的REPLACE函數進行字符串替換就行了，輸入的公式是=REPLACE(A13,7,,19)：
然後，咱們來考慮第4步中校驗碼的計算方法要如何用公式實現，咱們得先把上面得出的17位數分別取出來放在17個單元格中，方面操作，那麼，首先解決的問題是如何把他分別取出來呢？這裡我想到用mid公式取數，截圖給大家看看，我想應該不用說明了吧：
然後拖動填充全部取出17位對應的數：
咱們來完成第4步的第2”將這17位數字和係數相乘的結果相加“，那麼，同樣地，先把17位校驗碼依次寫在電子表格的某個區域內（如圖中的C7:S7），然後對應相乘的結果放在某單元格內（如圖中的C12:S12），同樣拖動填充乘積（下圖的第二張圖所示），最後再求和（下圖第三張圖片）：
用公式完成第4步的第3”用加出來和除以11，看餘數是多少？“，公式是輸入=MOD(A10,11)，得出餘數是7：
同樣地，咱們用一一對應關係，將餘數與校驗碼對應起來，7對應的自然就是5了：
這裡咱們用一個公式取值試試，輸入公式=INDEX(C16:M16,1,A13+1)，就得到對應的值了，來解讀下這個公式：這個公式的第一個參數是數據區域，第二個參數是相對數據區域中的第幾行，因為只有一行，所以我們填1，第三個參數是第幾列，取A13的數值+1，為什麼加1？因為上面C14：M14區域的序列是從0開始的咱們數個數他就是在原基礎上加1：
之後用“&”連接字符串將前面的17位與校驗碼連接起來，即得出18位的身份證號碼了：
通過分析最後是得出18位的新身份證號碼了，但是，我們要怎麼把這些步驟的公式合併起來呢？
咱們就以這個表為例，假設在A2處存放的是15位身份證號碼，需要在B2處轉換為18位身份證號碼：
一、按照第5步方法，先添上“19”，即得出REPLACE(A2,7,,19)
二、第6步中，咱們用mid函數分別將17位數取出來，可以這樣表達：
mid(REPLACE(A2,7,,19),1,1),mid(REPLACE(A2,7,,19),2,1),mid(REPLACE(A2,7,,19),3,1)...mid(REPLACE(A2,7,,19),17,1)
三、第8步將這17位數字和係數相乘的結果相加，即：
sum(mid(REPLACE(A2,7,,19),1,1)*7+mid(REPLACE(A2,7,,19),2,1)*9+...+mid(REPLACE(A2,7,,19),17,1)*2)。
四、第9步中將結果進行求餘：mod(sum(mid(REPLACE(A2,7,,19),1,1)*7+mid(REPLACE(A2,7,,19),2,1)*9+...+mid(REPLACE(A2,7,,19),17,1)*2)，11).
五、第11步中，我們用index取值，但想一下，其數據都在每個單元格內，現在數據變為了一個整體，我們怎麼辦呢？不訪直接把數據放進來代替C16:M16，那麼，第11步的公式可改為：INDEX({1,0,"X",9,8,7,6,5,4,3,2},1,A13+1),而A13+1的數據是咱們分步中的求餘，用上面的求餘表達式代替，公式變為：
INDEX({1,0,"X",9,8,7,6,5,4,3,2},1,mod(sum(mid(REPLACE(A2,7,,19),1,1)*7+mid(REPLACE(A2,7,,19),2,1)*9+...+mid(REPLACE(A2,7,,19),17,1)*2)，11))，這就可以求出效驗碼了。
六、然後再將原來的17位（REPLACE(A2,7,,19)）和校驗碼（INDEX({1,0,"X",9,8,7,6,5,4,3,2},1,mod(sum(mid(REPLACE(A2,7,,19),1,1)*7+mid(REPLACE(A2,7,,19),2,1)*9+...+mid(REPLACE(A2,7,,19),17,1)*2)，11)+1)）進行字符串連接起來，就變為18位號碼了，即(REPLACE(A2,7,,19))&(INDEX({1,0,"X",9,8,7,6,5,4,3,2},1,(MOD(SUM(MID(REPLACE(A2,7,,19),1,1)*7+MID(REPLACE(A2,7,,19),2,1)*9+...+MID(REPLACE(A2,7,,19),17,1)*2),11))+1))。
最後的結果如下截圖第2張所示：
最後為了說明公式是正確的，我們把公式放在下面的某個單元格中，已把最後的身份證號碼顯示出來為證：
當然，我們在電子表格輸入公式時不能有省略號“...”的，最後完整的公式是
=(REPLACE(A2,7,,19))&(INDEX({1,0,"X",9,8,7,6,5,4,3,2},1,(MOD(SUM(MID(REPLACE(A2,7,,19),1,1)*7+MID(REPLACE(A2,7,,19),2,1)*9+MID(REPLACE(A2,7,,19),3,1)*10+MID(REPLACE(A2,7,,19),4,1)*5+MID(REPLACE(A2,7,,19),5,1)*8+MID(REPLACE(A2,7,,19),6,1)*4+MID(REPLACE(A2,7,,19),7,1)*2+MID(REPLACE(A2,7,,19),8,1)*1+MID(REPLACE(A2,7,,19),9,1)*6+MID(REPLACE(A2,7,,19),10,1)*3+MID(REPLACE(A2,7,,19),11,1)*7+MID(REPLACE(A2,7,,19),12,1)*9+MID(REPLACE(A2,7,,19),13,1)*10+MID(REPLACE(A2,7,,19),14,1)*5+MID(REPLACE(A2,7,,19),15,1)*8+MID(REPLACE(A2,7,,19),16,1)*4+MID(REPLACE(A2,7,,19),17,1)*2),11))+1))
注意事項
本經驗旨在教大家如何綜合運用Excel的一些公式進行復雜的運算