こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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


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

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

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

(各金種について)
列Qの金種別合計額が入る行に、項目見出しとして「金種別合計額」と入っています。
1万円の金種別合計額------列R
5千円の金種別合計額------列S
1千円の金種別合計額------列T (※2千円は求めていません)
5百円の金種別合計額------列U
1百円の金種別合計額------列V
5十円の金種別合計額------列W
1十円の金種別合計額------列X
5円の金種別合計額--------列Y
1円の金種別合計額--------列Z


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

各列の金額桁数
列Hは億の単位で、列Iは1千万円の単位です。順次、セルが右に移るごとに一桁下がります。
例えば、123,456,789円(1億2,345万6,789円)の場合
・HIJKLMNOP(列)
・123456789  と、各セルに表示させたいのです。

また、金額が少ない場合は、数字が入らないセルは空欄にしたいのです。
例えば、56,789円(5万6,789円)の場合
・HIJKLMNOP(列)
・    56789  このように、桁数に対応する各セルに表示させたいのです。


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

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

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


1万円の金種別合計額
セルH10からセルL10までのセルに1万円単位の金額のみ右詰で表示させたいのです。下4桁(セルM10からセルP10までのセル)にはゼロが入っています。

5千円の金種別合計額
セルH11からセルM11までのセルに5千円単位の金額のみ右詰で表示させたいのです。下3桁(セルN11からセルP11までのセル)にはゼロが入っています。

1千円の金種別合計額 
セルH13からセルM13までのセルに1千円単位の金額のみ右詰で表示させたいのです。下3桁(セルN13からセルP13までのセル)にはゼロが入っています。
(※本当は行12に2千円の項目があるのですが、2千円札は使わないので省略します)

5百円の金種別合計額
セルH14からセルN14までのセルに5百円単位の金額のみ右詰で表示させたいのです。下2桁(セルN14からセルP14までのセル)にはゼロが入っています。

1百円の金種別合計額
セルH15からセルN15までのセルに1百円単位の金額のみ右詰で表示させたいのです。下2桁(セルN15からセルP15までのセル)にはゼロが入っています。

5十円の金種別合計額
セルH16からセルO16までのセルに5十円単位の金額のみ右詰で表示させたいのです。下1桁(セルP16のセル)にはゼロが入っています。

1十円の金種別合計額
セルH17からセルO17までのセルに1十円単位の金額のみ右詰で表示させたいのです。下1桁(セルP17のセル)にはゼロが入っています。

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

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


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

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

投稿日時 - 2007-11-02 14:44:09

QNo.3482965

困ってます

質問者が選んだベストアンサー

>例えば、123,456,789円(1億2,345万6,789円)の場合
>・HIJKLMNOP(列)
>・123456789  と、各セルに表示させたいのです。

>また、金額が少ない場合は、数字が入らないセルは空欄にしたいのです。
>例えば、56,789円(5万6,789円)の場合
>・HIJKLMNOP(列)
>・    56789  このように、桁数に対応する各セルに表示させたいのです。

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

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

なお、50000円の場合は
・HIJKLMNOP(列)
・    50000
になる。「0の場合も空欄」にして
・HIJKLMNOP(列)
・    5
としたい場合は、少し工夫が必要。

>1万円の金種別合計額
前述の式の「下何桁目」を順に「9~5」にすればOK。

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

これって「最適に両替されている」として計算して良いのか?
つまり「千の単位が5~9の時だけ、5千円札が1枚だけになる。千の単位が4~0の時は、5千円札が無い」と考えて良いのかどうか。

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

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

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

投稿日時 - 2007-11-02 16:19:36

補足

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

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

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


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

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

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

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

投稿日時 - 2007-11-02 16:58:41

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

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

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

投稿日時 - 2007-11-02 23:06:28

お礼

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

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

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

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

よろしくお願いします。

投稿日時 - 2007-11-05 18:27:13

ANo.3

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

投稿日時 - 2007-11-02 16:37:46

お礼

ありがとうございます。

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

投稿日時 - 2007-11-05 18:28:05

ANo.1

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

投稿日時 - 2007-11-02 14:49:10

補足

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

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

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

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

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



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

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

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

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

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

投稿日時 - 2007-11-02 16:25:14

あなたにオススメの質問