Googleスプレッドシート QUERY関数 超応用例 8（where句 徹底解析2）

Googleスプレッドシートの最強関数 QUERY関数について書いたnoteの第8回です。

前回に続き QUERY関数のwhere句を掘り下げていきます。

👇これまでのQUERY関数シリーズは マガジンにまとめています。QUERY関数をディープに使いこなしたい人は必読です！

前回はQUERY関数の where句 徹底解析1 で、where句の基本や空白除去や条件をセル参照する方法などを紹介しました。

where句で使える 演算子を理解する

今回は前回 さらっと紹介だけした、where句で使える 演算子を徹底解析していきます。

1. 基本の比較演算子

2. 複雑な文字列比較演算子の基本

3. 論理演算子

演算子はこの3つです。

ただし「複雑な文字列比較演算子」は奥が深いので、今回は基本の使い方、動作のみを紹介し、応用編は次回となります。

where句 基本の比較演算子

まずは where句で使える基本の比較演算子を理解していきましょう。

$$\begin{array}{ll} \text{比較演算子}&\text{解説}\\ \hline \text{<=}&\text{〇〇以下}\\ \hline \text{ <}&\text{〇〇より小さい}\\ \hline \text{ >}&\text{〇〇より大きい}\\ \hline \text{ >=}&\text{〇〇以上}\\ \hline \text{ =}&\text{〇〇と一致する}\\ \hline \text{ !=}&\text{〇〇と一致しない}\\ \hline \text{ <>}&\text{〇〇と一致しない}\\ \hline \end{array}$$

こんなの理解してるよ！って人が多いかもしれませんが、実はスプレッドシート上で使う演算子と挙動が違う部分も結構あります！

クエリ文の where句で使う比較演算子と スプレッドシート上の数式で使う比較演算子の違いをまとめると

1. 一致しないで <> だけでなく != も使える

2. where句の比較演算子は = の一致が 厳密判定

3. 不等号で 日本語（漢字）を比較した時に違いがある

4. 空白の扱いが違う

5. where句の比較演算子は 型違いは比較できない

このようになっています。それぞれ解説していきます。

1. 「一致しない」は <> と != が使える

QUERY関数の第2引数 クエリ文の中は、スプレッドシートの理（ルール）とは別のルールの領域です。

それを如実に感じるのが、「一致しない」という条件を　!= で記述できる点です。

シート上の数式では「一致しない」は <> で記述しますが、クエリ文の where句では <> も使えますが、それに加えて　!= というプログラミングの不等価演算子 が使えます。

たとえば、👆のように 2列目の区分が A に一致しない という条件は、

と書くことも出来ますし、

と書くことも出来ます。

<>と != はどちらもクエリ文では「一致しない」を意味し、挙動は完全に一緒と思われるので、どちらを使ってもOKです。

なお、前回書きましたが「空白でない」を条件とする場合（空白除外）は、

 <>''　や　!=''　とせず、　is not null　という特殊な書き方を使います。

where句で空白データを除外する

2. = の一致は圧倒的に厳密判定

過去に何度か紹介していますが、Googleスプレッドシートの イコールによる一致判定は、かなーり緩めです。

上の表でわかる通り、全角・半角、ひらがな・カタカナ、さらに 小さい文字（あに対するぁ）、伸ばし棒と母音 を区別しません。

フリーレン と 

ふりーれん
ふりいれん
ふりぃれん
ﾌﾘｲﾚﾝ

はイコールで判定すると 全て TRUE（同じである）となります。

この緩い一致判定のせいで COUNTIFや XLOOKUPが誤った結果を返すことがあるんで困ります。

COUNTIFや XLOOKUPを厳密判定で使うテクニックは、過去noteで紹介しています。

しかし、QUERY関数のクエリ文内の = イコールによる一致は、かなり厳密です。

たとえば

=QUERY(A:B,"where Col2 ='フリーレン'")

と

=FILTER(A:B,B:B="フリーレン")

は、どちらも A:B を B列（2列目）が フリーレン と一致するものだけを抽出する式ですが、

かなり結果に違いが出てるのがわかりますね。

QUERY関数が 完全一致の 6 フリーレン　だけを返しているのに対して、FILTER関数の方は = イコール一致で条件を判定している為、1～6の 偽フリーレンを含めた全てを返しています。

もちろん FILTER関数でも EXACT関数を使うなど、厳密に判定する方法はあります。

でも、「手っ取り早く厳密な一致フィルターを使いたい時は、QUERY関数が使える」ってことも覚えておくとよいでしょう。

3. 不等号 <=, <, >=, > は数値以外にも使える

これらは基本的には数値型の列に対して使うもので、

こんな感じで利用します。

しかし不等号の比較演算子は数値の比較専用というわけではなく、= と同じく 数値型以外の文字列型、ブール値（ブーリアン型）、日付、時刻、日時 にも利用可能です。※日付、時刻、日時に関しては今回は触れません

たとえば2列目（区分）が、A,B,Cの3種類のいずれかが入るというルールであれば、 B または C の行を抽出したい時には

このように where Col2 > 'A'　と書く方法があります。

文字列の不等号は基本的には一般的な並び順（アルファベットであれば AよりもBが大きくBよりもCが大きい）がルールとなっており、ざっくり言えば 後に登場する文字の方が大きい です。

文字列における不等号は、別にクエリ文の中だけの仕様というわけではなく、シート上で使う数式でも同じ仕様になっています。

ただアルファベットやひらがな、カタカナの比較であれば問題ありませんが、漢字を大小比較する時は注意が必要です。

このようにシート上では、阿部よりも川端は大きい、田中よりも和田は大きいと判定されますが、QUERY関数内で比較した場合は

阿部 ＞ 川端
田中 ＞ 和田

と 判定結果がシート上の比較演算と違っているのが、わかりますね。

QUERY関数のこの仕様は、後で登場する order by 句の並び替えに影響があるので、order by句の解説の時に詳しく触れたいと思います。

4. 比較演算子で空白 Null は最弱（なによりも小さい）

シート上の空白は、比較演算子を使った数式で 数値と比較すると 0と 同一と見なされます。

このようにマイナスの値よりも空白は 大きいと 判定され、0と空白は =で一致と判定されます。

ちなみに TRUE / FALSE のブール値と空白をシート上で演算子で比較した場合も

👆このようになります。

一方 where句で空白 Nullを比較演算子で扱う場合は、シート上での扱いとは大きく異なり、

たとえば

とした時に Col3（3列目）が 空白の行も抽出してしまいます。

これは クエリ文の中では

正の数（10）　>　0　>　負の数（-5） ＞ 空白（Null）

となっている為です。

ちなみに ブール値型の列の場合は

TRUE > FALSE > 空白（Null）

このようになっています。

つまり where句で 比較演算子で大小比較した際、空白（Null）は最も小さいものとして扱われるということです。

この仕様の影響で

=QUERY(A:C,"where Col3 < -5")

このように 第1引数のデータをお尻を決めない A:C という書き方をして、比較演算子で < （より小さい）、<= （以下） を条件にした場合は、

👆このように 2件ヒットしたように見えても、実は赤枠内は 全て空白行を抽出した状態となっているってことです。

ROWS関数で 結果の行数を確認すると

=ROWS(QUERY(A:C,"where Col3 < -5"))

が 977 を返しているのがわかりますね。

where句で 列を < や <= で絞り込む場合は、空白も抽出してしまうので注意しましょう。

5. 違う型は比較ができない

シート上では 比較演算子を使って 違う型を比較することが出来ます。

ブール値は数値化すると TRUEは1、FALSEは0 なんですが、単純に 比較演算子で数値と比べた時は TRUEもFALSEも 数値型より大きいと判定されます。

文字列を加えると

数値　<　文字　<　ブール値

このようになっています。

一方 クエリ文の比較演算子は 型違いは判定できません。

文字列型の Col2に対して

=QUERY(A:C,"where Col2 > 0")

=QUERY(A:C,"where Col2 < 0")

どちらも 空白行含め、1件も抽出できていないのがわかりますね。

つまり型が違っている場合は比較できない、比較しても意味がないってことです。

QUERY関数のクエリ内は「型を意識することが重要」ってのを実感しますね。

where句の 複雑な文字列比較演算子 の基本

つづいて前回は紹介だけで終わってしまった、複雑な（高度な）文字列比較演算子を紹介していきましょう。

$$\begin{array}{ll} \text{複雑な文字列比較演算子}&\text{解説}\\ \hline \text{contains}&\text{〇〇を含む}\\ \hline \text{starts with}&\text{〇〇から始まる}\\ \hline \text{ends with}&\text{〇〇で終わる}\\ \hline \text{like}&\text{ ワイルドカードを使った一致判定}\\ \hline \text{matches}&\text{正規表現を使った一致判定}\\ \hline \end{array}$$

これらを使いこなせれば、一気にwhere句で出来ることが広がります！

実際に操作して理解を深められるようにサンプルデータを用意しました。
こちらをA1セルに貼り付けてテーブル化、sample というテーブル名をつけて 実際に試してみましょう。

まずは基本的な使い方だけ紹介します。

氏名	ふりがな	性別	年齢	血液型	住所	既婚
佐藤 健太	さとう けんた	男	32	A	東京都千代田区	TRUE
田中 美咲	たなか みさき	女	28	B	大阪府大阪市	FALSE
鈴木 大輔	すずき だいすけ	男	45	O	愛知県名古屋市	TRUE
高橋 由美	たかはし ゆみ	女	25	AB	福岡県福岡市	FALSE
伊藤 誠	いとう まこと	男	39	A	北海道札幌市	TRUE
渡辺 さおり	わたなべ さおり	女	31	B	神奈川県横浜市	TRUE
山本 拓也	やまもと たくや	男	27	O	京都府京都市	FALSE
中村 莉子	なかむら りこ	女	42	AB	埼玉県さいたま市	TRUE
小林 健吾	こばやし けんご	男	36	A	千葉県千葉市	TRUE
加藤 あや	かとう あや	女	29	B	兵庫県神戸市	FALSE
吉田 翔太	よしだ しょうた	男	34	O	広島県広島市	TRUE
山田 奈々	やまだ なな	女	26	AB	宮城県仙台市	FALSE
木村 悠斗	きむら ゆうと	男	48	A	東京都新宿区	TRUE
林 美穂	はやし みほ	女	33	B	大阪府堺市	TRUE
斎藤 健太	さいとう けんた	男	24	O	愛知県豊田市	FALSE
松本 由紀	まつもと ゆき	女	40	AB	福岡県北九州市	TRUE
井上 大輝	いのうえ だいき	男	37	A	北海道旭川市	TRUE
河野 さゆり	こうの さゆり	女	30	B	神奈川県川崎市	FALSE
森下 亮太	もりした りょうた	男	23	O	京都府宇治市	FALSE
石井 舞	いしい まい	女	43	AB	埼玉県川越市	TRUE
原田 拓郎	はらだ たくろう	男	38	A	千葉県船橋市	TRUE
坂本 理沙	さかもと りさ	女	27	B	兵庫県姫路市	FALSE
西村 健二	にしむら けんじ	男	35	O	広島県呉市	TRUE
中川 愛	なかがわ あい	女	29	AB	宮城県石巻市	FALSE
藤田 勇太	ふじた ゆうた	男	46	A	東京都世田谷区	TRUE
遠藤 真紀	えんどう まき	女	32	B	大阪府東大阪市	TRUE
村上 グエン	むらかみ ぐえん	男	26	O	愛知県岡崎市	FALSE
大野 晴香	おおの はるか	女	41	AB	福岡県久留米市	TRUE
金子 拓海	かねこ たくみ	男	39	A	北海道函館市	TRUE
三浦 由佳	みうら ゆか	女	31	B	神奈川県相模原市	TRUE
古川 翔	ふるかわ しょう	男	25	O	京都府舞鶴市	FALSE
高木 菜摘	たかぎ なつみ	女	44	AB	埼玉県所沢市	TRUE
青木 亮	あおき りょう	男	37	A	千葉県松戸市	TRUE
杉山 美香	すぎやま みか	女	30	B	兵庫県尼崎市	FALSE
寺田 健太	てらだ けんた	男	24	O	広島県福山市	FALSE
松浦 陽子	まつうら ようこ	女	42	AB	宮城県気仙沼市	TRUE
野口 翔太	のぐち しょうた	男	47	A	東京都杉並区	TRUE
川崎 シェーン	かわさき しぇーん	女	33	B	大阪府枚方市	TRUE
福島 大地	ふくしま だいち	男	27	O	愛知県一宮市	FALSE
石川 梓	いしかわ あずさ	女	40	AB	福岡県大牟田市	TRUE
岡本 健吾	おかもと けんご	男	36	A	北海道小樽市	TRUE
西田 恵	にしだ めぐみ	女	29	B	神奈川県平塚市	FALSE
島田 マイケル	しまだ まいける	男	34	O	京都府長岡京市	TRUE
谷口 遥	たにぐち はるか	女	26	AB	埼玉県春日部市	FALSE
堀内 雄太	ほりうち ゆうた	男	48	A	千葉県柏市	TRUE
池田 詩織	いけだ しおり	女	35	B	兵庫県西宮市	TRUE
金沢 健太	かなざわ けんた	男	28	O	広島県尾道市	FALSE
橋本 美咲	はしもと みさき	女	43	AB	宮城県白石市	TRUE
荒木 慎吾	あらき しんご	男	23	A	東京都練馬区	FALSE
稲垣 彩	いながき あや	女	38	B	大阪府吹田市	TRUE

「含む」を判定する contains

まずは 一番よく使う「含む」を判定する containsです。

この式は、sampleというテーブルから 1列目（Col1）が 大 という文字を含むデータだけを抽出する式です。

where Col1 contains '大' 

条件を記述しているのが 👆 この部分です。
前回書きましたが、単語間にはスペースを入れましょう。

名前に 大が付くデータが 4件ヒットしましたね。

含む検索したいキーワードは シングルクォートで括って contains  の後ろに記述します。

=QUERY(sample[#ALL],"where Col6 contains '名古屋'")

住所（Col6）が 名古屋 を含む という条件にしたい場合は 👆こんな感じ。

記述自体はわかりやすく、簡単ですね。

「から始まる」を判定する starts with

「含む」ではなく データの先頭に指定した文字がある、つまり「〇〇から始まる」文字を条件としたい場合は starts with を使います。

間違って start with って書きがちなんで注意です。 starts withです。

Col1 starts with '大'

と記述することで、先ほどの contains では 大 を含む判定で抽出されていた

鈴木 大輔 や 井上 大輝

などの 途中に 大 が付く名前の人は対象外となり、先頭に 大 が付く 

大野 晴香

のみ抽出されました。

同じように 住所（Col6）が 東京都 から始まるデータだけを抽出したい場合は

=QUERY(sample[#ALL],"where Col6 starts with '東京都'")

このようになります。

「で終わる」を判定する ends with

逆に データの最後に指定した文字がある、つまり「〇〇で終わる」文字を条件としたい場合は ends with を使います。

これも end with って間違って覚えがちなんで注意です。 ends withです。

Col1 ends with '太'　と記述することで、太 で終わる名前の人だけを抽出しました。

途中で太が入る 田中 太郎 や 太で始まる 太田 光 など（があった場合）は除外されます。

ワイルドカードを使った判定ができる like

ワイルドカードを使った「一致」判定ができるのが、likeです。

いやいや「この流れだったら likes ちゃうんかーい！」「s つかんのかーい！」と言いたくなりますねｗ

likeは sが付きません。

where句の likeでは 2つの ワイルドカード が使えます。

SQLを利用している人は慣れているのかもしれませんが、ワイルド―カードは、どうしても COUNTIFや VLOOKUPで使える * や ?  の印象が強いんで 混乱しちゃいますね。。

likeは contains、starts with、 ends with の代わりとして使用することができます。

✅likeを contains の代わりに使う

このように Col1 like '%大%'　と 検索キーの文字を %で挟むことで、「大 を含む」という条件を記述できます。

%は 0 個以上の任意の文字を表すので、 大が先頭に来る（大の前に何も文字が無い）ケース  大野 晴香 も条件を満たすと判定され抽出されます。

✅likeを starts with の代わりに使う

前に付く %を無くしキーワード（大）の後ろにだけ %を付けることで、starts withの代替として「大 で始まる」を条件とすることが出来ます。

✅likeを ends with の代わりに使う

同じく前にだけ %を付けることで ends with の代替として、「太 で終わる」条件を記述できます。

✅likeの ワイルドカード _（アンダースコア）を使う

もう一つのワイルドカード _（アンダースコア）ですが、たとえば 

Col1 like '_田%'

と書くことで 田 の前に 1文字だけ付く人、つまり 氏名の2文字目に 田 が付く人（〇田さん）だけを条件とすることが出来ます。

ワイルドカードを使って contais、starts with、ends with を代替できる likeは非常に使い勝手がよく重要な演算子なので 是非覚えておきましょう。

正規表現を使った一致判定ができる matches

where句で使える 複雑な文字列比較演算子の最後は matches です。

これは正規表現が使える強力な演算子で、likeを含めた全ての複雑な文字列比較演算子をこれ一つでカバーできるチート演算子です。

ただし「正規表現」の理解が必須なので、ややハードルの高い、初心者には使いこなすのが難しい 演算子とも言えます。

ここで正規表現の解説を始めると、それだけで全〇回というシリーズになってしまうので、とりあえず正規表現の記述方法は他サイトを参考にしてください。

また、注意点として matches の正規表現は 記述した内容と「一致する」ものを TRUE（条件に一致する）と判定します。

REGEXMATCH関数のように 部分一致で TREU となる仕様ではありません。

だから cotains のようなキーワードを「含む」条件で使いたい場合は

ではなく

となります。 前後に

.*  （任意の一文字 .　の * 0回以上の繰り返し）

を入れて .*大.* とすることで、likeのワイルドカード %大%  と同じ 「大」を含むという条件にしています。

記述方法は 他の文字列比較演算子と同じで　matches '.*大.*'　と、正規表現のルールに則った記述の 文字列の前後を 'シングルクォートで括ります。

「含む」と同様に

starts with や ends with の代替、likeのワイルドカード 活用例と同じ2文字目に 田 が来る名前の絞り込み

など全て matches の正規表現で書くことが出来ます。

まぁ ここまでは likeで済むレベルなんですが、matchesを使わないと難しい条件記述や 非常に便利な応用例もあるので、その辺りは次回紹介したいと思います。

複数条件を組み合わせる 論理演算子

$$\begin{array}{ll} \text{論理演算子}&\text{解説}\\ \hline \text{and}&\text{条件1、条件2をどちらも満たす}\\ \hline \text{or}&\text{条件1、条件2のどちらかを満たす}\\ \hline \text{not}&\text{条件1を満たさない}\\ \hline \end{array}$$

これまでは where句で 1つの条件で絞り込むケースだけを紹介してきましたが、where句は 複数の条件を組み合わせて抽出することも出来ます。

複数条件を組み合わせる and、or、そして 条件を否定（反転）させる not 、この 3つの論理演算子の使い方を見ていきましょう。

複数条件を組み合わせる and と or

これは実際の利用例を見ていくのがわかりやすいでしょう。

👇 性別が 男　かつ（and）　血液型が A　のデータを出力

👇年齢が 30以上で（and） 住所が東京都（から始まる）データだけを出力

👇血液型が A または（or） Bのデータだけを出力

👇年齢が20代　20以上 かつ（and）29以下 を出力

このように 使います。

シート上の比較演算子と同じで

20 <= Col4 <= 29 のように 3つ以上を繋げて書くことは出来ません。

20 <= Col4
and
Col4 <= 29

このように条件は 2つを比較したものを andで連結する必要があります。

複雑な 組み合わせの条件は (　)　カッコで制御

and や or が複雑に組み合わさる条件の場合は、条件の塊をカッコで括ることで 条件を明確にする必要があります。

たとえば、「年齢 が30より低く、血液型が AB または O のデータを抽出する」という条件を指定するのに、

このように単に3つの条件を連続して記述してしまうと、以下のようになぜか年齢が 45歳のデータが出力されています。

これは 👆の書き方だと

と受け取られてしまい、O型の人は年齢関係なく出力されてしまう為です。

これを回避する為に and や or を組み合わせる場合は、条件の塊（ブロック）をカッコで括ります。今回の場合は

( Col5 = 'AB' or Col5 = 'O' )

このように 血液型（Col5）が ABまたはOという or 条件をカッコで括り一つの塊とすることで、

年齢が30歳
かつ（and）
血液型が AB か（or） O

のデータだけを正しく抽出できます。

not で条件を否定

基本の比較演算子であれば

=（一致する）　の反対は　!=　<>　（一致しない）
>　（より小さい）　の反対は　<　（より大きい）

このように 反対の演算子が用意されています。

しかし、

contains（含む）　の反対は　「含まない」演算子・・・？
satats with (〇〇で始まる）　の反対は　「〇〇で始まらない」演算子・・・？

このように 複雑な文字列比較演算子 を否定する（反転させた）、「含まない」や「○○で始まらない」といった演算子は用意されていません。

この「含まない」「○○で始まらない」という条件を記述する時に使うのが notです。

つまり not は基本の比較演算子というよりは、contains や starts with、
 like、matches などの複雑な文字列演算子と組み合わせて使うことで真価を発揮するものってことです。

たとえば 住所（Col6）が東京都で始まらない データを出力する場合は

このように 条件の前に not を付けることで その後に記述された条件を否定します。

and、or　は条件の間に入れるもの
not は 条件の 頭につけるもの

と覚えましょう。

not も複数条件と組み合わせて利用する場合は、 カッコと合わせる必要があります。

たとえば 住所（Col6）が 東京都 か 大阪府 で始まらない （かつ） 年齢（Col4）が40以上のデータを出力したい、といった場合は

このように  or条件の塊（ブロック）に カッコを付けて、その前に notを記述することで、

カッコ部分全体を notで否定し、Col6 が 東京都 か 大阪府で 始まらない という条件にします。

ちなみに これら論理演算子の 評価の優先順位は

not ＞ and ＞ or

となっていますが、これは気にせずとにかくカッコで括ることで、優先度を明示してわかりやすくすることを意識しましょう。

次回は where句の 複雑な文字列比較演算子の応用 & お題を

今回は where句で使える 演算子として

1. 基本の比較演算子

2. 複雑な文字列比較演算子の基本

3. 論理演算子

を紹介しました。

基本的な内容が中心だったんで、今回のnoteはレベルの高い人には物足りなかったかもしれません。

冒頭でも書きましたが、複雑な文字列比較演算子に関しては、今回は基本の部分だけですが、まだまだ likeやmatchesの応用、実践的な使い方が書けていません。

次回はwhere句の 複雑な文字列比較演算子の 応用編、お題チャレンジに入っていきたいと思います！