データテーブルを使用したWhat-If分析

Excelのデータテーブルを使用すると、1つまたは2つの入力を簡単に変更して、What-if分析を実行できます。データテーブルは、一部のセルの値を変更して、問題に対するさまざまな回答を考え出すことができるセルの範囲です。

データテーブルには2つのタイプがあります-

1変数データテーブル
2変数データテーブル

分析の問題に3つ以上の変数がある場合は、Excelのシナリオマネージャーツールを使用する必要があります。詳細については、このチュートリアルの「シナリオマネージャーを使用したWhat-If分析」の章を参照してください。

1変数データテーブル

1つ以上の数式の1つの変数の異なる値がそれらの数式の結果をどのように変更するかを確認したい場合は、1変数データテーブルを使用できます。つまり、1変数のデータテーブルを使用すると、1つの入力を変更すると任意の数の出力がどのように変更されるかを判断できます。あなたは例の助けを借りてこれを理解するでしょう。

Example

30年間の保有期間で5,000,000のローンがあります。さまざまな利率の毎月の支払い（EMI）を知りたい。また、2年目に支払われる利息と元本の金額を知りたいと思うかもしれません。

1変数データテーブルを使用した分析

1変数データテーブルを使用した分析は、3つのステップで実行する必要があります-

Step 1 −必要な背景を設定します。

Step 2 −データテーブルを作成します。

Step 3 −分析を実行します。

これらの手順を詳しく理解しましょう-

ステップ1：必要な背景を設定する

金利が12％であると仮定します。
必要な値をすべてリストします。
数式にセル参照ではなく名前が付けられるように、値を含むセルに名前を付けます。
Excel関数（それぞれPMT、CUMIPMT、およびCUMPRINC）を使用して、EMI、累積利息、および累積元本の計算を設定します。

ワークシートは次のようになります-

列Cのセルには、列Dの対応するセルに示されている名前が付けられていることがわかります。

ステップ2：データテーブルを作成する

次のように、値のリスト、つまり、入力セルの列Eの下に代入する利率を入力します。

ご覧のとおり、金利値の上に空の行があります。この行は、使用する数式用です。

最初の関数を入力します（PMT）値の列の上の1行と右側のセル内。他の関数を入力します（CUMIPMT and CUMPRINC）最初の関数の右側のセル。

これで、金利値の上の2つの行は次のようになります。

データテーブルは次のようになります-

ステップ3：What-If分析データテーブルツールを使用して分析を行う

代入する数式と値を含むセルの範囲を選択します。つまり、範囲– E2：H13を選択します。
リボンの[データ]タブをクリックします。
[データツール]グループの[仮定分析]をクリックします。
ドロップダウンリストで[データテーブル]を選択します。

Data Table ダイアログボックスが表示されます。

[列]入力セルボックスのアイコンをクリックします。
セルをクリックします Interest_Rate、これはC2です。

列入力セルが$ C $ 2として取得されていることがわかります。[OK]をクリックします。

データテーブルには、以下に示すように、各入力値の計算結果が入力されます。

54,000のEMIを支払うことができる場合、12.6％の金利が適切であることがわかります。

2変数データテーブル

数式内の2つの変数の値が異なると、その数式の結果がどのように変化するかを確認する場合は、2変数データテーブルを使用できます。つまり、2変数データテーブルを使用すると、2つの入力を変更すると1つの出力がどのように変更されるかを判断できます。あなたは例の助けを借りてこれを理解するでしょう。

Example

50,000,000のローンがあります。金利とローン期間のさまざまな組み合わせが月々の支払い（EMI）にどのように影響するかを知りたいと思います。

2変数データテーブルを使用した分析

2変数データテーブルを使用した分析は、3つのステップで実行する必要があります-

Step 1 −必要な背景を設定します。

Step 2 −データテーブルを作成します。

Step 3 −分析を実行します。

ステップ1：必要な背景を設定する

金利が12％であると仮定します。
必要な値をすべてリストします。
数式にセル参照ではなく名前が付けられるように、値を含むセルに名前を付けます。
Excel関数を使用してEMIの計算を設定します– PMT。

ワークシートは次のようになります-

列Cのセルには、列Dの対応するセルに示されている名前が付けられていることがわかります。

ステップ2：データテーブルを作成する

タイプ =EMI セルF2内。

入力値の最初のリスト、つまりF列の下の利率を、数式の下のセル、つまりF3から始めて入力します。
入力値の2番目のリスト、つまり行2全体の支払い数を、数式の右側のセル、つまりG2から始めて入力します。

データテーブルは次のようになります-

What-If分析ツールのデータテーブルを使用して分析を行います

数式と置換する2つの値のセットを含むセルの範囲を選択します。つまり、範囲– F2：L13を選択します。
リボンの[データ]タブをクリックします。
[データツール]グループの[仮定分析]をクリックします。
ドロップダウンリストから[データテーブル]を選択します。

[データテーブル]ダイアログボックスが表示されます。

[行]入力セルボックスのアイコンをクリックします。
セルをクリックします NPER、これはC3です。
もう一度、[行]入力セルボックスのアイコンをクリックします。
次に、[列]入力セルボックスのアイコンをクリックします。
C2であるセルInterest_Rateをクリックします。
もう一度、[列]入力セルボックスのアイコンをクリックします。

行入力セルが$ C $ 3と見なされ、列入力セルが$ C $ 2と見なされることがわかります。[OK]をクリックします。

データテーブルには、2つの入力値の組み合わせごとの計算結果が入力されます-

54,000のEMIを支払うことができる場合、12.2％の金利と288のEMIが適しています。これは、ローンの期間が24年になることを意味します。

データテーブルの計算

データテーブルは、変更されていなくても、データテーブルを含むワークシートが再計算されるたびに再計算されます。データテーブルを含むワークシートでの計算を高速化するには、計算オプションを次のように変更する必要があります。Automatically Recalculate 次のセクションで説明するように、ワークシートではなくデータテーブル。