2011年3月アーカイブ


ピボットテーブルとは
 ピボット(pivot)は回転、テーブルはという意味です。
 簡単に言うと、目的に応じ自在にできるクロス集計表です。
 ピボットと言われているのは、フィールドを自由に入れ替えて集計表を組み替えることが出来るためです。
 対応するフィールドに応じ瞬時に対応する集計値が算出されるため、ユーザーの求めたいデータ分析が瞬時に可能になります。
 ピボットテーブルを作成するためには、リスト(Excelのデータベース)の準備が必要です。
 集計値はデフォルトでは合計値が算出されますが、目的に応じデータ個数、平均値など色々な集計値を算出することができます。
 リスト別のフィールドをページという領域に取り出し、3次元の集計表を作成することができます。
 ピボットテーブルのデータは、リストデータにリンクされていますので、ピボットテーブル内のデータは直接編集することはできません。
 変更をしようとすると「できない」旨の警告メッセージが表示されますので、変更が必要な場合は、もとのリストデータを変更します。

ピボットテーブルの用途
 売上明細、経皮明細や仕入れ明細、顧客管理情報、見積もり、契約物件、
 市場調査やアンケートデータ、社員名簿、成績評価修データ、用度品管理など、あらゆる部門のデータで数値を含む情報があればピボットテーブルを利用することができます。


次回は実際にピボットテーブルを作成する方法についてご紹介いたします。

Excel2003での方はこちらから (現在作成中)

複合グラフ


複合グラフ・・・
 異なるデータ系列を別途、数値軸を設け表示するものです。
 (主軸と第2軸を使って表示する複合グラフ)
 金額と比率、金額と数値、また同じ金額同士でもデータ系列間の数値が
大きく違う場合等に利用します。



下表には、商品ごとの売上高、構成比、累計構成比が表示されています。
この評を元に、商品別売上高のABC分析が出来る表に変更し、
商品別売上高のABC分析グラフを作成してみましょう。

e0164.jpg

1.売上高の大きい順に並べ替えをしましょう。
  セルB3:F20を範囲選択し、売上高を降順で並べ替えしましょう。
  ※この時、合計を算出している部分を含まずに選択します。

2.グラフにする範囲を選択しましょう。
  セルC4:D20とE4:F20を範囲選択します。
  e0167.jpg

  Excel2003までの方は
  1.グラフウィザードボタンを押下しましょう。
    表示されたグラフウィザードの「ユーザー設定」タブをクリックします。
    e0168.jpg
   グラフの種類から「2軸上の折れ線と縦棒」を選択して「次へ」ボタンを押下します。
 2.データ範囲を確認しましょう。
   e0169.jpg
   プレビューに複合グラフ(縦棒と折れ線グラフ)が表示されていますか?
   系列は「列」単位に取り込まれていますか?
   データ範囲は選択した通りになっていますか?
   確認が完了したら「次へ」ボタンを押下します。

 3.タイトルや凡例、目盛り線の位置を設定しましょう。
   タイトルは「商品別ABC分析」としましょう。
   凡例は下に表示してみましょう。
   e0171.jpg

 4.グラフが作成されました。
  e0172.jpg


 Excel2007の方は
 1.挿入タブ→グラフ→縦棒→集合縦棒を選択します。
   e0173.jpg

 2.まずは、売上高、累計構成比の縦棒グラフが作成されます。
   e0174.jpg
 3.累計構成比を選択します。
   e0175.jpg
  選択した累計構成比の上で右クリックをし、「データ系列の書式設定」を選択します。
  e0176.jpg
 データ系列の書式設定の使用する軸2軸(上/右側)にチェックをします。
 e0177.jpg
 閉じるボタンを押下し、データ系列の書式設定を閉じます。
 すると、累計構成比も縦棒グラフで表示されました。
 e0178.jpg

4.累計構成比が選択されたままになっています。
  選択されたままの状態で、デザインタブ→グラフの種類の変更→
  折れ線グラフのマーカー付き折れ線を選択します。
  e0179.jpg
  選択が完了したらOKボタンを押下しグラフ作成終了です。
  e0180.jpg

ABC分析
 上位集中度分析手法のことです。
 今回は、商品の売上高がどのくらい上位に集中しているかを累計構成比でみます。
 累計構成比が約70%までの商品をAクラス、95%までのクラスをBクラス、それ以外を
 Cクラスといいます。

Zチャート作成

 

Zチャートとは・・・

 毎月の販売高推移を、1)月毎の販売高
           2)累積販売高
           3)月毎の移動年計
 
 以上の3つを線グラフで表示したものです。
 ちょうど、アルファベットのZという文字に似たグラフとなるので、
 Zチャートと呼ばれています。

 ※月毎の移動年計(12カ月移動和ともいう)の計算式は
  =前月の移動年計+当月販売高−前年の当月販売高 で求めます。


下図の月別販売高推移表を使ってZチャートを作成してみましょう。
e0161.jpg

1.セルG5に移動年計を求める数式を入力しましょう。
  =D16+E5−C5
  と、入力をし、まず1月の移動年計を計算しましょう。

2.次にセルG6に2月以降の移動年計を求める数式を入力しましょう。
  =G5+E6−C6
  と入力をしたら、G6の数式をG16までコピーしましょう。
  e0162.jpg

3.上記の表が作成され、Zチャートを作成する準備が整いました。
  では、折れ線グラフを作成してみましょう。
  グラフにするセル範囲である B4:B16とE4:G16を選択しましょう。

4.選択が完了したら、グラフを挿入します。
  グラフの種類は「折れ線」を選択し、形式は「データマーカー付き」を選択、
  グラフタイトルは「月別売上高推移Zチャート」とし、新しいシートに
  グラフを作成しましょう。

  下のグラフが作成されましたか?
  e0163.jpg
  12月の移動年計と累計販売高が同じ値になり、アルファベットのZのような
  かたちになることに注目をしましょう。
  
  移動年計の折れ線は、月毎の年間販売高の推移を表します。
  そのため、移動年計が
  1)右上がりの時
    販売高は前年度に比べて上昇傾向
  2)横ばいの時
    販売高も横ばい
  3)右下がりの時
    販売高は下降傾向
  このように、読み取ることができます。



前回は元データを利用し、グラフ内に移動平均を表示しました。
今回は、移動平均を計算して表示させてみましょう。

移動平均の計算式は
 当月を含む過去一定期間の数値の平均です。

e0155.jpg
1.前回使用した表に3ヶ月移動平均
  5ヶ月移動平均のフィールドを
  作成しましょう。














e0156.jpg
 2.セルD6に計算式を入力しましょう。
   「=AVERAGE(C4:C6)」と入力します。
   数式をD15までコピーしましょう。
   ※今回数式はAVERAGE関数を用いましたが、
     「=SUM(C4:C6)/3」としても同じ結果を
     えることができます。










e0157.jpg

3.同様にセルE8に5ヶ月の移動平均を
  求め、セルE15まで数式を
  コピーしましょう。
 

 ※移動平均のみのグラフを作成する場合は、
   グラフの作成範囲を、B3:B15,D3:E15とし、
   折れ線グラフを選択すればOKです








分析ツールを使用して移動平均の値を求める

 上記方法は、AVERAGE関数を用いて移動平均を求めましたが、Excelのアドイン機能である
分析ツールを使用して移動平均を求めることができます。
 ※メニューバーのツールメニューの中に「分析ツール」が表示されていない場合は、
   メニュー→アドイン→分析ツールの前のチェックをつけて利用できるようにしましょう。
   Excel2007の場合は、
   画面左上のOfficeボタン→Excelのオプション→画面左側の「アドイン」をクリック→
   画面右側の下にあるExcelアドイン右側の「設定」ボタンをクリックし、
   「分析ツール」の前にあるチェックをつけましょう。

1.月度売上高が入力されているシートを開きましょう。
  売上高の右側に3ヶ月と5ヶ月の移動平均を表示させるフィールドを作成しておきましょう。

2.メニューバーのツール→分析ツールを選択し、「データ分析」ダイアログボックスを
  開きましょう。
  ※Excel2007では、データタブ→分析→データ分析でダイアログボックスが
    開いてきます。

  e0158.jpg
  上記データ分析のダイアログボックスの中から、「移動平均」を選択しOKボタンを
  押下します。

3.移動平均ダイアログボックスでは、3ヶ月の移動平均を表示する場合には下図のように設定をします。

  e0159.jpg
  ※入力範囲は、フィールド名称を含まず、売上高のセル全てを範囲選択します
  ※区間は、ここでは3ヶ月の移動平均を求めるので「3」を入力します
    (5ヶ月の場合は、「5」を入力します)
  ※出力先は、ここでは3ヶ月の移動平均を表示したいのでD4を選択します
    (5ヶ月の場合は、「E5」を選択します)
    出力先は値を出力する範囲の先頭のセルを必ず選択しましょう

4.3ヶ月の移動平均は、1月と2月は、求める式の範囲にデータがないので
  エラー値(#N/A)が表示されます。
  3月以降の値はAVERAGE関数で求められています。
  e0160.jpg



移動平均グラフを作成すると、データの傾向が平均化され傾向を読み取りやすくすることができます。
Excelのグラフ機能の中にある近似曲線を利用し移動平均のグラフを作成することができます。

e0148.jpg
1.左図の月別売上高のデータから、
  3カ月移動平均と5カ月移動平均のグラフを
  作成してみましょう。


















e0149.jpg
2.グラフを作成する範囲(B3:C15)を
  範囲選択しましょう。

3.グラフウィザードを起動しましょう。

4.グラフの種類:縦棒
  グラフの形式:集合縦棒
  上記2点を確認し、次へ進みましょう。

5.グラフタイトルは
  「月別売上高と移動平均」としましょう。
  グラフの作成場所は「新しいシート」と
  してみましょう。
  (同一シート内でも作成することが
   可能です)

6.次に近似曲線をグラフ内に追加して
  みましょう。
  データ系列(どの棒でも構いません)を
                              右クリックしてましょう。
  
e0150.jpg
  7.左図のメニューが表示されますので、
    近似曲線の追加をクリックしましょう。

e0150-1.jpg










上の図はExcel2007のメニュー        上の図はExcel2003までのメニュー


e0152.jpg
8.左図の近似曲線の書式設定が
  表示されます。
  (左図はExcel2007の画像)















e0152-1.jpg
8−1.左図の近似曲線の追加画面は
    Excel2003までの画像です。















9.「種類」タブ(Excel2007では近似曲線のオプション)であることを確認しましょう。
  移動平均アイコンをクリックし、区間を3に変更しましょう。
  変更が完了したらOKボタン(Excel2007では閉じるボタン)をクリックしましょう。
  グラフ内に移動平均のグラフが折れ線グラフで表示されたことを確認しましょう。
  
  確認が出来たら、同様に任意のデータ系列を右クリックし、移動平均を選択後
  区間を5に変更して5カ月の移動平均を表示しましょう。
  下図のように移動平均の折れ線グラフが表示されたことを確認しましょう。
  折れ線グラフの色の変更は通常の変更方法と同様です。

  e0154.jpg

次回は、元データを表示せず、移動平均のみのグラフを表示する方法をご紹介いたします。

2011年10月

            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31