【エクセル2002】別シートの数値を桁ごとに別々のセルに振り分けたい。

質問

お世話になります。
別シートで合計値を出した数値を参照し、指定のセルにその数値を桁ごとに分解して表示させたいのですが、うまくいきません。おそらく、IF関数、ISERROR関数、MATCH関数あたりを組み合わせるのだろうと思いますが、教えていただけないでしょうか
目的は、金種内訳票を作りたいのですが、票の規格により、各桁ごとに個別の升目（セル）に入力しなくてはならないためです。

【基礎データとなるシート】
ROUNDDOWN関数を使って金種別の金額を出し、SUM関数を使って合計値を出しています。その合計欄を参照したいと思っています。

シート名-------------差込データ

（総合計について）
合計値のセル---------差込データ!の列Gの下のほうの行にありますが、最終行ではありません。同じ行の列Bに「合計」と項目見出しが入っています。
合計値の桁数---------4桁から8桁の間で毎回変動します。

（各金種について）
列Ｑの金種別合計額が入る行に、項目見出しとして「金種別合計額」と入っています。
1万円の金種別合計額------列Ｒ
5千円の金種別合計額------列Ｓ
1千円の金種別合計額------列Ｔ　（※2千円は求めていません）
5百円の金種別合計額------列Ｕ
1百円の金種別合計額------列Ｖ
5十円の金種別合計額------列Ｗ
1十円の金種別合計額------列Ｘ
5円の金種別合計額--------列Ｙ
1円の金種別合計額--------列Ｚ

【差込先（表示させる）シート】
シート名-------------金種連絡票

各列の金額桁数
列Hは億の単位で、列Iは1千万円の単位です。順次、セルが右に移るごとに一桁下がります。
例えば、123,456,789円（1億2,345万6,789円）の場合
・ＨＩＪＫＬＭＮＯＰ(列)
・１２３４５６７８９　　と、各セルに表示させたいのです。

また、金額が少ない場合は、数字が入らないセルは空欄にしたいのです。
例えば、56,789円（5万6,789円）の場合
・ＨＩＪＫＬＭＮＯＰ(列)
・　　　　５６７８９　　このように、桁数に対応する各セルに表示させたいのです。

（総合計額を表示させるセル）
セル範囲　セルＨ9からセルＰ9までのセルに右詰で表示させたいのです。
　　　　　1億円の桁がセルＨ9で、順次桁が下がり、1円の桁がＰ9です。

（各金種額表示させるセル）
1万円はセルＨ10からセルＬ10までが計算により表示させる範囲で、ゼロ下４桁はシートに入力しておきます。
小額の金種は、各一行下に同じように表示します。

　※合計額の変動部分だけ計算により表示させたいです。
例えば、1万円の場合は、下のゼロ4つは計算でなく、シートにあらかじめ０を入れておきたいのです。これは、規定の様式でもあり、書体を変えて見やすくするという意味もあります。（金種の枚数ではなく、金額です）

1万円の金種別合計額
セルＨ10からセルＬ10までのセルに1万円単位の金額のみ右詰で表示させたいのです。下４桁（セルＭ10からセルＰ10までのセル）にはゼロが入っています。

5千円の金種別合計額
セルＨ11からセルＭ11までのセルに5千円単位の金額のみ右詰で表示させたいのです。下３桁（セルＮ11からセルＰ11までのセル）にはゼロが入っています。

1千円の金種別合計額　
セルＨ13からセルＭ13までのセルに1千円単位の金額のみ右詰で表示させたいのです。下３桁（セルＮ13からセルＰ13までのセル）にはゼロが入っています。
（※本当は行12に2千円の項目があるのですが、2千円札は使わないので省略します）

5百円の金種別合計額
セルＨ14からセルＮ14までのセルに5百円単位の金額のみ右詰で表示させたいのです。下２桁（セルＮ14からセルＰ14までのセル）にはゼロが入っています。

1百円の金種別合計額
セルＨ15からセルＮ15までのセルに1百円単位の金額のみ右詰で表示させたいのです。下２桁（セルＮ15からセルＰ15までのセル）にはゼロが入っています。

5十円の金種別合計額
セルＨ16からセルＯ16までのセルに5十円単位の金額のみ右詰で表示させたいのです。下１桁（セルＰ16のセル）にはゼロが入っています。

1十円の金種別合計額
セルＨ17からセルＯ17までのセルに1十円単位の金額のみ右詰で表示させたいのです。下１桁（セルＰ17のセル）にはゼロが入っています。

5円の金種別合計額
セルＨ18からセルＰ18までのセルに5円単位の金額のみ右詰で表示させたいのです。

1円の金種別合計額
セルＨ19からセルＰ19までのセルに1円単位の金額のみ右詰で表示させたいのです。

すみません。もちろん、全部の桁について教えていただこうなどとは思っていません。総合計の欄の表示方法が分かれば、後は当てはめて出来ると思います。
念のため、これからやりたいことを列記させていただきました。

どうか、よろしくお願いします。

回答No.2

chie65536 · ベストアンサー

＞例えば、123,456,789円（1億2,345万6,789円）の場合
＞・ＨＩＪＫＬＭＮＯＰ(列)
＞・１２３４５６７８９　　と、各セルに表示させたいのです。
＞
＞また、金額が少ない場合は、数字が入らないセルは空欄にしたいのです。
＞例えば、56,789円（5万6,789円）の場合
＞・ＨＩＪＫＬＭＮＯＰ(列)
＞・　　　　５６７８９　　このように、桁数に対応する各セルに表示させたいのです。

数値の入ったセルを元に、特定の１桁だけ抜き出す式
=(MID(RIGHT("_________"&数値の入ったセル,下何桁目),1,1))
（注：「_」は「半角の空白」の意味。実際に使う場合は「_」を「半角の空白」にする事）

これは「桁が無いなら空白」にする為「文字列」になる式。その為、標準の表示形式は左詰めになるので、セルの書式の「配置」で「横位置」を「右揃え」にしておく事。

なお、５００００円の場合は
・ＨＩＪＫＬＭＮＯＰ(列)
・　　　　５００００
になる。「０の場合も空欄」にして
・ＨＩＪＫＬＭＮＯＰ(列)
・　　　　５
としたい場合は、少し工夫が必要。

＞1万円の金種別合計額
前述の式の「下何桁目」を順に「９～５」にすればＯＫ。

＞5千円の金種別合計額
＞1千円の金種別合計額
＞5百円の金種別合計額
＞1百円の金種別合計額
＞5十円の金種別合計額
＞1十円の金種別合計額
＞5円の金種別合計額
＞1円の金種別合計額

これって「最適に両替されている」として計算して良いのか？
つまり「千の単位が５～９の時だけ、５千円札が１枚だけになる。千の単位が４～０の時は、５千円札が無い」と考えて良いのかどうか。

「最適に両替されている」と言う場合は、１円から順に
=MOD(合計金額,5)　　　…１円
=QUOTIENT(MOD(合計金額,10),5)*5　　　…５円
=QUOTIENT(MOD(合計金額,50),10)　　　…１０円
=QUOTIENT(MOD(合計金額,100),50)*5　　　…５０円
=QUOTIENT(MOD(合計金額,500),100)　　　…１００円
=QUOTIENT(MOD(合計金額,1000),500)*5　　　…５００円
=QUOTIENT(MOD(合計金額,5000),1000)　　　…１０００円
=QUOTIENT(MOD(合計金額,10000),5000)*5　　　…５０００円
で求まる。で、それぞれの結果は「０～４」か「５」しか有り得ない。つまり、セルは１つしか使わない筈。

あと「０枚なら空白」にしたいなら
=IF(MOD(合計金額,5)=0,"",MOD(合計金額,5))　　　…１円
=IF(MOD(合計金額,10)<5,"",QUOTIENT(MOD(合計金額,10),5)*5)　　　…５円
=IF(MOD(合計金額,50)=0,"",QUOTIENT(MOD(合計金額,50),10))　　　…１０円
=IF(MOD(合計金額,100)<50,"",QUOTIENT(MOD(合計金額,100),50)*5)　　　…５０円
など、IF文を使用する。

もし、千の桁とかが「６千円だけど、五千円札は無くて、千円札が６枚」とか「実は５千円札は何枚もあって、５枚あるなら、２５０００と表示」とか、そういうケースがある場合は
=(MID(RIGHT("_________"&(５千円札の枚数*5000),下何桁目),1,1))
の式で、最初と同じ方式で各セルを計算する必要があるし、表のどこかに「５千円札の枚数」が入力されてる必要がある。

補足

すごくスマートな式ですね。
例えば、合計額についてですが、
100万円の桁ならば、=(MID(RIGHT("_________"&差込データ!$G$152,7),1,1))
として表示させ、他の桁も思いどおりな結果を求めることができました。
大変ありがとうございました。

各金種についても、金種別の金額も算出しているので、これを当てはめ、
例えば1万円の金種の10万円の桁ならば、
=(MID(RIGHT("_________"&差込データ!$R$154,6),1,1))
として求めることができました。

大変スマートな式を覚えられて、大満足です。

ここで追加の質問で恐縮なのですが、

・シート名「差込データ」にある合計額は、その件数の増減により行が毎回変わるのですが、自動で判別させるにはどのようにしたらよいでしょうか？

ＭＡＴＣＨ関数でどうかと調べているのですが、どうにもうまく行きません。

スミマセンが、教えていただけないでしょうか。
よろしくお願いします。

回答No.4

kimino_naha

合計金額ですのでその範囲内では最大値となっているという前提ですが。

単純に MAX関数ではだめでしょうか。
=MAX(合計欄を含めた合計を求めた範囲以上の範囲)

お礼

回答ありがとうございます。

ＭＡＸ関数を調べたのですが、別シートを参照するところでつまづいているのでしょうか、うまく行きません。

現在、セル番地を指定するとうまく行くように式ができています。
=(MID(RIGHT("*********"&差込データ!$G$152,8),1,1))
*はスペースの代用です。

このシート名「差込データ」の列Ｇの最大値を当てはめるには、どのような式にすればよいかを教えていただけないでしょうか。

よろしくお願いします。

回答No.3

mu2011

仮にA1に合計欄として、H9セルに=MID(REPT(0,9-LEN($A1))&$A1,COLUMN(A1),1)*1を設定し、
P9までコピー、→セルの書式設定のユーザ定義で#を設定で如何でしょうか。

お礼

ありがとうございます。

ちょっと難解なのですが、よく考えて見ます。

回答No.1

root16

=QUOTIENT()
この関数は割り算の商を求める関数です。
99999を10000で割ったら商は9です。
他の桁も同様に行えば簡単だと思います。

補足

早速のご回答ありがとうございます。
=QUOTIENT()を使い、合計額についてはセルごとに分けることが出来ました。
素人考えで、次のようにしました。
１億円の桁のセル（絶対にこの桁は使わないのですが、様式としてあります）
=QUOTIENT(差込データ!$G$152,100000000)

１千万円の桁のセル
=QUOTIENT(差込データ!$G$152,10000000)

１百万円の桁のセル
=QUOTIENT(差込データ!$G$152,1000000)-I9*10

１十万円の桁のセル
=QUOTIENT(差込データ!$G$152,100000)-(I9*100+J9*10)

以降、桁が下がるごとに同じように上の桁の部分を引いて算出しています。

ここで追加の質問で恐縮なのですが、

・通常表示しないゼロ値は表示したくない。（例えば、100,0000の場合、000,100,000とはしたくないのですが、良い方法はあるでしょうか。
IFとISBLANKを使うと、一の桁とかもブランクになりそうです。）

・シート名「差込データ」にある合計額は、その件数の増減により行が毎回変わるのですが、自動で判別させるにはどのようにしたらよいでしょうか？

ＭＡＴＣＨ関数でどうかと調べているのですが、どうにもうまく行きません。

スミマセンが、教えていただけないでしょうか。
よろしくお願いします。