QUERY関数は、Googleスプレッドシート上でデータの検索、フィルタリング、集計を行うための関数です。SQLのように、クエリを書くことで複数の条件を指定してデータを取得できます。
基本的な構文
=QUERY(data, query, [headers])
-
data：検索するデータがある範囲を指定します。例えば、シート名を含む範囲、A1から始まる範囲などです。
-
query：検索条件を記述します。SQLに似た記述方法で、SELECT、WHERE、GROUP BY、ORDER BYなどのキーワードを使用してデータをフィルタリング、並べ替え、集計することができます。
-
headers：省略可能なパラメータで、データのヘッダーを表示するかどうかを指定します。
データの例
| 名前 | 年齢 | 都道府県 |
|---|---|---|
| 田中 | 20 | 東京都 |
| 山田 | 25 | 大阪府 |
| 鈴木 | 30 | 東京都 |
| 佐藤 | 35 | 北海道 |
このデータから、年齢が30歳以上で、都道府県が東京都の人の名前と年齢を取得するには、以下のようなQUERY関数を使用します。
=QUERY(A1:C5, "SELECT A, B WHERE B >= 30 AND C = '東京都'")
この場合、A1からC5の範囲を検索し、年齢が30以上かつ都道府県が東京都の人の名前と年齢を取得します。結果は以下のようになります。
| 名前 | 年齢 |
|---|---|
| 鈴木 | 30 |
このように、QUERY関数を使うことで、スプレッドシート上で高度なデータ分析を行うことができます。
QUERY関数のキーワードについて
- SELECT
SELECTキーワードは、取得する列を指定するために使用します。以下のように、列名や範囲を指定することができます。
SELECT A
SELECT A, B
SELECT A:C
- WHERE
WHEREキーワードは、検索条件を指定するために使用します。以下のように、条件式を記述することができます。
WHERE A > 10
WHERE A = 'abc'
WHERE A LIKE '%def%'
- ORDER BY
ORDER BYキーワードは、取得したデータの並べ替えを指定するために使用します。以下のように、並べ替えの基準と方向を指定することができます。
ORDER BY A ASC
ORDER BY B DESC
ORDER BY A, B DESC
- GROUP BY
GROUP BYキーワードは、取得したデータをグループ化するために使用します。以下のように、グループ化する列を指定することができます。
GROUP BY A
GROUP BY A, B
- LIMIT
LIMITキーワードは、取得するデータの件数を制限するために使用します。以下のように、取得する行数を指定することができます。
LIMIT 10
LIMIT 10 OFFSET 20
- OFFSET
OFFSETキーワードは、取得するデータの先頭行を指定するために使用します。LIMITキーワードと一緒に使用することが多いです。以下のように、取得する行数と先頭行の位置を指定することができます。
OFFSET 10
LIMIT 10 OFFSET 20
これらのキーワードを組み合わせることで、複雑なデータ分析が可能になります。以下に例を示します。
各キーワードの使用例
=QUERY(A1:C5, "SELECT A, AVG(B) WHERE C = '東京都' GROUP BY A ORDER BY AVG(B) DESC LIMIT 3")
この場合、A1からC5の範囲を検索し、都道府県が東京都の人の名前と年齢の平均値を求め、名前でグループ化して平均値の降順で並べ替えます。そして、平均値の上位3件を取得します。
QUERY関数では、日付を条件に入れることができます。以下に、日付を条件に入れた場合の例を示します。
=QUERY(A1:C5, "SELECT A, B WHERE C > date '2022-01-01'")
この場合、A1からC5の範囲を検索し、日付が2022年1月1日よりも後のデータを取得します。日付は、date '年-月-日'の形式で指定することができます。
また、以下のように、日付を範囲指定することもできます。
=QUERY(A1:C5, "SELECT A, B WHERE C >= date '2022-01-01' AND C <= date '2022-03-31'")
この場合、A1からC5の範囲を検索し、日付が2022年1月1日から3月31日までのデータを取得します。>=と<=を使って、範囲を指定することができます。
なお、日付には時刻情報も含めることができます。以下のように、日時を条件に指定することもできます。
=QUERY(A1:D5, "SELECT A, B WHERE C >= timestamp '2022-01-01 00:00:00' AND C <= timestamp '2022-01-01 23:59:59'")
この場合、A1からD5の範囲を検索し、日時が2022年1月1日のデータを取得します。timestamp '年-月-日 時:分:秒'の形式で指定することができます。
特定条件下で、特定列のみ抽出する例
例：2023年05月01日以降に生まれたユーザーだけを抽出し、nameだけを出力させる場合
=QUERY(A1:C, "SELECT B WHERE A > date '2023-05-01'")
この場合、A列には生年月日が入っており、B列にはユーザー名が入っていると仮定しています。
A1からCの範囲を検索し、A列が2023年05月01日以降のユーザーの名前をB列から取得します。SELECT句には、nameを指定するためにB列を指定しています。また、条件にはWHERE句を使用し、A列が指定した日付よりも後のデータのみを取得します。
なお、もしA列に生年月日だけでなく生年月日と時刻が入っている場合は、dateの代わりにdatetimeを使用して以下のように書き換える必要があります。
=QUERY(A1:C, "SELECT B WHERE A > datetime '2023-05-01 00:00:00'")
このように、条件を変更することで、特定の列のみを抽出することができます。