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 [↗])にも改めて感謝です！
合計と完全に一致ではなく合計以下（なるべく差異を少なく）の数値になるような組み合わせを探すにはどうしたらよいでしょうか。
目標値を、指定件数に、余りなく振り分けたい時もこの方法は適用可能でしょうか？
>>ほくとさん
ありきたりですが
目的セルを
=(合計値が入力されるセル番地)-(目標値)
と入力した別セルに設定し
ソルバーに(合計値が入力されるセル)<=(目標値)
というような条件を書き足した後
指定値ではなく最小値を選択して解決
というのはどうでしょう
訂正：最小値設定ですから
=(合計値が入力されるセル番地)-(目標値)
ではなく
=(目標値)-(合計値が入力されるセル番地)ですね
>>えくせるぅさん
記事における
B列が1である行のA列の値を
別の表の項目に振り分けたいということでしょうか？
それともB列を0,1ではなく任意の数にして
目標値をA列の値×B列の個数で余りなく分割したいということでしょうか？
前者ならば別の表を作り、目標値…目的セルを最終目標に合わせた数式をいれたセルに指定
後者ならば条件式のB列=バイナリをB列 int 整数に変更
適宜条件式を追加すれば大丈夫ですね
合計と完全に一致ではなく合計以下（なるべく差異を少なく）の数値になるような組み合わせを探、目的セル（C3）を
=150(目標値)-B2:B7(合計値が入力されるセル番地)と入力した別セルに設定し
ソルバーにB2:B7(合計値が入力されるセル)<=150(目標値)
というような条件を書き足した後
最小値を選択して解決してみましたができません。
御教授願います
最適解となる組み合わせが複数ある時にそれを全て出す事は可能でしょうか
例えば「範囲内から『３つ』選んで指定値に近づける」といったように、選ぶセル数を固定したい場合はどう設定すれば良いでしょうか？
2,400
2,900
11,300
6,200
16,400
19,700
19,400
40,400
26,800
18,500
35,000
89,000
229,400
22,600
この中から120,000になる組み合わせを探すのに大変助かりました。
ありがとうございます。
>あらさん
コメントありがとうございます。お役に立てたようで何よりです。
>コメントを書き込んで下さった皆様へ
コメントありがとうございます！コメント返信遅くなり申し訳ありません。
コメントに答えてくださった方、ありがとうございます。自分も勉強になりました。
コメントに答えられなかった、力不足でゴメンなさい。
目からうろこです！
こんな素晴らしい機能があるんですね。
作業効率が１００倍アップです。
ありがとうございました。
>>ろんさん
自分もかなり悩んで時間を費やしたので、そう言ってもらえるとうれしいです。
ありがとうございます！
複数の選択肢を回答に出すことは可能ですか？
例）指定値50
値：10,10,20,2030,30
10+10+30＝50
10+10+20＝50
20+30＝50
分かりづらくてすみません。。。
大変助かりました。素晴らしいです。ありがとうございました。
指定値を入力して計算して「見つかった」旨のメッセージが出たのですが、指定値と微妙にずれた値になりました。
ソルバーには16203223を指定したのですが、結果の合計は16203215でした。何か原因思い当たるものはありますでしょうか？
「変数セルが多すぎます。」というダイアログメッセージが出てしまいます。
解決方法はありますか？
＞UP主様
素晴らしい方法をご教授頂き、ありがとうございます！
私もハリーさんと同じく「変数セルが多すぎます。」というメッセージが出てしまいました。
解決策をご存じの方、ご教授頂けますと幸いです。
はじめまして
こちらのサイトを教えていただき参考にさせていただきました
ありがとうございました
どうしてもできない設定があり質問させてください
B列は数値の制限なしなのでバイナリ設定はしませんでした(整数設定はしました)
B列の数値がA列1最優先、次がA列2優先、A列3優先で選択という風に設定はできないのでしょうか
もしできるものでしたら助かります
お忙しいとは思いますがご教授いただけると助かります
よろしくお願いいたします
ここで質問する人さ、自分で調べる事しないの？
エクセル ソルバー 使い方 で沢山ヒットするよ
自分で調べもするし、ダメもとで聞いてるんでしょ
たいへん参考になりました。ありがとうございました。
ただし、こちらはExcelを使っておらずGoogleスプレッドシートを使用しているのですが、アドオンでOpenSolverというのをインストールするとExcelの場合とほとんど同じソルバーが使えるようなるので、このページに書いてあるのと同じ方法で問題を解くことができました。
>Excelを持ってない人さん
お役に立てて何よりです。
Googleスプレッドシート+OpenSolverでの情報提供ありがとうございます🙇
最近はGoogleスプレッドシートを使うことが増えてきたので感謝です。
経理の消込作業に大変役に立ちそうです。
涙が出るほど嬉しいです。
このように情報を提供してくださり、見知らぬ人を助けてくださることに
心から感謝いたします。ありがとうございました。
先ほどこちらの関数のおかげで大変助かりました‼️
ありがとうございます！
めちゃめちゃ助かりました。人間には不可能な領域で、コツコツ頑張って答えを見つけ出してくれるExcelちゃんがいとおしく思いました。本当にありがとうございます。
ありがとうございます！大変たすかりました！
実に素晴らしい！！
何十分も電卓であれこれ足し算してました。
時間短縮できました。ありがとうございました。
説明も解りやすいです。
Cの欄は複数入れても
探してくれるのでしょうか？
一段目189
二段目300
とか・・・
ありがとうございました！
とても助かりました！
説明もわかりやすく感謝です！
感動しました。
大変助かりました。
ありがとうございました。
数値を設定して
最後の解決を押すとexcelが落ちてしまいます。