Googleスプレッドシートで部分一致するセルを抽出・判定する方法

スプレッドシートのVLOOKUP関数、QUERY関数、COUNTIF関数などには、部分一致を判定するワイルドカードが使用できます。IF関数で部分一致を判定する方法や、条件付き書式で色づけする方法についても解説しているので、ぜひ参考にしてみてください。

部分一致するセルを抽出する方法

QUERY関数 はデータ範囲に対し、指定したクエリ(操作)を実行します。

クエリは10種類あり、WHERE句 は抽出の条件を指定するためのクエリです。WHERE句に指定できる演算子のうち、LIKEはワイルドカードを使用して部分一致を判定できます。

例えば “WHERE A like ‘%★%'” なら、A列を対象に★を含む任意の文字列かを判定します。

ワイルドカードの [%] は任意の 0 個以上の文字を表しています。つまり ‘★%’ なら★で始まる任意の文字列、 ‘%★’ なら★で終わる任意の文字列です。

任意の 1 文字を表すワールドカード [_] を使用すると、文字数を指定できます。

例えば ‘__★’ なら、★で終わる合計3文字の文字列です。

COUNTIF関数で部分一致をカウントする方法

COUNTIF関数は、範囲内で条件に一致する要素の個数を返します。

条件には * や ? などのワイルドカードを使用可能です。* は任意の 0 個以上の文字を表しているので、条件が “*★*” なら範囲から★を含む文字列を検索します。

例えば COUNTIF(A2:A6,”*★*”) なら、算出されるのは条件と一致する要素の個数 3 です。

ワイルドカードの [?] は任意 1 文字を表しています。つまり “★??” なら★で始まる 3 文字の文字列、 “??★” なら★で終わる 3 文字の文字列です。

ワイルドカードの ＊ や ? を検索値として利用したい場合は、チルダ[~] 使用してください。

チルダ[~]は * や ? の前に置くことで、ワイルドカードの効果を打ち消します。

IF関数で部分一致を判定する方法

前提として、IF関数にワイルドカードは使用できません。

そこで論理式の TRUE=1、FALSE=0 であることを利用して、真偽判定をします。

まずCOUNTIF関数で、ひとつのセル範囲に対して条件を指定します。

例えば COUNTIF(A2,”*★*”) は文字列が★を含むなら 1 を、含まないなら 0 を返します。

次にIF関数の論理式に、COUNTIF関数の返り値を指定してください。

論理式の 1 はTRUE、0 はFALSEと認識されるので、★を含むなら○を返します。これをひとつの数式にすると、IF(COUNTIF(A2,”*★*”),”○”,”×”) になります。

条件付き書式で部分一致を色づけする方法

まず色づけするセル範囲を選択し、表示形式タブから 条件付き書式 を開きます。

次に、書式設定の条件から 次を含むテキスト を選択してください。値または数式 の入力欄に ★ を指定すると、★ を含むすべてのセルに色がつきます。

条件付き書式でも、ワイルドカードの使用が可能です。

例えば [?] は任意 1 文字なので、?★? なら★の前後両方に文字を含むセルに色がつきます。

★? の場合は ★BCDE、BC★ABのように、★の後ろに文字を含むテキストが対象です。一方 ★???? の場合は ★の後ろに4文字を含むテキストなので、★BCDE のみ対象になります。