5分で分かる！Excel（エクセル）「ソルバー機能」の使い方を解説（具体例付き）

「ソルバー機能」について解説します。

ソルバー機能とは、別名を最適化分析ツールとも言います。

数式の計算結果を目標値として設定して、その結果を得るために複数の制約条件を指定し任意のセルを変化させて最適値を求める機能です。

この記事で分かること↓

※「ソルバー機能」を使えるようにする方法

※具体例を交えた使い方

※エラーが出た時の対処方法

こんな人におすすめ↓

※営業職で目標数値が決まっていて、達成するためにどの商品をどのくらい売ればいいか計算したい。

※仕入れ担当で、予算を最小限に商品を入荷するときのそれぞれの個数が知りたい。

今回は、解説にあたって例題を設定します。

Q：目標金額に対して、プランAとプランBの適切な料金を計算したい。

条件↓

・プランA （月額1000円以下）

・プランB （月額1500円以上　かつ　プランAの価格の3倍以下）

・利用者数はあらかじめ決まっている。

　→プランA：5000人

　→プランB：2000人

・売上目標は5000万円

この条件を基に解説します。

□「ソルバー機能」を有効にする

「ソルバー機能」は、初期状態では有効化されていません。

Excel（エクセル）のオプションから追加する必要があります。

①「ファイル」タブから「オプション」タブを選択

↓

②「アドイン」タブから「ソルバーアドイン」をチェック

↓

③完成

□最適な価格を求める

2つのプラン（プランA、プランB）と目標数値（5000万円）を基に、2つのプランの料金を算出します。

数式にて分かるところは整理

今回の例で言えば、売り上げを求める数式は設定できるので先に数式にて設定しておきましょう。

こうすることで、計算したい料金が決まることで自動的に合計数値を算出してくれます。

・プラン別の年間売上（D2、D3）には、「利用者数×料金×12」

・目標には（D4）には、「２つのプランの年間売上合計数式を入れる」

ソルバーのパラメーターで条件を整理

目標と条件を踏まえて、「ソルバー」機能で各プランの最適な月額を計算します。

①「データ」タブ　→ 「ソルバ」タブを選択

↓

②目標や変更対象セルなど条件を設定する

※今回設定したい条件を数式にて表現

・プランAは1000円以下　→  「＄C＄2＜＝1000」　

・プランBは1500円以上　→  「＄C＄3＞＝1500」

・プランBはプランAの3倍以下　→  「＄C＄3 ＜＝　＄C＄3＊3」

上記の式を追加します。

※全ての条件を追加し終わったら、「キャンセル」で終了します。

↓

③完成

※解が見つかったかどうかを確認します↓

残念ながら例題の条件に合う「最適な料金」は見つかりませんでした。
 

□上達のワンポイント：解が見つからなかった場合こそチャンス

もし解が見つからなかった場合は、「実行可能解が見つかりませんでした」というエラーメッセージが出ます。

目標数値、販売単価、条件が多いなど設定に無理があったことを示します。

解が見つかるまで試してみましょう。

適切な数値を設定するチャンスになります。

↓

目標数値を「5000万」から「7000万」に変更してみる。

※最適解が見つかりました 

目標数値を7000万円にした場合

→プランA：料金538円

→プランB：料金1572円

□まとめ

※ソルバーが使えるように設定する。

　「ファイル」→「オプション」→「アドイン」→「ソルバーアドイン」の順で設定

※条件を設定（数式にて表現）

　・プランAは1000円以下　→  「＄C＄2＜＝1000」　

　・プランBは1500円以上　→  「＄C＄3＞＝1500」

　・プランBはプランAの3倍以下　→  「＄C＄3 ＜＝　＄C＄3＊3」

※エラーが出た時こそチャンス

　適切な数値を見直すチャンス。何度かトライして適切な数値を見つける。

　目標数値を5000万円から7000万円に変更

　他にも

　利用者数を変更したり、プランの条件を変更したり、目的に応じて変更できます。