ソルバーの制約条件って何？それぞれどんな意味か実例付き解説

どうも、エクセル入力している脇に電卓を置いて計算している父に突っ込みを入れたことがある主婦です。

基本PC付けっぱなし生活をしていたので、電卓と言えばエクセルを出して計算していたのですが、まさかエクセル入力したデータを電卓で計算して入力していたとは・・・

というのはさておき、ソルバーを使う際に有効に使わないと全く答えが出てこない制約条件。

とても大事なのにちょっとわかりづらくて、ソルバーの動かし方自体は出来るけどここが実は意外と鬼門。

そこで、数学苦手マンが苦手なりに考え抜いたソルバーの設定条件に関するざっくりした理解を書き残しておこうと思います。
▼PICK UP!▼
ソルバーとは何か？簡単ザックリ説明ソルバーについて

制約条件とは？

設定条件に付いて疑問をお持ちの方で、ソルバーとは何ぞ？という方はあまりいらっしゃらないかと思いますが、ソルバーとは最適化分析ツール・・・・エクセルに標準装備されたパラメータの推定を行ってくれるアドインです。

そのソルバーですが、「色んなものを何個かずつ買わなきゃいけないんだけど、それぞれ何個買ったら最大になるかなぁ？」って聞いても「商品Aは2.8個、商品Bは1.33333…個、商品Cはマイナス4.45個デス」みたいな、人間なら小数以下にならないと自然に分かるようなところでも余裕で中途半端な数字を回答してきます。

そういう時に活躍するのが制約条件。

「商品だから割れないよ、個数は正の整数でお願いね」とか「商品Aは大人の都合で３個以上は絶対に買わなきゃいけないんだ」とか、そんな設定をしてあげることで適切な答えを導き出すことが出来るわけです。

ぶっちゃけ制約条件を制す者はソルバーを制すと言っても過言ではない。

上手く制約条件を付けてあげないと、むしろソルバーはただ空回りし続けるだけになります。

制約条件の設定方法

その制約条件ですが、どこにあるのかというと

ココにあります。

開いてみると

こんな感じで制約条件を追加することができます。

追加ボタンを押すと入力していた数値が消えますが、それは大きなウィンドウの方の制約条件の対象の方に記入された証拠なので安心してください。

OKを押せば制約条件の追加と一緒に制約条件のウィンドウを閉じることができます。

制約条件の種類と意味

それではどんなふうに条件を設定するのか？

大きく使える制約条件は４種類があります。

それぞれ何をするものなのかご紹介いたします。

比較演算子：＜＝、＝、＝＞

一番身近な条件である、以上・イコール・以下を設定するためのものです。

『＜＝』と『＝＞』はそれぞれ『≦』と『≧』です。

未満・より大きいを表わす『＜』と『＞』は使えませんので注意。

使い方は簡単で

セル参照に指定したいセルを、真ん中のプルダウンのところでいずれかの比較演算子、制約条件のところはセルを参照・数字の手入力どちらでも構いません。

整数：int

次に良く使うのがこれかなと個人的には思います。

個数を指定する場合には整数にしなければならないのでこちらを使います。

指定したいセルをセル参照に入れ、真ん中のプルダウンで『int』を選ぶと右の制約条件のところに自動で『整数』と入力されます。

注意点として『int』を使う際にはオプションを開いて「整数制約条件を無視する」がオフになっていることを確認してください(Excel2010ではこれにチェックボックスが入ってオンになっているのがデフォルトのようです)

バイナリ：bin

変化する値を0か1かどちらかの数字にする場合に使います。

プルダウンのところでbinを選ぶと右の制約条件のところに自動で「バイナリ」と入力されます。

バイナリは例えば「６種類ある商品のうち、どれを一個ずつ買うか？」というようなときに、買うものを「1」に、買わないものを「0」として変数で表す際に使います。

All different：dif

N個の指定したセルに1～N個以下の数字を順番に当てはめる制約条件です。

プルダウンのところでdifを選べば、右の制約条件の所に自動で「All different」が入力されます。

difは順番を付ける場合などに使います。

A君、B君、C君(N＝3)に対してそれぞれ0,1,2(N個の数字３個)と数字を振り分けたり、あるいはアルファベット26文字(N＝26)に対して0～4(N個以下の数字４個)の数字をそれぞれ当てはめるなどの指示を出すものです。

制約条件の変更の際の注意点

なお、私が持っているOffice2007の時点では、制約条件の変更をする際には制約条件のプルダウンのところが『＜＝』に戻ってしまっているので毎回設定し直しています。

その後のExcelでは解決している問題なのか未確認ですが、注意してください。

それぞれの制約条件はどういうときに使う？

一口に制約条件と言っても、それぞれ特徴があるので、各種制約条件を使ったソルバー野問題を作ってみました(数学苦手マンが作っているので穴があるかもしれませんがそのあたりはご容赦を)

★計算がそのままできるExcelのシートのダウンロードする
※良識の範囲内でご利用ください。再配布などはお控えください。

良ければダウンロードして以下の問題を一緒に解いてみてください。

比較演算子＜＝、＝、＝＞　整数int

比較演算子と整数は同じ問題でやっていきます。

小学生の時にやったつるかめ算の拡張バージョンです。

といっても、私の大好きな海洋生物さんたちの脚の本数で種類マシマシでつるかめ算です(すでに鶴と亀ですらない)

と、させていただきます。

・・・・と、見せかけて実はこれ匹数は整数にしなければならないという基本設定ありですね。

これで合計の脚の本数が1220本になるためにはどういった条件を設定すればよいか・・・？

まずはそれぞれ指定する値はこちら

そして制約条件はこうです

コレを解決してもらうと・・・

こんな感じで計算結果を返してくれると思います。

バイナリbin

バイナリは組み合わせを計算するときに良く使います。

どんな組み合わせかというと

またしても海産物デス。

手巻き寿司パーティーをするのに3000円ピッタリ使い切るには、それぞれ一つずつどの組み合わせで買ってくればいいでしょうか？

値段のところと個数は手入力で、合計金額の部分が『=SUMPRODUCT(B2:B11,C2:C11)』となっています。

SUMPRODUCTのイメージは

こんな感じ・・・(ちょっと違う画像で申し訳ない)

なので個数のところが0か1かにすることで、どういう組み合わせで買ってくればいいのかが分かるという仕組みです。

まずはそれぞれの指定の値がこちら

そして制約条件は今回はバイナリのみでOKです

これで動かしてみます。

答えがこちら

ちなみにこの問題は初期値を変えると別の答えが出てくるように調整してあります。

もう一つの組み合わせもぜひ見つけてみてくださいね。

All different ：dif

difは組み分けをする際に良く使われます。

組み合わせと似ていますが、組み合わせが「AとBとCの組み合わせ」というのに対して、組分けとは「AとBとCが①チームで、DとEとFが②チーム」というのが組分けです。

それでは早速問題です。

ジブリヒロインを出来るだけ年齢が均等になるように３チームに分けます。
少し数式が分かりづらいので数式を表示した物がこちら

設定はこうなります

制約条件は変数セルのdifのみ

これで解決をお願いすると・・・

無事に収束しました。

最後に

実は最後のdifの問題ですが、変数(ヒロインの人数)を10人とかに増やしてやってみたのですが、なかなか収束せず試行回数が数万回？十数万回？になってしまいました・・・・

そんなの、手計算で出来るわけがないですよね・・・・だからこそのマシンパワーを頼る。

上手く制約条件を付けてあげることでソルバーはようやく本来の能力を発揮することができます。

とはいえ、何度間違えても元に戻しさえすればやり直しがきくのがシミュレーションなので、何か上手くソルバーが動かないなと思ったら制約条件が本当にそれでいいのかどうかまず確かめるように私はしています。

・・・あとは数式の間違い探しですが、これがまた大変なんですよねぇ。。。