スプレッドシート QUERY関数まとめ
QUERY関数は指定したデータ範囲から欲しいデータを抽出する関数です。
VLOOKUP関数と違って返し値が配列になるため複数抽出することが可能です。
抽出したデータを集計することまでできる超便利な関数です。
基本構文
=QUERY(データ範囲,クエリ,[見出し(省略可)])
見出しは私は殆ど使いませんが、見出しの行数を入れるようです。
今回は主にクエリについての内容になります。
データ範囲
クエリの中で登場するデータの範囲を指定します。
同一のスプレッドシート内なら『シート1!A:D』的な感じで普通に指定したらいいですが、もし他のスプレッドシートを参照したい場合は、データ範囲にimportRange関数を入れてあげればOKです。
=QUERY(importrange("インポートするURL","データ範囲"),クエリ,[見出し(省略可)])
こんな感じです。(importrangeもほんと便利。)
一回importrange関数でシートに出力してから指定してもいいですが、重くなるしセルの無駄遣いになるので個人的にはできれば避けたいかなと思います。
注意点
後述しますが、select句で出力したい列を指定するので、その範囲でデータ範囲を選択してしまうことがありますが、出力はしないけどwhere句で登場する列が入っていなかったらエラー吐きます。
例えば、『A,B,C列を出力したい、条件はI列が"完了"だったら』みたいなケースですが、データ範囲は『A:I』で指定しないといけません。『A:C』にしないようにしましょう。
クエリ
ケース別にみて行きます。
範囲内の列を出力
"select * "
アスタリスクでデータ範囲全ての列になります。
条件式を満たす範囲内の列を出力
"select * where 条件式"
条件式を満たす特定の列を出力
例：特定の列がA～C列の場合
"select A,B,C where 条件式"
where句
検索条件の書き方です。
文字列の比較
例：A列が「完了」
"select * where A = '完了'"
例：A列が「完了」ではない
"select * where A <> '完了'"
プログラム書く人にとっては常識的なことと思いますが、ダブルクォーテーションの中にはシングルクォーテーションです。
ダブルの中にダブルがあると開くつもりのところで閉じていると認識されてしまいます。
↓ダメな例
"select * where A = "完了""
数値の比較
例：A列が60未満
"select * where A < 60"
数値なのでダブルクォーテーションは不要です。
日付の比較
今月の集計をしたい！のパターンでいきます。
集計期間はA1(今月1日)～B1(今月末日)の期間だとします。
↓A1：今月1日
=text(TODAY(),"YYYY-MM-01")
↓B1：今月末日
=text(eomonth(TODAY(),0),"YYYY-MM-DD")
こんな感じでセルに入っているとします。
本題で『A列(日付データ)が今月』
"select * where A >= date '"&A1&"' and A <= date '"&B1&"'"
複数の条件を指定するときは『and』でつなげます。
また、セルの値や関数を条件式に入れるときは文字列として&でつなげます。
"select * where A >= date '"&text(TODAY(),"YYYY-MM-01")&"' and A <= date '"&text(eomonth(TODAY(),0),"YYYY-MM-DD")&"'"
これでもいいですが、集計期間は出力しておかないと逆に「これって今期？今月？」とか、月が変わってすぐに月が変わった感覚がないまま見て「バグってる！」とか言われないかな(わかりやすいかな？)と思って、あえてセルに出して使っています。
集計する
こんなデータがあったとします。
デモデータ
足し算
個数を商品ごとに合計を出したいとします。
sum()を使います。
=QUERY(A1:E7,"select B,SUM(D) group by B")
出力結果
ラベルが『sum 個数』ってなってるのがなんか嫌なのでラベル変更します。
=QUERY(A1:E7,"select B,SUM(D) group by B label SUM(D) '個数合計'")
ラベルは商品名も変えたい場合(複数指定する場合)は、カンマでつなげたらOKです。
平均
avg()を使います。
=QUERY(A1:E7,"select B,avg(D) group by B label avg(D) '個数平均'")
一番ハマったところ
『BY』列を扱う時、要注意です。
`BY
『`』でエスケープしないと『group by』のBYと認識されちゃうみたいです。
検索してもこの情報に行き着かなくて私は結構時間を溶かしてしまいましたのでご注意ください。
GASでやるしかないんか・・と思っていたら、社内のスプレッドマスターが調べて解決策を共有してくださり1つナレッジが増えました。感謝です。
所感
QUERY関数と併せてUNIQE関数もよく使いますが、Microsoft365、Office2021以外のExcelでは使えないようです。
kintoneから出力したレコードデータを元に集計をする機会が沢山ありますが、GAS等で出力するところはやって、あとは関数の世界だから依頼者にお任せ～って感じでした。
しかし、そういえば私はExcelやスプレッドシートの関数に触れる機会が今まであまりなくて、早めに(決してもう早くはないけど。。)慣れておいて損はない！！と思い、なるべく集計までやっていくことにしました。
今のところ使っているのはこんな感じかなと思いますが、また何か発見等あれば更新していきたいです。
Discussion