2021年以降を中心にExcelに追加された機能・関数の中で特に実務に役立つものについて解説しています。基本的な使い方や他の機能・関数と組み合わせた応用テクニックも紹介しています。
コンサルタントとして様々なプロジェクトに従事してきた著者が、実務において実際に活用したExcelのテクニックを紹介します。著者自身が「あれ？どうすればいいんだろう」と悩んだ部分を中心にピックアップしています。
以前の記事でゴールシークを使って目標を達成するための解を求める方法を紹介しましたが、ゴールシークでは設定できる条件は目標値のみでした。今回紹介するソルバーでは、複数の変数に対して複数の条件を設定して最適解を求めることができます。
ソルバーの設定方法
ソルバーは標準ではメニューにないので、Excelアドインから追加する必要があります。
「ファイル」⇒「オプション」から「アドイン」を開き、「設定」をクリックします。
「ソルバーアドイン」にチェックを入れて、「OK」を押します。
これで「データ」タブに「ソルバー」の項目が追加されます。
以下では、具体例と共にソルバーの使い方を解説していきます。
目標売上を達成するための価格プラン
あるサービスについて「ライト」「スタンダード」「プレミアム」3つのプランを提供しようとする場合を考えます。各プランの利用者数が与えられているものとして、売上目標を達成するため3つのプランの月額を求めたいと思います。
ただし、制約条件として以下のように価格の幅が与えられているものとします。
- ライト：500円～1,000円
- スタンダード：1,500~2,500円
- プレミアム：3,000~5,000円
この条件も踏まえて、各プランの月額をソルバーを使って求めたいと思います。この際、あらかじめ数式を使って計算する部分には数式を入力しておきます。
- 売上＝利用者数*月額
- 合計=ライト・スタンダード・プレミアムの合計
「データ」タブのソルバーをクリックし、「ソルバーのパラメーター」を開きます。
各パラメーターを以下のように設定していきます。
- 目的セルの設定：売上合計のセルE4
- 目標値：売上目標（ここでは2,000万円とする）
- 変数セルの変更：各プランの価格セルB3~D3
次に制約条件を設定していきます。たとえば、「ライト」プランの価格は500円以上という条件を設定します。ソルバーの「追加」をクリックします。
そうすると、制約条件の追加というウィンドウが表示されます。
ライトプランの価格が500円以上（セルB3>=500）という条件を設定し、「追加」をクリックします。
これで、条件を追加することができます。同じようにライトプランの価格が1,000円以下（セルB3<=1000）と設定する場合は以下のようになります。
その他の条件も追加していき、最後の条件を設定した状態で「OK」をクリックします。
これで制約条件の対象には、表に記載している制約条件が追加されました。
最後に解法を選択しますが、「売上=利用者数×月額」のように数式が、すべて変数に対して掛け算や足し算だけで構成されている場合は、「シンプレックスLP」を選択します。
「解決」をクリックすれば、「ライト」「スタンダード」「プレミアム」それぞれの価格が表示されます。
目標の数値を変更すれば、それに応じた価格が計算されます。
また、プレミアムの価格の条件として「スタンダードの2.5倍以上」という条件を加えます。
そうすると、条件を満たすように価格が計算されます。
コメント