Excel の What-If 分析ツールに含まれるデータ テーブルについて、研修でご紹介する機会があったので、こちらにも残しておきます。
 
 まずは簡単な例を。
 セル B1 の値 (単価) とセル B2 の値 (数量) を乗算して、セル B3 に結果 (金額) を表示するとしたらこんな感じ。
 A01.png
 
 数量や単価の値を変えれば金額が変わるのは当たり前。
 
 数量が 110 だったら？120 だったら？150 だったら？ 単価を変えたら？というのが知りたかったら、それぞれのセルの値を変更して、金額を確認します。
 A02.png
 
 これが料金表みたいに一覧になっていたらよいな、と思ったら、候補となる単価と数量を一覧で用意し、組み合わせの結果が交差したセルに表示されるようにします。
 A03.png
 
 数式をコピーしたらできあがり。
 A04.png
 
 この表の数式の部分を作ってくれるのがデータ テーブルです。
 同じ表をデータ テーブルで作るとしたらこんな感じ、というのを「データ テーブルを使うための準備」と「データ テーブルの利用」に記載します。
  

データ テーブルを使うための準備


 データ テーブルを使うために、決めておくことや準備しておくものがあります。
 
 まずは自分がどんな計算をしたいのか、そのときにどの値を変化させたいのかを決めないといけません。
 今回の例だと、単価 (セル B1) と数量 (セル B2) の値を使って掛け算したい。
 すなわち、変化させたいのはセル B1 とセル B2 だ、と決めます。
 この 2 つのセルは、いろいろな値を代入して変化させたいセルなので、「代入セル」といいます。
 
 代入セルの値を使ってどんな計算をしたいのか、確認用の数式を作って動作を確認します。
 A05.png
 
 次に、計算に使う値を列や行にまとめた一覧を準備します。
 今回は、単価のセルに代入したい値を行方向に、数量のセルに代入したい値を列方向に並べた一覧を作っています。
 
 うすい黄色のセルはあとで数式を作る予定があるのであけておきます。
 A06.png
 
 準備の最後に、基準となる数式を決められた位置に作成します。(それがどこなのかはのちほど)
 
 今回の場合は金額を求めるときにセル B3 に作った確認用の数式 (=B1*B2) をセル A7 にコピーすればよいです。
 相対参照の場合はセルのコピーはだめですよ。数式だけコピーしてくださいね。
 A21.png
 
 これで準備ができました。

データ テーブルの利用

  1. 値の一覧と数式を準備したセル範囲 (数式のセルから表の右下) を選択して、リボンの [データ] タブの [予測] グループの [What-IF 分析] をクリックし、[データ テーブル] をクリックします。
     A08.png
      
  2. [データ テーブル] ダイアログ ボックスの [行の代入セル] と [列の代入セル] に数式で参照しているセルを指定します。
     
     今回の場合は、行方向 (横) に並べた値が単価。単価はセル B1 に入るべき値なので [行の代入セル] に指定します。
     列方向 (縦) に並べた値が数量。数量はセル B2 に入るべき値なので [列の代入セル] に指定して [OK] をクリックします。
     クリックでセルを指定すると自動的に絶対参照になります。手入力する場合は絶対参照で入力してください。
     A09.png
      
  3. データ テーブルによって数式が作成され、結果が表示されます。
     A10.png

ここまできたら、もともとどんな計算をしたいのか？を確認用に作った金額の数式は削除してしまってよいし、表の左上に作成した基準となる数式は見えないほうが美しいということなら、表示形式 (;;;) で非表示にしたらよいですね。
 A11.png
 
 なお、データ テーブルで作成された数式は一部のセルだけを編集することができません。もし消してやり直したいのなら、「=TABLE(・・・)」の数式が作成されている部分 (データ テーブルで作られた部分) のセルをすべて選択してクリアしてください。

データ テーブルの種類


 データ テーブルは必ず行と列の 2 つの値を使わなければならない、ということではありません。
 行方向のみ、または列方向のみに値の一覧を用意して代入することもでき、このようなテーブルを「単入力テーブル」といいます。
 
 ■行方向のみの単入力テーブルの例
 
 こちらは、セル B20 の単価上昇率が少しずつ変わっていた場合の結果をデータ テーブルで算出するために作った数式です。

A12.png
 
 [行の代入セル] に、行方向に並べた値を代入したいセルを指定します。([列の代入セル] は空のままでOK) 
 A13.png
 
 ■列方向のみの単入力テーブルの例
 
 こちらは、セル B33 の数量増加率が少しずつ変わっていた場合の結果をデータ テーブルで算出するために作った数式です。
 A14.png
 
 [列の代入セル] に、列方向に並べた値を代入したいセルを指定します。([列の代入セル] は空のままでOK) 
 A15.png
 
 ■行方向と列方向を使った複入力テーブルの例
 
 こちらは、セル B50 の単価上昇率と、セル B52 の数量増加率が少しずつ変わっていた場合の結果をデータ テーブルで算出するために作った数式です。
 A16.png
 
 [行の代入セル] に行方向に並べた値を代入したいセルを、[列の代入セル] に列方向に並べた値を代入したいセルを指定します。
 A17.png
  

データ テーブルで使う基準となる数式の場所


 データ テーブルを使うときには、どんな計算をしたいのかを「基準となる数式」として作成します。
 この数式を作る場所 (セルの位置) には決まりがあります。
 
 下図の黄色の位置に基準となる数式を作成します。
 
 ■単入力テーブルの場合
 値の一覧が行方向のみ        ：左下のセル
 値の一覧が列方向のみ        ：右上のセル
 
 A18.png
 
 ■複入力テーブルの場合
 列の上で行の左 (表の左上端)
 
 A19.png
  

データ テーブルの作成例


 私は財務のプロではないのでちゃんとしたことはわかりませんが、たとえば数式を工夫すればこんなこともできるのかな、と。
 
 売上や利益が伸びていったら ｘ 年後にどのくらいの営業利益が見込めるのか、というのを作ってみました。
 年数の部分はセル参照ではなく「3」などの固定の値にしてもよいのかもしれません。
 
 A20.png
  

データ テーブルは使い方も簡単だし、ざくっと結果をだしてくれるので便利ですが、思うような結果を手に入れるために大切なのは準備だと思います。
 
 どのセルの値を使うの？どんな数式にしたらやりたいことができるの？ というところさえクリアすればよい。でもそれが難しいのよね。。。
 
 お試しください。
 
 石田 かのこ