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

Excel for Microsoft 365 Excel for the web Excel 2021 Excel 2019 Excel 2016 Excel 2013 その他…表示数を減らす

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

新しいバージョン Begin >>>

概要

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 個ですが、作成できるシナリオの数には限りがありません。

詳しくは、「 target=”_blank” title=”What-If 分析の概要」をご覧ください” rel=”noopener”>What-If 分析の概要」をご覧ください。

データテーブルの基本

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

単入力テーブル

1 つ以上の数式の 1 つの変数の値を変更したときに数式の結果がどのように変化するかを調べるには、単入力テーブルを使います。たとえば、単入力テーブルで target=”_blank” title=”PMT 関数” rel=”noopener”>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 キーを押します。

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

[ ファイル > オプション] > [数式] をクリックします。
[ 計算オプション ] セクションの [ 計算] で、[ データテーブルを除く自動] をクリックします。

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

新しいバージョン End <<<

Web Begin >>>

異なる数値をセルに差し込むことで、問題に対するさまざまな答えをすぐに思い付くことができます。良い例として、さまざまな金利とローン期間 (月単位) の target=”_blank” title=”PMT” rel=”noopener”>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 ドルの支払いを計算します。

Web End <<<

補足説明

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

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