【応用編】GoogleスプレッドシートQUERY関数 応用テクニック8選

アイキャッチ画像 基本解説

「QUERY関数で基本的なデータ抽出はできるようになったけど、もっと複雑な条件で絞り込みたい」
「別のシートのデータを直接集計したり、レポートの表示形式まで完璧に整えたい」

そんな、一歩進んだデータ活用を目指すあなたのために、本記事ではQUERY関数の実務で役立つ8つの応用テクニックを厳選してご紹介します。

WHERE句の高度な使い方から、複数シートをまたいだ動的なクエリ作成、レポートの出力制御まで、具体的な使用例を交えて徹底解説。この記事を読めば、面倒な手作業から解放され、あなたのデータ分析スキルは格段に向上するはずです。

※QUERY関数の基本的な構文については、こちらの記事をご覧ください。

準備：この記事で使うサンプルデータ

今回は、多くの企業で使われるであろう「営業案件管理リスト」を例にします。

例に使用する表データ

WHERE句で自在に絞り込む！高度な検索テクニック

まずは、データ抽出の要であるWHERE句を、より高度に使いこなすためのテクニックを見ていきましょう

テクニック1：セル参照で条件を動的に変更する

条件を変えて何度も抽出する作業は、セル参照を使えば劇的に効率化できます。

やりたいこと： I1セルに入力した担当支社の案件データを抽出したい。

関数例：

=QUERY(
  A1:G11,
  "SELECT *
  WHERE B = '"&I1&"'
  "
)
セル参照の例
担当支社がI1セルの「東京」と同じデータだけ表示されている

解説：
WHERE B = ‘”&I1&”‘ の部分がポイント。QUERY関数のクエリ文（文字列）とI1セルの値を & で結合することで、I1セルの値を変更するだけで抽出結果を動的に変えることができます。

テクニック2：日付データを正しく比較する

日付で期間を絞り込むには、dateキーワードとTEXT関数を組み合わせるのが定石です。

やりたいこと： I2セルに入力した日付（例：2025/6/1）以降に計上された案件を抽出したい。

関数例：

=QUERY(
  A1:G11,
  "SELECT *
  WHERE A >= date '"&TEXT(I2, "yyyy-mm-dd")&"'
  "
)
日付で絞る例

解説：
QUERY関数は日付を直接認識できないため、TEXT関数で ‘yyyy-mm-dd’ 形式の文字列に変換し、dateキーワードを付けて渡すことで、日付として正しく比較できるようになります。

テクニック3：containsとlikeで部分・曖昧検索

「特定の文字を含む」といった曖昧検索には contains と like が有効です。この2つの演算子は似ていますが、役割が明確に異なります。以下の表で違いを理解し、状況に応じて使い分けましょう。

演算子目的主な特徴使用例
contains単純な部分一致検索したい文字列が含まれるかを判定する最もシンプルな方法。顧客名に「株式会社」を含むデータを抽出する。
likeワイルドカードを使ったパターン一致%（0文字以上の任意文字列）や_（任意の1文字）を使い、より柔軟なパターンで検索する。顧客名が「B」で始まり「事」で終わるデータを抽出する。

やりたいこと： 顧客名（C列）に「株式会社」を含む案件を抽出したい。

関数例：

=QUERY(
  A1:G11,
  "SELECT *
  WHERE C contains '株式会社'
  "
)
含む一致の例

やりたいこと： 顧客名（C列）が「B」で始まり、「事」で終わる案件を抽出したい。（例：「B商事」）

関数例：

=QUERY(
  A1:G11,
  "SELECT *
  WHERE C like 'B%事'
  "
)
部分一致の例

テクニック4：matchesで正規表現を使いこなす

likeでも表現できない、さらに複雑なパターンには正規表現が使える matches が最適です。

やりたいこと： 顧客名（C列）が「アルファベット大文字1文字 + 株式会社」の形式に一致する案件を抽出したい。（例：「A株式会社」「F株式会社」）

関数例：

=QUERY(
  A1:G11,
  "SELECT *
  WHERE C matches '^[A-Z]株式会社$'
  "
)
正規表現で絞り込む例

解説：
この条件をlikeで表現しようとすると、C like ‘A株式会社’ or C like ‘B株式会社’ … と26個のOR条件を記述する必要があり非現実的です。matchesと正規表現 ^[A-Z]株式会社$（行頭が大文字A～Zの1文字で、その後「株式会社」が続き行末となる）を使えば、このようにスマートに記述できます。

データソースを拡張し、表示を操る応用ワザ

次に、他のファイルにあるデータを扱ったり、抽出する結果の件数をコントロールしたりするテクニックを見ていきましょう。

テクニック5：IMPORTRANGEで別ファイルのデータを直接操作

IMPORTRANGEをQUERY関数のデータ範囲に指定することで、別のスプレッドシート（ファイル）にあるデータを直接QUERYで集計・抽出できます。

やりたいこと： 別のファイルにある営業案件管理リストから、東京支社のデータのみを抽出したい。

関数例：

=QUERY(
  IMPORTRANGE("スプレッドシートのURL", "シート名!A1:G11"),
  "SELECT *
  WHERE Col2 = '東京'
  ",
  1
)
IMPORTRANGEで、別SSから範囲を選択する例

解説：
IMPORTRANGEで外部データを参照する場合、列指定はA, B, CではなくCol1, Col2, Col3という形式になる点に注意が必要です。Col2は元のデータの2列目（B列）を指します。

テクニック6：LIMITとOFFSETで表示件数を制御する

LIMITは抽出する行数を制限し、OFFSETは指定した行数分をスキップします。これらを組み合わせることで「売上トップ5」や「検索結果の2ページ目」といった表示が可能になります。

やりたいこと： 受注金額が高い順に並べ替えた上で、上位3位から5位までの案件を表示したい。

関数例：

=QUERY(
  A1:G11,
  "SELECT *
  ORDER BY F DESC
  LIMIT 3
  OFFSET 2
  "
)
LIMITとOFFSETで、3~5位を表示する

解説：
ORDER BY F DESCで受注金額を降順にソートし、OFFSET 2で上位2件をスキップ、LIMIT 3でそこから3件を取得しています。つまり、3位、4位、5位のデータが抽出されます。

作った後がラクになる！自動化とメンテナンスの工夫

最後に、一度作ったら後々の修正がラクになる、メンテナンス性の高い数式の作り方を紹介します。

テクニック7：TEXTJOINで究極の動的検索

複数の検索条件を組み合わせて動的にフィルタリングできる、非常に強力なテクニックです。別シートに設けた検索条件に応じて、AND条件を自動で組み立てます。

やりたいこと： 別の「抽出条件」シートに入力された条件を全て満たす案件を抽出したい。空欄の条件は無視し、入力された条件だけでAND検索を行いたい。

動的なWHERE句作成

解説：
このテクニックの肝は、条件の入力部分と、QUERYで使う条件式を生成する部分を分けることです。

  1. 条件式の生成（C列）
    C列に、B列の入力値に応じてIF関数で条件式を生成させます。（例：B = ‘東京’）入力がなければ空欄を返します。
  2. TEXTJOINで結合
    最終的に、別のセルでTEXTJOINを使い、C列で生成された条件式を ” AND ” で連結します。
  3. QUERYに組み込み
    TEXTJOINで完成した文字列を、QUERY関数のWHERE句に&で埋め込みます。

最終的な関数例（結果を表示したいセルに入力）：

=QUERY(
  '営業案件管理リスト'!A1:G11,
  "SELECT *
  WHERE "&TEXTJOIN(" AND ", TRUE, C2:C8)
)
TEXTJOINの例1
6月1日以降の新規受注に絞る例。C列の値をANDで繋ぎ、WHERE句を作成している。
TEXTJOINの例2
東京の受注に絞る例。QUERY関数を編集しなくても、B列の値を変えるだけで表示結果が変わっている。

この構造にすることで、条件の追加・変更が容易になり、非常にメンテナンス性の高い動的検索フォームが実現できます。

テクニック8：列の挿入・削除に強い数式を組む

QUERY関数は列をA, B, Cで指定するため、元のデータに列が挿入・削除されると数式が壊れてしまいます。MATCHやADDRESSを組み合わせることで、列の位置が変わっても自動で追従する堅牢な数式が作れます。

やりたいこと： 列の順番に依存せず、「顧客名(C列)」「受注金額(F列)」を抽出したい。

関数例：

=QUERY(
  A1:G11,
  "SELECT
    "&SUBSTITUTE(ADDRESS(1, MATCH("顧客名", A1:G1, 0), 4), "1", "")&",
    "&SUBSTITUTE(ADDRESS(1, MATCH("受注金額", A1:G1, 0), 4), "1", "")&"
  "
)
列名でSELECT句を指定している

解説：
一見すると非常に複雑ですが、やっていることはパーツに分解すると理解できます。
“&SUBSTITUTE(ADDRESS(1, MATCH(“顧客名”, A1:G1, 0), 4), “1”, “”)&” の部分を例に見てみましょう。

  1. MATCH(“顧客名”, A1:G1, 0)
    MATCH関数で、1行目のヘッダーの中から「顧客名」が何番目の列にあるかを探します。この例では 3 が返ります。
  2. ADDRESS(1, 3, 4)
    ADDRESS関数で、行番号と列番号をセル参照の形式に変換します。ADDRESS(1, 3, 4) は、1行目・3列目を相対参照（例：C1）の形式で返します。
  3. SUBSTITUTE(“C1”, “1”, “”)
    SUBSTITUTE関数で、C1 という文字列から 1 を取り除き、空文字に置換します。結果として、列を示すアルファベット C だけが残ります。

この一連の処理を抽出したい列のヘッダー名ごとに行い、,（カンマ）を挟んで & で結合していくことで、”SELECT C, F” というクエリ文を動的に生成しているのです。これにより、元の表の構成変更に強い、メンテナンス性の高いシートが実現します。

まとめ

今回は、QUERY関数の応用テクニックを8つ、厳選してご紹介しました。これらを組み合わせることで、データ抽出から集計、レポーティングまで、これまで手作業や複数の関数で行っていた作業の多くをQUERY関数一つで自動化できます。

ぜひ、このテクニックを活用し、日々の業務でQUERY関数を使いこなし、データ活用の達人を目指してください。

※Googleサービスは、Google LLC の商標であり、この記事はGoogleによって承認されたり、Google と提携したりするものではありません。