Excelでデータテーブルを作成する5つの簡単な方法|初心者向け完全ガイド

Excelでデータテーブルを簡単に作成する方法をお探しですか?この記事では、Excelでデータテーブルを作成する5つの方法をご紹介します。データテーブルは、数式の入力値をいろいろと試しながら、その値の変化が計算結果にどのような影響を与えるかを確認できる強力な機能です。これを活用すれば、あらゆる種類の計算やシミュレーションが格段に楽になります。

それでは、本題に入りましょう。

サンプルファイルのダウンロード

Excelでデータテーブルを作成する5つの方法

ここでは、ある人が銀行に2,000ドルを預け入れ、年利5%で5年間運用した結果、総残高が2,500ドルになったというデータセットを使って説明します。

総残高を求める数式は以下のとおりです。

総残高 = 投資額 + 投資額 × 年数 × 利率

総残高 = C3+(C3*C4*C5)

データテーブルを使えば、投資額や利率などの変数が変わったときに総残高がどう変化するのかを、簡単に確認できます。

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

ケース1:列方向(縦型)のデータテーブル
ステップ1:まず、さまざまな投資額の値を入力する列と、結果を表示するための総残高の列を作成します。
ここでは変数として投資額を使用します。次に、総残高列の最初の行を、セルC7の計算結果にリンクさせます。

ステップ2:続いて、範囲全体E4:F9を選択し、「データ」タブ >> 「予測」グループ >> 「What-If分析」>> 「データテーブル」の順にクリックします。

ステップ3:すると「データテーブル」ダイアログボックスが表示されます。列方向のデータテーブルなので、「列の代入セル」に絶対参照で入力セルC3を指定します。
最後に「OK」を押します。

ステップ4:これで、総残高列にそれぞれの投資額に対応した計算結果が表示されます。

ケース2:行方向(横型)のデータテーブル
ステップ1:今度は、さまざまな投資額の値を並べた行と、結果を表示する総残高の行を作成します。ここでも変数は投資額です。総残高行の最初の列を、セルC7の計算結果にリンクさせましょう。

ステップ2:範囲全体C9:H10を選択し、「データ」タブ >> 「予測」グループ >> 「What-If分析」>> 「データテーブル」の順にクリックします。

ステップ3:「データテーブル」ダイアログボックスが表示されたら、行方向のデータテーブルなので、今回は「行の代入セル」に絶対参照でC3を入力し、「OK」を押します。

ステップ4:すると、総残高行に各投資額に応じた結果が表示されます。

関連記事: Excelで1変数データテーブルを作成する方法

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

ステップ1:2変数データテーブルでは、行方向と列方向の両方の形式を組み合わせます。まず、B9などのセルをC6の総残高にリンクさせます。そして、B9のすぐ下と右側に、投資額と利率という2種類の変数を以下のように配置します。

ステップ2:次に、データ範囲B9:F13を選択し、方法1のステップ2と同じ操作を行います。「データテーブル」ダイアログボックスが表示されたら、「行の代入セル」と「列の代入セル」の両方に参照を入力して「OK」を押します。

ステップ3:これで、以下のように2つの変数の組み合わせごとの結果が表示されます。

関連記事: Excelで2変数データテーブルを作成する方法

方法3:データテーブルで複数の結果を比較する

前の方法では2つの変数を使いましたが、今度は逆に、2つの出力結果を比較したい場合を考えてみましょう。
まず、総残高から投資額を差し引いて得られる利息を、もうひとつの出力としてC8で計算します。
次に、投資額列の隣に「総残高」と「利息」の2つの列を作成します。

ステップ1:まず、「総残高」列と「利息」列の最初の行を、それぞれC7とC8にリンクさせます。

ステップ2:続いて、範囲E4:G9を選択し、方法1のステップ2と同じ操作を行います。「データテーブル」ダイアログボックスが表示されたら、これは列方向のデータテーブルなので「列の代入セル」に参照を入力し、「OK」を押します。

ステップ3:これで、以下のように2つの出力結果が同時に表示され、比較できるようになります。

あわせて読みたい:

• Excelデータテーブルの活用例(6つの条件)
• Excelでデータテーブルを使ってWhat-If分析を行う方法
• Excelでデータテーブルが動かないときの原因と対策(7つの問題と解決策)

方法4:Power Queryを使ったデータテーブルの作成

ステップ1:データ範囲全体を選択し、「データ」タブ>>「テーブル/範囲から」をクリックします。

ステップ2:「テーブル作成」ダイアログボックスが表示されるので、「先頭行をテーブルの見出しとして使用する」にチェックを入れます。

ステップ3:テーブルが作成されたら、総残高の最初の行に数式を入力するだけです。

ステップ4:Enterキーを押すと、総残高の残りの行にも自動的に数式が反映され、以下のような結果が表示されます。

方法5:「テーブルとして書式設定」を使ったデータテーブルの作成

ステップ1:データ範囲全体を選択し、「ホーム」タブ>>「スタイル」グループ>>「テーブルとして書式設定」の順にクリックして、好みのスタイルを選択します。

ステップ2:「テーブル作成」ダイアログボックスが表示されるので、「先頭行をテーブルの見出しとして使用する」にチェックを入れます。

ステップ3:テーブルが作成されたら、総残高の最初の行に数式を入力します。

ステップ4:Enterキーを押すと、総残高の残りの行にも自動的に数式が反映され、以下のような結果が表示されます。

まとめ

この記事では、Excelでデータテーブルを作成する最も簡単な方法を5つご紹介しました。1変数・2変数のデータテーブルから、Power Queryやテーブル書式設定を活用した方法まで、目的に合わせて使い分けてみてください。わかりにくい点があれば、お気軽にコメントください。また、他におすすめの方法をご存じの方は、ぜひ情報を共有していただければ幸いです。最後までお読みいただきありがとうございました。

さらに学びたい方へ

• Excelグラフにデータテーブルを追加する方法(4つのクイック手法)
• Excelでのシナリオ分析:2つのサンプルケース+テンプレート付きガイド