2011年11月19日 星期六

Excel-ISBN檢核練習

每一本書的背面都有大家熟悉的 ISBN(國際標準書碼),利用這個 ISBN,我們要在 Excel 中練習檢核輸入的 ISBN 是否正確。首先,介紹檢核碼的計算方法。

【檢核碼的計算方法】

以下資料取自:http://zh.wikipedia.org/wiki/國際標準書號

(1) 10碼

假設某國際標準書號號碼前 9 位是:7-309-04547

計算加權和S:S = 7×10 + 3×9 + 0×8 + 9×7 + 0×6 + 4×5 + 5×4 + 4×3 + 7×2 = 226

計算 S ÷ 11 的餘數M:M = 226 mod 11 = 6

計算11 - M 的差N:N = 11 − 6 = 5

如果N = 10,校驗碼是字母「X」

如果N = 11,校驗碼是數字「0」

如果N為其他數字,校驗碼是數字N。

所以,本書的校驗碼是5,故該國際標準書號為 ISBN 7-309-04547-5。

(2) 13位

假設某國際標準書號號碼前 12 位是:978-986-181-728

計算加權和S:S = (9x1)+(7x3)+(8x1)+(9x3)+(8x1)+(6x3)+(1x1)+(8x3)+(1x1)+(7x3)+(2x1)+(8x3) = 164

計算 S÷10 的餘數M:M = 164 mod 10 = 4

計算10 - M 的差N:N = 10 − 4 = 6

如果N = 10,校驗碼是數字「0」

如果N為其他數字,校驗碼是數字N。

所以,本書的校驗碼是6。完整的國際標準書號號碼為 ISBN 978-986-181-728-6

 

【實作練習一:10碼檢核】

根據上述的檢核方式,設計以下的做法:

儲存格C2:=MID($A$2,ROW(1:1),1)

複製儲存格C2,貼至儲存格C2:C11。

儲存格F2:=SUM(E2:E11)

儲存格F4:=MOD(F2,11)

儲存格F6:=IF(F4=0,"正確","錯誤")

如果不想這麼複雜的過程來處理,而想以一個儲存格來檢核,該如何處理?需要用到陣列公式!

儲存格F6:{=IF(MOD(SUM(MID(A2,ROW(1:10),1)*(11-ROW(1:10))),11)=0,"正確","錯誤")}

陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

公式解釋:

MID(A2,ROW(1:10),1):取出儲存格A2中的每一位數。

11-ROW(1:10):產生 10, 9, 8, … , 2, 1 的數列。

SUM(MID(A2,ROW(1:10),1)*(11-ROW(1:10))):將以上二式的每個數字兩兩相乘,再全部加總。

利用 MOD 函數求得除以 11 的餘數。如果餘數為 0,則顯示「正確」,如果餘數不為 0,則顯示「錯誤」。

 

【實作練習二:13碼檢核】

儲存格C14:=MID($A$14,ROW(1:1),1)

複製儲存格C14,貼至儲存格C14:C26。

儲存格F14:=SUM(E14:E26)

儲存格F16:=MOD(F14,10)

儲存格F18:=IF(F16=0,"正確","錯誤")

如果不想這麼複雜的過程來處理,而想以一個儲存格來檢核,該如何處理?需要用到陣列公式!

儲存格F18:{=IF(MOD(SUM(MID(A14,ROW(1:13),1)*(MOD(MOD(ROW(1:13),2)+2,3)+1)),10)=0,"正確","錯誤")}

陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

公式解釋:

公式原理參考實作練習一。其中:

MOD(MOD(ROW(1:13),2)+2,3)+1:可以產生 1, 3, 1, 3, …, 3, 1 的數列。

沒有留言:

張貼留言

檢視其他文章

好康東東