Excelの[データ]タブの[What-If 分析]の中にある[データテーブル]を使うと、複数の計算結果を同時に一覧形式で表示できます。
様々な計算結果を比較するときに便利な機能です。
列のみや、行のみを変化させるテーブルを単入力テーブル、行も列も変化させるテーブルを複入力テーブルといいます。
[データテーブル]のポップヒントには以下のように記されています。
データテーブル
複数の入力の結果を同時に表示します。
表の作成
ここでは、パン1個の単価と売上個数が変化するとどうなるかという試算表を作成します。
あらかじめ、以下のような表を作成します。
セル[C9]からセル[G13]には、通貨の表示形式を設定します。
通貨表示のショートカットキーは、[Ctrl]+[Shift]+[4]です。
下は、[数式]タブの[ワークシート分析]にある[数式の表示]をクリックしています。
セル[C4]には、売上金額を求めるための数式[=C2*C3]を入力して、セル[B8]にはセル[C4]を参照する数式[=C4]を入力しています。
[=C4]ではなく、セル[C4]と同じ数式[=C2*C3]でも構いません。
[数式の表示]を再度クリックすると、数式は非表示になります。
注意データテーブルでは、必ず行と列の交点のセルに数式を入力します。ここでは、セル[B8]ですね。
参考分かりやすくするため、ここでは枠線を非表示にしています。
[ページレイアウト]タブの[シートのオプション]にある[枠線]から[表示]のチェックボックスをオフにしています。
範囲選択
セル[B8]からセル[G13]までを範囲選択します。
範囲選択を間違えないようにしてください。
データテーブルの作成
[データ]タブにある[What-If 分析]の中にある[データテーブル]をクリックします。
[データテーブル]ダイアログボックスが表示されます。
[行の代入セル]には、セル[C2]を指定します。
[列の代入セル]には、セル[C3]を指定します。
参考[行の代入セル]には、代入する値が行方向に入力されている場合に代入セルを設定します。
[列の代入セル]には、代入する値が列方向に入力されている場合に代入セルを指定します。
結果、以下のようになります。
データテーブル内のセルをクリックすると、以下のような数式となっています。
計算式を参照させたセルの内容を非表示
セル[B8]の数式の結果を非表示にするには、文字の色を白色に設定するか、ユーザー定義の書式設定で[;;;]と入力します。
セルの内容を非表示にする方法については、以下の記事でも解説しています。
-
セルのデータを非表示にするには(ユーザー定義で設定)
印刷するときにセルに入力された文字や数字を隠したいことってありますね。 また、空白に見せたいけど、数字を入力していなければ計算できないという ...
再計算も可能
売上個数や単価の値を変更すると、再計算されます。
単入力テーブル
列のみや、行のみを変化させる単入力テーブルも可能です。
行も列も変化させるテーブルを複入力テーブルといいます。
列のみを代入させて変化させる場合
行のみを代入させて変化させる場合
参考[What-If 分析]の中にある[ゴールシーク]と[シナリオの登録と管理]については以下の記事で解説しています。
-
ゴールシークを使って目標を達成するための値を求める(数式の逆算)
Excelのゴールシークという機能を使うと、目標値を設定して特定のセルの値を変化させて、目標達成に必要な値を求めることができます。 ここでは ...
-
[シナリオの登録と管理]で数式の結果をシミュレーション
Excelには[What-If 分析]ツールというのがあり、複数の異なる値を入力して結果をシミュレーションすることができます。 [データ]タ ...
[ゴールシーク]を使って、損益分岐点を求めることもできます。
-
損益分岐点をExcelの分析ツール[ゴールシーク]で求めてみよう
損益分岐点とは、売上と費用が等しくなるポイントのことです。 Excelには[ゴールシーク]という機能があり、ボタンをクリックするだけで、解を ...
[ソルバー]については、以下の記事で解説しています。
-
ソルバーを使用して最適値を求める
Excelには、ソルバーという機能があります。 ソルバーとは、数式の計算結果を目標値として設定して、その結果を得るために複数の制約条件を指定 ...