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

在系統中尤其是在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。

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)*{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;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}。

MID(A2,ROW(:),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)*{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)*{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)*{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位數字，如果相等，代表身份證號碼合法，否則是不合法。

做人事統計表格時，會發現輸入身份證號的時候顯示出來的不是你輸入的身份證數字串，而是很怪的一串數字加英文加符號的字符串。

為什么會這樣？身份證號是18位數字，而在Excel表格中超過12位的數字串都會自動轉化為科學記數法來顯示。因此不能直接去輸入18位數字。這對于剛接觸Excel的新手來說確實不知所措，以下就以實際操作的方式分享幾種操作簡單的解決方法。

第一步：輸入法在英文半角狀態下輸入一個單引號 ' 接著再輸入身份證號。

OK。

還有一種方法，先選中要輸入身份證號的單元格，把單元格格式設置為文本，然后再輸入身份證號。

圖，在EXCEL表格中經常會碰到輸入的身份證號不能正確顯示的情況，該如何解決呢？建立的檔案中，年齡會每年改變，龐大的數據一個一個更新肯定是不科學的，有什么好的方法呢？下面我以2007版本為例講一下步驟：

一、正確顯示身份證號的方法

1、文本法：

①選中需要輸入的表格，右鍵單擊鼠標，選擇“設置單元格格式”

②選擇“數字”選項卡----“分類"里選擇”文本“選項

③此時輸入即可正確顯示

2、自定義法：如以上設置，在分類里選擇“自定義”--“類型”里選擇@

3、符號法：直接在身份證號前加一個英文狀態的單引號

二、根據身份證號顯示年齡的方法：

1、選中需要顯示年齡的單元格輸入=YEAR(NOW())-MID（A2,7,4）

2、回車后結果如圖

3、用填充格式填充下部即可。

后記：在EXCEL表中如果是根據身份證號顯示年齡的，需要把本地的時鐘設置正確才行，需要用的函數是YEAR（）--可以得到某個日期的年份

NOW（）--可以得到當前電腦系統中的日期，

MID--可以從字符串中得到指定數量的字符

“MID(A2,7,4)”表示得到從A2單元格中第7位字符開始的共4位字符，即身份證中的出生年份信息。這樣，用“電腦中的年份”減去“身份證中的出生年份”即得到該人員的實際年齡。

如有不明白的或有更簡捷高效的方法請關注我的頭條號私信或留言共同學習，謝謝！