Excelで予算を最大限使用する組み合わせ

質問

こんにちは。

Excel2003で、予算額が決まっている中で物品を購入しなければならないのですが、
予算をなるべく残さないようにするには何を何個購入すればよいのか計算する方法はありますか？

【例】予算2000円
　　商品名　　　金額
　　りんご　　　100円
　　みかん　　　100円
　　ぶどう　　　250円
　　いちご　　　300円　

上記のような物品の一覧表があり、予算は2000円と決まっています。
この場合、どの商品を何個購入すれば2000円の予算を最大限に使用できるか調べる方法はありますか？
(つまり、おつりの額がなるべく小さくなるようにしたいです。)

このとき、上記の商品の中から何を買っても構いません。
おつりの額が小さくなるのであれば買わない商品があってもいいです。

ネットでいろいろと検索し、ソルバーの機能かなと思いましたが、
うまく結果を求めることができませんでした…。

ご回答いただけると助かります！
宜しくお願い致します。

回答No.3

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

C2セル
=ROUNDUP($B$7/B2,0)
下へオートフィル
総当り数 =PRODUCT(C2:C5)
E2セル =B2*D2
E6セル =SUM(E2:E5)

ツール - ソルバー
目的セル E6
値(目標値) 2000
変化させるセル $D$2:$D$5
制約条件
$D$2:$D$5 <= $C$2:$C$5
$D$2:$D$5 区間 整数
$D$2:$D$5 >= 0
上記は 目標値と完全一致する場合です。
あまりを最小にするにはもう一工夫必要と思われます

ちなみに 27個の総当りはとんでもない数になりそうです

お礼

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

とてもご丁寧な解説で分かりやすかったです。
ご教授いただいた内容で最適値が見つかりました。
とても助かりました！

もしご存知であればで構わないのですが、
この商品は多くても1個しか購入できない(2個以上は購入できない)といった場合の
操作方法はご存知でしょうか？

図々しく何度も質問してしまい申し訳ありません。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

>この商品は多くても1個しか購入できない(2個以上は購入できない)といった場合
C列で制限(制約条件)をかけていますので、対象商品を1とすればよいでしょう。
人に教えるなら当方が行ったことを理解できるように努めてください。

「目標/金額」は余分な計算をしないように制約条件をつけています。

目標値と完全に一致しない場合で、あまりを最小値にしようと思うととんでもなく時間がかかりそうです。制約条件が多いほど計算時間は短くなると思います。

お礼

CoalTarさんありがとうございます。

制約条件に「$C$2:$C$28<=1」を追加するとちょうど最適な解を
見つけることができました！

分かりやすく、そして度重なる質問にもご丁寧に回答していただき
本当に感謝しております。

ありがとうございました。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

「ナップサック問題」と言うヤツでしょう。
キーワード検索してみてください

ソルバーは答えが一つになるなら有効と思われます。
求める答えが幾通りにもなる場合のひとつだけ求められれば良いなら
ソルバーでも良いかもしれません

つまるところ総当りしかないのかなあって考えます。

お礼

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

ナップサック問題と呼ばれるのですね。
恥ずかしながら初めて知りました。

検索してみましたが、VBAでの結果を割り出すという答えが多くありました。
実は、Excelで解く方法が分かったら、これをまた別の社員に教えなければなりません。
私がVBA等、プログラミングの知識がまったくないため、できればソルバーなどの
Excelに用意されている機能を使って答えを出したいと思っています。
(質問しておきながらわがままですみません)

予算額を最大限に使用できるのであれば結果は一つで構いませんので、
ソルバーでの計算方法をご教授いただけますか？

今回はA1:A27に商品の金額の一覧を入力しており、
予算額は数式の結果ではなく数値として直接入力しています。
そして、予算額・商品の金額含め、すべて1千万単位です。

宜しくお願い致します。

回答No.1

warumx

こんにちは。
おっしゃるように、ソルバーが最も手っ取り早いでしょう。
それで求めると、例えば、
　りんご　2個　金額＝2*100=200 円
　いちご　6個　金額＝6*300=1800 円
　合計額　2000円
となり予算に等しい。
でも、商品の種類が少ない場合は単純な数え上げでも
見つかるのでは。
ソルバーは何も考えなくともよいメリットはあります。
どこで上手く行ってませんか。

お礼

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

実はその商品の金額がすべて1千万単位となっており、
目標値となる予算は59,101,000になります。
このとき、予算額は数式ではなく数値としてセルに入力しています。

たとえば、A1:A27に商品の一覧を入力していた場合、
どのようにソルバーで結果を割り出せばよいのか教えていただけると助かります。

度重なる質問となり、申し訳ありませんm(__)m