![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
全国地方公共団体コードがあります。
wikipediaによると、5桁と6桁の2種類があり、6桁は、最後の一桁に5桁のチェックデジットを追加したモノです。チェックデジットの算出方法は下記の通りです。
「上5桁を「abcde」としたとき、まずa×6+b×5+c×4+d×3+e×2を求め、これを11で割った余りを11から引く。」
ここに5桁のコード表がexcelの数量値として入っています。excelのワークシート関数のみで6桁に変換するにはどのように書くのがスマートでしょうか?
(数量値なので、北海道などは前ゼロがついていない状態です)
No.4ベストアンサー
- 回答日時:
更に改良。
=TEXT(A1*10+MOD(MOD(11-MOD(SUMPRODUCT(MID(TEXT(A1,"00000"),ROW($A$1:$A$5),1)*(7-ROW($A$1:$A$5))),11),11),10),"000000")
「数値の末尾1桁を抜き出す」なら「RIGHT(xxx,1)」でなく「MOD(xxx,10)」の方が「より、スマート」です。数値は数値のまま演算した方が良いですからね。
あ、今まさに同じ答えにたどり着いていました。
下記で一都三県確認済です。
=A1*10+MOD(11-(MOD(SUMPRODUCT(MID(TEXT(A1,"00000"),ROW($A$1:$A$5),1)*(7-ROW($A$1:$A$5))),11)),10)
10,11の扱いはこれが正しいようです。
11のデジットは下記の通り[1]となります。
http://www.lasdec.nippon-net.ne.jp/com/addr/kaku …
111091埼玉県さいたま市緑区
112071埼玉県秩父市
No.3
- 回答日時:
mshr1962さんのSUMPRODUCTの使い方に脱帽。
当方の回答と合わせると
=TEXT(A1*10+RIGHT(MOD(11-MOD(SUMPRODUCT(MID(TEXT(A1,"00000"),ROW($A$1:$A$5),1)*(7-ROW($A$1:$A$5))),11),11),1),"000000")
が完成形になります。
No.2
- 回答日時:
=TEXT(A1*10+RIGHT(MOD(11-MOD(MID(TEXT(A1,"00000"),1,1)*6+MID(TEXT(A1,"00000"),2,1)*5+MID(TEXT(A1,"00000"),3,1)*4+MID(TEXT(A1,"00000"),4,1)*3+MID(TEXT(A1,"00000"),5,1)*2,11),11),1),"000000")
この式は「モジュラス11、ウェイト2~6」で作成しています。
>上5桁を「abcde」としたとき、まずa×6+b×5+c×4+d×3+e×2を求め、これを11で割った余りを11から引く。
上記の「モジュラス11、ウェイト2~6」では「余りが0ならチェックデジットは0、余りが1ならチェックデジットは0」と言う条件を採用しています。
つまり「余りを11から引いて、10と11になったら、チェックデジットは0」と言う数式です。ここは「普通に書くとIF関数が必要で、スマートに書くのが最も難しい所」です。
この式が正しい値(6桁の文字列を作る)を求めているかどうかは
http://www.lasdec.nippon-net.ne.jp/com/addr/kaku …
で確認済みです。
この回答への補足
ありがとうございます。
>「余りが0ならチェックデジットは0、余りが1ならチェックデジットは0」
今ローデータとつきあわせたところ、
「余りが0ならチェックデジットは0、余りが1ならチェックデジットは1」
のようです。
No.1
- 回答日時:
>まずa×6+b×5+c×4+d×3+e×2を求め、これを11で割った余りを11から引く
A1=abcde(5桁以内の数値)として
=11-MOD(SUMPRODUCT(MID(TEXT(A1,"00000"),ROW($A$1:$A$5),1)*(7-ROW($A$1:$A$5))),11)
※結果は1~11になります。1桁ということですが、10,11の時はどうなりますか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) Excelで総数量を変動させたい 2 2022/11/04 23:49
- Visual Basic(VBA) VBA シート間の転記で、条件の追加コードの書き方について教えて下さい。 13 2023/02/26 09:31
- Excel(エクセル) 複数のExcelブックのシート1の内容を1つのExcelブックにコピー貼り付けたいのでvbaコードを 7 2023/02/10 23:20
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- その他(データベース) Accessフォームからパラメーターで表示したレコードを指定のExcelのセルへ転送する方法について 2 2022/08/22 18:04
- その他(Microsoft Office) Excel 2列の値を返す数式についてです 1 2022/11/23 22:59
- Excel(エクセル) Excelにて、行の最後のセルの値をコピーして別sheetに張りつけるVBAコードをご教授願います 3 2022/11/20 14:35
- Excel(エクセル) Excelの複数置換はSUBSTITUTEを重ねるしかない? 9 2022/05/08 11:25
- Excel(エクセル) Indirect関数について、Formulatextで抽出した数式を参照したい。 1 2022/12/15 11:16
- Excel(エクセル) 棚卸表の前月比の関数等あれば教えてください 2 2023/05/02 18:34
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【ExcelVBA】ダブルクォーテー...
-
エクセルをエクセレントに究める
-
【ExcelVBA】名前を付けて保存→...
-
Excel VBAで全ての矢印を赤色に...
-
エクセル関数を使って
-
【Excel】別シートから条件に合...
-
エクセルの数式バーのフォント...
-
2列に入っているデータを1列...
-
数字入力後他の文字等が表示さ...
-
エクセル シート表示
-
エクセルでファイルの最終更新...
-
Excelに詳しい方! B列が「日...
-
特定の文字列を含む、住所を抽...
-
EXCELの散布図で日付が1900年に...
-
Excelセルを跨いで合計を出す方法
-
excel2013 MonthDays 関数が使...
-
Excelのピボットグラフの作り方...
-
Excel関数について教えてくださ...
-
エクセルで 自動的に◯や数字を...
-
Excel分数の表示について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報