國際標準書號(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 者的和。


arrow
arrow
    文章標籤
    Excel SUMPRODUCT MOD
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()