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

Excelで将来の収益予測やコスト計算を行う際、いくつかの要因が結果にどう影響するかを知りたい場面があります。例えば、売上単価が10円上がったら利益はどう変わるか、あるいは、売上単価と販売数量の両方が変動したらどうなるか、といった分析です。このような、特定の入力値の変化が計算結果に与える影響を調べる手法を「感度分析」と呼びます。Excelには、この感度分析を効率的に行うための「データテーブル」機能があります。この記事では、データテーブルを使って1変数と2変数の感度分析を行う具体的な手順を、初心者の方にも分かりやすく解説します。ぜひ、あなたのビジネス分析に役立ててください。

データテーブル機能の概要と活用場面

Excelのデータテーブル機能は、数式で使用する1つまたは2つの変数を変更した場合に、その数式の結果がどのように変化するかを自動的に計算し、表形式で表示する機能です。この機能を使うことで、様々なシナリオを想定したシミュレーションを効率的に行うことができます。例えば、以下のような場面で活用できます。

・商品の価格設定:価格をいくらに設定すれば、目標利益を達成できるかを調べる。

・融資の返済計画:金利や返済期間が変わった場合に、月々の返済額や総返済額がどうなるかを把握する。

・投資の収益予測:投資額や想定利回りによって、将来の資産価値がどう変動するかをシミュレーションする。

データテーブルは、試行錯誤を繰り返しながら最適な条件を見つけ出すための強力なツールとなります。特に、複数の要因が絡み合う複雑な計算結果を、直感的に理解しやすい表で確認できる点が大きなメリットです。

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

1変数データテーブルでは、1つの入力セルの値だけを変化させたときの、数式結果の推移を分析します。ここでは、商品の販売単価を変更した場合の利益の変化を分析する例で手順を説明します。

1. 分析したい計算式を作成する
   まず、感度分析の対象となる計算式をExcelシート上に作成します。例として、利益を計算する数式をB4セルに作成します。利益は「(販売単価 – 原価) × 販売数量」で計算されるとします。原価はB2セルに100円、販売数量はB3セルに1000個と入力しておきます。数式は「=(B1-B2)*B3」となります。B1セルには、これから変化させる「販売単価」を入力します。
2. 変化させたい入力値のリストを作成する
   次に、B1セルに入力する「販売単価」の候補となる値を、別の列(例えばA5セル以降)にリストアップします。ここでは、150円から250円まで10円刻みで入力します。
3. データテーブルの数式セルを設定する
   データテーブルの集計結果を表示させたいセル(ここではC4セル)に、感度分析の対象となる計算式(B4セルの利益計算式)への参照を入力します。つまり、C4セルに「=B4」と入力します。このC4セルが、後でデータテーブルによって各単価での利益の値で埋め尽くされます。
4. データテーブル範囲を選択する
   入力した単価リスト(A5:A15)と、数式への参照セル(C4)を含む範囲、さらに利益計算式(B4)も含めた範囲(A4:C15)をマウスでドラッグして選択します。
5. データテーブル機能を実行する
   選択した状態で、Excelのリボンメニューから「データ」タブをクリックします。「予測」グループにある「What-If分析」をクリックし、「データテーブル」を選択します。
6. 行の入力セルと列の入力セルを指定する
   「データテーブル」ダイアログボックスが表示されます。「行の入力セル」は今回は使用しないため、空欄のままにします。「列の入力セル」には、単価リスト(A5:A15)が参照している元のセル、つまり販売単価が入力されているB1セルを指定します。
7. OKをクリックして結果を確認する
   「OK」をクリックすると、選択した範囲のC5セルからC15セルに、A5セルからA15セルまでの各販売単価に対応する利益の値が自動的に計算されて表示されます。これで、販売単価が10円上がるごとに利益がいくら増加するかを一覧で確認できます。

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

2変数データテーブルでは、2つの入力セルの値を同時に変化させたときの、数式結果の推移を分析します。ここでは、販売単価と販売数量の両方を変化させた場合の利益を分析する例で手順を説明します。

1. 分析したい計算式と入力値リストを作成する
   1変数データテーブルと同様に、利益計算式(B4セル)と、変化させる2つの入力値(販売単価B1セル、販売数量B3セル)を準備します。今回は、販売単価のリストをA列(A5セル以降)に、販売数量のリストを5行目(C4セル以降)に作成します。販売単価は150円から250円まで、販売数量は500個から1500個まで、それぞれ100個刻みでリストアップします。
2. データテーブルの数式セルを設定する
   2変数データテーブルの場合、数式への参照セルは、行のリストと列のリストが交差する角のセル(ここではC4セル)に配置します。このC4セルに、感度分析の対象となる利益計算式(B4セルの参照)を入力します。つまり、C4セルに「=B4」と入力します。
3. データテーブル範囲を選択する
   販売単価リスト(A5:A15)、販売数量リスト(C4:E4)、そして数式への参照セル(C4)を含む範囲(A4:E15)をマウスでドラッグして選択します。
4. データテーブル機能を実行する
   選択した状態で、「データ」タブ→「What-If分析」→「データテーブル」を選択します。
5. 行の入力セルと列の入力セルを指定する
   「データテーブル」ダイアログボックスで、「行の入力セル」には、行のリスト(C4:E4)が参照している元のセル、つまり販売数量が入力されているB3セルを指定します。「列の入力セル」には、列のリスト(A5:A15)が参照している元のセル、つまり販売単価が入力されているB1セルを指定します。
6. OKをクリックして結果を確認する
   「OK」をクリックすると、選択した範囲のC5セルからE15セルに、各販売単価と販売数量の組み合わせに対応する利益の値が自動的に計算されて表示されます。これにより、例えば「販売単価200円、販売数量1000個」のときの利益、「販売単価220円、販売数量1200個」のときの利益などを一覧で確認できます。

データテーブル機能の注意点と活用例

データテーブル機能は非常に便利ですが、いくつか注意しておきたい点があります。これらの注意点を理解することで、より効果的に活用できます。

数式セルへの参照は必須

データテーブル機能を使うためには、必ず感度分析の対象となる計算式が入力されたセルを、データテーブルの範囲内に含める必要があります。1変数データテーブルでは、その計算式セルへの参照を「数式セル」として配置します。2変数データテーブルでは、行と列の入力リストが交差する角のセルに、その計算式セルへの参照を配置します。この参照がないと、データテーブルは正しく機能しません。

計算結果の更新について

データテーブルで計算された結果は、元の入力値が変更された場合に自動的に再計算されます。しかし、Excelの計算方法の設定によっては、手動で再計算する必要がある場合があります。「ファイル」タブ→「オプション」→「数式」と進み、「計算方法の設定」で「自動」が選択されているか確認してください。もし「手動」になっている場合は、「自動」に変更するか、F9キーを押して再計算を実行してください。

データテーブルの範囲外への数式コピーは不可

データテーブル機能で作成された表のセルは、Excelの通常のセルとは少し異なります。データテーブルの範囲外のセルに、データテーブルで計算された数式をコピーして貼り付けることはできません。もし、データテーブルで得られた結果を他の場所で利用したい場合は、結果の値をコピー&ペースト(値として貼り付け)するか、数式を再入力する必要があります。

大量のデータテーブルは計算負荷に注意

データテーブルは、指定した範囲のすべての組み合わせに対して計算を実行します。そのため、入力値のリストが非常に多い場合や、計算式が複雑な場合は、Excelの処理に時間がかかることがあります。特に2変数データテーブルで、行と列のリストがそれぞれ数百行・数百列に及ぶような場合、PCのスペックによっては動作が重くなる可能性があります。必要最小限の範囲で分析を行うように心がけましょう。

他のExcel機能との連携

データテーブルで分析した結果は、グラフ化することでさらに視覚的に理解しやすくなります。例えば、1変数データテーブルの結果であれば、販売単価を横軸、利益を縦軸にした折れ線グラフを作成すると、価格変動による利益の変化傾向が一目でわかります。また、2変数データテーブルの結果は、3Dグラフや条件付き書式などを活用して、複数の要因が絡み合った関係性を表現することも可能です。

XLOOKUP関数とVLOOKUP関数の機能比較

データテーブル機能は、特定の入力値に対する結果を一覧表示するのに適していますが、特定の条件に合致するデータを検索・抽出する際には、XLOOKUP関数やVLOOKUP関数が役立ちます。これらは、データテーブルとは異なる目的で使用される関数ですが、データ分析の文脈でよく比較されます。

項目	XLOOKUP関数	VLOOKUP関数
検索方向	左から右、右から左、上から下、下から上(指定可能)	左端の列のみ(固定)
検索モード	完全一致、近似一致(前方一致、後方一致も可能)	完全一致、近似一致
エラー処理	IFERROR関数との組み合わせ不要(IFERROR引数で指定可能)	IFERROR関数との組み合わせが必要
返り値の列	指定した列を返せる	検索値がある列より右側の列のみ返せる
バージョン	Microsoft 365、Excel 2021以降	Excel 2007以降

XLOOKUP関数は、VLOOKUP関数に比べて柔軟性が高く、より直感的に使用できます。特に、検索対象の列が返したい値の列より右側にある場合や、検索方向を自由に指定したい場合に強力です。VLOOKUP関数は広く普及していますが、検索対象が左端の列に限定されるなどの制約があります。どちらの関数も、データテーブル機能とは異なり、特定の条件に合致するデータを検索・抽出する際に利用されます。

まとめ

本記事では、Excelのデータテーブル機能を用いて、1変数および2変数の感度分析を行う具体的な手順を解説しました。データテーブルを使えば、価格や数量などの入力値が変化した場合に、利益などの計算結果がどう変動するかを一覧で把握できます。この機能を活用することで、様々なシナリオを想定したシミュレーションが容易になり、より精度の高い意思決定が可能になります。次に、ご自身の業務で扱うデータを用いて、販売単価やコスト、生産量などの条件を変えて、利益やコストがどう変化するかをデータテーブルで分析してみてください。さらに、分析結果をグラフ化することで、より直感的な理解を深めることができます。

📊

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

🏆

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