ピボットテーブルを使った代表的な集計表レイアウトの作り方 [ピボットテーブル基礎]

Aさん

ピボットテーブルを使い始めましたが、なかなか思い通りに集計表をつくれません。。
何かコツってありますか？

森田

ピボットテーブルに慣れないうちは確かにどう設定すれば、どういう集計表をつくれるかイメージ湧きませんね。

では、今回は実務で代表的な集計表をピボットテーブルで作成する方法を教えます！

では、詳細を解説していきますね。

解説動画：【ピボットテーブル#3】実務で代表的な集計表レイアウト4ケースの作り方 - 主要レイアウトを作る際の集計条件の設定方法を理解する

この記事の内容は下記の動画でも解説しています。

はじめに

本題に入る前に、この記事がおすすめな方を挙げてみます。

前提条件

この記事はピボットテーブルの概要を理解していることが前提です。
→まず、以下の記事で概要を把握することをおすすめします。

ピボットテーブルの集計表は「行」・「列」ボックスの組み合わせパターン次第

ピボットテーブルの集計表（ピボットテーブルレポート）の大前提ですが、「ピボットテーブルのフィールド」ウィンドウ下部のエリアセクションにある4つのボックスの設定に応じて自動的に作成されます。

この際、「値」ボックスは合計や個数などを集計する上で設定がマストですが、集計表のレイアウトについては「行」ボックスと「列」ボックスの2つが大きく関係します。

「行」・「列」ボックスの役割は、「値」ボックスに設定したデータを集計表上でどんな切り口で内訳を見せるのかを設定することだからです。

ピボットテーブルに不慣れなうちは、この「行」・「列」ボックスの設定に応じて、どんな集計表のレイアウトになるかのイメージが湧かないという方は非常に多いです。

なので、まずは実務で代表的な集計表レイアウトをピボットテーブルでつくる際のパターンを覚えてしまいましょう。

この基本を押さえておけば、あとは自分で好みの集計表をピボットテーブルで作成できるようになるはずです。

実務で代表的な4種類の集計表レイアウトの作り方

では、実務で代表的な4種類の集計表をピボットテーブルで作成したものを順番に解説していきます。

ちなみに、分かりやすいように集計表と「行」・「列」ボックスには同じ色をつけておきましたので、どのように連動しているかを確認してくださいね。

【パターン1】単純集計表

1つ目は、もっともオーソドックスな「単純集計表」です。

この集計表は、フィールドが1種類のみであり、一般的には縦軸（縦方向）に並べることが多いです。

この場合、「行」ボックスに切り口としたいフィールド（元データの列）を設定しましょう。

【パターン2】クロス集計表

2つ目は、ビジネスで頻出な「クロス集計表」です。

この集計表は、フィールドが2種類あり、縦軸と横軸でクロスしている表を指します。

この場合、「行」ボックスと「列」ボックスの両方にそれぞれ切り口としたいフィールドを設定しましょう。

ちなみに、「行」ボックスは集計表の縦軸、「列」ボックスは集計表の横軸となります。

集計表の縦軸・横軸と「行」・「列」ボックスの位置が同じだとイメージできると、よりスピーディーにピボットテーブルで集計表を作成できますよ！

上記の「列」ボックスは「受注日」フィールドを設定しておりますが、「月」というフィールドが自動的に生成されています。
時系列のデータの場合、Excel上で自動的に日単位のデータを月単位にグループ化してくれるためです。

【パターン3】階層集計表

続いて3つ目は、「階層集計表」です。

この集計表は、縦軸か横軸一方に、フィールドが２種類以上ある形式の表です。

この場合、「行」・「列」ボックスのいずれに階層的に表現したいフィールドを設定しましょう。

ちなみに、ボックス内に設定したフィールドの並び順で親子関係が決まります。

ボックス内で上にある方が集計表上でも上位の階層となるため、ボックス内のフィールドの並び順をちゃんと確認しましょう。

【パターン4】多重クロス集計表

最後に4つ目は、「多重クロス集計表」です。

この集計表は、クロス集計表と階層集計表の複合型ですね。

縦軸と横軸でクロスしており、かつ縦軸・横軸のいずれか、または両方が階層的な状態です。

この場合、「行」ボックスと「列」ボックスの両方に任意のフィールドを複数設定しましょう。

また、階層関係が正しくなるよう、それぞれのボックス内のフィールドの並び順もご注意ください。

集計表を作成する際の3つの留意点

上記4パターンで、ある程度ピボットテーブルで集計表をつくる際の「行」・「列」ボックスの設定方法がイメージできてきたと思います。

あとは、ピボットテーブルに限らず、集計表全般で言えることではありますが、以下3つの留意点を踏まえて、ピボットテーブルを活用していきましょう。

①の補足ですが、一般的にPCは縦スクロールされることが前提に設計されています。
（マウスのホイールや、WEBサイトの構成など）

これは、人の認知的にも上から下へ情報を見た方が理解しやすいという特性を考慮されたものであるため、集計表についても原則縦方向で整理した方が良いです。

③は、特に階層集計表や多重クロス集計表を作成する時に注意しましょう。

集計表は、フィールドの数に比例してどんどん大きくなり、「見やすさ」が損なわれていきます。

つまり、情報量と「見やすさ」はトレードオフの関係なのです。

よって、必要のないフィールドは極力省きましょう。

場合によっては、集計表を分割するなどした方が、結果的には見やすく分かりやすく情報を整理できますよ。

さいごに

いかがでしたでしょうか？

この記事を参考に、ぜひ実務でピボットテーブルを試していろいろな集計表をつくれるようになりましょう。

集計表を自由自在につくれるようになると、今まで以上にピボットテーブルの便利さを体感できますよ！

なお、ピボットテーブルの各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。

ピボットテーブルも関数もぜんぶ使う! Excelでできるデータの集計・分析を極めるための本

森田 貢士 (著)

Amazon

Kindle

楽天

ご参考になれば幸いですm(_ _)m

森田

ピボットテーブルを覚えると、突発的な集計作業がめちゃくちゃ早くなります。

また、さまざまな角度・切り口からデータを分析することも可能です。

データを扱う機会が多い方こそ、ぜひピボットテーブルを活用していきましょう！