Excelで、複数の値からある合計数に一致する組み合わせを探す。
例えば、「値: 100, 78, 25, 66, 120, 20」を使って、
合計が189になるような組み合わせは…「78, 25, 66, 20」となる。
(これはいわゆるナップサック問題の一種)
これをExcelに計算してもらう方法。
色々悩んだ末に、Web検索でそのものズバリの解答がありました。
→『複数の値からある合計数に一致する組み合わせ』（シン） エクセル Excel [エクセルの学校] [
]
ここの一番下のatiboh氏の回答です。ありがとうございます！
以下、上記リンク先を参考に、画像を加え、Excel2013で説明していきます。
(Excel2010でも同様に動くことを確認)
「値: 100, 78, 25, 66, 120, 20」を使って、合計が189になるような組み合わせを探します。
まず、Excel標準のアドインであるソルバーを追加します。
「ファイル」タブをクリックし…
「オプション」をクリック
「アドイン」をクリックし、管理を「Excel アドイン」にし、「設定」ボタンをクリック
「ソルバー アドイン」にチェックを入れ、「OK」ボタンをクリック
これで「データ」タブのリボンの一番右端に、「分析」グループと「ソルバー」ボタンができます。
次に、元となる「値: 100, 78, 25, 66, 120, 20」をA列に入力します。
B列に作業用セル(今は空白にしておく)を作ります。
そして、SUMPRODUCT関数でA列とB列を掛け算します。(セルC2に、”=SUMPRODUCT(A2:A7,B2:B7)“と入力)
次に、いよいよソルバーを使います。
「データ」タブ→「ソルバー」をクリック
「ソルバーのダイアログ」が開くので、
「目的セルの設定」を”$C$2“に、
「目標値」を「指定値」にし”189“に、
「変数セルの変更」を”$B$2:$B$7“にします。
さらに、制約条件の対象を追加するために「追加」ボタンをクリック
「制約条件の追加」ダイアログにて、
「セル参照」を”$B$2:$B$7“に、
その右側にあるリストボックスを”bin“にして、
「OK」ボタンをクリック
これで「解決」ボタンを押すと、Excelが計算してくれます。
計算がうまくいくと、”ソルバーによって解が見つかりました。”と表示されます。
「OK」ボタンを押すと答えが表示されます。
空白にしておいた作業セル(B列)に、”1″か”0″の値が入ります。
“1”が入ってるものが組み合わせ対象です。
この例の場合、”78, 25, 66, 20″が組み合わせ対象となります。
78 + 25 + 66 + 20を計算すると、”189″になるはずです。
制約条件の追加の際に、「バイナリ制約条件のセル参照には、変数セルのみを含めてください。」というアラートが表示されて、先へ進めません。何か解決方法をご存知でしょうか。ご教示ください。
制約条件追加の際に、参照しているセルをA列にしていないでしょうか。
私も同じアラートが表示され、確認したら間違えてA列を参照していたので、B列にしたら直りました。
>>山さん
コメントありがとうございます。コメント返し遅くなってしまいすいません…
お役に立てずにすいません。”通りすがりの人”さんのコメントでうまくいきましたでしょうか？
>>通りすがりの人さん
親切なコメントありがとうございます。コメント返し遅くなってしまいすいません。
自分一人では結構抜けてるところもあるので、こういうコメントは大変ありがたいです！
すばらしい！！100年かかる仕事が5分でできた！
自分もどうすれば良いか悩んだのでいたので、お役に立ててよかったです。
])にも改めて感謝です！
またこの記事の元になった記事様(http://www.excel.studio-kazu.jp/kw/20101029154929.html [
合計と完全に一致ではなく合計以下（なるべく差異を少なく）の数値になるような組み合わせを探すにはどうしたらよいでしょうか。
目標値を、指定件数に、余りなく振り分けたい時もこの方法は適用可能でしょうか？