小ネタ・便利技

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

投稿日:2015.12.13 更新日:

alphabet photo

以前(というかかなり昔)、とある方から

『例えば「ABCABCBC」っていう文字列に「A」がいくつあるか数えるエクセルの関数ってある?』

って問い合わせがあったんですが、皆様にもその方法を連携しますねー。

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

文字列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

-小ネタ・便利技
-, , , , ,


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

【Excel】ユーザー設定リストを活用してオートフィルと並べ替えをカスタマイズ

Excel には「ユーザー設定リスト」という順序に関する情報を保存している機能があります。順序に関する情報なので、使いこなせばオートフィルや並べ替えの機能をより使いやすくすることも可能です。今回はこの …

number

文字列として保存された”数字”を計算などで使用できる”数値”に一括で変換する方法

文字列として保存された”数字”は計算などには使用出来ません。そういった”数字”は”数値”に変換してあげなければならないんですが、 …

book

大量シートの中から目的のシートを簡単に選択する方法

ブック内にシートがたくさんある時って、目的のシートを探して選択するのって面倒じゃないですか?面倒ですよね?面倒なんですか?そうですか。そんなあなたに目的のシートを簡単に選択する方法をご紹介します。  

hide-returned-zero

数式の計算結果で0を表示しない方法

VLOOKUP関数やINDEX関数、あるいは「=A1」などセルを参照する数式で、その計算結果が空白セルを参照する場合、Excel は空白(“”)ではなく0(ゼロ)を返します。今 …

VBAで一番省略されているキーワード

VBA にはプロパティやステートメントなど様々なキーワードがあります。そして中には記述を省略できるものも少なくありません。 では一番省略されているキーワードっていったいなんなのでしょうか?