基于Excel實現書號校驗碼的驗證

2016-09-05 09:53:57王劍波鐘勁松

中國管理信息化 2016年13期

王劍波，鐘勁松

（湖南教育出版社，長沙410007）

基于Excel實現書號校驗碼的驗證

王劍波，鐘勁松

（湖南教育出版社，長沙410007）

本文利用Excel軟件實現書號(ISBN 10，13位)校驗碼的驗證，并給出了具體的流程圖和主要算法語句。本系統可批量驗證書號和校驗碼的正確性，提高了出版工作的效率。

Excel；ISBN；校驗碼

1　書號（ISBN）簡介

書號，即國際標準書號（Intetnationa1 Standatd Book Numbet），簡稱ISBN，是國際通用的圖書或獨立的出版物代碼，是出版物唯一的身份標識，是出版物發行和流通的國際通行證，其重要性相當于一個人的身份證和護照。

國際標準書號號碼由13位數字組成，并以四個連接號或四個空格加以分割，每組數字都有固定的含義。2007年1月1日之前圖書的標準書號是10位，2007年1月1日以后，所有的10位書號圖書前加上“978”后重新計算校驗碼，轉換成13位書號的形式。圖1為某圖書的書號，書號中的具體段的含義如下：

第一組：978；

第二組：國家、語言或區位碼；

圖1　某圖書書號條碼

第三組：出版社代號，由國家或地區的國際標準書號分配中心分給出版社；

第四組：圖書編號，由出版社具體給出；第五組：校驗碼，只有一位，從0到9。

2　校驗碼的作用

ISBN的最后一位數字是校驗碼（check-digit），校驗碼用來檢驗前面數字是否準確，這也是辨別盜版書籍、識別真假、保護知識產權的一種有效方法。

3　校驗碼的計算

3.1圖書書號10位校驗碼的算法

第一步計算加權和：將ISBN號碼前9位數字分別乘以10，9，8，7，6，5，4，3，2，然后將它們相加，得到加權和（設該加權和為S）；

第二步計算余數：將第一步得到的和（S）除以11，取其余數（設該余數為M）；

第三步計算校驗碼：用11減去第二步得到的余數（M），所得之差就是校驗碼（N）。

說明：校驗碼只有一位，從0到10，其中余數是10時校驗碼用X代替。

［例1］書號為7-5064-2595-5的圖書校驗碼計算方法如圖2所示。

圖2　圖書校驗碼計算方法（例1）

3.2圖書書號13位校驗碼的算法

第一步計算加權和：用1分別乘ISBN的前12位中的奇數位，用3乘以偶數位，然后將兩者相加，即得到加權和（設該加權和為S）；

第二步計算余數：即將第一步得到的加權和除以10，取其余數（設該余數為M）；

第三步計算校驗碼：即用10減去第二步得到的余數，所得之差就是校驗碼。同樣，余數為10時校驗碼用X表示。

［例2］書號為978-7-5064-2595-7的圖書校驗碼的計算方法如圖3所示。

圖3　圖書書號校驗碼計算方法（例2）

4　基于ExCel實現圖書校驗碼的驗證

圖書的校驗碼的驗證框圖如圖4所示。

圖4　圖書書號校驗碼驗證框圖

利用Exce1實現書號校驗碼驗證的主要代碼如下：

第一步：標準化輸入的書號，去掉單元格A3中書號的“-”和空格””符號。

B3=SUBSTITUTE（（SUBSTITUTE（A3，"-"，""）），""，""）第二步：判斷B3單元格中書號的位數，如果書號的位數不是10或13，且書號的前9位或者前12位必須為數字，返回數值“0”，否則，返回數值“1”。

=IF（ISERROR（B3*1），0，IF（OR（LEN（B3）=10，LEN（B3）= 13），1，0））

第三步：如果B3單元格返回值為“1”，表示書號的位數正確，且由數字組成，否則顯示書號錯誤。接下來對書號校驗碼進行計算，計算時先判斷書號的長度，如果為10位，則采用10位校驗碼的計算方法；如果為13位，則采用13位校驗碼的計算方法。

//ISBN10位數的校驗碼計算方法

=IF（（11-MOD（SUMPRODUCT（MID（B3，｛1；2；3；4；5；6；7；8；9｝，1）*（｛10；9；8；7；6；5；4；3；2｝）），11））=10，”X”，（11-MOD（SUMPRODUCT（MID（B3，｛1；2；3；4；5；6；7；8；9｝，1）*（｛10；9；8； 7；6；5；4；3；2｝）），11））

//ISBN13位數的校驗碼計算方法MOD10

=10-MOD（SUMPRODUCT（MID（B3，｛1；2；3；4；5；6；7；8；9；10；11；12｝，1）*（｛1；3；1；3；1；3；1；3；1；3；1；3｝）），10）

第四步：比較單元格C3中的數字或者符號，是否與B3最后一個數字或者字母相同。如果相同，則返回“校驗碼正確”，否則返回“校驗碼錯誤”，并把結果顯示在單元格D3中。

//比較結果，計算結果是否與單元格B3最后一位數字或符號相同

=IF（IF（RIGHT（B3，1）="X"，VALUE（C3）=10），

"校驗碼正確"，IF（C3=VALUE（RIGHT（B3，1）），"校驗碼正確"，"校驗碼錯誤"））

本系統不僅可以批量地檢測圖書的檢驗碼，還可以生成新的正確的校驗碼，如圖5所示。