【スプレッドシートQUERY関数 Part2】クエリ言語を完全マスター!where, group by, pivotを使いこなしデータ抽出の達人に

「GoogleスプレッドシートのQUERY関数、基本的な使い方は覚えたけど、もっと複雑な条件でデータを抽出したい…」 「大量のデータから、必要な情報だけを自動で集計してレポートを作りたい…」

もしあなたがそう思っているなら、この記事がその悩みを解決します。

この記事では、QUERY関数の心臓部とも言える「クエリ言語」に焦点を当て、基本的な使い方(Part1)から一歩進んだ、より高度で実践的なデータ操作術を徹底的に解説します。

where句を使った複数条件でのデータ絞り込みから、group bypivotを使ったデータ集計、order byでの並び替えまで、この記事を読み終える頃には、あなたはまるでデータベースを操る専門家のように、自在にデータを抽出・加工できるようになっているでしょう。

▼この記事で学べること

  • where句を使った、様々な条件(数値、日付、複数条件など)でのデータ抽出方法
  • group by句やpivot句を使った、クロス集計表の自動作成方法
  • order by句を使った、複数条件でのデータ並び替えテクニック
  • label句やformat句を使った、抽出結果の見た目を整える方法
  • 実践的なシナリオに基づいた、クエリ言語の組み合わせ活用術

さあ、あなたもこの記事でクエリ言語をマスターし、「データ抽出の達人」への第一歩を踏み出しましょう!

QUERY関数の「クエリ言語」とは?

QUERY関数がなぜこれほど強力なのか?その答えが「クエリ言語」の存在です。

クエリ言語とは、データベースに「どんなデータを、どのように取得したいか」を命令するための専用の言語です。GoogleスpreッドシートのQUERY関数で使われるクエリ言語は、データベース言語として広く使われている「SQL」に非常によく似ています。

=QUERY(データ範囲, "クエリ言語の命令文", [見出しの行数])

第2引数に指定する "クエリ言語の命令文" を使いこなすことで、単純なデータ抽出だけでなく、フィルタリング、並び替え、集計、ピボットテーブルのような集計表作成まで、あらゆる操作が可能になります。

まずは、このクエリ言語を構成する主要な「句(Clause)」の役割を理解することから始めましょう。

【基本編】クエリ言語の主要な句を理解しよう

ここでは、データ操作の基本となるselect, where, order byの応用的な使い方を、以下のサンプルデータを使って解説します。

【サンプルデータ】 A1:E11

商品名 (A)カテゴリ (B)販売日 (C)担当者 (D)売上金額 (E)
りんご果物2025/4/5佐藤1,200
みかん果物2025/4/8鈴木800
牛乳飲料2025/4/10高橋500
りんご果物2025/5/2鈴木1,500
キャベツ野菜2025/5/5佐藤300
牛乳飲料2025/5/12田中550
みかん果物2025/6/1高橋900
鶏肉精肉2025/6/5鈴木2,500
りんご果物2025/6/10佐藤1,300
牛乳飲料2025/6/15田中600

where句でデータを自在に絞り込む

where句は、特定の条件に一致する行だけを抽出するための、最も重要な句の一つです。様々な条件指定の方法をマスターしましょう。

1. 数値で絞り込む(比較演算子)

=QUERY(A1:E11, "select * where E >= 1000")

売上金額が1,000円以上のデータを抽出します。

  • 数式: =QUERY(A1:E11, "select * where E >= 1000")
  • ポイント: > (より大きい), < (より小さい), >= (以上), <= (以下), = (等しい), <> (等しくない) といった比較演算子が使えます。

2. テキストで絞り込む

=QUERY(A1:E11, "select * where B = '果物'")

カテゴリが「果物」のデータを抽出します。

  • 数式: =QUERY(A1:E11, "select * where B = '果物'")
  • ポイント: テキストを指定する場合は、必ずシングルクォーテーション(')で囲みます。

3. 日付で絞り込む

=QUERY(A1:E11, "select * where C >= date '2025-05-01'")

2025年5月1日以降のデータを抽出します。

  • 数式: =QUERY(A1:E11, "select * where C >= date '2025-05-01'")
  • ポイント: 日付を条件にする場合は、date 'YYYY-MM-DD' という形式で記述します。

4. 複数条件で絞り込む (and, or)

=QUERY(A1:E11, "select * where B = '果物' and E >= 1000")

カテゴリが「果物」かつ、売上金額が1,000円以上のデータを抽出します。

  • 数式: =QUERY(A1:E11, "select * where B = '果物' and E >= 1000")
  • ポイント:
    • and: 両方の条件を満たす場合に抽出します。
    • or: いずれかの条件を満たす場合に抽出します。

order by句でデータを並び替える

order by句を使うと、抽出したデータを特定の列を基準に並び替えることができます。

1. 昇順・降順で並び替える

=QUERY(A1:E11, "select * order by E desc")

売上金額が高い順(降順)にデータを並び替えます。

  • 数式: =QUERY(A1:E11, "select * order by E desc")
  • ポイント:
    • asc: 昇順(省略した場合のデフォルト)
    • desc: 降順

2. 複数列を基準に並び替える

=QUERY(A1:E11, "select * order by B asc, E desc")

カテゴリで昇順に並び替え、同じカテゴリ内では売上金額が高い順(降順)に並び替えます。

  • 数式: =QUERY(A1:E11, "select * order by B asc, E desc")
  • ポイント: 優先したい基準列を先に記述し、カンマ(,)で区切ります。

【応用編】データを集計・加工するクエリ言語

ここからは、group bypivotといった、データ集計を自動化する強力な句を解説します。これらを使いこなせば、面倒な手作業での集計から解放されます。

group by句でデータをグループ化して集計する

=QUERY(A1:E11, "select D, sum(E) group by D")

group by句は、特定の列の項目でデータをグループ化し、それぞれのグループに対して合計や平均などの集計を行う機能です。

例:担当者別の売上合計を算出する

  • 数式: =QUERY(A1:E11, "select D, sum(E) group by D")
  • 結果: | 担当者 | sum 売上金額 | | :— | :— | | 佐藤 | 2,800 | | 鈴木 | 4,800 | | 高橋 | 1,400 | | 田中 | 1,150 |
  • ポイント:
    • select句には、グループ化する列(D)と集計する列(sum(E))を指定します。
    • 使える集計関数には sum() (合計), avg() (平均), count() (個数), max() (最大値), min() (最小値) などがあります。

pivot句で行と列を入れ替えたクロス集計表を作成する

=QUERY(A1:E11, "select D, sum(E) group by D pivot B")

pivot句は、group byと組み合わせることで、行と列を入れ替えたクロス集計表(ピボットテーブル)を瞬時に作成できる非常に強力な機能です。

例:担当者別のカテゴリごとの売上合計クロス集計表を作成する

  • 数式: =QUERY(A1:E11, "select D, sum(E) group by D pivot B")
  • 結果: | 担当者 | 飲料 | 果物 | 精肉 | 野菜 | | :— | :— | :— | :— | :— | | 佐藤 | | 2,500 | | 300 | | 鈴木 | | 1,500 | 2,500 | | | 高橋 | 500 | 900 | | | | 田中 | 1,150 | | | |
  • ポイント:
    • group by D: 行の項目(担当者)を指定します。
    • pivot B: 列の項目(カテゴリ)を指定します。

その他の便利な句

機能使用例
limit抽出する行数を制限するlimit 5 (上位5行のみ表示)
label列の見出しを変更するlabel sum(E) '合計金額'
format表示形式(フォーマット)を変更するformat sum(E) '#,##0円'

labelformatを使った整形例

=QUERY(A1:E11, "select D, sum(E) group by D label sum(E) '合計売上' format sum(E) '¥#,##0'")

group byで算出した担当者別売上合計の見出しを日本語にし、通貨形式で表示します。

  • 数式: =QUERY(A1:E11, "select D, sum(E) group by D label sum(E) '合計売上' format sum(E) '¥#,##0'")
  • 結果: | 担当者 | 合計売上 | | :— | :— | | 佐藤 | ¥2,800 | | 鈴木 | ¥4,800 | | 高橋 | ¥1,400 | | 田中 | ¥1,150 |

【実践シナリオ】クエリ言語を組み合わせて使ってみよう

それでは、これまで学んだ句を組み合わせて、より実践的なデータ抽出に挑戦してみましょう。

シナリオ:4月と5月のデータを対象に、カテゴリ別の売上合計を算出し、売上が高い順に並べる

このお題をクエリ言語に翻訳すると、以下のようになります。

  1. データを抽出する: select B, sum(E) (カテゴリと売上合計)
  2. 条件で絞り込む: where C >= date '2025-04-01' and C < date '2025-06-01' (4月1日~5月31日)
  3. グループ化する: group by B (カテゴリで)
  4. 並び替える: order by sum(E) desc (売上合計の降順で)
  5. 見出しを整える: label B '商品カテゴリ', sum(E) '売上合計'

これらを一つにまとめると、以下の数式が完成します。

  • 完成した数式:Excel=QUERY(A1:E11, "select B, sum(E) where C >= date '2025-04-01' and C < date '2025-06-01' group by B order by sum(E) desc label B '商品カテゴリ', sum(E) '売上合計'")
  • 実行結果: | 商品カテゴリ | 売上合計 | | :— | :— | | 果物 | 4,200 | | 飲料 | 1,050 | | 野菜 | 300 |

このように、句を正しく組み合わせることで、複雑な条件のデータ集計も一つの関数で完結させることができます。

QUERY関数でよくあるエラーと対処法

最後に、QUERY関数で遭遇しがちなエラーとその解決策をご紹介します。

エラー内容原因の例対処法
#N/Awhere句の条件に一致するデータがない条件式が正しいか、データ範囲に目的のデータが含まれているか確認する
#VALUE!クエリ言語の構文が間違っているselect, whereなどの句のスペルミス、テキストを'で囲み忘れていないかなどを確認する
#ERROR! (数式解析エラー)複数のデータ型が混在する列で比較しようとしているTEXT関数などを使って列のデータ型を統一するか、クエリ内でstring()などを使って型変換する
結果が空欄になるselect句で指定した列とgroup by句で指定した列が一致していないgroup byで指定した列と、集計関数以外のselect句の列を一致させる

Google スプレッドシートにエクスポート

まとめ:クエリ言語を使いこなし、データ分析を次のレベルへ

今回は、GoogleスプレッドシートのQUERY関数の強力な機能「クエリ言語」について、応用的な使い方を解説しました。

  • whereで、複数条件や日付・テキストなど、思い通りのデータ絞り込みができる。
  • order byで、昇順・降順・複数キーでの並び替えが自在にできる。
  • group byと**pivot句**を使えば、手作業だった集計作業を自動化できる。
  • labelや**format句**で、抽出結果をレポートとして見やすく整形できる。

最初は少し難しく感じるかもしれませんが、この記事のサンプルを参考に実際に手を動かしてみてください。一度使い方をマスターすれば、あなたのデータ分析やレポート作成の効率は劇的に向上するはずです。

ぜひ、QUERY関数のクエリ言語を使いこなし、日々の業務を効率化していきましょう!