スプレッドシートQuery関数を使ってみよう　④WHERE句

クエリイメージスプレッドシート
2019.11.30
この記事は約11分で読めます。
スポンサーリンク
もしも_楽天

読んで欲しい人

  • SQLに興味があるが、一から環境やデータ整備するのがめんどくさい人
  • vlookupとかでデータ抽出しているが、限界を感じている人
  • データ分析したい人
  • ノンプログラマーでもクエリぐらいは使いたいって人
  • クエリって美味しいの？って人（クエリは美味しいですよって意味も込めて）

Query関数（クエリ関数）シリーズ予定　（あくまで予定）

  1. 概要と目的
  2. クエリ関数の構成
  3. 基本の基本　SELECT句（全部取得、特定カラムだけ取得）
  4. 抽出の基本　WHERE句を使ってみる(数値一致、大小、日付一致、大小etc）←今回
  5. GROUP　BY句を使ってみる
  6. 他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
  7. 並び替えの基本　ORDER BY句
  8. SQLやるなら必須　サブクエリをやってみる
  9. データに改行が入ってるぞ　likeで対応できない時はcontains/starts withが助けてくれる

Query関数：WHERE句の基本

WHERE句ですが、基本はSELECT文で抽出範囲を選んだのちに、条件を設定するために作成します。スプレッドシート関数のsumif/sumifsとかのif/ifs部分に類似するイメージですね。

イメージ
=query(データ範囲,"select * where （カラム） = '抽出対象'")

サンプル
=query(sample!A1:H7,"select * where B = 'Eng'")　＃前回公開しているデータでB列deptがEngのデータを抽出

検索の方法としては、主に

  • 一致（複数一致, OR一致、前方一致、後方一致）
  • 含有（like, containsなど）
  • 以上以下
  • 空白か否か
  • 正規表現

に分類できます。以下では一つづつ実際の書き方をみながら説明していきます。

今後利用する元データは前回リンクを貼ってありますが、再度貼ると共に、画像も貼り付けておきます。

URL Link: https://developers.google.com/chart/interactive/docs/querylanguage

元データ

WHERE句　複数条件一致（論理積）はANDを使う

抽出条件を複数使いたい場合（sumifsのイメージ）は、ANDでWHERE句内の抽出条件を複数設定します。

例えば、deptがEngに所属している、ageが35歳の人を抽出したいとしましょう。こんな感じになります。

=query(sample!A1:H7,"select * where B = 'Eng' AND F =35")

whereの後に、B＝XXとF＝XXをAND条件でつないでいます。これで、２つの条件共にみたすデータだけが抽出できます。この後説明していきますが、条件は＝だけでなく、様々（大小やNull出ないなど）ありますので、それを利用することも可能です。

例えば、deptがEngに所属している、ageが30歳以上の人を抽出したい場合、以下のようになります。

=query(sample!A1:H7,"select * where B = 'Eng' AND F >=30")

WHERE句　どちらか条件一致（論理和）はORを使う

これはスプレッドシート関数sumifsだと標準でサポートされていない集計方法ですが、WHERE句では簡単にできます。先ほどみたANDの部分をORに変えればOKです。

例えば、deptがEngに所属している人、もしくはageが30歳未満の人を抽出したいとしましょう。こんな感じになります。

=QUERY(sample!A1:H7,"select * where B = 'Eng' or F <30")

WHERE句　前方一致はstarts with/　後方一致はends with

特定の文字/数字/記号で始まる行を検索するためにはstarts withを使います。

例えば、deptがSで始まるものを抽出すると、Sales部門所属のデータだけが抽出されます。

=QUERY(sample!A1:H7,"select * where B starts with 'S'")

また、2005年入社の人を抽出したいときには、hireDateが2005で始まるものを抽出すれば行けます。

=query(sample!A1:H7,"where E starts with 2005")

他方、特定の文字/数字/記号で終わる行を検索するためにはends withを使います。

例えば、10日入社の社員だけ抽出してみたい場合、ends withが有用です。

=query(sample!A1:H7,"where E ends with 10")

WHERE句　日付を検索するためのDate

さて、2005年10月10日入社の人を抽出したい、となりました。普通にWHERE句を利用して日付を指定しても想定した結果が表示されません。

❌　=query(sample!A1:H7,"where E = 2005-10-10") #ValueErrorになる
❌　=query(sample!A1:H7,"where E = '2005-10-10'")　＃何も抽出されない

これは、日付を検索する場合には、dateを検索条件の前につける必要があります。

⭕️ =query(sample!A1:H7,"where E = date '2005-10-10'")

これは、dateパラメータを付けないと、入力した条件（今回の場合2005-10-10）が、日付として判定されず、一致する値がないとquery関数で判断されるためです。

また、日付を直接入力するのではなく、セルから日付を参照する場合はTEXT関数とセットで使います。

⭕️ =query(sample!A1:H7,"where E = date '”& TEXT(条件入力セル,”YYYY-MM-DD”) & “'")

&で囲んで、TEXT関数という使用方法は非常によく使う方法です。スプレッドシート 上に検索条件入力枠を持たせる場合に使いますので、ぜひ慣れてください。TEXT関数がわからない方は、ググってもらうと色々記事が出てくるかと思います。

WHERE句　時刻を検索するためのtimeofday/timestamp型のdatetime　ついでにその他の型も復習

先ほどDate型を紹介しましたが、実はこれまではstring(文字列)/number(数値）の２つの型をベースにみてきていました。では、そもそもWHERE句で利用するデータ型の一覧はどうなっているのでしょうか。以下でみていきましょう。

型内容
string文字列: “aa”, ‘bb’など
number数値: 1, 1.0, -2 , -71など
booleantrue or false
date日付、yyyy-mm-dd：date “2005-10-10”
timeofday時刻、HH:mm:ss: timeofday “12:30:45”
datetime日付と時刻、yyyy-mm-dd HH:mm:ss: datetime ‘2008-03-18 12:30:34.123’

数値や文字列はそのまま入力可能ですが、日付や時刻については、検索条件の前にdate/timeofday/datetimeなどのデータ型の宣言をしないと動作しませんので注意してください。

WHERE句　最もよく使われるlike検索

LIKEパラメータを使えば、2つのワイルドカードを元にデータを抽出することができます。

ワイルドカード

  1. ％(パーセント記号）: ゼロor 1文字以上
  2. _ （アンダーバー）: 任意の1文字

Like ％を利用して検索する

特定のゼロor1文字以上として認識される％を利用すると、前方一致・内部一致・後方一致検索全てがカバーできます。

前方一致

2005年入社の人を検索したいとします。前方一致なので、検索したい2005＋%でできます。

=query(sample!A1:H7,"where E like '2005%'")

これがstarts withと一致します。

後方一致

10月10日入社の人を検索してみましょう。後方一致ですね、%+10-10で行けますね。

=query(sample!A1:H7,"where E like '%10-10'")

これでends withと同様の動作をします。

内部一致

2005年の何月でもいいから10日入社の人を検索するとします。

=query(sample!A1:H7,"where E like '2005%10'")
like検索はあくまでstr/num検索

さて、気づかれた方もいるかと思いますが、これまで日付を検索するときはDATEパラメータをつけることが必要でしたが、今回のLIKEパラメータではDATEパラメータが不要となっています。これは、日付を検索しているのですが、実際に検索している型は日付ではなく、数値なのです。

そのためLIKE検索をする場合、検索条件をDATE型に変更する必要はありません。

Like _を利用して検索する

_(アンダーバー）を利用する場合、文字数がわかっているor特定の文字数内でフレーズを検索したいときに利用します。

例えば、salaryが3桁の人を検索したいとすると以下のような形になります。

=query(sample!A1:H7,"where D like '___'")　＃アンダーバー３つ

もちろん％と_を組み合わせることも可能ですので、セットで利用するケースもよくあります。

WHERE　CONTAINS検索　 LIKE検索ににてるけど少し違う

LIKE検索ににているのですが、少し違うのはCONTAINS検索。単純に特定の文字・記号等が含まれているかどうかを判断します。LIKE検索で代替も可能ですが、フレーズ検索で利用する場合、前後文字とかの条件を考えなくてもいいので楽ですね。あとは、データ上で目安にする記号とうが含まれてる（受注したら、案件名に★をつける）データにする、みたいな運用をしている方も活用方法がありますね。

=query(データ範囲,"select * where A contains '★'")

WHERE　空白検索NULL is null /is not null

is nullを利用すると空白だけを、is not nullを利用すると空白ではないものだけ抽出可能です。

空白ではない
=query(sample!A1:H7,"select * where H is not null")
空白である
=query(sample!A1:H7,"select * where H is null")

空白ではないは利用するケースがあまり多くないかもですが、データの漏れを検索する際にis not nullはよく利用します。

WHERE　正規表現による検索MATCHES

MATCHESパラメータを使えば、自分が設定した正規表現を元にデータを抽出することができます。

正直いうと、『複数条件AND/OR』と『日付のDATE』、『空白』以外は、MATCHESだけで代用できます。

例えば先ほどLIKE _（アンダーバー）で紹介した3桁収入の人だけ抽出するは、以下のように正規表現で代替できます。

=query(sample!A1:H7,"where D matches '.{3}'")　＃アンダーバー３つ

.が任意の1文字を意味し、{数値}で繰り返しの数を指定します。正規表現の記法は色々やくに立ちますので、どこかで一覧を紹介したいと思います。現段階ではググってもらえると。

Query関数 WHERE　セル参照の方法

さて、追記ですが、セル参照の方法も紹介しておきましょう。セル参照のパターンとしては、1)文字列参照、2)セルの数値参照の２パターンが考えられます。それぞれ以下のような指定方法になります。

Query関数　WHERE　セルの参照：文字列参照

さて文字列参照というのは、例えばA1セルに、人名やセクションなど固定の文字列が入っていて、その”文字列”自体を参照したい場合です。

=QUERY('sample'!A1:H7,"where D='"&A1&"'")

要は、【'”&セル番地&”‘】となっていますが、これは“条件式の1部” & セル & “条件式の続き”という構成だと思ってもらえればOKです。

Query関数　WHERE　セルの参照：セルの数値参照

さて、セルの数値参照というのは、例えばA1セルに、数値が入っていて、その数値自体を参照したい場合です。

=QUERY('sample'!A1:H7,"where D="&A1&"")

要は【”&セル番地&”】となっていますが、これは最初の”でQueryの条件式を一回コメントアウトして、A1セルをつなげて、さらに”でクエリ句をつなげる感じだと思ってもらえればOKです。

まとめ

駆け足で色々紹介してきましたが、多いですね。それだけWHEREで検索することはqueryにおいてメジャーな機能ということになります。

ぜひいろんなパターンを習得してみてください。同じような機能なのになぜあるんだって物もあるかと思いますが、実は細かな仕様で差があったりします。それは、最後9回目のデータが汚い場合（改行が入っていたり、同じ列に違うデータタイプがあったり）において、少し説明していきたいなと思っています。

【提供講座の紹介】

スプレッドシートで始めるクエリ習得（クエリ関数）
++++++++++++++++++++++++++++++++++++++
https://www.street-academy.com/myclass/121961?conversion_name=direct_message&tracking_code=aca582bad30e1261a3c978717da46284
++++++++++++++++++++++++++++++++++++++

連載目次： spreadsheet_query関数を使いこなそう

SQL勉強もかねて、spreadsheetのquery関数の使い方に慣れていきます。簡単なselect文の使い方から、SQLとの違いも含めて勉強していきます。

  1. スプレッドシートQuery関数を使ってみよう　①概要と目的
  2. スプレッドシートQuery関数を使ってみよう　②クエリ関数の構成
  3. スプレッドシートQuery関数を使ってみよう　③SELECT句
  4. スプレッドシートQuery関数を使ってみよう　④WHERET句
  5. スプレッドシートQuery関数を使ってみよう　⑤GROUP BYを使ってみる
  6. スプレッドシートQuery関数を使ってみよう　⑥他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
  7. スプレッドシートQuery関数を使ってみよう　⑦並び替えの基本　ORDER BY句
  8. スプレッドシートQuery関数を使ってみよう　⑧SQLやるなら必須　Query関数でもサブクエリをやってみる
  9. スプレッドシートQuery関数を使ってみよう　⑨データに改行が入ってるぞ etc クエリ関数エラー対策
  10. スプレッドシートQuery関数番外編　〜Query関数のエラー原因ランキング〜
スポンサーリンク
タイトルとURLをコピーしました