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

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

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

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

前回はQUERY関数の where句 徹底解析2 で、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}$$

サンプルデータは前回と同じ 👇 こちらを使うものとします。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

Q1. where句の文字列比較演算子で「含む」のキーワードを セル参照にしたい

QUERY関数で「含む」条件で抽出する際、キーワードをセル参照に出来たら便利ですよね？

まずはこれをお題にしてみましょう。

テーブル sample に対して 住所（Col6）が I1セルのキーワードを 含む データを抽出する QUERY式を作成したい！というお題です。

ただし I1が空の時は 全てのデータを出力するものとします。

これは簡単ですかね？（1つ出来た人は 他の文字列演算子を使った回答にチャレンジしてみてください）

考えてみましょう！

↓↓
回答はここから。
↓↓

A1. where句の文字列比較演算子で「含む」のキーワードを セル参照にする

回答です。

文字列比較演算子で含むは contains、like、matches  どれを使っても書けるので、3パターン提示します。

いずれのケースも 前後のシングルクォートや メタ文字部分は ダブルクォートで括った範囲に含め固定の文字列として、セル参照の I1 を &で連結します。

追加条件の「I1が空の時は 全データを返す」に関しては、上の式はいずれも I1セルが空白の場合は

Col6 contains ’’
Col6 like '%%'
Col6 matches '.*.*'

となり、これらは 空白データ含め 全てのデータに一致すると判定されるので、

特に考慮する必要なく、I1セルが空白時に全データを出力はクリアできます。

QUERY関数のキーワードを セル参照した場合の 含む条件での抽出、これはよく使うので、さくっと書けるようにしておきましょう。

Q2. where句の文字列比較演算子で「含む」と「一致」を簡単に切り替えできるようにしたい

「含む」条件を最も簡単に使えるのは contains なんですが、contains では「一致」を条件とすることが出来ません。

contains は 含む 専用 の文字列演算子ってことです。

たとえば 👆画像のような 血液型（Col5）を対象に I1の血液型で参照しようとした場合、I1セルを A とした時に contains は A型 だけでなく AB型 も抽出してしまいます。

一方、like や matches は記述の仕方で「一致」条件にすることが可能です。

それではお題です。

血液型（Col5）を対象列として、検索キーワードを入れるI1セルの右 J1セルに 「一致」と「含む」の条件切り替えプルダウンを設置し、

「一致」または 未選択（空白）の場合　▶　一致条件
「含む」を選択した場合　▶　含む条件

このように簡単に一致モード・検索モードを切り替えできるQUERY関数を作りたい！とした場合、どんな式を組めばよいでしょうか？

contains と = イコール で切り替えるという方法もあるんですが、今回は like または matches を使うものとします。

考えてみましょう！

↓↓
回答はここから。
↓↓

A2. where句の文字列比較演算子で「含む」と「一致」を簡単に切り替えできる式をつくる

回答です。

ポイントはIF関数の分岐ですね。

前述した通り、このお題は IF でJ1の値によって contains と = を分岐させて、」「含む」と「一致」を切り替える式を作ることも出来るんですが、like や matches で「一致」条件を記述すると = より便利な点があります。

次の章でこのメリットを解説していきましょう。

文字列比較演算子を 文字列以外で活用する

公式の クエリ言語リファレンスには

と記述があります。

これは、

contains や like matches は、文字列型の列だけでなく、数値や日付など 文字列以外の列を対象として利用できる

ということを意味しています。

ざっくり言うと TO_TEXT関数で数値や日付、ブール値を文字列化してから 一致や含むといった比較を行うイメージです。（厳密には少し違いますが）

文字列比較演算子で一致判定するメリット

前述した通り、likeやmatchesは一致判定にも活用できるんで、数値型の年齢列（Col4）に対して イコールを使わず

こんな式で、年齢が30に一致するデータを抽出できます。

likeや matches といった文字列比較演算子の後ろにくる キーワードは、数値やブール値の列を対象とした場合でも、必ず ' シングルクォートで括る 必要があります。

イコールや 不等号などの基本の比較演算子と勝手が違うので戸惑うかもしれませんが、逆に シンプルな比較演算子（= や <、>など）は、

このように比較対象の列の型を意識する必要がありますが、文字列比較演算子であれば

型を意識せず一致や含む判定できる ってことです。

これが結構便利だったりします。（後ほどお題で触れます）

文字列比較演算子を文字列型以外の列で使う際の注意点

文字列比較演算子は ブール値や日付型などの列に使う際は、注意すべき点があります。

👆上の例を見てください。

チェックボックスの 既婚列（Col7）を対象として チェックが入っている（TUREである）データを 文字列演算子を使って判定し抽出したいのですが

=QUERY(sample[#ALL],"where Col7 like 'TRUE'")

では 1件も抽出されません。

この理由は ブール値 を文字列として扱う場合は

と、ブール値の中身が実は 小文字になっている為です。

文字列比較演算子も 大文字と小文字を区別する 厳密な判定となっているため、’TRUE' を条件としても  true は不一致と見なされてしまいます。

だから式を

=QUERY(sample[#ALL],"where Col7 like 'true'")

とすれば

正しく機能し、Col7がTRUEのデータだけを抽出できます。

シート上では TRUEとなっているのに、クエリ文で文字列として扱う場合は、見えているそのままではないケースがあるってことです。

ぶっちゃけブール値で「含む」判定をする機会はほぼ無いんで、この影響を大きく受けるのは 日付型を クエリ文で文字列として扱う時なんですが、これは日付型を解説する回で触れたいと思います。

Q3. 列と条件の両方をプルダウンで可変にして QUERY関数で抽出したい

文字列比較演算子が文字列以外でも使える特性を活かした お題にチャレンジしてみましょう。

sampleテーブルを対象に I1で 対象とする列を、J1で一致判定するキーワードを それぞれプルダウンで選択した時に、その条件に合致するデータを出力する QUERY関数の式を作るにはどうすればよいでしょうか？

👆こんなイメージです。

文字列型の性別、数値型の年齢、ブーリアン型の既婚、いずれの列を選択してもJ1のキーワードで一致判定できてますね。

ただし、対象の列が未選択（I1セルが空白）の場合は

全データを返すものとします。

ちなみにこのI1、J1の連動プルダウンは

I1の方のプルダウンを　=sample[#HEADERS]

このI1の値を使って J1用のプルダウンリストを生成する領域を適当なセル（今回の場合は R3）に

という式を入れて生成し、その範囲 R3:Rを J1のプルダウンに使用しています。

このような連動プルダウン作成方法は マガジンにまとめています。

ちょっとハードル上がりましたね。考えてみましょう！

↓↓
回答はここから。
↓↓

A3. 列と条件の両方をプルダウンで可変にして QUERY関数で抽出する

回答です。（他の書き方もあります。一例です）

likeを使った回答にしてみました。

まずI1で選択した見出しの列の番号を取得する必要があるので

XMATCH(I1,sample_7[#HEADERS])

を用意して、その前に 文字列で "where Col" を&で連結。

この列が J1の条件に一致するとしたいので、型を気にせず使える like と組み合わせ

"like '" & J1 & "'"

このように書きます。

"where Col"& XMATCH(I1,sample_7[#HEADERS])&" like '"&J1&"'"

ただ、今回はブーリアン値の「既婚」の列を対象とした時に

J1のプルダウンが シート上で見える TRUE / FALSE の大文字となってしまい正しく抽出されないケースを考慮する必要があります。

ここは IFで分岐させてI1で既婚を選択した時は LOWER関数を使うようにしましょう。※血液型の列が 大文字アルファベットを使っているので、常にJ1をLOWERとは出来ない為

イレギュラーなケースへの対応もできました。

さらに I1が空白時に全データを出力させる必要がります。

ここもIFで分岐させてもいいんですが、I1が空白の場合は XMATCHが#N/Aを返す（#N/Aエラーと文字列を連結しても#N/Aエラー）ので、IFNA関数で

#N/Aの時は where Col1 = Col1 となるように します。

Col1 = Col1 で全データを返す仕様は前回学びましたね。

基本関数の積み上げですね。理解できたでしょうか？

Q4. 年齢が 20～29 歳のデータだけを簡単に抽出したい

文字列比較演算子が文字列以外にも使える特性が真価を発揮するのは、数値や日付・時刻・日時 を対象に「含む」や「前方一致」を条件とする時です。

今回は 日付系は扱わないので、次は この特性を 数値に使った 簡単なお題に取り組んでみましょう。

QUERY関数 where句の 文字列比較演算子を利用して、数値型の列 年齢（Col4）が 20歳～29歳 のデータだけを抽出したい！ というお題です。

データには実際含まれてませんが、2歳の人がいる可能性も考慮してください。

考えてみましょう！

↓↓
回答はここから。
↓↓

A4. 年齢が 20～29 歳のデータだけを簡単に抽出する

回答です。

likeを使う場合

matchesを使う場合

わかりますか？

正攻法で数値型のまま処理をしようとすると

このように以上 と 以下 の条件をそれぞれ記述して and で連結する必要がありますが、文字列比較演算子を使うことで

20～29 は、2から始まる文字列

とみることが出来ます。（QUERY関数で扱っているので 2a などの文字列が混在することはない）

ただし、starts with '2'　だと、2歳や 210歳（実際はいませんが）もヒットしてしまうので、ワイルドカードの任意の1文字 _（アンダースコア）を使って

like '2_'

もしくは 正規表現の 任意の数字1文字を表す \d を使って

mathces '2\d'

とすることで条件記述1つだけで 20～29 の間を出力出来ます。

Q5. 年齢が 21～30歳のデータだけを簡単に抽出したい

では、少しアレンジして 抽出したい年齢が 20～29ではなく、21～30 を対象とする場合は、文字列演算子を使ってどのような式を組めばよいでしょうか？

21～30なんで、20歳は対象外となり、30歳は抽出対象となります。

ピンときた方には簡単ですね。考えてみましょう！

↓↓
回答はここから。
↓↓

A5. 年齢が 21～30 歳のデータだけを簡単に抽出する

回答です。

QUERY関数シリーズの第7回で触れましたが、where句は 列に算術演算子を組み合わせて利用することが出来ます。

だから、年齢の列Col4を　 Col4 -1　とすることで、年齢が 20の人は19、21の人は20…そして30の人は29 となります。

こうなれば、先ほどの like '2_' や matches '2\d' がそのまま使えますね。

ベースの知識（武器）を増やした上で、お題に対して どう組み合わせて解決できるか？をイメージすることが大事です。

where句 matchesで 基本の正規表現を活用する

続いて matches で基本の正規表現を活用する例を学んでいきましょう。

なお以降のお題は、正規表現を知らないと解けません。

正規表現はわかりません！って人はお題チャレンジはすっ飛ばして、回答で使えそうな箇所だけ覚えちゃいましょう。（ブックマークしとくでもOK）

Q6. 年齢が 20～24歳（20代前半）のデータだけを簡単に抽出したい

Q4の 20～29、Q5の 21～30 は likeでも処理できましたが、今回のお題 年齢が 20～24 のデータ抽出は、正規表現を使った方が良いケースです。

正規表現を知っている人には簡単すぎるかもしれませんが、年齢（Col4）が20～24の データだけを抽出するQUERY関数の数式を作るお題にチャレンジしてみましょう！

↓↓
回答はここから。
↓↓

A6. 年齢が 20～24歳（20代前半）のデータだけを簡単に抽出する

回答です。

正規表現で 2[0-4] とします。

20～24は 1番目に2、2番目に 0～4のいずれかの数字の組み合わせの2文字の文字列と言えます。

[0-4] は0,1,2,3,4 のいずれかの1文字を意味する正規表現なので、2と[0-4]を組み合わせることで20～24のパターンに一致するという条件となっています。

[　]（文字クラス）　と　-　を組み合わせて範囲指定する方法は、非常に便利な正規表現の一つです。（例： [A-Z]　A～Zまでの大文字アルファベットのいずれか1文字）

ちなみにREGEXMATCH関数などのREGEX系の関数は、第1引数のテキストに数値を指定するとエラーになりますが、

QUERY関数の matches の正規表現は 対象が数値でも文字列化して処理する

のが便利ですね。

Q7. 氏名に全角カタカナが含まれているデータだけを簡単に抽出したい

正規表現の範囲指定を活用するお題をもう一ついってみましょう。

実は sampleデータの中には 氏名にカタカナが含まれる人が3名います。このデータをQUERY関数で抽出する方法を考えてみましょう！

↓↓
回答はここから。
↓↓

A7. 氏名に全角カタカナが含まれているデータだけを簡単に抽出する

回答です。

なんとなく　[ア-ン] かなと考えるかもしれませんが、実はカタカナの伸ばし棒だけは連続した範囲に無いため、これだけ付け足して [ア-ンー] とする必要があります。

正規表現は AIに聞いちゃえばOK

こんなの覚えられないよーと思うかもしれませんが、イチイチ覚える必要はありません。

昔だったらググってましたが、今だったら正規表現はAIに聞いちゃいましょう！

ここで QUERY関数の matchesで使うことを明記して質問すれば

このように具体的な式の例まで返してくれます。

便利ですね。

だったあ、そもそも この noteで QUERY関数について学ばなくても、全部AIに聞いちゃえばいいんじゃないの？と思うかもしれませんが、それを言っちゃおしまいですｗ

※mirのnoteはディープな無駄知識を楽しめる人向けです。学習コスパとかを気にする人には向いてません

Q8. 〇文字以上、〇文字以下に一致する データだけを抽出したい

シート関数だったら「文字数」という条件が出てきたら LEN関数の出番ですが、QUERY関数のクエリ文ではLEN関数は使えません。

その代わりに matchesで使える正規表現では、{n,m} で n文字以上、m文字以下という指定が可能です。

たとえば sampleテーブルの1列目の氏名が5文字以上（姓と名の間には半角スペースがあるので 合わせて6文字以上）の人だけを抽出したい場合は

となります。

逆に 氏名が3文字以下（スペースを入れて4文字以下）の人だけを抽出したい場合は

このようになります。

Googleスプレッドシートの正規表現は REGEX系関数でもQUERY関数でも m文字以下の記述は {,m} はNGで、{0,m} と必ず 0を入れる必要があります。

また、4文字以下とした場合は 0文字である空白セルにもマッチします。

では、これを踏まえてお題いってみましょう。

このように1列目の氏名の名（First Name)部分が4文字以上の人だけをQUERY関数で抽出するには、どのように記述すればよいでしょうか？

氏名の姓と名の間には半角スペースが必ず入っているというルールがあります。

簡単ですね。さくっと解いちゃいましょう！

↓↓
回答はここから。
↓↓

A8. 〇文字以上、〇文字以下に一致する データだけを抽出する

回答です。

'.+ .{4,}'　👈　.+ と.{4,} の間には半角スペースが入っています。これが 姓と名を区切る半角スペースです。

つまり半角スペースの前後を

.+　・・・　名前の姓の部分
.{4,}　・・・　名前の名の部分（4文字以上という条件）

とすることで、名前部分が4文字以上の人だけを抽出できるというわけです。

ちなみにもう少し丁寧に書くと

'^.+? .{4,}$'

こうなるんですが、QUERY関数のmatchesの正規表現は完全一致条件なので、文頭を表す ^ や 文末を表す $ を わざわざ記述する必要はありません。

Q8. 複数選択プルダウンで 選択した年齢と一致する データだけを抽出したい

最後に matchesの正規表現で これだけは覚えて欲しい！というテクニックを紹介して、今回は終わりとしましょう。

その記述方法が 👇 コレです！

I1セル（見やすいように横にセル結合）を年齢を選択肢とする複数選択プルダウンとした時、選択した年齢のいずれかと一致するデータだけを抽出するQUERY関数を作りたい。どのような式にすればよいでしょうか？

要は上の例であれば 年齢の列（Col4）が、

24 または 26 または 31 または 34 のいずれかに一致する

という条件を可変で記述したいってことです。

一つ一つ = で一致する条件を書いて OR でつなげて・・・と考えちゃうとドツボにはまっていきます。

どうでしょうか？ここまでの流れでヒントは出ています。考えてみましょう！

↓↓
回答はここから。
↓↓

A8. 複数選択プルダウンで 選択した年齢と一致する データだけを抽出する

回答です。

正規表現の |（パイプ）区切りを使った 「または」記述を 利用します。

正規表現における または、いずれか（OR）の記述は

複数文字（2文字以上）の場合は |（パイプ）で区切って記述します。

複数選択プルダウンの I1セルは、選択された値は , (カンマ+半角スペース）で区切られているので、 ", "（カンマ半角スペース）を "|"（パイプ）に SUBSTITUTE関数で置換することで、正規表現で使えるパイプ区切りの文字列を生成します。

SUBSTITUTE(I1,", ","|")

あとはこれを

　　　　👇
mathes ' ココ '

に入れれば良いので、　

"where Col4 matches '"&SUBSTITUTE(I1,", ","|")&"'"

とすればOKですね。

もし 未選択（空白）の時、全セル出力としたい場合は IFで分岐させて、I1セルが空白の時は .* を返す式としましょう。

複数選択プルダウンの値を「いずれかに一致する」ではなく、「いずれかを含む」としたい場合は、

住所の列（Col6）を対象に I2でプルダウンで選択した都道府県を抽出する例だと

前後に .* を付けて 👆このようになります。

where句の matchesで 複数の値のいずれかに「一致する」記述と、いずれかを「含む」記述

QUERY関数の where句 matches で使える 正規表現で コレだけは覚えておきたい、複数条件の OR連結

「いずれかに一致する」、「いずれかを含む」

の記述方法の まとめです。

これを使えば、通常はFILTER関数を使う重複チェックもQUERY関数で対応することが出来ます。

たとえば、A:B列から A列の値がD列の値と一致するデータを抽出したい場合、

通常は FILTER関数にCOUNTIFを組み合わせて

こんな式を書きますが、これをQUERY関数の matchesを使って

このように書くことが出来るってことですね！

QUERY関数の活用の幅がぐっと広がったんじゃないでしょうか。

まだ続く！ where句の ディープな活用例

実はまだ幾つか紹介しておきたい where句のディープな応用があるんですが、今回は既に1万3千文字を超えてしまったので続きは次回とします。

次回、今回かけなかった

✅メタ文字を「含む」を条件で利用したい場合の記述

✅セル内改行ありの列を対象とした場合の「含む」条件

✅REGEX系関数で使える 正規表現と違って QUERY関数の matchesの正規表現は「先読み」「後読み」が使える！？

✅複数列の一致や含むの長いクエリ文の記述を生成する方法

この辺りを書いて、ようやくQUERY関数の where句については終了となります。（まだ日付、日時、時刻 のケースが残ってますが。。）

QUERY関数 where句マスターへの果てしない旅はまだまだ続く！