スプレッドシートのIF、SUMIF、COUNTIFで「含む(部分一致)」を条件判定する

スプレッドシートのIFやSUMIF、COUNTIFは通常、条件に完全一致するか条件判定を行います。

しかし、ifで部分一致(○○を含む)で判定したい場合もあります。

そこでスプレッドシートのif系の関数で部分一致の条件判定する方法を解説します。

スプレッドシートのifの文字列判定は完全一致

スプレッドシートでは、IF関数がよく用いられます。

IF関数の条件判定に応じて、表示する内容を切り替えることができます。

そのほかにも、指定範囲のセルで条件判定をみたしたセルを数えるCOUNTIFや、条件に一致するセルの値を合計するSUMIFなどがあります。

IF関数で数値を判定する場合には等しい以外に、「より大きい」、「より小さい」などのバリエーションがあります。

しかし、IF関数で文字列を判定する場合、基本は完全一致です。

判定条件に「完全に一致」するか「一致しないか」の2パターンになります。

SUMIFやCOUNTIFで「含む」を判定したいケースも

しかし、IF関数でも完全一致以外に、「含む」といった部分一致の判定を行いたいケースもあります。

例えば、住所などの判定の場合、都道府県ではなく区・市町村で判定したい場合は、部分一致が必要になってきます。

スプレッドシートで住所の都道府県や市町村のみで抽出したい場合に、「含む」の部分一致の条件判定が必要

上記のようなケースで住所が都道府県と区市町村の場合に、都道府県ごとの売上をSUMIFで出したい場合に、そのまま都道府県を指定しても完全一致ではないため、算出不可能です。

ただ、実はIF関数の条件の引数を工夫することで、部分一致での判定が可能になります。

if,sumif,countifで部分一致(含む)で判定する方法

そこで、スプレッドシートのIFやCOUNTIF、SUMIFで完全一致ではなく部分一致の条件で判定する方法を解説します。

IF系の関数で部分一致の判定を行うケースとして、以下の2種類の方法をそれぞれ紹介します。

  1. 文字列を含むかの条件判定
  2. セル参照による部分一致の条件判定

文字列を含むか、部分一致を判定する方法

IFやCOUNTIF、SUMIFで指定範囲で文字列を含んでいるか、部分一致の条件判定を行う方法は、「”*(判定文字列)*”」を条件の引数に指定します。

例えば、先程紹介したスプレッドシートでCOUNTIF関数を使い、「港区」を含んでいる行をカウントしてみます。

スプレッドシートのCOUNTIFで港区を含むか部分一致を満たした行数をカウント

=COUNTIF(F2:F16,”*港区*”)

結果、COUNTIF関数が港区が含まれている2つのセルを検出し、2と出力してくれます。

続いて、SUMIF関数でも同様にF列の住所が「大阪」である注文金額を合計してみます。

スプレッドシートのSUMIF関数で部分一致を満たしたセルの合計値を算出

=SUMIF(F2:F16, “*大阪*”, E2:E16)

SUMIF関数の2番目の引数、条件を*(アスタリスク)で挟むことで大阪が含まれている3つのセルを検出し、E列の注文金額を合計し、3500を出力してくれます。

※この部分一致の判定方法はIF関数でも適用可能です。

セル参照による部分一致の条件判定

次はセル参照を用いた部分一致の条件判定をIFやSUMIF、COUNTIFで表現する方法を解説します。

注文の住所でG列に設定された東京と大阪を含んでいる注文の金額をSUMIFで条件判定し、合計します。

スプレッドシートのIFやSUMIF、COUNTIFでセル参照で部分一致を条件判定する

=SUMIF(F2:F16, “*”&G4&”*”, E2:E16)

*(アスタリスク)とセル参照を結合することで、セル参照した情報を含んでいる住所を検出し、金額をSUMIFで合計します。

このように文字列の場合とセル参照でもIFやSUMIF、COUNTIFで部分一致の条件判定が可能です。

応用で前方一致や後方一致のif判定も

IFやSUMIF、COUNTIF関数で部分一致の判定を行う場合には、”*(判定文字列)*”という記述を行うことで、判定文字列を含むかの条件分岐が可能です。

さらに、この部分一致を応用することで、前方一致や後方一致もIFで表現することができます。

前にある1つ目の*(アスタリスク)を取り除くことで前方一致になります。

IF関数で”(判定文字列)*”を設定することで「判定文字列から始まる」という判定が可能になります。

また、後ろにある2つ目の*を取り除くと、次は後方一致になります。

“*(判定文字列)”で「判定文字列で終わる」という後方一致の判定ができます

スプレッドシートのIF関数周りでは「*」を使いこなすことで、部分一致に加え、前方一致や後方一致の判定が可能です。

まとめ・終わりに

今回、スプレッドシートのIFやSUMIF、COUNTIFで部分一致で文字列を条件判定する方法を紹介しました。

通常のIF関数では、文字列の判定は完全一致になります。

しかし、SUMIFやCOUNTIFなどで文字列が含まれるという部分一致の判定を行いたいケースは多いです。

その場合はIF関数の論理式に「*(条件の文字列)*」と記述することで部分一致が可能です。

さらに、前の*(アスタリスク)を取り除けば前方一致、後ろの*を取り除けば後方一致の判定ができます。

SUMIFやCOUNTIFで一部の文字列を含んでいるかという条件判定をしたい場合は、今回の方法を活用しましょう。