2017年8月23日 星期三

Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)

國際標準書號(International Standard Book Number)簡稱 ISBN,是由 13 碼所組成。參考維基百科(https://zh.wikipedia.org/wiki/國家標準書號)上的說明:
國際標準書號號碼由 13 個碼的數字所組成,並以『-』或空格加以分隔,每組數字有其義涵。
○第一組:978或979。
○第二組:國家、語言或區位代碼
○ 第三組:出版社代碼:由各國家或地區的國際標準書號分配中心,分給各個出版社。
○ 第四組:書序碼,該出版物代碼,由出版社建立。
○ 第五組:檢驗碼,一位數,從 0 到 9。

檢驗碼為為了用來驗證 ISBN 是否正確之用,如何使用試算表來加以驗證?
ISBN 範例:9789863088363
前規則是:
1. 取前12碼計算加權的和(S)
依據下圖將每個位元乘上不同的加權(1、3)
Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)
2. 計算 S 除以 10 的餘數
147 ÷ 10 的餘數是 7。
3. 求 10 減掉餘數 = 檢驗碼
若餘數為 0,則檢驗碼=0。
本例 10-7=3,檢驗碼=3,兩者相符。
Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)
只要有一個數字變動,其檢驗碼就應該會不一樣。(下圖檢驗碼:0)
Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)

【公式設計與解析】
1. 計算數字加權和(S)
儲存格C5:=SUMPRODUCT(B1:M1*B2:M2)

2. 計算檢驗碼
儲存格C7:=MOD(10-MOD(SUMPRODUCT(B1:M1*B2:M2),10),10)
(1) MOD(SUMPRODUCT(B1:M1*B2:M2),10)
利用 MOD 函數計算加權和除以 10 的餘數。
(2) MOD(10-第(1)式,10)
10-第(1)式:計算 10 減餘數的結果。
再利用 MOD 函數計算除以 10 的餘數。
讓第(1)式結果為 0 者,本式傳回 0,否則傳回第(2)的結果。

如果你想試試一個儲存格就寫出公式,可以這樣做:
Excel-驗證ISBN是否是正確的編碼(SUMPRODUCT,MOD)
儲存格B2:
{=MOD(10-MOD(SUM((MOD(ROW(1:12),2)=1)*MID(B1,ROW(1:12),1)*1)+
SUM((MOD(ROW(1:12),2)=0)*MID(B1,ROW(1:12),1)*3),10),10)}
(1) SUM((MOD(ROW(1:12),2)=1)*MID(B1,ROW(1:12),1)*1)
計算加權乘積中要乘以 1 者的和。
(2) SUM((MOD(ROW(1:12),2)=0)*MID(B1,ROW(1:12),1)*3)
計算加權乘積中要乘以 3 者的和。

沒有留言:

張貼留言

檢視其他文章

好康東東