Q2 |
櫃號檢查-巨集一一檢查版 |
||||
示範檔 |
無 |
範例檔 |
Q2.XLSX |
結果檔 |
Q2F.XLSX |
錦子老師,您好,
因工作需要,需常檢查貨櫃號碼有無key錯,但需自行一個一個copy&paste上去。但我想知道是否可以把它編寫成程式,放入Excel中呢?
檢查碼公式如下:
Ex.櫃號APHU4517446,其檢查碼為尾端的6。
1.先將前英文字母部份換成值(英文字母代碼對照表如下表)。
A |
B |
C |
D |
E |
F |
G |
H |
I |
10 |
12 |
13 |
14 |
15 |
16 |
17 |
18 |
19 |
J |
K |
L |
M |
N |
O |
P |
Q |
R |
20 |
21 |
23 |
24 |
25 |
26 |
27 |
28 |
29 |
S |
T |
U |
V |
W |
X |
Y |
Z |
|
30 |
31 |
32 |
34 |
35 |
36 |
37 |
38 |
|
2.位數之乘數
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
0 |
1 |
2 |
4 |
8 |
16 |
32 |
64 |
128 |
256 |
512 |
3.APHU英文部份換算成代碼為10,27,18,32。
4.然後將四個英文+6個數字共後的6個數字。
(第1碼的值*1+第2碼的值*2+第3碼的值*4+第4碼的值*8+第5碼的值*16+第6碼的值*32+第7碼的值*64+第8碼的值*128+第9碼的值*256+第10碼的值*512+)除以11。
所以10*1+27*2+18*4+32*8+4*16+5*32+1*64+7*128+4*256+4*152=4648。
求出4648後,除11=442.545454。
取小數點後0.545454*11=5.99999(A),再四捨五入到整數值得出6。
6就是檢查碼,也就是櫃號尾碼。
若(A)值大於或等於10時,需再減10,則檢查碼則為0或1。
錦子老師,若將櫃號(不含檢查碼)放於A欄,自動計算後之檢查碼自動放入B欄,可以這樣的嗎?
當看到上面這位讀者的來信,一堆堆運算條件真是恐佈,其實認真看完後覺得沒什麼,只是我們要將其分解成一些動作。
1.首先啟動Excel並開啟該活頁簿。
2.按ALT+F11鍵啟動VBA視窗。
3.點取「插入 > 模組」指令,開啟一個新的模組。
4.在模組中輸入巨集內容,如下所示:
Sub TEU()
Dim S, T, U, X, Y, NO, TOTAL
A = InputBox("請輸入櫃號")
NO = Mid(A, 5, 6)
For T = 1 To 4
S = UCase(Mid(A, T, 1))
Select Case S
Case "A"
U = 10
Case "B"
U = 12
Case "C"
U = 13
Case "D"
U = 14
Case "E"
U = 15
Case "F"
U = 16
Case "G"
U = 17
Case "H"
U = 18
Case "I"
U = 19
Case "J"
U = 20
Case "K"
U = 21
Case "L"
U = 23
Case "M"
U = 24
Case "N"
U = 25
Case "O"
U = 26
Case "P"
U = 27
Case "Q"
U = 28
Case "R"
U = 29
Case "S"
U = 30
Case "T"
U = 31
Case "U"
U = 32
Case "V"
U = 34
Case "W"
U = 35
Case "X"
U = 36
Case "Y"
U = 37
Case "Z"
U = 38
End Select
If T = 1 Then
X = U
Else
If T = 2 Then
X = X + (U * 2)
Else
If T = 3 Then
X = X + (U * 4)
Else
X = X + (U * 8)
End If
End If
End If
Next
TOTAL = X + (Mid(NO, 1, 1) * 16) + (Mid(NO, 2, 1) * 32) + (Mid(NO, 3, 1) * 64) + (Mid(NO, 4, 1) * 128) + (Mid(NO, 5, 1) * 256) + (Mid(NO, 6, 1) * 512)
Y = Round(((TOTAL / 11) - Int(TOTAL / 11)) * 11, 0)
Z = CInt(Mid(A, 11, 1))
If Y <> Z Then
MsgBox "您的櫃號有誤"
End If
End Sub
5.點取Excel視窗,再點取「檢視 > 巨集 > 巨集」下拉方塊,選擇「檢視巨集」指令,如下圖所示。
6.在「巨集名稱」列示方塊中點取「TEU」項目,再點取「執行」鈕,如下圖所示,開啟「Microsoft Excel」對話方塊。
7.輸入櫃號,如下圖所示。
8.點取「確定」鈕,若櫃號有問題會出現如下圖的對話方塊,若櫃號沒問題,則不會出現任何對話方塊。
留言列表