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

質問

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

例えば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 · ベストアンサー

クイズを解くようでしたが、やって見ました。自信はありませんが。一度検証をお願いします。
A1:E8は
金種 前日残金 前日残金 手持ち目標　追加額
枚数 金額
10000 465 4650000
5000 283 1415000 2000000 585000
1000 2261 2261000 2000000 0
500 364 182000 200000 18000
100 1556 155600 200000 44400
計 8663600
式は
A列なし、B列なし。C3に=A3*B3、C7まで縦複写、C8に
=SUM(C3:C7)
D列式なし。E4に=IF(D4>C4,D4-C4,0)、E5からE7まで複写。
F1:J8
冊束硬貨巻　札束考慮　同左列 調整のみ 両者計

3790000 3790000
500000 500000 500000 5000 505000
100000 0 0 1000 1000
50000 0 0 0 0
5000 40000 40000 400 40400
4336400 6400 4336400
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列）が要ると思いますが略。

お礼

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

回答No.4

taisuke555

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

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

前日残金　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百万なのですが数えやすさ優先にします。

回答No.3

mshr1962

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

お礼

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

回答No.2

mshr1962

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万円の所がマイナス表示になるので
別途、振り分けが必要になります。

お礼

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

回答No.1

imogasi

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

お礼

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