close

Q2

櫃號檢查-簡易版

示範檔

範例檔

Q2.XLSX

結果檔

Q2F.XLSX

錦子老師,您好,

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

檢查碼公式如下:

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

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

10

12

13

14

15

16

17

18

19

20

21

23

24

25

26

27

28

29

 

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,則檢查碼則為01

錦子老師,若將櫃號(不含檢查碼)放於A欄,自動計算後之檢查碼自動放入B欄,可以這樣的嗎?

當看到上面這位讀者的來信,一堆堆運算條件真是恐佈,其實認真看完後覺得沒什麼,只是我們要將其分解成一些動作。

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

10

12

13

14

15

16

17

18

19

20

21

23

24

25

26

27

28

29

30

31

32

34

35

36

37

38

1.首先我們新增一張空白工作表(英文數字對照表),建立一個英文(A)與數字(B)的對照表,如下圖所示。

.櫃號檢查

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

1.首先我們要抓取A3儲存格櫃號的第一個英文字A所代表的數字,點取工作表1B3儲存格,輸入公式「=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,使檢查碼為10,則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用自訂函數來做才可以,否則會很痛苦。


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()