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

読んで欲しい人

• 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など
boolean	true 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文字を意味し、{数値}で繰り返しの数を指定します。正規表現の記法は色々やくに立ちますので、どこかで一覧を紹介したいと思います。現段階ではググってもらえると。

まとめ

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

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

連載目次： 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 クエリ関数エラー対策