2007年度経済情報処理

第8回 ピボットテーブルを使ったデータの整理　

第8回の目標

↑経済情報処理（2007）ホームページに戻る

1 簡単なクロス表の例

表1（原データ）は、あるグループについて、各メンバーの性別と文系・理系の別を調べたものである。このデータから、「男性と女性では文系・理系の選択に差がある」かどうかをチェックしたい。

表１. 原データ

名前 性別 文系・理系の別
太郎 男性 文系
花子 女性 理系
かな 女性 理系
じん 男性 文系
さくら 女性 文系
一郎 男性 理系

そこで、縦に文系・理系の別を取り、横に男性・女性の別を取った以下のような表を作成してみる。

人数 性別
女性 男性
コース 文系 ① ②
理系 ③ ④

表中①に入るのは「女性」かつ「文系」の人なので、表1をその条件でチェックすると該当するのは｛さくら｝である。同様に②は「男性」かつ「文系」であるから｛太郎、じん｝が該当、③は｛花子、かな｝、④は｛一郎｝であることがわかる。ここでは個別の名前ではなく人数だけ知りたいので、人数を書き込むと、

人数 性別
女性 男性
コース 文系 1 2
理系 2 1

となることがわかった。このような表をクロス表と呼ぶ。クロス表を見ると、女性は理系が文系の倍、男性は文系が理系の倍いるので、このグループについては男女でコース選択で差があるように見える（厳密には検定が必要だが、この講義の範囲を超える。興味があれば統計関係の講義を履修すること）。

練習問題 1. 以下のデータ（表2）から縦に居住地、横に性別をとった人数のクロス表を手作業で作成してみなさい

表2. 居住地・性別・年収

居住地 性別 年収（万円）
中区 女 176
神奈川区 女 579
西区 女 623
中区 女 487
中区 男 271
鶴見区 男 436
神奈川区 男 816
神奈川区 男 854
神奈川区 女 244
西区 女 666

2 ピボットテーブルを作るためのデータ入力

Excelでピボットテーブルを作成する場合、

  1. 1系列のデータはワークシートの列方向に（たとえば、A列は品目、B列は売上個数といったように）並んでいること
  2. 各系列のデータ範囲の先頭行は、その系列の名前を表す文字列（フィールド名）が入っていること
  3. 行はデータの1件に対応していること。たとえば 3行目に入っているデータは全てある特定のデータ（レコード）に相当すること

が大前提。たとえば下の図で言えば、D列に入っているデータは全て販売数のデータであって、店舗コードなどが混じっていないことが1の意味になる。また、2は、D列を代表する名前が「売上個数」であることを、3は8行目に入っているデータは全て4月に神大前店で売れた「きつねはクール」に関するデータであり、そのほかの行のデータと入り交じっていないことを意味する。

ピボットテーブルはデータをフィールド名で管理するため、フィールド名の名前をどう付けるかは作業効率上重要となる。注意点は以下の通り。

Tips: 3D集計とピボットテーブル

例題1. simple-pivot.xlsのシート 売上個数 に入っているデータから、店舗と商品の売上個数に関するクロス表を作る

ピボットテーブルを作成するには？　→ Excel編 Q53

Step1. ピボットテーブルウィザードを起動する

ピボットテーブルを作成する場合は、[データ(D)]-[ピボットテーブルとピボットテーブルグラフレポート(P)]を選択する

Step2. データのある場所と作成するレポートの種類を指定する

今回は Excelの中に入力済みのデータを用いるので、データのある場所としては「Excelのリスト/データベース(M)」を選択。レポートの種類としては、「ピボットテーブル(T)」を選ぶ

Step3. データの範囲を指定する

Step2.で、Excelの中に入力済みのデータを使うことにしたので、データが入っている範囲をここで指定する。この際、データのフィールド名が入っている行も忘れずに範囲に含めること。具体的にはA1:D48の範囲

Step 4. 作成場所を指定する

既存ワークシートの一部に作成することもできるが、ピボットテーブルは意外に大きくなったりするので新しいワークシートに作成しておいた方が無難。

Step 5. 集計に使う縦横の属性フィールドと、集計用のフィールドを指定する

Step4. まで終わると、新しいシートに下のようなクロス表のひな形と、指定した範囲に含まれているフィールドが表示される。

ここで作成したいクロス表は店舗名と商品名が縦横の属性、それぞれの属性ペアを満たす販売個数の合計が集計結果となればよいので、

  1. 「商品名」を「ここに行のフィールドをドラッグします」の領域にドラッグ&ドロップ
  2. 「店舗名」を「ここに列のフィールドをドラッグします」にドラッグ＆ドロップ
  3. 「販売個数」を「ここにデータアイテムをドラッグします」にドラッグ＆ドロップ

すればよい。

Step 6. できあがり

できあがりはこんな感じ。この集計表は「月」のフィールドを使っていないので、各セルに入っている集計値は全期間の合計になっている。

Step 7. グラフにしてみる

ピボットテーブルからグラフを作成するには？　→ Excel編 Q57

ピボットテーブルで作成した表は、すぐグラフにすることができる。たとえば、商品ごとの売上を店舗の積み重ね棒グラフで表すとこんな感じ。

練習問題2. simple-pivot.xls　を使って以下のような図表を作ってみなさい

  1. 各商品が月ごとに全店舗で何個ずつ売れているかを合計した表
  2. 各店舗での月ごとの売上個数を合計した表
  3. 各店舗での月ごとの売上個数の変化を表すグラフ

例題2. ピボットテーブルでの集計方法を変える

例題1の例ではたまたま自動的に設定された集計方法（合計）が目的に合っていたのでそのままにしておいてよかったが、たとえば平均を計算したい場合は集計方法を変える必要がある。

simple-pivot.xlsを使って、店舗・商品ごとの月平均売上個数を求めるとこんな感じになる。

集計方法を変えるには？　→ Excel編　Q55

練習問題3. simple-pivot.xls　を使って以下のような表を作ってみなさい

  1. 各商品が月ごとに全店舗で何個ずつ売れているかの平均を計算した表
  2. 各店舗での月ごとの売上個数の平均を計算した表

例題3. 3つ以上の系列を使って分類し、集計する

ピボットテーブルは縦横の表なので分類に使える系列の数は自然に考えれば2つまでである。Excelで3つ以上の系列で分類する方法は2つある。

  1. ページを分ける（複数の表に分割する）
  2. 縦あるいは横の分類を、複数の系列の複合したものにする

ページを分ける場合は、下の図で「ここにページのフィールドをドラッグします」と書いてあるところにページ切替に使いたい系列を入れればいい。

たとえば、ここに月を入れると、

のようになる。最初は「すべて」になっているので、各月の合計であるが、

のようなメニューから月を選んでやると、その月のデータだけで集計を行う。たとえば5月を選ぶとこんな感じ。

系列を複合系列にしたい場合は、表の縦あるいは横の分類系列の上に、さらに系列名をドラッグ＆ドロップしてやればいい。「商品名」と「月」を組み合わせた場合はこんなかんじ。

練習問題4. immigration.xls のデータを使って以下のクロス表を作りなさい

immigration.xls は平成１８年版の「出入国管理」（法務省）から新規入国者について分析しやすい形式で入力したものである。このデータを使って以下の集計表を作成しなさい。

  1. 入国年×在留資格の表を作成しなさい
  2. 入国者数の降順で在留資格が並ぶように表を整形しなさい
  3. 在留資格のなかで、入国者数が上位5位までのものを選び、年を追ってどのように変化したかわかるグラフを作成しなさい

上位5位までのデータを昇順・降順に並べ替えて表示するには？　→ Excel編 Q56

練習問題5. cvs.xls のデータを使って以下のクロス表を作りなさい

  1. 商品名を縦、性別を横に取り、販売数の合計を計算する
  2. 店舗、性別ごとの売上額合計
  3. 店舗ごとの総売上額

練習問題6. cvs-db.xls　のデータを使って以下の作業をしなさい

ただし、

売上伝票　シートに入っているデータは、レシート毎の情報（伝票番号、日付、店舗コード、購入者の性別）
販売明細　シートに入っているデータは、個別商品毎の情報（明細番号、伝票番号、伝票内の順序、商品コード、数量）
店舗マスタ　と　商品マスタ　は、それぞれ店舗コード、商品コードから店舗名や商品名、価格を得るための表

となっている

※VLOOKUP関数を適宜使うこと。表頭あるいは表側で複数の属性を組み合わせて集計を行う場合は、複数のフィールド名を表頭あるいは表側部分にドロップすればよい。

  1. 表3のような日付・店舗・商品毎の売上額の表を作成しなさい
  2. 店舗と客の性別ごとの売上合計
  3. 店舗と客の性別ごとの来客数（伝票1枚が客一人に対応すると仮定）
  4. 店舗と客の性別ごとの客一人あたり平均売上額（伝票1枚が客一人に対応すると仮定）
  5. 日付・店舗ごとの売上総額の推移グラフ
  6. 客の性別と売れている商品に関係があるか否かをチェックするためのクロス表
  7. 曜日と売れている商品に関係があるか否かをチェックするためのクロス表（曜日は WEEKDAY という関数で計算できる）

表3. 日付・店舗・商品別売上一覧（部分）

日付 店舗コード 店舗名 商品コード 商品名 単価 数量 売上
2007/1/5 1 白楽駅前 1300 漬け物 200 1 200
1600 寿司 1000 1 1000
2 横浜西口 1000 豆腐 105 2 210
1300 漬け物 200 2 400
1600 寿司 1000 2 2000
1800 チーズ 600 1 600
1900 お茶 150 2 300
2000 炭酸飲料 150 2 300
3 渋谷東口 1200 納豆 150 1 150
5 新横浜北口 1300 漬け物 200 3 600
1600 寿司 1000 3 3000
1900 お茶 150 4 600
2100 酒 220 4 880
2007/1/6 1 白楽駅前 1000 豆腐 105 3 315
1100 こんにゃく 98 1 98
1300 漬け物 200 1 200
1600 寿司 1000 1 1000
1800 チーズ 600 2 1200
1900 お茶 150 3 450
2 横浜西口 1000 豆腐 105 2 210
1100 こんにゃく 98 1 98
1400 煮豆 245 1 245
1500 佃煮 500 1 500
1900 お茶 150 2 300
2000 炭酸飲料 150 2 300
2100 酒 220 1 220
3 渋谷東口 1200 納豆 150 1 150
1500 佃煮 500 2 1000
1700 かまぼこ 300 2 600
4 自由が丘駅前 1700 かまぼこ 300 2 600
5 新横浜北口 1000 豆腐 105 1 105
1200 納豆 150 2 300

練習問題7. pivot.xls を使って以下の作業を行いなさい

pivot.xls の　成績　シートに入っているデータは、ある高校での成績データである。内申平均点はこの高校の学区内にある 3つの中学校（ A, B, C ）での成績（相対評価）を平均したものになっている。科目毎の成績は高校で実施した試験の得点（絶対評価）を示している。

  1. 出身中学毎に、内申平均点の人数分布のグラフを、横軸を内申平均点、縦軸を人数とした縦棒グラフで作成しなさい
  2. 高校での5科目の平均得点を計算し、出身中学毎に平均点の人数分布のグラフを作成しなさい。ただし、平均得点分布は 1点階級（つまり整数）になるように関数を使って変換すること
  3. 高校で実施した試験結果が正しく学力を評価できていると仮定した場合、学区内の3中学の学力水準には差があると考えられるか。データを示して説明しなさい
まとめの問題
foods.xls は、あるチェーン店（店舗は横浜駅西口、白楽駅前、菊名、神大前にある）の各店長がまとめた2007年1月〜3月の売上月報である。このデータから以下の表やグラフを作成しなさい。
1. 店舗別の売上総額が、月ごとにどのように変化しているかを表すグラフ
2. 商品別、店舗別の３ヶ月間売上高合計表
3. 商品別の売上総額が、月ごとにどのように変化しているかを表すグラフ
4. 3ヶ月間の商品別合計売上が上位5番までに入っている商品と、各月の売上額が分かる表

↑経済情報処理（2007）ホームページに戻る

©2007, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.