近日，有人遇到需要把15位的身份證号變為18位的問題。其實很早聽說過身份證号數字間是有邏輯關系的，但具體是什麼關系，沒太弄清，正好借着這個機會，給整明白了。幹脆整理記錄下來，以後遇到需要核對身份證号是否正确時也能用得上。

要解決15位身份證号碼變18位的問題，首先得搞明白身份證号數字的意義。這方面因為一般也用不到，所以完全弄清楚的人不多。基本上，大家都知道前面6位代表所在的省市縣，7到14位代表出生年月日。後面的4位代表啥，就不太了解了。而這正是解題的關鍵，也是本文要說的問題。身份證号第15-17位為順序号，其中17位表示性别，男為單數，女為雙數；最後一位（第18位）為校驗碼，數值為0-9和X。

身份證号15位變18位，或者18位身份證号的複核是否正确，都是通過計算第18位的校驗碼來進行處理。校驗碼的計算邏輯為：

用前17位的數字，分别乘以“7;9;10;5;8;4;1;9;6;3;7;9;10;5;8;4;2”用17個乘積數的和除以11求得餘數，根據餘數從0到10的順序，從“1,0,"x",9,8,7,6,5,4,3,2”序列中找對應順序的數據即為校驗碼數字。比如乘積和除11後的餘數為0，則校驗碼為1，餘數為3，校驗碼為x，以此類推。

而15位的身份證号出生年份沒有19，比如1985年2月2日生的，按18位号碼為：19850202，按15位則是850202，省略了前面的19；另外沒有末尾的校驗碼。

所以15位身份證号變18位，其實就是要在身份證号的第7、8位補充進入19，然後在末尾補充進入計算出的校驗碼。

1、補充19：

将原15位的号碼從第7位開始截斷為兩部分，left(ref,6)提取前6位，right(ref，9）提取後9位，然後再用&将其和19連起來，left(ref,6)&"19"&right(ref,9)，則完成了前面17位數據的整理。

2、計算校驗碼：

17位第第7、8位為19，按校驗碼計算規則，分别乘以2和1，相加的結果為11，對最後總合計除以11的餘數不影響，所以可以直接用15位的數據與“7;9;10;5;8;4;6;3;7;9;10;5;8;4;2”相乘後合計。數組相乘求和，SUMPRODUCT正好派上用場。接下來是如何将15位身份證号設置為數組的問題，利用MID(ref,ROW($B$1:$B$15),1)可以轉變為15個數字組成的數組。然後利用求餘數函數mod(ref,11)，求得餘數。最後根據餘數在數組裡取得相應的值，從一位數組取值就需要用到index(ref,n)。 校驗碼綜合計算公式為：INDEX({1,0,"x",9,8,7,6,5,4,3,2},MOD(SUMPRODUCT(MID(E5,ROW($B$1:$B$15),1)*{7;9;10;5;8;4;6;3;7;9;10;5;8;4;2}),11) 1)

其中{1,0,"x",9,8,7,6,5,4,3,2}間隔符可以為“，”，也可以為“；”；

而{7;9;10;5;8;4;6;3;7;9;10;5;8;4;2}間隔符必須為“；”。

最終，15位身份證号變18位的完整公式為：示例E5為15位身份證号所在單元格

=IF(E5<>"",LEFT(E5,6) & "19" & RIGHT(E5,9) & INDEX({1;0;"x";9;8;7;6;5;4;3;2},MOD(SUMPRODUCT(MID(E5,ROW($B$1:$B$15),1)*{7;9;10;5;8;4;6;3;7;9;10;5;8;4;2}),11) 1),"")

其中，if條件語句為避免為空時出現錯誤碼。

首先将前17位取出形成數組，根據驗證碼邏輯關系，求出對應的驗證碼，=INDEX({1;0;"x";9;8;7;6;5;4;3;2},MOD(SUMPRODUCT(MID(E5,ROW($B$1:$B$17),1)*{7;9;10;5;8;4;1;9;6;3;7;9;10;5;8;4;2}),11) 1)

然後用求出的驗證碼和身份證号第18位進行對比，相同及複核通過，不同則有誤。

=if(right(e5,1)=INDEX({1;0;"x";9;8;7;6;5;4;3;2},MOD(SUMPRODUCT(MID(E5,ROW($B$1:$B$17),1)*{7;9;10;5;8;4;1;9;6;3;7;9;10;5;8;4;2}),11) 1),"","錯誤")

以前見過有人用自定義函數進行複核，相比而言，本文的函數公式比起用VBA編寫自定義函數方法還是要簡單得多。

本文是利用身份證号校驗碼邏輯計算出校驗碼進行比對，來驗證該号碼是否合理，其實最後結果不重要，因為這種用途不會經常遇到。關鍵是公式用到的各函數是如何一步步組合實現的，這個實現的思路過程比最後得到的結果更有用。職場人學excel、工作中用excel，思路比結果更重要。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!