データテーブルを使って複数の計算結果を同時に表示する

Excelの［データ］タブの［What-If 分析］の中にある［データテーブル］を使うと、複数の計算結果を同時に一覧形式で表示できます。

様々な計算結果を比較するときに便利な機能です。

列のみや、行のみを変化させるテーブルを単入力テーブル、行も列も変化させるテーブルを複入力テーブルといいます。

［データテーブル］のポップヒントには以下のように記されています。

表の作成

ここでは、パン1個の単価と売上個数が変化するとどうなるかという試算表を作成します。

あらかじめ、以下のような表を作成します。

セル［C9］からセル［G13］には、通貨の表示形式を設定します。

通貨表示のショートカットキーは、［Ctrl］+［Shift］+［４］です。

下は、［数式］タブの［ワークシート分析］にある［数式の表示］をクリックしています。

セル［C4］には、売上金額を求めるための数式［=C2*C3］を入力して、セル［B8］にはセル［C4］を参照する数式［=C4］を入力しています。

［=C4］ではなく、セル［C4］と同じ数式［=C2*C3］でも構いません。

［数式の表示］を再度クリックすると、数式は非表示になります。

注意データテーブルでは、必ず行と列の交点のセルに数式を入力します。ここでは、セル［B8］ですね。

参考分かりやすくするため、ここでは枠線を非表示にしています。

［ページレイアウト］タブの［シートのオプション］にある［枠線］から［表示］のチェックボックスをオフにしています。

範囲選択

セル［B8］からセル［G13］までを範囲選択します。

範囲選択を間違えないようにしてください。

データテーブルの作成

［データ］タブにある［What-If 分析］の中にある［データテーブル］をクリックします。

［データテーブル］ダイアログボックスが表示されます。

［行の代入セル］には、セル［C2］を指定します。

［列の代入セル］には、セル［C3］を指定します。

参考［行の代入セル］には、代入する値が行方向に入力されている場合に代入セルを設定します。

［列の代入セル］には、代入する値が列方向に入力されている場合に代入セルを指定します。

結果、以下のようになります。

データテーブル内のセルをクリックすると、以下のような数式となっています。

計算式を参照させたセルの内容を非表示

セル［B8］の数式の結果を非表示にするには、文字の色を白色に設定するか、ユーザー定義の書式設定で［;;;］と入力します。

セルの内容を非表示にする方法については、以下の記事でも解説しています。

再計算も可能

売上個数や単価の値を変更すると、再計算されます。

単入力テーブル

列のみや、行のみを変化させる単入力テーブルも可能です。

行も列も変化させるテーブルを複入力テーブルといいます。

列のみを代入させて変化させる場合

行のみを代入させて変化させる場合

参考［What-If 分析］の中にある［ゴールシーク］と［シナリオの登録と管理］については以下の記事で解説しています。

［ゴールシーク］を使って、損益分岐点を求めることもできます。

［ソルバー］については、以下の記事で解説しています。