いきなり答える備忘録

Google Workspace(旧G Suite)・Microsoft 365・LibreOfficeなどに関するメモ

(Gスプレッドシート)文字列がリスト内の単語を含むか判定する

 Googleスプレッドシートで、ある文字列が、リストで定義された複数の単語(言葉)のいずれか(またはすべて)を含んでいるかどうかを判定する方法についてです。
 利用例としては、いわゆるNGワードを含むかどうかの判定が挙げられます。

  • REGEXMATCH関数等を使って、ある文字列がリスト中の言葉を含むかどうか判定できます。

手順

リストのいずれかの言葉を含むか判定する

REGEXMATCH関数とJOIN関数を併用する方法

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

 E3:E6には単語のリストが入力されています。
 また、B3:B10には判定対象となる文字列が入力されています。これらの文字列がリストの単語のいずれかを含むかどうかをTRUE(含む)かFALSE(含まない)で判定するものとします。

 画像は、まずC3セルに次のように入力し、B3セルの文字列に対して判定したところです。

 C3セル

=REGEXMATCH(B3,JOIN("|",E3:E6))

 JOIN関数を使って、リストの単語を「|」を挿みつつ連結しています。つまり「りんご|ぶどう|なし|いちご」という文字列が生成されます。
 これをREGEXMATCH関数の第2引数とすることでOR判定(いずれかの単語を含むかどうかの判定)を行っています。
 

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

 この方法のメリットは、ARRAYFORMULA関数を使って判定対象を容易に拡張できるところです。
 画像では、C3セルに次のように入力し、B3:B10セルの文字列すべて対して判定を行っています。

 C3セル

=ARRAYFORMULA(REGEXMATCH(B3:B10,JOIN("|",E3:E6)))

 ただしデメリットとして、ANDの判定(すべての単語を含むかどうかの判定)に応用できない点と、リストが膨大になったときJOIN関数で生成される文字列が長くなりすぎるとエラーになる点が挙げられます。


REGEXMATCH関数とOR関数を併用する方法

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

 画像ではC3セルに次のように入力し、B3セルの文字列に対して判定しています。

 C3セル

=OR(ARRAYFORMULA(REGEXMATCH(B3,E3:E6)))

 REGEXMATCH関数とARRAYFORMULA関数を用いて、B3セルの文字列がE3:E6の単語を含んでいるかそれぞれ(4回)判定しています。
 さらにOR関数を使い、結果のいずれかがTRUE(含む)であればTRUEを返すようにしています。
 

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

 この方法の場合、ARRAYFORMULA関数を併用しても判定対象となる文字列を拡張できないため、各セルに式を入力して個別に判定する必要があります。
 画像ではC3セルに次のように入力してからC10までコピーし、B3:B10セルの文字列すべて対して判定を行っています。
 絶対参照($)を使っている点に注意してください。

 C3セル

=OR(ARRAYFORMULA(REGEXMATCH(B3,E$3:E$6)))

リストのすべての言葉を含むか判定する

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

 画像ではC3セルに次のように入力し、B3セルの文字列に対して判定しています。

 C3セル

=AND(ARRAYFORMULA(REGEXMATCH(B3,E3:E6)))

 REGEXMATCH関数とARRAYFORMULA関数を用いて、B3セルの文字列がE3:E6の単語を含んでいるかそれぞれ(4回)判定しています。
 さらにAND関数を使い、結果のすべてがTRUE(含む)であればTRUEを返すようにしています。
 

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

 ただしこの場合も複数の文字列を判定する場合は、各セルに式を入力して個別に判定する必要があります。
 画像ではC3セルに次のように入力してからC10までコピーし、B3:B10セルの文字列すべて対して判定を行っています。
 絶対参照($)を使っている点に注意してください。

 C3セル

=AND(ARRAYFORMULA(REGEXMATCH(B3,E$3:E$6)))