QUERY関数の6個のWHEREオプションで、データ抽出を捗らせる方法!

query-6where

SPONSORED LINK

こんにちは、いろんな効率化が大好きなたぬ(@KAivlys)です!

スプレッドシートのオリジナル関数の1つ、QUERY関数使っていますか?

QUERY関数の構文
=QUERY(データ, クエリ, [見出し])
例:QUERY(A3:C21,"WHERE B='A'",-1)

意味:範囲A3:C21の中で、B列の値が『A』のものだけ抽出することが出来る。

他にもクエリの部分に『SELECT』や『WHERE』、『ORDER BY』のようなオプションを追加して、表から任意の条件でデータを抽出することが出来ます。

VLOOKUP関数でも、似たようなことは出来ますが、1つのキーに対して1つの値しか返すことが出来ません。

対して、QUERY関数はというと、条件に対して複数の値を返すことが出来ます!!

Excelじゃなくて、わざわざスプレッドシート使ってるのにQUERY関数つかっていない人は、本当に時間がもったいないです。今すぐQUERY関数を使って効率をあげましょう。

▼過去記事で、QUERY関数の使い方を紹介しました。

この記事を読めば、QUERY関数の基本的な使い方はマスターできるので、この記事ではQUERY関数に基本的な使い方について割愛します。あしからず。

スプレッドシートのQUERY関数で、条件に一致するデータをまとめて抽出する方法

2018.07.01

この記事では、QUERY関数の一番肝心な『WHEREオプション』について、もっと深く紹介します!

実は、『Where C='条件'』以外の書き方にも、もっと条件を柔軟に設定できるオプションがいくつか用意されています。

今回は、その中から本当に実務で使える6個のオプションだけ厳選してみました。

複数の条件を設定|AND/OR

これから紹介する2つの方法を用いれば、単一の条件ではなく、複数の条件を設定することができます。

それぞれ見てみましょう。

AND|条件Aかつ条件Bを同時に満たす

ANDパラメータを使えば、『条件Aかつ条件B』を同時に満たすものを抽出することが出来ます。

ANDオプションの使用例
=QUERY(B2:F23, "WHERE C='くさ' AND E='アローラ'", -1)
query-6where1

こちらで確認できます。

例では、複数の条件『C列の値がくさ』『E列の値がアローラ』を同時に満たすものだけ表示しています。

OR|条件Aまたは条件Bを満たす

ORパラメータを使えば、『条件Aまたは条件B』を満たすものを抽出することが出来ます。

ORオプションの使用例
=QUERY(B2:F23, "WHERE C='くさ' OR E='アローラ'", -1)
query-6where2

こちらで確認できます。

例では、複数の条件『C列の値がくさ』『E列の値がアローラ』のいずれかを満たすものだけ表示しています。

日付を条件に設定|DATE

DATEパラメータを使えば、セットした日付と一致したものを抽出することが出来ます。

DATEパラメータを使わないと、セットした日付を日付データとして認識してくれないので、日付を検索したい場合は、必ずDATEパラメータを使いましょう。

DATEオプションの使用例
=QUERY(B2:F23, "WHERE B= DATE '2018-07-01'", -1)
query-6where3

こちらで確認できます。

例では、B列の値が『2018ー07ー01』を満たすものを表示しています。

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

query-6where5

こちらで確認できます。
セルから日付を参照する場合
=QUERY(B2:F23,"WHERE B= DATE '"& TEXT(H3,"YYYY-MM-DD") & "'",-1)

例では、H3セルに入力した日付データをトリガーとして、B列の値とそれが一致したものを表示しています。

TEXT関数を使わなかった場合、下図のようなエラーが表示されてるので気を付けましょう。

query-6where4

フォーマットを『yyyy-MM-dd』に直せとエラーがでます

TEXT関数は、数値の表示形式を整えてくれるので、丁寧なデータ作りには欠かせません。

最低限のマナーとして、会得しておきたい関数であるといえますね。

過去記事にTEXT関数についてまとめているので、興味がある方はどうぞ(^o^)

【必見】スプレッドシートを共有するときはTEXT関数で表示形式を整えよう

2018.02.09

空白を検索|IS NULL

IS NULLパラメータを使えば、空白なセルを検索することができます。

IS NULLオプションの使用例
=QUERY(B2:F23,"WHERE B IS NULL", -1)
query-6where6

こちらで確認できます。

IS NOT NULL|空白ではないものを検索

空白じゃないものを検索したいときもありますよね。

そんなときは、IS NOT NULLパラメータを使えば、空白ではないセルを検索することができます。

IS NOT NULLオプションの使用例
=QUERY(B2:F23,"WHERE B IS NOT NULL", -1)
query-6where7

こちらで確認できます。

ある文字列を含むものを検索|CONTAINS

CONTAINSパラメータを使えば、自分が設定した文字列がセルに含まれているものだけを抽出することができます。

CONTAINSオプションの使用例
=QUERY(B2:F23,"WHERE B CONTAINS 'カプ'", -1)
query-6where8

こちらで確認できます。

例では、B列に『カプ』という文字列を含むポケモンだけを抽出しています。

ワイルドカードで文字列を検索|LIKE

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

少しだけ敷居が高いかもしれませんが、文字列検索をより柔軟にしてくれるので、ぜひ覚えてみてください!

ワイルドカード
  1. %(パーセント)|ゼロ文字または1文字以上
  2. _(アンダーバー)|任意の1文字

それぞれ見てみましょう。

%|ゼロ文字または1文字以上

%は、『ゼロ文字または1文字以上』を意味するワイルドカードです。likeを使った検索のとき限定で使うことができます。

LIKEオプション-ワイルドカード:%の使用例
=QUERY(B2:F23, "WHERE B LIKE '%ス'", -1)
query-6where9

こちらで確認できます。

この場合、文字数は何でもいいけど、『ス』で終わる文字列だけを表示します。いわゆる後方一致というやつです。

逆に前方一致検索したい場合は、『ア%』のような感じにすればいいです。この場合、『ア』から始まる文字列だけ抽出されます。

また、『ア%ス』と設定すれば、『アマリリス』や『アリス』、『アノニマス』のような、『ア』から始まって『ス』で終わる文字列だけを表示できます。

_|1文字

_は、『任意の1文字』を意味するワイルドカードです。%と同様に、likeを使った検索のとき限定で使うことができます。

LIKEオプション-ワイルドカード:_の使用例
=QUERY(B2:F23, "WHERE B LIKE '______'", -1)
query-6where10

こちらで確認できます。

ちょっと見にくいですが、_を6回連続して書いてます。なので、6文字の文字列を抽出することができます。

6文字限定で、『ア』から始まって『ス』で終わる文字列を検索したい場合は、『ア____ス』とすれば良いです。

正規表現で文字列を検索|MATCHES

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

WHEREのオプションの中で一番難易度が高いですが、MATCHESだけで出来ることが多いのでぜひ覚えてみてください。

ぶっちゃけた話、『複数条件AND/OR』と『日付のDATE』、『空白』以外は、MATCHESだけで代用できます。

MATCHESオプションの使用例
=QUERY(B2:F23,"WHERE B MATCHES '正規表現'", -1)
=QUERY(B2:F23,"WHERE B MATCHES '.{6}'", -1)
query-6where11

こちらで確認できます。

例えば、LIKEオプションの_で紹介した『6文字の文字列』だけを抽出するものは『.{6}』と代用出来ます。

スプレッドシートの正規表現については、過去記事でまとめているので、興味がある方はどうぞ!

知らないと損!正規表現を使ってスプレッドシートの文字列を便利に抽出する方法

2018.01.27

さいごに

今回は、QUERY関数で使えるWHEREの6つのオプションについて紹介しました。

全部を一気に覚える必要はありません。ケースバイケースで使えれば十分です。

QUERY関数のWHERE以外のものを確認したい場合は、下の記事をどうぞ。

スプレッドシートのQUERY関数で、条件に一致するデータをまとめて抽出する方法

2018.07.01

QUERY関数のWHEREを自分の思うがままに扱って、データ抽出を捗らさせましょう。

それでは(^o^)

SPONSORED LINK

query-6where

Time Ticketはじめました
実務で即戦力になるためのスプレッドシート講座開講!

記事を読んでも、よく分からなかった人やもっと深く知りたい人はこちらからご連絡ください。

ブログ特典!!

『クーポンコード:9876』とメッセージいただければ、どのレベルのコースでも初級コースと同じ価格で教えます!!

スプレッドシートをより深く学んで、実務で即戦力になりませんか?

ABOUTこの記事を書いてる人

たぬ(谷口健太)

25歳社会人。本職は福岡の通販会社でWebマーケッターやっとります。 プログラミング✕マーケティング=最強説を唱えており、非効率的な世の中に革命を起こそうと本ブログを設立。 ストレングスファインダー:戦略性・最上志向・目標志向・未来志向・個別化

NEW POSTこのブログの最新記事