職場中，人力資源部門是和身份證号接觸最多的部門了，因為每個公司都需要錄入員工信息，這涉及身份證複印件的存管與身份信息的錄入，在工作中，經常會碰到身份證号碼錄入出錯的情況。

在系統中尤其是在EXCEL中錄入身份證号碼，出錯的可能性很多，如位數不等于18位、号碼中數字錯誤、後3位變為0、員工提供假身份證号碼、身份證号碼輸入重複等，我們可以從多個方面來驗證輸入的号碼是否規範，如長度是否為18位、是否輸入的文本格式、是否輸入重複等。

在EXCEL中，我們可以采用一種通用的方法來避免前述各種錯誤的産生，這種方法是從身份證号碼的編輯規則入手，對輸入的身份證号碼進行判斷，如果錯誤則不允許輸入。

身份證号碼第18位為校驗碼，它主要是來驗證前17位數字輸入的是否正确，它的取值範圍為0至10，當為10的時候用X來表示。校驗碼的計算方法如下：

1．将前面的身份證号碼17位數分别乘以不同的系數，從第1位到第17位的系數分别為：7、9、10、5、8、4、2、1、6、3、7、9、10、5、8、4、2。

身份證号碼前17位對應系數

2．将這17位數字和系數相乘的結果相加，然後除以11，餘數隻可能有0、1、2、3、4、5、6、7、8、9、10這11個數字。其分别對應的最後一位身份證的号碼為1、0、X 、9、8、7、6、5、4、3、2 (即餘數0對應1，餘數1對應0，餘數2對應X……) 。

根據校驗碼的計算方法，如果身份證号碼位數不對、一不小心把數字輸錯、後3位全為0甚至輸入其它内容的時候，通過數據驗證都可以進行錯誤提醒。

我們在EXCEL中具體演示一下（合法的号碼專門隐去了，不能發出來）：

公式如下：

=IF(VLOOKUP(MOD(SUMPRODUCT(MID(A2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0)&""=RIGHT(A2,1),"合法","不合法")

公式解析：

這是多個函數的嵌套公式，其中的思想就是和前面講的驗證方法一緻，以B2單元格公式為例。

ROW($1:$17)是生成1到17的數字，生成1列17行的數組{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}。

MID(A2,ROW($1:$17),1)是依次提取身份證号碼前17個數字，生成1列17行的數組，結果為{"1";"3";"0";"1";"8";"2";"1";"9";"8";"6";"0";"2";"1";"5";"5";"0";"3"}。

SUMPRODUCT(MID(A2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2})是将身份證号前17位分别與對應的系數相乘，然後相加得出結果，結果為287。

MOD(SUMPRODUCT(MID(A2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)是對SUMPRODUCT公式的結果（287）除以11求餘，結果為1。

VLOOKUP(MOD(SUMPRODUCT(MID(A2,ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0)&""可以視為VLOOKUP(1,{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0)，這個是VLOOKUP的基礎用法，第二個參數是2列11行的數組，用連接符“&”連接""是為了将結果轉換為文本格式，結果為0。

然後我們用IF函數嵌套，通過與身份證号碼最後一位對比，RIGHT(A2,1)是提取身份證号碼最後1位數字，如果相等，代表身份證号碼合法，否則是不合法。

,