【Excel】エクセルでJANコードを作成する方法（関数・表示・文字列）

Excelで商品管理表や在庫一覧を作る際、JANコードをセルに正しく入力し、桁数を保ったまま表示したい場面があります。

JANコードは数字だけに見えますが、Excelでは数値として扱うか文字列として扱うかによって、先頭の0が消えたり、表示形式が変わったりすることがあります。

特に13桁のJANコードでは、チェックデジットの計算、先頭ゼロの維持、バーコード用フォントへの変換など、目的に合わせた設定が必要です。

この記事では、関数を使ってJANコードを作成する方法、文字列として表示する方法、計算結果を確認する手順を詳しく解説します。

 

エクセルでJANコードを作成する方法【チェックデジットを関数で計算】

それではまず、12桁の商品コードをもとに13桁のJANコードを作成する方法について解説していきます。

A列 商品名	B列 12桁コード	C列 チェックデジット	D列 JANコード
緑茶	490123456789	4	4901234567894
炭酸水	490987654321	8	4909876543218

 

JANコードの構成と13桁目の役割

JANコードは、日本国内で流通する商品を識別するためのコードです。

一般的なJANコードは13桁で構成され、先頭側には事業者コードや商品アイテムコードが入り、最後の1桁にはチェックデジットが入ります。

チェックデジットは、入力された番号に誤りがないかを検査するための数字です。

12桁までの数字が分かっていれば、規則に沿って13桁目を計算できます。

偶数番目の数字に3を掛け、奇数番目の数字はそのまま加算し、合計値が10の倍数になるように最後の数字を決める仕組みです。

たとえば12桁のコードが490123456789の場合、右側から数えて偶数位置の数字を3倍して合計します。

Excelでは桁ごとに数字を取り出して計算するため、関数を組み合わせれば自動化できます。

元の12桁コードを変更したときにチェックデジットも更新されるため、商品マスターの整備にも便利でしょう。

 

12桁コードからチェックデジットを求める数式

続いては、B列に12桁の商品コードが入力されている場合の数式を確認していきます。

サンプルデータでは1行目を見出し行とし、最初の商品コードはB2セルに入力されているものとします。

C2セルには、次の数式を入力します。

この数式では、MID関数でB2セルの1文字目から12文字目までを1桁ずつ取り出します。

ROW関数で1から12までの位置を作り、IF関数で偶数番目なら3、奇数番目なら1を返します。

SUMPRODUCT関数によって、各桁と重みを掛け合わせた値を合計できます。

最後にMOD関数を二重に使うことで、合計値を次の10の倍数にするための数字が求められます。

計算後にC2セルへ4と表示されれば、B2の12桁コードに対応するチェックデジットを作成できています。

数式を下方向へコピーすれば、B3以降の商品コードにも同じルールを適用できます。

ただし、B列に空白や12桁以外の値が混ざると意図しない結果になるため、入力規則も併用すると安心です。

 

13桁のJANコードを結合する手順

続いては、12桁コードと計算したチェックデジットを結合する操作を確認していきます。

D2セルには、B2セルとC2セルをつなげる数式を入力します。

アンパサンドは、複数の値を文字列として連結する演算子です。

この式により、B2セルの490123456789とC2セルの4がつながり、D2セルには4901234567894と表示されます。

JANコードを作成した後は、数値ではなく文字列として扱うことが重要です。

数値形式のままでも13桁の多くのJANコードは表示できますが、先頭が0になる商品コードでは桁が欠ける可能性があります。

作成列をあらかじめ文字列形式にするか、TEXT関数を利用して13桁表示を固定しましょう。

例として、D2セルに=TEXT(B2,”000000000000″)&C2と入力すると、12桁部分の先頭ゼロを補った状態で連結できます。

商品マスターへ貼り付ける前には、表示だけでなく数式バーでも13桁すべてが残っているか確認する習慣が大切です。

 

JANコードを文字列として表示する設定

続いては、ExcelでJANコードの先頭ゼロや桁数を維持して表示する設定について確認していきます。

入力値	数値形式での表示	文字列形式での表示
0123456789012	123456789012	0123456789012
4901234567894	4901234567894	4901234567894

 

セルの表示形式を文字列へ変更する方法

それではまず、入力前にセルを文字列形式へ変更する方法について解説していきます。

JANコードを入力する列を選択し、ホームタブの表示形式一覧から文字列を選びます。

この設定をしてからコードを入力すると、先頭の0を含む桁並びをそのまま保持できます。

商品コードや郵便番号と同じく、JANコードは四則演算をする数値ではなく識別番号です。

すでに数値として入力済みのセルへ文字列形式を設定しても、消えた先頭ゼロは自動では戻りません。

元データに先頭ゼロがある場合は、改めて入力し直すか、TEXT関数で必要な桁数へ整えます。

セル左上に緑色の三角マークが出ることがありますが、識別番号を文字列で管理している場合は必ずしもエラーではありません。

数値として計算する必要がない列であれば、警告を無視して運用しても問題ないケースが多いです。

 

先頭にアポストロフィを付けて入力する方法

続いては、単発でJANコードを文字列入力したい場合の手順を確認していきます。

セルへ入力する際に、JANコードの先頭へ半角のアポストロフィを1つ付けます。

たとえば0123456789012を入力したい場合は、’0123456789012のように入力します。

確定後のセルにはアポストロフィ自体は表示されず、0123456789012だけが表示されます。

この方法は、列全体の設定を変えずに一部のセルだけ先頭ゼロを守りたいときに便利です。

一方で、多数の商品コードを貼り付ける作業では、毎回アポストロフィを付ける方法は手間がかかります。

CSVファイルからインポートする場合や、大量のJANコードを扱う管理表では、取り込み前に対象列を文字列として指定するほうが安定します。

入力済みのデータにアポストロフィを追加するため、値の先頭へ記号を機械的に付けると、他システムへ渡す際に不都合が出ることもあります。

データの受け渡し先が文字列を期待しているかも確認しましょう。

 

TEXT関数で桁数を固定する方法

続いては、数値として存在するコードを13桁の文字列表示に変換する方法について解説していきます。

たとえばB2セルに123456789012があり、先頭に0を補って13桁にしたい場合は、別セルに次の数式を入力します。

ダブルクォーテーション内の0は、表示したい桁数を表します。

0を13個並べることで、B2セルの値を常に13桁で表示できます。

TEXT関数の結果は文字列になるため、その後にJANコードを結合したり、バーコード用の文字列を作ったりする処理にも向いています。

ただし、元の値が誤って12桁しかないのか、本来は先頭ゼロを含む13桁なのかは、関数だけでは判断できません。

桁数を補う処理は便利ですが、商品マスターの正しい原本と照合してから使うことが大切です。

 

関数でJANコードの桁数とチェックデジットを確認する方法

続いては、入力済みのJANコードが13桁であるか、チェックデジットが正しいかを関数で判定する方法について確認していきます。

A列 JANコード	B列 桁数判定	C列 チェック判定
4901234567894	OK	OK
4909876543210	OK	要確認

 

LEN関数による13桁チェック

それではまず、JANコードが13桁で入力されているかを確認する方法について解説していきます。

A2セルにJANコードが入っている場合、B2セルには次の数式を入力します。

LEN関数は、セルに含まれる文字数を数える関数です。

A2セルの文字数が13ならOK、それ以外なら要確認と表示します。

JANコード列を文字列として管理していれば、先頭ゼロも1文字として正しく数えられます。

空白を含む値では文字数が増えるため、外部データを貼り付けた後のチェックにも使えます。

見た目では13桁に見えても、末尾にスペースが含まれるケースもあるため、異常値を見つけたら数式バーで内容を確認しましょう。

 

RIGHT関数とMID関数による検査用コードの作成

続いては、13桁のうち最後の1桁を取り出し、先頭12桁と比較する準備を確認していきます。

RIGHT関数を使うと、JANコードの右端にあるチェックデジットを取得できます。

A2セルの最後の1桁を取り出す数式は、=RIGHT(A2,1)です。

一方で、先頭12桁を取り出すには、=LEFT(A2,12)と入力します。

LEFT関数とRIGHT関数を分けて使うと、どの部分に誤りがあるかを確認しやすくなります。

たとえば入力担当者が最後のチェックデジットだけを打ち間違えた場合、先頭12桁は正しくても全体のJANコードは無効です。

チェックデジットは単独で確認するより、先頭12桁から再計算した値と照合することが重要です。

この考え方を利用すると、商品登録前の検査シートを作成できます。

 

チェックデジットの一致判定

続いては、再計算したチェックデジットと入力済みの末尾1桁を比較する数式について解説していきます。

C2セルには、次の数式を入力します。

この数式は、A2セルの先頭12桁から正しいチェックデジットを再計算し、RIGHT関数で取り出した末尾の数字と比較します。

一致すればOK、不一致なら要確認と表示されます。

数式をC2から下方向へオートフィルすると、商品一覧全体を一括で検査できます。

JANコードが空欄の行にも要確認と出る場合は、IF(A2=””,””,数式)の形にして空白時の表示を抑えると見やすくなります。

 

JANコードの表示形式と数値化を防ぐ対処法

続いては、ExcelでJANコードの表示が崩れる原因と、数値化を防ぐ対処法について確認していきます。

現象	主な原因	対処
先頭の0が消える	数値として入力された	文字列形式に変更する
指数表示になる	列幅不足または数値形式	列幅と表示形式を確認する

 

先頭ゼロが消える原因

それではまず、先頭の0が消える理由について解説していきます。

Excelは、数字だけで構成された入力値を数値として認識しようとします。

数値では先頭の0は値に影響しないため、0123456789012と入力しても123456789012として扱われます。

JANコードの0は商品を識別するための重要な文字であり、算術上の不要な0ではありません。

そのため、入力前の文字列形式設定、またはアポストロフィを使った入力が必要です。

データをコピーして貼り付けるときも、貼り付け先の表示形式によっては数値化されることがあります。

外部システムからCSVを開く場合は、ダブルクリックで直接開くのではなく、データタブのテキストまたはCSVから読み込む方法が有効です。

 

指数表示と列幅不足の見分け方

続いては、JANコードが1.23E+12のように表示される場合について確認していきます。

指数表示は、セルが数値形式であり、列幅が足りないときに起こることがあります。

まず列見出しの境界をドラッグして幅を広げ、表示が元に戻るかを確認しましょう。

幅を広げても指数表示のままであれば、セルの表示形式が標準または数値になっている可能性があります。

表示形式を文字列へ変更した後、必要に応じて元データを入力し直します。

見た目だけを整えるために小数点以下の表示桁数を変更しても、JANコードの文字列化にはなりません。

コードの列には十分な幅を確保し、中央揃えよりも左揃えまたは標準表示にしておくと、文字列として扱われているか見分けやすいでしょう。

 

CSVファイルを取り込む際の注意点

続いては、CSV形式の商品データをExcelへ読み込むときの注意点について解説していきます。

CSVは値と区切り文字だけで構成されるため、JANコードが文字列なのか数値なのかという情報を保持しません。

ExcelでCSVを直接開くと、自動判定によって先頭ゼロが消えることがあります。

データタブからテキストまたはCSVを選び、プレビュー画面でJANコードの列を文字列として指定してください。

取り込み後は、LEN関数による桁数チェックを実行すると安心です。

元のCSVを上書き保存すると先頭ゼロが失われた状態で保存される可能性もあるため、作業前の原本を別に残しておきましょう。

 

JANコードを商品管理表とバーコード作成へ活用する方法

続いては、作成したJANコードを商品管理表やバーコード作成へ活用する方法について確認していきます。

商品名	JANコード	在庫数	確認結果
緑茶	4901234567894	24	OK
炭酸水	4909876543218	18	OK

 

商品マスターでの重複確認

それではまず、商品マスターに同じJANコードが重複していないかを確認する方法について解説していきます。

JANコードは原則として商品を識別する番号なので、同じ管理対象内で重複している場合は確認が必要です。

A列にJANコードが入力されているなら、B2セルへ=COUNTIF($A:$A,A2)と入力すると、同じコードの件数を数えられます。

結果が2以上になった行は、同じJANコードが複数回登録されている候補です。

重複は必ずしも誤りとは限りませんが、内容量違いや色違いの商品を誤って同じ商品として登録していないか確認しましょう。

条件付き書式を使って重複値に色を付けると、一覧から探しやすくなります。

JANコード列を文字列として統一しておくことが、正しい重複判定の前提になります。

 

バーコードフォントを使う前の準備

続いては、ExcelのJANコードをバーコード表示へ利用する前に確認したい点について解説していきます。

JANコードの数字をそのまま特定のバーコードフォントへ変更しても、規格どおりのバーコードにならない場合があります。

フォントによって必要な開始文字、終了文字、チェックデジットの扱いが異なるためです。

利用するバーコードフォントやアドインの説明書を確認し、EAN-13またはJAN-13に対応しているかを確かめましょう。

バーコード化する前に、Excel上のJANコードが13桁であり、チェックデジットの検査に通っていることを確認してください。

印刷後は、スマートフォンのバーコード読み取り機能や専用スキャナーで実際に読み取れるかを試験することも重要です。

画面表示がバーコードらしく見えても、印刷倍率や余白の不足で読み取れないことがあります。

 

VLOOKUP関数とXLOOKUP関数での商品検索

続いては、JANコードをキーにして商品名や価格を検索する活用方法について確認していきます。

商品マスターのA列にJANコード、B列に商品名がある場合、入力したJANコードから商品名を表示できます。

Excelの新しいバージョンでは、XLOOKUP関数を使うと分かりやすく検索できます。

E2セルにスキャナー入力や手入力でJANコードを入れると、対応する商品名が表示されます。

旧バージョンのExcelでは、VLOOKUP関数でも同様の検索が可能です。

検索値と商品マスター側のJANコードで、片方だけが数値、もう片方だけが文字列になっていると一致しないことがあります。

この場合は両方の列を文字列に統一し、不要な空白がないか確認してください。

 

まとめ エクセルでJANコードを作成する方法（文字列・表示・関数）

ExcelでJANコードを作成する場合は、先頭12桁からチェックデジットを関数で計算し、最後に文字列として連結すると管理しやすくなります。

チェックデジットにはSUMPRODUCT、MID、ROW、IF、MODなどの関数を組み合わせて利用できます。

JANコードは商品を識別する文字列なので、先頭ゼロを失わないようにセルの表示形式を文字列へ設定することが大切です。

入力済みのコードはLEN関数で13桁かを確認し、再計算したチェックデジットと末尾の数字を比較すると、入力ミスを見つけやすくなります。

CSVを取り込む場合も、JANコード列を文字列として指定することで表示崩れを防げます。

商品管理表、重複チェック、バーコード印刷、XLOOKUP関数による商品検索へ活用すれば、Excel上の商品データをより正確に扱えるようになるでしょう。