独学MOS

データの分析

分析ツールとは

Excelに元から用意されているアドインで様々なデータの分析を行ったり分析結果からグラフを作成したりすることができます。

分析ツールアドインの有効化

分析ツールアドインは初期状態では有効化されていないため利用できません。以下の手順で分析ツールを利用するための設定を行う必要があります。

  1. 「ファイル」タブをクリック
  2. 「オプション」をクリック
  3. Excelのオプションダイアログの「アドイン」をクリック
  4. アドイン一覧の「分析ツール」を選択して「設定」ボタンをクリック
  5. アドインダイアログの「分析ツール」にチェックをつけて「OK」ボタンをクリック
  6. 「データ」タブをクリックして分析グループが存在していることを確認する

分析ツールを使ったヒストグラムの作成

例として以下の表があったとします。

点数 データ区間
15 10
20 20
23 30
24 40
89 50
75 60
40 70
33 80
100 90
9 100
85
70
83

この表から分析ツールを使ってヒストグラムを作成するには以下の手順を行います。

  1. 「データ」タブをクリック
  2. 分析グループの「データ分析」をクリック
  3. データ分析ダイアログの分析ツール一覧から「ヒストグラム」を選択して「OK」ボタンをクリック
  4. ヒストグラムダイアログの各項目を設定して「OK」ボタンをクリック
  5. ヒストグラムダイアログの設定に応じてヒストグラムが作成される

分析ツールを使った回帰分析の作成

例として以下の表があったとします。

Y値 X値
10 10
15 20
20 30
25 40
30 50
35 60
40 70
45 80
50 90
55 100

この表から分析ツールを使って回帰分析を作成するには以下の手順を行います。

  1. 「データ」タブをクリック
  2. 分析グループの「データ分析」をクリック
  3. データ分析ダイアログの分析ツール一覧から「回帰分析」を選択して「OK」ボタンをクリック
  4. 回帰分析ダイアログの各項目を設定して「OK」ボタンをクリック
  5. 回帰分析ダイアログの設定に応じて回帰分析が作成される

What-If分析とは

What-If分析とは数式に対して順番に異なる値を入れていき、結果の変化を観察する分析方法のことです。代入する値を用意する方法はそれぞれの分析方法ごとに異なります。

データテーブルを使ったWhat-If分析

データテーブルを使ったWhat-If分析では一つ、あるいは二つの値を変化させて数式の計算結果の変化を観察することができます。次に示す表は一つの値を変化させていく単入力テーブルと呼ばれる例です。(「Y」がセル「A1」にあるものとします)

Y X
0 10
Yの代入値 数式
  =A2*B2
10  
20  
30  
40  
50  

「Yの代入値」の下にあるセルが空白になっているのが注目すべきポイントです。単入力テーブルでは縦に代入値を並べる場合はその右上に値を代入する数式を設定しなければならないという決まりごとがあるためです。この表を用いてのデータテーブルを使ったWhat-If分析は以下の手順で行います。

  1. 「データ」タブをクリック
  2. What-If分析で使用するセル範囲を選択
  3. データツールグループの「What-If分析」をクリック
  4. 「データテーブル」をクリック
  5. データテーブルダイアログで「列の代入セル」項目に数式の参照元になっているセルを入力して「OK」ボタンをクリック
  6. 数式に値が代入された結果が表示される

上記の例と手順では代入するデータが縦に並んでいましたが、横に並んだデータを同じ手順で分析することも可能です。たとえば以下のような表があったとします。(「Y」がセル「A1」にあるものとします)

Y 0 Yの代入値   10 20 30 40 50
X 10 数式 =B1*B2          

「Yの代入値」の右にあるセルが空白になっているのが注目すべきポイントです。単入力テーブルで横並びのデータを扱う場合は、代入値の左下に値を代入する数式を設定しなければならないという決まりごとがあります。そして分析の手順の中に出てきたデータテーブルダイアログで「行の代入セル」項目に数式の参照元になっているセルを入力しなければなりません。そのほかの手順については代入値を縦に並べた時と同じです。

さて、ここまでで一つの値を変化させる単入力テーブルを学習しました。データテーブルを使ったWhat-if分析にはもう一つ、複入力テーブルを使って二つの値を同時に変化させながら数式の結果を分析していく方法があります。その例となるテーブルを以下に示します。(「Y」がセル「A1」にあるものとします)

Y X
0 10
    Xの代入値
  =A2*B2 11 12 13 14 15
Yの代入値 10          
20          
30          
40          
50          

このテーブルで注目すべきところは数式が設定されているセルと代入値が設定されているセルの位置関係です。代入値が二つある場合、数式と同じ行と列にそれぞれの代入値を並べなければなりません。複雑な構成のテーブルですが分析の手順自体は単入力テーブルと同じです。異なっている点はデータテーブルダイアログで「行の代入セル」と「列の代入セル」の両方を入力する必要があるという部分のみです。

シナリオを使ったWhat-If分析

シナリオを使ったWhat-If分析では任意の値の組み合わせを順番に使って数式の結果がどう変化していくか確認することができます。例として次の表があったとします。(「X」がセル「A1」にあるものとします)

X Y Z X+Y+Z
1 2 3 =A2+B2+C2

この表に対してシナリオを用いて「X」「Y」「Z」の値を変化させ、数式の結果がどう変化していくか調べるには以下の手順を行います。

  1. 「データ」タブをクリック
  2. データツールグループの「What-If分析」をクリック
  3. 「シナリオの登録と管理」をクリック
  4. シナリオの登録と管理ダイアログで「追加」ボタンをクリック
  5. シナリオの編集ダイアログで「シナリオ名」項目と「変化させるセル」を入力し「OK」ボタンをクリック
  6. シナリオの値ダイアログで変化させるセルの値を入力し「OK」ボタンをクリック
  7. シナリオの登録と管理ダイアログで「表示」ボタンをクリック
  8. シナリオの値ダイアログで設定された値がセルに代入される
  9. シナリオの登録と管理ダイアログで「情報」ボタンをクリック
  10. シナリオ情報ダイアログで「OK」ボタンをクリック
  11. 新しいワークシートにシナリオごとの値とその値が設定された場合の結果が一覧で表示される

シナリオの登録は複数可能ですので、試してみたい値の組み合わせの数だけシナリオを用意すれば、さまざまな値の組み合わせとその計算結果を確認することができます。

ゴールシークを使ったWhat-If分析

ゴールシークを使ったWhat-If分析ではあらかじめ目標となる値を設定しておき、目標を達成するために必要な値を求めます。例として次の表があったとします。(「X」がセル「A1」にあるものとします)

X Y Z X+Y+Z
  5 3 =A2+B2+C2

この表で数式の計算結果を「10」にしたいとき「X」には何を設定すればいいのかを求める手順は以下の通りです。

  1. 「データ」タブをクリック
  2. データツールグループの「What-If分析」をクリック
  3. 「ゴールシーク」をクリック
  4. ゴールシークダイアログで各項目を設定して「OK」ボタンをクリック
  5. ゴールシークダイアログに結果が表示されるので「OK」ボタンをクリック
  6. セルに計算結果が代入される

ソルバーを使ったWhat-If分析

ソルバーアドインは初期状態では有効化されていないため利用できません。以下の手順でソルバーアドインを利用するための設定を行う必要があります。

  1. 「ファイル」タブをクリック
  2. 「オプション」をクリック
  3. Excelのオプションダイアログの「アドイン」をクリック
  4. アドイン一覧の「ソルバーアドイン」を選択して「設定」ボタンをクリック
  5. アドインダイアログの「ソルバーアドイン」にチェックをつけて「OK」ボタンをクリック
  6. 「データ」タブをクリックして分析グループが存在していることを確認する

ソルバーを使ったWhat-If分析ではあらかじめ設定した目標値を満たすための値を複数の条件から導き出すことができます。例として次の表があったとします。(「X」がセル「A1」にあるものとします)

X Y Z X+Y+Z
      =A2+B2+C2

この表で数式の計算結果を「10」にしたいとき「X」「Y」「Z」には何を設定すればいいのかを求めたいとします。三つの値の組み合わせは一通りではありませんので以下のような条件を設定します。

  • Xは3以上
  • Yは5以上
  • Zは10以下

これらの条件をもとにソルバーを使った分析をするための手順は以下の通りです。

  1. 「データ」タブをクリック
  2. 分析グループの「ソルバー」をクリック
  3. ソルバーのパラメーターダイアログで各項目を入力して「追加」ボタンをクリック
  4. 制約条件の追加ダイアログで一つ目の条件を入力して「追加」ボタンをクリック
  5. 制約条件の追加ダイアログで二つ目の条件を入力して「追加」ボタンをクリック
  6. 制約条件の追加ダイアログで三つ目の条件を入力して「OK」ボタンをクリック
  7. ソルバーのパラメーターダイアログで「解決」ボタンをクリック
  8. ソルバーの結果ダイアログで「OK」ボタンをクリック
  9. セルに計算結果が代入される