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

注: 最新のヘルプ コンテンツをできるだけ早く、お客様がお使いの言語で提供したいと考えております。このページは、自動翻訳によって翻訳されているため、文章校正のエラーや不正確な情報が含まれている可能性があります。私たちの目的は、このコンテンツがお客様の役に立つようにすることです。お客様にとって役立つ情報であったかどうかを、このページの下部でお知らせください。簡単に参照できるように、こちらに英語の記事があります。

データテーブルはセルの範囲であり、一部のセルの値を変更して、問題に対するさまざまな回答を思い付くことができます。データテーブルの良い例としては、住宅ローンローンで手頃な金額を計算するために、異なるローン額と利率を持つPMT関数を使用しています。さまざまな値を試して、結果に対応するバリエーションを確認することは、データ分析の一般的な作業です。

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

what-if 分析は、セルの値を変更して、ワークシートの数式の結果にどのように影響するかを確認するプロセスです。たとえば、データテーブルを使用して、ローンの利率や期間の長さを変更することができます。毎月の支払い額を評価します。

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

what-if 分析の種類   

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

データ テーブルでは、シナリオと同様に、予測される結果のセットを調べることができます。しかし、シナリオとは異なり、すべての結果が 1 つのワークシートの 1 つの表に表示されます。データ テーブルを使用すると、予測される結果の範囲をひとめで簡単に確認できます。1 つまたは 2 つの変数だけを使用するので、表形式で結果を簡単に確認および共有できます。

データ テーブルには 3 つ以上の変数を入力できません。3 つ以上の変数を分析する場合は、代わりにシナリオを使用する必要があります。1 つのデータ テーブルに入力できる変数は 1 つまたは 2 つ (行の代入セルに 1 つ、列の代入セルに 1 つ) だけですが、変数の値はいくつでも入力できます。1 つのシナリオに入力できる値は最大 32 個ですが、シナリオはいくつでも作成できます。

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

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

単入力テーブル   

1つまたは複数の数式の1つの変数の値によって、それらの数式の結果がどのように変化するかを確認する場合は、単一変数のデータテーブルを使用します。たとえば、1つの変数のデータテーブルを使用して、 PMT 関数を使用して毎月の住宅ローンの支払いにさまざまな利率がどのように影響するかを確認できます。変数の値を1つの列または行に入力すると、結果は隣接する列または行に表示されます。

次の図では、セル D2 に返済額の計算式 =PMT(B3/12,B4,-B5) が入力されており、この数式は代入セル B3 を参照します。

単入力テーブル

複入力テーブル   

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

次の図では、セル C2 に返済額の計算式 =PMT(B3/12,B4,-B5) が入力されており、この数式は代入セル B3 と B4 を使用します。

複入力テーブル

データ テーブルの計算   

ワークシートが再計算されるたびに、データに変更がない場合でも、すべてのデータテーブルについても再度計算が行われます。データテーブルを含むワークシートの計算を速くするには、計算オプションを変更して、ワークシートを自動的に再計算しますが、データテーブルは表示されません。詳細については、「データテーブルを含むワークシートの計算速度を向上させる」を参照してください。

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

次の手順を行います。

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

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

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

      単入力テーブル

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

    • データテーブルが行方向の場合 (変数の値が行内にある場合) は、セルの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 を使用します。

複入力テーブル

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

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

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

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

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

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

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

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

ゴール シーク

数式に予期される結果がわかっているが、その結果を取得するために必要な入力値を正確に把握していない場合は、目標シーク機能を使用します。入力値を調整して目的の結果を見つけるには、「ゴールシークを使用する」を参照してください。

Excel ソルバー

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

補足説明

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

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

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

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

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

×