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

alphabet

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




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

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

例えば

セルA1:ABCABCBC
セルA2:A

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

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

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

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

つまり、

LEN(A1)
=「ABCABCBC」の文字数
= 8

LEN(SUBSTITUTE(A1,A2,””))
=「ABCABCBC」から「A」を空文字(””)に置換した文字数
=「BCBCBC」の文字数
= 6

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

検索する文字列が2文字以上の場合は注意!

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

セルA1:ABCABCBC
セルA2:BC

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

8(ABCABCBC)- 2(AA)= 6

となってしまいます。

6じゃなくて3だけど...

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

間違ってるじゃないの。ばかばか。

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

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

文字列長で割ってあげる!

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

(8 – 2) / 2 = 3

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

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

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

なので

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

IFERROR関数を使ってみる

とするか

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

MAX関数を使ってみる

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

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

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

まとめ。

標準の関数にない機能でも、関数の組み合わせ次第である程度の事は出来ちゃいます。つまり発想力が大事なんですね。アタマカタイ星人は脳みそコネコネしなくちゃですな。

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

HAYs