文字列操作のサンプルとして、JANコードのチェックデジットを算出する処理を提示します。
「JANコード」って？
 |
←この画像は手元にあったお菓子のパッケージに印刷されていた「バーコード」です。 この「バーコード」の種類が「JANコード」と呼ばれるものです。 「バーコード」のすだれ模様のところに13桁の数字が印刷されていますが、専用のスキャナで読み取るとこの「13桁の数字」が読み取られます。 すなわちこの「13桁の数字」がこの商品の「JANコード」であって、商品個別の番号にあたります。 店舗のPOSシステムでは商品情報の登録内容と照合されて、商品名や価格が紐付けされます。 |
- 「チェックデジット」とは
-
このページのタイトルは「JANコードのチェックデジットを算出する」でした。
「JANコード」は上のコラムで、一般の商品に印刷されているバーコードもしくはそのバーコードに表意している番号のことだと解ったと思います。
次に説明するのは「チェックデジット」です。
「チェックデジット」とは「検証数字」ということですが、上のコラムで紹介した「13桁の数字」には既に含まれています。
実際の商品固有の番号を指しているのは「13桁の数字」のうちの上位12桁であって、最後の1桁が「チェックデジット」です。
「チェックデジット」は自身を除く上位12桁を「ある計算に掛けて算出」されるものであって、バーコードをスキャナで光学的に読み取る時の「誤読」をチェックする目的のものです。
実際にはスキャナかPOSシステム側で一時的に「上位12桁」と「末尾上位1桁」に分けて「チェックデジット」の計算を行ない、 照合OKなら元の「13桁の数字」を読み取った「JANコード」として扱います。
従って「チェックデジット」の有無や値を利用現場で意識することはないのですが、「ある計算に掛けて算出」のところを以下で説明しますので、 お手元にある何かの商品パッケージに印刷されている「JANコード」で確認してみて下さい。
「JANコード」という名前から日本の規格に見えますが、これは国際標準の規格であり海外からの輸入品にあるバーコードでも 普通に読み取って運用されています。 日本では「JAN」の呼び名ですが、カナダ・アメリカでは「UPC」、ヨーロッパでは「EAN」と呼ばれています。
- サンプルを見てみましょう。
-
(画像をクリックすると、このサンプルがダウンロードできます)
赤矢印が指しているB2セルに「JANコード」の「チェックデジット」を含む13桁(全桁)を入力すると、 その下の右端(C3セル)にこのシートで計算された「チェックデジット」が表示されるというものです。
ちょうどこの画像は上のコラムにある画像の「JANコード」の値を入力したところで、「JANコード」欄の右端桁と その下の「算出C/D⇒」の値が一致しているのが確認できます。 お手元に何かの商品パッケージでバーコードが印刷されているものがあれば、番号を入力してみて下さい。 入力した「JANコード」で算出された「チェックデジット」と末桁が一致しないとセルが赤くなって表示されるようになっています。
Excelには「チェックデジット計算」を行なう関数があるわけではなく、また「チェックデジット計算」を行なう業務も非常に「まれ」でしょうが、 まずは、どのように計算しているかを説明します。
- チェックデジットの計算方法
-
「JANコード」の「チェックデジット」の計算方式は「モジュラス10ウエイト1-3」などと呼ばれるようですが、
よく解るように画面右下の「ここから下は作業域です」の所で中間計算を行なっています。
この部分を作業順に上から説明していきます。計算方法の説明なので数式の解説はなるべくしないようにします。(難しい関数は出てきません)
場所 内容説明(算出方法等) 13桁整合
(G5セル)「JANコード」を入力されたB2セルから転記してきていますが、入力されるコードでは「短縮JAN」が8桁だったり、 「UPCコード」が1桁少ない12桁だったりするので、一旦13桁に桁揃えします。
桁数が足りない部分は前ゼロを付加させます。
桁位置
(7行目)左からの連番の見出しです。ここからは「JANコード」を文字列処理として1文字ずつ切り出すための位置になります。
「チェックデジット」の計算方法を解説するサイトによっては「JANコード」が数字であるため、右からの連番で説明しているところも多いですが、 Excelでの処理を考えると、これはあくまで「文字列」としての処理だということで、左からの連番にしています。
各桁値
(8行目)上の「13桁整合」で揃えられた「JANコード」を左から順に1桁ずつ切り出して各列に並べており、 ここでは末桁の「チェックデジット」は除外しています。
ウエイト
(9行目)計算方式名にある「ウエイト1-3」というのは、奇数桁位置には「1」を掛け、偶数桁位置には「3」を掛けるので、 この「1」と「3」を交互に並べている定数行です。
乗算値
(10行目)各桁位置について「各桁値」と「ウエイト」の掛け算の結果を収容しています。
右端のS10セル(乗算値合計)は各桁位置の乗算値の横合計です。
10の剰余
(S11セル)「乗算値合計(S10セル)」を10で割った「あまり」です。
算出C/D⇒
(C3セル)上記「10の剰余(S11セル)」を10から差し引いた値で、 これが「チェックデジット」になります。
使用した関数は以下の通りです。
関数 概略説明 RIGHT関数 文字列の末尾(右端)から指定された文字数の文字を返します。引数は以下の通りです。
① 文字列
② 文字数
MID関数 文字列の指定された位置から指定された文字数の文字を返します。引数は以下の通りです。
① 文字列
② 開始位置
③ 文字数
SUM関数 引数に指定した個々の値、セル参照、セル範囲、またはこれらの組み合わせを加算合計します。
MOD関数 数値を除数で割ったときの剰余を返します。 戻り値は除数と同じ符号になります。
引数は以下の通りです。
① 数値
② 除数
このように難しい関数は使用していません。桁ごとの切り分けができれば後は簡単な算術式で済むものです。
念のため説明するとすれば、最初の「13桁整合」で、ここでは「短縮JAN」などの対応のため、 13桁未満の場合には左側に「0」を埋めておきたいわけです。 ここでの数式は、
としており、「&演算子」で先にゼロを並べた後に入力されたコードを接合させてから「右から13桁」を取り出しています。=RIGHT("000000"&$B$2,13)