エクセル関数で全体量の半分に近い組合わせを出したい

質問

(1)　１１．５
(2)　１５．６
(3)　３０．５
(4)　５０．８
　　　.
　　　.
　　　.
　　　.
(20)　４５．４

これらのような数字が２０個程度あり、例えば全て足した重量が５００だったとすると大体半量の２５０に近い組み合わせを関数で出したいです。
条件としては、一つの数字は一回しか使えません、きっちり半量じゃなくても出来るだけ近い値で良いです。どの番号の物を組み合わせて２５０ぐらいになったか分かるようにしたいです。

何かちょうど良い関数はあるでしょうか。

回答No.3

tsubuyuki · ベストアンサー

ひとつだけ補足です。

条件は「０か１」ですから、今までの条件をすべて削除して、
セル参照に「＄Ｃ＄１：＄Ｃ＄２０」、真ん中の不等号を「ｂｉｎ」にし、、
制約条件に「バイナリ（自動入力）」としてやると、条件がすっきりしますね（汗）。

情報があっちに行ったりこっちに行ったりで混乱させて申し訳ないです。

お礼

ありがとうございます！
できました！

回答No.2

tsubuyuki

> Ｃ列に－１が出て引き算も組み込まれていました。

セル参照に「＄Ｃ＄１：＄Ｃ＄２０」、真ん中の不等号を「＞＝」、
制約条件に「０」と入力し、追加ボタンを押します。
これで、「０以上」が追加されます。

・・・が、これだと「２５０丁度」になるパターンが無い場合にエラーがでますね（汗）。

申し訳ないです。
私もまだまだ勉強不足のようです（陳謝）。

お礼

ダメな場合は少し値を変えれば大丈夫ですよね^^
十分作業が楽になりましたので、ありがとうございました。

回答No.1

tsubuyuki

関数は（おそらく）ありません。

かわりにエクセルの拡張機能「ソルバー」というアドインを使うと比較的楽です。
２０１０を基準に説明しますので、
ヴァージョンが違う際は逐次お調べください。

まず、「ソルバー」アドインを組み込みます。
リボンの「ファイル」タブから「オプション」⇒「アドイン」と進みます。
（この時点でアドイン一覧に「ソルバー アドイン」が有れば、この後は不要です。）
管理：Excel アドイン　であることを確認し、「設定」ボタンを押します。
ダイアログ内の「ソルバー アドイン」にチェックを入れ、「ＯＫ」で閉じます。
これでアドインの組み込みが完了です。
リボンの「データ」タブに「ソルバー」があることを確認してください。

データを準備します。
少しでも簡単に考えられるように、図の左側のように
Ａ列：連番、Ｂ列：数字、Ｃ列：空白として、
Ｄ列には「＝Ｂ２＊Ｃ２」と入力し、下方向にフィルしておきます。
Ｂ２１セルには「＝ＳＵＭ（Ｂ１：Ｂ２０）」で数字の合計を求め、
Ｂ２２セルには「＝Ｂ２１／２」として、「半量」を計算しておきます。
Ｄ２１セルには「＝ＳＵＭ（Ｄ１：Ｄ２０）」として、計算結果の合計を計算させます。

では、「ソルバー」を使ってみます。
先ほど追加された、リボンの「データタブ」⇒「ソルバー」をクリックし、
「ソルバーのパラメーター」ダイアログを表示します。
ダイアログの上から順に
・目的セルの設定：＄Ｄ＄５１
・目標値：指定値＝２５０　　（←Ｂ２２セルの計算結果を「手入力」しましょう）
・変数セルの変更：＄Ｃ＄１：＄Ｃ＄２０
としてやります。
ざっと説明すると、「変数セル」の値をアレコレ組み合わせて、
「目的セル」の計算結果を、「目標値」に近づける・・という感じです。

もう一工夫。
> 半量に近い組み合わせ
> 一つの数字は一回しか使えません
という事ですから、Ｃ列には「０か１」しか入らない、という条件がつきます。
これをソルバーに認識させるため、「制約条件の対象」に条件を入れ、制約をかけます。
「追加ボタン」をクリックし、「制約条件の追加」ダイアログを出します。
まず、セル参照に「＄Ｃ＄１：＄Ｃ＄２０」、真ん中の不等号を「＜＝」、
制約条件に「１」と入力し、追加ボタンを押します。
これで、Ｃ列には「１以下」の数字しか入らなくなりました。
でも、Ｃ列に小数が入られると困りますので、
セル参照「＄Ｃ＄１：＄Ｃ＄２０」で、記号を「ｉｎｔ」にしてやります。
（制約条件は自動的に「整数」が入ります。）
これを追加することによって、Ｃ列には「１以下かつ整数」＝「０か１」しか認めない、
という制約が整いました。

これで、準備完了です。
ここまで頑張って「解決」ボタンを押すと、エクセル（と言うかソルバー）が
一生懸命色々な組み合わせを（自動的に）試し、
（少々時間はかかりますが）結果を導いてくれます。
その結果が、図の右側半分です。

使い方次第で非常に便利に使える機能ではあります。
是非、お試しくださいませ。

お礼

ありがとうございます！
早速作ってやってみましたが、Ｃ列に－１が出て引き算も組み込まれていました。
実際には引く事は出来ないので、１か０だけにしたいのですが条件追加の所でどう入力すれば良いのでしょうか。

あと画面をプリントスクリーンで撮ったのですが、どこにどうやって貼り付けたらいいのだかが・・・・