Googleスプレッドシートで排他抽出

先日、社内で某営業さんから、

イベントの登録者一覧で、EメールがリストされているB列で任意ドメイン（gmail, yahoo, など)を除外してリストアップしたい。でも、フィルターで「次を含まないテキスト」との条件を設定しても、期待通りの一覧が作成できない（除外しきれない）。どうしたらいいですか？

との質問・支援を求められました。まずは該当のスプレッドシートを共有してもらい、フィルターの設定と、抽出されたデータを確認したら、確かに除外しきれてない😖

自分の経験上、Googleスプレッドシートでは、条件が複雑な場合（&大量データ）、ごく稀に期待通りの動作がしないこともあります。しばし考え、フィルター制御では埒があかないので別の方法で回避。

別のシートを作り、A1セルにQuery関数で排他条件書いてリストアップ

=QUERY(参照元のシート名!A1:F10000,"WHERE not B contains 'gmail' and not B contains 'yahoo'")

これはあっさり動作しました（QUERY関数を使う時点で、一般的にあっさりとは言えませんが）。ちょっとだけ解説すると、

• not B contains ‘gmail’ はB列にgmailが含まれない

との条件を2つ作成し（コピペ&編集）、WHERE 〜〜 and 〜〜 で結合。
or を活用して、

=QUERY(参照元のシート名!A1:F10000,"WHERE NOT (B contains 'gmail' or B contains 'yahoo')")

と書くのもありです。また、

contains 'gmail'　を　like '%gmail%'

でも同じで、この辺は好みが分かれます。

さいごに

QUERY関数が使えるとGoogleスプレッドシートの便利度はかなり上がるけど、認知度低いです。確かに取っ掛かりの難しい関数なので、じっくり時間を設けて、勉強しすることをお勧めします

QUERY - Google ドキュメント エディタ ヘルプ