【超便利】QUERY関数の基本的な使い方、使えない関数、where句で複数条件/セル参照する方法｜スプレッドシート活用事例

GoogleスプレッドシートのQuery関数について説明します。この関数は、データを検索して抽出する強力なツールで、SQLを書いたことがある人にとっては超便利な関数です！！
以下に基本的な使用方法といくつかの例を示します。

基本構文

=QUERY(data, query, [headers])

• data: 検索対象のデータ範囲

• query: SQLに似たクエリ文

• [headers]: データの範囲にヘッダー行が含まれている場合、その行数を指定（省略可能）

例

1. 全データの抽出

データ範囲 A1:C10 から全データを抽出する例です。

=QUERY(A1:C10, "SELECT *", 1)

この場合、1 はヘッダー行が1行目にあることを示します。

2. 条件付きデータの抽出

列Bが「りんご」である行のみを抽出する場合：

=QUERY(A1:C10, "SELECT * WHERE B = 'りんご'", 1)

3. 特定の列を抽出

列Aと列Cのみを抽出する場合：

=QUERY(A1:C10, "SELECT A, C", 1)

4. 集計関数の使用

列Bの平均を計算する場合：

=QUERY(A1:C10, "SELECT AVG(B)", 1)

よく使われるクエリ文の構文

• SELECT: 列を選択

• WHERE: 条件を指定

• ORDER BY: ソート

• LIMIT: 抽出する行数を制限

• GROUP BY: グループ化

• PIVOT: データのピボット

詳細な例

データ範囲 A1:C10 から、列Bが「りんご」で、列Cの値が50以上の行を抽出し、列Aを昇順に並べ替えるクエリ：

=QUERY(A1:C10, "SELECT * WHERE B = 'りんご' AND C >= 50 ORDER BY A ASC", 1)

利用時の注意

1. クエリ文のシンタックスエラーに注意

ポイント：
・クエリ文を書くときに、文字やスペースの使い方に気をつけましょう。

具体例：
・シングルクォート ' を使うべきところをダブルクォート " にしないようにしましょう。
・クエリ文の中で条件を書くときは、正しいスペースを入れましょう。

間違い例：

=QUERY(A1:C10, "SELECT * WHERE B= 'apple'", 1) # Bの後ろにスペースが足りない

正しい例：

=QUERY(A1:C10, "SELECT * WHERE B = 'apple'", 1) # スペースを入れる

2. データ範囲とヘッダーの指定が正しいか確認

ポイント：
・データ範囲を指定するとき、その範囲が正しいか確認しましょう。
・ヘッダー（列名が書かれている行）の行数を正しく設定しましょう。

具体例：
・データが A1:C10 にある場合、A1:C10 と指定します。
・1行目に列名（ヘッダー）がある場合、1 を指定します。

間違い例：

=QUERY(A1:C10, "SELECT A, B", 0) # ヘッダー行が正しく指定されていない

正しい例：

=QUERY(A1:C10, "SELECT A, B", 1) # ヘッダー行を正しく指定

3. 範囲外のセルやデータ型の不一致がないか確認

ポイント：
・データ範囲外のセルを参照していないか確認しましょう。
・数値や文字列のデータ型が合っているか確認しましょう。

具体例：
・A1:C10 を参照するなら、範囲が A1:C10 から外れていないかチェックします。
・数値が入っている列には数値、文字列が入っている列には文字列を入れます。

間違い例：

=QUERY(A1:C5, "SELECT A, B", 1) # データがA1:C10にあるのに範囲が狭すぎる

正しい例：

=QUERY(A1:C10, "SELECT A, B", 1) # データ範囲を正しく指定

• クエリ文の文字やスペースに気をつけましょう。

• データ範囲とヘッダー行を正しく指定しましょう。

• 範囲外のセルやデータ型の不一致がないか確認しましょう。

Query関数と互換性のない関数について

GoogleスプレッドシートのQuery関数はデータの抽出やフィルタリングに非常に便利ですが、他の一部の関数とはうまく組み合わせられないことがあります。特に注意すべきポイントとその解決策を説明します。