Excel で複数の結果を同時に表示 | What-If 分析:データテーブルの利用

Excel の What-If 分析の中にある「データテーブル」を使うと、1つの計算式に含まれる1つまたは2つの変数の値を変化させて、計算結果を表の形で同時に表示することができます。

例えば、販売単価を 10~25 円の 5 円刻み、売上個数を 100~300 個の 100 個刻みで変化させて、両者の組み合わせで計算した売上金額を一覧表として表示することができます。
通常の数式を使っても同様の表は作成できますが、データテーブルを使うとより簡単に作成できます。さらに、販売単価を(原価÷原価率)に分解して、3個以上の変数によるシミュレーションを簡単に行えます。

ここでは、3種類のデータテーブルを「2変数データテーブル」、「1変数データテーブル」、「無変数データテーブル」と呼んで、これらの作成方法と利用方法を説明します。

2変数データテーブルは、2変数を1行と1列にそれぞれ割り当てて作成したデータテーブルです。1つの計算式を使います。
1変数データテーブルは、1変数を1行または1列のいずれかに割り当てて作成したデータテーブルです。複数の計算式が使えます。
無変数データテーブルは、ここだけの仮の名称です。1変数データテーブルの特殊な使い方で、変数を設定せずに乱数の関数と組み合わせて使います。

なお、この投稿は以下を利用して作成しました。
Windows 10
サブスクリプションの Microsoft 365 の Excel 2016

2変数データテーブル

2変数データテーブルは、2変数を1行と1列にそれぞれ割り当てて作成したデータテーブルです。1つの計算式を使います。

変数と計算式

変数を入力した2つ以上のセルを用意します。ここでは、「原価」、「原価率」、「個数」の3変数の事例で説明します(表示1-(1))。この中から2変数を選び、「原価」のセル D3 を「行の代入セル」、「個数」のセル D5 を「列の代入セル」とします。

これらの変数を含む「利益」の計算式を D7 のセルに入力して、このセルを「計算式のセル」とします(表示1-(2))。

なお、見やすいように、表示1にセルの背景色や罫線などの装飾を加えてあります。以下、同様です。

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

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

データテーブルの作成

データテーブルの基になる表を用意します(表示1-(3))。表頭の 11 行に「原価」の値、表側の C 列に「個数」の値を設定します。

表頭と表側の交差するセル C11 に「=D7」を入力して、上記で設定した「計算式のセル」を参照します(表示1-(3))。あるいは、計算式「=(D3 / D4 – D3) * D5」をセル C11 に直接入力するか、「計算式のセル」からコピーします。コピーする場合、「計算式のセル」を F4 キーで相対参照から絶対参照「=($D$3 / $D$4 – $D$3) * $D$5」に変換した後に、セル C11 に貼り付けます。

表頭と表側を含む矩形のセル範囲 C11:G14 を範囲指定します(表示1-(3))。[データ]>[予測]>[What-If 分析]>[データテーブル]の順に選択します。ダイアログボックスに、上記で設定した[行の代入セル]の番地 D3 と、[列の代入セル]の番地 D5 を指定して[OK]をクリックします。
表頭の値と表側の値から計算した結果が、表に出力されます(表示2-(3))。
なお、「原価」と「個数」の項目名を追加してあります。

表示2 2変量データテーブルによる分析

表示2 2変量データテーブルによる分析

分析

変数の「原価率」のセル D4 を 0.3 から 0.4 に変更すると、データテーブルが再計算されます(表示2)。
データテーブルの表頭の原価の数値と、表側の個数の数値を変更すると再計算されます。

原価 16 円、個数 300 個の利益は 11,200 円です(表示2-(3))。これを 15,000 円にするためには、原価率をどのくらいに設定すればいいのか、ゴールシークを利用して求められます。[データ]>[予測]>[What-If 分析]>[ゴールシーク]の順に選択し、ダイアログボックスの[数式入力セル]に G14、[目標値]に 15000 、[変化させるセル]に D4 を指定すると、原価率 0.242 が得られます。この原価率で全体が再計算されます。

データテーブルを基にグラフを描くと、視覚的な分析が行えます。データテーブルが再計算されると、グラフに反映されます。

これらの分析は、次の1変数データテーブルでも同様に行えます。

1変数データテーブル

1変数データテーブルは、1変数を1行または1列のいずれかに割り当てて、これを変化させたデータテーブルです。複数の計算式が使えます。

作成の方法は、上記の2変数データテーブルの場合とほぼ同じです。

1列に変数を割り当てた場合

変数を入力した3つのセルの中から、「個数」のセル D5 を「列の代入セル」とします(表示3-(1))。
これらの変数を含む「販売単価」「売上高」「売上原価」「利益」の計算式を、セル D7、D8、D9、D10 に入力します(表示3(2))。これらを「計算式のセル」とします。

データテーブルの基になる表を用意します(表示3-(3))。表側の C 列に「個数」の値を設定し、表頭の 14 行で「計算式のセル」D7、D8、D9、D10 を参照します。あるいは、計算式を直接入力するか、「計算式のセル」から絶対参照に変換してコピーします。

なお、表示1と表示3に示したように、「計算式のセル」を参照するセルの位置が、2変量データテーブルと1変量データテーブルでは異なるので、注意してください。

表頭と表側を含む矩形のセル範囲 C14:G17 を範囲指定します(表示3-(3))。[データ]>[予測]>[What-If 分析]>[データテーブル]の順に選択します。ダイアログボックスに、[列の代入セル]の番地 D5 を指定します。[行の代入セル]は空白のまま[OK]をクリックします。

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

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

表頭の計算式と表側の値から計算した結果が、表に出力されます(表示4-(3))。

1行に変数を割り当てた場合

上記と同様の方法で、表頭の 21 行に「原価」の値を設定し、表側の C 列で「計算式のセル」D7、D8、D9、D10 を参照します。データテーブルのダイアログボックスの[行の代入セル]に「原価」のセル番地 D3 を指定すると、表頭に「原価」を割り当てたデータテーブルが得られます(表示4-(4))。

ただし、比較する数値を縦に並べるのが表の基本です。この場合は、表側に「原価」を割り当てたデータテーブルを作成します(表示4-(5))。

表示4 1変数データテーブルの種類

表示4 1変数データテーブルの種類

無変数データテーブル

ここだけの仮の名称です。1変数データテーブルの特殊な使い方で、変数を設定しないデータテーブルです。
本サイトで紹介しているグリーン本* では、乱数を生成する関数(RAND など)と組み合わせて、シミュレーションの一手法として利用しています。

ここでは、10 個と 20 個の正規乱数からそれぞれ平均と標準偏差を計算し、どのくらい母平均と母標準偏差に近いかを比較する事例で説明します(表示5)。
平均(x-bar)16.0、標準偏差(SD)1.5 の正規乱数を 20 個、セル範囲 C3:F7 に生成します(表示5-(1))。
このうちの 10 個(セル範囲 C3:D7)の平均と標準偏差を求める計算式を、セル E10 とE12 に入力します(表示5-(2))。同様に、20 個(セル範囲 C3:F7)の平均と標準偏差を求める計算式を、セル E11 と E13 に入力します。これらを「計算式のセル」とします。

データテーブルの基になる表を用意します(表示5-(3))。本来、表側の C 列に変数を入力しますが、空白のままにします。表頭の 17 行で「計算式のセル」E10、E11、E12、E13 を参照します。あるいは、計算式を直接入力するか、絶対参照に変換して「計算式のセル」からコピーします。

表頭と空白の表側を含む矩形のセル範囲 C17:G27 を範囲指定します(表示5-(3))。空白の表側 C17:C27 を含めることに注意してください。
ここでは、10 行分の出力を得るために 27 行まで範囲指定しました。20 行分の出力を得るには 37 行まで範囲指定します。つまり、必要な行数を確保するように範囲を選択します。
[データ]>[予測]>[What-If 分析]>[データテーブル]の順に選択します。ダイアログボックスで、[列の代入セル]に該当するセルは存在しませんが、ダミーとして適当な空白セルを指定します。[行の代入セル]は空白のままで[OK]をクリックします。

表示5 無変数データテーブルの作成

表示5 無変数データテーブルの作成

乱数の発生と再計算が1行ごとに繰り返し行われ、10 行分の結果が出力されます(表示6(3))。

RAND 関数は、ワークシートが再計算されるたびに、新しい乱数に更新されます。しかし、データテーブル本体の値は再計算されません。これはデータテーブルの良い点です。

表示6 無変数データテーブルの利用

表示6 無変数データテーブルの利用

応用事例

2変数データテーブルの利用

サンプルから得られた平均値が、ある仮設値と有意に異なるか検定するには、母平均と仮設値との差の z 検定を行います(母標準偏差が既知の場合)。
この検定において、サンプルサイズ n と母平均と仮設値との差から計算される検出力を、データテーブルで計算している事例が、グリーン本*の第1部「§2.4 平均に関する推測(母標準偏差既知)」で取り上げられています(表示7)。

表示7 サンプルサイズと母平均による検出力の変化

表示7 サンプルサイズと母平均による検出力の変化

無変数データテーブルの利用

単回帰分析において、パラメータの推定精度とサンプルサイズの大きさの関係をシミュレーションする事例が、グリーン本* の第1部「§4.4 誤差を考慮した推定」で取り上げられています(表示8)。

y は母集団のパラメータ αβ に従い、さらに RAND 関数を利用した標準偏差 σ の誤差が加わっています。この x と y が 14 組あり、ここから n=14 と n=8 のサンプルを取り出して、切片 a と傾き b を推定します。
無変数データテーブルを利用することにより、1行ごとに乱数の発生と再計算が繰り返し行われ、切片と傾きの推定値が得られます。ここには 10 行の出力が得られています。同様にして 100 行、200 行の出力が容易に得られます。

表示8 単回帰分析におけるパラメータ精度のシミュレーション

表示8 単回帰分析におけるパラメータ精度のシミュレーション

参考事項

大きな規模のデータテーブルや多数のデータテーブルがシートにある場合、Excel の計算速度が遅くなります。これは、テーブル内の要素が変更されるたびにデータテーブル全体が再計算されるからです。
この場合、データテーブルの再計算を停止させるため、[数式]>[計算方法]>[計算方法の設定]の順に選択し、[自動]から[データテーブル以外自動]に変更します。この状態でデータテーブルを手動で再計算するには F9 キーを押します。

データテーブルの構造は固定されています。データテーブルに行や列の挿入・削除を行おうとすると、エラーメッセージが表示されます。このような場合は、作成し直す必要があります。
ただし、1変数データテーブルの場合、計算式の追加は可能です。

データテーブルの中で、計算式を参照しているセル(計算式が入力されているセル)の表示は不要です。これを削除することはできませんが、文字を背景色と同じにして非表示にすることができます。あるいは、[フォント]>[表示形式]>[ユーザー定義]の順に選択し、「;;」(セミコロン2個)を指定して、表示形式を変更します。

内容の正確を期すように最大限努力しましたが、これらの内容に関しては自己責任で利用してください。
*グリーン本:芳賀敏郎「医薬品開発のための統計解析」第1部、第2部、第3部(サイエンティスト社)
(2023年5月29日)

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です