QUERY関数の便利な使い方

ヨシモト2018/03/23 18:30

営業部ヨシモトです。

以前スプレッドシートの便利な関数をご紹介いたしました。
その中のQUERY関数についてもう少しご紹介します。

QUERY関数とは

“Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。”

QUERY – ドキュメントエディタヘルプ

簡単にいうと範囲を指定して、その中の任意の条件のものを出力できるようなイメージです。

しかも、引用元には手を加えずに扱えるので、ひとつデータベース的なものを用意してそれを別シートでQUERY関数を使っていけるのが便利です。

QUERY関数で検索すると使い方を解説しているブログなどありますので、いくつも参考にさせていただきました。
ですが、肝心のクエリというものに、なんとなく覚えづらさのようなものを感じていました。（個人的な感想です）

クエリについて

QUERY関数のドキュメントエディタヘルプや解説しているブログを読んでもクエリの使い方などいまいちピンと来なかったのですが、Progateというプログラミングの入門サイトを使って自宅でSQLの勉強していると、QUERY関数で使ったクエリっぽいと思いました。

それからはSQLの基礎中の基礎のような部分を繰り返し取り組んでみるとわりと使えるようになってきました。

QUERY関数はSQLっぽいけどSQLではない

これは当たり前なんですが、SQLでできるけど QUERY関数ではできないことだらけです。
ただ、SQLのwhere句、group句などをここで覚えておくとQUERY関数がスムーズに使えるようになります。
~~(すごくできそうですけどwhereにnotが使えません。もしできるなら教えていただけると嬉しいです)~~

※2018/04/09追記
where にnot が使えます。以前使えなかったと思っていたのは私の使い方が間違っていたようです(笑)

また、わからないことがあってもGoogle Visualization APIのリファレンスを読むことで解決できます。（SQLを知る以前は読んでもわかりませんでした）

こんなことができます

野菜の購入品目と、金額、購入日を記録した表があります。これをいじっていきましょう。
ちなみにこれはGoogle Apps Scriptで一気にランダムに書き込みました。500行あるのですが3/1以降が見えてないのであまり意味ないですね…
（3/1にちょっと買いすぎ、とか時々安すぎるものがあるのはあまり気にしないでください）

=QUERY(A:C)

引数に範囲のみを入れるとその範囲をそのまま出力します。

=QUERY(A:C,”SELECT *”)

出力内容は先ほどと同じですが、これは第2引数にSELECTを指定してA:Cのうちどの項目を出力するか選びます。

SELECT *で全ての項目が出せます。
SELECT Aにすると品目のみ、SELECT A,Bにすると品目と金額の2項目が出せます。

=QUERY(A:C,”SELECT * WHERE A = ‘トマト'”)

WHEREで条件のようなものが指定できます。
A:C(品目、金額、購入日)からA(品目)がトマトのものだけ出力する、というものです。

=QUERY(A:C,”SELECT * WHERE A = ‘トマト’ AND B >= 200 ORDER BY B DESC LIMIT 5″)

WHEREは複数設定することができます。
WHERE A = ‘トマト’ AND B >= 200は200円以上で買ったトマトということになります。それを、
ORDER BY B DESC金額が高い順に並べて
LIMIT 5 そのうち5項目だけ出しています。

=QUERY(A:C,”SELECT SUM(B) WHERE A = ‘トマト’ LABEL SUM(B) ‘トマトの合計金額'”)

SELECT SUM(B)はB列の合計金額を選択(出力)します。
SELECT SUM(B) WHERE A = ‘トマト’としてあるので、「A(品目)がトマトのB(金額)の合計」ということになります。
LABEL SUM(B) ‘トマトの合計金額’ 最後のLABELですが、これを使うとSUM(B)を「トマトの合計金額」のように任意の項目名を与えることができます。
LABELで指定しなくてもそれっぽい項目名にはなるのですが、わかりやすくしたい場合はこうした方が良いかもしれません。