[[20101104135259]]　『合計から各値の個数を求める』（m&m）　ページの最後に飛ぶ

『合計から各値の個数を求める』（m&m）

　あらかじめ決まっている予算（合計値）を元に、各金額ごとの個数を逆算したいと思います。

　具体的には、予算が300,000円で、それを元に5,000円、4,000円、3,000円、2,000円の商品を購入したいと思います。

　いまは、適当に個数を入力して、合計に合わせるようにしているのですが、なかなかピッタリ合わずに苦労しています。

　関数を使って計算する方法がありましたら、お教えいただきたいと思い質問させていただきました。

　皆様のお知恵をお借りさせていただきたいと思いますので、よろしくお願いいたします。

 計算する際の条件はほかにはないのでしょうか？

 例えば2,000円を150個とか3,000円を100個とかでもかまわないのでしょうか？
 (独覚)

独覚様、早速のご回答ありがとうございました。

ご指摘いただいたように、他の重要な条件を記入し忘れていました。

5,000円から2,000円までの商品の合計数が100個になるようにしたいと思います。

お手数をおかけしてしまい申し訳ありませんでした。再度、よろしくお願いいたします。

 関数ではなくソルバーを使う方法です。

 先ず「ツール」―「アドイン」で「ソルバーアドイン」にチェックを入れます。
 (2007の場合はOfficeボタンから「Excelのオプション」を開き「アドイン」の「設定」タブから)

 次にA1セルからA4セルに
 2000
 3000
 4000
 5000
 と入力し、B1セルからB4セルには「0」、B5セルには
 =SUM(B1:B5)
 C1セルに
 =SUMPRODUCT(A1:A5,B1:B5)
 と入力します。

 そして「ツール」―「ソルバー」(2007の場合は「データ」―「分析」―「ソルバー」)を開き、「目的セル」に「$C$1」、
 「目標値」で「値」にチェックを入れて「300000」、「変化させるセル」に「$B$1:$B$4」と設定します。

 次に「制約条件」で 「追加」をクリックして「セル参照」に「$B$1:$B$4」「区間」「整数」と設定して「追加」、
 「$B$5」「=」「100」と設定して「追加」し、 その後「キャンセル」をクリックして条件設定を抜けます。

 そして「制約条件」が
 $B$1:$B$4 = 整数
 $B$5 = 100
 となっているのを確認して「実行」を押してみてください。
 (独覚)

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

お教えいただいた方法を試させていただいたところ、希望どおり各金額の個数を求めることができました。

ところが、合計金額と合計個数を変更して、再度試してみたところ、個数にマイナスのものが出てきてしまいました。（合計金額270,000円、合計数95個で試してみました。）

できれば、どの金額の商品も１個以上の数になるようにしたいのですが、何か良い方法はありますでしょうか。

追加の質問で申し訳ありませんが、ご回答いただけますようよろしくお願いいたします。

 すみません、うっかりしてました。

 制約条件に
 「$B$1:$B$4」「>=」「1」
 を追加してみてください。

 あと、A列の順番や「ソルバー」の「オプション」を変えると違う値(該当する複数の結果がある場合)になります。

 (独覚)

独覚様、詳しいご説明ありがとうございました。

教えていただいた方法で希望どおりの結果を求めることができました。

「ソルバー」というのは今まで全く知らなかったので、とても参考にさせていただくことができました。

あらためて深く感謝いたします。

 実際の商品では　2,000、3,000、・・というようなきりのいい数字(合計金額、合計数についても)ではないと思いますが。
 提示された例では(合計金額　300,000　　合計数　　100)
 3,000円×100個＝300,000円をもとに　3,000×2=2,000+4,000　また　3,000×3=2,000×2+5,000と置き換えられます。2,000　3,000　4,000　だけでは
 2,000	3,000	4,000	5,000
	100		
 1	98	1	
 2	96	2	
 ・・・・・・・・・・			
 5	90	5	
 ・・・・・・・・・・			
 10	80	10	
 ・・・・・・・・・・			
 45	10	45	
 ・・・・・・・・・・			
 49	2	49	
 またこれらの各組み合わせで　　3,000円の3個を2,000円×2個+5,000×1個に順次置き換えたものが考えられます。　　　(NB)

 独覚様教えて下さい。
 ソルバーなる言葉をはじめて知りました。
 一箇所疑問点があります。
 C1セルに
 =SUMPRODUCT(A1:A5,B1:B5)
 と入力します。
 これにするとC1が3000000になります。
 それでいいのでしょうか。
 よろしくご指導お願いします。
 (初心者)

 別のところでも忠告されていたかと思いますが(そしてそれに対して放っているようですが)質問を行うのであれば
 新規に行ってください。

 他人の質問に関することであればその時に

[[20101104135259]]　『合計から各値の個数を求める』（m&m）

 とその質問に対するリンクを張っておけばいいでしょう。

 また、やり取りを行っている最中であればまだしも名指しして回答を求めるのはマナー違反ですよ。
 ここはあくまでも質問を読んで回答しようと思った人が回答するところですので。

 と一般論を書いておいて。

 すみません(汗

 正しくは
 >=SUM(B1:B4)

 >=SUMPRODUCT(A1:A4,B1:B4)
 ですね。
 でもA5セルになにか値が入っていなければSUMPRODUCT関数の方は問題にならないと思いますが。
 一番疑わしいのは初心者さんが目標値に「3000000」と設定している場合だと思いますが。
 (というかセルの値、ソルバーを実行する前か後か等、判断材料が少なすぎますが)

 とりあえず次回からはきちんと新規に質問を行ってください(名指しせずに)
 (独覚)

 すみませんでした。質問をよく読んでいなかったです。
 予算は３０００００と書いてあるのを見過ごしていました。
 まったく持って申し訳の無いことをしました。
 どうもすみませんでした。恥ずかしい限りです。
 ３０００００は実行した後に出ました。
 私はソルバーという言葉をはじめて知りました。
 それで興味を持って読みながら実行してみたんです。
 ところがC1の>=SUMPRODUCT(A1:A4,B1:B4)
 これがびっくりするような数値が出たんです。
 ソルバーというものがそのようなことになっているのか疑問を持ったのです。
 名指しで質問してすみませんでした。
 あの数値でよいんですね。
 (初心者)

 質問では「300,000」ではなく「3,000,000」と書かれていますが「300,000」なのですか？

 そもそもC1セルの値が「300,000」になる組み合わせを求ているのだからC1セルが「300,000」になるのは当たり前では？

 当然、ヘルプで調べるなり関数辞典のようなものでSUMPRODUCT関数がどのような計算を行う関数か調べたうえで
 質問しているのですよね？

 そのうえでどこら辺がびっくりするような値なんでしょうか？
 (独覚)

コメント返信：

[ 一覧(最新更新順) ]

[[20101104135259]] 『合計から各値の個数を求める』（m&m） ページの最後に飛ぶ

[[20101104135259]]　『合計から各値の個数を求める』（m&m）　ページの最後に飛ぶ