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

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

 


操作の概要

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

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



 

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

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

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

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

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

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

  2. 【挿入】タブ→[テーブル]の【ピボットテーブル】をクリックします。

ピボットテーブルの作成が表示されます

  1. 「ピボットテーブルの作成」 のダイアログが表示されます。

  2. 今回はExcel の表から、ピボットテーブルを作成するので、このまま【OK】とします。

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

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

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

  1. 『ピボットテーブルのフィールドリスト』より、「売上日付」を行欄(行ラベル)へドラッグします。
       (または上段の「売上日付」をクリックすると、自動的に行欄に入ります)

  2. 「売上金額」をデータ欄 (煤@値)にドラッグします。
       (または上段の「売上金額」をクリックすると、自動的にデータ欄へ入ります)

    売上日付ごとの売上額表が出来ました。

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

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

  2. 今表示されているリボンパーの【オプション】タブ→[グループ]の【グループの選択】をクリックします。
    (または【グループフィールド】をクリックします)

    数値データと日付データのグループ化の場合には、【グループフィールド】でも【グループの選択】のどちらでもOKです。

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

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

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

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

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

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

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

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

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

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

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

  1. 今表示されているリボンバーの【オプション】タブ→[ツール]の【ピボットグラフ】をクリックします。

  2. グラフの種類の選択画面になりますので、ここでは推移を見たいので、折れ線グラフ を選択します。

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

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

  • 2ヶ年の売上推移が表示されました。

2ヶ年の売上推移を明確に示す方法

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

−−−−−−−

@近似曲線を追加してみましょう −2つの方法あり

  1. 【レイアウト】タブ→[分析]の【近似曲線】をクリックします。

  2. 表示されたメニューより、【線形近似曲線】をクリックで選択します。

  3. ピボットグラフに「線形近似曲線」が追加されました。
      −−−−−−−

  4. グラフをクリックして、右クリックします。

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

  6. 近似曲線の書式設定ダイアログより、【近似曲線のオプション】タブの「線形近似曲線」を選択し【OK】をクリックします。

方法1

−−−−−−−−

方法2

−−−−−−−

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

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

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

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

−−−−−−

  • データ分析の考え方や、ピボットテーブルやピボットテーブルの考え方や操作 (Excel2002の)は、詳細に全て、筆書の本にて詳細に説明しています。
    ここでは全ては語れませんので、是非下記の本を参照して頂ければ思います。
    この本は2003年に初版が発刊されて、現在14版まで増刷されています。

    但し、2003年刊なので、図は Excel2002 バージョンです。
    また、Excel2007 より、ツールバーがリボンバーに変更されて、操作の面では少し違いがあります。
     このWebにおいて、Excel2010 の基本操作等は記載しますが、全ての操作の記載は出来ません。
     Excel2010の具体的な操作の説明ではありませんが、操作の参考にはなると思います(操作の行き道が違いますが、ピボットテーブルやピボットグラフで何が出来るかは、良く分かると思います)。
     ビジネスデータ分析の考え方の参考には大いになります。よろしければ、一読をお勧めします。

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

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

 

 


 

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

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

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

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

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

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

  2. 【挿入】タブ→[テーブル]の【ピボットテーブル】をクリックします。

ピボットテーブルの作成が表示されます

  1. 「ピボットテーブルの作成」 のダイアログが表示されます。

  2. 今回はExcel の表から、ピボットテーブルを作成するので、このまま【OK】とします。

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

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

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

  1. 『ピボットテーブルのフィールドリスト』より、「売上日付」を行欄(行ラベル)へドラッグします。
       (または上段の「売上日付」をクリックすると、自動的に行欄に入ります)

  2. 「売上金額」をデータ欄 (煤@値)にドラッグします。
       (または上段の「売上金額」をクリックすると、自動的にデータ欄へ入ります)

    売上日付ごとの売上額表が出来ました。

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

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

  2. 今表示されているリボンパーの【オプション】タブ→[グループ]の【グループの選択】をクリックします。
    (または【グループフィールド】をクリックします)

    数値データと日付データのグループ化の場合には、【グループフィールド】でも【グループの選択】のどちらでもOKです。

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

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

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

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

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

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

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

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

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

−−−−−−

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

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

    このWebサイトでは全ては説明できないため、是非上記の本を参照して頂ければ思います。
    この本は2003年に初版が発刊されて、現在13刷まで増刷されています。
    但し、2003年刊なので、図は Excel2002 バージョンです。
    データ分析の考え方や進め方を中心に説明していますので、操作だけでなく考え方を参考にしていただければと思います。

    Excel2007・2010のリボン等の操作の説明はありませんが、データ分析の操作の流れや、ピボットテーブルやピボットグラフで何が出来るかは、良く分かると思います。