スキップしてメイン コンテンツへ

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

データテーブルは、一部のセルの値を変更して、問題に対するさまざまな回答を得ることができるセルの範囲です。 データテーブルの例としては、 PMT関数にさまざまなローン金額と金利を使って、住宅ローンの返済額を計算するのに役立ちます。 さまざまな値を試して、結果の対応するバリエーションを確認することは、データ分析の一般的なタスクです。

Microsoft Excel では、データテーブルは、What-if 分析ツールと呼ばれる一連のコマンドに含まれています。 データテーブルの構築と分析では、what-if 分析を実行します。

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

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

What-if 分析の種類    

Excel には、シナリオ、データテーブルゴールシークなどの3種類の分析ツールがあります。 シナリオとデータテーブルは、一連の入力値を使って、考えられる結果を計算します。 ゴール-シークが明確に異なる場合は、1つの結果を使用し、その結果を生成する可能性のある入力値を計算します。

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

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

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

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

単入力テーブル    

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

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

変数が 1 つのデータ テーブル

複入力テーブル    

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

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

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

データ テーブルの計算    

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

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

次の手順に従います。

  1. 代入セルで代用する値のリストを、1列または1行に入力します。 値の両側に空の行と列をいくつか残しておきます。

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

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

      変数が 1 つのデータ テーブル

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

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

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

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

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

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

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

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

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

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

手順は次のとおりです

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

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

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

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

  3. [データ] タブで、[ What-if 分析>データテーブル] をクリックします ([データツール] グループまたは 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 分析 > データテーブル] ([データツール] グループまたは Excel 2016の予測グループ内) をクリックします。

  6. [行の代入セル] フィールドに、行の入力値の代入セルへの参照を入力します。
    [行の代入セル] ボックスにセル B4を入力します。

  7. [列の代入セル] フィールドに、列の入力値の代入セルへの参照を入力します。
    [列の代入セル] ボックスにB3を入力します。

  8. [OK] をクリックします。

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

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

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

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

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

  1. 次のいずれかを行ってください :

    • Excel 2007で、[ Microsoft Office ボタン Office ボタンの画像 ] をクリックし、[ Excel のオプション] をクリックして、[数式] カテゴリをクリックします。

    • その他のすべてのバージョンでは、[ファイル>オプション>数式] をクリックします。

  2. [計算方法の設定] セクションの [計算] で、[データテーブル以外自動] をクリックします。

    ヒント: 必要に応じて、[数式] タブで、[計算方法の設定] の矢印をクリックし、[データテーブル以外自動] (計算グループ) をクリックします。

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

ゴール シーク

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

Excel ソルバー

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

1つのセルにさまざまな数値を差し込むと、問題に対するさまざまな回答をすばやく見つけることができます。 優れた例として、さまざまな金利とローン期間 (月数) が指定されたPMT関数を使用して、住宅または自動車で利用可能なローンの量を把握することができます。 データテーブルと呼ばれるセル範囲に数値を入力します。

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

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

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

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

このライブブックでは、セル D2 に支払式=PMT (C2/12, $B $3, $B $4) が含まれています。 セル B3 は可変セルで、さまざまな長さの期間 (月々の支払い期間) を接続できます。 セル D2 では、PMT 関数は利率である 100%/12、360か月、$225000 のローンをプラグインし、$1042.01 の毎月の支払いを計算します。

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

このライブブックでは、セル C3 に支払い式、 =PMT ($B $ 3/12、$B $ 2, B4) が含まれています。これには、2つの変数セル B2 と B3 を使用します。 セル C2 では、PMT 関数は利率 3.875%/12 年、360か月、$225000 ローンと計算され、$1058.03 の毎月の支払いを計算します。

補足説明

Excel Tech Community では、いつでも専門家に質問できます。Microsoft コミュニティでは、サポートを受けられます。また、Excel User Voice では、新機能についての提案や改善案を送信することができます。

注:  このページは、自動翻訳によって翻訳されているため、文章校正のエラーや不正確な情報が含まれている可能性があります。 私たちの目的は、このコンテンツがお客様の役に立つようにすることです。 情報が役に立ったかどうか、ご意見をお寄せください。 参考までに、こちらから英語の記事をお読みいただけます。

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

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

ご意見をいただきありがとうございます。

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

×