PHP  エクセル10倍活用術              操作編

 ・「日付項目」をグループ化する(「日付データ」のグループ化)

 


操作の概要

 ここでは、 日付データをグループ化する2つの方法を説明します。

   @日付データをグループ化して、「年」「月」「四半期」という新規項目を作成する方法
        併せて、推移分析する時に有効な「近似曲線」の追加方法と
        「前年同月対比グラフ」の作成方法を説明します。
   A日付を何日間(ここでは7日間)というグループ化をする方法
        途中まで同じ操作ですが、繰り返して説明します。
 



 

操作の流れ@−「年」「月」「四半期」の新規項目作成

下図のような「売上伝票明細データ」があります

  • ここでは下図のような項目名を持った、43,357件の売上伝票明細データがあります。
    このデータは、2009年4月1日より2011年3月31日までの2ヶ年のデータです。

  • このデータには「売上日付」という項目はありますが、「年」「月」「四半期」という項目はありません。
    グループ化という機能で、新規項目を作成し、前年同月対比などのグラフで分析する方法を説明します。

ピボットテーブルを指定します

  1. 表内の任意のセル1つをクリックしておきます。ここではA1をクリックしています。

  2. メニューバーの【データ】→【ピボットテーブルとピボットグラフ レポート】をクリックします。

ピボットテーブル/ピボットグラフ ウィザード-1/3 が表示されます

  1. ピボットテーブル/ピボットグラフ ウィザード-1/3 が表示されました。

  2. ここではExcel の表から、ピボットテーブルを作成するので、この1/3の所で【完了】とします。

新規シートに白紙のピボットテーブルが表示されます

  1. 新規シートに下図のように、ピボットテーブルを作成する為の白紙のグラフ表と『ピボットテーブルのフィールドリスト』と『ピボットテーブルのツールバー』が表示されます。

売上年月ごとの売上表を作成します

  1. {『ピボットテーブルのフィールドリスト』より、「売上日付」を行欄へドラッグします。

  2. 「売上金額」をデータ欄にドラッグします。
    売上日付ごとの売上額表が出来ました。

売上日付のグループ化を指定します

  1. ここでは、A列のA4以降(「売上日付」の項目ボタンでも、日付の内容表示のセルでもOKです)の任意のセルを1つクリックします。
    ここでは、A6のセルをクリックしました。

  2. 右クリックします。

  3. 表示されたメニューより、【グループと詳細の表示】→【グループ化】とクリックします。

グループ化項目の指定をします

  1. 表示された 「グループ化」のダイアログにて、単位の欄で、「日」「月」「四半期」「年」と4つをクリックで選択します。

  • この時の複数選択は【Shift】キーを押す必要はありません。
    クリックだけで、選択の「On」と「Off」となります。

     
  • この「グループ化」のダイアログは、日付データに対して、グループ化指定した場合に表示されるものです。
     
  • この時に必ず「日」も入れた4項目を選択して下さい
    下図にもあるように、今現在の「売上日付」のデータが「日」の欄に表示されるからです。

  1. 【OK】をクリックします。

グループ化して表示されました

  • 下図のように「年」「四半期」「月」とグループ化されて表示され、元の「売上日付」は「日」の欄に表示されています。
  • さらに、下段の図のように、ピボットテーブルフィールドリストに、「月」「四半期」「年」という新規項目が追加されています。
    『ピボットテーブルのフィールドリスト』に追加されたという事は、この項目を利用してピボットテーブルとピボットグラフが作成出来ると
    いう事です。
    但し、この新規項目は、今のピボットテーブルシートと 、ここより作成するピボットグラフにおいてのみ利用出来ます

年別月別売上表を作成してみましょう

  1. 今のピボットテーブル表から、「四半期」と「売上年月」の項目を削除します。

    その方法はその項目ボタンを表の外か『ピボットテーブルのフィールドリスト』の上にドラッグするだけです。
    (ここで削除しても、『ピボットテーブルのフィールドリスト』にあるので、また必要により利用出来ます)

年別月別売上表からピボットグラフを作成してみましょう

  1. 「ピボットテーブルツールバー」か「標準ツールバー」の  (グラフウィザート)をクリックします。
    別シートにピボットグラフが表示されます。

  2. 推移を見たいので、折れ線グラフに変更してみます。
    再度  をクリックして、グラフの種類を折れ線グラフに変更します。

2ヶ年の売上推移を示す折れ線グラフが表示されました

  • 2ヶ年の売上推移が表示されました。
  • この推移動向を分析して見たいのですが、このままでは少しつかみにくいです。
    そこで2つの方法で推移をつかむことが出来ます。
        方法1:@「近似曲線」を追加して、上がり下がりを分析します。
        方法2:A前年同月対比グラフにして、動向を分析します。
     
  • 以下でその2つの方法を説明します。

−−−−−−−

@近似曲線を追加してみましょう−1

  1. 折れ線グラフをクリックで選択します。

  2. 右クリックします。

  3. 表示されたメニューより、【近似曲線の追加 】をクリックします。

近似曲線を追加してみましょう−2

  1. 近似曲線の追加ダイアログが表示されます。

  2. 「線形近似」が一番分かりやすいので、このまま【OK】をクリックします。

  3. 近似曲線が追加されたグラフとなります。
    (ここでは、グラフと近似曲線が見やすいように、背景の色を変更し、グラフも少し太くし 、近似曲線の色も変更してあります)

A前年同月対比グラフにしてみましょう

  • 近似曲線で、やや伸びている事が分かりました。
    次に、このグラフを前年同月対比グラフにして、再度確認してみましょう
  1. 今、項目欄にある「年」の項目ボタンを、系列欄へドラッグで移動するだけです。

 

前年同月対比グラフを読んでみましょう

  • 上図の前年同月対比グラフを見れば、2009年度より全体に2010年度は売上がやや上がっている事が読みとれます。

−−−−−−−−

  • データ分析の考え方や、ピボットテーブルやピボットテーブルの考え方や操作は全て、筆書の本にて詳細に説明しています。
    ここでは全ては語れませんので、是非下記の本を参照して頂ければ思います。
    この本は2003年に初版が発刊されて、現在14版まで増刷されています。
    但し、2003年刊なので、図はExcel2002バードョンですが、Excel2003との違いは、ほんの少しピボットテーブルウィザートの中の絵が違う程度で、操作としては全く同じなので、お役に立てると思います。

    『Excelでマスターする ビジネスデータ分析 実践の極意』(アスキー・メディアワークス社)
    上記より amazon.co.jp の本の購入へリンクしています。

    またこのWebと同じ、suminaka.com の「書籍・ツールのご紹介」からでも、リングで進めます。

 


 

操作の流れA− 日付データより7日間でグループ化

下図のような「売上伝票明細データ」があります

  • ここでは下図のような項目名を持った、43,357件の売上伝票明細データがあります。
    このデータは、2009年4月1日より2011年3月31日までの2ヶ年のデータです。

  • ここでは、日付を7日間でグループ化する方法を説明します。

ピボットテーブルを指定します

  1. 表内の任意のセル1つをクリックしておきます。ここではA1をクリックしています。

  2. メニューバーの【データ】→【ピボットテーブルとピボットグラフ レポート】をクリックします。

ピボットテーブル/ピボットグラフ ウィザード-1/3 が表示されます

  1. ピボットテーブル/ピボットグラフ ウィザード-1/3 が表示されました。

  2. ここではExcel の表から、ピボットテーブルを作成するので、この1/3 の所で【完了】とします。

新規シートに白紙のピボットテーブルが表示されます

  1. 新規シートに下図のように、ピボットテーブルを作成する為の白紙のグラフ表と『ピボットテーブルのフィールドリスト』と『ピボットテーブルのツールバー』が表示されます。

売上 日付ごとの売上表を作成します

  1. 『ピボットテーブルのフィールドリスト』より、「売上日付」を行欄へドラッグします。

  2. 「売上金額」をデータ欄にドラッグします。
    売上日付ごとの売上額表が出来ました。

売上日付のグループ化を指定します

  1. ここでは、A列のA4以降(「売上日付」の項目ボタンでも、日付の内容表示のセルでもOKです)の任意のセルを1つクリックします。
    ここでは、A6のセルをクリックしました。

  2. 右クリックします。

  3. 表示されたメニューより、【グループと詳細の表示】→【グループ化】とクリックします。

グループ化ダイアログの表示

  1.  「グループ化」のダイアログが表示されます。

  2. 「日」をクリックします。そして、今選択されている「月」をクリックで消します。
    (「日」だけにします)

  3. すると[日数]という欄が利用できるようになります。

グループ化したい日数の指定

  1. 今グループ化してい日数は7日間なので、日数を「7」にします。

  2. 【OK】をクリックします。

7日間にグループ化されて表示されます

  • 下図のように、7日間 にグループ化されました。
    この日数でのグループ化は、ピボットテーブル上で表示はされますが、新規項目はできません。

−−−−−−−

  • データ分析の考え方や、ピボットテーブルやピボットテーブルの考え方や操作は、筆書の本にて詳細に説明しています。

    『Excelでマスターする ビジネスデータ分析 実践の極意』(アスキー・メディアワークス社)
    上記より amazon.co.jp の本の購入へリンクしています。
    またこのWebと同じ、suminaka.com の「書籍・ツールのご紹介」からでも、リングで進めます。

    ここでは全ては語れませんので、是非上記の本を参照して頂ければ思います。
    この本は2003年に初版が発刊されて、現在13刷まで増刷されています。

    2003年刊なので、図はExcel2002バードョンですが、Excel2003との違いは、ほんの少しピボットテーブルウィザートの中の絵が違う程度で、操作としては全く同じなので、お役に立てると思います。