データ テーブルは、一部のセルの値を変更し、問題に対してさまざまな回答を得ることができるセルの範囲です。 データ テーブルの良い例は、住宅ローンローンの手頃な金額を計算するために、異なるローン金額と金利を持つ PMT 関数を採用しています。 さまざまな値を試して、結果の対応するバリエーションを観察することは、 データ分析の一般的なタスクです。
Microsoft Excel では、データ テーブルは、What-If 分析ツールと呼ばれる一連のコマンドの一部です。 データ テーブルを構築して分析すると、what-if 分析が実行されます。
What-If 分析は、セルの値を変更したときにワークシートの数式の結果にどのように影響するかを調べるプロセスです。 たとえば、データ テーブルを使用して、ローンの金利と期間の長さを変更して、潜在的な毎月の支払額を評価できます。
注: データ テーブルとVisual Basic for Applications (VBA) を使用して、より高速な計算を実行できます。 詳細については、「Excel What-If データ テーブル: VBA を使用した高速な計算」を参照してください。
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) が含まれています。これは、B3 と B4 の 2 つの入力セルを使用します。
データ テーブルの計算
ワークシートが再計算されるたびに、データに変更がない場合でも、データ テーブルも再計算されます。 データ テーブルを含むワークシートの計算を速くするには、[計算方法] のオプションを変更して、ワークシートのデータ テーブル以外の部分だけが自動的に再計算されるようにします。 詳細については、「 データ テーブルを含むワークシートでの計算の高速化」セクションを参照してください。
1 変数データ テーブルには、1 つの列 (列指向)、または行全体 (行指向) のいずれかに入力値が含まれます。 1 変数データ テーブル内の数式は、1 つの 代入セルのみを参照する必要があります。
手順
-
入力セルに置き換える値の一覧を入力します(1 列下または 1 行に渡ります)。 値の両側に空の行と列をいくつか残しておきます。
-
次のいずれかの操作を行います。
-
データ テーブルが 列指向 の場合 (変数の値は列内にあります)、上のセルの 1 行に数式を入力し、値の列の右側に 1 つのセルを入力します。 この 1 変数データ テーブルは列指向であり、数式はセル D2 に含まれています。
-
データ テーブルが 行指向 の場合 (変数の値が行内にある) 場合は、最初の値の左側にあるセル 1 列に数式を入力し、値の行の下に 1 つのセルを入力します。他の数式に対するさまざまな値の効果を調べる場合は、最初の数式の 下 のセルに追加の数式を入力します。
-
-
代入する数式と値を含むセル範囲を選択します。 上の図では、この範囲は C2:D5 です。
-
[ データ ] タブで、[ What-If Analysis > データ テーブル ] をクリックします ([ データ ツール] グループまたは [ Excel 2016の予測 ] グループ)。
-
次のいずれかの操作を行います。
-
データ テーブルが列指向の場合は、[列入力セル] フィールドに入力セルの セル参照 を 入力 します。 上の図では、入力セルは B3 です。
-
データ テーブルが行指向の場合は、[行入力セル] フィールドに入力セルのセル参照を 入力 します。
注: データ テーブルの作成後、結果セルの書式を変更する場合もあります。 図では、結果のセルは通貨として書式設定されます。
-
単入力テーブルで使用される数式は、同じ代入セルを参照している必要があります。
手順は次のとおりです
-
次のいずれかの操作を行います。
-
データ テーブルが列指向の場合は、データ テーブルの先頭行にある既存の数式の右側にある空白セルに新しい数式を入力します。
-
データ テーブルが行指向の場合は、データ テーブルの最初の列の既存の数式の下にある空のセルに新しい数式を入力します。
-
-
データ テーブルと新しい数式を含むセルの範囲を選択します。
-
[ データ ] タブで、[ What-If Analysis > データ テーブル ] をクリックします ([ データ ツール] グループまたは [ 予測 ] グループの [ 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 Analysis > データ テーブル ] ([ データ ツール] グループまたは [ 予測 ] グループの [ Excel 2016] ) をクリックします。
-
[ 行入力セル ] フィールドに、行の入力値の入力セルへの参照を入力します。[行入力セル] ボックスに「セル B4」と入力します。
-
[ 列入力セル ] フィールドに、列の入力値の入力セルへの参照を入力します。[列の入力] セル ボックスに「B3」と入力します。
-
[OK] をクリックします。
2 変数データ テーブルの例
2 変数のデータ テーブルは、金利とローン条件の異なる組み合わせが、毎月の住宅ローンの支払いに与える影響を示します。 この図では、セル C2 に支払い式 =PMT(B3/12,B4,-B5) が含まれています。これは、B3 と B4 の 2 つの入力セルを使用します。
この計算オプションを設定した場合、ブック全体で再計算が行われると、データ テーブルの計算は行われません。 データ テーブルを手動で再計算するには、数式を選択して F9 キーを押します。
計算パフォーマンスを向上させるには、次の手順に従います。
-
[ ファイル > オプション] > [数式] をクリックします。
-
[ 計算オプション ] セクションの [ 計算] で、[ データ テーブルを除く自動] をクリックします。
ヒント: 必要に応じて、[ 数式 ] タブの [ 計算オプション] の矢印をクリックし、[ データ テーブルを除く自動 ] ( [計算 ] グループ) をクリックします。
特定の目標がある場合や大量の変数データを使用している場合は、その他の Excel ツールをいくつか使用して、What-If 分析を実行できます。
ゴール シーク
数式から予想される結果がわかっていても、その結果を取得するために数式で必要な入力値が正確にわからない場合は、Goal-Seek 機能を使用します。 「ゴール シークを使用して入力値を調整し、必要な結果を求める」をご覧ください。
Excel ソルバー
Excel ソルバー アドインを使用して、一連の入力変数の最適な値を見つけることができます。 ソルバーは、目的セルと制約セルの数式を計算する際に使用されるセルのグループ (デシジョン変数、または単に変数セルと呼ばれます) で動作します。 ソルバーは決定変数セルの値を調整し、制約セル上の制限を満たし、目的セルに必要な結果を生成します。 詳細については、この記事の「 ソルバーを使用して問題を定義して解決する」を参照してください。
異なる数値をセルに差し込むことで、問題に対するさまざまな答えをすぐに思い付くことができます。 良い例として、さまざまな金利とローン期間 (月単位) の PMT 関数を使用して、自宅や車に対してどの程度のローンを支払うことができるかを把握します。 データ テーブルと呼ばれるセル範囲に数値を入力します。
ここで、データ テーブルはセル B2:D8 の範囲です。 B4 の値、ローン金額、および列 D の毎月の支払いを変更すると、自動的に更新されます。 D2 は、3.75% の金利を使用して、次の数式 =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 つの変数セルを使用します。 セル C2 では、PMT 関数は金利 3.875%/12、360 か月、225,000 ドルのローンに差し込み、毎月 1,058.03 ドルの支払いを計算します。
補足説明
Excel Tech Communityで、いつでも専門家に質問できます。また、コミュニティでは、サポートを受けられます。
