【Googleスプレッドシート】QUERY関数のPIVOT句!行→列の変換テクニック

QUERY関数は、スプレッドシートでデータベースライクな操作を行える強力な関数です。その中でもPIVOT句を使うと、縦に並んだデータを横方向に展開し、行と列を入れ替えた集計表を簡単に作成できます。たとえば、月ごとの売上データを各商品ごとに列に並べたい場合などに役立ちます。この記事では、PIVOT句の基本構文から実践的な使用例、注意点までを詳しく解説します。

PIVOT句が行データを列見出しに変換する仕組み

QUERY関数は、SQLライクなクエリ言語でデータを操作します。PIVOT句は、指定した列のユニークな値を新しい列見出しとして生成し、それに対応する集計値をセルに配置します。たとえば、商品名が列に並び、各月の売上額が行と列の交点に表示される形です。この処理には、集計関数(SUM, COUNT, AVERAGE, MAX, MINなど)が必ず必要です。PIVOT句は、GROUP BY句と似ていますが、グループ化の結果を列方向に展開する点が異なります。また、PIVOT句を使うには、SELECT句に集計関数を含める必要があり、変換対象の列は1つだけ指定できます。

QUERY関数のPIVOT句を使う具体的な手順

ここでは、実際のデータを用いてPIVOT句の操作手順を説明します。サンプルデータとして、A列に日付、B列に商品名、C列に売上金額が入力されていると仮定します。

1. データ範囲と構造を確認する
   まず、元データが縦持ち形式であることを確認します。各行が1つのレコードを表し、変換したいカテゴリ列(この例では商品名列)が含まれている必要があります。
2. QUERY関数の基本形を入力する
   結果を表示したいセルに=QUERY(と入力します。続けてデータ範囲、カンマ、ダブルクォーテーションで囲んだクエリ文、そしてヘッダー行数を指定します。
3. SELECT句と集計関数を指定する
   クエリ文の最初にSELECT句を書き、表示したい列と集計関数を指定します。例として、日付ごとに各商品の売上合計を求めるには、"SELECT A, SUM(C) PIVOT(B)"とします。ここでAは日付列、SUM(C)は売上合計、PIVOT(B)で商品名列を列見出しに変換します。
4. PIVOT句で変換する列を指定する
   PIVOT句の後ろに、列見出しにしたい列を指定します。上記の例ではB列です。この列のユニークな値がそれぞれ新しい列の見出しとなります。
5. 必要に応じてWHERE句やORDER BY句を追加する
   データを絞り込みたい場合はWHERE句、並び替えたい場合はORDER BY句を付け加えます。たとえば、売上1000以上のデータだけを対象にする場合は"SELECT A, SUM(C) PIVOT(B) WHERE C>=1000"とします。
6. 実行結果を確認する
   関数を確定(Enterキーを押す)と、指定した条件で行データが列に展開されたクロス集計表が表示されます。各セルには対応する売上合計が表示され、空白のセルは0または空白になります。

応用例:月ごとの集計

日付を月単位にグループ化したい場合は、日付列を月に変換します。たとえば、A列が日付(シリアル値)の場合、SELECT句でMONTH(A)を使えます。しかし、PIVOT句と組み合わせる際に注意が必要です。適切な例として、日付の月を抽出し、その月ごとに商品の売上合計を集計するには、"SELECT MONTH(A), SUM(C) PIVOT(B) GROUP BY MONTH(A)"とする必要があります。ただし、GROUP BY句はPIVOT句と同時に使うことができません。実際には、PIVOT句が暗黙的にGROUP BYを含むため、明示的なGROUP BYは不要です。正しい構文は、"SELECT MONTH(A), SUM(C) PIVOT(B)"です。MONTH関数で変換した値をそのままSELECTに指定します。

応用例:COUNT関数での利用

売上合計ではなく、取引件数を集計したい場合は、SUMの代わりにCOUNTを使います。"SELECT A, COUNT(C) PIVOT(B)"とすることで、日付ごとに各商品の販売件数が列に展開されます。

PIVOT句使用時の注意点とよくあるエラー

複数列のPIVOTはできない

PIVOT句で指定できる列は1つだけです。複数の列を変換したい場合は、QUERY関数を複数回使うか、ピボットテーブル機能を利用します。たとえば、商品名と地域の2つのカテゴリを列方向に展開したい場合、PIVOT句では実現できません。

PIVOT句を使うときは必ず集計関数が必要

SELECT句に集計関数がないとエラーになります。たとえば、"SELECT A, B PIVOT(C)"のように集計関数を省略すると、クエリが無効となります。必ずSUM、COUNT、AVERAGEなどの集計関数を使用してください。

PIVOT句とORDER BYの組み合わせ

PIVOT句で生成された列は、デフォルトで値の昇順(数値なら小さい順、文字列ならアルファベット順)に並びます。これを変更したい場合は、ORDER BY句で並び替えを指定します。ただし、ORDER BY句はPIVOT句の後ろに記述します。例:"SELECT A, SUM(C) PIVOT(B) ORDER BY A DESC"

ヘッダー行数の指定を間違えない

QUERY関数の第3引数(ヘッダー行数)を正しく指定しないと、データの先頭行が無視されたり、見出しがデータとして扱われたりします。通常、見出し行が1行ある場合は1と指定します。ただし、PIVOT句を使う場合、QUERY自身が新しい見出し行を生成するため、元データの見出し行は特別な注意が必要です。元データに見出し行がある場合は1を指定し、ない場合は0を指定します。

空白やエラーの処理

PIVOT句の結果、該当するデータがないセルは空白になります。空白を0で表示したい場合は、IFERROR関数やTO_TEXT関数などを組み合わせて調整します。また、元データに空白セルがあると、PIVOT句が意図しない列を生成する可能性があるため、事前にデータをクリーニングしておきましょう。

QUERYのPIVOT句とピボットテーブルの比較

まとめ

今回は、QUERY関数のPIVOT句を使って行データを列に変換する方法を解説しました。基本的な構文は=QUERY(範囲, "SELECT 列, 集計関数 PIVOT(変換列)", ヘッダー数)です。このテクニックを使えば、集計表を関数ベースで自動化でき、データ更新にも強みを発揮します。次は、QUERY関数の他の句(WHERE、GROUP BY、LABELなど)を組み合わせて、さらに高度なデータ分析に挑戦してみてください。PIVOT句と合わせて使うことで、スプレッドシートのデータ処理能力を大きく引き出せます。