（Gスプレッドシート）QUERY関数でのIN及びNOT INの代替手段

　GoogleスプレッドシートのQUERY関数のオプションにはSQLライクなWHERE句があるものの、それに続くIN句（演算子）及びNOT IN句（演算子）がありません。
　そこで代わりになる方法の一例を示します。

• MATCHES句で比較対象を列挙できるのでIN句の代わりにすることができます。

手順

IN句の代替例

　グループ1、グループ2としてそれぞれ複数の都市が掲げられています。
　グループ1のうち、グループ2にも存在しているものはどれとどれでしょうか。

　B13セルに次のように記して答えを求めます。

=QUERY(B3:B9,"WHERE B MATCHES '^("& JOIN("|",D2:D11)&")$'")

　MATCHESは正規表現によるマッチングを行うための句ですが、マッチング対象として「'(宇都宮市|広島市|川崎市|……)'」という形式の文字列を指定することでOR検索を行うことができます。
　そこで、JOIN関数を用いてこの形式の文字列を生成しています。

　なお、比較方法は完全一致としていますが、実はMATCHESによるマッチングは（REGEXMATCH関数などと異なり）デフォルトで完全一致検索となるので「^」と「$」がなくても完全一致検索になります。
　前方一致（~から始まる）にする場合は「$」を「.*」に代え、後方一致（~で終わる）にする場合は「^」を「.*」に代えます。両方代えると部分一致（~を含む）になります。

　結果はこうなります。

NOT IN句の代替例

　グループ1の都市のうち、グループ2に存在しないものはどれとどれでしょうか。

　B13セルに次のように記して答えを求めます。

=QUERY(B3:B9,"WHERE NOT B MATCHES '^("& JOIN("|",D2:D11)&")$'")

　上記の例との違いはNOTが加わったことだけです。「B NOT MATCHES~」などとするとエラーになりますので句の順番に注意してください。

　結果はこうなります。