シナリオマネージャーでExcelのWhat-If分析を極める|複数条件の切り替えとシナリオ集計表の実務活用とMOS試験対策

「楽観・基本・悲観の3シナリオで売上予測をまとめたい」「原価率を変えたときの利益をパターン別に比較したい」「採用人数ごとの人件費シミュレーション結果を1枚の表に整理したい」——Excelのシナリオマネージャーは、このような複数条件のシミュレーションを一括管理するWhat-If分析ツールです。

What-If分析には「ゴールシーク」「データテーブル」「シナリオマネージャー」の3つのツールがあります。シナリオマネージャーは、変化させるセルが複数あり、条件のパターンを名前をつけて保存・切り替えたいときに最適です。シナリオ集計表(サマリーレポート)を生成すれば、すべてのシナリオの結果を1枚のシートにまとめて比較できます。MOS Excel 365エキスパート(MO-211)試験でもWhat-If分析は出題対象に含まれる機能です。本記事では、シナリオの作成手順・集計表の生成・実務活用パターン・MOS試験攻略のポイントまで徹底解説します。

「複数条件の試算を効率よく管理したい」「MOS Excel エキスパート試験のWhat-If分析問題を確実に得点したい」という方は、ぜひ最後までお読みください。


シナリオマネージャーでExcelのWhat-If分析を極める|複数条件の切り替えとシナリオ集計表の実務活用とMOS試験対策 - 解説
目次

シナリオマネージャーとは:What-If分析の3ツールと使い分け

シナリオマネージャーを理解するために、まずExcelのWhat-If分析全体像を把握しましょう。

What-If分析の3つのツール

「もしもXが変わったらYはどうなるか」を試算するWhat-If分析には、Excelに3つのツールが用意されています。「データ」タブ→「予測」グループ→「What-If分析」ボタンから3つすべてにアクセスできます。

ツール名主な用途変数の数特徴
ゴールシーク目標値から入力値を逆算する変数1つ結果が決まっており、そこから入力値を求める
データテーブル1変数または2変数の感度分析変数1~2つ変数の変化と結果を表形式で自動生成
シナリオマネージャー複数変数を名前付きで管理・比較変数2~32個条件パターンを名前で保存・切り替え・集計

シナリオマネージャーが適しているケース

シナリオマネージャーは次のようなケースで力を発揮します。

  • 変化させるセルが2つ以上ある(例:単価・販売数量・原価率を同時に変える)
  • 条件のパターンが3つ以上ある(例:楽観・基本・悲観シナリオ)
  • シナリオごとに名前をつけて保存・管理したい
  • 複数のシナリオ結果を1枚の集計表にまとめて比較したい
  • 会議中にシナリオを切り替えながらシート全体の試算結果をリアルタイムで提示したい

一方、「結果の目標値が決まっており、そこから入力値を逆算したい」場合はゴールシーク、「1~2変数の変化と結果の対応表を自動生成したい」場合はデータテーブルが向いています。

シナリオの作成手順:ステップバイステップ

シナリオマネージャーの操作は「データ」タブから始まります。ここでは基本的な作成手順を解説します。

シナリオマネージャーの開き方

  1. 「データ」タブをクリックする
  2. 「予測」グループ内の「What-If分析」ボタンをクリックする
  3. メニューから「シナリオの管理」をクリックする

「シナリオの管理」ダイアログが開きます。最初は何も登録されていない状態です。ここから「追加」「表示」「編集」「削除」「統合」「集計」の各操作を行います。

変化させるセル(変更セル)の設計

シナリオを作成する前に、シート上でどのセルをシナリオごとに変化させるかを決めます。これを変更セルといいます。変更セルはセル参照(例:B2:B4)を入力するか、Ctrlキーを押しながら複数セルを選択して飛び地指定できます。変更セルは最大32セルまで設定できます。

以下は、想定例として作成した販売計画シートです。実際の企業データや実測値ではありません。

セル項目現在値(基本シナリオ)
B2単価(円)5,000
B3販売数量(個)1,000
B4変動費率(%)40
B6売上高(=B2*B3)5,000,000
B8粗利益(=B6*(1-B4/100))3,000,000

この想定例では、B2・B3・B4の3セルを変更セルとして3つのシナリオ(楽観・基本・悲観)を作成します。

シナリオの追加手順

「シナリオの管理」ダイアログで「追加」ボタンをクリックすると、「シナリオの追加」ダイアログが開きます。

  1. シナリオ名を入力する(例:「楽観シナリオ」)
  2. 変更セルにB2:B4を入力する(または事前に選択しておく)
  3. コメント欄に説明を入力する(任意)
  4. 「OK」をクリックすると「シナリオの値」ダイアログが開く
  5. 各変更セルに対応する値を入力する
  6. 「追加」ボタンで次のシナリオを続けて登録するか、「OK」で完了する

同じ手順を繰り返して「基本シナリオ」(現在の値のまま)と「悲観シナリオ」をそれぞれ追加します。3シナリオの設定値の想定例は以下のとおりです。

シナリオ名単価(B2)販売数量(B3)変動費率(B4)
楽観シナリオ(想定例)6,0001,20035
基本シナリオ(想定例)5,0001,00040
悲観シナリオ(想定例)4,00080045

シナリオの表示と切り替え

「シナリオの管理」ダイアログで表示したいシナリオを選び「表示」ボタンをクリックすると、シート上の変更セルが選択したシナリオの値に切り替わります。シートに入力されている数式はそのままに、変更セルの値だけが書き換わるため、シート全体の試算結果がリアルタイムで更新されます。

元の状態に戻すには、「シナリオの管理」ダイアログで「基本シナリオ」(または現状の値を登録したシナリオ)を選んで「表示」をクリックするか、Ctrl+Zで操作を取り消します。

シナリオ集計表(サマリーレポート)の生成

シナリオマネージャーの最も便利な機能のひとつがシナリオ集計表(シナリオの要約)の生成です。すべてのシナリオの変更セルの値と結果セルの値を一覧表形式で自動出力します。

シナリオ集計表の作成手順

  1. 「シナリオの管理」ダイアログを開く
  2. 「集計」ボタンをクリックする
  3. 「シナリオの要約」を選択する(通常はこちらを使用)
  4. 結果セルを指定する(例:売上高B6と粗利益B8を「B6,B8」と入力)
  5. 「OK」をクリックすると新しいシート「シナリオの要約」が自動作成される

シナリオ集計表の見方

生成された集計表には次の情報が含まれます。

  • 現在の値列:集計表生成時点のシートの実際の値
  • 各シナリオ名の列:登録した各シナリオの変更セルの値と結果セルの値
  • 変更セルの行:各シナリオで設定した値が横並びで表示
  • 結果セルの行:指定した結果セルの計算結果が各シナリオ列に表示

想定例で生成される集計表のイメージは次のとおりです。

項目現在の値楽観シナリオ基本シナリオ悲観シナリオ
変更セル $B$2(単価)5,0006,0005,0004,000
変更セル $B$3(販売数量)1,0001,2001,000800
変更セル $B$4(変動費率)40354045
結果セル $B$6(売上高)5,000,0007,200,0005,000,0003,200,000
結果セル $B$8(粗利益)3,000,0004,680,0003,000,0001,760,000

名前付き範囲を使うと集計表が読みやすくなる

上記のように、集計表の行見出しはデフォルトではセル番地($B$2など)で表示されます。事前に変更セルと結果セルに名前付き範囲を設定しておくと(例:B2に「単価」、B6に「売上高」と定義)、集計表の見出しが分かりやすい名前で表示されます。報告資料にそのまま使えるきれいな集計表になります。

ピボットテーブル形式との違い

「集計」ダイアログで「シナリオのピボットテーブル」を選択すると、シナリオの要約の代わりにピボットテーブルが生成されます。結果セルが多く、集計・並べ替えを柔軟に行いたい場合に便利です。ただし、シナリオの要約(標準の集計表)の方が一般的に使われます。

実務活用パターン(想定例)

シナリオマネージャーの実務への適用イメージを、以下に想定例として示します。いずれも架空のシミュレーションであり、実際の企業データや実測値ではありません。

想定例①:年度売上予測(楽観・基本・悲観シナリオ)

年度予算を策定する場面を想定します。変更セルは「単価」「販売数量」「広告費」の3つで、各シナリオに「強気予算」「標準予算」「保守的予算」と名前をつけて管理します。シナリオ集計表を生成すると、3パターンの売上高・営業利益が横並びになった比較表が1クリックで完成します。予算会議の資料としてそのまま印刷できる形で出力できます。

想定例②:コスト構造の変動試算(原価率・物流費・人件費)

製品の原価率・物流費・人件費が変動したときの粗利益と営業利益を試算する場面を想定します。「原材料高騰シナリオ」「通常シナリオ」「コスト削減シナリオ」の3つを登録し、各部門長との検討会議でシナリオを切り替えながらリアルタイムに結果を確認する運用が考えられます。

想定例③:採用計画と人件費シミュレーション

採用人数・平均給与・社会保険料率を変更セルとして「採用5名シナリオ」「採用10名シナリオ」「採用15名シナリオ」の3パターンを用意します。シナリオ集計表を生成することで、各採用パターンの年間人件費総額・一人当たりコストの比較表が自動で作成されます。

シナリオマネージャー活用のポイント

  • シナリオ名は具体的に:「シナリオ1」ではなく「2026年度強気予算」のように内容が分かる名前をつける
  • 変更セルに名前をつける:名前付き範囲を使うと集計表の見出しが読みやすくなる
  • シナリオの編集・削除:「シナリオの管理」ダイアログの「編集」「削除」ボタンで随時変更可能
  • シナリオの統合:複数ブックのシナリオを1つのブックにまとめる「統合」機能も利用できる
  • 保護との組み合わせ:シート保護でシナリオの変更を禁止・許可するオプションを設定できる

ゴールシーク・データテーブルとの使い分け

What-If分析3ツールの使い分け判断軸を整理します。

ゴールシーク:結果が決まっており入力値を逆算したい

「月返済額を80,000円に抑えるには借入上限額はいくらか」のように、目標とする結果が1つ決まっており、そこから入力値を逆算したい場合はゴールシークが最適です。変数は1つのみで、一方向の逆算に特化しています。

データテーブル:1変数または2変数の変化を表形式で一覧したい

「借入金利を3.0%~5.0%まで0.5%刻みで変えたときの返済額一覧を作りたい」のように、1変数または2変数の変化に対する結果の一覧表を自動生成したい場合はデータテーブルが向いています。条件を表として整理でき、感度分析に便利です。

シナリオマネージャー:複数変数を名前付きで管理・比較したい

「楽観・基本・悲観の3シナリオで単価・数量・費用率の3変数を同時に変えて比較したい」のように、変更セルが複数あり、パターンを名前で管理・切り替えたい場合はシナリオマネージャーが最適です。

使いたい場面適したツール
目標値から入力値を逆算するゴールシーク
1変数の変化を表で一覧するデータテーブル(1変数)
2変数の組み合わせを表で一覧するデータテーブル(2変数)
複数変数を名前付きパターンで管理するシナリオマネージャー
シナリオ集計表を作って比較するシナリオマネージャー

MOS Excel 365 エキスパート(MO-211)試験での出題ポイント

シナリオマネージャーの出題範囲

シナリオマネージャーを含むWhat-If分析は、MOS Excel 365エキスパート(MO-211)の出題範囲「データの管理、書式設定」に含まれます。分野別の出題数・配点は公表されていません。

MO-211の4つの出題領域は次のとおりです(公式PDFの逐語表記)。

  • ブックのオプションと設定の管理
  • データの管理、書式設定
  • 高度な機能を使用した数式およびマクロの作成
  • 高度な機能を使用したグラフやテーブルの管理

試験で問われる操作のポイント

MO-211試験では次のような操作が問われます。

  • 「データ」タブ→「What-If分析」→「シナリオの管理」を開く
  • シナリオ名・変更セル・各セルの値を正しく設定して追加する
  • シナリオを選択して「表示」でシートの値を切り替える
  • 「集計」からシナリオ集計表を生成し、結果セルを正しく指定する
  • 既存シナリオの編集・削除

試験はマルチプロジェクト形式で、5個~10個のプロジェクトで構成されます。試験時間は50分、採点は1000点満点です。合格点は公式には非公開ですが550点~850点が目安とされています。合格率は公表されていません。受験料については、最新情報を公式サイトの「受験料・価格」(https://mos.odyssey-com.co.jp/exam/examfee.html)でご確認ください。

学習時間の目安

MOS Excel 365エキスパート(MO-211)はアソシエイト(MO-210)合格後に挑戦する上位資格です。学習時間の目安としては、アソシエイト合格後に追加で60~100時間が目安です。What-If分析(シナリオマネージャー・ゴールシーク・データテーブル)は実際に手を動かして操作手順を覚えることが最短の対策です。1日1時間の学習ペースであれば、追加で2~3か月程度が目安になります。

よくあるつまずきと対処法

シナリオを表示しても値が変わらない

変更セルの指定が誤っている可能性があります。「シナリオの管理」でシナリオを選び「編集」ボタンをクリックして変更セルのアドレスを確認してください。変更セルに数式が入っている場合、シナリオの表示時にその数式が上書きされます。誤って上書きしてしまった場合はCtrl+Zで元に戻せます。

シナリオ集計表のセル参照が分かりにくい

集計表の行見出しがセル番地($B$2など)で表示されて分かりにくい場合は、事前に変更セルと結果セルに名前付き範囲を設定しておきましょう。名前付き範囲を使うと集計表の見出しが「単価」「売上高」のように分かりやすい名前で表示されます。名前付き範囲の設定は「数式」タブ→「名前の定義」から行います。

シナリオを他のブックと共有したい

「シナリオの管理」ダイアログの「統合」ボタンを使うと、別のブックのシナリオを現在のブックに統合できます。複数人がそれぞれのブックでシナリオを作成し、最終的に1つのブックにまとめる運用が可能です。ただし、各ブックのシートと変更セルの構造が同一であることが前提です。

シナリオの追加・編集がグレーアウトしている

シート保護が設定されている場合、シナリオの追加・変更がロックされることがあります。「校閲」タブ→「シート保護の解除」でパスワードを入力するか、シート保護のダイアログで「シナリオの編集」チェックボックスを確認してください。チェックが入っていないと保護中でもシナリオを変更できますが、外れているとシナリオ操作は禁止されます。

シナリオ集計表を更新したい

シナリオ集計表は生成時点のスナップショットです。シナリオを追加・変更した後は、「シナリオの管理」→「集計」を再実行して新しい集計表を生成する必要があります。古い集計表シートは手動で削除するか、上書きして使います。

PR

改善Excel パフォーマンスを底上げする仕事改善・効率化テクニック

シナリオマネージャーやWhat-If分析など、Excelの高度な機能を使った業務改善・効率化の実践テクニックが解説された一冊。日々の集計・分析業務をより短時間でこなすための考え方と操作が体系的にまとめられています。

PR

たった1秒で仕事が片づくExcel自動化の教科書【改訂第3版】

繰り返し発生する集計・試算業務をExcelの機能で自動化する方法を解説しています。シナリオ管理のような複数条件の試算を効率化したい方の実務スキルアップにも役立ちます。


シナリオマネージャーでExcelのWhat-If分析を極める|複数条件の切り替えとシナリオ集計表の実務活用とMOS試験対策 - まとめ

まとめ:シナリオマネージャーで複数条件の試算を一元管理する

シナリオマネージャーは、変更セルを複数持つ複雑なシミュレーションを「名前付きパターン」として管理し、ボタン1クリックで切り替えられるExcelのWhat-If分析ツールです。シナリオ集計表を生成すれば、すべてのシナリオの変更値と結果値を1枚の表に整理できます。

本記事のポイントをまとめます。

  • 「データ」タブ→「What-If分析」→「シナリオの管理」でシナリオを作成・切り替え
  • 変更セルは最大32セルまで、Ctrlキーで飛び地指定も可能
  • 「集計」→「シナリオの要約」で結果セルを指定してシナリオ集計表を自動生成
  • 名前付き範囲を使うと集計表の見出しが読みやすくなる
  • ゴールシーク(逆算)・データテーブル(1~2変数の一覧)との使い分けを意識する
  • MOS Excel 365エキスパート(MO-211)の「データの管理、書式設定」範囲の対象機能

シナリオマネージャーを使いこなすことで、業務の意思決定資料を短時間で作成できるようになります。MOS試験の準備としても、実際に架空のシートを作って3シナリオを登録し、集計表を生成する練習を繰り返すことが最も効果的な対策です。

MOS試験の受験方法・受験日程については、公式サイト(https://mos.odyssey-com.co.jp/)をご覧ください。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

目次