情報科学 - 2009/11th/cross_tabulation のバックアップ(No.5)

• バックアップ一覧
• 差分 を表示
• 現在との差分 を表示
• 現在との差分 - Visual を表示
• ソース を表示
• 2009/11th/cross_tabulation へ行く。
  • 1 (2010-01-05 (火) 08:49:55)
  • 2 (2010-01-05 (火) 11:41:32)
  • 3 (2010-01-06 (水) 03:41:10)
  • 4 (2010-01-06 (水) 04:47:16)
  • 5 (2010-01-06 (水) 05:59:06)

ピボットテーブル

シートを切り替えます。 「ピボットテーブル」という名前のシートをクリックして選択してください。

ピボットテーブル

ピボットテーブルとは

表計算ソフトを使って表やグラフを作っても、 複数の項目や大量のデータがある場合は、 表やグラフを見ただけでは、 すぐにデータの特徴をつかむことができないこともあります。 データに埋もれた特徴をつかむためには、 データをうまく集計することも必要です。

「ピボットテーブル」（クロス集計ともいう）の機能を使えば、 大量のデータを集計・分析することができます。 ピボットテーブルの機能は、簡単にいえば、 「大量なデータを複数の項目に注目して集計しまとめる」です。 項目ごとの合計や平均など、 単純な集計ではわかりづらいデータの特徴をつかむことができます。

たとえば、ある商品の購入した100人を調査して、 その割合が男性が7割で女性が3割だったとします。 これは「男性か女性か」というひとつの項目で集計した結果ですが、 「年代」の項目を追加してみましょう。

年代によって男女の比率がかわり、 「女性のほうが購入している」年代があることがわかります。 ピボットテーブルを使えば、 このようなより細かいデータの分析もできます。

ピボットテーブルの表

ピボットテーブルの表（クロス集計表ともいいます）では、 2種類の項目のデータを比べやすくするための1つの作法（ルール）があります。 作法にしたがうと、 上の例は「年代という基準で、性別ごとの購入者数を比較した」ことになります。

• 列ごとの項目（縦方向）
  • 比較する基準になる項目や要因となる項目
  • 上の例では「年代」
• 行ごとの項目（横方向）
  • 結果や比較したい項目
  • 上の例では「性別」

ピボットテーブルを使ったデータの分析

「ピボットテーブル」というシートのデータを使って、 ピボットテーブルを作成して、データを分析してみましょう。

ピボットテーブルの作成

まず、ピボットテーブルを作成します。 「販売先」を基準にして、「担当者」ごとの売上の「金額」を比較してみましょう。

1. A5セルをマウスでクリックする
   （表の中のセルであれば、どれでもかまわない）
2. 「挿入」タブの「テーブル」グループにある、「ピボットテーブル」ボタンをクリックする
   
3. 「ピボットテープルの作成」ウィンドウが表示される
   
   • 「テーブルまたは範囲を選択」をクリックして選択する
   • 「テーブル/範囲」が「ピボットテーブル!$A$5:$I$56」となっているのを確認する
   • 「新規ワークシート」をクリックして選択する
4. 「OK」をクリックすると、ピボットテーブルの枠が作成される
   

ピボットテーブルは、新しいシート（「Sheet1」など）に作成されます。 もとのデータはそのままです。

ここまでは、ピボットテーブルの枠の作成になります。 次に、集計をするために、 ピボットテーブルの列（列ラベル）、行（行ラベル）、集計値（Σ値）を設定して、 ピボットテーブルの中身の作成をします。

1. ウィンドウ右側の「ピボットテーブルのフィールドリスト」作業ウィンドウの上側にある、フィールド名の部分を、列ラベル、行ラベル、値のフィールドのボックスへ、マウスでドラッグする
   
   • 列ラベル : 「販売先」を「列ラベルフィールド」へドラッグする
   • 行ラベル : 「担当者」を「行ラベルフィールド」へドラッグする
   • 値 : 「金額」を「値フィールド」へドラッグする
2. 設定ができると、ピボットテーブルに集計結果が表示される
   

データの抽出

ピボットテーブルの列または行ラベルエリアの項目を選択して、 特定のデータだけ抽出することができます。 フィルタ機能と同じような操作です。

「列ラベル」から「Ａ株式会社」と「Ｂ電子」のデータだけ抽出してみましょう。

1. ピボットテーブルの「列ラベル」の「▼」をクリックする
2. 表示されたメニューから条件を設定する
   
   • 「すべての選択」のチェック欄（□）をクリックしてチェックを外す
   • 「Ａ株式会社」と「Ｂ電子」のチェック欄（□）をクリックしてチェックする
3. 「OK」ボタンをクリックすると、フィルタが実行される
   

もとの状態に戻すには、「すべての選択」のチェック欄（□）をクリックして、 チェック状態にします。

集計方法の変更

ピボットテーブルを作った状態では、 集計方法は数値の場合は合計を表示します。

集計方法を変更して、データの傾向を別の角度から見てみましょう。

1. ウィンドウ右側の「ピボットテーブルのフィールドリスト」作業ウィンドウの、値フィールドのボックスにある、「合計 / 金額」ボタンをクリックする
2. メニューから「値フィールドの設定」を選択する
   
3. 「値フィールドの設定」ウィンドウが表示される
   
   • 集計方法から「平均」を選択する
   • もし小数点以下の桁数や桁区切りなど、表示形式を変更したい場合は、「表示形式」ボタンをクリックして、設定する
4. 「OK」をクリックすると、ピボットテーブルの集計方法が、合計から平均の値に変更される
   

集計する項目の変更

フィールドのグループ化

ピボットテーブルを使った集計

左下のシート名「売上表」をクリックし、「売上表」のシートを表示してください。

ピボットテーブルの作成

担当者ごとの、各販売先への売り上げ金額を調べてみましょう。

次のようにして、ピボットテーブルを作成します。

1. メニューバーから「データ」→「ピボットテーブルとピボットグラフレポート」を選びます。
2. 「ピボットテーブル／ピボットグラフウィザード - 1/3」というウィンドウが表示されます。 データの場所が「Excelのリスト／データベース」、 レポート種類が「ピボットテーブル」なのを確認し、 「次へ」ボタンをクリックします。

   #ref(): File not found: "excel06.png" at page "2009/11th/cross_tabulation"

3. 「ピボットテーブル／ピボットグラフウィザード - 2/3」というウィンドウが表示されます。 ピボットテーブルで集計する範囲を選びます。
   A1〜G46セルをマウスでドラッグして選択します。 選択できたら「次へ」ボタンをクリックします。

   #ref(): File not found: "excel07.png" at page "2009/11th/cross_tabulation"

4. 「ピボットテーブル／ピボットグラフウィザード - 3/3」というウィンドウが表示されます。
   どこにピボットテーブルを作るか選択します。「既存のワークシート」を選び、下の入力欄をクリックした後、A50セルをクリックしてください。

   #ref(): File not found: "excel08.png" at page "2009/11th/cross_tabulation"

5. 「レイアウト」ボタンをクリックしてください。
   1. 「レイアウト」ウィンドウでは、どのようにデータを集計するかを設定します。
      右側に並んでいる項目名のアイコンを、左側の表へドラッグすることで設定します。 次のようにドラッグしてください。 すべてできたら「OK」ボタンをクリックしてください。
      • 「日付」を「ページ」へドラッグ
      • 「担当者」を「行」へドラッグ
      • 「販売先」を「列」へドラッグ
      • 「金額」を「データ」へドラッグ
6. 「完了」ボタンをクリックすると、ピボットテーブルができあがります。

   #ref(): File not found: "excel09.png" at page "2009/11th/cross_tabulation"

この表を使えば、 「担当者を基準とした、各販売先への売り上げの比較」を分析することができます。

#ref(): File not found: "excel10.png" at page "2009/11th/cross_tabulation"

ピボットテーブルの設定

「日付」や「担当者」や「販売先」の「▼」ボタンをクリックすると、データをさらに絞り込むことができます。

1. 「▼」ボタンをクリック
2. 表示したい項目だけをメニューから選択／またはチェック
3. 「OK」ボタンをクリック

   #ref(): File not found: "excel11.png" at page "2009/11th/cross_tabulation"

元に戻すには「▼」ボタンのメニューから「すべて」を選ぶかチェックします。

• 「日付」では、各日付のデータを表示
• 「担当者」や「販売先」では、集計したい担当者・販売先のデータだけを選択して表示

  #ref(): File not found: "excel12.png" at page "2009/11th/cross_tabulation"

また、「合計／金額」をダブルクリックすると、集計方法も変更できます。 集計するデータ（今は「金額」）の 合計・個数・平均・最大最小値などを自動的に集計することができます。 また、「表示形式」をクリックすれば、 「￥10,000」「100.00」のようなデータの表示形式も設定できます。

#ref(): File not found: "excel13.png" at page "2009/11th/cross_tabulation"

ピボットテーブルの設定をいろいろ変更し、表がどのようにかわるか確認してください。

集計する項目の変更

現在のピボットグラフを変更して、 別の角度からデータを集計・分析することができます。 それには、表にしている項目を変更します。

たとえば「担当者を基準に、各商品の売り上げ状況を調べる」ことにしましょう。 それには、次のように操作します。

1. ピボットテーブルから「販売先」のボタンを、マウスでドラッグし表の外に出します（マウスポインタが「×」にかわります）。
2. 「ピボットテーブルのフィールド」ウィンドウ（縦長の小さなウィンドウ）から、 「品物」をクリックし、表の「販売先」があった場所（「合計／金額」の右）までドラッグします。

   #ref(): File not found: "excel14.png" at page "2009/11th/cross_tabulation"

このように、行の項目を変更することができます。 ほかに「列」（担当者）や「データ」（金額）や「ページ」（日付）の各部分も、 使用するデータの項目を変更することができます。

#ref(): File not found: "excel15.png" at page "2009/11th/cross_tabulation"

項目をいろいろ変更して、どのように表が変わるか確認してください。 また、行や列などの各部分には、２つ以上の項目を設定することもできますので、確認してください。

データのグループ化

上の図の状態では、日付（年月日）ごとの集計しかできません。 このような集計では、細かいデータの動きはわかっても、大まかな動きはわかりにくくなります。

そこで、日付を月単位でまとめることにしましょう。 つまり「月ごとの集計」を表示させます。

1. ピボットテーブルの日付の列の中（どこでもよい）を右クリック
2. メニューから「グループと詳細の表示」→「グループ化」を選択

   #ref(): File not found: "excel16.png" at page "2009/11th/cross_tabulation"

3. 「グループ化」のウィンドウで、「単位」を「月」に設定

   #ref(): File not found: "excel17.png" at page "2009/11th/cross_tabulation"

4. 「OK」ボタンをクリックすると、月ごとの集計に変わる

   #ref(): File not found: "excel18.png" at page "2009/11th/cross_tabulation"

次へ進んでください。