【Excel】データテーブルで1・2変数の感度分析をする方法

事業計画や予算策定において、さまざまな要因の変動が結果にどう影響するかを知りたいと感じることはありませんか。

Excelのデータテーブル機能は、このような「もしも」のシナリオを効率的に分析できる強力なツールです。

この記事では、1つの要因を変える感度分析と、2つの要因を同時に変える感度分析の具体的な手順を解説します。

データテーブルとは?事業計画の感度分析に役立つ理由

データテーブルは、Excelの「What-If分析」ツールのひとつです。特定のセルの値を変化させたときに、計算結果がどのように変わるかを一覧で表示できます。

事業計画や予算策定において、売上予測や費用率などの入力項目が変動した場合の利益やキャッシュフローへの影響を「感度分析」として評価する際に非常に役立ちます。

複数のシナリオを手作業で入力し直す手間を省き、意思決定の精度を高めることにつながります。1つの入力変数を変える分析と、2つの入力変数を同時に変える分析が可能です。

1変数データテーブルで感度分析をする手順

まず、1つの入力値が結果に与える影響を分析する方法です。例えば、販売価格だけを変えた場合の利益の変化などを確認します。

以下の手順で1変数データテーブルを作成できます。

1. シミュレーションモデルの準備
   分析したい計算式を含むモデルをExcelシートに作成します。例えば、「利益 = (売上単価 – 変動費率 × 売上単価 – 固定費) × 販売数量」といった数式を準備します。入力セルと結果セルが明確である必要があります。
2. 入力値リストの作成
   変更したい入力変数(例: 売上単価)の値を、列または行に連続して入力します。例えば、A1セルに「売上単価」と入力し、A2からA10セルに500、550、600といったように異なる単価をリストアップします。
3. 参照セルの配置
   入力値リストの隣、または上に、分析結果を表示したい計算式のセルを参照させます。例えば、B1セルにモデルの利益計算式が入ったセルへの参照「=C10」と入力します。
4. データテーブル範囲の選択
   入力値のリストと、参照セルを含む範囲をすべて選択します。先の例であれば、A1からB10の範囲を選択します。
5. データテーブルの設定ダイアログを開く
   「データ」タブの「What-If分析」グループから、「データテーブル」を選択します。
6. 代入セルの指定
   データテーブルダイアログが表示されます。「列の代入セル」または「行の代入セル」に、モデル内の該当する入力セル(例: 売上単価の入力元であるC1セル)を指定します。リストが列方向(縦)の場合は「列の代入セル」を、行方向(横)の場合は「行の代入セル」を使用します。
7. データテーブルの確定
   「OK」ボタンをクリックすると、選択した範囲に各入力値に対する計算結果が自動的に表示されます。

2変数データテーブルで感度分析をする手順

次に、2つの入力値が同時に結果に与える影響を分析する方法です。例えば、販売価格と変動費率を同時に変えた場合の利益の変化などを確認します。

以下の手順で2変数データテーブルを作成できます。

1. シミュレーションモデルの準備
   1変数データテーブルと同様に、分析したい計算式を含むモデルを準備します。今回は2つの入力セルが必要となります。
2. 2つの入力値リストの作成
   1つ目の入力変数(例: 売上単価)の値を列方向(縦)に、2つ目の入力変数(例: 変動費率)の値をそのリストの右隣の行方向(横)に入力します。
3. 参照セルの配置
   2つの入力値リストが交差するセル(左上隅)に、分析結果を表示したい計算式のセルを参照させます。例えば、B1セルに「=C10」と入力します。
4. データテーブル範囲の選択
   2つの入力値のリストと、参照セルを含む範囲をすべて選択します。例えば、B1からD10の範囲を選択します。
5. データテーブルの設定ダイアログを開く
   「データ」タブの「What-If分析」グループから、「データテーブル」を選択します。
6. 代入セルの指定
   「行の代入セル」には、モデル内の1つ目の入力セル(例: 変動費率の入力元であるC2セル)を指定します。これは横方向に並んだ値の代入先です。「列の代入セル」には、モデル内の2つ目の入力セル(例: 売上単価の入力元であるC1セル)を指定します。これは縦方向に並んだ値の代入先です。
7. データテーブルの確定
   「OK」ボタンをクリックすると、選択した範囲に各入力値の組み合わせに対する計算結果が自動的に表示されます。

データテーブル利用時の注意点と制限

データテーブルは強力な機能ですが、その利用にはいくつかの注意点があります。誤った操作や知識不足によるトラブルを避けるために、これらを理解しておきましょう。

数式が直接変更されてしまう誤操作

データテーブルが作成されたセルの範囲には、特殊な配列数式が自動的に入力されます。この数式を個別に手動で変更しようとすると、エラーが発生したり、データテーブルの機能が損なわれたりします。

1. 対処法: データテーブルの計算結果を修正したい場合は、元になるシミュレーションモデルの計算式や入力セルを変更してください。データテーブル自体は、その変更を自動的に反映します。

計算パフォーマンスへの影響

データテーブルは、入力値の組み合わせの数だけ計算を繰り返します。そのため、行や列の数が非常に多い大規模なデータテーブルを作成すると、Excelの再計算に時間がかかる場合があります。

1. 対処法: 大量のデータテーブルを扱う場合は、一時的にExcelの計算方法を「手動」に設定することを検討してください。「ファイル」タブから「オプション」を選択し、「数式」カテゴリの「計算方法の設定」で「手動」を選びます。必要なときにF9キーを押して再計算を実行できます。

Excel2019・2021との互換性

データテーブル機能はExcelの古いバージョンから提供されている基本機能です。Excel for Microsoft 365で解説した操作手順は、Excel2019やExcel2021でも同様に利用できます。

1. 補足: 細かなユーザーインターフェースやダイアログの見た目に若干の違いがある可能性はありますが、基本的な機能と設定方法は共通しています。

データテーブルと関連機能の比較

感度分析に関連するExcelの機能として、「ゴールシーク」や「シナリオマネージャー」があります。データテーブルとの違いを理解し、適切なツールを選びましょう。

項目	データテーブル	ゴールシーク	シナリオマネージャー
主な目的	複数の入力値に対する結果の変化を一覧表示する	目標の結果を達成するための入力値を逆算する	複数の入力値のセット(シナリオ)を保存・切り替えて比較する
入力変数の数	1つまたは2つ	1つ	複数(無制限ではない)
出力形式	表形式で結果を一覧表示	結果と対応する入力値を単一で表示	個々のシナリオを切り替えてシート全体の表示を変える
分析の方向性	入力→結果(順方向分析)	結果→入力(逆方向分析)	入力セットの比較(順方向分析)

これらの機能はそれぞれ異なる目的で設計されており、ビジネスの意思決定プロセスで相互に補完し合います。データテーブルは、入力の変動が結果にどう影響するかを網羅的に把握したい場合に最適です。

まとめ

この記事では、Excelのデータテーブル機能を使って、1変数および2変数の感度分析を行う具体的な手順を解説しました。

この機能を活用することで、事業計画や予算策定における様々な「もしも」のシミュレーションを効率的かつ視覚的に実施できます。

ぜひ、ご自身のビジネスモデルに応用し、より根拠に基づいた意思決定にデータテーブルを役立ててください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】