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

データテーブルとは、一部のセルの値を変更し、問題に対してさまざまな回答を得ることができるセルの範囲です。データテーブルの好例としては、異なるローン金額や金利を使って PMT 関数で住宅ローンの返済可能額を計算する例があります。さまざまな値を試して、それに応じた結果の変化を観察することは、"データ分析"でよく行われる作業です。

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

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

what-if 分析の種類

Excel には、"シナリオ"、"データテーブル"、"ゴールシーク"の3 種類の what-if 分析ツールがあります。シナリオとデータテーブルは、一連の入力値を用いて、考えられる結果を計算します。ゴールシークはこれらとは明確に異なり、単一の結果を用いて、その結果を生み出す可能性のある入力値を計算します。

シナリオと同様に、データテーブルは考えられる一連の結果を調べるのに役立ちます。シナリオとは異なり、データテーブルには、すべての結果が 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 つのデータテーブル

複入力テーブル

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

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

変数が 2 つあるデータテーブル

データテーブルの計算

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

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

手順

代入セルに代入する値の一覧を入力します。値は、縦方向に 1 列で入力するか、横方向に 1 行で入力します。値の両側に空の行と列をいくつか残しておきます。
次のいずれかの操作を行います。
- データテーブルが "列方向"(変数値が 1 列に入力されている) の場合は、値の列の右上のセルに数式を入力します。この 1 変数データテーブルは列方向で、数式はセル D2 に含まれています。他の数式でさまざまな値を試し、その変化を調べるには、最初の数式の "右の"セルに追加の数式を入力します。
- データテーブルが "行方向"(変数値が 1 行に入力されている) の場合は、最初の値の左下のセルに数式を入力します。他の数式でさまざまな値を試し、その変化を調べるには、最初の数式の "下の"セルに追加の数式を入力します。
代入する数式と値を含むセル範囲を選択します。上の図では、この範囲は C2:D5 です。
[データ] タブで、[what-if 分析] >[データテーブル] (Excel 2016 の [データツール] グループまたは [予測] グループ内) を選択します。
次のいずれかの操作を行います。
- データテーブルが列方向の場合は、[列の代入セル] フィールドに代入セルへのセル参照を入力します。上の図では、代入セルは B3 です。
- データテーブルが行方向の場合は、[行の代入セル] フィールドに代入セルへのセル範囲を入力します。
  
  注: データテーブルの作成後、結果セルの書式を変更する場合もあります。この図では、結果セルが通貨として書式設定されています。

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

手順は次のとおりです

次のいずれかを行います。
- データテーブルが列方向の場合は、データテーブルの一番上の行に入力されている既存の数式の右側の空白セルに、新しい数式を入力します。
- データテーブルが行方向の場合は、データテーブルの最初の列にある既存の数式の下の空白セルに新しい数式を入力します。
データテーブルと新しい数式を含むセルの範囲を選択します。
[データ] タブで、[what-if 分析] > [データテーブル] (Excel 2016 の [データツール] グループまたは [予測] グループ内) を選択します。
次のいずれかを行います。
- データテーブルが列方向の場合は、[列の代入セル] ボックスに代入セルへのセル範囲を入力します。
- データテーブルが行方向の場合は、[行の代入セル] ボックスに代入セルへのセル範囲を入力します。

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

手順

ワークシートのセルに、2 つの代入セルを参照する数式を入力します。

次の例では、数式の開始値がセル B3、B4、B5 に入力されており、セル C2 に数式 =PMT(B3/12,B4,-B5) を入力します。
数式の下に、縦 1 列に 1 つ目の代入値のリストを入力します。

この場合、セル C3、C4、C5 に異なる金利を入力します。
数式と同じ行 (その右側) に 2 番目のリストを入力します。

セル D2 と E2 にローン期間を月単位で入力します。
数式 (C2)、値の行と列の両方 (C3:C5 と D2:E2)、計算した値を表示するセル (D3:E5) を含むセル範囲を選択します。

この場合は、範囲 C2:E5 を選択します。
[データ] タブの [データツール] グループまたは [予測] グループ (Excel 2016 の場合) で、[what-if 分析] >[データテーブル] (Excel 2016 の [データツール] グループまたは [予測] グループ内) を選択します。
[行の代入セル] フィールドに、行の入力値の代入セルへの参照を入力します。[行の代入セル] ボックスに「セル B4」と入力します。
[列の代入セル] フィールドに、列の入力値の代入セルへの参照を入力します。[列の代入セル] ボックスに「B3」と入力します。
[OK] を選択します。

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

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

変数が 2 つあるデータテーブル

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

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

[ファイル] > [オプション] > [数式] の順に選択します。
[計算オプション] セクションで、[自動] を選択します。

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

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

ゴールシーク

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

Excel ソルバー

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