この記事について
Googleスプレッドシートなどの表計算アプリを使っていると、データを様々な角度から分析したくなることがあると思います。ピボットテーブルを使うと素早く簡単にデータを分析することができます。
ピボットテーブルとは
データを集計して表にしてくれる機能です。
データに対してどういった切り口で集計するかを指定するだけで集計表を作ってくれます。
データの要約、フィルタリング、クロス集計をしてくれます。
ピボットPIVOTなので読み間違えに気を付けましょう。
注意点
この後説明するサンプルデータのようにデータが整っていることが大事です。データの形式がバラバラであったり、入っているデータに整合性がないと上手く集計することはできません。
サンプルデータの説明
ピボットテーブルは実際に触ってみるとすぐに理解できると思います。そのためにサンプルデータを用意しました。
サンプルデータは学力テストの成績を表したものです。
データは次の通りです。
- クラスは3年1組・3年2組・3年3組の3クラス
- クラスに3人ずつで全部で9人
- 科目は国語・数学・英語の3科目
- テストは2023/4/1と2023/6/1の2回
- 点数は100点満点
サンプルデータ
スプレッドシートにコピペで貼り付けることができます。
日付 クラス 名前 科目 点数
2023/04/01 3年1組 Aさん 国語 77
2023/04/01 3年1組 Aさん 数学 80
2023/04/01 3年1組 Aさん 英語 90
2023/04/01 3年1組 Bさん 国語 46
2023/04/01 3年1組 Bさん 数学 51
2023/04/01 3年1組 Bさん 英語 74
2023/04/01 3年1組 Cさん 国語 77
2023/04/01 3年1組 Cさん 数学 86
2023/04/01 3年1組 Cさん 英語 65
2023/04/01 3年2組 Dさん 国語 1
2023/04/01 3年2組 Dさん 数学 93
2023/04/01 3年2組 Dさん 英語 14
2023/04/01 3年2組 Eさん 国語 71
2023/04/01 3年2組 Eさん 数学 36
2023/04/01 3年2組 Eさん 英語 68
2023/04/01 3年2組 Fさん 国語 8
2023/04/01 3年2組 Fさん 数学 23
2023/04/01 3年2組 Fさん 英語 1
2023/04/01 3年3組 Gさん 国語 75
2023/04/01 3年3組 Gさん 数学 84
2023/04/01 3年3組 Gさん 英語 14
2023/04/01 3年3組 Hさん 国語 71
2023/04/01 3年3組 Hさん 数学 32
2023/04/01 3年3組 Hさん 英語 0
2023/04/01 3年3組 Iさん 国語 85
2023/04/01 3年3組 Iさん 数学 12
2023/04/01 3年3組 Iさん 英語 37
2023/06/01 3年1組 Aさん 国語 44
2023/06/01 3年1組 Aさん 数学 21
2023/06/01 3年1組 Aさん 英語 34
2023/06/01 3年1組 Bさん 国語 100
2023/06/01 3年1組 Bさん 数学 49
2023/06/01 3年1組 Bさん 英語 89
2023/06/01 3年1組 Cさん 国語 90
2023/06/01 3年1組 Cさん 数学 86
2023/06/01 3年1組 Cさん 英語 89
2023/06/01 3年2組 Dさん 国語 69
2023/06/01 3年2組 Dさん 数学 88
2023/06/01 3年2組 Dさん 英語 53
2023/06/01 3年2組 Eさん 国語 83
2023/06/01 3年2組 Eさん 数学 44
2023/06/01 3年2組 Eさん 英語 75
2023/06/01 3年2組 Fさん 国語 16
2023/06/01 3年2組 Fさん 数学 95
2023/06/01 3年2組 Fさん 英語 32
2023/06/01 3年3組 Gさん 国語 68
2023/06/01 3年3組 Gさん 数学 94
2023/06/01 3年3組 Gさん 英語 85
2023/06/01 3年3組 Hさん 国語 51
2023/06/01 3年3組 Hさん 数学 73
2023/06/01 3年3組 Hさん 英語 67
2023/06/01 3年3組 Iさん 国語 19
2023/06/01 3年3組 Iさん 数学 22
2023/06/01 3年3組 Iさん 英語 81
サンプルデータの分析
このサンプルデータをどのような切り口で分析したくなるでしょうか？
- 平均点の高い科目は何だろう？
- 平均点の高いクラスはどこだろう？
- 1回目のテストと2回目のテストで平均点は上がったのか？
等々
ピボットテーブルを作ってみよう！
まず新規のスプレッドシートを用意してサンプルデータを貼り付けましょう。
ピボットテーブルを作成
メニュー、挿入、ピボットテーブル
データ範囲、挿入先はそのままで作成ボタンを押します。
画面説明：左側がピボットテーブル、右側がピボットテーブルエディタ
これで準備完了です。
右側のピボットテーブルエディタで設定を行うことで、左側のピボットテーブルに集計表が自動で作成されます。
分析してみよう！
「平均点の高い科目は何だろう？」を見てみましょう。
右側のピボットテーブルエディタを設定することで、左側のピボットテーブルに集計結果が表示されます。
行に科目を追加
左側のピボットテーブルが次のような表示に変わります。
値に点数を追加します。
値の集計をaverageに変更
これで平均点の高い科目が一目瞭然です。
さらに列にクラスを追加します。
「平均点の高いクラスはどこだろう？」がわかるようになります。
ここで3年2組の英語の平均点が低いことに気付いたとします。
C列3行目のセルをダブルクリックすると…
別シートにその内容がコピーされます。このシートを見ると1点しか取れていない人がいることがわかります。
「1回目のテストと2回目のテストで平均点は上がったのか？」を知るにはどうすれば良いでしょうか。
シートが切り替わっていると思いますので「ピボット テーブル 1」のシートに戻ります。
ビボットテーブルエディタで次のように設定します。
行に指定されている科目を削除
代わりに日付を追加
平均点数が上がったクラスや下がったクラスを確認することができます。
フィルタについて
集計の仕方はそのままで、対象とするデータをフィルタすることができます。
科目をフィルタへドラッグします。
現在のフィルタの下の「すべての項目を表示しています」をクリックします。
次のように科目から数学を外してみます。
するとピボットテーブルの方は数学が除かれた集計結果になります。
グラフにしてみる
ピボットテーブル上で範囲選択して
メニューからグラフを選ぶ
グラフも簡単（平均値をただ積み上げているだけですが…）
最後に
ピボットテーブルでは他にも総計に対する割合を表示できたり、行や列を複数指定することができたりと多機能です。また同様の機能がエクセルにも備わっています。（こちらの方が元祖かな）
ピボットテーブルはとても便利な機能なのですが、私の周りでは案外知らない人が多かったので紹介しておこうと思いました。