ソルバーに組み合わせで最適解を出す方法・一体何をやってるの？

ソルバーってパラメータをExcelの出すアドインなのですが、実は希望の数値になるような数値の組わせを出すことにも使えるらしいと

あんまりこの手のパラメータ推定はやってこなかったのですが、今回色々と読み漁りながらトライしてみました。

数字苦手人間が解説するめちゃくちゃザックリしたソルバーの組み合わせの使い方です。

▼PICK UP!▼
ソルバーとは何か？簡単ザックリ説明ソルバーについて

ソルバーとは

ソルバーに組み合わせを解かせようとしている人が、ソルバーを知らないとは思いますが一応です。

ソルバーはExcelのアドインソフトで、Excelを買うと標準で付いてくる拡張ソフトみたいなものです。

ソルバーの使い方

通常状態ではソルバーはオフになっているので、オンにしてあげないと使うことができません。

ファイル＞オプション＞アドイン＞管理『Excelアドイン』設定(ウィンドウの一番下)をクリックすると

こんなウィンドウが出てくるので、ソルバーにチェックボックスを入れてOKを押してください。

するとデータのタブの一番右にソルバーが出現します。

なお、Office2010で開発タブが出ている場合には開発タブにすでに『Excelアドイン』と書かれた歯車が二個のアイコンがあると思うので、そちらをクリックするとチェックボックスのウィンドウを一発で出すことができます。(画像の私のやつはOffice2007なので色や雰囲気が少し違うかもしれません)

エクセルに組み合わせを解かせる方法

複数の任意の数値の中から、いくつかを選んで希望の合計値にする・・・・と言うとなんだか難しいですが、問題形式でやって行きたいと思います。
★計算がそのままできるExcelのシートのダウンロードする
※良識の範囲内でご利用ください。再配布などはお控えください。

手巻き寿司パーティーでもするんですかね。

コレをエクセルに解決してもらいたいと思います。

これが作業する際のセルの配置図になります。

使っている関数『SUMPRODUCT』はイメージで言うとこんな感じのことをしてくれています。

掛け算して足し算してもいいんですけど、関数一個でやってくれるならこっちの方がお得感がありますね(ちなみにSUMPRODUCTは３列以上設定できるのでA列の数字×B列の数字×C列の数字・・・とすることもできます)
※SUMPRODUCTを使わなくても、CとDの間に一列入れて掛け算をして合計を算出しても同じ計算になるはずです。

ここまでセルができたらソルバーに託します

この状態で解決を押してみます。

これで正解が見つかりました！OKを押して数値を確定させます。

買って帰るのは個数のところに「1」が入力されている物なので、ヤリイカ・サーモン・マグロ・ブリだそうです。

※もし同じことをして違う答えが出てきた場合には「複数解あるとき」を見てください

実行可能解が見つかりませんでした？！

こんな表示が出てしまうことがあります。

これは「組み合わせ試してみたけど、どう頑張っても正解がないです」という意味です。

そのため、こちらが出てしまった場合には、そもそもデータ(今回は価格)か目標値(今回は合計金額3000円)のどちらかを修正する必要があります。

ソルバーで組み合わせが複数解あるとき

実は今回の問題は初期値が違うと別の答えが出てくるように調整しています。

こんな風にマダイといくらだけ初期値(最初に手入力しておく数字)を「1」にしてから同じようにソルバーで解決をさせます。

すると・・・

さっきとは違う答えが出てきました。

なんで？どうして？というのには、実はソルバーの計算がどうやって行われているのかが少し関係してきます。

(正確にはちょっと違うのですがイメージとして)ソルバーは簡単に言うと、一個一個数字を当てはめて「こっちの数字はどうかな？」「今度はこの組み合わせだと上手くいくかな？」とやっています。

人間がコレをやるとものすごーーーーーく大変ですが、PCのマシンパワーさえあればサクッと終わるわけです(スペックが低いPCでやたら計算が複雑なソルバーを動かすと数時間かかることがあります※実体験)

ピッタリと上手くいく組わせを見つけた時、計算を終了して「終わったよ、答えでたよ」と教えてくれるわけです。

ところが正解が二つある場合には先に正解を見つけた時に計算を終了してしまいます。

イメージ的にはこんな感じですね。

柴犬：初期値　スイカ：正解

目隠しをしてぶんぶん棒を振り回しながら当たりを探しているので、初期値が正解に近いほど早く見つかりますが、複数正解がある場合には近い方の正解を先に引き当ててしまいます。

そのため、初期値はいくつか入れて正解が複数ないか調べてみるしかありません。

一回で解決したとは思わず、いくつか入力して計算し直して試してみてください。

最後に

組み合わせって数が多ければ多いほど大変ですし、複数の組み合わせが存在する可能性を調べるのもかなり大変です。

そこをマシンパワーに物言わせて解決してくれるのは、かなりありがたいんじゃないかなぁという存在です。

ただ複数解については本当に気を付けないと、意外と答えが見つけられないことあるので、そこだけは手間を惜しまない方が良いかと思います。