前回の記事では、Excelの「ソルバー」機能を使って、2つの商品を販売する際に利益を最大化する方法を紹介しました。今回はその応用編として、より複雑な条件下での最適化を行う方法を解説します。
「複数の商品を扱っている」「在庫や予算だけでなく、作業時間や人員数なども制約に含まれる」——そんな現実的な状況でも、Excelソルバーを使えば最適な販売戦略を導き出すことができます。
応用シナリオの概要
ある会社では、以下の3つの商品（A・B・C）を販売しています。
| 商品 | 単価 | 利益 | 在庫 | 作業時間（1個あたり） |
|---|---|---|---|---|
| A | 1,000円 | 300円 | 100個 | 1時間 |
| B | 2,000円 | 500円 | 80個 | 2時間 |
| C | 1,500円 | 400円 | 60個 | 1.5時間 |
制約条件は以下の通りです：
- 総売上は15万円以内（予算制約）
- 総作業時間は200時間以内（人員制約）
- 各商品の販売数は在庫数以内
この条件下で、利益が最大になる販売数の組み合わせを求めます。
Excelでの表の作成
まずは以下のような表をExcelに作成します：
| 商品 | 単価 | 利益 | 販売数 | 売上 | 利益合計 | 作業時間 |
|---|---|---|---|---|---|---|
| A | 1000 | 300 | [空欄] | =単価×販売数 | =利益×販売数 | =販売数×1 |
| B | 2000 | 500 | [空欄] | =単価×販売数 | =利益×販売数 | =販売数×2 |
| C | 1500 | 400 | [空欄] | =単価×販売数 | =利益×販売数 | =販売数×1.5 |
| 合計 | =SUM(売上列) | =SUM(利益合計列) | =SUM(作業時間列) |
「販売数」は空欄にしておき、ソルバーが自動で埋めるようにします。
ソルバーの設定
Excelの「データ」タブ → 「ソルバー」をクリックし、以下のように設定します。
目的セルの設定
- 「利益合計」のセルを選択
- 「最大値」を選択
変数セルの変更
- 「販売数」の3セル（A・B・C）を選択
制約条件の追加
- 各販売数 ≥ 0
- Aの販売数 ≤ 100
- Bの販売数 ≤ 80
- Cの販売数 ≤ 60
- 売上合計 ≤ 150,000
- 作業時間合計 ≤ 200
解決方法の選択
- 「GRG 非線形」を選択（初期値）
設定が完了したら「解決」をクリック！
結果の確認と考察
ソルバーが計算を終えると、販売数のセルに最適な数値が自動で入力されます。例えば以下のような結果になるかもしれません：
このように、制約条件をすべて満たしながら、利益を最大化する販売戦略が導き出されました。
よくある注意点
- 制約条件が厳しすぎると「解なし」となることがあります。その場合は条件を緩和するか、矛盾がないか確認しましょう。
- ソルバーは整数制約を標準では扱いません。販売数を整数にしたい場合は「制約条件」で「整数」にチェックを入れましょう。
- 解法によって結果が変わることがあります。「単純法」「進化的」なども試してみると良いでしょう。
まとめ
Excelソルバーは、複雑な条件下でも最適な解を導き出してくれる強力なツールです。今回は「複数商品」「複数制約」の中で利益最大化を目指す応用例を紹介しました。
業務の中で「限られたリソースをどう配分するか」「どの商品をどれだけ売るべきか」といった課題に直面したとき、ソルバーを使えば客観的かつ効率的な意思決定が可能になります。
ぜひ、あなたの業務にもExcelソルバーを取り入れてみてください！
