最新消息

[公告2014/05/30] 如有需要將部落格中,任何一篇文章的程式碼使用在商業用途,請與我聯繫。

[公告2015/04/26] Line版的 iInfo程式與投資應用 群組已上線想加入的朋友們,請先查看 "入群須知" 再與我聯繫 Line : aminwhite5168,加入請告知身分與回答 "入群須知" 的問題。

[公告2018/04/22] 台北 Python + Excel VBA 金融資訊爬蟲課程,課程如網頁內容 金融資訊爬蟲班:台北班 Python 金融資訊爬蟲、EXCEL VBA 金融資訊爬蟲

[公告2019/01/08] 請注意:我再次重申,部落格文章的程式碼,是要提供各位參考與學習,一旦網頁改版請自行修改,別要求東要求西要我主動修改,你們用我寫東西賺錢了、交差了,請問有分我一杯羹嗎?既然賺錢沒分我,請問有什麼理由要求我修改,如果沒能力改,就花錢來找我上課。

[公告2019/12/01] 若各位有 Excel VBA 案子開發需求,歡迎與我聯繫,可接案處理。

[公告2020/05/22] 頁面載入速度慢,起因為部分JS來源(alexgorbatchev.com)失效導致頁面載入變慢,目前已做調整,請多見諒。

2019年10月30日 星期三

Excel VBA:身分證識別

前幾天在FB看到有人問要如何判斷身分證號,找了一下還真的有規則,本還以為只要限制字串長度為10、字串第二個字為1或2,結果還真的正規的判別方法,最後就將方法寫成Excel VBA函數來判斷。
判斷身份證字號是否正確方法如下。

Step 1. 身分證字號一共有十碼,包括起首一個大寫的英文字母與接續的九個阿拉伯數字,英文代號以下表轉換成數字,如下表。

停用代碼 英文代碼 代號 縣市 現行行政區 停用代碼 英文代碼 代號 縣市 現行行政區
A 10 臺北市 N 22 彰化縣
B 11 臺中市 O 35 新竹市
C 12 基隆市 P 23 雲林縣
D 13 臺南市 Q 24 嘉義縣
E 14 高雄市 * R 25 臺南縣 臺南市
F 15 新北市 * S 26 高雄縣 高雄市
G 16 宜蘭縣 T 27 屏東縣
H 17 桃園市 U 28 花蓮縣
I 34 嘉義市 V 29 臺東縣
J 18 新竹縣 W 32 金門縣
K 19 苗栗縣 X 30 澎湖縣
* L 20 臺中縣 臺中市 * Y 31 陽明山管理局 臺北市
M 21 南投縣 Z 33 連江縣

Step 2. 英文字後第一個數字用來區分性別,男性為1、女性為2,如A123456789為男性、U214833768為女性。

Step 3. 把英文代碼對應的代碼分成個位數跟十位數,在依序接上身分證的數字,串成11碼。
如:A123456789 變成 10123456789、U214833768 變成 28214833768。

Step 4. 將11碼的每個數值分別乘上一個權重數值,並計算其總和。
$$\begin{array} {|c|c|c|c|c|c|c|c|c|c|c|} \hline 位置 & n_1 & n_2 & n_3 & n_4 & n_4 & n_6 & n_7 & n_8 & n_9 & n_{10} & n_{11} \\ \hline 身分證號 & 1 & 0 & 1 & 2 & 3 & 4 & 5 & 6 & 7 & 8 & 9 \\ \hline 權重 & 1 & 9 & 8 & 7 & 6 & 5 & 4 & 3 & 2 & 1 & 1 \\ \hline 乘積 & 1 & 0 & 8 & 14 & 18 & 20 & 20 & 18 & 14 & 8 & 9 \\ \hline \end{array}$$
$$ 1 \times 1 + 0 \times 9 + 1 \times 8 + 2 \times 7 + 3 \times 6 + 4 \times 5 + 5 \times 4 + 6 \times 3 + 7 \times 2 + 8 \times 1 + 9 \times 1 = 130$$
上面的身分證號與權重相乘轉成陣列,並把身分證號改以變數代換。
$$n_1 \times 1 + n_2 \times 9 + n_3 \times 8 + n_4 \times 7 + n_5 \times 6 + n_6 \times 5 + n_7 \times 4 + n_8 \times 3 + n_9 \times 2 + n_{10} \times 1 + n_{11} \times 1 \\\\$$ \begin{eqnarray*} && \Rightarrow \begin{bmatrix} n_1 & n_2 & n_3 & n_4 & n_5 & n_6 & n_7 & n_8 & n_9 & n_{10} & n_{11} \end{bmatrix} \times \begin{bmatrix}1 \\ 9 \\ 8 \\ 7 \\ 6 \\ 5 \\ 4 \\ 3 \\ 2 \\ 1 \\ 1 \end{bmatrix} \end{eqnarray*}
Step 5. 總和若為10的倍數,則該身分證字號為正確格式,反之則為錯誤。
130 取 10 的餘數, 130 MOD 10 = 0,表示A123456789為正確的身分證號。

以上方法轉成程式碼。
Const sexlist As String = "男性,女性"
Const townname As String = "臺北市,臺中市,基隆市,臺南市,高雄市,新北市,宜蘭縣,桃園市,嘉義市,新竹縣,苗栗縣,臺中縣,南投縣,彰化縣,新竹市,雲林縣,嘉義縣,臺南縣,高雄縣,屏東縣,花蓮縣,臺東縣,金門縣,澎湖縣,陽明山管理局,連江縣"
Const cityid As String = "10,11,12,13,14,15,16,17,34,18,19,20,21,22,35,23,24,25,26,27,28,29,32,30,31,33"

Function 身分證驗證(cell As Range)
    Dim aa(10)
    Dim Cityname, City, Weights, Sex, result
    Dim tmp() As Byte
    Dim id As String
    Dim index As Integer, total As Integer
    
    id = cell ' Sheets(1).Cells(1, 1)
    If Len(id) <> 10 Then '判斷長度
        身分證驗證 = "長度錯誤,格式錯誤,不是身分證"
        Exit Function
    End If
    
    tmp = StrConv(id, vbFromUnicode)

    If 65 <= tmp(0) And tmp(0) >= 90 Then '判斷英文字
        身分證驗證 = "無法識別地區,格式錯誤,不是身分證"
        Exit Function
    End If
    
    If tmp(1) <> 49 And tmp(1) <> 50 Then '判斷男女
        身分證驗證 = "性別錯誤,格式錯誤,不是身分證"
        Exit Function
    End If
    
    Sex = Split(sexlist, ",") '性別列表
    Cityname = Split(townname, ",") '地區名稱列表
    City = Split(cityid, ",") '地區編號列表
    Weights = Array(1, 9, 8, 7, 6, 5, 4, 3, 2, 1, 1) '加權列表
   
    index = tmp(0) - 65
    aa(0) = City(index) \ 10
    aa(1) = City(index) Mod 10
    For i = 1 To UBound(tmp)
        aa(i + 1) = CInt(tmp(i)) - 48
    Next

    total = Application.WorksheetFunction.MMult(aa, Application.Transpose(Weights))(1)
    result = total Mod 10  '為10的倍數就是正確的身分證
    If result = 0 Then
        身分證驗證 = "正確," & Cityname(index) & Sex(tmp(1) - 49) & ",身分證號檢查總和為" & total
    Else
        身分證驗證 = "錯誤,身分證號檢查總和為" & total
    End If
End Function


檔案連結

參考資料:


沒有留言:

張貼留言