文章目錄

• 前言
• 身份證號(hào)校驗(yàn)
• • 校驗(yàn)規(guī)則
  • EXCEL實(shí)現(xiàn)
• 性別提取
• 籍貫提取
• 出生年月提取

前言

我們的居民身份證作為我們國(guó)家每個(gè)人唯一的身份標(biāo)識(shí)，其設(shè)計(jì)十分巧妙，18位的身份證號(hào)包含了許多信息，并且為了防止身份證錄入錯(cuò)誤，還提供了一種可靠的校驗(yàn)方式。
順帶介紹一下我們身份證的構(gòu)成。
我們以500225199203026858為例

號(hào)碼含義

500225	行政區(qū)域代碼
19920302	出生年月
685	同一地區(qū)同一天出生的順序碼（奇數(shù)男；偶數(shù)女）
8	校驗(yàn)碼

身份證號(hào)校驗(yàn)

校驗(yàn)規(guī)則

身份證號(hào)碼位數(shù) $w_i$123456789101112131415161718

系數(shù) $A_i$	7	9	10	5	8	4	2	1	6	3	7	9	10	5	8	4	2	校驗(yàn)碼

計(jì)算公式：$余數(shù)={\sum }_1^{17}(w_i?A_i)\%11$

根據(jù)余數(shù)區(qū)查詢對(duì)應(yīng)得校驗(yàn)碼表

余數(shù)012345678910

校驗(yàn)碼	1	0	X	9	8	7	6	5	4	3	2

以上就是身份證的校驗(yàn)。

EXCEL實(shí)現(xiàn)

我們需要在EXCEL中實(shí)現(xiàn)這個(gè)校驗(yàn)，在輸入身份證號(hào)后能夠去校驗(yàn)并且能夠進(jìn)行提示
如圖所示：

在身份證驗(yàn)證結(jié)果中輸入公式：

=IF(MID("10X98765432",(MOD(SUMPRODUCT(MID(A14,ROW(INDIRECT("1:17")),1)* {7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1),1)=RIGHT(A14),TRUE,FALSE)

首先解釋一下幾個(gè)關(guān)鍵的函數(shù)：
INDIRECT(引用區(qū)域，引用格式）：

• 解釋: 用于引用，一般分為兩種情況，引用文本或者引用內(nèi)容，第二個(gè)參數(shù)缺省值為True。
• 例如 對(duì)于B2 (此單元格的內(nèi)容為A1)，如果在某個(gè)單元格輸入=INDIRECT（B2 ），則得到A1的實(shí)際值；如果輸入=INDIRECT（“B2” ）,則得到B2這個(gè)文本值
• 這里主要是用于以文本格式

MID(text, start_num, num_chars):

• 從一個(gè)字符串中截取出指定數(shù)量的字符

ROW(refrence):

• 獲取引用的單元格的行號(hào)
• 這里的用法相當(dāng)巧妙，ROW(INDIRECT(“1:17”)生成了1…17的行號(hào)，生成一個(gè)數(shù)組{1；2；…17}

SUMPRODUCT(array1, [array2], [array3], …):

• 數(shù)組對(duì)應(yīng)相乘再求和。

MOD(a,b)
-a/b求余

IF函數(shù)之流不必解釋了，進(jìn)行一頓操作之后，將計(jì)算出來的校驗(yàn)碼與身份證號(hào)中的最后一位RIGHT(身份證號(hào))進(jìn)行相等判斷，即可得出身份證是否爭(zhēng)取輸入身份證號(hào)。

性別提取

身份號(hào)中的性別是順序碼的奇偶性進(jìn)行判斷的，奇數(shù)分配給男性，偶數(shù)分配給女性，于是，只需要提取第17位號(hào)碼，進(jìn)行2的求余，若為1，則為男性，反之，則為女性。
上代碼：

=IF(MOD(MID(A10,17,1),2),"男","女")

籍貫提取

身份證號(hào)的前5位為行政區(qū)域代碼，直接查表即可。但是存在一個(gè)不好的地方就是，此代碼隨著行政區(qū)域的變動(dòng)而變動(dòng)，例如在，1997年前，重慶未直轄，大足的代碼就是510230，后來直轄，變成了500225，再到前幾年合并雙橋區(qū)升為大足區(qū)，改為500111。所以如果登記不同年齡階段的人，那么我們就不能用同一張代碼表。
我介紹一下怎么提取。
首先下載一張比較完全的行政區(qū)域代碼表鏈接: 示例文檔 提取碼: tpgu ，此文檔中的存在一張17年的表，可直接使用。
利用vlookup函數(shù)進(jìn)行查詢填充。
具體代碼：

=VLOOKUP(MID(A14,1,6),行政區(qū)域表!A:B,2,TRUE)

出生年月提取

出生年月提取就很好理解了，直接利用DATE函數(shù)，分別提取年/月/日進(jìn)行提取。

總結(jié)

以上是生活随笔為你收集整理的EXCEL身份证号码校验以及提取基本信息的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。