複数の変数に依存する複雑な数式があり、それらの入力の変更が結果にどのような影響を与えるかを効率的に理解したい場合、Excel の What-If 分析データ テーブルは強力なツールです。これにより、考えられるすべての結果を一目で確認できます。 Excel でデータ テーブルを作成する方法をステップごとに説明します。さらに、データ テーブルを効果的に使用するためのいくつかの重要なポイントについて説明し、データ テーブルの削除、編集、再計算などのその他の操作を示します。

Excelのデータテーブルとは何ですか?

1変数データテーブルの作成

• 列指向のデータテーブル
• 行指向のデータテーブル
• 1 変数データ テーブルの複数の式

2変数データテーブルの作成

データテーブルを使用する際のポイント

データテーブルを使用するためのその他の操作

• データテーブルを削除する
• データテーブルの編集結果
• データテーブルを手動で再計算する

Excelのデータテーブルとは何ですか?

Excel では、データ テーブルは What-If 分析ツールの 1 つであり、これを使用すると、数式のさまざまな入力値を試し、数式の出力の変化を観察できます。このツールは、特に数式が複数の変数に依存する場合、さまざまなシナリオを調査し、感度分析を実施するのに非常に役立ちます。

Excel には 2 種類のデータ テーブルがあります。

1 変数データ テーブル: これにより、1 つの変数のさまざまな値が式にどのような影響を与えるかを分析できます。入力を行全体で変更するか、列ごとに変更するかに応じて、1 変数データ テーブルを行指向または列指向の形式で設定できます。

2 変数データ テーブル: このタイプでは、2 つの異なる変数を変更した場合の数式の結果への影響を確認できます。 2 変数データ テーブルでは、行と列の両方に沿って値を変更します。

1変数データテーブルの作成

Excel で 1 変数データ テーブルを作成することは、1 つの入力の変更がさまざまな結果にどのような影響を与えるかを分析するための貴重なスキルです。このセクションでは、列指向、行指向、および複数式の 1 変数データ テーブルを作成するプロセスについて説明します。

すばらしい！ Chrome、Edge、Firefox、Safari など Excel で効率的なタブを使用する!
時間の50％を節約し、毎日何千ものマウスクリックを減らしましょう！

---

列指向のデータテーブル

列指向のデータ テーブルは、列に変数値をリストして、単一変数のさまざまな値が数式の出力にどのような影響を与えるかをテストする場合に便利です。簡単な財務例を考えてみましょう。

50,000 ドルのローンを検討し、3 年間 (36 か月に相当) で返済する予定だとします。自分の給与に基づいて手頃な月々の支払い額を評価するために、さまざまな金利が毎月支払う必要がある金額にどのような影響を与えるかを調査することに興味があります。

ステップ 1: 基本式を設定する

支払いを計算するために、ここでは利息を 5% に設定します。セル B4 に PMT 式を入力すると、金利、期間数、ローン金額に基づいて毎月の支払い額が計算されます。スクリーンショットを参照してください:

= -PMT($B$1/12, $B$2*12, $B$3)

ステップ 2: 列に金利をリストします。

列に、テストするさまざまな金利をリストします。たとえば、4% から 11% までの値を 1% ずつ列にリストし、結果の右側に少なくとも XNUMX つの空白列を残します。スクリーンショットを参照してください:

ステップ 3: データテーブルを作成する

1. セル E2 に次の数式を入力します。 = B4.
   
   Note: B4 はメインの数式が配置されているセルです。これは、金利の変化に応じて変化する結果を確認したい数式です。
   
2. 数式、金利のリスト、結果の隣接するセルを含む範囲を選択します (例: D2 から E10 を選択します)。スクリーンショットを参照してください:
   
3. リボンに移動し、 且つ タブ、[OK]をクリックします What-If分析 > データ表、スクリーンショットを参照してください：
   
4. データ表 ダイアログ ボックスで、 列入力セル ボックス (入力値に列を使用しているため) をクリックし、数式で参照される変数セルを選択します。この例では、金利を含む B1 を選択します。最後にクリックしてください OK ボタン、スクリーンショットを参照してください：
   
5. Excel は、各変数値 (さまざまな金利) に隣接する空のセルに、対応する結果を自動的に入力します。スクリーンショットを参照してください:
   
6. 必要に応じて、結果 (通貨) に目的の数値形式を適用します。これで、列指向のデータ テーブルが正常に作成されました。結果をすぐに確認して、金利が変化したときに手頃な月々の支払額を評価できるようになりました。スクリーンショットを参照してください:
   

行指向のデータテーブル

Excel で行指向のデータ テーブルを作成するには、変数値が列ではなく行全体にリストされるようにデータを配置する必要があります。上記の例を参考にして、Excel で行指向のデータ テーブルの作成を完了する手順を進めてみましょう。

ステップ 1: 金利を連続してリストする

変数値 (金利) を行に配置し、式の左側に空の列が少なくとも 1 つ、結果の空の行が下に 1 つあることを確認します。スクリーンショットを参照してください。

ステップ 2: データテーブルを作成する

1. セル A9 に次の数式を入力します。 = B4.
   
2. 数式、金利のリスト、および結果の隣接するセルを含む範囲を選択します (例: A8 から I9 を選択します)。次に、クリックします 且つ > What-If分析 > データ表.
3. データ表 ダイアログ ボックスで、 行入力セル ボックス (入力値に行を使用しているため) をクリックし、数式で参照される変数セルを選択します。この例では、金利を含む B1 を選択します。最後にクリックしてください OK ボタン、スクリーンショットを参照してください：
   
4. 必要に応じて、結果 (通貨) に目的の数値形式を適用します。これで、行指向のデータテーブルが作成されました。スクリーンショットを参照してください。
   

1 変数データ テーブルの複数の式

Excel で複数の数式を含む 1 変数データ テーブルを作成すると、1 つの入力の変更が複数の異なる数式にどのような影響を与えるかを一度に確認できます。上の例で、返済額と合計利息の両方の金利の変化を確認したい場合はどうすればよいでしょうか?設定方法は次のとおりです。

ステップ 1: 合計利息を計算するための新しい式を追加する

セル B5 に次の式を入力して、利息総額を計算します。

=B4*B2*12-B3

ステップ 2: データテーブルのソースデータを配置する

列に、テストするさまざまな金利をリストし、結果を表示するために右側に少なくとも 2 つの空白列を残します。スクリーンショットを参照してください:

ステップ 3: データテーブルを作成します。

1. セル E2 に次の数式を入力します。 = B4 元のデータに返済計算への参照を作成します。
2. セル F2 に次の数式を入力します。 = B5 元のデータの合計関心度への参照を作成します。
   
3. 数式、金利のリスト、結果の隣接するセルを含む範囲を選択します (例: D2 から F10 を選択します)。次に、クリックします 且つ > What-If分析 > データ表.
4. データ表 ダイアログ ボックスで、 列入力セル ボックス (入力値に列を使用しているため) をクリックし、数式で参照される変数セルを選択します。この例では、金利を含む B1 を選択します。最後にクリックしてください OK ボタン、スクリーンショットを参照してください：
   
5. 必要に応じて、結果 (通貨) に目的の数値形式を適用します。また、さまざまな変数値に基づいて各式の結果を確認できます。
   

2変数データテーブルの作成

Excel の 2 変数データ テーブルには、2 セットの変数値のさまざまな組み合わせが数式の結果に与える影響が表示され、数式の 2 つの入力の変更がその結果に同時にどのように影響するかを示します。

ここでは、2 変数データ テーブルの外観と構造をよりよく理解できるように、簡単なスケッチを描きました。

1 変数データ テーブルを作成する例に基づいて、Excel で 2 変数データ テーブルを作成する方法を学習してみましょう。

以下のデータセットには、金利、融資期間、融資額があり、次の式を使用して月々の支払い額を計算しています。 =-PMT($B$1/12, $B$2*12, $B$3) 同じように。ここでは、データから 2 つの主要な変数、金利とローン金額に焦点を当て、これら両方の要因の変化が同時に返済額にどのような影響を与えるかを観察します。

ステップ 1: XNUMX つの変化する変数を設定する

1. 列に、テストするさまざまな金利をリストします。スクリーンショットを参照してください:
   
2. 行の列の値のすぐ上にさまざまなローン金額の値を入力します (数式セルの 1 つ右側のセルから開始します)。スクリーンショットを参照してください。
   

ステップ 2: データテーブルを作成する

1. 変数値をリストした行と列の交点に数式を配置します。この場合、次の式を入力します。 = B4 セルE2に入力します。スクリーンショットを参照してください:
   
2. ローン金額、金利、数式セル、結果が表示されるセルを含む範囲を選択します。
   
3. 次に、をクリックします。 且つ > What-If分析 > データ表。 [データ テーブル] ダイアログ ボックスで次の操作を行います。
   • 行入力セル ボックスで、行の変数値の入力セルへのセル参照を選択します (この例では、B3 にローン金額が含まれています)。
   • 列入力セルl ボックスで、列の変数値の入力セルへのセル参照を選択します (B1 には金利が含まれます)。
   • 次に、をクリックします OK ボタン。
     
4. ここで、Excel はローン金額と金利の各組み合わせの結果をデータ テーブルに入力します。ローン金額と金利のさまざまな組み合わせが月々の支払いにどのように影響するかを直接把握できるため、財務計画と分析に役立つツールとなります。
   
5. 最後に、必要に応じて、結果 (通貨) に目的の数値形式を適用する必要があります。
   

データテーブルを使用する際のポイント

• 新しく作成したデータ テーブルは、元のデータと同じワークシート内に存在する必要があります。
• データ テーブルの出力はソース データ セット内の数式セルに依存し、この数式セルに変更を加えると出力が自動的に更新されます。
• データテーブルを使用して値を計算すると、次のコマンドを使用して値を元に戻すことはできません。 Ctrlキー+ Z。ただし、すべての値を手動で選択して削除することができます。
• データ テーブルは配列数式を生成するため、テーブル内の個々のセルを変更したり削除したりすることはできません。

データテーブルを使用するためのその他の操作

データ テーブルを作成したら、データ テーブルを効果的に管理するために、データ テーブルの削除、結果の変更、手動再計算の実行などの追加の操作が必要になる場合があります。

データテーブルを削除する

データ テーブルの結果は配列数式を使用して計算されるため、データ テーブルから個々の値を削除することはできません。ただし、削除できるのはデータテーブル全体のみです。

データ テーブルのすべてのセルを選択するか、結果が含まれるセルのみを選択して、 を押します。 削除 キーボードのキー。

データテーブルの編集結果

実際、データ テーブル内の個々のセルを直接編集することはできません。これらのセルには Excel が自動的に生成する配列数式が含まれているためです。

変更を加えるには、通常、既存のデータ テーブルを削除し、必要な変更を加えて新しいデータ テーブルを作成する必要があります。これには、基本式または入力値を調整し、これらの変更を反映するためにデータ テーブルを再度設定することが含まれます。

データテーブルを手動で再計算する

通常、Excel では、変更が加えられるたびに、開いているすべてのブック内のすべての数式が再計算されます。多数の変数値と複雑な数式を含む大規模なデータ テーブルが Excel ワークブックの速度を低下させる原因となっている場合。

ブックに変更が加えられるたびに Excel がすべてのデータ テーブルに対して計算を自動的に実行しないようにするには、計算モードを自動から手動に切り替えることができます。次のようにしてください。

に行きます 式 タブ、[OK]をクリックします 計算オプション > データテーブルを除いて自動、スクリーンショットを参照してください：

この設定を変更すると、ブック全体を再計算するときに、Excel はデータ テーブル内の計算を自動的に更新しなくなります。

データテーブルを手動で更新する必要がある場合は、結果が表示されているセル (TABLE() 数式を含むセル) を選択し、 を押すだけです。 F9 キー。

このガイドで概説されている手順に従い、重要なポイントを念頭に置くことで、データ分析のニーズに合わせてデータ テーブルを効率的に利用できます。 Excel のヒントやテクニックをさらに詳しく調べることに興味がある場合は、当社の Web サイトで何千ものチュートリアルが提供されています。 ここをクリックしてアクセスしてください。 読んでいただきありがとうございます。今後さらに役立つ情報を提供できることを楽しみにしています。