わずか5分でスキルアップ!　Excel熟達Tips(46) ソルバー機能を使って複雑な条件の連立方程式の解を導く

今回は「ソルバー」という機能の使い方を紹介していこう。前回の連載で紹介した「ゴールシーク」と少し似ている機能だが、複数の解答がある問題を逆算したり、さまざまな条件を指定したりできるのがソルバーならではの特長。Excelをユニークに活用する方法として、覚えておいても損はないだろう。

ソルバーの概要とアドイン

まずは、簡単な例を使ってソルバーの概要を紹介していこう。たとえば、以下のような問題(つるかめ算)をExcelで解く場合を考えてみる。

(問題) 「つる」と「かめ」が合計10匹います。足の数の合計が26本であった場合、「つる」と「かめ」はそれぞれ何匹いるでしょうか？

これをExcelの表で示すと以下の図のようになる。ただし、「背景が黄色のセル」の数値は、連立方程式を解かない限り求めることができないはずだ。

「つるかめ算」の解答

このような場合にソルバーを利用すると、連立方程式を解かなくても、Excelだけで最適な解を求めることができる。なお、ソルバーはExcelの追加機能であるため、初めて利用するときは以下の手順でアドインを行っておく必要がある。

(1)「ファイル」タブを選択し、「オプション」をクリックする。

オプションの呼び出し

(2)Excelの設定画面が表示されるので、画面左側で「アドイン」を選択し、画面右側で「ソルバーアドイン」を選ぶ。続いて、「設定」ボタンをクリックする。

ソルバーのアドイン

(3)「ソルバーアドイン」のチェックボックスをONにし、「OK」ボタンをクリックする。

「ソルバーアドイン」の選択

(4)「データ」タブを開くと、「ソルバー」のコマンドが追加されていることを確認できる。

「データ」タブに追加された「ソルバー」のコマンド

以上で、ソルバーを利用するための準備は完了。さっそく、先ほどの「つるかめ算」を解いてみよう。

ソルバーを使って「つるかめ算」の解を求める

まずは「つるかめ算」を解くための表を作成する。現時点では、まだ「つる」と「かめ」が何匹ずついるかは不明なので、とりあえずは適当な数値を入力しておく。今回は初期値として、それぞれに2を指定した。あとは問題に従って数式や関数を入力していくだけ。「足の数の小計」は「=C3*D3」のように(匹)×(足の数)の数式で計算できる。また、「つる」と「かめ」の匹数の合計は「=SUM(C3:C4)」で算出可能だ。

「つるかめ算」を解くために用意した表

数式や関数を正しく入力できたら、「データ」タブにある「ソルバー」をクリックして設定画面を表示する。ここでは、最初に「目的セル」の設定を行う。今回の例では「足の数の合計」が26本にならなければいけないので、「目的セル」にE5を指定し、「目標値」を26に指定する。

「目的セル」の設定

続いて「変化させるセル」を指定する。今回の例では「つる」と「かめ」の匹数を変化させるので、C3～C4のセル範囲、すなわち「C3:C4」と入力する。その後、「追加」ボタンをクリックして条件を追加していく。

「変化させるセル」の設定

今回の例題では、「つる」と「かめ」の合計は10匹、というのが条件。よって、「C5＝10」という条件を指定し、「OK」ボタンをクリックする。

追加条件の設定

以上で、ソルバーの設定は完了。目的セルや条件の設定をよく確認してから「解決」ボタンをクリックする。

ソルバーの設定確認と実行

すると、Excelが自動的に最適な解を求めてくれる。今回の例では、「つる」が7匹、「かめ」が3匹という結果が表示された。結果をそのまま残す場合は、「ソルバーの解の保持」を選択し、「OK」ボタンをクリックする。

ソルバーにより求められた解

以上が、ソルバーの基本的な使い方。もう一度まとめておくと、以下のような手順だ。

数式や関数を使って問題を解くための表を作成する。
「求めたい値」には適当な数値を入力しておく。
ソルバーを起動し、「目的セル」と「変化させるセル」を指定する。
問題に従って条件を追加する。
「解決」ボタンをクリックすると、最適な解が自動的に求められる。

このように、複数の解がある問題を、条件を指定して解くことができるのがソルバーの特長。とはいえ「つるかめ算」では少し面白みに欠けるので、もう少し具体的な例を紹介してみよう。

ソルバーで利益を最大にする組み合わせを求める

続いては、「利益が最大になる組み合わせ」をソルバーを使って解いてみよう。今度は、以下のような問題を考えてみる。

(問題) ある菓子店で、クッキー、マドレーヌ、パウンドケーキの「詰め合わせ商品」を新たに発売します。合計12個の商品を1箱に詰め、2,000円で販売する場合、利益を最大にするには、どうすればよいでしょうか？　なお、各商品の1個当たりの「定価」と「原価」は以下の通りです。

クッキー(定価160円、原価55円)
マドレーヌ(定価190円、原価72円)
パウンドケーキ(定価220円、原価93円)

「詰め合わせ商品」にする以上、多少は"お買い得感"を出さなければいけません。よって、各商品の「定価の合計」が2,200円以上になるように箱詰めします。また、特定の商品が偏りすぎるとよくないので、各商品を最低でも2個以上詰めことが条件です。

上記の問題を表にすると、以下の図のようになる。現時点では、クッキー、マドレーヌ、パウンドケーキの「数量」は不明なので、とりあえず初期値として2を入力しておく(背景が黄色のセル)。あとは問題に従って計算式を入力するだけ。念のため、それぞれの計算式を示しておこう。

(定価の小計)＝(数量)×(定価)
(原価の小計)＝(数量)×(原価/個)
(数量の合計)は「=SUM(C3:C5)」
(定価の合計)は「=SUM(E3:E5)」
(原価の合計)は「=SUM(G3:G5)」
(利益)＝2000－(原価の合計)　　※「=G8-G6」

最大利益を求めるための表

この問題をソルバーで解く場合の操作手順は以下の通り。

ソルバーを起動し、「利益」(G9セル)が最大になるように「目的セル」を指定。また「変化させるセル」には、各商品の「数量」、すなわち「C3:C5」を指定する。続いて、「追加」ボタンをクリックして条件を追加していく。

「目的セル」と「変化させるセル」の設定

今回の例題では「数量の合計」を12個にしなければならない。よって、「C6＝12」という条件を指定し、「追加」ボタンをクリックする。続いて「定価の合計」は2,200円以上にしなければならないので、「E6>=2200」という条件を追加。さらに「各商品を最低でも2個以上」という条件も追加しておく必要がある。この条件は、「C3:C5 >= 2」である。

「数量の合計」＝12の条件設定
「定価の合計」が2200円以上の条件設定
それぞれの「数量」が2以上の条件設定

以上で、すべての条件を指定できた。ただし、このままでは正しい結果を得られない。というのも、それぞれの商品の「数量」が小数点以下を含む数値になってしまう可能性があるからだ。

先ほど例にした「つるかめ算」ではこの条件指定を省略していたが、厳密には「整数に限る」という条件も追加しておく必要がある。整数の条件は、中央にあるセレクトボックスに「int」を選択することで設定できる。左側のテキストボックスには、値を整数に限定するセル範囲を入力すればよい。

それぞれの「数量」が整数の条件設定

以上で条件の追加は完了。「OK」ボタンをクリックしてソルバーの設定画面に戻る。あとは、目的セルや条件の設定をよく確認し、「解決」ボタンをクリックするだけだ。条件の設定などを確認しやすいように、「この問題を解くために用意した表」も合わせて掲載しておこう。

最大利益を求めるための表
ソルバーの設定確認と実行

上記の条件でソルバーを実行すると、以下のような結果が表示される。クッキー4個、マドレーヌ6個、パウンドケーキ2個を詰め合わせると、利益が最大になり、1箱あたり1,162円の利益を得られることがわかる。

ソルバーにより求められた解

このようにソルバーを利用すると、複雑な条件がある問題でも最適解を導くことが可能だ。条件の指定などに多少の手間を要するが、自分の頭で試行錯誤するよりスムーズに解を見つけられる場合が多い。一度、試してみるとよいだろう。

ソルバーを利用するときの注意点

前述したように、ソルバーを利用すると「条件を満たす解」を手軽に求められる。ただし、表示された結果が、必ずしも最適な解になるとは限らないことに注意しなければならない。たとえば、先ほどの例において、「数量」の初期値(C3～C5セル)をすべて0にした状態でソルバーを実行すると、以下のような結果が表示される。