今回は、Excelのシナリオマネージャーを使って、複数の条件による計算結果を比較する方法を紹介します。
シナリオマネージャーとは
シナリオマネージャーは、指定した入力セルへ複数の値の組み合わせを登録し、切り替えて計算結果を確認する機能です。売上、単価、経費などの前提を「標準」「控えめ」「強気」といったケースに分けて比較できます。
入力値を手作業で何度も変更する方法では、前の条件を忘れたり、一部のセルだけ変更し忘れたりすることがあります。シナリオとして登録すると、前提条件の組み合わせを名前付きで保存して切り替えられるため、試算の再現性を保ちやすくなります。
ただし、元データを大量に切り替える用途ではなく、限られた入力セルの条件比較に向いています。複雑な最適化や連続した多数の値を分析する場合は、別の機能も検討します。
比較用の計算表を準備する
シナリオを登録する前に、入力セルと結果セルを分けます。入力セルには単価、数量、経費率などの前提を置き、結果セルには売上や利益の数式を設定します。
どのセルを変更するか分かるよう、入力欄に色を付けたり、見出しを付けたりします。数式セルへ直接数値を入力すると、シナリオを切り替えても正しい計算ができません。
- 変更する前提値を入力セルへまとめる
- 結果セルには数式を設定する
- 金額、割合、個数などの単位を明記する
- 入力セルと計算セルの書式を分ける
- 通常ケースの値を控える
前提値が複数のシートへ散らばっている場合は、試算用シートへ集約すると管理しやすくなります。
シナリオを登録する手順
データタブからWhat-If分析のメニューを開き、シナリオマネージャーを選択します。追加操作を行い、シナリオ名と変化させるセルを指定します。その後、各セルへ設定する値を入力します。
- 変更対象となる入力セルを確認する
- シナリオマネージャーを開く
- シナリオを追加する
- 内容が分かるシナリオ名を付ける
- 変化させるセルを指定する
- 各セルの値を入力して保存する
複数のシナリオで比較する場合は、変更セルをそろえます。標準ケースでは三つのセル、別のケースでは二つのセルだけを登録すると、比較条件が分かりにくくなります。
シナリオ名を具体的にする
「ケース1」「ケース2」では、後から前提を判断できません。「単価据置_数量増」「経費上昇_売上維持」のように、主な条件を含めます。
名前だけで説明しきれない場合は、コメント欄へ想定時期や前提の根拠を記載します。誰が見ても同じ条件を再現できる状態が重要です。
シナリオを切り替えて確認する
登録したシナリオを一覧から選び、表示操作を行うと、指定した入力セルへそのシナリオの値が入ります。結果セルの売上や利益なども再計算されます。
切り替え後は、入力値だけでなく結果セルと関連グラフを確認します。シナリオ名をセルへ表示する仕組みではないため、画面を共有するときは現在どのケースを表示しているか説明します。
元の入力値へ戻したい場合は、通常ケースもシナリオとして登録しておくと便利です。
シナリオの概要を作成する
複数のシナリオを一覧で比較したい場合は、概要レポートを作成します。結果セルとして売上、費用、利益などを指定すると、各シナリオの変更値と結果が別シートへまとめられます。
レポートを作る前に、入力セルと結果セルへ名前を定義しておくと、セル番地だけの表より内容を理解しやすくなります。「B4」ではなく「販売数量」、「F10」ではなく「営業利益」のように表示できます。
概要は作成時点の内容をまとめたものです。後から元の数式やシナリオを変更しても、既存レポートが自動更新されない場合があります。条件を修正したら概要を作り直し、古いレポートと混同しないようにします。
比較する条件をそろえる
シナリオ比較では、一度に多くの前提を変えると、どの条件が結果へ影響したか分かりにくくなります。最初は主要な二つか三つの変数に絞ります。
たとえば、単価の影響を見たいなら数量と経費を固定し、単価だけを変えます。次に数量を変えたケースを作ると、違いを説明しやすくなります。
- 比較するシナリオで変更セルを統一する
- 固定する条件を明確にする
- 単位と端数処理をそろえる
- 結果セルの数式を途中で変えない
- ケースごとの前提をコメントへ残す
複数条件を同時に変えるシナリオは、個別条件の影響を確認した後に作成します。
入力値の妥当性を確認する
シナリオマネージャーは登録した値を入力セルへ反映しますが、その値が現実的かどうかを判断する機能ではありません。負の数量、100%を超える比率、予算外の金額なども入力できます。
入力規則や条件付き書式を使い、想定範囲を外れた値へ注意を表示する方法があります。ただし、シナリオ切り替え時の動作を事前に確認します。
算出結果だけを比較せず、前提値が業務上成立するかを担当者と確認します。
シナリオを編集・削除する
条件が変わった場合は、シナリオマネージャーから対象を選び、値や変更セルを編集します。似たシナリオを新規追加し続けると一覧が増えるため、不要になったものは削除します。
編集前には、何を変更したか記録します。名前は同じでも中身が変わると、過去の説明資料と条件が一致しなくなる可能性があります。重要な比較では、年月や版を名前へ含める方法もあります。
削除する前に、概要レポートや別ファイルとして必要な結果を保存します。
ほかの分析機能との使い分け
シナリオマネージャーは、複数の入力値の組み合わせへ名前を付けて比較する用途に向いています。一つの結果を目標値へ合わせるならゴールシーク、一定範囲の値を連続的に比較するならデータテーブル、制約条件の下で最適値を探すならソルバーを検討します。
機能を選ぶ基準は、変更するセルの数と、比較したい値の種類です。数件の代表ケースを説明する資料にはシナリオマネージャーが使いやすくなります。
共同作業での注意点
シナリオを切り替えると入力セルの値が変わります。共有ブックでほかの利用者が同時に作業している場合、表示中の数値が変わり混乱する可能性があります。
試算専用のコピーやシートを用意し、確定データと分けます。誰がどのシナリオを確認しているかを明確にし、試算値を本番値として保存しないようにします。
シナリオ概要を共有するときは、作成日時と元ブックの版を記載します。試算と確定値を保存場所から分けることが重要です。
結果を説明するコツ
シナリオごとの最終結果だけでなく、どの入力値を変えたかを並べます。「利益が増えた」という結果だけでは、単価、数量、経費のどれが要因か分かりません。
結果セルは多く指定しすぎず、判断に必要な項目へ絞ります。売上、総費用、利益など、相互関係が分かる指標を選びます。説明文では、試算であり予測を保証するものではないことも明示します。
まとめ
Excelのシナリオマネージャーを使うと、複数の入力条件を名前付きで登録し、計算結果を切り替えて比較できます。入力セルと結果セルを分け、各シナリオで変更するセルと単位をそろえることが基本です。
概要レポートを作成すれば、代表ケースを一覧で確認できます。試算値は確定データと分け、前提条件と作成時点を記録します。結果だけでなく、結果を生んだ条件を並べて比較することで、判断材料として使いやすくなります。