錦子老師

錦子老師,您好,

因工作需要，需常檢查貨櫃號碼有無key錯，但需自行一個一個copy&paste上去。但我想知道是否可以把它編寫成程式，放入Excel中呢?

檢查碼公式如下:

Ex.櫃號APHU4517446,其檢查碼為尾端的6。

1.先將前英文字母部份換成值（英文字母代碼對照表如下表）。

2.位數之乘數

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.首先我們新增一張空白工作表(英文數字對照表)，建立一個英文(A欄)與數字(B欄)的對照表，如下圖所示。

二.櫃號檢查

本來在這邊需要用一個儲存格直接寫檢查結果，但怕讀者會看不懂那麼長的公式，故將其分解為十多項動作。

1.首先我們要抓取A3儲存格櫃號的第一個英文字A所代表的數字，點取工作表1的B3儲存格，輸入公式「=VLOOKUP(MID($A3,B$2,1),英文數字對照表!$A$2:$B$27,2,0)」字串，再按Enter鍵，如下圖所示。

如果A3儲存格中的英文字有些輸入人員一個沒注意可能會輸入英文小寫字母，這時為防止這樣情形造成工作困擾，可以將MID($A3,B$2,1)改成UPPER(MID($A3,B$2,1))來防止此問題，如下圖所示。

2.點取C3儲存格輸入公式：「=VLOOKUP(UPPER(MID($A3,C$2,1)),英文數字對照表!$A$2:$B$27,2,0)」字串，再按Enter鍵。

3.點取D3儲存格輸入公式：「=VLOOKUP(UPPER(MID($A3,D$2,1)),英文數字對照表!$A$2:$B$27,2,0)」字串，再按Enter鍵。

4.點取E3儲存格輸入公式：「=VLOOKUP(UPPER(MID($A3,E$2,1)),英文數字對照表!$A$2:$B$27,2,0)」字串，再按Enter鍵。

這三個儲存格公式可以自行輸入或複製B3儲存格的公式，由使用者自行選擇，如下圖所示。

三.計算櫃號總值

1.點取F3儲存格，輸入公式「=(B3*1)+(C3*2)+(D3*4)+(E3*8)+(MID($A3,5,1)*16)+(MID($A3,6,1) *32) +(MID($A3,7,1)*64)+(MID($A3,8,1)*128)+(MID($A3,9,1)*256)+(MID($A3,10,1)*512)」字串，再按Enter鍵，如下圖所示。

2.點取G3儲存格，輸入公式「=ROUND(MOD(F$3/11,1)*11,0)」字串，再按Enter鍵，如下圖所示。

這是在計算櫃號的總值部份除以11，而將運算後的小數部份*11 ，再將整數部份四捨五入的結果 。

如果此欄的值大於或等於10時，必須將其值減10，使檢查碼為1或0，則G3儲存格公式還要再改寫成「=IF(ROUND(MOD(F$3/11,1)*11,0)>=10,ROUND(MOD(F$3/11,1)*11,0)-10,ROUND(MOD(F3/11,1)*11,0))」。

四.判斷是否為KEY錯

1.點取H3儲存格，輸入公式「=IF(VALUE(RIGHT(A$3,1))=G$3,"沒KEY錯","KEY錯")」字串，再按Enter鍵，如下圖所示。

五.刪除KEY錯資料

1.這時我們可以點取「資料 > 排序與篩選 > 篩選」圖示，讓A1~I1儲存格右方都出現三角按鈕。

2.點取「I1」儲存格三角按鈕，開啟對話中，只讓「KEY錯」選項核取方塊打勾，如下圖所示。

3.點取「確定」鈕，這時KEY錯的櫃號全部都顯示了，如下圖所示。

這時用滑鼠左鍵將這些篩選資料框選起來，再點取「常用 > 儲存格 > 刪除工作表列」指令，即可將這KEY錯資料刪除，如下圖所示。

會不會感覺得有些眼花花了呢？哈哈，這種作業一定要學習Excel VBA用自訂函數來做才可以，否則會很痛苦。