[Excel]複数の値からある合計に一致する組み合わせ

投稿


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標準のアドインであるソルバーを追加します。
ファイル」タブをクリックし…
001
 
オプション」をクリック
002
 
アドイン」をクリックし、管理を「Excel アドイン」にし、「設定」ボタンをクリック
003
 
ソルバー アドイン」にチェックを入れ、「OK」ボタンをクリック
004
 
これで「データ」タブのリボンの一番右端に、「分析」グループと「ソルバー」ボタンができます。
005
 



 
次に、元となる「値: 100, 78, 25, 66, 120, 20」をA列に入力します。
006
 
B列作業用セル(今は空白にしておく)を作ります。
そして、SUMPRODUCT関数でA列とB列を掛け算します。(セルC2に、”=SUMPRODUCT(A2:A7,B2:B7)“と入力)
007
 


 
次に、いよいよソルバーを使います。
データ」タブ→「ソルバー」をクリック
005
 
「ソルバーのダイアログ」が開くので、
目的セルの設定」を”$C$2“に、
目標値」を「指定値」にし”189“に、
変数セルの変更」を”$B$2:$B$7“にします。
さらに、制約条件の対象を追加するために「追加」ボタンをクリック
008
 
「制約条件の追加」ダイアログにて、
セル参照」を”$B$2:$B$7“に、
その右側にあるリストボックスを”bin“にして、
OK」ボタンをクリック
009
 
これで「解決」ボタンを押すと、Excelが計算してくれます。
010
 
計算がうまくいくと、”ソルバーによって解が見つかりました。”と表示されます。
OK」ボタンを押すと答えが表示されます。
11
 
空白にしておいた作業セル(B列)に、”1″か”0″の値が入ります。
“1”が入ってるものが組み合わせ対象です。
012
この例の場合、”78, 25, 66, 20″が組み合わせ対象となります。
78 + 25 + 66 + 20を計算すると、”189″になるはずです。





[Excel]複数の値からある合計に一致する組み合わせ」への2件のフィードバック

  1. 制約条件の追加の際に、「バイナリ制約条件のセル参照には、変数セルのみを含めてください。」というアラートが表示されて、先へ進めません。何か解決方法をご存知でしょうか。ご教示ください。

  2. 通りすがりの人

    制約条件追加の際に、参照しているセルをA列にしていないでしょうか。
    私も同じアラートが表示され、確認したら間違えてA列を参照していたので、B列にしたら直りました。

コメントを残す

メールアドレスが公開されることはありません。