【Googleスプレッドシート】クロス集計表の作成!ピボットテーブルでの2軸表示

データを縦横に集計して分析したいとき、クロス集計表はとても便利です。しかし、項目が増えるとどうしても表が見づらくなり、1つの軸だけでは傾向がつかみにくくなることがあります。そこで役立つのが、ピボットテーブルを使った2軸表示の方法です。この記事では、Googleスプレッドシートでピボットテーブルを作成し、行と列に複数のフィールドを配置して2軸でデータを可視化する手順を詳しく解説します。

ピボットテーブルでクロス集計と2軸表示ができる仕組み

ピボットテーブルは、元のデータを「行」「列」「値」の3つの領域にフィールドを割り振ることで、データを動的に集計する機能です。クロス集計とは、行方向と列方向の2つのカテゴリでデータを分類し、その交点に集計値を表示する表のことです。2軸表示とは、このクロス集計表において、行または列に複数のカテゴリを積み重ねたり、値の集計方法を変えたりすることで、複数の視点からデータを見られるようにする状態を指します。たとえば、月別の売上と利益率を同じ表に表示する場合、月を行軸に、売上と利益率を2つの値として並べることで、1つの表で両方の推移を確認できます。ピボットテーブルは、フィールドをドラッグ&ドロップするだけで、直感的に2軸表示を実現できる点が大きなメリットです。

基本的なピボットテーブルでクロス集計表を作成する手順

まずは、シンプルなクロス集計表を作成する手順を説明します。サンプルデータとして、商品カテゴリ、販売月、売上金額の3列があるデータを想定します。

1. データ範囲を選択する
   元データのセル範囲をマウスでドラッグして選択します。ヘッダー行を含めることがポイントです。
2. ピボットテーブルを挿入する
   メニューの「データ」から「ピボットテーブル」をクリックします。表示されるダイアログで、新しいシートに作成するか、現在のシートの指定セルに作成するかを選びます。今回は「新しいシート」を選択して作成します。
3. 行と列のフィールドを設定する
   画面右側にピボットテーブルエディタが表示されます。「行」に「商品カテゴリ」、「列」に「販売月」をドラッグ&ドロップします。すると、表の左端に商品カテゴリ、上部に販売月が並びます。
4. 値フィールドを設定する
   「値」に「売上金額」をドラッグします。デフォルトでは「SUM(売上金額)」として集計されます。これで、各カテゴリと各月の売上合計が交差セルに表示されるクロス集計表が完成します。

値フィールドの集計方法を変更する

値フィールドの集計方法(合計、平均、カウントなど)は、フィールド名の横にある歯車アイコンから変更できます。たとえば売上の平均を表示したい場合は、「売上金額」のドロップダウンから「平均」を選択します。

値フィールドを複数追加して2軸表示を実現する

2軸表示の最も簡単な方法は、値フィールドを複数追加することです。たとえば、売上金額に加えて「利益額」の列がある場合、値フィールドに「利益額」もドラッグすると、表の右側に利益額の列が追加されます。これにより、行方向に商品カテゴリ、列方向に月とその下に売上・利益という2階層の表示ができ、異なる指標を同時に比較できます。

ピボットテーブルでより高度な2軸表示を行う手順

さらに高度な2軸表示として、行や列に複数のフィールドを積み重ねたり、計算フィールドを使ったりする方法があります。

1. 行または列にサブカテゴリを追加する
   たとえば、行に「商品カテゴリ」を設定した後、さらに「商品名」を行にドラッグします。すると、商品カテゴリごとに商品名が折りたたんで表示され、行方向に2軸の階層ができます。列方向も同様に、月の下に「四半期」など別のフィールドを追加できます。
2. 計算フィールドを追加して新しい指標を作る
   ピボットテーブルエディタの「値」の下にある「計算フィールド」をクリックします。ここで、既存のフィールドを使って数式を作成できます。たとえば「利益率」という計算フィールドを「=利益額/売上金額」と定義すれば、自動で利益率が計算され、元の値を変更するだけで計算フィールドも更新されます。これにより、売上合計と利益率を同時に表示する2軸表が作れます。
3. フィルタを使って特定のデータに絞り込む
   ピボットテーブルエディタの「フィルタ」領域にフィールドをドラッグすると、表示するデータを条件で絞り込めます。たとえば、特定の地域だけのデータを表示したい場合に使います。フィルタを適用した状態でも、2軸表示はそのまま維持されます。

2軸表示で注意すべきポイントとよくある失敗

データの形式が不統一で正しく集計されない

元データのセルに数値と文字列が混在していると、ピボットテーブルが正しく集計できません。特に、数値が文字列として入力されている場合、SUMやAVERAGEが機能しません。データを追加する前に、すべての数値セルが数値形式になっているか確認してください。日付も日付形式で統一することが重要です。

2軸にするために無理にフィールドを追加すると表が複雑になる

行や列にあまり多くのフィールドを追加すると、表が横長や縦長になりすぎて逆に見づらくなります。2軸表示は最大でも3階層程度にとどめ、必要に応じてフィルタやスライサーを使ってデータを絞り込むほうが効果的です。また、値フィールドを2つ以上追加する場合は、列の幅が狭くなり数字が見切れることがあるので、適宜列幅を調整してください。

ピボットテーブルが更新されない

元データを追加・変更しても、ピボットテーブルは自動で更新されません。更新するには、ピボットテーブル内のセルを右クリックし、「更新」を選択するか、メニューの「データ」から「ピボットテーブルを更新」を実行します。また、元データをテーブル形式に変換しておくと、データ追加時に範囲が自動拡張されるため便利です。

計算フィールドが正しく計算されない

計算フィールドの数式で参照するフィールド名は、元データのヘッダーと完全に一致する必要があります。スペル違いや余計なスペースがあるとエラーになります。また、0で割るような計算がある場合はエラーが発生するため、IF関数などで回避してください。

ピボットテーブルとQUERY関数のクロス集計比較

まとめ

この記事では、Googleスプレッドシートでピボットテーブルを使ってクロス集計表を作成し、2軸表示にする方法を解説しました。行・列・値にフィールドを配置する基本操作をマスターすれば、複数のカテゴリや指標を1つの表で比較できるようになります。さらに、計算フィールドやフィルタを組み合わせることで、より深いデータ分析が可能です。ぜひ実際のデータで試してみてください。また、QUERY関数との併用も検討すると、さらに柔軟な集計が実現できます。