農場の経営管理・資材管理システムを作るために活用したいと思い、QUERY関数の使い方を学んでいます。
教科書として、ノンプロ研メンバーのカワムラさんが執筆された技術同人誌で勉強させていただきました。入門としてとてもわかりやすく、体系立ててQUERY関数について学ぶことができる書籍です！
今回の記事では、QUERY関数を実データを使って触ってみたので、基本の使い方についてまとめていきます。
スプレッドシートをデータベースにした資材管理のシステムを作るのに、QUERY関数を使うとうまくいく気がしている。
— おおさき🥔小さなIT活用で快適な農場づくりを！ (@massa_potato) 2022年9月17日
ノンプログラマーのためのスキルアップ研究会 | 会社員がVLOOKUPの次に覚えるQUERY関数超入門 #技術書典 https://t.co/wFKypNKWbx
QUERY関数とは
Googleスプレッドシートで使えるQUERY関数は、指定したデータ範囲から条件を指定して抽出することができる便利な関数です。
指定範囲からデータを抽出する関数にはVLOOKUP関数が思い浮かびます。しかしVLOOKUP関数では単一のデータしか取得できなかったり、複雑な条件で抽出できないなど不便なことがあります。そういう時にQUERY関数を使うと、とてもシンプルな式でやりたいことを実現できたりします。
QUERY関数の基本の使い方
同一スプレッドシート内のデータを参照する方法
QUERY関数を使って同一スプレッドシート内のデータを参照する場合は、以下のように使います。
セルA1
=QUERY('農薬情報'!A:J, "select *")
- 第1引数には参照するデータ範囲を指定
- 第2引数にはクエリ（抽出するための条件）をダブルクォーテーションで囲って入力
この例では、「農薬情報」シートのA列〜J列をデータ範囲に指定し、その全てのデータを抽出（参照）しています。
関数を入力するセルは左上の1つのセルだけで大丈夫です。ただし、もし範囲に余計なデータが存在しているとエラーになってしまうので注意が必要です。
第2引数のクエリには、いくつかの句を使うことができます。基本の句として以下の3つがあります。
- select句：表示したい列と表示順を指定して抽出
- where句：列の値の条件を指定して行を抽出
- order by句：指定した列の値を基準として並べ替え
これらを活用することで、いろいろな条件でのデータの抽出や並べ替えが可能になります。
列を指定して抽出する select句
第2引数のクエリにselect句を使って、表示したい列を指定して抽出することができます。
=QUERY('農薬情報'!A:J, "select A, B, C, D, E, F, G, H, I, J")
=QUERY('農薬情報'!A:J, "select *")
selectの後に抽出したい列をアルファベットで指定します。
ここでアスタリスク*を使うと、第1引数に指定したデータ範囲を全て抽出します。
また、たとえば以下のようにselect句を記述することで、表示する列や表示順を自在に変えることができます。
=QUERY('農薬情報'!A:J, "select A, B, F, E, G")
条件を指定して行を抽出する where句
第2引数のクエリにwhere句を使って、条件を指定して行の抽出をすることができます。
=QUERY('農薬情報'!A:J, "select A, B, F, E, G where E = '殺虫剤'")
この例では、参照するデータ範囲のE列「用途」が「殺虫剤」にあたる行だけを抽出しています。
指定した列の値を基準として並べ替える order by句
第2引数のクエリにorder by句を使って、データの並べ替えをすることができます。
=QUERY('農薬情報'!A:J, "select A, B, F, E, G order by B desc")
この例では、order by B descと記述することで、参照するデータ範囲のB列「農薬名」を降順にソートしています。
また、以下のようにorder by B ascと指定することで、B列を昇順にソートすることができます。昇順ソートの場合、空白行を省くためにwhere B is not nullという記述も併せて必要です。
=QUERY('農薬情報'!A:J, "select A, B, F, E, G where B is not null order by B asc")
QUERY関数の細かい活用方法
他のスプレッドシートのデータを参照する方法
QUERY関数の第1引数に指定するデータ範囲を他のスプレッドシートのデータにしたい場合は、IMPORTRANGE関数を利用します。
セルA1
=QUERY(IMPORTRANGE("<スプレッドシートID>", "作業項目情報!A:D"), "select *")
=QUERY(IMPORTRANGE("<スプレッドシートID>", "作業項目情報!A:D"), "select Col1, Col2, Col3, Col4")
- IMPORTRANGE関数の第1引数には参照するスプレッドシートを指定、第2引数にデータ範囲を指定
- この場合、QUERY関数の第2引数のクエリでの列指定は、アルファベットではなく
Col1, Col2, ...を用いる
この例では、別のスプレッドシートにある「作業項目情報」シートのA列〜D列をデータ範囲に指定し、その全てのデータを抽出（参照）しています。
クエリの条件抽出にセル参照を使う方法
QUERY関数の第2引数に指定するクエリでwhere句で条件抽出する際に、セル参照を用いることも可能です。
その場合、イコールの後の書き方にひと工夫が必要です。
- 数値を参照する場合は、アンパサンドとダブルクォーテーションで囲む（
"&セル番号&"） - 文字列を参照する場合は、上に加えてさらにシングルクォーテーションで囲む（
'"&セル番号&"'）
例えば、セルB1セルに参照したい文字列を記入する欄を作成し、そのセルを参照して条件抽出する場合は、以下のようになります。
セルA2
=QUERY('農薬情報'!A:J, "select A, B, F, E, G where E = '"&B1&"'")
この例では文字列を参照したいので、セル番号をアンパサンドとダブルクォーテーション、さらにシングルクォーテーションで囲んでいます。ちょっと複雑ですね。
数値や文字列のほかに、日付を参照することもできます。そのやり方は、次回の記事で応用として使ってみたいと思います。
おわりに
QUERY関数の基本の活用方法について、学習したことをまとめてみました。
改めて、カワムラさんの書籍は入門にピッタリで、引き続き活用させていただきたいと思います。ありがとうございます！
