Initial Site

Initial Site

Initial Site

QUERY関数の便利な使い方

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

営業部 ヨシモトです。

 

以前スプレッドシートの便利な関数をご紹介いたしました。
その中の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のリファレンスを読むことで解決できます。(これを知る以前は読んでもわかりませんでした)

 

こんなことができます

tomatobuy

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

 

=QUERY(A:C)

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

 

 

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

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

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

 

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

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

selecttomato

 

=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項目だけ出しています。
selecttomato200

 

=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で指定しなくてもそれっぽい項目名にはなるのですが、わかりやすくしたい場合はこうした方が良いかもしれません。

tomatototal

 

簡単な使用例ではありますが、これを覚えるだけでも使い勝手が変わります。
ちなみに{}を使うとQUERYの中にQUERYが書けたりするので、複数の範囲のものを合体させてクエリを指定する、なんてこともできます。
これもうまく扱えるようになると色々捗ります。

 

他にもQUERY関数の便利な使い方があれば是非教えてください。

 

その他、スプレッドシートやGASに関する記事はこちらからどうぞ。

GASを使ってGmailから本文の一部を抜き出す

スプレッドシート独自の便利な関数

QUERY関数の便利な使い方

スプレッドシートを使って簡単なスクレイピングをしてみよう

【GAS】正規表現を使ってGmailの本文から文章を抜き出す

【簡単】GASでスプレッドシートの送信リストからメールを送る

【GAS】正規表現を使って複数行の文章をGmailから抜き出す

【GAS】Gmailの添付ファイルを一括でGoogleドライブへ保存する

【難しくない】GASでwebスクレイピングして正規表現でデータを集める

【GAS】OCRを使った画像の文字取得を自動化する

それでは!!


コメントをどうぞ

ツールの記事

  1. ai_search
    【GAS】OCRを使った画像の文字取得を自動化する
  2. dance_shoot_dance
    【難しくない】GASでwebスクレイピングして正規表現でデータを集める
  3. bg_kaitenzushi
    【GAS】Gmailの添付ファイルを自動でGoogleドライブへ保存する
  4. kid_job_boy_programmer
    【GAS】正規表現を使って複数行の文章をGmailから抜き出す