エクセルで金種一覧 枚数制限付を作りたい

以前のQ&Aで金種のエクセル計算は何度かありましたが、
最近　銀行での両替について両替枚数50枚以上は手数料がかかるので、
1万円、5千円の枚数を出来るだけ少なく、千円を多く、且つ50枚で収まるような計算の金種一覧を作成したいのですけど
アドバイスをお願いいたします

No.5 ベストアンサー 回答者： deus_ex_machina 回答日時： 2007/11/05 17:55

#4です。

実は
「なんで硬貨の取り扱いについて言及がないんだろう？
　キリのいい額しかおろさないのかなぁ？」
と思ってはいたのですが、やはり硬貨の処理もいるんですね(^^;;

硬貨については最小限の枚数で、ということでよろしいですね？
二千円札については考えなくてよろしいですね？

※
「百円玉も多めに…」なんてことになると、
「千円札を優先するのか百円玉を優先するのか」という問題がでて、
まったく別の次元の課題になってしまいます。

さて。

以下、1,000円未満の額について普通に金種表を作り、
#4で50枚だった制限を「50-硬貨の枚数」にして計算してるだけで、
処理自体は#4と変わりません。

まず、２行目のD2:L2セルにそれぞれ
1,5,10,50,100,500,1000,5000,10000
を入力してください。

次に３行目を下記のように。

A3セル：払い出し総額
　手打ち

B3セル：作業列ア
　=INT(A3/1000)

C3セル：作業列イ
　=50-SUM(D3:I3)

D3～I3セル：一円玉～五百円玉
D3セル：一円玉
　=INT(MOD($A3,E$2)/D$2)
としてI3まで右方にフィル

J3セル：千円札
　=B3-(B3>C3)*(INT((B3-C3)/9)*10+INT(MOD(B3-C3,9)/4.5+0.9)*5)

K3セル：五千円札
　=(B3>C3)*(INT(MOD(B3-C3,9)/4.5+0.9)=1)

L3セル：一万円札
　=(B3-J3-5*K3)/10

M3セル：合計金額（チェック用）
　=SUMPRODUCT($D$2:$L$2,D3:L3)

N3セル：合計枚数（チェック用）
　=SUM(D3:L3)

ex.
合計金額：\76,783
　一円玉：３枚
　五円玉：０枚
　十円玉：３枚
五十円玉：１枚
　百円玉：２枚
五百円玉：１枚
　千円札：36枚
五千円札：０枚
一万円札：４枚
合計枚数：50枚

合計金額：\98,276
　一円玉：１枚
　五円玉：１枚
　十円玉：２枚
五十円玉：１枚
　百円玉：２枚
五百円玉：０枚
　千円札：33枚
五千円札：１枚
一万円札：６枚
合計枚数：47枚

(Excel2003で動作確認済)

この回答へのお礼

ありがとうございます
無事解決致しました

お礼日時：2007/11/07 12:54

No.4 回答者： deus_ex_machina 回答日時： 2007/11/04 03:10

ご質問の趣旨を取り違えていましたらすみません。

こういったことでしょうか？

以下、２行目に入力してください。
（１行目は見出しに必要かと思いましたので）

A2セル：払い出し総額
　手打ち

B2セル：作業列
　=A2/1000

C2セル：千円札の枚数
　=B2-(B2>50)*(INT((B2-50)/9)*10+INT(MOD(B2-50,9)/4.5+0.9)*5)

D2セル：五千円札の枚数
　=(B2>50)*(INT(MOD(B2-50,9)/4.5+0.9)=1)

E2セル：一万円札の枚数
　=(B2-C2-5*D2)/10

F2セル：合計金額（チェック用）
　=1000*C2+5000*D2+10000*E2

G2セル：合計枚数（チェック用）
　=SUM(C2:E2)

ex.
合計金額：\76,000のとき、
　千円札：46枚
五千円札：０枚
一万円札：３枚
合計枚数：49枚

合計金額：\80,000のとき、
　千円札：45枚
五千円札：１枚
一万円札：３枚
合計枚数：49枚

払い出し総額が例えば\70,000のとき、
45枚＋１枚＋２枚＝48枚　としても
45枚＋３枚＋１枚＝49枚　としても
千円札の枚数は一緒ですが、
とりあえず前者の解を返すようにしています。

IF関数を使って普通に書くと　
　C2：=A2/1000-IF(A2<=50000,0,INT(((A2/1000)-50)/9)*10+IF(MOD(((A2/1000)-50),9)=0,0,IF(MOD(((A2/1000)-50),9)<=4,5,10)))
のように長大になってしまうので、若干テクニカルな書き方をしています。
詳しい解説はご勘弁ください。

この回答への補足

私の質問の仕方が悪いのかと思います
申し訳ありません
金種には1円の位までを含んで50枚以下になる計算を考えてます

お手数ですがご教示願えればと思います

補足日時：2007/11/05 16:32

No.3 回答者： noname#79209 回答日時： 2007/11/02 16:33

#2です。

補足は拝見しました。でもまだ疑問がのこります。
質問者さんには自明の事柄でも、回答者には解りません。
そこで、みずほ銀行へ電話で問い合わせてみました。
そこで判明したこと。
1.払い出しが50枚を超えても、金種を指定しなければ手数料はかからない。
2.金種を指定した途端、「万札も含め」50枚を超えたら手数料がかかる。
このような事を知りたかったのです．．．

グチはともかく、作ってみましたが、一発で算出するのは大変なのと、複数の結果が出るので、
ユーザーが組み合わせのなかから選ぶような形態を採りました。（ダサイ方法ですが．．．）

1.おろす金額をセルA1に入力します。（104000などと入力します）
2.セルA3～A13に、0～10をタテに入力します。(万札の枚数です＝△万円)
3.セルB1～V1に、0～20をヨコに入力します。（5千円札の枚数です）
4.セルB2に数式で「=B$1*5000」と入力し、これをC2～V2へコピーします。
　（5千円札分の金額です）
5.セルB3に数式で「=($A$1-$A3*10000-B$1*5000)/1000」と入力し、
　これを、タテ・ヨコにV13までコピーします。
　これで、一応A1に入力した金額での一万円札、五千円札、千円札の組み合わせが表示されます。

ただ、これでは見にくいので、
6.セルB15に、数式「=MAX((B$3:B$13+B$1+$A$3:$A$13<51)*(B$3:B$13))」と入力し、
　「ENYER」を押さず、「CTRL」と「SHIFT」を両方押したまま「ENTER」を押します。
　その結果、数式全体が「{=MAX((B$3:B$13+B$1+$A$3:$A$13<51)*(B$3:B$13))}」と
　「{」と「}」で括られていることを確認して下さい。
7.この数式を、C15からV15へヨコにコピーします。
8.セルW3に、数式「=MAX(($B3:$V3+$B$1:$V1<(51-$A3))*($B3:$V3))」と入力し、
　「ENYER」を押さず、「CTRL」と「SHIFT」を両方押したまま「ENTER」を押します。
　その結果、数式全体が「{=MAX(($B3:$V3+$B$1:$V1<(51-$A3))*($B3:$V3))}」と
　「{」と「}」で括られていることを確認して下さい。
9.この数式を、W4からW13へタテにコピーします。
10.セルB3～V13を選択します。
11.選択された状態のまま、「書式」－「条件付書式」をクリック
12.「数式が」を選択し、数式「=AND(B3=$W3,B3=B$15)」を入力し、
　　「書式」をクリックし、「パターン」タブをクリック。
　　目立つ色を選択したら、「OK」をクリックし、さらに「OK」をクリック。

これで、可能性のある組み合わせのセルのバックに色がつきますから、
この中から、質問者さんが納得する組み合わせを選んで下さい。

No.2 回答者： noname#79209 回答日時： 2007/11/01 16:14

一応エクセルで作ってはみましたが、作っているうちに「意味ないのでは？」と思い始めました。

もし、ＣＤ機で10万円おろすとき、「両替ボタン」を押すと手数料が取られるなら、5万円づつおろせば良いのでは？
つまり、何回かに分けておろせば良いのではと思ってしまいます。

この回答への補足

説明不足ですみません
両替機を使う場合は50枚以下でも105円の手数料がかかり
通帳からの払い出しの場合、50枚以下は無料
50枚以上は315円の手数料がかかります

ですので、両替機を使わずに
払い出しの際に手数料がかからない
50枚以下での金種一覧を作りたいのです

補足日時：2007/11/02 10:01

No.1 回答者： imogasi 回答日時： 2007/10/31 15:16

最近の銀行の両替機を使ったことが無いのだが、質問者のよく使う金融機関の枚数制限状況を具体的に補足してください。

その金融機関の使用機種でばらつきガあるように思うが。
総枚数合計で５０枚以下の制限がかかっていますか。
それをはみ出した場合、次善の策は、どうするのが良いのでしょう。
そして質問者はどういう風にしてほしいのか、具体的に考えるのが質問者の仕事でしょう。エクセルは計算道具に過ぎないのですから。
それを読者と共有しないと回答を（できるかどうか）考えることもできません。
＞1万円、5千円の枚数を出来るだけ少なく・・
これでは枚数が増えるのでは？
ーー
なんとなく、ＶＢＡでプログラムを組まないと解決できないように思うのですが。

この回答への補足

当方、老人ホームの事務員です
小口現金として、毎月始めに１万から10万の現金をおろします。
使用用途はおもに入所者の両替などです。

ですので、出来るだけ多く千円札をおろしたいのですが、
銀行で両替（現金をおろす）場合に50枚以上ですと315円の手数料が発生します。

ですので、出来るだけ手数料が、かからず多くの千円札をおろすために
エクセルで計算ができないかと思い書き込みました

方法はマクロ、VBAどの方法でもかまいません

ご教示願えればと思います

補足日時：2007/10/31 17:55