Excel：JANコードのチェックデジットを計算する

Microsoft 365でエクセルに追加されたLET関数を使用してGTIN（JANコード）のチェックデジットを計算する数式ワンライナーネタです。

GTIN（JANコード）とは

GTIN（JANコード）は「どの事業者の、どの商品か」を表す国際標準の商品識別コードでそのコード体系には標準タイプと短絡タイプがあります。

今回は標準タイプのチェックデジットを計算する数式を作成しました。

標準タイプのコード体系はGS1事業者コードと商品アイテムコードを合わせた12桁と1桁のチェックデジットの合計13桁で構成されます。

チェックデジットの計算

それではエクセルでチェックデジットの計算を行う数式を作成していきます。

最初にGTIN（JANコード）の標準タイプのチェックデジットを計算する数式を作成して、そのあとにモジュラス10/ウェイト3のチェックデジットの計算を行うようにその数式を改良します。

GTIN（JANコード）の標準タイプのチェックデジットの計算

GTIN（JANコード）の標準タイプのチェックデジットの計算方法は以下です。

1. すべての偶数桁の数字を加算
2. 1.の結果を3倍する
3. すべての奇数桁の数字を加算
4. 2.の結果と3.の結果を加算
5. 4.の結果の下1桁の数字を10から引いたもの（下1桁が0の場合は0）

奇数桁、偶数桁はチェックデジットを含めたコードの後ろから数えます。

後ろから1桁目のチェックデジットの桁が奇数桁、後ろから2桁目が偶数桁になります。

つまりチェックデジットを除いたコードの桁数によって

• チェックデジットを除いたコードの桁数が偶数の場合、左から1桁目が奇数桁
• チェックデジットを除いたコードの桁数が奇数の場合、左から1桁目が偶数桁

になります。

以下は上記の計算方法をできるだけそのままエクセルの数式にしたものです。

=LET(
  コード12桁, B3&C3,
  ステップ1, SUM(VALUE(MID(コード12桁,SEQUENCE(6,1,2,2),1))),
  ステップ2, ステップ1*3,
  ステップ3, SUM(VALUE(MID(コード12桁,SEQUENCE(6,1,1,2),1))),
  ステップ4, ステップ2+ステップ3,
  ステップ5, IF(MOD(ステップ4,10)=0,0,10-MOD(ステップ4,10)),
  ステップ5)

B3に入力されているGS1事業者コード、C3に入力されている商品アイテムコードから標準タイプ（13桁）のチェックデジットを計算します。

標準タイプ（13桁）の場合チェックデジットを含めない桁数が12桁（偶数）なので左から1桁目を奇数桁として計算を行っています。

チェックデジットを計算する上記の数式をD3のセルに入力した結果は以下です。

モジュラス10/ウェイト3のチェックデジットの計算

GTIN（JANコード）のチェックデジットはモジュラス10/ウェイト3で求められます。

先ほどのエクセルの数式は標準タイプ（13桁）限定ですので汎用的にするため以下の計算方法で計算するように数式を改良することにしました。

1. 各桁の数字に対するウエイトを決定する
   1. チェックデジットを含めない桁数が偶数の場合
      • 左から奇数桁の場合は1
      • 左から偶数桁の場合は3
   2. チェックデジットを含めない桁数が奇数の場合
      • 左から奇数桁の場合は3
      • 左から偶数桁の場合は1
2. すべての数字に対して1.のウエイトをかける
3. 2.の数字を合計する
4. 3.の結果の下1桁の数字を10から引いたもの（下1桁が0の場合は0）

上記の計算方法で計算する数式は以下になります。

=LET(
  コード, E3,
  ステップ1, IF(MOD(SEQUENCE(LEN(コード)),2)=MOD(LEN(コード),2),3,1),
  ステップ2, VALUE(MID(コード,SEQUENCE(LEN(コード)),1))*ステップ1,
  ステップ3, SUM(ステップ2),
  ステップ4, IF(MOD(ステップ3,10)=0,0,10-MOD(ステップ3,10)),
  ステップ4)

チェックデジットを計算する上記の数式をF3:F7のセルに入力した結果は以下です。

まとめ

LET関数を使用することで1つの数式の中でもステップに分けて計算することができます。

ステップに分けることで複雑な計算を簡潔に記載することができます。