ピボットテーブルを使ってみる

はじめに

Excelの機能として、存在は知っていたけれど全く活用できていなかったピボットテーブル。
最近こちらを使った作業を経験し、使いこなせればとても便利なツールだということが分かったので、まとめてみたいと思います。

ピボットテーブルとは

ピボットテーブルは、Excel「挿入」タブの一番左、「テーブル」のくくりにあります。

Excel側の説明の通り、複雑なデータを様々な条件で集計し、可視化するのに向いているツールです。
さらにそこからグラフの作成も可能。フィルターを付与し、条件に応じたグラフを表示させることもできます。（※今回はグラフについては触れません）

ピボットテーブルのメリット

先述のとおり、「複雑なデータを扱うことができる」「様々な条件で素早く集計できる」というところが大きなメリットではないかと感じています。
計算式やフィルターなどを設定することなく、感覚的に操作できるのがピボットテーブルの特長です。

次項から、実際に操作しながらそのメリットをお伝えできればと思います。

ピボットテーブルの作成

たとえば、こんなデータがあったとします。

※データはでたらめです。このサイトを利用させていただきました。
https://tm-webtools.com/Tools/TestData

テストデータは、いろんなECサイトに店舗を持つWEBショップのユーザー情報とします。
基本のユーザー情報のほかに、ユーザー登録をした日付、どのショップから購入しているか、そして購入回数に基づくランクの情報が付加されています。

まずはピボットテーブルを作成してみましょう。
「挿入」＞「ピボットテーブル」を選んで、集計したい範囲を選択します。

そうすると、こんな画面が出てきます。

上の①（フィールド）が、集計に使うことができる条件です。元データの列のヘッダが表示されているのが分かります。
下の②は完成する表の構成情報になっています。「行」「列」はそのまま行と列の条件になり、「フィルタ」は条件でしぼることができる機能、「値」は集計結果の値になります。個数や合計など、集計に使う値を選ぶことが可能です。
①から②にフィールドをドラッグ＆ドロップすることで、集計条件を設定していきます。

…と、言葉で説明すると分かりにくいので、実際に見てみましょう。
たとえば、各ショップからのユーザー登録数を調べてみます。
今回知りたいのは「購入ショップ」からのユーザー登録の数なので、設定はこんな感じになります。

IDはユーザーごとに一意なので、IDの個数が分かれば登録数が分かります。

では、どんな表が作成されたか見てみましょう。

「行」に設定した「購入ショップ」が行ラベルとして表示され、「値」に設定した「個数/ID」が、それぞれの行に対する結果として表示されています。
行ラベルを「その他の並べ替えオプション」で、「個数/ID」降順で並べ替えれば、

IDの個数が一番多いものから順に並べ替えることができ、「http://test.org」からの登録が一番多いことが分かります。

では、ここに「購入商品」の内訳の情報を加えてみましょう。
「列」に「購入商品」を追加します。

すると、表がこのように更新されました。

ここまで、集計したい条件の選択と、ドラッグ・クリックといったマウス操作だけで集計することができました。
フィルターで絞り込んでCOUNT関数を入れて…でできなくもないのですが、こちらの方が早いですね。
今何を集計しようとしているのかを視覚的に把握しながら集計できるので、ミスも減るのではないかなと思います。

では、少し条件を複雑にしてみます。

このWEBショップで、「最近ユーザー登録してくれたけれど、それほど頻繁に使ってくれているわけではない人」に対しての施策を考えたいとします。
どんな年齢層向けがいいのか、男性向け・女性向けどちらがいいのか、どんな商品がいいのかなどが分かったら嬉しいですね。

こんな条件でしぼってみました。

表はこんな感じになります。

「列」に設定した「年齢」がすべて表示されてしまっているので、少し見にくいですね。
そんなときは、「グループ化」を利用します。
「年齢」の表示された任意のセルの上で右クリックして、「グループ化」を選択。

するとこのようなウィンドウが出てきますので、

「単位」の欄にグループ化したい数値の幅を設定します。
たとえば、今回の場合は10歳単位でグループ化したいので、「10」を入力して「OK」を押すと、

こんな感じで、10歳単位で数値がまとまりました！

さて、改めて作成された表を見てみましょう。

「フィルター」に設定した「ランク」と「年（登録日）」は、通常のフィルター機能のように絞れるようになっています。
今回は「最近ユーザー登録してくれたけれど、それほど頻繁に使ってくれているわけではない人」が対象なので、ランク「D」、登録年が「2021年」で絞ってみます。
「（すべて）」の横の▼を押すと、こんな感じで選択肢が表示されます。

ここで「D」を選択、同様に「年」の方も「2021年」を選択してみると、

フィルターの内容に合わせて集計結果が更新されました！

ちょっとテストデータが少なすぎてリアリティのない数字になってしまいましたが、、
40代女性向けのウェアあたりが施策の対象になるでしょうか。

（※画像は「ピポットテーブルツール」の「デザイン」タブより「表形式」を選択しています。）

おわりに

結果が説得力に欠けましたが、ピボットテーブルによって集計したい内容から感覚的に集計作業を行うことができるのはお伝えできたかと思います。
途中で「この集計結果もほしい」「この条件でも集計したい」ということがあっても、条件の変更がドラッグ＆ドロップでできてしまうので非常に楽です。
短時間で集計結果にたどり着くことができるので、頻繁に集計作業がある場合にはとても利便性の高いツールではないでしょうか。
今回触れることができなかった機能もあるので、引き続き勉強していこうと思います。

Season 1