GoogleスプレッドシートのQUERY関数を理解する

これはなに？

GoogleスプレッドシートのQUERY関数（＝Google Visualization API のクエリ言語）をさくっと理解するための記事。
なお、本記事執筆時点（2024/04/01）でのバージョンは0.7のため、後述する「できないこと」は将来的にできるようになるかも。（ならないかも）

公式Docsは下記の通り。

基本的な使い方

下記 members シートを用意する。

	A	B	C
1	name	age	memo
2	Alice	34
3	Bob	21
4	Charlie	12	Leader

このシートに対し、下記関数を発行する。

// 関数
=QUERY('members'!A1:B, "SELECT A, B", 1)

// 出力結果
name, age
Alice, 34
Bob, 21
Charlie, 12

この実行内容の意味するところは下記の通り。

• 第１引数: members シートのA1 セルから B 列の最終行までの範囲をデータソース（≒テーブル）とする
• 第２引数: A列（＝nameカラム）とB列（＝ageカラム）を SELECT する
• 第３引数: 1行目をヘッダー行とみなす

できること

• SELECT クエリを発行できる
• WHERE や GROUP BY 、 ORDER BY など、集計時にあると嬉しいものはある程度揃っている
• データソースとして、任意のシートとセル範囲を指定できる

できないこと

• SELECT クエリ以外の発行はできない
  • INSERT や UPDATE 、 DELETE などは使えない
• SELECT 時に列名を指定できない
  • AやBといった列名で指定する必要がある
• エイリアス AS が利用できない
  • LABEL 句で集計結果に出力される列名を編集できるが、クエリ内で使い回すことはできない

Tips: データソースを2箇所指定する方法

{'<1つ目のシート名>'!<セル範囲>; '<1つ目のシート名>'!<セル範囲>} と指定してあげれば良い。

例えば、下記2つのシートをデータソースに指定し、年齢の平均値を集計したい場合。

members_1 シート

	A	B	C
1	name	age	memo
2	Alice	34
3	Bob	21
4	Charlie	12	Leader

members_2 シート

	A	B	C
1	name	age	memo
2	太郎	57	お休み中
3	次郎	45
4	三郎	38	お食事中

下記のように平均年齢を取得できる。

// 関数
=QUERY({'members_1'!A1:B; 'members_2'!A1:B}, "SELECT AVG(Col2) LABEL AVG(Col2) '平均年齢'", 1)

// 出力結果
平均年齢
34.5

注意点

• カラム指定方法について、2箇所以上のデータソースを指定した場合、 A, B といった形式ではなく Col1, Col2 とする必要がある
  • 今回の例においては、 SELECT AVG(B) ではなく SELECT AVG(Col2) としている
• また、 LABEL AVG(Col2) '平均年齢' と記載することで、出力結果のカラム名を変更している
  • LABEL 句なしの場合、出力結果内のカラム名は avg age となる