Excel で複数の結果を同時に表示 | What-If 分析：データテーブルの利用
Excel の What-If 分析の中にある「データテーブル」を使うと、１つの計算式に含まれる１つまたは２つの変数の値を変化させて、計算結果を表の形で同時に表示することができます。
例えば、販売単価を 10～25 円の 5 円刻み、売上個数を 100～300 個の 100 個刻みで変化させて、両者の組み合わせで計算した売上金額を一覧表として表示することができます。
通常の数式を使っても同様の表は作成できますが、データテーブルを使うとより簡単に作成できます。さらに、販売単価を（原価÷原価率）に分解して、３個以上の変数によるシミュレーションを簡単に行えます。
ここでは、３種類のデータテーブルを「２変数データテーブル」、「１変数データテーブル」、「無変数データテーブル」と呼んで、これらの作成方法と利用方法を説明します。
２変数データテーブルは、２変数を１行と１列にそれぞれ割り当てて作成したデータテーブルです。１つの計算式を使います。
１変数データテーブルは、１変数を１行または１列のいずれかに割り当てて作成したデータテーブルです。複数の計算式が使えます。
無変数データテーブルは、ここだけの仮の名称です。１変数データテーブルの特殊な使い方で、変数を設定せずに乱数の関数と組み合わせて使います。
なお、この投稿は以下を利用して作成しました。
Windows 10
サブスクリプションの Microsoft 365 の Excel 2016
２変数データテーブル
２変数データテーブルは、２変数を１行と１列にそれぞれ割り当てて作成したデータテーブルです。１つの計算式を使います。
変数と計算式
変数を入力した２つ以上のセルを用意します。ここでは、「原価」、「原価率」、「個数」の３変数の事例で説明します（表示１-(1)）。この中から２変数を選び、「原価」のセル D3 を「行の代入セル」、「個数」のセル D5 を「列の代入セル」とします。
これらの変数を含む「利益」の計算式を D7 のセルに入力して、このセルを「計算式のセル」とします（表示１-(2)）。
なお、見やすいように、表示１にセルの背景色や罫線などの装飾を加えてあります。以下、同様です。
表示１ ２変数データテーブルの作成
データテーブルの作成
データテーブルの基になる表を用意します（表示１-(3)）。表頭の 11 行に「原価」の値、表側の C 列に「個数」の値を設定します。
表頭と表側の交差するセル C11 に「=D7」を入力して、上記で設定した「計算式のセル」を参照します（表示１-(3)）。あるいは、計算式「=(D3 / D4 – D3) * D5」をセル C11 に直接入力するか、「計算式のセル」からコピーします。コピーする場合、「計算式のセル」を F4 キーで相対参照から絶対参照「=($D$3 / $D$4 – $D$3) * $D$5」に変換した後に、セル C11 に貼り付けます。
表頭と表側を含む矩形のセル範囲 C11:G14 を範囲指定します（表示１-(3)）。［データ］＞［予測］＞［What-If 分析］＞［データテーブル］の順に選択します。ダイアログボックスに、上記で設定した［行の代入セル］の番地 D3 と、［列の代入セル］の番地 D5 を指定して［OK］をクリックします。
表頭の値と表側の値から計算した結果が、表に出力されます（表示2-(3)）。
なお、「原価」と「個数」の項目名を追加してあります。
表示２ ２変量データテーブルによる分析
分析
変数の「原価率」のセル D4 を 0.3 から 0.4 に変更すると、データテーブルが再計算されます（表示2）。
データテーブルの表頭の原価の数値と、表側の個数の数値を変更すると再計算されます。
原価 16 円、個数 300 個の利益は 11,200 円です（表示2-(3)）。これを 15,000 円にするためには、原価率をどのくらいに設定すればいいのか、ゴールシークを利用して求められます。［データ］＞［予測］＞［What-If 分析］＞［ゴールシーク］の順に選択し、ダイアログボックスの［数式入力セル］に G14、［目標値］に 15000 、［変化させるセル］に D4 を指定すると、原価率 0.242 が得られます。この原価率で全体が再計算されます。
データテーブルを基にグラフを描くと、視覚的な分析が行えます。データテーブルが再計算されると、グラフに反映されます。
これらの分析は、次の１変数データテーブルでも同様に行えます。
１変数データテーブル
１変数データテーブルは、１変数を１行または１列のいずれかに割り当てて、これを変化させたデータテーブルです。複数の計算式が使えます。
作成の方法は、上記の２変数データテーブルの場合とほぼ同じです。
１列に変数を割り当てた場合
変数を入力した３つのセルの中から、「個数」のセル D5 を「列の代入セル」とします（表示３-(1)）。
これらの変数を含む「販売単価」「売上高」「売上原価」「利益」の計算式を、セル D7、D8、D9、D10 に入力します（表示３(2)）。これらを「計算式のセル」とします。
データテーブルの基になる表を用意します（表示３-(3)）。表側の C 列に「個数」の値を設定し、表頭の 14 行で「計算式のセル」D7、D8、D9、D10 を参照します。あるいは、計算式を直接入力するか、「計算式のセル」から絶対参照に変換してコピーします。
なお、表示１と表示３に示したように、「計算式のセル」を参照するセルの位置が、２変量データテーブルと１変量データテーブルでは異なるので、注意してください。
表頭と表側を含む矩形のセル範囲 C14:G17 を範囲指定します（表示３-(3)）。［データ］＞［予測］＞［What-If 分析］＞［データテーブル］の順に選択します。ダイアログボックスに、［列の代入セル］の番地 D5 を指定します。［行の代入セル］は空白のまま［OK］をクリックします。
表示３ １変数データテーブルの作成
表頭の計算式と表側の値から計算した結果が、表に出力されます（表示４-(3)）。
１行に変数を割り当てた場合
上記と同様の方法で、表頭の 21 行に「原価」の値を設定し、表側の C 列で「計算式のセル」D7、D8、D9、D10 を参照します。データテーブルのダイアログボックスの［行の代入セル］に「原価」のセル番地 D3 を指定すると、表頭に「原価」を割り当てたデータテーブルが得られます（表示4-(4)）。
ただし、比較する数値を縦に並べるのが表の基本です。この場合は、表側に「原価」を割り当てたデータテーブルを作成します（表示4-(5)）。
表示４ １変数データテーブルの種類
無変数データテーブル
ここだけの仮の名称です。１変数データテーブルの特殊な使い方で、変数を設定しないデータテーブルです。
本サイトで紹介しているグリーン本* では、乱数を生成する関数（RAND など）と組み合わせて、シミュレーションの一手法として利用しています。
ここでは、10 個と 20 個の正規乱数からそれぞれ平均と標準偏差を計算し、どのくらい母平均と母標準偏差に近いかを比較する事例で説明します（表示５）。
平均（x-bar）16.0、標準偏差（SD）1.5 の正規乱数を 20 個、セル範囲 C3:F7 に生成します（表示５-(1)）。
このうちの 10 個（セル範囲 C3:D7）の平均と標準偏差を求める計算式を、セル E10 とE12 に入力します（表示５-(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］をクリックします。
表示５ 無変数データテーブルの作成
乱数の発生と再計算が１行ごとに繰り返し行われ、10 行分の結果が出力されます（表示6(3)）。
RAND 関数は、ワークシートが再計算されるたびに、新しい乱数に更新されます。しかし、データテーブル本体の値は再計算されません。これはデータテーブルの良い点です。
表示６ 無変数データテーブルの利用
応用事例
２変数データテーブルの利用
サンプルから得られた平均値が、ある仮設値と有意に異なるか検定するには、母平均と仮設値との差の z 検定を行います（母標準偏差が既知の場合）。
この検定において、サンプルサイズ n と母平均と仮設値との差から計算される検出力を、データテーブルで計算している事例が、グリーン本*の第１部「§2.4 平均に関する推測（母標準偏差既知）」で取り上げられています（表示７）。
表示７ サンプルサイズと母平均による検出力の変化
無変数データテーブルの利用
単回帰分析において、パラメータの推定精度とサンプルサイズの大きさの関係をシミュレーションする事例が、グリーン本* の第１部「§4.4 誤差を考慮した推定」で取り上げられています（表示８）。
y は母集団のパラメータ α、β に従い、さらに RAND 関数を利用した標準偏差 σ の誤差が加わっています。この x と y が 14 組あり、ここから n=14 と n=8 のサンプルを取り出して、切片 a と傾き b を推定します。
無変数データテーブルを利用することにより、１行ごとに乱数の発生と再計算が繰り返し行われ、切片と傾きの推定値が得られます。ここには 10 行の出力が得られています。同様にして 100 行、200 行の出力が容易に得られます。
表示８ 単回帰分析におけるパラメータ精度のシミュレーション
参考事項
大きな規模のデータテーブルや多数のデータテーブルがシートにある場合、Excel の計算速度が遅くなります。これは、テーブル内の要素が変更されるたびにデータテーブル全体が再計算されるからです。
この場合、データテーブルの再計算を停止させるため、［数式］＞［計算方法］＞［計算方法の設定］の順に選択し、[自動]から［データテーブル以外自動］に変更します。この状態でデータテーブルを手動で再計算するには F9 キーを押します。
データテーブルの構造は固定されています。データテーブルに行や列の挿入・削除を行おうとすると、エラーメッセージが表示されます。このような場合は、作成し直す必要があります。
ただし、１変数データテーブルの場合、計算式の追加は可能です。
データテーブルの中で、計算式を参照しているセル（計算式が入力されているセル）の表示は不要です。これを削除することはできませんが、文字を背景色と同じにして非表示にすることができます。あるいは、［フォント］＞［表示形式］＞［ユーザー定義］の順に選択し、「;;」（セミコロン２個）を指定して、表示形式を変更します。
内容の正確を期すように最大限努力しましたが、これらの内容に関しては自己責任で利用してください。
*グリーン本：芳賀敏郎「医薬品開発のための統計解析」第１部、第２部、第３部（サイエンティスト社）
（2023年5月29日）
