データ テーブルを使用して複数の結果を計算する

概要

Microsoft Excel では、データ テーブルは、what-if 分析ツールと呼ばれる一連のコマンドの一部です。 データ テーブルを作成して分析することは、what-if 分析を実行することを意味します。

What-If 分析は、セルの値を変更したときにワークシートの数式の結果にどのように影響するかを調べるプロセスです。 たとえば、データ テーブルを使ってローンの金利や期間を変化させ、毎月の返済額の可能性を評価することができます。

what-if 分析の種類 

Excel には、"シナリオ"、"データ テーブル"、"ゴールシーク"の3 種類の what-if 分析ツールがあります。 シナリオとデータ テーブルでは、入力値のセットを使用して、可能な結果を計算します。 ゴールシークは明らかに異なり、1 つの結果を使用し、その結果を生成する可能性のある入力値を計算します。

シナリオと同様に、データ テーブルは考えられる一連の結果を調べるのに役立ちます。 シナリオとは異なり、データ テーブルには、すべての結果が 1 つのワークシート上の 1 つのテーブルに表示されます。 データ テーブルを使用すると、さまざまな可能性をひとめで簡単に確認できます。 1 つまたは 2 つの変数に焦点を絞るため、結果を表形式で簡単に読み取って共有できます。

データ テーブルでは 3 個以上の変数は操作できません。 3 個以上の変数を分析する場合は、代わりにシナリオを使用してください。 変数は 1 つまたは 2 つ (行の代入セルに 1 つ、列の代入セルに 1 つ) に制限されていますが、データ テーブルには必要な数の異なる変数値を含めることができます。 シナリオには追加できる値は最大で 32 個ですが、作成できるシナリオの数には限りがありません。

詳細については、「what-if 分析の概要」の記事を参照してください。

データ テーブルの基本

テストする必要がある変数と数式の数に応じて、1 変数または 2 変数のデータ テーブルを作成します。

単入力テーブル 

1 つ以上の数式の 1 つの変数の値を変更したときに数式の結果がどのように変化するかを調べるには、単入力テーブルを使います。 たとえば、単入力テーブルで PMT 関数を使用して、利率の変化が毎月のローン返済額に及ぼす影響を調べることができます。 変数値を 1 つの列または 1 つの行に入力すると、隣接する列または行に結果が表示されます。

次の図では、セル D2 に代入セル B3 を参照する支払い数式 =PMT(B3/12,B4,-B5) が含まれています。

複入力テーブル 

1 つの数式の 2 つの変数の値を変更したときに数式の結果がどのように変化するかを調べるには、複入力テーブルを使います。 たとえば、複入力テーブルを使用して、利率とローン期間の値の変化が毎月のローン返済額に及ぼす影響を調べることができます。

次の図では、セル C2 に支払い数式 =PMT(B3/12,B4,-B5) が含まれています。ここでは、2 つの代入セル (B3 と B4) を使用します。

データ テーブルの計算 

ワークシートが再計算されるたびに、データ テーブルも再計算されます。データに変更がない場合でも行われます。 データ テーブルを含むワークシートの計算を速くするには、[計算方法] のオプションを変更して、ワークシートのデータ テーブル以外の部分だけが自動的に再計算されるようにします。 詳細については、「データ テーブルを含むワークシートでの計算を高速化する」セクションを参照してください。

単入力テーブルを作成する

1 変数データ テーブルには、1 つの列 (列指向)、または行全体 (行指向) のいずれかの入力値が含まれます。 1 変数データ テーブル内の数式は、1 つの入力セルのみを参照する必要があります。

手順

1. 代入セルに代入する値の一覧を入力します。値は、縦方向に 1 列で入力するか、横方向に 1 行で入力します。 値の両側に空の行と列をいくつか残しておきます。

2. 次のいずれかの操作を行います。

   • データ テーブルが "列方向"(変数値が 1 列に入力されている) の場合は、値の列の右上のセルに数式を入力します。 この 1 変数データ テーブルは列方向で、数式はセル D2 に含まれています。

     

     他の数式に対するさまざまな値の効果を調べる場合は、最初の数式の右側にあるセル に 追加の数式を入力します。

   • データ テーブルが "行方向"(変数値が 1 行に入力されている) の場合は、最初の値の左下のセルに数式を入力します。

     他の数式に対するさまざまな値の効果を調べる場合は、最初の数式の 下 のセルに追加の数式を入力します。

3. 代入する数式と値を含むセル範囲を選択します。 上の図では、この範囲は C2:D5 です。

4. [ データ ] タブで、[ What-If Analysis > データ テーブル ] (Excel 2016 の [データ ツール] グループまたは [予測 ] グループ) を選択します。

5. 次のいずれかの操作を行います。

   • データ テーブルが列指向の場合は、[列入力セル] フィールドに入力セルのセル参照を 入力 します。 上の図では、代入セルは B3 です。

   • データ テーブルが行方向の場合は、[行の代入セル] フィールドに代入セルへのセル範囲を入力します。

     注

     データ テーブルの作成後、結果セルの書式を変更する場合もあります。 この図では、結果セルが通貨として書式設定されています。

単入力テーブルに数式を追加する

単入力テーブルで使用される数式は、同じ代入セルを参照している必要があります。

手順は次のとおりです

1. 次のいずれかを行います。

   • データ テーブルが列方向の場合は、データ テーブルの一番上の行に入力されている既存の数式の右側の空白セルに、新しい数式を入力します。
   • データ テーブルが行方向の場合は、データ テーブルの最初の列にある既存の数式の下の空白セルに新しい数式を入力します。

2. データ テーブルと新しい数式を含むセルの範囲を選択します。

3. [ データ ] タブで、[ What-If Analysis>Data Table (Excel 2016 の [データ ツール] グループまたは [予測 ] グループ) を選択します。

4. 次のいずれかの操作を行います。

   • データ テーブルが列方向の場合は、[列の代入セル] ボックスに代入セルへのセル範囲を入力します。
   • データ テーブルが行方向の場合は、[行の代入セル] ボックスに代入セルへのセル範囲を入力します。

複入力テーブルを作成する

複入力テーブルでは、2 つの代入値のリストを含む数式を使用します。 数式は 2 つの異なる代入セルを参照している必要があります。

手順

1. ワークシートのセルに、2 つの代入セルを参照する数式を入力します。
   次の例では、数式の開始値がセル B3、B4、B5 に入力されており、セル C2 に数式 =PMT(B3/12,B4,-B5) を入力します。
2. 数式の下に、縦 1 列に 1 つ目の代入値のリストを入力します。
   この場合、セル C3、C4、C5 に異なる金利を入力します。
3. 数式と同じ行 (その右側) に 2 番目のリストを入力します。
   セル D2 と E2 にローン期間を月単位で入力します。
4. 数式 (C2)、値の行と列の両方 (C3:C5 と D2:E2)、計算した値を表示するセル (D3:E5) を含むセル範囲を選択します。
   この場合は、範囲 C2:E5 を選択します。
5. [ データ ] タブの [ データ ツール ] グループまたは [ 予測 ] グループ (Excel 2016) で、[ What-If Analysis > データ テーブル ] (Excel 2016 の [データ ツール ] グループまたは [予測 ] グループ) を選択します。
6. [行の代入セル] フィールドに、行の入力値の代入セルへの参照を入力します。
   [行の代入セル] ボックスに「セル B4」と入力します。
7. [列の代入セル] フィールドに、列の入力値の代入セルへの参照を入力します。
   [列の代入セル] ボックスに「B3」と入力します。
8. [OK] を選択します。

2 変数データ テーブルの例

2 変数のデータ テーブルでは、金利とローン条件のさまざまな組み合わせが毎月の住宅ローン支払いにどのように影響するかを示すことができます。 この図では、セル C2 に 支払い数式の =PMT(B3/12,B4,-B5) が含まれています。ここでは、2 つの代入セル (B3 と B4) を使用します。

データ テーブルを含むワークシートの計算を速くする

この計算オプションを設定した場合、ブック全体で再計算が行われると、データ テーブルの計算は行われません。 データ テーブルを手動で再計算するには、数式を選択して F9 キーを押します。

計算パフォーマンスを向上させるには、次の手順に従います。

1. [ File>Options>Formulas] を選択します。

2. [計算オプション] セクションで、[自動] を選択します。

   ヒント

   必要に応じて、[数式] タブの [計算オプション] の矢印を選択し、[自動] を選択します。

関連情報

特定の目標がある場合や大量の変数データを使用している場合は、その他の Excel ツールをいくつか使用して、What-If 分析を実行できます。

ゴール シーク

数式から得たい結果がわかっていて、その結果を得るために必要な数式の入力値が正確にわからない場合は、ゴールシーク機能を使用します。 「ゴール シークを使用して入力値を調整し、必要な結果を求める」をご覧ください。

Excel ソルバー

Excel ソルバー アドインを使用すると、一連の入力変数の最適値を見つけることができます。 ソルバーは、目的セルや制約セルの中の数式を計算するのに使われる決定変数セルまたは単に変数セルと呼ばれるセルのグループと連動します。 ソルバーは決定変数セルの値を調整し、制約セル上の制限を満たし、目的セルに必要な結果を生成します。 詳細については、「ソルバーを使用して問題を定義して解決する」の記事を参照してください。

異なる数値をセルに差し込むことで、問題に対するさまざまな答えをすぐに思い付くことができます。 好例として、金利やローン期間 (月単位) が異なる PMT 関数を使用して、住宅や自動車の購入時にどれくらいのローンが組めるかを計算する方法があります。 データ テーブルと呼ばれるセル範囲に数値を入力します。

ここでは、データ テーブルはセル B2:D8 の範囲です。 B4 (ローン金額) の値を変更すると、列 D の月々の返済額が自動的に更新されます。 金利 3.75% を使用すると、D2 は数式 =PMT(C2/12,$B$3,$B$4) を使って、月々の返済額 1,042.01 ドルを返します。

テストする変数と数式の数に応じて、1 つまたは 2 つの変数を使用できます。

1 変数テストを使用すると、数式内の 1 つの変数の値を変えると結果がどのように変化するかを確認できます。 たとえば、PMT 関数を使用すると、住宅ローンの月々の返済額の金利を変更できます。 変数値 (金利) を 1 つの列または行に入力すると、隣接する列または行に結果が表示されます。

このライブ ブックのセル D2 には、支払い式 =PMT(C2/12,$B$3,$B$4) が含まれています。 セル B3 は "可変"セルで、異なる期間 (月々の返済期間の数) を入力できます。 セル D2 では、PMT 関数によって金利 3.75%/12、返済期間 360 か月、ローン金額 225,000 ドルが代入され、月々の返済額 1,042.01ドルが計算されます。

2 変数テストを使用すると、数式内の 2 つの変数の値を変えると結果がどのように変化するかを確認できます。 たとえば、金利と月々の返済期間の異なる組み合わせをテストして、住宅ローンの返済額を計算できます。

このライブ ブックでは、セル C3 に支払い式 =PMT($B$3/12,$B$2,B4) が含まれています。これは、B2 と B3 の 2 つの変数セルを使用します。 セル D2 では、PMT 関数によって金利 3.875%/12,、返済期間 360 か月、ローン金額 225,000 ドルが代入され、月々の返済額 1,058.03 ドルが計算されます。