2015年10月27日 投稿

[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"になるはずです。

 

 

 



コメントを残す

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