特定の文字列を含む/含まないをCOUNTIF関数でカウント

  > > COUNTIF関数で含むをカウント

ここでは、エクセルのCOUNTIF関数で「特定の文字列を含む/含まないセルをカウントする方法」を解説します。

 

「含む/含まない」の検索は「あいまい検索」とも呼ばれますが、普段と違うのは「検索条件の指定の仕方」です。

 

「あいまい検索」とはなにか?と併せて、検索条件の指定の仕方を説明します。



1.あいまい検索とは?部分一致とは??

始めに「あいまい検索」とはなにか、「部分一致」とはどのようなものかから見てみましょう。

 

1-1.あいまい検索とは

「あいまい検索」とは、「『部分一致』の条件で検索すること」を言います。

 

「部分一致」とは、「条件値と部分的に一致している」状態のことです。

 

例えば、「『あ』という文字が含まれているか?」は部分一致です。また、「『あ』という文字が含まれていないか?」も部分一致です。

 

対してよく使われる「A1=1(セルA1は1か?)」は、「完全一致」と言います。

 

ちなみに「含む」は、言い換えると「特定の文字列の前後は何でもいい」ということです。

 

1-2.部分一致の形は全部で6種類×2

部分一致は、「含む/含まない」だけではありません。次の形があります。

「含む」系 「含まない」系
含む 含まない
から始まる から始まるもの以外
で終わる で終わるもの以外
の前後に〇文字づつ 前後に〇文字づつ以外
後に〇文字 後に〇文字以外
前に〇文字 前に〇文字以外

※ 〇には「文字数」が入る

 

このように、「含む」以外にも「特定の文字から始まる(=特定の文字の後ろは何でもいい)」、「特定の文字で終わる(=特定の文字の前は何でもいい)」というように、前後どちらかだけ何の文字でもいい、という形も「部分一致」です。

 

また、文字数の制限を設けるか設けないか、そもそも「含む」系か「含まない」系かといった形でも分かれます。

 

1-3.あいまい検索の例

では、あいまい検索の例を一つ見てみましょう。

上の表は、仕入れのリストです。

 

この表から、「商品コードに『bk(色がブラック)』を含む商品の仕入回数」をカウントする場合、数式は次のようになります。

=COUNTIF(B4:B8,"*bk*")

 

検索条件に「*」が付いているのが分かると思いますが、これは「ワイルドカード」と呼ばれる特殊な記号です。数値を乗算しているのではありません。

 

COUNTIF関数は、「検索条件にワイルドカードを付ける」ことで、あいまい検索にできるのです。



2.ワイルドカードは「*」と「?」の2種類

「ワイルドカード」には、次の2種類があります。

種類 特徴
 *  文字数を指定しない時に使う
 ?  文字数を指定したい時に使う

「*」と「?」はどちらも「全て(何でもいい)」という意味です。カードゲームに出てくる「ワイルドカード」と一緒です。

 

一方で、「*」と「?」の違いは「文字数を指定するか?」です。

 

「*」は、文字数の制限を設けない場合に使います。反対に「?」は、文字数の制限を設ける時に使います。

【 使用例 】

"*あ*"    :「あ」を含む(文字数制限なし)

"?あ?"  :「あ」の前後に何か1文字づつ

"あ???":「あ」の後に何か3文字

一般的には文字数を指定しないことが多く、その結果「?」よりも「*」の方がよく使われます。



3.特定の文字を「含む」セルをカウントするには

では、「特定の文字を『含む』セルをカウント」する数式の形を見てみます。

COUNTIF関数を使って「特定の文字を含むセルをカウント」する方法の例

上の表は、社員基本情報の一覧表です。

 

この表の「勤務形態(E4~E18)」に、「非」という文字を含むセルがいくつあるかカウントしてみます。

 

数式の形は、検索条件に入れる条件値を「文字列」にするか、「セル番号」にするかで変わってきますので、それぞれ確認します。

 

3-1.条件値を「文字列」で指定する場合

条件値を文字列で指定する場合のポイントは、次の通りです。

  • 条件に応じた「検索条件の形」を確認する
  • 条件値に「*」や「?」を付け、条件値全体を「"」で囲む

 

条件に応じた「検索条件の形」や数式の例は、次の通りです。

条件 検索条件の形 数式の例
含む "*条件値*" =COUNTIF(E4:E18,"*非*")
から始まる "条件値*"

=COUNTIF(E4:E18,"非*")

で終わる "*条件値"

=COUNTIF(E4:E18,"*非")

の前後に1文字 "?条件値?"

=COUNTIF(E4:E18,"?非?")

後に1文字 "条件値?"

=COUNTIF(E4:E18,"非?")

前に1文字 "?条件値"

=COUNTIF(E4:E18,"?非")

このような形になるよう条件値に「*」や「?」を付け、その後条件値全体を「"」で囲みます。

 

例えば「含む」条件の場合、数式の形は次のようになります。

 

一つ例を見てみましょう。

先ほどの表から「勤務形態に『非』という文字を含む」という条件でカウントしたい場合、検索条件は「*非*」、数式は、

=COUNTIF(E4:E18,"*非*")

となります。

 

3-2.条件値を「セル番号」で指定する場合

条件値をセル番号で指定する場合のポイントは、次の通りです。

  • 条件に応じた「検索条件の形」を確認する
  • 「"」で囲んだ「*」や「?」を「&」で条件値につなげる

 

条件に応じた「検索条件の形」や数式の例は、次の通りです。

条件 検索条件の形 数式の例
含む "*"&セル番号&"*"  =COUNTIF(E4:E18,"*"&H1&"*")
から始まる セル番号&"*"

 =COUNTIF(E4:E18,H1&"*")

で終わる "*"&セル番号

 =COUNTIF(E4:E18,"*"&H1)

前後に1文字ずつ

"?"&セル番号&"?"

=COUNTIF(E4:E18,"?"&H1&"?")

後に1文字 セル番号&"?"

=COUNTIF(E4:E18,H1&"?")

前に1文字 "?"&セル番号

=COUNTIF(E4:E18,"?"&H1)

このような形になるよう「"」で囲んだ「*」や「?」を「&」でつなぎます。

 

例えば「含む」条件の場合、数式の形は次のようになります。

 

こちらも例を見てみましょう。

先ほどの表から「勤務形態に『非』という文字を含む」という条件でカウントしたい場合、検索条件は「"*"&H1&"*"」、数式は、

=COUNTIF(E4:E18,"*"&H1&"*")

とも書けます。


口コミ・評判のいいExcel関数本の紹介】

 Excel関数+組み合わせ術 [実践ビジネス入門講座]

4.特定の文字を「含まない」セルをカウントするには

続いて、「特定の文字列を『含まない』セルをカウント」する数式です。

 

ここでも先ほどと同じように、条件値を文字列で指定する場合とセル番号で指定する場合の2種類を見てみましょう。

 

4-1.条件値を「文字列」で指定する場合

この場合のポイントは、次の通りです。

  • 条件に応じた「検索条件の形」を確認する
  • 条件値の一番左に「<>」を付ける
  • 条件値に「*」や「?」を付け、条件値全体を「"」で囲む

 

条件に応じた「検索条件の形」や数式の例は、次の通りです。

条件 検索条件の形 数式の例
含まない "<>*条件値*"

=COUNTIF(E4:E18,"<>*非*")

から始まらない "<>条件値*"

=COUNTIF(E4:E18,"<>非*")

で終わらない "<>*条件値"

=COUNTIF(E4:E18,"<>*非")

前後に1文字以外 "<>?条件値?"

=COUNTIF(E4:E18,"<>?非?")

後に1文字以外 "<>条件値?"

=COUNTIF(E4:E18,"<>?非?")

前に1文字以外 "<>?条件値"

=COUNTIF(E4:E18,"<>?非?")

このような形になるよう、条件値の左側に「<>」を付け、形に合わせて「*」や「?」を付けた後、条件値全体を「"」で囲みます。

 

例えば「含まない」条件の場合、数式の形は次のようになります。

 

一つ例を見てみます。

先ほどの表から「勤務形態に『非』という文字を含まない」という条件でカウントしたい場合、検索条件は「"*<>非*"」、数式は、

=COUNTIF(E4:E18,"<>*非*")

となります。

 

4-2.条件値を「セル番号」で指定する場合

この場合のポイントは、次の通りです。

  • 条件に応じた「検索条件の形」を確認する
  • 条件値の左に「"<>"」を付ける
  • 「"」で囲んだ「*」や「?」を「&」でつなげる
  • 「"<>"」と「"*"」や「"?"」は組み合わせて一つにする

 

条件に応じた「検索条件の形」や数式の例は、次の通りです。

条件 検索条件の形 計算式の例
含まない "<>*"&セル番号&"*" =COUNTIF(E4:E18,"<>*"&H1&"*")
から始まらない "<>"&セル番号&"*"

=COUNTIF(E4:E18,"<>"&H1&"*")

で終わらない "<>*"&セル番号

=COUNTIF(E4:E18,"<>*"&H1)

前後に1文字ずつ以外 "<>?"&セル番号&"?"

=COUNTIF(E4:E18,">?"&H1&"?")

後に1文字以外 "<>"&セル番号&"?"

=COUNTIF(E4:E18,"<>"&H1&"?")

前に1文字以外 "<>?"&セル番号

=COUNTIF(E4:E18,"<>?"&H1)

このような形になるよう、条件値の左側に「"<>"」を付け、形に合わせて「"*"」や「"?"」を付けた後、条件値と「&」でつなぎます。

 

ちなみに、左側に「"*"」か「"?"」を付ける場合には「"<>"」と組み合わせます。

 

例えば「含まない」条件の場合、数式の形は次のようになります。

 

こちらも例を見てみましょう。

先ほどの表から「勤務形態に『非』という文字を含まない」という条件でカウントしたい場合、検索条件は「"<>*"&H1&"*"」、数式は、

=COUNTIF(E4:E18,"<>*"&H1&"*")

とも書けます。

 

なお、数式の書き方はCOUNTIFS関数でも同じですので、応用してみてください。

 

以上参考になれば幸いです。

 



なお、COUNTIF関数を使いこなすために必要な知識を載せたページへは、以下のリンクをクリックすることで移動することができます。

  • COUNTIF関数 使いこなすためのポイントはこちら