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

データ テーブルは、一部のセルの値を変更し、問題に対するさまざまな回答を考え出すセルの範囲です。 データ テーブルの良い例では、異なるローン金額と金利を持つ PMT 関数を使用して、住宅ローンの手頃な金額を計算します。 結果の対応する変動を観察するために異なる値を試すのは、データ分析の一般的 なタスクです

このMicrosoft Excel、データ テーブルは、分析ツールと呼ばれる一What-If一部です。 データ テーブルを構築して分析するときに、what-if 分析を行います。

What-If 分析は、セルの値を変更したときにワークシートの数式の結果にどのように影響するかを調べるプロセスです。 たとえば、データ テーブルを使用してローンの金利と期間を変更し、潜在的な毎月の支払額を評価できます。

注: データ テーブルとデータ テーブル (VBA) を使用してVisual Basic for Applications実行できます。 詳細については、「データ テーブルのExcel What-If VBA を使用した高速な計算」を参照してください

what-if 分析の種類    

分析ツールには、シナリオ、データ テーブル、目標Excelの 3種類があります。 シナリオとデータ テーブルでは、入力値のセットを使用して、考えられる結果を計算します。 目標シークは明らかに異なっています。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 つのデータ テーブル

複入力テーブル    

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

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

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

データ テーブルの計算    

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

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

手順

  1. 入力セルに置き換える値の一覧を入力します (1 列下または 1 行に渡る)。 値の両側に空の行と列をいくつか残しておきます。

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

    • データ テーブルが列指向 (変数 値が列内にある) 場合は、1 行上のセルに数式を入力し、値の列の右側に 1 つのセルを入力します。 この 1 変数データ テーブルは列指向であり、数式はセル D2 に含まれる。


      変数が 1 つのデータ テーブル
      他の数式に対するさまざまな値の影響を調べる場合は、最初の数式の右側にあるセルに追加の数式を入力します。

    • データ テーブルが行指向 (変数 値が行内にある) 場合は、最初の値の左側にあるセル 1 列、値の行の下の 1 つのセルに数式を入力します。

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

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

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

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

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

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

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

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

手順は次のとおりです

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

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

    • データ テーブルが行指向の場合は、データ テーブルの最初の列の既存の数式の下にある空のセルに新しい数式を入力します。

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

  3. [データ] タブで、[What-If Analysis>データ テーブル]をクリックします([データ ツール] グループまたは [予測] グループ 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 には、2 つの入力セル B3 と B4 を使用する =PMT(B3/12,B4,-B5)という支払い式がセル C2 に含まれている。

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

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

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

  1. [ 数式] の [> オプション] > クリックします。

  2. [計算オプション ] セクションの [計算] で、[データテーブルを除 く自動] をクリックします

    ヒント: 必要に応じて、[数式]タブの[計算オプション] の矢印をクリックし、[データ テーブルを除く自動] ([計算] グループ内)をクリックします。

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

ゴール シーク

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

Excel ソルバー

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

異なる数値をセルに差し込むと、問題に対するさまざまな答えがすぐに表示されます。 大きな例は、異なる金利とローン期間 (月) で PMT 関数を使用して、自宅や車に対してどのくらいのローンを支払えるのか把握する方法です。 データ テーブルと呼ばれるセルの範囲に数値を入力します。

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

この場合、B2:D8 というセルの範囲がデータ テーブル

テストする変数と数式の数に応じて、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 には、2 つの可変セル B2 と B3 を使用する PMT ($B$3/12,$B$2,B4) という支払い式が含まれています。 セル C2 では、PMT 関数は金利 3.875%/12、360 か月、および 225,000 ドルのローンを差し込み、月額 1,058.03 ドルの支払いを計算します。

補足説明

Excel 技術コミュニテでは、いつでも専門家に質問できます。また、Answers コミュニティでは、サポートを受けられます。

ヘルプを表示

Office のスキルを磨く
トレーニングの探索
新機能を最初に入手
Office Insider に参加する

この情報は役に立ちましたか?

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×