文字列Aに含まれる文字列Bの数をカウントする方法

セル内に特定文字列がいくつあるかカウントする場面はしばしばありますが、その要望を実現する関数は標準では実装されていません。今回はセル内の特定文字列をカウントする方法をご紹介します。

文字列をカウントする基本的な考え方

文字列Ａ内にいくつ文字列Ｂが含まれるかは 「文字列Ａの長さから、文字列Ｂを除外した長さの差分」 から求める事が出来ます。

例えば

の場合、下記の数式でカウントできます。

=(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))

LENは文字列の長さを返す関数です。SUBSTITUTEは第1引数の文字列に含まれる第2引数の文字列を第3引数の文字列に置換する関数です。

つまり、

となります。よって8 - 6 = 2で検索文字列Aは2個あることが導き出せます。

検索する文字列が2文字以上の場合

検索する文字列が1文字の場合は先ほどの数式で十分なのですが、検索する文字列が2文字以上の場合は工夫が必要です。

の場合は上記の数式で算出すると

となってしまいます。

「ABCABCAC」の中に「BC」は6個ではなく3個です。

なので下記のとおり修正します。

=(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)

変わったのは最後の/LEN(A2)の部分です。検索する文字数で除してあげれば

となり正しい個数が導き出せます。この数式なら検索する文字列が1文字でもちゃんと機能しますので良さげです。

エラーを考慮してより完璧に！

まだこれでも十分ではありません。 検索する文字列に空文字(長さ0)が入ると数式は#DIV/0!のエラーを返します。

なので

=IFERROR((LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2),0)

とするか

=(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/MAX(1,LEN(A2))

とすれば、正しい結果を得られます。

IFERORRは第1引数の数式がエラーとならない場合はそのまま計算結果を返し、エラーと評価された時は第2引数を返す関数です。例ではエラーとなった場合に0を返します。これなら#DIV/0!以外のエラーにも対応できます。

2つ目のMAXを使った方でも同じ結果が得られます。Excel 2003 以前はIFERRORがありませんので、こちらの方法で#DIV/0!のエラーをトラップできます。
簡単に説明すると、MAXは与えた引数の最大値を返す関数なので、セルA2が空白(LEN(A2)=0)の場合は固定で1を返し、結果的にその1で除すので#DIV/0!を回避できるのです。

まとめ。

標準の関数にない機能でも、関数の組み合わせ次第である程度の事は出来ます。つまり発想力が大事なんですね。

ご質問等ございましたらお気軽にお問い合わせくださいまし。

HAYs