準備問題data08.xlsx の コンビニ売上げ分析表 シートに、商品マスタ シートの情報から商品名を入れなさい。ただし、商品名の検索には、VLOOKUP関数を用いること。
表1（原データ）は、あるグループについて、各メンバーの性別と文系・理系の別を調べたものである。このデータから、「男性と女性では文系・理系の選択に差がある」かどうかをチェックしたい。
表１. 原データ
| 名前 | 性別 | 文系・理系の別 |
|---|---|---|
| 太郎 | 男性 | 文系 |
| 花子 | 女性 | 理系 |
| かな | 女性 | 理系 |
| じん | 男性 | 文系 |
| さくら | 女性 | 文系 |
| 一郎 | 男性 | 理系 |
そこで、縦に文系・理系の別を取り、横に男性・女性の別を取った以下のような表を作成してみる。
| 人数 | 性別 | ||
|---|---|---|---|
| 女性 | 男性 | ||
| コース | 文系 | ① | ② |
| 理系 | ③ | ④ | |
表中①に入るのは「女性」かつ「文系」の人なので、表1をその条件でチェックすると該当するのは｛さくら｝である。同様に②は「男性」かつ「文系」であるから｛太郎、じん｝が該当、③は｛花子、かな｝、④は｛一郎｝であることがわかる。ここでは個別の名前ではなく人数だけ知りたいので、人数を書き込むと、
| 人数 | 性別 | ||
|---|---|---|---|
| 女性 | 男性 | ||
| コース | 文系 | 1 | 2 |
| 理系 | 2 | 1 | |
となることがわかった。このような表をクロス表と呼ぶ。クロス表を見ると、女性は理系が文系の倍、男性は文系が理系の倍いるので、このグループについては男女でコース選択で差があるように見える（厳密には検定が必要だが、この講義の範囲を超える。興味があれば統計関係の講義を履修すること）。
練習問題 1. 以下のデータ（表2）から縦に居住地、横に性別をとった人数のクロス表を手作業で作成してみなさい
表2. 居住地・性別・年収
| 居住地 | 性別 | 年収（万円） |
|---|---|---|
| 中区 | 女 | 176 |
| 神奈川区 | 女 | 579 |
| 西区 | 女 | 623 |
| 中区 | 女 | 487 |
| 中区 | 男 | 271 |
| 鶴見区 | 男 | 436 |
| 神奈川区 | 男 | 816 |
| 神奈川区 | 男 | 854 |
| 神奈川区 | 女 | 244 |
| 西区 | 女 | 666 |
できあがりのイメージ
| 人数 | 性別 | ||
| 男 | 女 | ||
| 居住地 | 神奈川区 | ||
| 鶴見区 | |||
| 中区 | |||
| 西区 | |||
が大前提。たとえば下の図で言えば、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. まで終わると、新しいシートに下のようなクロス表のひな形と、指定した範囲に含まれているフィールドが表示される。
ここで作成したいクロス表は店舗名と商品名が縦横の属性、それぞれの属性ペアを満たす販売個数の合計が集計結果となればよいので、
すればよい。
Step 6. できあがり
できあがりはこんな感じ。この集計表は「月」のフィールドを使っていないので、各セルに入っている集計値は全期間の合計になっている。
Step 7. グラフにしてみる
ピボットテーブルからグラフを作成するには？ → Excel編 Q57
ピボットテーブルで作成した表は、すぐグラフにすることができる。たとえば、商品ごとの売上を店舗の積み重ね棒グラフで表すとこんな感じ。
練習問題2. data08.xlsx の 売上個数 シートに入っているデータを使って以下のような図表を作ってみなさい
例題2. ピボットテーブルでの集計方法を変える
例題1の例ではたまたま自動的に設定された集計方法（合計）が目的に合っていたのでそのままにしておいてよかったが、たとえば平均を計算したい場合は集計方法を変える必要がある。
data08.xlsx の 売上個数 シートを使って、店舗・商品ごとの月平均売上個数を求めるとこんな感じになる。
集計方法を変えるには？ → Excel編 Q55
練習問題3. data08.xlsx の 売上個数 シートを使って以下のような表を作ってみなさい
例題3. 3つ以上の系列を使って分類し、集計する
ピボットテーブルは縦横の表なので分類に使える系列の数は自然に考えれば2つまでである。Excelで3つ以上の系列で分類する方法は2つある。
ページを分ける場合は、「ピボットテーブルのフィールドリスト」で「フィルタ」のボックスページ切替に使いたい系列を入れればいい。
たとえば、ここに月を入れると、
のようになる。最初は「すべて」になっているので、各月の合計であるが、
のようなメニューから月を選んでやると、その月のデータだけで集計を行う。たとえば5月を選ぶとこんな感じ。
系列を複合系列にしたい場合は、表の縦あるいは横の分類系列の上に、さらに系列名をドラッグ＆ドロップしてやればいい。「商品名」と「月」を組み合わせた場合はこんなかんじ。
練習問題4. data08.xlsx 新規入国者数 シートのデータを使って以下のクロス表を作りなさい（☆）
data08.xlsx 新規入国者数 シートは平成１８年版の「出入国管理」（法務省）から新規入国者について分析しやすい形式で入力したものである。このデータを使って以下の集計表を作成しなさい。
上位5位までのデータを昇順・降順に並べ替えて表示するには？ → Excel編 Q49
練習問題5. data08.xlsx コンビニ売上分析表 シートのデータを使って以下のクロス表を作りなさい
※準備問題をやっていない場合は、商品マスタシートとVLOOKUP関数を使って商品名を入力する必要がある
練習問題6. data08.xlsx のデータを使って以下の作業をしなさい（☆）
※表頭あるいは表側で複数の属性を組み合わせて集計を行う場合は、複数のフィールド名を表頭あるいは表側部分にドロップすればよい。
練習問題7. data08.xlsx 成績 シートのデータを使って以下の作業を行いなさい
data08.xlsx 成績シートに入っているデータは、ある高校での成績データである。内申平均点はこの高校の学区内にある 3つの中学校（ A, B, C ）での成績（相対評価）を平均したものになっている。科目毎の成績は高校で実施した試験の得点（絶対評価）を示している。
©2015, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.