Excel業務カイゼンブログ

セルA2に「かすみ草、黒種草、留紅草、つゆ草」と入力されていて、その中から「草」という文字が何文字入っているか、直接調べる関数はExcelにはありません。

複数セルに特定の文字数が入力されているセル数を数えるには、COUNTIF関数を使いますが、あくまで1つのセルに該当文字が何文字入っていてもカウント数は1になってしまいます。

そこで、今回は、このような場合に使える計算方法を解説します。

文字列を数える方法

文字列を直接数える関数はないので、次のように考えます。

1. セル内の文字数を求めるLEN関数で、セル内の全ての文字数を数えます
2. セルに入力されている文字列から、探したい文字を他の文字列に置き換えるSUBSTUTUTE関数を使って空白文字と置換えることで省きます
3. 2の文字数を、LEN関数を使って求めます
4. 1の結果から3の結果を差し引きます
5. ここまでで文字数を求めることができます。ただし、探す文字が「草」1文字ならばよいのですが、2文字以上の場合もあります。そこで、次の5以降の計算方法も追加します。
6. 探す文字の文字数をLEN関数で求めます
7. 4の結果を5の結果で割ります

これで探す文字が何文字でも対応できるようになります。

計算式の作成

ではセルA2入っている元の文字列の中に、探す文字列が何個あるか数える数式を考えてみましょう。

まず、1の部分の計算式は、セルA2の文字数をLEN関数で求めるので次の計算式になります。
=LEN(A2)

2の部分の計算式は、セルA2から探す文字をSUBSTITUTE関数で空白文字に変換するので次の計算式になります。
=SUBSTITUTE(A2,探す文字,"")

3の部分の計算式は、2の文字数をLEN関数で求めるので次の計算式になります。
=LEN(SUBSTITUTE(A2,探す文字,""))

4の計算式は1から3を引くので次の計算式です。
=LEN(A2)-LEN(SUBSTITUTE(A2,探す文字,""))

5の計算式は探す文字の文字数をLEN関数で求めるので次の通りです。
=LEN(探す文字)

6の計算式は4÷5なので次の通りです。
=LEN(A2)-LEN(SUBSTITUTE(A2,探す文字,""))/LEN(探す文字)

これで計算式が出来上がりです。

セルA2の中に、セルB1に入力されている文字列が何個入っているかを求めるには次の計算式です。
=LEN(A2)-LEN(SUBSTITUTE(A2,B1,""))/LEN("草")

上記の表において、セルA2の中に「草」が何個入っているかを求めるには次の計算式です。
=LEN(A2)-LEN(SUBSTITUTE(A2, "草",""))/LEN("草")
直接文字列を計算式に入力するので、" "で囲みます。

まとめ

xcelには数多くの関数が用意されていますが、実際の仕事でやりたいことをズバリ求めるような関数がないケースもあります。そのような場合は、今回のように、計算の方法や順番を工夫することが重要になってきます。足し算ができなければ、引き算、のように逆の方法で答えにたどり着くこともできます。