COUNTIFで「含む/含まない」を簡単検索！前方一致・後方一致もできる

COUNTIF関数にワイルドカードを使う方法

COUNTIFは、条件に合うセルの数を数える関数です。ワイルドカードという特別な記号を使うと、完全一致だけでなく、文字を含むかどうか、始まりや終わりが同じかどうかも調べられます。ここでは、基本の型を順番に紹介します。ExcelとGoogleスプレッドシートのどちらでも使える考え方です。

「～を含む」をカウントする方法

「含む」は、文字列の前後にアスタリスク（*）をつけるのが基本です。アスタリスクは、0文字以上のどんな文字にも合う記号です。たとえば、品番に「AB」を含むセルを数えたいときは次のように書きます。

Excel例：=COUNTIF(A2:A100, “*AB*”)

スプレッドシート例：=COUNTIF(A2:A100, “*AB*”)

大文字小文字の違いは無視されます。日本語や記号を含む場合も同じ書き方で使えます。スペースが混ざるときは、余分な空白に注意します。

「～を含まない」をカウントする方法

「含まない」は、含む条件の反対を数えます。COUNTIFは1つの条件しか持てないため、全体の件数から「含む」を引くのが簡単です。

Excel例：=COUNTA(A2:A100) – COUNTIF(A2:A100, “*AB*”)

スプレッドシート例：=COUNTA(A2:A100) – COUNTIF(A2:A100, “*AB*”)

空白セルを除いて数えたい場合は、範囲に実データだけが入っていることを確認します。数値と文字が混在すると、COUNTAはどちらも数えます。

「～で始まる」をカウントする方法

「始まる」は、後ろにアスタリスクをつけます。これは前方一致とも呼びます。たとえば「AB」で始まる品番なら次のようにします。

Excel例：=COUNTIF(A2:A100, “AB*”)

スプレッドシート例：=COUNTIF(A2:A100, “AB*”)

ハイフンやスペースなどの記号が続く場合も同じです。先頭のゼロがあるコードは、文字列として保存されているかを確認すると安心です。

「～で終わる」をカウントする方法

「終わる」は、前にアスタリスクをつけます。これは後方一致とも呼びます。たとえば「-X」で終わる型番なら次のようにします。

Excel例：=COUNTIF(A2:A100, “*-X”)

スプレッドシート例：=COUNTIF(A2:A100, “*-X”)

末尾の全角記号や句読点も同様です。終わりの文字が見えにくいときは、セルの表示形式や余分な空白に気をつけます。

FAQ：ワイルドカード「*」「?」「~」の意味と注意点

Q：記号の意味は？

A：「*」は0文字以上に合い、「?」はちょうど1文字に合います。Excelでは「~」を前につけると、「*」「?」を文字として検索できます。スプレッドシートではバックスラッシュで同様の効果になります。

COUNTIF関数でワイルドカード条件をセル参照にする方法

毎回条件を打ち直すのは手間です。セルにキーワードを書き、関数の中でつなげると、入力を変えるだけで結果が更新されます。これは、&演算子で文字列とワイルドカードを連結する方法です。空白や全角混在、特殊記号があると一致しないことがあるので、確認の観点も合わせて紹介します。

基礎：&演算子で文字列とワイルドカードをつなぐ

例として、B1セルに探したい語「AB」が入っているとします。含む、始まる、終わるの3型は次のとおりです。

「?」を使う場合は、必要な文字数だけ「?」を並べます。たとえば、先頭2文字が何でもよく、そのあとに「-01」が続く場合は「??-01」とします。

応用：空白・全角混在・特殊記号を含むときの書き方

一致しない原因の多くは、目に見えない違いです。余分な空白、全角と半角の混在、改行コード、特殊記号の扱いなどが影響します。次の関数で、検索前に整えると安定します。

• 余分な空白を減らす：TRIM（先頭と末尾、連続空白を整理）
• 制御文字を除く：CLEAN（印字不可の文字を削除）
• 特定の文字を統一：SUBSTITUTE（全角スペースを半角に置換など）

ワイルドカード自体を文字として探したいときは、Excelでは「~*」「~?」のようにチルダを前につけます。スプレッドシートでは「\*」「\?」のようにバックスラッシュを使います。条件をセル参照にする場合も同じ考え方です。

FAQ：セル参照だと一致しないのはなぜ？

Q：セル参照にしたら結果が0になりました。

A：結合の位置や空白の混入が多い原因です。結合記号の周りに余分なスペースがないか、参照先のセルに改行や全角スペースがないかを確認します。必要に応じてTRIMやSUBSTITUTEで整えます。

「含む」の条件が複数ある場合（AND条件／OR条件）

複数の語を同時に含むか、どれか一つを含めばよいかで、式の作り方が変わります。ここではCOUNTIFとCOUNTIFSの使い分けを整理します。ANDはすべて満たす、ORはいずれかを満たすという考え方です。実務では、得意先名が複数語を含むか、メール件名がどれかのキーワードを含むかなどでよく使います。

すべての文字列を含む場合（AND条件）

ANDはCOUNTIFSを使うと簡単です。COUNTIFSは複数の条件を同じ範囲に並べられます。たとえば、A列が「AB」と「-X」をどちらも含む行の数は次のように書きます。

Excel例：=COUNTIFS(A2:A100, “*AB*”, A2:A100, “*-X*”)

スプレッドシート例：=COUNTIFS(A2:A100, “*AB*”, A2:A100, “*-X*”)

語がセルに入っている場合は、連結で「*」を足します。=COUNTIFS(A2:A100, “*”&B1&”*”, A2:A100, “*”&C1&”*”) のように書けます。

いずれかの文字列を含む場合（OR条件）

ORはCOUNTIFを足し合わせる方法が分かりやすいです。重複して数えないよう、範囲と条件の組み合わせを確認します。たとえば「AB」または「-X」を含む行の数は次のとおりです。

Excel例：=COUNTIF(A2:A100, “*AB*”) + COUNTIF(A2:A100, “*-X*”)

スプレッドシート例：=COUNTIF(A2:A100, “*AB*”) + COUNTIF(A2:A100, “*-X*”)

キーワードがたくさんある場合は、配列を使って合計をまとめる方法もあります。Excelのスピル対応版では、=SUM(COUNTIF(A2:A100, “*”&E1:E5&”*”)) のように書けます。スプレッドシートでも同様に動きます。

Excelの場合の書き方と落とし穴

Excelでは、ワイルドカードのエスケープにチルダを使います。また、古いバージョンでは配列数式が必要な場面がありました。スピルに対応していない環境では、CTRL+SHIFT+ENTERを使う配列数式が必要になることがあります。先頭のゼロを含むコードは、セルの表示形式を「文字列」にしておくと安全です。

Googleスプレッドシートの場合の書き方と落とし穴

スプレッドシートでは、ワイルドカードのエスケープにバックスラッシュを使います。FILTERやREGEXMATCHと組み合わせる場面も多く、データが変化すると結果が自動で更新されます。別シート参照ではシート名の後に感嘆符を付けます。日本語の全角スペースが混ざると一致しないことがあるため、SUBSTITUTEで置き換えると安定します。

FAQ：COUNTIFとCOUNTIFSはどちらを使うべき？

Q：複数語を含む判定はどちらが良いですか？

A：ANDはCOUNTIFS、ORはCOUNTIFの合計が分かりやすいです。語が多いときは、配列とSUMで短くできます。処理が重い場合は、範囲を必要な行だけに絞ると安定します。