いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

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

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

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

手順

IN句の代替例

f:id:accs2014:20191208012614p:plain:right:w600

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

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

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

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

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

f:id:accs2014:20191208012617p:plain:right:w550

 結果はこうなります。

NOT IN句の代替例

f:id:accs2014:20191208012621p:plain:right:w600

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

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

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

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

f:id:accs2014:20191208012625p:plain:right:w550

 結果はこうなります。