Excelの仮説検証：ゴールシーク、シナリオマネージャー、データテーブル

指定するのは次の3つです。

• 設定するセル: 式が入っているセル（出力）
• 目標値: 目指す結果の値
• 変化させるセル: Excelが調整してよい入力セル

実際にやってみましょう。

ノートを販売する小さなビジネスを運営しているとします。現在の売上は$25,000、目標は$50,000です。価格は$25で固定のため、必要な販売数量を求める必要があります。

手順は次のとおりです。

1. データ > 仮説検証 > ゴールシーク を開く

2. 設定するセルで、B4（売上の式が入ったセル）を選択

3. 目標値に 50000 と入力

4. 変化させるセルで、B2（販売数量）を選択

5. OKをクリック

ゴールシークはB2を1,000から2,000に調整します。これは、1単位あたり$25で売上$50,000を達成するために必要な正確な数量です。

ゴールシークで特定の結果を達成。画像提供：著者

Excelでシナリオマネージャーを使う方法

シナリオマネージャーは、同じスプレッドシート内で複数の入力値セットを保存し、切り替えられる仮説検証ツールです。保存された各バージョンを「シナリオ」と呼びます。

必要なだけ作成し、すぐに切り替えて結果を比較できます。

最良・最悪・想定といった結果を、ファイルを分けずに計画したいときに便利です。

指定するのは次の3つです。

• シナリオ名: 各前提セットのラベル
• 変化させるセル: シナリオごとに異なる入力セル
• 値: 各シナリオの具体的な数値

シナリオを作成する

ノート事業の四半期売上を計画するケースを考えます。

現在の利益は$15,000で、最良、最悪、想定の3つのシナリオをモデリングしたいとします。

シナリオの作成手順は次のとおりです。

1. データ > 仮説検証 > シナリオマネージャー を開く

2. シナリオマネージャーダイアログで追加をクリック

3. シナリオ名に Best Case と入力

4. 変化させるセルに B2:B4 を指定

5. 続いてシナリオの値ダイアログが表示されます。各欄に次の値を入力します。

• B2 = 2000

• B3 = $30

• B4 = $10

6. OKをクリック

残りの2つのシナリオについても、以下の値で同様に作成します。

3つのケースでシナリオを作成。画像提供：著者

シナリオを切り替えるには、一覧から選択して表示をクリックします。Excelが即座に値を入れ替え、利益が更新されます。

「表示」をクリックして各シナリオの値を確認。画像提供：著者

シナリオを編集する

一覧から任意のシナリオを選び、編集をクリックします。名称、対象セル、値を変更できます。「表示」をクリックするまで、スプレッドシートには反映されません。

シナリオレポートを作成する

シナリオマネージャーは、保存した全シナリオを横並びで比較するレポートも作成できます。

シナリオレポートを作成する手順:

1. シナリオマネージャー内で集計をクリック

2. シナリオの集計でシナリオの集計を選択

3. 結果のセルに B7（利益のセル）を指定

4. OKをクリック

Excelは新しいシートに、3つのシナリオを横並びで示す集計表を作成します。

シナリオ集計レポートを作成。画像提供：著者

Excelでデータテーブルを使う方法

データテーブルは、一定範囲の入力値に対して繰り返し計算を自動化します。入力を手動で変えて出力を記録する手間を省き、一度範囲を定義すれば、Excelが一括で結果を埋めてくれます。

指定するのは次の3つです。

• 入力範囲: 検証したい値の一覧
• 行/列の入力セル: それらの値で置き換える、式中のセル
• 式のセル: テーブルの基になる出力

1変数データテーブル

1変数データテーブルは、1つの入力を変えたときの式の結果への影響を検証します。

たとえば、ローン返済額の式があり、金利が5%から10%のときに毎月の返済額がどう変化するかを確認したい場合です。

1変数データテーブルの作成手順:

1. データセットを用意します。注意点が1つあります。私のデータでは、列見出しの横ではなく、最初の金利の真上にあるE1セルに =B5 を入力します。E1が空白だったり、参照式を別のセルに置いたりすると、テーブル自体は動作しても、返済額ではなくすべて0が返ってきます。

1変数データテーブルを作成。画像提供：著者

2. 次に、D1:E9 の全範囲（E1を含む）を選択

3. データ > 仮説検証 > データテーブル を開く

4. 行の入力セルは空欄のままにする

5. 列の入力セルに B4（金利）を指定

6. OKをクリック

Excelは各入力値に対する計算結果でテーブルを埋めます。返済額は均等には増えない点に注目してください。3%から4%の増加は$49ですが、9%から10%では$61です。金利の上昇幅が同じでも、高い水準になるほど影響が大きくなります。

各入力値に対する計算結果。画像提供：著者

2変数データテーブル

2変数データテーブルは、2つの入力を同時に変えたときの式の結果への影響を検証します。

たとえば、価格モデルが製品単価と販売数量の両方に依存する場合、2変数データテーブルなら価格帯と販売数量ごとの利益を1画面で確認できます。

まず、データセットを用意します。

使用するデータセットは次のとおりです。

• D1セルに =B5 を入力します。これは参照式で、ラベルの横ではなく、グリッドの左上隅に配置する必要があります。

• 行1のE1から右方向に、検証したい販売数量を並べます: 500、1000、1500、2000

• 列DのD2から下方向に、検証したい価格帯を並べます: $20、$25、$30、$35

2変数データテーブル用のデータセットを準備。画像提供：著者

2変数データテーブルを作成する手順:

1. 範囲 D1:H5 を選択

2. データ > 仮説検証 > データテーブル を開く

3. 行の入力セルに B3（販売数量）を指定

4. 列の入力セルに B2（単価）を指定

5. OKをクリック

Excelは、価格と販売数量の各組み合わせに対する利益を埋めて表示します。

入力値に対する計算結果。画像提供：著者

2変数データテーブルは、1つの前提だけでは全体像が見えないときに特に有効です。価格だけを見ると良さそうでも、実際の利益は顧客が何個購入するかにも左右されます。2つ目の変数を入れることで、モデルが一気に実用的になります。

ゴールシーク vs. シナリオマネージャー vs. データテーブル

ゴールシーク、シナリオマネージャー、データテーブルはいずれもExcelの仮説検証ツールですが、解決する課題は異なります。

以下に簡単な比較を示します。

仮説検証とソルバーの違い

仮説検証とソルバーはいずれもExcelで異なる結果を探索するのに役立ちますが、解く問題のタイプが異なります。

• 仮説検証は前提を検証します。1つ以上の入力を変え、その変化が結果にどう影響するかを確認します。目的は最適解の探索ではなく、起こりうる結果を理解することです。
• ソルバーは動きが異なります。利益最大化やコスト最小化などの目標を与えると、その目標を最も満たす入力の組み合わせを探索します。前提を試すのではなく、最適な解を求めます。

例えば、ローン申込者はゴールシークで、毎月$500の返済になるローン金額を求められます。一方、製造業の企業は、労務や材料の制約内で最大利益を生む生産計画を決めるためにソルバーを使うかもしれません。

注: ソルバーは標準の仮説検証ツールには含まれていません。別のアドインです。多くのExcelバージョンでは、データタブに表示させる前にExcelのオプションで手動で有効化する必要があります。

仮説検証のベストプラクティス

仮説検証を行う際のベストプラクティスをいくつか紹介します。

1. 前提と式を分離する

入力値は専用のセルに保存し、式ではそのセルを参照します。数値を計算式に直接書き込むのは避けてください。

例えば、次の式は更新が面倒です。

=(50*1000)-10000

一方、次のようにすれば保守が格段に容易です。

=(B2*B3)-B4

前提が変わっても、ワークブック全体の式を編集するのではなく、入力セルだけを更新すれば済みます。

2. シナリオ名は明確に

シナリオ名は、内容が一目で分かるように付けましょう。「シナリオ1」「シナリオ2」「シナリオ3」では、半年後には意味が伝わりません。「Best Case: 15% Growth」のように、実態を示す名前にします。

3. 入力値を検証する

仮説検証の信頼性は、検証する入力値の正確さに依存します。実行前に次の点を確認してください。

• パーセンテージの入力が正しい
• 日付の形式が一貫している
• 単位系が統一されている
• 入力値が現実的な範囲に収まっている

たとえ技術的には正しくても、売上が500%増を前提にした予測は、意思決定には役立たないでしょう。

4. 前提を文書化する

今日自分にとって分かりやすいモデルでも、3か月後には自分にも他人にも分かりやすいとは限りません。

各前提の横に「Q3実績に基づく」程度の短いメモを残しておくだけで、後々の手探りを大幅に減らせます。文書化しておけば、数値の根拠を他者が理解でき、将来の更新も容易になります。

仮説検証でよくある問題

仮説検証での不具合の多くは、ツール自体ではなくワークシートの設定に起因します。ゴールシーク、シナリオマネージャー、データテーブルで想定外の結果が出たら、以下の一般的な原因を確認してください。たいていはこれで解決します。

1. 式が更新されない

入力を変えても何も起きない。多くの場合、計算モードがどこかで手動に切り替わっているのが原因です。

対処法: 数式 > 計算方法の設定で自動になっているか確認します。すでに自動なら、F9で再計算を実行します。

2. セル参照の誤り

仮説検証ツールは正確なセル参照に依存します。誤ったセルを選択すると、計算自体は完了しても結果は間違います。

たとえば、ゴールシークが誤った入力セルを変更してしまう、データテーブルが誤った行または列の入力セルを参照してしまう、シナリオマネージャーが誤ったセルの値を保存してしまう、といったケースです。

対処法: 実行前に入力セルを見直し、各ツールのダイアログで選択内容を再確認し、セルの意味が分かる説明的なラベルを使いましょう。

3. ゴールシークの制約の誤解

ゴールシークは複数の変数を同時に変更する問題も解けると期待されがちですが、できません。ゴールシークが変更できるのは、目標値に到達するための1つの入力セルだけです。

例えば、$15,000の利益を出すために必要な販売数量は求められますが、販売数量・価格・コストを同時に調整してその目標に到達することはできません。

対処法: 変更すべき変数が1つだけのときはゴールシークを、複数あるときはシナリオマネージャーやソルバーを使いましょう。

4. 複雑すぎるシナリオモデル

シナリオマネージャーは技術的には最大32個の変更セルを扱えますが、可能だからといって常に有効とは限りません。

4〜5変数を超えると、シナリオの比較は難しくなり、他者への説明はさらに困難になります。可変要素がそれほど多いなら、1つのシナリオに詰め込むのではなく、モデル自体の構造を見直すべきです。

まとめ

あらゆるスプレッドシートには、どこかに前提が含まれています。成長率、価格、金利などです。

仮説検証は前提を取り除くものではありません。前提が外れたらどうなるかを見せてくれます。良い意思決定の多くは、実はそこにかかっています。

初めて取り組むなら、変数5つと3つの入れ子シナリオといった大掛かりなモデルから始めないでください。まずは、入力1つ・出力1つ・ゴールシーク1回といった小さなモデルで、入力と結果がどう連動するかを掴みましょう。基礎が分かってから、複雑なことに進めば十分です。