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

データ テーブルは、What-If 分析ツールと呼ばれているコマンド群の一部です。 データ テーブルを使用すると、What-If 分析が行われます。 What-If 分析では、セルの値を変更し、その変更により、シート上の数式の結果がどのような影響を受けるのか確認します。 たとえば、データ テーブルを使用してローンの金利や期間を変更し、可能な毎月の支払額を決定できます。

Excel には、シナリオ、データ テーブル、ゴール シークの 3 種類の what-if 分析ツールがあります。 シナリオとデータ テーブルでは、一連の入力値を指定してその結果を確認します。 ゴール シークは、結果を取得してその結果を得る可能性のある入力値を決定するという点で、シナリオおよびデータ テーブルと異なります。 シナリオと同様に、データ テーブルは一連の可能な結果を調べるのに役立ちます。 シナリオとは異なり、データ テーブルには、1 つのシート上の 1 つのテーブル内のすべての結果が表示されます。 データ テーブルを使用すると、さまざまな可能性をひとめで簡単に確認できます。 1 つまたは 2 つの変数に焦点を絞るため、結果を表形式で簡単に読み取って共有できます。

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

データ テーブルの基本

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

詳細については、「PMT 関数」を参照してください。

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

単入力テーブル

[入力] セルを します。

Excel が入力セル B3 で置き換える値の一覧。

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

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

複入力データ テーブル

列入力セル 。

行入力セル B4 で Excel が置き換える値の一覧。

[行入力] セル 。

列入力セル B3 で Excel が置き換える値の一覧。

データ テーブルの内容が変更されていない場合でも、シートが再計算されるとデータ テーブルも再計算されます。 データ テーブルを含むワークシートの計算を速くするには、データ テーブルではなく、シートを自動的に再計算するように [計算方法] オプションを変更します。

単入力テーブルでは、代入値を縦 1 列 (列方向) または横 1 行 (行方向) に入力します。 単入力テーブルで使用される数式は、1 つの代入セルだけを参照している必要があります。

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

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

データ テーブルの方向	操作
列方向 (変数値が列に入っている)	値の列の右上のセル (最初の値の 1 行上、1 列右の位置にあるセル) に数式を入力します。 概要セクションの単入力データ テーブルの図では、列方向のデータ テーブルを確認できます。数式はセル D2 に含まれています。 注: 他の数式でさまざまな値を試し、その変化を調べるには、最初の数式の右のセルに追加数式を入力します。
行方向 (変数値が行に入っている)	値の行の左下のセル (最初の値の 1 行下、1 列左の位置にあるセル) に数式を入力します。 注: 他の数式でさまざまな値を試し、その変化を調べるには、最初の数式の下のセルに追加数式を入力します。

1. 代入する数式と値を含むセル範囲を選択します。 前の概要セクションの最初の図では、この範囲は C2:D5 です。

2. Excel 2016 for Mac の場合:[データ]、[What-If 分析]、[データ テーブル] の順にクリックします。

   

   Excel for Mac 2011 の場合:[データ] タブの [分析] で [What-If] をクリックし、[データ テーブル] をクリックします。

   

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

データ テーブルの方向	操作
列方向	[列の代入セル] ボックスに代入セルのセル参照を入力します。 最初の図の例では、代入セルは B3 です。
行方向	[行の代入セル] ボックスに代入セルのセル参照を入力します。

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

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

データ テーブルの方向	操作
列方向 (変数値が列に入っている)	データ テーブルの一番上の行の数式の右にある空のセルに新しい数式を入力します。
行方向 (変数値が行に入っている)	データ テーブルの最初の列の数式の下にある空のセルに新しい数式を入力します。

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

2. Excel 2016 for Mac の場合:[データ]、[What-If 分析]、[データ テーブル] の順にクリックします。

   

   Excel for Mac 2011 の場合:[データ] タブの [分析] で [What-If] をクリックし、[データ テーブル] をクリックします。

   

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

データ テーブルの方向	操作
列方向	[列の代入セル] ボックスに代入セルのセル参照を入力します。
行方向	[行の代入セル] ボックスに代入セルのセル参照を入力します。

複入力テーブルでは、2 つの代入値のリストを含む数式を使用します。 数式は 2 つの異なる代入セルを参照している必要があります。

1. シート上のセルに、2 つの入力セルを参照する数式を入力します。 次の例では、セル B3、B4、B5 に数式の開始値を入力し、セル C2 に数式 =PMT(B3/12,B4,-B5) を入力します。

2. 数式の下に、縦 1 列に 1 つ目の代入値のリストを入力します。 この場合、セル C3、C4、C5 に異なる金利を入力します。

3. 数式の右に、横 1 行に 2 つ目の代入値のリストを入力します。 セル D2 と E2 にローン期間を月単位で入力します。

4. 数式 (C2)、値の行と列の両方 (C3:C5 と D2:E2)、計算した値を表示するセル (D3:E5) を含むセル範囲を選択します。 この場合は、範囲 C2:E5 を選択します。

5. Excel 2016 for Mac の場合:[データ]、[What-If 分析]、[データ テーブル] の順にクリックします。

   

   Excel for Mac 2011 の場合:[データ] タブの [分析] で [What-If] をクリックし、[データ テーブル] をクリックします。

   

6. [行の代入セル] ボックスに、行の値が代入される代入セルの参照を入力します。 [行の代入セル] ボックスに「B4」と入力します。

7. [列の代入セル] ボックスに、列の値が代入される代入セルの参照を入力します。 [列の代入セル] ボックスに「B3」と入力します。

2 変数のデータ テーブルは、金利とローン条件の異なる組み合わせが、毎月の住宅ローンの支払いに与える影響を示します。 次の図では、セル C2 に支払い式 =PMT(B3/12,B4,-B5) が含まれています。これは、B3 と B4 の 2 つの入力セルを使用します。

列入力セル 。

行入力セル B4 で Excel が置き換える値の一覧。

[行入力] セル 。

列入力セル B3 で Excel が置き換える値の一覧。

1. [Excel] メニューで [環境設定] をクリックします。

2. [数式とリスト] セクションで、[計算] をクリックし、[テーブル以外自動] をクリックします。