ExcelでJANコードのチェックデジットを計算する
12桁のJANコードがA2にあるなら、B2に =MOD(-SUMPRODUCT(MID(A2,ROW(INDIRECT("1:12")),1)*1,{1;3;1;3;1;3;1;3;1;3;1;3}),10) を入力します。これで13桁目のチェックデジットが求まります。以降では式の意味と、作業列を使う方法も紹介します。
結論：JANコードのチェックデジットをExcelで求める基本式
最初に、コピーしてそのまま使える完成形を示します。前提は「A2セルに12桁のJANコード（事業者コード＋商品アイテムコード）が文字列として入力されている」ことです。
=MOD(-SUMPRODUCT(MID(A2,ROW(INDIRECT("1:12")),1)*1,{1;3;1;3;1;3;1;3;1;3;1;3}),10)この式が返すのは0〜9の1桁、すなわちチェックデジットです。12桁が「490123456789」なら結果は「5」となり、完成する13桁は「4901234567895」になります。
式の構成要素は次のとおりです。
| 要素 | 役割 |
|---|---|
MID(A2,ROW(INDIRECT("1:12")),1) | 12桁を1文字ずつ取り出す |
*1 | 文字列を数値に変換する |
{1;3;1;3;…} | 左から順に1倍・3倍を交互に掛ける重み |
SUMPRODUCT | 重み付きの積を合計する |
MOD(-合計,10) | 10の補数からチェックデジットを求める |
注意点として、A2は必ず「文字列」として扱ってください。数値書式のままだと先頭の0が消え、桁数がずれて誤った結果になります。セルの書式設定を「文字列」にし、12桁を固定で入力するのが安全です。
モジュラス10/ウェイト3の計算ルールを最小限理解する
JANコード（GTIN-13）のチェックデジットは「モジュラス10、ウェイト3」と呼ばれる方式で計算します。これはGTIN共通のルールで、GTIN-8やGTIN-14でも考え方は同じです。手順は次の4段階です。
- 右端（13桁目）を除いた12桁を、左から数えて奇数位置と偶数位置に分ける。
- 奇数位置の数字は1倍、偶数位置の数字は3倍する。
- すべての積を合計する。
- 合計を10で割った余りを求め、10から引く。結果が10なら0とする（これが10の補数）。
具体例で確認します。12桁を「4 9 0 1 2 3 4 5 6 7 8 9」とします。
| 位置 | 数字 | 重み | 積 |
|---|---|---|---|
| 1 | 4 | 1 | 4 |
| 2 | 9 | 3 | 27 |
| 3 | 0 | 1 | 0 |
| 4 | 1 | 3 | 3 |
| 5 | 2 | 1 | 2 |
| 6 | 3 | 3 | 9 |
| 7 | 4 | 1 | 4 |
| 8 | 5 | 3 | 15 |
| 9 | 6 | 1 | 6 |
| 10 | 7 | 3 | 21 |
| 11 | 8 | 1 | 8 |
| 12 | 9 | 3 | 27 |
合計は4+27+0+3+2+9+4+15+6+21+8+27=126。126を10で割った余りは6なので、10−6=4がチェックデジットです。先ほどの式で「490123456789」の結果が5だったのは、左からの重みの付け方が異なる例だからではありません。式の重み配列は左から1,3,1,3…としており、この表と同じ並びです。実際に式へ入れる場合は、桁の並びと重みの開始位置を必ず一致させてください。
なお、GTIN-8（短縮タイプ）は8桁、GTIN-14（集合包装用）は14桁と桁数が変わります。桁数が変われば重みの開始位置も変わるため、12桁用の式をそのまま流用すると誤計算になります。Excelで扱う場合は、対象の桁数をセルごとに分けるか、後述のように桁数に応じて式を切り替えるのが実務的です。
12桁のJANコードからチェックデジットを求める手順
実際のシート配置を想定して、入力から確認までの流れを示します。ここではA列に12桁、B列にチェックデジット、C列に13桁完成形を置きます。
- A1に「JAN12」、B1に「チェックデジット」、C1に「JAN13」と見出しを入力する。
- A列全体の書式を「文字列」に設定する。書式設定はセルを選び、右クリックの「セルの書式設定」→「表示形式」→「文字列」で行う。
- A2に12桁を入力する。先頭が0のコードもそのまま入力できる。
- B2に先ほどの完成式を入力する。
- C2に
=A2&B2と入力すると13桁が完成する。 - B2とC2を下方向へコピーする。
入力時にありがちなのが、Excelが「490123456789」を数値と判断して表示形式を変えてしまうケースです。桁数が12桁を超えると指数表記になることもあります。これを避けるには、入力前に列全体を文字列書式にしておくか、TEXT関数で明示的に文字列化します。たとえば別のセルに数値で入っている場合は、=TEXT(A2,"000000000000") のように桁数を指定すると、先頭の0を保った12桁文字列に正規化できます。
12桁より短いコードが混在する場合は、TEXTで桁数を揃えてから計算します。ただし、JANコードとして正しい桁数でない場合はチェックデジットの意味自体が変わるため、実務では「12桁以外はエラーとする」列を用意するのがおすすめです。たとえばD2に =IF(LEN(A2)<>12,"桁数エラー","OK") と入れておけば、誤入力に気づけます。
作業列を使って計算過程を可視化する
式の意味を確認したい場合や、計算過程を監査したい場合は、作業列を使う方法が分かりやすいです。A2に12桁があるとして、D列以降に1文字ずつ展開します。
| セル | 数式 | 内容 |
|---|---|---|
| D2 | =MID($A2,COLUMN(A1),1)*1 | 1桁目を取り出す（右へコピー） |
| O2 | =MID($A2,COLUMN(L1),1)*1 | 12桁目を取り出す |
| D3 | =D2*IF(MOD(COLUMN(A1),2)=1,1,3) | 位置に応じて1倍または3倍 |
| D4 | =SUM(D3:O3) | 合計 |
| D5 | =MOD(-D4,10) | チェックデジット |
このように工程を分けると、どの桁で計算がずれているかを目視で確認できます。1セル式がうまく動かないときのデバッグにも役立ちます。
1セルで完結させる
作業列を増やさず、1つのセルでチェックデジットから13桁完成までを出すこともできます。12桁がA2にある場合、次の式で13桁が直接得られます。
=A2&MOD(-SUMPRODUCT(MID(A2,ROW(INDIRECT("1:12")),1)*1,{1;3;1;3;1;3;1;3;1;3;1;3}),10)元の12桁が文字列であれば、&でチェックデジットをつなぐだけで13桁の文字列になります。数値として扱いたい場合は VALUE で囲みますが、先頭が0のコードでは数値化すると桁が落ちるため、JANコードは文字列のまま管理するのが基本です。
また、チェックデジットだけを別の列に残したい場合は、先に示した MOD(-SUMPRODUCT(...),10) の部分だけを使います。どちらの式も、12桁が文字列で、途中に空白やハイフンが入っていないことが前提です。ハイフン付きのコードを扱う場合は、SUBSTITUTEで除去してから計算してください。
ExcelでJANコードを扱うときは、計算式だけでなく、桁数の正規化や文字列管理も合わせて設計するとトラブルを減らせます。バーコードの基礎や桁数の考え方については、バーコードの基礎ガイドや無料バーコード作成ツールも参考にしてください。
計算結果を検証する：既知のJANコードと逆算チェック
式を組んだら、まず既知のJANコードで答え合わせをしましょう。検証の基本は「計算したチェックデジットが正解と一致するか」と「完成した13桁から逆算しても同じ値になるか」の2点です。ここでは、標準的な13桁のJANコードを例に手順を示します。
次の12桁を用意し、チェックデジットを計算してみます。
| 桁位置 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 値 | 4 | 9 | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
モジュラス10/ウェイト3では、右端（12桁目）から数えて奇数位置に3、偶数位置に1を掛けます。つまり左から見ると、1桁目は3、2桁目は1、3桁目は3…と交互に重みが付きます。
手計算での答え合わせ
各桁に重みを掛けた合計を求めます。
| 桁 | 値 | 重み | 積 |
|---|---|---|---|
| 1 | 4 | 3 | 12 |
| 2 | 9 | 1 | 9 |
| 3 | 0 | 3 | 0 |
| 4 | 1 | 1 | 1 |
| 5 | 2 | 3 | 6 |
| 6 | 3 | 1 | 3 |
| 7 | 4 | 3 | 12 |
| 8 | 5 | 1 | 5 |
| 9 | 6 | 3 | 18 |
| 10 | 7 | 1 | 7 |
| 11 | 8 | 3 | 24 |
| 12 | 9 | 1 | 9 |
合計は12+9+0+1+6+3+12+5+18+7+24+9=106です。106を10で割った余りは6なので、チェックデジットは「10−6＝4」となります。完成する13桁は「4901234567894」です。
Excelの式で同じ結果になるか確認する
12桁がA2セルに入っている場合、次の式でチェックデジットが求められます。
=MOD(10-MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:12")),1)*1,{3;1;3;1;3;1;3;1;3;1;3;1}),10),10)
この式の結果が4になれば正解です。配列定数を使わず作業列で計算している場合も、合計が106、余りが6、チェックデジットが4になることを確認してください。
逆算チェックの考え方
完成した13桁を再度チェックデジット計算にかけると、今度はチェックデジットを含めた全桁の重み付き合計が10の倍数になります。上の例では、13桁目にも重み3が付き、106+4×3=118、118は10の倍数ではありません。これは重みの向きが逆になるためで、13桁全体で検証する場合は右端から重み1、3、1、3…と付け直します。つまり「4901234567894」の右端から数えると、チェックデジットの4に重み1、その左の9に重み3、以下同様です。
検証用の式は次のとおりです。
=MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:13")),1)*1,{1;3;1;3;1;3;1;3;1;3;1;3;1}),10)
これが0になれば、13桁のJANコードとして整合が取れています。自作の式が不安なときは、この2段階チェックを習慣にすると誤りに気づきやすくなります。
よくある間違いとトラブルシューティング
チェックデジット計算が合わない、エラーになる原因は、ほとんどが「データの型」と「桁数」に集約されます。以下を上から順に確認してください。
#VALUE! や #NUM! が出る
- セルに数字以外が入っている：ハイフン、空白、全角数字、単位記号などが混じるとMIDで取り出した文字が数値に変換できずエラーになります。
- 桁数が足りない：MIDで存在しない位置を指定すると空文字が返り、掛け算で#VALUE!になります。LENで桁数を確認してください。
- INDIRECTの範囲指定ミス：ROW(INDIRECT("1:12"))の部分が1:13になっていると、13桁目が空のときにエラーになります。
先頭の0が消える
Excelは「012345678901」のような先頭0付きの数字を数値として扱い、0を落としてしまいます。JANコードは文字列として管理するのが原則です。セルの表示形式を「文字列」に設定してから入力するか、CSV取込時に該当列を文字列として読み込んでください。既に入力済みの場合は、TEXT関数で桁数を固定する方法があります。
=TEXT(A2,"000000000000")
全角数字やハイフンが混入している
全角の「１２３」は数値として扱えません。ASC関数で半角に変換し、SUBSTITUTEでハイフンや空白を除去してから計算します。
=SUBSTITUTE(ASC(A2),"-","")
空白も同様にSUBSTITUTEで除去してください。見た目では気づきにくいため、LENで桁数を確認するのが確実です。
数値と文字列が混在している
同じ列に数値セルと文字列セルが混ざると、MIDの結果が不安定になります。計算前にTEXT関数で文字列に統一するか、VALUE関数で数値に統一してください。統一しないままSUMPRODUCTを使うと、一部の行だけ結果がずれることがあります。
CSV取込で指数表記になる
長い数字をCSVから取り込むと「4.90123E+11」のように指数表記に変換されることがあります。これは数値として解釈された結果です。取込ウィザードで該当列を「文字列」に指定するか、Power Queryで型をテキストに変更してください。指数表記のままではMIDで正しい桁が取り出せません。
8桁のJANコードで桁数が合わない
GTIN-8（短縮タイプ）は12桁ではなく7桁からチェックデジットを計算します。13桁用の式をそのまま使うと合いません。MIDの範囲と配列定数の個数を7に変更してください。桁数が混在する場合は、LENで分岐させるか、列を分けて管理するのが安全です。
計算結果が常に0や10になる
MODの外側と内側の順序を間違えると、この症状が出ます。正しくは「10から余りを引いた値をさらに10で割った余り」です。余りが0のときはチェックデジットも0になるため、MOD(10-0,10)=0となります。この挙動を忘れて「10」と表示してしまうケースも多いので注意してください。
チェックデジットが合っているのに読み取れない
計算が正しくても、バーコードとして読み取れない場合は、桁数ではなく印刷品質やバーの太さの問題です。計算結果と読み取り結果は切り分けて考えてください。
Excelでチェックデジットまで計算できたら、次は実際にバーコードとして生成・印刷する段階です。ここでは、Excelで管理しているJANコードを一括でバーコード化する流れを整理します。
1. Excel側で13桁を文字列として確定する
チェックデジットを結合した13桁を、必ず文字列として保持します。数値のままだと先頭0が消えたり指数表記になったりして、生成ツールに渡したときに別のコードになってしまいます。TEXT関数で桁数を固定し、余分な空白やハイフンがないことを確認してください。
2. CSV出力する
バーコード生成に必要な列だけを残し、CSVで保存します。このとき、Excelの「名前を付けて保存」でCSVを選ぶと文字列の扱いが変わる場合があるため、UTF-8で出力するか、生成ツール側の取込形式に合わせてください。1列目にJANコード、2列目に商品名などを置くと整理しやすくなります。
3. 一括生成ツールに取り込む
当サイトの無料バーコード作成・一括生成ツールプロでは、JAN・CODE39・ITF・QRなどに対応しており、CSVからの一括生成が可能です。JANコードは1次元バーコードなので、1次元作成ツールを使うとスムーズです。商品ごとに個別に生成したい場合は、バーコード生成ツールでも作成できます。
4. 印刷精度と読み取り確認
バーコードは印刷サイズが小さすぎると読み取れません。JANコードの標準的な寸法を守り、プリンターの解像度に合わせて拡大率を調整してください。印刷後は必ず実際のスキャナーで読み取り確認を行います。画面上で正しく見えても、にじみやかすれで読み取れないことがあります。
5. 桁数正規化の考え方を流用する
Excel側で桁数を揃える考え方は、データベース設計にも通じます。詳しくはバーコードの基礎ガイドで種類や番号の扱いを確認できます。桁数が混在するデータを扱う場合は、入力時点で正規化しておくと、後工程でのエラーを大幅に減らせます。
まとめ：次にやるべきこと
ExcelでJANコードのチェックデジットを計算するには、モジュラス10/ウェイト3の重みを正しく理解し、データを文字列として管理することが重要です。作業列を使う方法と1セルで完結する方法のどちらでも結果は同じなので、保守しやすい方を選んでください。計算後は必ず既知のコードで検証し、先頭0落ちや全角数字などの典型的なトラブルを潰しておきましょう。
次のステップとして、検証済みのJANコードをCSVに出力し、一括生成ツールで実際のバーコードを作成して、スキャナーで読み取り確認まで行ってください。計算と生成を別々に確認することで、問題の切り分けがしやすくなります。
Sources
ExcelでJANコードのチェックデジットを計算する基本の式は？
12桁がA2に入っている場合、=MOD(10-MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:12")),1)*1,{3;1;3;1;3;1;3;1;3;1;3;1}),10),10) で求められます。作業列を使う場合は各桁に重みを掛けた合計を出し、10で割った余りを10から引きます。
モジュラス10/ウェイト3とは何ですか？
JANコードのチェックデジット計算方法の名称です。右端から数えて奇数位置の桁に3、偶数位置の桁に1を掛け、その合計を10で割った余りを10から引いてチェックデジットを求めます。
チェックデジットの計算結果が合わないときは？
まず桁数とデータ型を確認してください。先頭0が消えていたり、全角数字やハイフン、空白が混じっていたりすると正しく計算できません。セルを文字列形式にし、不要な文字をSUBSTITUTEで除去してください。
先頭の0が消えてしまうのを防ぐには？
セルの表示形式を「文字列」に設定してから入力するか、TEXT関数で桁数を固定します。CSVから取り込む場合は、取込時に該当列を文字列として指定してください。
8桁のJANコードにも同じ式が使えますか？
使えません。GTIN-8は7桁からチェックデジットを計算するため、MIDの範囲と配列定数の個数を7に変更する必要があります。13桁と8桁が混在する場合は列を分けて管理してください。
計算結果が10と表示されるのはなぜですか？
MODの順序を間違えている可能性があります。正しくは10から余りを引いた値をさらに10で割った余りを取ります。余りが0のときはチェックデジットも0になります。
#VALUE!エラーが出るときの対処法は？
セルに数字以外の文字が入っているか、MIDで存在しない位置を参照している可能性があります。LENで桁数を確認し、ASCやSUBSTITUTEで不要な文字を除去してください。
CSV取込で指数表記になった場合は？
数値として解釈されているのが原因です。取込ウィザードやPower Queryで該当列の型をテキストに変更してください。指数表記のままではMIDで正しい桁が取り出せません。
チェックデジットを含む13桁を再検証するには？
13桁全体に右端から重み1、3、1、3…と付け直し、重み付き合計を10で割った余りが0になるか確認します。自作の式の答え合わせに有効です。
数値と文字列が混在しているとどうなりますか？
MIDの結果が不安定になり、一部の行だけ計算結果がずれることがあります。TEXT関数で文字列に統一するか、VALUE関数で数値に統一してから計算してください。
作業列を使う方法と1セルで完結する方法の違いは？
計算結果は同じです。作業列を使う方法は途中の合計を目視確認しやすく、1セルで完結する方法は列を増やさずに済みます。保守性で選んで問題ありません。
計算したJANコードをバーコードとして印刷するには？
13桁を文字列としてCSVに出力し、一括生成ツールに取り込んでバーコードを生成します。印刷後はスキャナーで読み取り確認を行い、にじみやかすれがないか確認してください。
全角数字が混じっているときの変換方法は？
ASC関数で半角に変換できます。ハイフンや空白もSUBSTITUTEで除去し、LENで桁数が正しいことを確認してから計算してください。
チェックデジットが正しいのに読み取れないのはなぜ？
計算ではなく印刷品質の問題です。バーコードのサイズが小さすぎたり、印刷がにじんでいたりすると読み取れません。標準的な寸法を守り、解像度に合わせて調整してください。
桁数が足りないデータを一括で正規化できますか？
TEXT関数で桁数を固定するか、入力時点で文字列として管理するのが有効です。桁数が混在する場合は列を分け、LENで分岐させる方法もあります。