部署やステータスなど、複数の条件に合うデータを数えるときに便利なExcelのCOUNTIFS関数ですが、OR条件を追加するたびに数式を「＋」でつなぐのは手間がかかります。
Microsoft 365で利用できるREGEXTEST関数を使えば、複数の候補を正規表現の「|」でまとめられます。
この記事では、営業部の「未対応」「保留」「調査中」を数える例を使い、基本の数式から注意点、応用方法まで解説します。
COUNTIFSでOR条件を数えると数式が長くなる
COUNTIFS関数は、複数の条件をすべて満たすデータを数える関数です。
たとえば、B列が「営業部」で、E列が「未対応」の行を数える場合は、次のように記述します。
=COUNTIFS(B2:B39,"営業部",E2:E39,"未対応")
このように、部署が「営業部」かつステータスが「未対応」というAND条件は、ひとつの数式で指定できます。
一方、ステータスが「未対応または保留」のようなOR条件を指定する場合は、それぞれの件数を足し合わせる方法が一般的です。
=COUNTIFS(B2:B39,"営業部",E2:E39,"未対応")+COUNTIFS(B2:B39,"営業部",E2:E39,"保留")
候補が増えるほど同じようなCOUNTIFSを追加する必要があり、条件の変更や追加が面倒になります。
REGEXTESTで複数の候補をまとめる
REGEXTESTは、指定した文字列が正規表現のパターンに一致するかどうかを判定する関数です。
基本の構文は次のとおりです。
=REGEXTEST(検索対象, 正規表現パターン, [大文字と小文字の区別])
正規表現では、縦棒の|をOR条件として使えます。
たとえば、セルの内容が「未対応」または「保留」なら一致させるパターンは、未対応|保留です。
ただし、このままでは候補の一部を含む文字列にも一致する可能性があります。
セル全体がいずれかの候補と一致するように、先頭と末尾を表す^と$を加え、^(未対応|保留)$と指定します。
営業部の対象データを数える数式
部署がB列、ステータスがE列に入力されている表で、「営業部」かつ「未対応」「保留」「調査中」のいずれかを数える数式は次のとおりです。
=SUMPRODUCT((B2:B39="営業部")*--REGEXTEST(E2:E39,"^(未対応|保留|調査中)$"))
この数式では、REGEXTESTがE列の各セルを調べ、指定したステータスに一致すればTRUE、一致しなければFALSEを返します。
条件に合う候補を追加するときは、正規表現のかっこ内に|で区切って書き足します。
--はTRUEとFALSEをそれぞれ1と0に変換し、SUMPRODUCTは部署の条件とステータスの条件が両方成立した行を合計します。
| 数式の部分 | 役割 |
|---|---|
B2:B39="営業部" | 部署が営業部か判定します。 |
^(未対応|保留|調査中)$ | ステータスが候補のいずれかと完全一致するか判定します。 |
-- | 判定結果を計算に使える1または0に変換します。 |
SUMPRODUCT | 両方の条件を満たす行を合計します。 |
正規表現の記号と条件追加のポイント
「|」でOR、「*」でANDを表す
正規表現の|は「または」を表します。
^(未対応|保留|調査中)$なら、3つのステータスのどれかとセル全体が一致したときにTRUEになります。
一方、部署条件とステータス条件のように、複数の判定結果を同時に満たすAND条件は、数式内で*を使って組み合わせています。
たとえば、「営業部」と「未対応または保留」を数えるなら、パターンを^(未対応|保留)$に変更します。
部分一致にしたい場合はアンカーを調整する
^は文字列の先頭、$は文字列の末尾を示します。
そのため、両方を付けるとセル全体との一致になり、誤って「未対応確認中」のような文字列を数えるのを防げます。
「未対応」を含む文字列を対象にしたい場合は、たとえば未対応のようにアンカーを付けないパターンを使います。
ただし、部分一致では意図しない文字列まで対象になる可能性があるため、集計の目的に合わせて指定してください。
候補に正規表現の記号が含まれる場合
候補の文字列に.や*、?、(などの正規表現で特別な意味を持つ記号が含まれる場合は注意が必要です。
これらを文字として扱うには、通常は直前にバックスラッシュを付けてエスケープします。
また、候補を直接数式に書く方式では、候補の追加や削除のたびにパターンを編集する必要があります。
候補が頻繁に変わる場合は、候補を別のセル範囲に一覧化する方法や、データの入力規則で表記を統一する方法も検討しましょう。
利用時に確認したい注意点
REGEXTESTは、利用できるExcelのバージョンや更新状況が環境によって異なる場合があります。
数式を入力して関数名が認識されない場合は、Microsoft 365の更新状況や利用環境を確認してください。
また、数式の範囲は部署列とステータス列で同じ行数にそろえます。
たとえば、部署の範囲をB2:B39にした場合は、ステータスの範囲もE2:E39にします。
データが増えるたびに範囲を直すのが大変なら、表をExcelのテーブルに変換し、構造化参照で管理する方法も有効です。
なお、従来のExcelとの互換性を重視するブックでは、COUNTIFSを足し合わせる方法のほうが共有先で動作しやすい場合があります。
まとめ
COUNTIFSはAND条件の集計に便利ですが、OR条件の候補が増えると数式が長くなりがちです。
Microsoft 365でREGEXTESTが使える場合は、正規表現の|で複数候補をまとめ、SUMPRODUCTと組み合わせて集計できます。
セル全体を判定するなら^と$を付け、部署など別の条件は数式内で掛け合わせるのがポイントです。
対象範囲や候補の表記を整えたうえで、日々の集計や条件変更の手間を減らしましょう。