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

　・数値項目の層別分析をする（数値項目のグループ化）

　

操作の概要

　ここでは、 数値データをグループ化して、グラフ化して、ヒストグラム分析をする操作方法を説明をします。
　（ここでは数値を持つ項目を行欄に入れて、グループ化する操作を説明します）
　

操作の流れ

下図のような「社員名簿」があります

  • ここでは下図のような項目名を持った、89名の社員名簿データがあります。

  • このデータの数値項目は「年齢」です。他に金額も数量もありません。しかし、このようなデータでもピボットテーブルで分析は出来ます。
    それも以下説明して行きます。

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

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

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

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

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

  2. 今回は、Ｅｘｃｅｌ の表から、ピボットテーブルを作成するので、このまま【ＯＫ】とします。

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

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

年齢別人数表を作成します

  1. 『ピボットテーブルのフィールドリスト』より、「年齢」を行欄（行ラべル）へドラッグします。
        （ここで、上段の「年齢」の項目をクリックすると、数値データなので、データ欄（∑　値）の欄に入ります。
        　その場合には、手動で行欄へドラッグして下さい）

  2. 「氏名」をデータ欄 （∑　値）にドラッグします。
        （ここでも、上段の「氏名」の項目をクリックすると、文字列データなので、行欄（行ラベル）の欄に入ります。
    　    その場合には、手動でデータ欄へドラッグして下さい）
     
    これで、２３歳から７３歳までの、年齢別人数表が出来ました。
  • 「年齢」は数値項目です。しかし、行欄に入れる事も出来ます。
    （行欄と列欄には、基本的に「担当者名」などの文字項目を入れます）
    　
  • 「氏名」は文字項目です。数値データではありません。
    その項目をデータ欄にドラッグすると、当然「合計」は出来ません。
    文字項目をデータ欄にドラッグすると、件数集計となります。
    件数とは、行数のカウントです。この社員名簿の氏名のように、重複する項目がたい場合には、正しく件数集計が出来ます。
    　
  • その機能を利用して、ここでは、年齢別の人数表が作成出来ます。

　

これより、ピボットグラフを作成してみましょう

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

  2. グラフの種類の選択画面になりますので、ここでは「縦棒グラフ」を選択します。

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

年齢をグループ化指定します

  • 年齢別人数グラフが出来ました。これでも会社の年齢構成はわかるのですが、さらに大きく動向をつかむ為に、年齢をグループ化してみます。

  1. ピボットテーブルの、Ａ４以降Ａ３１までの（年齢の内容表示のセル）任意のセルを１つクリックします。
    ここでは、Ａ６のセルをクリックしました。

  2. 【オプション】タブ→［グループ］の【グループフィールド】をクリックします。
    又は【グループの選択】でも結構です。

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

又は



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

  • 数値項目のグループ化の指定をしたので、最小値と最大値が自動的に表示されています。
    又単位として、既定値として全体を１０分類する位の値が表示されています。

  1. 表示された 「グループ化」のダイアログにて、 ここでは「先頭の値」として、「２０」を入力します（２０歳よりと指定します）。

  2. 末尾の値は、このままで良いとします。

  3. 単位は、５歳区切りで表示させたいので、ここでは「５」と入力します。

  4. 【ＯＫ】をクリックします。

グループ化されたピボットテーブルとピボットグラフが表示されました

  • 下図のように年齢が５歳区切りに、グループ化されたピボットテーブルとピボットグラフとなりました。

－－－－－－－－

このピボットグラフを、完全なヒストグラムにしてみましょう－１

  • ヒストグラムは、グラフの棒の間隔がないものが完全です。
    そこで、グラフの間隔をなくしてみます。

  1. 表示されている棒グラフをクリックし、棒の上で右クリックします。

  2. 【データ系列の書式設定】をクリックします。

このピボットグラフを 、完全なヒストグラムにしてみましょう－２

  1. 「データ系列の書式設定」のダイアログが表示されます。
  2. 今表示されている【系列のオプション】タブにて、「要素の間隔」の欄で、間隔を「なし」 の方へドラッグして、「０％」とします。
  3. 【閉じる】をクリックします。

棒の間隔はなくなりました

  • 棒グラフの間隔はなくなりましたが、これでは、棒グラフの境目が分かりにくいです。
    棒グラフの枠線を薄い水色に変更してみます。

棒グラフの棒の枠線の色を薄い水色に変更してみます－１

  1. 再度、棒グラフの棒の上で右クリックします。

  2. 【データ系列の書式設定】をクリックします。

  3. 「データ系列の書式設定」のダイアログが表示されます。

  4. 今回は、【枠線の色】 タブをクリックします。

  5. 今「自動」になっています。それを「線（単色）」をクリックで選択します。

棒グラフの棒の枠線の色を薄い水色に変更してみます－２

  1. 色の横の　　をクリックします。

  2. 表示された色パレット等から、 変更する色を選択します。

  3. 【閉じる】をクリックします。

  4. 枠線が薄い水色になり、区別がつくヒストグラムになりました。

－－－－－－－－

課別の分析をしてみましょう （詳細は割愛します）

  • グループ化の操作とヒストグラム作成の操作は以上で終了です。
  • ここからは、今作成したピボットテーブルとヒストグラムを利用して、データ分析をしてみましょう。

  1. 「所属　部」と「所属　課」をページ欄（レポートフィルタ）へドラッグします。

  2. 「性別」を系列欄（汎用フィルタ）へドラッグします。

  3. 全社員の性別、年齢別のピボットテーブルと構成グラフとなりました。

    ここより、条件欄の、所属部や所属課の条件を変えた、スライス分析が出来ます。
    又系列欄（汎用フィルタ）に入れた「性別」を「役職」に変えて、ダイス分析も出来ます。

　
  • この数値データのグループ化は、いろいろと分析の際に役立ちます。

    ここでは例として使用しませんでしたが、例えば「売上単価」などの項目があるデータリスト表からは、『売上単価別売上金額』や『売上単価別売上個数（重複する文字列がないデータの場合のみ）』などの分析に役立ちます。

    つまり、売上単価は業種によりかなりの数がある場合が多いです。そのままグラフにしても読み切れない場合も多いです。その場合に、グループ化すると、ある単価グループごとの傾向が簡単に掴めます。
    　
  • ここでは、あえて社員名簿のような、売上額などの数値データがないデータリスト表からもピボットテーブルとピボットグラフにより、いろいろと分析も出来る事も含めて説明しました。

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

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

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

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