スプレッドシートのIF、SUMIF、COUNTIFで「含む(部分一致)」を条件判定する
スプレッドシートのIFやSUMIF、COUNTIFは通常、条件に完全一致するか条件判定を行います。
しかし、ifで部分一致(○○を含む)で判定したい場合もあります。
そこでスプレッドシートのif系の関数で部分一致の条件判定する方法を解説します。
スプレッドシートのifの文字列判定は完全一致
スプレッドシートでは、IF関数がよく用いられます。
IF関数の条件判定に応じて、表示する内容を切り替えることができます。
そのほかにも、指定範囲のセルで条件判定をみたしたセルを数えるCOUNTIFや、条件に一致するセルの値を合計するSUMIFなどがあります。
IF関数で数値を判定する場合には等しい以外に、「より大きい」、「より小さい」などのバリエーションがあります。
しかし、IF関数で文字列を判定する場合、基本は完全一致です。
判定条件に「完全に一致」するか「一致しないか」の２パターンになります。
SUMIFやCOUNTIFで「含む」を判定したいケースも
しかし、IF関数でも完全一致以外に、「含む」といった部分一致の判定を行いたいケースもあります。
例えば、住所などの判定の場合、都道府県ではなく区・市町村で判定したい場合は、部分一致が必要になってきます。
上記のようなケースで住所が都道府県と区市町村の場合に、都道府県ごとの売上をSUMIFで出したい場合に、そのまま都道府県を指定しても完全一致ではないため、算出不可能です。
ただ、実はIF関数の条件の引数を工夫することで、部分一致での判定が可能になります。
if,sumif,countifで部分一致(含む)で判定する方法
そこで、スプレッドシートのIFやCOUNTIF、SUMIFで完全一致ではなく部分一致の条件で判定する方法を解説します。
IF系の関数で部分一致の判定を行うケースとして、以下の2種類の方法をそれぞれ紹介します。
- 文字列を含むかの条件判定
- セル参照による部分一致の条件判定
文字列を含むか、部分一致を判定する方法
IFやCOUNTIF、SUMIFで指定範囲で文字列を含んでいるか、部分一致の条件判定を行う方法は、「"*(判定文字列)*"」を条件の引数に指定します。
例えば、先程紹介したスプレッドシートでCOUNTIF関数を使い、「港区」を含んでいる行をカウントしてみます。
=COUNTIF(F2:F16,"*港区*")
結果、COUNTIF関数が港区が含まれている2つのセルを検出し、2と出力してくれます。
続いて、SUMIF関数でも同様にF列の住所が「大阪」である注文金額を合計してみます。
=SUMIF(F2:F16, “*大阪*", E2:E16)
SUMIF関数の2番目の引数、条件を*(アスタリスク)で挟むことで大阪が含まれている3つのセルを検出し、E列の注文金額を合計し、3500を出力してくれます。
※この部分一致の判定方法はIF関数でも適用可能です。
セル参照による部分一致の条件判定
次はセル参照を用いた部分一致の条件判定をIFやSUMIF、COUNTIFで表現する方法を解説します。
注文の住所でG列に設定された東京と大阪を含んでいる注文の金額をSUMIFで条件判定し、合計します。
=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で一部の文字列を含んでいるかという条件判定をしたい場合は、今回の方法を活用しましょう。