每一本書的背面都有大家熟悉的 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 的數列。
留言列表