シナリオを利用し、さまざまな値セットを切り替える

Excel​​ には、シナリオ、データ テーブル、ゴール シークという 3 種類の What-If 分析ツールがあります。 シナリオとデータ テーブルは一連の入力値を受け取って予測し、考えられる結果を決定します。 ゴール シークはシナリオやデータ テーブルとは異なり、結果を受け取り、逆行予測し、その結果を生成する入力値を判断します。

各シナリオは、最大 32 個の変数値に対応できます。 32 個より多い値を分析するとき、値が表す変数が 1 つか 2 つだけの場合、データ テーブルを利用できます。 変数は 1 つか 2 つに限られていますが (行の入力セルに 1 つ、列の入力セルに 1 つ)、データ テーブルの場合、異なる変数値をいくらでも追加できます。 シナリオには追加できる値は最大で 32 個ですが、作成できるシナリオの数には限りがありません。

これらの 3 種類のツールに加えて、What-If 分析の実行に役立つソルバー アドインなどのアドインをインストールできます。 ソルバー アドインはゴール シークと似ていますが、より多くの変数を扱うことができます。 また、Excel に組み込まれているフィル ハンドルおよび各種のコマンドを使用して予測を作成することもできます。 さらに高度なモデルの場合は、分析ツールパック アドインを使用できます。

予算を作成するが、収益については見通しが付かないとします。 シナリオを使用することで、収益に考えられるさまざまな値を定義し、シナリオを切り替え、What-If 分析を実行できます。

たとえば、最悪の場合の予算シナリオが総収益 $50,000、売上原価 $13,200、粗利益 $36,800 であるとします。 この値セットをシナリオとして定義するには、次の画像のように最初にワークシートに値を入力します。

変化させるセルには、ユーザーが入力する値が入ります。結果セルには、変化させるセルに基づく数式が入ります (この図の場合、セル B4 に =B2-B3 という数式が入っています)。

次に、[シナリオの登録と管理] ダイアログを使用し、これらの値をシナリオとして保存します。 [データ] タブ、[What-If 分析]、[シナリオの登録と管理]、[追加] の順に進みます。

[シナリオ名] ダイアログで、シナリオに「Worst Case」という名前を付け、B2 と B3 がシナリオにより変化する値であることを指定します。 シナリオを追加する前にワークシートで [変化させるシナリオ] を選択した場合、シナリオの登録と管理では、セルが自動的に挿入されます。挿入されない場合、手動で入力するか、[変化させるセル] ダイアログ ボックスの右にあるセル選択ダイアログを利用できます。

保護 – シナリオを保護することもできます。保護セクションで、必要なオプションを選択します。あるいは、保護が必要ではない場合、選択を解除します。

• ワークシートが保護されているときにシナリオの編集を禁止するには、[変更できないようにする] を選択します。

• ワークシートが保護されているときにシナリオの表示を禁止するには、[表示しない] を選択します。

最も望ましい場合の予算シナリオが総収益 $150,000、売上原価 $26,000、粗利益 $124,000 であるとします。 この値セットをシナリオとして定義するには、別のシナリオを作成し、「Best Case」という名前を付け、セルに別の値を入力します (セル B2 に 150,000、セル B3 に 26,000)。 粗利益 (セル B4) は数式であるため、つまり、収益 (B2) と原価 (B3) の差であるため、Best Case シナリオのセル B4 は変更しません。

保存したシナリオは、What-If 分析で利用できるシナリオの一覧に表示されます。 先の画像の値を所与とすると、Best Case シナリオを表示した場合、ワークシートの値が変わり、次の画像のようになります。

あらゆるシナリオを考慮するとき、すべての情報を 1 つのワークシートまたはブックに表示すると便利です。 ただし、他の出典からシナリオ情報を集めなければならないときもあります。 たとえば、会社の予算を作成するとします。 その場合、営業、給与支払、生産、マーケティング、法務など、さまざまな部署からシナリオを集めなければならないことがあります。予算を作成するとき、情報はそのソースによって異なるためです。

[結合] コマンドを利用すれば、シナリオを 1 つのワークシートに集めることができます。 情報のソースからは、変化させるセルの値を必要な数だけ集めることができます。できるだけ少なくすることもできます。 たとえば、各部署から支出予測を集めるが、収益予測を集めるのは一部の部署に限定します。

結合を選択すると、シナリオの登録と管理はシナリオのコピー ウィザードを読み込みます。このウィザードにより、アクティブなブックのすべてのワークシートが一覧表示されます。その時点で開いているブックが他にあれば、それも表示されます。 選択したソース ワークシートごとのシナリオ数が表示されます。

さまざまなソースから異なるシナリオを集めるとき、各ブックで同じセル構造を利用してください。 たとえば、収益を常にセル B2 に入力し、支出を常にセル B3 に入力します。 さまざまなソースのシナリオで異なる構造が利用されると、結果の結合が難しくなります。

いくつかのシナリオを比較するために、同じページにシナリオの要約が記載してあるレポートを作成できます。 このレポートでは、シナリオを横並びで表示したり、ピボットテーブル レポートで提示したりできます。

先の 2 つのサンプル シナリオに基づくシナリオ情報レポートは次のようになります。

グループ化レベルが自動的に追加されます。セレクターをクリックすると、ビューを展開したり、折りたたんだりできます。

情報レポートの終わりに、[現在の値] 列はシナリオ情報レポートが作成された時点での変化させるセルの値を表しており、各シナリオの変化させるセルが灰色で強調表示されるという注記が表示されます。