Excel-data-analysis-what-if-analysis-with-data-tables
データテーブルを使用した仮説分析
Excelのデータテーブルを使用すると、1つまたは2つの入力を簡単に変更し、仮説分析を実行できます。 データテーブルは、いくつかのセルの値を変更し、問題に対するさまざまな回答を得ることができるセルの範囲です。
データテーブルには2種類あります-
- 1変数データテーブル
- 2変数データテーブル
分析問題に3つ以上の変数がある場合、Excelのシナリオマネージャーツールを使用する必要があります。 詳細については、このチュートリアルの章– link：/excel_data_analysis/what_if_analysis_with_scenario_manager [シナリオマネージャを使用したWhat-If分析]を参照してください。
1変数データテーブル
1つ以上の式の1つの変数の異なる値がそれらの式の結果をどのように変更するかを確認する場合は、1変数データテーブルを使用できます。 つまり、1変数のデータテーブルを使用すると、1つの入力を変更すると出力の数がどのように変化するかを判断できます。 例の助けを借りてこれを理解できます。
例
30年間の在職期間で5,000,000のローンがあります。 さまざまな金利の毎月の支払い（EMI）を知りたい場合。 また、2年目に支払われる利息と元金の額を知りたい場合もあります。
1変数データテーブルを使用した分析
1変数データテーブルを使用した分析は、3つのステップで実行する必要があります-
- ステップ1 *-必要な背景を設定します。
- ステップ2 *-データテーブルを作成します。
- ステップ3 *-分析を実行します。
これらの手順を詳細に理解しましょう-
ステップ1：必要な背景を設定する
- 金利が12％であると仮定します。
- すべての必要な値をリストします。
- 式がセル参照の代わりに名前を持つように、値を含むセルに名前を付けます。
- EMI、累積利息、および累積元金の計算を、それぞれExcel関数（PMT、CUMIPMT、およびCUMPRINC）で設定します。
ワークシートは次のようになります-
列Cのセルには、列Dの対応するセルに指定された名前が付けられていることがわかります。
ステップ2：データテーブルを作成する
- 値のリスト、つまり 次のように列Eの入力セルで置換する金利-
- 最初の関数（ PMT ）を1行上のセルと値の列の右側のセルに入力します。 最初の関数の右側のセルに他の関数（ CUMIPMTおよびCUMPRINC ）を入力します。 +これで、金利の値の上の2行は次のようになります-
ステップ3：仮説分析データテーブルツールを使用して分析を実行する
- 置換する数式と値を含むセルの範囲を選択します。 範囲-E2：H13を選択します。
- リボンの[データ]タブをクリックします。
- [データツール]グループの[仮説分析]をクリックします。
- ドロップダウンリストで[データテーブル]を選択します。
- データテーブル*ダイアログボックスが表示されます。
- [列入力セル]ボックスのアイコンをクリックします。
- セル Interest_Rate （C2）をクリックします。
Column入力セルが$ C $ 2として取得されていることがわかります。 OKをクリックしてください。
データテーブルには、以下に示すように、各入力値の計算結果が入力されます-
54,000のEMIを支払うことができる場合、12.6％の金利が適切であることがわかります。
2変数データテーブル
数式内の2つの変数の異なる値がその数式の結果をどのように変更するかを確認する場合は、2変数データテーブルを使用できます。 つまり、2変数のデータテーブルを使用すると、2つの入力を変更すると1つの出力がどのように変化するかを判断できます。 例の助けを借りてこれを理解できます。
例
50,000,000のローンがあります。 金利とローン保有期間のさまざまな組み合わせが毎月の支払い（EMI）にどのように影響するかを知りたいと思います。
2変数データテーブルを使用した分析
2変数データテーブルを使用した分析は、3つのステップで行う必要があります-
- ステップ1 *-必要な背景を設定します。
- ステップ2 *-データテーブルを作成します。
- ステップ3 *-分析を実行します。
ステップ1：必要な背景を設定する
- 金利が12％であると仮定します。
- すべての必要な値をリストします。
- 数式にセル参照の代わりに名前が付くように、値を含むセルに名前を付けます。
- Excel関数- PMT でEMIの計算を設定します。
ワークシートは次のようになります-
列Cのセルには、列Dの対応するセルで指定された名前が付けられていることがわかります。
ステップ2：データテーブルを作成する
- セルF2に = EMI と入力します。
- 入力値の最初のリスト、つまり 数式の下のセルから始まる列Fの金利、つまり F3.
- 入力値の2番目のリスト、つまり 数式の右側のセルから始まる、行2全体の支払い回数、つまり G2. +データテーブルは次のようになります-
仮説分析ツールのデータテーブルを使用して分析を行う
- 数式を含むセルの範囲と、置換する2組の値を選択します。 範囲-F2：L13を選択します。
- リボンの[データ]タブをクリックします。
- [データツール]グループの[仮説分析]をクリックします。
- ドロップダウンリストから[データテーブル]を選択します。
[データテーブル]ダイアログボックスが表示されます。
- [行入力セル]ボックスのアイコンをクリックします。
- セル NPER （C3）をクリックします。
- 繰り返しますが、[行入力セル]ボックスのアイコンをクリックします。
- 次に、「列入力セル」ボックスのアイコンをクリックします。
- セルInterest_Rate（C2）をクリックします。
- 再度、列入力セルボックスのアイコンをクリックします。
行入力セルは＆dollar; C＆dollar; 3として、列入力セルは＆dollar; C＆dollar; 2として取得されます。 OKをクリックしてください。
データテーブルは、2つの入力値の各組み合わせの計算結果で満たされます-
54,000のEMIを支払うことができる場合、12.2％の金利と288 EMIが適しています。 これは、ローンの保有期間が24年になることを意味します。
データテーブルの計算
データテーブルは、変更されていない場合でも、それらを含むワークシートが再計算されるたびに再計算されます。 データテーブルを含むワークシートの計算を高速化するには、次のセクションで示すように、計算オプションを変更して、ワークシートを*自動再計算*し、データテーブルではないようにする必要があります。
ワークシートでの計算の高速化
あなたは2つの方法でデータテーブルを含むワークシートで計算を高速化することができます-
- Excelオプションから。
- リボンから。
Excelオプションから
- リボンの[ファイル]タブをクリックします。
- 左ペインのリストからオプションを選択します。
[Excelオプション]ダイアログボックスが表示されます。
- 左ペインから、 Formulas を選択します。
- [計算オプション]セクションの[ワークブック計算]の下にある[データテーブルを除く自動]オプションを選択します。 OKをクリックしてください。
リボンから
- リボンの[式]タブをクリックします。
- [計算]グループの[計算オプション]をクリックします。
- ドロップダウンリストで[データテーブルの自動除外]を選択します。
