excel:全国地方公共団体コードのcheck digitのきれいな作り方

質問

全国地方公共団体コードがあります。
　wikipediaによると、５桁と６桁の２種類があり、６桁は、最後の一桁に５桁のチェックデジットを追加したモノです。チェックデジットの算出方法は下記の通りです。
「上5桁を「abcde」としたとき、まずa×6+b×5+c×4+d×3+e×2を求め、これを11で割った余りを11から引く。」

ここに5桁のコード表がexcelの数量値として入っています。excelのワークシート関数のみで6桁に変換するにはどのように書くのがスマートでしょうか？
(数量値なので、北海道などは前ゼロがついていない状態です）

回答No.4

chie65536 · ベストアンサー

更に改良。

=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")

「数値の末尾１桁を抜き出す」なら「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_ken/saitamaken.htm

111091 埼玉県 さいたま市緑区
112071 埼玉県 秩父市

回答No.3

chie65536

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

chie65536

=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")

この式は「モジュラス１１、ウェイト２～６」で作成しています。

＞上5桁を「abcde」としたとき、まずa×6+b×5+c×4+d×3+e×2を求め、これを11で割った余りを11から引く。

上記の「モジュラス１１、ウェイト２～６」では「余りが０ならチェックデジットは０、余りが１ならチェックデジットは０」と言う条件を採用しています。

つまり「余りを１１から引いて、１０と１１になったら、チェックデジットは０」と言う数式です。ここは「普通に書くとIF関数が必要で、スマートに書くのが最も難しい所」です。

この式が正しい値（６桁の文字列を作る）を求めているかどうかは
http://www.lasdec.nippon-net.ne.jp/com/addr/kaku_ken/hokkaido.htm
で確認済みです。

補足

ありがとうございます。
＞「余りが０ならチェックデジットは０、余りが１ならチェックデジットは０」
今ローデータとつきあわせたところ、
「余りが０ならチェックデジットは０、余りが１ならチェックデジットは１」
のようです。

回答No.1

mshr1962

>まず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の時はどうなりますか？

補足

ありがとうございました！
sumproductのつかいかた、すごいですね。
midに配列入れるやり方も知りませんでした。