神奈川大学 経済学部
2010年度経済情報処理

第16課 ピボットテーブルを使ったデータ整理

第16課の目標

↑経済情報処理(2010)ホームページに戻る

準備問題
data08.xlsx の コンビニ売上げ分析表 シートに、商品マスタ シートの情報から商品名を入れなさい。ただし、商品名の検索には、VLOOKUP関数を用いること。

1 簡単なクロス表の例

表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. data08.xlsx売上個数 シートに入っているデータから、店舗と商品の売上個数に関するクロス表を作る

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

Step1. ピボットテーブルの作成を起動する

ピボットテーブルを作成する場合は、[挿入]タブから[テーブル]グループの[ピボットテーブル]ボタンをクリックして、[ピボットテーブル]を選択する。

Step2. データのある場所とテーブルの作成場所を指定する

今回は Excelの中に入力済みのデータを用いるので、データのある場所としては「テーブルまたは範囲を選択」を使う。また、ピボットテーブルを作る場所は
「新規ワークシート」にしておく。

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

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

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

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

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

  1. 「商品名」を「行ラベル」のボックスにドラッグ&ドロップ
  2. 「店舗名」を「列ラベル」のボックスにドラッグ&ドロップ
  3. 「販売個数」を「値」のボックスにドラッグ&ドロップ

すればよい。

Step 6. できあがり

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

Step 7. グラフにしてみる

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

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

練習問題2. data08.xlsx売上個数 シートに入っているデータを使って以下のような図表を作ってみなさい

  1. 各店舗での月ごとの売上個数を合計した表
  2. 各店舗での月ごとの売上個数の変化を表すグラフ

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

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

data08.xlsx売上個数 シートを使って、店舗・商品ごとの月平均売上個数を求めるとこんな感じになる。

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

練習問題3. data08.xlsx売上個数 シートを使って以下のような表を作ってみなさい

  1. 各店舗での月ごとの売上個数の平均を計算した表

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

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

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

ページを分ける場合は、「ピボットテーブルのフィールドリスト」で「フィルタ」のボックスページ切替に使いたい系列を入れればいい。

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

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

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

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

練習問題4. data08.xlsx 新規入国者数 シートのデータを使って以下のクロス表を作りなさい

data08.xlsx 新規入国者数 シートは平成18年版の「出入国管理」(法務省)から新規入国者について分析しやすい形式で入力したものである。このデータを使って以下の集計表を作成しなさい。

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

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

練習問題5. data08.xlsx コンビニ売上分析表 シートのデータを使って以下のクロス表を作りなさい

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

※準備問題をやっていない場合は、商品マスタシートとVLOOKUP関数を使って商品名を入力する必要がある

練習問題6. data08.xlsx のデータを使って以下の作業をしなさい

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

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

練習問題7. data08.xlsx 成績 シートのデータを使って以下の作業を行いなさい

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

  1. 出身中学毎に、内申平均点の人数分布のグラフを、横軸を内申平均点、縦軸を人数とした縦棒グラフで作成しなさい
  2. 高校での5科目の平均得点を計算し、出身中学毎に平均点の人数分布のグラフを作成しなさい。ただし、平均得点分布は 1点階級(つまり整数)になるように関数を使って変換すること
  3. 高校で実施した試験結果が正しく学力を評価できていると仮定した場合、学区内の3中学の学力水準には差があると考えられるか。データを示して説明しなさい
確認問題
data08.xlsx 月間売上 シートのデータを使って、以下の作業を行いなさい。
1. 縦に店舗名、横に年月をとって、店舗毎の月間売上合計の表をピボットテーブルを使って作成し、月による売上の変動が一番大きな店舗を探しなさい。
2. 縦に商品名、横に店舗名を取って、店舗、商品ごとの月間売上合計の表をピボットテーブルを使って作成しなさい。更に、各店舗の総売上額に占める各商品の割合を計算し、店舗ごとに売れ筋商品に差があるかどうか検討しなさい。

↑経済情報処理(2010)ホームページに戻る