導(dǎo)讀近日，有人遇到需要把15位的身份證號變?yōu)?8位的問題。其實很早聽說過身份證號數(shù)字間是有邏輯關(guān)系的，但具體是什么關(guān)系，沒太弄清，正好借著這個機會，給整明白了。....

近日，有人遇到需要把15位的身份證號變?yōu)?8位的問題。其實很早聽說過身份證號數(shù)字間是有邏輯關(guān)系的，但具體是什么關(guān)系，沒太弄清，正好借著這個機會，給整明白了。干脆整理記錄下來，以后遇到需要核對身份證號是否正確時也能用得上。

身份證號數(shù)字代表的含義

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

身份證號15位變18位，或者18位身份證號的復(fù)核是否正確，都是通過計算第18位的校驗碼來進行處理。校驗碼的計算邏輯為：

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

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

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

函數(shù)計算公式

1、補充19：

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

2、計算校驗碼：

17位第第7、8位為19，按校驗碼計算規(guī)則，分別乘以2和1，相加的結(jié)果為11，對最后總合計除以11的余數(shù)不影響，所以可以直接用15位的數(shù)據(jù)與“7;9;10;5;8;4;6;3;7;9;10;5;8;4;2”相乘后合計。數(shù)組相乘求和，SUMPRODUCT正好派上用場。接下來是如何將15位身份證號設(shè)置為數(shù)組的問題，利用MID(ref,ROW($B$1:$B$15),1)可以轉(zhuǎn)變?yōu)?5個數(shù)字組成的數(shù)組。然后利用求余數(shù)函數(shù)mod(ref,11)，求得余數(shù)。最后根據(jù)余數(shù)在數(shù)組里取得相應(yīng)的值，從一位數(shù)組取值就需要用到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條件語句為避免為空時出現(xiàn)錯誤碼。

18位身份證號復(fù)核

首先將前17位取出形成數(shù)組，根據(jù)驗證碼邏輯關(guān)系，求出對應(yīng)的驗證碼，=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位進行對比，相同及復(fù)核通過，不同則有誤。

=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),"","錯誤")

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

總結(jié)

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