スプレッドシートで商品管理を行い、項目としてJANコード13桁を入力する列を作り、
それが正しいJANであるか（チェックデジットに誤りが無いか）の確認ができる数式をご紹介します。
JANコードについて少し調べてみると「チェックデジット」というものが存在し、
JANコードの13桁は先頭の12桁の数字と、末尾1桁で別々の役割を持っているということがわかります。
先頭12桁の数字を分割して特定の計算式を行えば、チェックデジットと呼ばれる13桁目の数字が導き出せる、
逆に言うと先頭12桁目の数字と、チェックデジットの数字があっていなければ「正しくないJANコード」ということがわかり、
こういった「正しくないJANコード」はPOSレジのバーコードリーダーでは読めなかったりします。
ということで、GoogleスプレッドシートやエクセルでJANコードを管理する上で、正しくないJANコードはアラートが出るようにできれば、あらかじめ入力ミスを防ぐことができます。
計算式自体は単純な四則演算のみでできますので、条件式自体は長くなりますがチェックデジット正誤はスプレッドシートやExcelでも確認可能です。
今回はGoogleスプレッドシートでの記載例をご紹介します。
まず、チェックデジットの計算式はこちら
- すべての偶数桁の数字を加算
- 1の結果を3倍
- すべての奇数桁の数字を加算
- 2の結果と2の結果を加算
- 4の結果の下一桁の数字を「10」から引いたものがチェックデジットの数値
A.まずは上記の計算式はこちら（「A1」セルにJANコード13桁が入っている場合）
=RIGHT(10-RIGHT((((MID(A1,2,1))+(MID(A1,4,1))+(MID(A1,6,1))+(MID(A1,8,1))+(MID(A1,10,1))+(MID(A1,12,1)))*3+(MID(A1,1,1))+(MID(A1,3,1))+(MID(A1,5,1))+(MID(A1,7,1))+(MID(A1,9,1))+(MID(A1,11,1))),1),1)
B.次に「A1」セルの13桁からチェックデジットを抽出する式はこちら
=(MID(A1,13,1)
上記を踏まえて、A=Bの場合、13桁のJANを入力したセルに色がつくように設定します。
JANを入力したセルを右クリック＞条件付き書式をクリック
書式ルールの「セルの書式設定の条件」タブで「カスタム書式」を選択、
下の「値または数式」箇所に以下を入力
=(RIGHT(10-RIGHT((((MID(A1,2,1))+(MID(A1,4,1))+(MID(A1,6,1))+(MID(A1,8,1))+(MID(A1,10,1))+(MID(A1,12,1)))*3+(MID(A1,1,1))+(MID(A1,3,1))+(MID(A1,5,1))+(MID(A1,7,1))+(MID(A1,9,1))+(MID(A1,11,1))),1),1))=(MID(A1,13,1))
書式設定のスタイルは、JANのチェックデジットが正しい場合の色を選択して下さい。
設定は以上です。これで、スプレッドシートの特定のセルに入力したJAN13桁のチェックデジット計算が正しい場合、入力したセルに色がつくようになりました。
また、逆に「間違った場合に色がつく」というようにしたい場合の条件付き書式のカスタム書式は以下のとおりです。
=(RIGHT(10-RIGHT((((MID(A1,2,1))+(MID(A1,4,1))+(MID(A1,6,1))+(MID(A1,8,1))+(MID(A1,10,1))+(MID(A1,12,1)))*3+(MID(A1,1,1))+(MID(A1,3,1))+(MID(A1,5,1))+(MID(A1,7,1))+(MID(A1,9,1))+(MID(A1,11,1))),1),1))<>(MID(A1,13,1))
<>
の部分が≠（ノットイコール）の意味となります。
この方法で、JANのスプレッドシートやExcelへの入力ミスが防げるようになると思います。ぜひご活用下さい。
