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





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

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

  2. 通りすがりの人

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

  3. あみだがみね 投稿作成者

    >>山さん
    コメントありがとうございます。コメント返し遅くなってしまいすいません…
    お役に立てずにすいません。”通りすがりの人”さんのコメントでうまくいきましたでしょうか?

    >>通りすがりの人さん
    親切なコメントありがとうございます。コメント返し遅くなってしまいすいません。
    自分一人では結構抜けてるところもあるので、こういうコメントは大変ありがたいです!

  4. ほくと

    合計と完全に一致ではなく合計以下(なるべく差異を少なく)の数値になるような組み合わせを探すにはどうしたらよいでしょうか。

  5. えくせるぅ

    目標値を、指定件数に、余りなく振り分けたい時もこの方法は適用可能でしょうか?

  6. 匿名

    >>ほくとさん
    ありきたりですが
    目的セルを
    =(合計値が入力されるセル番地)-(目標値)
    と入力した別セルに設定し
    ソルバーに(合計値が入力されるセル)<=(目標値)
    というような条件を書き足した後
    指定値ではなく最小値を選択して解決
    というのはどうでしょう

  7. 匿名

    訂正:最小値設定ですから
    =(合計値が入力されるセル番地)-(目標値)
    ではなく
    =(目標値)-(合計値が入力されるセル番地)ですね

    >>えくせるぅさん
    記事における
    B列が1である行のA列の値を
    別の表の項目に振り分けたいということでしょうか?
    それともB列を0,1ではなく任意の数にして
    目標値をA列の値×B列の個数で余りなく分割したいということでしょうか?
    前者ならば別の表を作り、目標値…目的セルを最終目標に合わせた数式をいれたセルに指定
    後者ならば条件式のB列=バイナリをB列 int 整数に変更
    適宜条件式を追加すれば大丈夫ですね

  8. たかし

    合計と完全に一致ではなく合計以下(なるべく差異を少なく)の数値になるような組み合わせを探、目的セル(C3)を
    =150(目標値)-B2:B7(合計値が入力されるセル番地)と入力した別セルに設定し
    ソルバーにB2:B7(合計値が入力されるセル)<=150(目標値)
    というような条件を書き足した後
    最小値を選択して解決してみましたができません。
    御教授願います

  9. 匿名

    最適解となる組み合わせが複数ある時にそれを全て出す事は可能でしょうか

  10. はす

    例えば「範囲内から『3つ』選んで指定値に近づける」といったように、選ぶセル数を固定したい場合はどう設定すれば良いでしょうか?

コメントを残す

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