- 読んで欲しい人
- Query関数(クエリ関数)シリーズ予定 (あくまで予定)
- Query関数:WHERE句の基本
- まとめ
読んで欲しい人
- SQLに興味があるが、一から環境やデータ整備するのがめんどくさい人
- vlookupとかでデータ抽出しているが、限界を感じている人
- データ分析したい人
- ノンプログラマーでもクエリぐらいは使いたいって人
- クエリって美味しいの?って人(クエリは美味しいですよって意味も込めて)
Query関数(クエリ関数)シリーズ予定 (あくまで予定)
- 概要と目的
- クエリ関数の構成
- 基本の基本 SELECT句(全部取得、特定カラムだけ取得)
- 抽出の基本 WHERE句を使ってみる(数値一致、大小、日付一致、大小etc)←今回
- GROUP BY句を使ってみる
- 他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
- 並び替えの基本 ORDER BY句
- SQLやるなら必須 サブクエリをやってみる
- データに改行が入ってるぞ 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条件でつないでいます。これで、2つの条件共にみたすデータだけが抽出できます。この後説明していきますが、条件は=だけでなく、様々(大小や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(数値)の2つの型をベースにみてきていました。では、そもそも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つのワイルドカードを元にデータを抽出することができます。
ワイルドカード
- %(パーセント記号): ゼロor 1文字以上
- _ (アンダーバー): 任意の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 '___'") #アンダーバー3つ
もちろん%と_を組み合わせることも可能ですので、セットで利用するケースもよくあります。
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}'") #アンダーバー3つ
.が任意の1文字を意味し、{数値}で繰り返しの数を指定します。正規表現の記法は色々やくに立ちますので、どこかで一覧を紹介したいと思います。現段階ではググってもらえると。
Query関数 WHERE セル参照の方法
さて、追記ですが、セル参照の方法も紹介しておきましょう。セル参照のパターンとしては、1)文字列参照、2)セルの数値参照の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回目のデータが汚い場合(改行が入っていたり、同じ列に違うデータタイプがあったり)において、少し説明していきたいなと思っています。
【レクチャー関係紹介】
MENTA
メンターを探すサイトも存在します。あまりGASやQuery関数を教えますってメンターはそこまで多くはないのが実情です。ノンプロエンジニア向けではなく、エンジニア向けor エンジニア志望者向けなので、Query関数はあまり対象にならないかもしれません。他方で純粋SQLを教えてほしいというリクエストであれば、多くのメンターが集まると思います。

ちなみに、私のレクチャーページも紹介しておきます。
【Query関数】 相談PLAN連載目次: spreadsheet_query関数を使いこなそう
SQL勉強もかねて、spreadsheetのquery関数の使い方に慣れていきます。簡単なselect文の使い方から、SQLとの違いも含めて勉強していきます。
- スプレッドシートQuery関数を使ってみよう ①概要と目的
- スプレッドシートQuery関数を使ってみよう ②クエリ関数の構成
- スプレッドシートQuery関数を使ってみよう ③SELECT句
- スプレッドシートQuery関数を使ってみよう ④WHERET句
- スプレッドシートQuery関数を使ってみよう ⑤GROUP BYを使ってみる
- スプレッドシートQuery関数を使ってみよう ⑥他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
- スプレッドシートQuery関数を使ってみよう ⑦並び替えの基本 ORDER BY句
- スプレッドシートQuery関数を使ってみよう ⑧SQLやるなら必須 Query関数でもサブクエリをやってみる
- スプレッドシートQuery関数を使ってみよう ⑨データに改行が入ってるぞ etc クエリ関数エラー対策
- スプレッドシートQuery関数番外編 〜Query関数のエラー原因ランキング〜