エクセルで金種表を作り方を教えてください

銀行へ両替依頼をするときにいつも悩みます

例えば13,000,000円あります。
前日の残金は8,663,600円です。
本日の入金は4,336,400円です。
この本日入金額を金種ごとに振り分けるのですが
条件があるのです。
各金種には前日の残金があり、その日の合計の金額もあります。さらには受取りやすいように束になるようにしたいのです。

残金
1万円　4,650,000円
5千円　1,415,000円　　
1千円　2,261,000円
5百円　　182,000円
1百円　　155,600円とあります。
本日入金する4,336,400円を以下の条件で振り分けます
5千円と千円は概ね2百万円にします
5百円と百円は概ね20万円にします
そのとき受取りやすいように束で受け取りたいのです
5千円は50万が束、千円は10万が束
5百円は2万5千円が束、百円は5千円が束です
端数は百円千円一万円とそれぞれ出てきます。

本日入金する金額を金種ごとに振り分ける
計算式ありませんか？

No.5 ベストアンサー 回答者： imogasi 回答日時： 2004/05/29 16:01

クイズを解くようでしたが、やって見ました。

自信はありませんが。一度検証をお願いします。
A1:E8は
金種前日残金前日残金手持ち目標　追加額
枚数金額
100004654650000
500028314150002000000585000
10002261226100020000000
50036418200020000018000
100155615560020000044400
計8663600
式は
A列なし、B列なし。C3に=A3*B3、C7まで縦複写、C8に
=SUM(C3:C7)
D列式なし。E4に=IF(D4>C4,D4-C4,0)、E5からE7まで複写。
F1:J8
冊束硬貨巻　札束考慮　同左列調整のみ両者計

37900003790000
5000005000005000005000505000
1000000010001000
500000000
5000400004000040040400
433640064004336400
F4の=A4*100、F5は=A5*100、F6は=A6*100、F7は=A7*50
G4は=INT(E4/F4)*F4、G5からG7まで複写。
H3は=INT((H8-(H4+H5+H6+H7))/A3)*A3
H4は=G4、H5はG5,H6はG6、H7はG7、H8は本日入金額
I4は=INT(I8/A4)*A4
I5は=INT((I8-I4)/A5)*A5
I6は=INT((I8-I4-I5)/A6)*A6
I7は=INT((I8-I4-I5-I6)/A7)*A7
I8は=H8-SUM(H3:H7)
J3は=H3+I3、J4からJ7は式を縦複写、J8は=SUM(J3:J7)
J列が答えの積もり。
次の日への繋ぎ（B、C列）が要ると思いますが略。

この回答へのお礼

貴重な時間を割いてありがとうございます。
早々に計算式を試して見ます。

お礼日時：2004/05/31 10:42

No.4 回答者： taisuke555 回答日時： 2004/05/28 14:01

この質問の場合、各金種の束とバラはいくらになるのでしょう？

以下と違っていたら、補足してください。

前日残金　8,663,600　　　　本日入金　4,336,400　　　　　　4,336,400
金種　　　枚数　　金額　　　　必要数　束単位　束数　　ばら　　金額
10000　　465　　4,650,000　　　　　　　　　　　　　　　　　328　　3,280,000
5000　　283　　1,415,000　　　400　　　100　　　　2　　　　　　　　1,000,000
1000　　2261　2,261,000　　　2000　　　100　　　　0　　　6　　　　　6,000
500　　　364　　182,000　　　　400　　　50　　　　1　　　　　　　　25,000
100　　1556　　155,600　　　　2000　　　50　　　　5　　　4　　　　25,400

A1（B1と結合）：前日残金
C1:=SUM(C3:C7)
D1（E1と結合）：本日入金
F1：★[入力]

A2～H2：各見出し

A3～A7：（金種）10000,5000,1000,500,100と入力
B3～B7：★前日残金の金種ごとの枚数[入力]
C3～C7：（金額）C3に=A3*B3と入力しC7までコピー

D4～D7：（必要数）400,2000,400,2000と入力
E4～E7：（束単位）100,100,50,50と入力
F4～F7：（束数）F4に=MAX(0,ROUNDUP((D4-B4)/100,0))と入力しF7までコピー

G3：=INT((F1-SUMPRODUCT(F3:F7*A3:A7*E3:E7))/A3)
G5：=INT((F1-SUMPRODUCT(A3:A7*E3:E7*F3:F7)-G3*A3)/A5)
G7：=INT(MOD(F1-SUMPRODUCT(A3:A7*E3:E7*F3:F7),1000)/A7)

H3～H7：（金額）H3に=A3*(E3*F3+G3)と入力しH7までコピー

H1：（確認用）=SUM(H3:H7)

●１度表を作成すれば、後は★の項目のみ変更すればOKです。
●１万円がマイナスになる場合がありますが（本日入金が少ない場合）、
　必要数重視なら、前日残金からマイナス分、１万円を持って行く事になります。

としてみました。（数式はともかく、考え方が合っているか分かりません）

この回答への補足

束、バラの数は決まってません。
今回の場合ですと、一万円が328枚になりますが
束が３とバラが28となります。
その場合、千円の束を2にすると合計金額が数えやすくなります。
千円は概ね2百万なのですが数えやすさ優先にします。

補足日時：2004/05/28 14:39

この回答へのお礼

ありがとうございます。
計算式参考にしながら表計算を作ってみます。

お礼日時：2004/05/28 14:49

No.3 回答者： mshr1962 回答日時： 2004/05/28 13:07

#2のmshr1962です。

すみません。少し訂正が必要でした。
#2のままだと1万円の所で5,000円の調整があります。
1百円=IF(155600<200000,CEILING(200000-155600,10000),0)+MOD(4336400,10000)
としてください。
1百円の所で5千円の束が1個追加の計算になります。

この回答へのお礼

重ね重ねありがとうございます。

お礼日時：2004/05/28 14:38

No.2 回答者： mshr1962 回答日時： 2004/05/28 12:28

1百円=IF(155600<200000,CEILING(200000-155600,5000),0)+MOD(4336400,5000)

5百円=IF(182000<200000,CEILING(200000-182000,25000),0)
1千円=IF(2261000<2000000,CEILING(2000000-2261000,100000),0)
5千円=IF(1415000<2000000,CEILING(2000000-1415000,500000),0)
1万円=4336400-(上記の合計)

実際の式は入金額と各残金のセルを参照してください。
ただし入金額と1万円以下の残金の計が440万円以上を想定しています。
額が小さい場合は1万円の所がマイナス表示になるので
別途、振り分けが必要になります。

この回答へのお礼

ありがとうございます。
計算式までありがとうございます。実際に試してみます。

お礼日時：2004/05/28 14:37

No.1 回答者： imogasi 回答日時： 2004/05/28 12:04

http://okweb.jp/kotaeru.php3?q=870955
に単純な金種表の関数によるご回答例があります。
しかし本質問は、前残があり、札束・硬貨巻き条件や「概ね」条件がついている（特に後２点）ので関数では、不可能（ＩＦを３段以上ネストするのは、ここに入れるのが私の好み）と思います。
上記で私はＶＢＡで回答を（遠慮がちに）上げましたが
本件はなおさらＶＢＡでプログラムを組まないとだめなように思います。
またプログラムの行数も、上記の５倍程度にならないかと
思います。職場での仕事の、スモール課題をコンピュタ・システム化パソコン化するものですが、一般には関数では実現が難しいと思っています。特に職場の他人にも使ってもらう場合は付加的な仕組みも備えてないとだめなことが多く、複雑化するものです。

この回答へのお礼

ありがとうございます。
同様の質問が無いか確認したのですが今回の質問と同様とはいえなかったので質問しました。
確かに「概ね」の条件がありすぎますね。
職場で使用すしたいと考えていたんですがエクセルの式では無理ですかね。
少し勉強してみます。

お礼日時：2004/05/28 14:34