1. よねさんのExcelとWordの使い方 » 
2. エクセル2010基本講座：目次 » 
3. データ分析・パワービュー » 
4. What-If分析：データテーブル

エクセル2010基本講座：What-If分析：データテーブル

データテーブル　　　　　Topへ

• データテーブルは1つまたは2つの値を変化させて計算することができます。
  以下では2つの値を変化させて計算する例を示します。
• 計算例
  • 預貯金の金利計算をしてみたいと思います。
    • 計算式は元金×(1+年利×複利期間)＾(預金年数/複利期間)　をもとにしています。
      1年複利として、元金×(1+年利)＾預金年数　を使って計算します。
      (現実の計算方法とは異なることがありますことをお断りしておきます)
      • 元金は定数、金利と期間が変数になります。金利と期間の2つを変えたときの計算を行っています。
      
    • 計算したい期間と金利の表を作成します。
      • B7セルではB4セルに入力した計算式を参照するため「=B4」としています。
        B7セルに計算式を入力してもOKです。
      • B7セルには計算式の結果が表示されますので、目障りな場合はフォントの色を白にするなどして対処します。
        ここでは、説明上、見えていないと困りますのでそのままにしています。
      
  • データテーブルの使用手順
    1. データテーブルで計算結果を表示する範囲を選択します。
       
    2. [データ]タブの[データツール]グループにある[What-If分析]→[データテーブル]を実行します。
       
       • Excelの表示幅が狭いと下図のような表示になります。
         
    3. 「データテーブル」ダイアログボックスで下図のように設定します。
       • 行の代入セル：行には金利に対する計算結果を求めるので元の金利のセル「$B$2」
       • 列の代入セル：列には期間に対する計算結果を求めるので元の期間のセル「$B$3」
       
    4. 計算結果が表示されました。
       
    5. 計算結果のセル(C8:G11)の表示を整数値とし、桁区切りを入れ見易くしました。
       

データテーブルを修正すると・・・　　　　　Topへ

• データテーブルの一部を修正すると・・・再計算されます。
  • E7セルを「0.4%」に修正しました。すると、E8:E11セルは再計算された結果が表示されました。
    

データテーブルの作り方の例　　　　　Topへ

• 上記は説明するうえで書式など設定して、分かりやすく作成していますが、最小限必要なものだけで作成すると以下のような感じになります。
• 変数の参照セルとテーブルだけを作成すればよいことになります。
  定数は計算式に入れればよいでしょう。しかし、この値を変えたいのならこの定数部分もセル参照した方がよいことになります。
• 計算式をデータテーブルの左上隅のセルに入力します。
  計算式は変数をセル参照するように設定します。
  1. 変数となるセルを決めます。
     下図のB2,B3セルです。
     
  2. B8セルに計算式をセル参照して入力します。
  3. C8〜G8　に変数Xの値を入力します。
     B9〜B12　に変数Yの値を入力します。
     
  4. B8:G13　を選択し、[What-If分析]→[データテーブル]を実行します。
     
  5. [OK]ボタンをクリックすると、計算結果が表示されます。
     計算結果は｛=TABLE(B2,B3)}　といった配列数式のような形式の数式で求められているのがわかります。
     
  6. 変更できるのはB2,B3にあたるセル値(C8〜G8　と　B9〜B12　の値)。
     テーブルの左上隅のセルに入力した計算式自体も変更することができます。
     
     • なお、このような計算は通常の数式でも可能です。
       データテーブルを使うと、複合参照などを考えずに済む・・・・といった便利さがあります。
       • C9セルに=2*C$8*(1+$B9)+50　と入力して、C9セルをコピーし、他のセルへ数式を貼り付けします。