excel身份證號碼正確與否（Excel中身份證號碼驗證）

2023-05-17 18:56:18 1

大家在處理員工信息的時候，應該都有處理過身份證號碼吧。身份證號碼裡包含了很多信息，包括出生地、年齡、性別、生日、生肖、星座等等。今天我們不說如何提取這些信息，說一個更重要的身份證號碼錄入，畢竟有了數據才能提取，而且錄入過程也有很多問題，讓我們一起來看看如何解決這些問題。

我們以現在主流的18位身份證號碼為例，當我們在單元格中隨便輸入一個18位數字比如：123456789012345678。

我們發現身份證號碼顯示變成科學計數展示了，而且當我們再次選中身份證號碼會發現最後3位數字都變成了0。這是因為Excel中數字的精度是15位，當數字超過15位時會用0代替。

處理方法也很簡單，這裡給兩個小方法：①輸入法切換至英文狀態下，先輸入單引號「』」再接著輸入身份證號碼即可；②右擊單元格，設置單元格格式為文本格式即可。

身份證號碼18位數字，錄入多了難免會眼花繚亂，一不小心就會多一位或少一位。

「數據驗證」可以很好地解決這種情況，選中單元格區域，點擊「數據」選項卡下「數據工具」命令組中「數據驗證」，調出數據驗證對話框，然後點擊「設置」選項卡，允許選擇「文本長度」，數據選擇「等於」，長度選擇「18」，最後點擊確定即可。

演示如下：

當位數驗證完以後，又會有一個問題：重複輸入。

選中不能重複輸入單元格區域；選擇「數據」→「數據工具」→「數據驗證」調出驗證對話框；驗證對話框中「設置」選項卡下「驗證條件」中「允許」為"自定義"，公式為「=COUNTIF(B$5:B$7,B5&"*")=1」；

我們知道身份證號碼是有一套計算規則的，下面我們就來一步一步講解計算規則。

①首先身份證號碼前17位數字各自都有其對應的係數，然後把17位數字分別和各自的係數相乘並把結果相加；

②把第一步計算的結果除以11並得到餘數。而每一個餘數都有其對應身份證號碼第18位應該是什麼數字；

③我們把上面計算出來的身份證號碼尾數和實際的對比一下即可得出這個身份證號碼是否合法。

我們舉個例子，比如身份證號碼為：321324199005161058。

①首先計算前17位數與其係數乘積之和：3*7 2*9 1*10 3*5 2*8 4*4 1*2 9*1 9*6 0*3 0*7 5*9 1*10 6*5 1*8 0*4 5*2=264；

②然後計算第一步結果除以11的餘數：264/11餘數為0，其對應的第18位數字應該是1；

③計算出來的數字和我們實際數字不匹配，因此這個身份證號碼不合法。

其實知道了計算規則以後，我們可以用很多種方式來驗證身份證的合法性，下面我們提供一個公式：「=IF(MID("10X98765432",MOD(SUMPRODUCT(MID(B5,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11) 1,1)=RIGHT(B5),"合法","不合法")」，只需要把B5單元格換成自己需要的單元格即可。

下面我們來看看演示效果：

當然還有很多其他公式或方法驗證身份證號碼合法性，大家有興趣的可以自己去嘗試嘗試。

我們依次解決了顯示問題、長度問題、重複問題、合法問題。但是其實整個過程還不完美，比如我們還可以對前6位所在地驗證，是不是一個合法的所在地；對8位日期驗證，是不是一個正確的日期。這樣才能最大限度地保證身份證的正確性。後面我會用VBA寫一個我自認為比較完美的自定義函數來驗證身份證號碼，到時候分享出來，有興趣的可以一起討論哦。