• Chiquilin Site■08.04.05_Excel:チェックデジットの求め方

  • はじめに:チェックデジット(チェックディジット)について
    その1:JANコード (標準タイプ13桁)
    その2:変動する祝日
    その3:振替休日(振り替え休日)
    ChiquilinSite

    はじめに:チェックデジット(チェックディジット)について

    チェックデジット(Check Digit・C/D)は番号の入力間違いをチェックするための数字です。JANコードの場合は末尾の1桁がそれにあたります。より複雑なものもあるようですがこちらでは取り上げませんので 適当なサイトで調べて下さい。ひとまずJANコード限定でいきます。
    ページの一番上へ

    その1:JANコードのチェックデジット計算方法

    JANコードの13桁コードの構成は「企業コード (9桁)」+「アイテムコード (3桁)」+「チェックデジット (1桁)」です。8桁の場合も コードの数が変わるだけで基本的な計算方法は変わりません。 手順を書きますと
    例:企業コード/456995111 アイテムコード/123
    1. コードを右詰に13桁並べた時の 偶数桁の数字を合計する
      「5」+「9」+「5」+「1」+「1」+「3」=「24」
    2. (1)の結果を3倍する
      「24」×3=「72」
    3. コードを右詰に13桁並べた時の 奇数桁(C/D除く)の数字を合計する
      「4」+「6」+「9」+「1」+「1」+「2」=「23」
    4. (2)の結果と (3)の結果を合計する
      「72」+「23」=「95」
    5. (4)の1桁目の数字を「10」から引く
      「10」−「5」=「5」
    以上です。
    ページの一番上へ

    その2:作業セルを使う計算

    計算方法自体は単純なので セルを分けて計算するなら なんてことのない計算ですね。
    作業セルを使う方法
    企業コードを A1セルに入力
    アイテムコードを B1セルに入力
    ■C1セル
     =TEXT(A1*1000+B1,0)
    8桁コードの時は
     =TEXT(A1*10+B1,REPT(0,12))
     
    A3:A14セルに「1〜12」の連番を入力して
    ■B3セル
     =MID(C$1,A3,1)*1
     縦方向にオートフィルコピー
     
    ■C3セル:奇数合計
     =SUM(B3,B5,B7,B9,B11,B13)
     これをC4セルにコピー
    ■C5セル
     =C4*3+C3
    ■C6セル:チェックデジット
     =MOD(10-C5,10)
    これで求められます。C6セルの計算式で 先に10から引いて計算しているのは 桁が「0」の時に「10」と表示されないようにする為です。
    ページの一番上へ

    その3:1セルで計算する方法

    続いて作業セルを使わない方法です。よく考えれば 偶数桁と奇数桁を分けてはいますが 結局全部足し算はする訳です。一文字ずつ切り分けて奇数番目には「1」偶数番目には「3」を掛けて全部足してやっても同じ結果が得られますね。以下13桁コードの計算式です。
    完成
    企業コードを A1セルに入力
    アイテムコードを B1セルに入力
    ■C1セル
    =MOD(10-SUM(MID(A$1*1000+B1,{1,2,3,4,5,6,7,8,9,10,11,12},1)
    *{1,3,1,3,1,3,1,3,1,3,1,3}),10)
    もしくは
    =MOD(10-SUM(MID(A$1*1000+B1,{1,3,5,7,9,11;2,4,6,8,10,12},1)*{1;3}),10)
    長ったらしいのが気になるなら [挿入]→[名前]→[定義]で「CD」と名前を付けて
    1行目で [挿入]→[名前]→[定義]
     
    [名  前]:CD
    [参照範囲]:
    =MOD(10-SUM(MID(!$A$1*1000+!$B1,ROW(!$1:$12),1)
    *(1+MOD((ROW(!$1:$12)-1),2)*2)),10)
    →[OK]
     
    として
    ■D1セル
     =A$1*10^4+B1*10+CD
     表示形式を[数値] もしくはユーザー定義の「0」に設定
    としておく手もあります。
    済みません定義付けした数式に誤りがありましたので修正しました (2011.10.17)
    →参考ファイル(037.xls)
    ページの一番上へ
    因みに8桁の場合の計算を一応載せておきますが (10.10.06 追加)
    JANメーカーコード(6桁)を A1セルに入力
    アイテムコード(1桁)を B1セルに入力
    ■C1セル
    =MOD(10-SUM(MID(A$1*10+B1,{1,2,3,4,5,6,7},1)*{3,1,3,1,3,1,3}),10)
    以上です。
    ChiquilinSite トップページへ Copyright(C) Chiquilin_site. All Rights Reserved.