「利益をもっと増やしたいけど、最大化できる販売パターンはどれだろう？」
「支出を最小限にしたいけど、どういう仕入パターンにしたらいいだろうか？」
「目標を達成するためには、あとどのくらい必要だろうか？」
仕事をしていると、このような状況を考えたことがある人も多いと思います。
こうした「制約の中で最適解を求める」場面こそ、ソルバーが本領を発揮します。
Excelには「ゴールシーク」という便利な機能があります。
これを使うことで目標値から逆算することができますが、「ゴールシーク」は複数の条件を守りながら最適な組みあわせを求めることはできません。
そこで、その問題を解決してくれる便利なツールが、Excelの「ソルバー」アドインとなります。
本記事では、実務ではあるあるなケースを挙げたうえで、Excelソルバーの使い方を分かりやすく解説します。
- Excelのソルバーで何ができるのか？
- ソルバーが表示されない場合のアドイン追加方法
- ソルバーの具体的な使い方（最大値/最小値/指定値をケース別に解説）
Excelソルバーとは？実務で何ができるのか
Excelソルバーとは、条件を守りながら、最大・最小・指定値の答えを探すツールです。
つまり、複数の条件に合致した答えを求めることができる便利な機能です。
設定するのは、以下のたった3つです。
1.目的セル（最大化 or 最小化したい値）
2.変数セル（調整する値）
3.制約条件（守るルール）
ゴールシークが「1つの値を目標に合わせるツール」なのに対し、ソルバーは「複数条件の中で最適解を探すツール」です。
例えば、以下のような業務に活用することができます。
・人員配置の最適化
・広告費の配分最適化
・仕入数量の最適化
・コスト最小化
いずれも、仕事で使いそうな内容ですよね。
ではさっそく、どのようにソルバーを使っていくか解説していきます。
Excelソルバーが表示されない？アドインの追加方法
Excelソルバーをいざ使おうとなったとき、
「Excel ソルバーがどこにもない」
と迷われている方もいると思います。
実はExcelソルバーは、初期状態では表示されていないことがあります。
もし見つからないという場合は、アドインから有効化する必要があります。
以下手順で、ソルバーを表示させることができますので、試してみてください。
１．「ファイル」から「オプション」を選択
２．「アドイン」から「設定」を選択
３．「ソルバーアドイン」にチェックを入れ、「OK」を選択
これで設定すると、「データ」タブの右側に「ソルバー」が表示されます。
※ソルバーは無料で利用できます。
Excelソルバーの使い方例①：利益最大化
エクセルのソルバーアドインを使うことで、複数条件に合う最大値を求めることができます。
複数商品・条件の中で、利益が最大化になる販売個数を求めるケースを例に説明していきます。
■ケース設定
以下の3商品を販売しているとする。
商品A → 売上単価：1,500円、利益率：25%、在庫上限：20個
商品B → 売上単価：1,000円、利益率：30%、在庫上限：50個
商品C → 売上単価：3,000円、利益率：15%、在庫上限：30個
但し、合計販売数は75個までとする。
この場合、利益を最大化する各商品の販売個数は？
■シート設計
まずは、先ほどのケースの条件をシートに落とし込んでいく必要があります。
・A列：商品
・B列：販売数量 ←変数セル
・C列：売上単価
・D列：利益率
・E列：利益額（B列×C列×D列）
・F列：在庫条件
販売数量（B列）と利益額（E列）にはそれぞれ合計をSUM関数で作成します。
■ソルバーの設定手順
では、先ほど入力した条件をもとに、ソルバーを設定する手順を説明していきます。
１．「データ」タブから「ソルバー」を選択
すると、以下の「ソルバーのパラメーター」画面が表示されます。
ここから順番に条件を入力していきます。
２．「目的セルの設定」に、利益額の合計セル（E5）を指定する
※今回は最大利益額を求めるため、目標値は「最大値」を指定
３．「変数セルの変更」に、販売数量範囲セル（B2:B4）を指定する
※最大利益額になるために、販売個数が何個となればよいかを調べるため
４．「制約条件の対象」で、「追加」を選択 → それぞれの条件を追加する
４-1．商品Aの販売数量が、在庫条件を上回らないようにする（B2≦F2）
４-2．商品Bの販売数量が、在庫条件を上回らないようにする（B3≦F3）
４-3．商品Cの販売数量が、在庫条件を上回らないようにする（B4≦F4）
４-4．商品A～Cの販売数量が、販売数上限（75個）を上回らないようにする（SUM(B2:B4)≦75）
※制約条件にはセル参照は可能ですが、直接数式を入力することができません。
４-5．販売数量が整数になるようにする（B2:B4=int）
5．条件を入力したら、「解決」を選択する
これで、利益が最大となる販売個数を計算することができました。
Excelソルバーの使い方例②：コスト最小化
エクセルのソルバーアドインを使うことで、複数条件に合う最小値を求めることもできます。
複数商品・条件の中で、コストが最小化になる仕入個数を求めるケースを例に説明していきます。
■ケース設定
以下の3商品を仕入れようとしている。
A商品→ 仕入単価：1,000円、最低仕入：30個、50個以上で15%・100個以上で25%オフ
B商品→ 仕入単価：1,500円、最低仕入：20個、50個以上購入で45%オフ
C商品→ 仕入単価： 900円、最低仕入：40個
但し、合計仕入個数は200個とする。
この場合、コストを最小化する商品の仕入個数は？
■シート設計
同じように、先ほどのケースの条件をシートに落とし込んでいきます。
・A列：商品
・B列：仕入数量 ←変数セル
・C列：仕入単価 ←仕入個数によって単価を分岐
・D列：仕入額（B列×C列）
・E列：最低個数
販売数量（B列）と利益額（D列）にはそれぞれ合計をSUM関数で作成します。
今回は条件をやや複雑にし、
・A商品は仕入個数によって、3パターンの仕入単価が存在
・B商品は仕入個数によって、2パターンの仕入単価が存在
というように、仕入個数によって仕入単価が異なる設定としてみました。
■ソルバーの設定手順
先ほどと同じようにソルバーを立ち上げ、条件を入力していきます。
1．「目的セルの設定」に、仕入額の合計セル（D5）を指定する
※今回は最小仕入額を求めるため、目標値は「最小値」を指定
2．「変数セルの変更」に、販売数量範囲セル（B2:B4）を指定する
※最小仕入額になるために、仕入個数が何個となればよいかを調べるため
3．「制約条件の対象」で、「追加」を選択 → それぞれの条件を追加する
3-1．商品Aの仕入数量が、最低個数を下回らないようにする（B2≧E2）
3-2．商品Bの仕入数量が、最低個数を下回らないようにする（B3≧E3）
3-3．商品Cの仕入数量が、最低個数を下回らないようにする（B4≧E4）
3-4．仕入数量が整数になるようにする（B2:B4=int）
3-5．商品A～Cの仕入数量が、仕入数200個以上にする（SUM(B2:B4)≧200）
4．条件を入力したら、「解決方法の選択」を「エボリューショナリー」に設定し、「解決」を選択する
これで、最小仕入額となる仕入個数を計算することができました。
ちなみに「解決方法」として「エボリューショナリー」を選択したのは、
・仕入数によって仕入単価がIF文で複雑に分岐しているから
となります。
試しに、以下は「GRG非線形」で計算したケースです。
「エボリューショナリー」と比較して、仕入額合計が多くなってしまっています。
これは「GRG非線形」が滑らかな曲線を前提に最適解を探すのに対し、IF文は「ある点で、結果が急に変わる不連続な式」になるため、GRG非線形では最適解が不安定になることがあります。
「エボリューショナリー」は計算にちょっと時間はかかりますが、複雑な条件時には一番安定する方法となります。
計算が安定しない場合は、エボリューショナリーを試してみてください。
Excelソルバーの使い方例③：指定値にする（ちょうど目標達成）
エクセルのソルバーアドインを使うことで、複数条件に合う指定値を求めることもできます。
複数商品・条件の中で、売上がちょうど指定値になる販売個数を求めるケースを例に説明していきます。
■ケース設定
以下の3商品を販売しようとしている。
A商品→ 売上単価：1,000円、販売上限：30個
B商品→ 売上単価：1,500円、販売上限：20個
C商品→ 売上単価： 900円、販売上限：40個
但し、売上額を80,000円ぴったりとしたい。
この場合、売上額を指定した金額に合わせる商品別の目標販売個数は？
■シート設計
同じように、先ほどのケースの条件をシートに落とし込んでいきます。
・A列：商品
・B列：目標販売個数 ←変数セル
・C列：売上単価
・D列：売上額（B列×C列）
・E列：販売上限
販売数量（B列）と利益額（D列）にはそれぞれ合計をSUM関数で作成します。
■ソルバーの設定手順
先ほどと同じようにソルバーを立ち上げ、条件を入力していきます。
1．「目的セルの設定」に、売上額の合計セル（D5）を指定する
※今回は売上額80,000円を求めるため、目標値は「指定値」で、「80000」を指定
2．「変数セルの変更」に、販売数量範囲セル（B2:B4）を指定する
※売上80,000円になるために、販売個数が何個となればよいかを調べるため
3．「制約条件の対象」で、「追加」を選択 → それぞれの条件を追加する
3-1．商品Aの目標販売個数が、販売上限を上回らないようにする（B2≦E2）
3-2．商品Bの目標販売個数が、販売上限を上回らないようにする（B3≦E3）
3-3．商品Cの目標販売個数が、販売上限を上回らないようにする（B4≦E4）
3-4．目標販売個数が整数になるようにする（B2:B4=int）
4．条件を入力したら、「解決」を選択する
これで、目標売上額が80,000円ぴったりとなる販売個数を計算することができました。
ゴールシークとの違い｜使い分けが重要
Excelには、今回紹介したソルバーとは別に、「ゴールシーク」という逆算できる機能があります。
どちらも目標値から答えを導くツールですが、役割は大きく異なります。
ここでは、その違いと活用場面を説明します。
機能の違いを比較
ソルバーとゴールシークがどう違うか、比較表にまとめてみました。
| 比較項目 | ゴールシーク | ソルバー |
|---|---|---|
| 変数の数 | 1つのみ | 複数可 |
| 制約条件 | 設定不可 | 設定可能 |
| 最大化・最小化 | 不可 | 可能 |
| 整数制約 | 不可 | 可能 |
| 操作の手軽さ | ◎ | △ |
| 実務での最適化 | △ | ◎ |
ソルバーが向いているケース
今回ご紹介したように、ソルバーは「制約の中で最適な組み合わせを求める」ことに適しています。
・売上を最大化したいのだが、各商品にそれぞれ在庫条件があり、販売数上限が決まっている
・コストを最小化したいのだが、最小のコストでより多くの商品を仕入れたい
・目標金額を達成するため、各商品を何個ずつ販売すればいいか
実際の業務でも、様々な制約のもと利益の最大化・コスト最小化を目指すことが多いです。
複雑な制約のもとにある最適解を導き出す手段として、ソルバーは非常に便利な機能です。
実はゴールシークのほうが優れている場面もある
一方で、次のようなケースではゴールシークのほうが効率的です。
①変数が1つだけの場合
変数が1つだけであれば、ゴールシークを使うほうが良いです。
例えば、
・利益率30%になる売価はいくらか？
・月5万円返済にする金利は？
など、1つの値を逆算するならゴールシークのほうが簡単で素早く解けます。
②制約条件がない場合
ソルバーの良いところは、様々な制約条件がある場合に力を発揮することです。
制約条件がなければ、わざわざソルバーを使う必要がなく、ゴールシークで十分対応できます。
③仮説を素早く試したい場合
例えば、
・利益率が20％になる場合の売価はいくら？
・じゃあ10％にするといくらになるだろう？
など、軽い検証を行う場合、ゴールシークのほうが操作がシンプルです。
実務での正しい使い分け
以上を踏まえると、次のような使い分けをすることをオススメします。
・逆算だけなら → ゴールシーク
・制約付き最適化なら → ソルバー
この違いを理解することで、Excelの“逆算機能”を使いこなせるようになります。
👉ちなみにゴールシークの使い方や実務例は、こちらの記事でまとめています。
こちらもぜひチェックしてみてください。
ソルバーのよくあるエラーと対処法
ソルバーは便利な機能ですが、実行したときにエラーが発生することがあります。
ここでは、よくあるエラーと対処法を見ていきましょう。
①実行可能解が見つかりませんでした
ソルバーを実行した後、以下のように「実行可能解が見つかりませんでした」と表示するときがあります。
この場合、
・制約条件が厳しすぎる
・条件が矛盾している
可能性があるため、一度条件を見直してみてください。
（≧と≦を間違えていたり、目標値がどう計算しても達成できない値などがよくあります）
②解が少数になる
今回ソルバーの使い方でご紹介しましたが、
・制約条件に「int（整数）」を設定しないと、小数点が発生してしまう
可能性があります。
販売する商品数や金額など、整数を求める場合は忘れずに「int（整数）」を制約条件に追加してください。
③うまく計算できない
「解決方法の選択」が、条件にマッチしたものとなっていない場合、適切な回答が得られないケースがあります。
以下がソルバーで選ぶことができる「解決方法の選択」となります。
状況に応じて選びなおすことで、正しく計算ができるようなる可能性があります。
まずは「シンプレックスLP」から試してみて、うまくいかなければ、「GRG非線形」→「エボリューショナリー」と試してみてください。
| 解決方法の選択 | 使用する状況 |
|---|---|
| シンプレックスLP | きれいな線形の場合 |
| GRG非線形 | 割り算・二乗など滑らかな非線形の場合 |
| エボリューショナリー | IFなどの複雑な条件分岐がある場合 |
まとめ：逆算はゴールシーク、最適化はソルバー
Excelソルバーは、
・利益を最大化したい
・コストを最小化したい
・制約条件を守りながら、最適な組み合わせを求めたい
そんな場面で力を発揮するツールです。
一方で、
・変数が1つだけ
・単純に目標値を逆算したい
といった場合、ゴールシークのほうが手軽で効率的ですので、状況に応じて使い分けしてみてください。
また、今回紹介したソルバー活用例を参考に、業務の最適化に活用してみてください。
最後まで読んでいただき、ありがとうございました。
コメント