EXCELではなくスプレッドシートを使う理由の1つに、「スプレッドシートではQUERY関数が使えるから」という人も多いのではないでしょうか。
当記事では、
「QUERY関数が便利なのは知ってるけど、selectとかwhereとかよくわからないよ」
という方向けに、
- QUERY関数の基礎知識
- where句の役割
- where句で条件を指定する方法
- where句で複数条件を指定する方法
- where句で特別な条件を指定する方法
などについて解説していきます。
QUERY関数の基礎知識
まずはQUERY関数の基礎知識からお話します。
QUERY関数は表計算ソフトの中でGoogleスプレッドシートでしか使うことのできない「特別な関数」です。
QUERY関数は以下の公式で構成されています。
"select 出力データ列
where 条件式
group by グルーピング列
order by ソート条件
limit 出力データ数
offset 開始行
label 列名")
QUERY関数はDB操作に使われるSQL文をベースに作られています。
QUERY関数を使えばデータのフィルタリングやソート、グルーピングなどが驚くほど簡単にできるようになります。
この記事ではQUERY関数のwhere句の使い方について解説するので、
「まずはQUERY関数全般について知りたい」
という方は、QUERY関数の基礎から解説している「QUERY関数の基礎から応用まで!使い方を徹底解説【完全版】」から先にお読みください。
where句の役割
QUERY関数におけるwhere句の役割はズバリ「データのフィルタリング」です。
「価格が100円以上の商品リスト」
「8月の購入数が5点以上の顧客」
「10時〜14時の購買データ」
などなど、特定の条件でフィルタリングしたデータが欲しいときにQUERY関数は有効です。
条件が複雑になると関数の内容も少し複雑になりますが、基本をしっかり抑えていれば同じことの繰り返しなのでそこまで難しくありません。
where句で条件を指定する方法
それでは早速、where句で条件を指定する方法について解説します。
まずは、条件式の記述に使用する比較演算子を紹介します。
以下がQUERY関数で使える比較演算子たちです。
比較演算子 | 意味 |
---|---|
= | 等しい |
!= または <> | 等しくない |
< | より小さい |
> | より大きい |
<= | 以下 |
>= | 以上 |
QUERY関数のwhere句では、使用した比較演算子の条件式が「TRUE」の行のデータが出力されるようになります。
たとえば、以下のようなデータセットに対して
以下のような条件式(B列が200より大きい行のA,B列のデータを出力)でQUERY関数を実行します。
=query(A1:B6,"select * where B>200")
すると、
このように、単価が200円より大きい商品が出てきますね。
ちなみに、
=query(A1:B6,"select * where B>=200")
このように「200円以上」の条件だと、
単価が200円ピッタリの青りんごも含めて出力されます。
where句で複数条件を指定する方法
where句では、単一条件だけでなく複数条件を指定することもできます。複数条件を指定することで余分なデータを除去できるため、抽出データが綺麗になります。
また、複数条件を指定する際には「AND条件(かつ)」と「OR条件(または)」の指定が必要です。AND条件にするかOR条件にするかで抽出結果は全く別物になるので注意しましょう。
where句で複数条件を指定する方法や、AND条件・OR条件の解説については下記の記事で詳しく解説しています。
where句で特別な条件を指定する方法
最後に、特別な条件を指定したいときに使う演算子について紹介します。
QUERY関数のwhere句で使用が想定される特別な演算子は、以下の4つです。
演算子 | 意味 |
---|---|
A like '%B%' | AがBを含む |
date 比較演算子 'YYYY-MM-DD' | 日付の条件指定 |
timeofday 比較演算子 'hh:mm:ss' | 時刻の条件指定 |
not 条件A | 条件Aを満たさない |
以下のような購買データを例に解説します。
【like】文字列を含む条件式
商品名に「肉」を含む購買データを取得するとします。
QUERY関数は以下のようになり、シングルクォーテーション「'」とパーセント「%」の間に、部分一致の条件にしたい文字列を記述して指定します。
=QUERY(A1:D6,"select * where C like '%肉%'")
出力結果は以下のようになり、商品名に「肉」が含まれる商品のみ出力されていることがわかります。
【date】日付の条件式
2019年9月18日の購買データを取得するとします。
QUERY関数は以下のようになり、「date」のあとに、日付を「YYYY-MM-DD」のテキスト形式で記述して指定します。
=QUERY(A1:D6,"select * where A=date '2019-09-18'")
出力結果は以下のようになり、2019年9月18日の購買データのみ出力されていることがわかります。
【timeofday】時刻の条件式
午前中の購買データを取得するとします。
QUERY関数は以下のようになり、「timeofday」のあとに、時刻を「hh:mm:ss」のテキスト形式で記述して指定します。
=QUERY(A1:D6,"select * where B<timeofday '12:00:00'")
出力結果は以下のようになり、午前中の購買データのみ出力されていることがわかります。
【not】否定の条件式
商品名に「肉」を含まない購買データを取得するとします。
QUERY関数は以下のようになり、「like」での条件式を「かっこ()」で囲んだ上で、頭に「not」をつけて指定します。
=QUERY(A1:D6,"select * where not(C like '%肉%')")
出力結果は以下のようになり、商品名に「肉」を含まない商品のみ出力されていることがわかります。
まとめ
今回はQUERY関数のwhere句の使い方について解説しました。
where句を理解すると、QUERY関数をかなり自由に使いこなせるようになると思います。
QUERY関数はとっても便利な関数なので、ぜひ使い方をマスターして、業務の効率化を目指しましょう。
では、また。