エクセルには様々な便利な関数があります。
自分が行いたい操作をピンポイントで実現する関数がなくても、既存の関数を組み合わせることで実現できるのがエクセルの面白いところです。
今回は、セル内に特定の文字列がいくつ含まれるかを調べる方法について解説していきます。
エクセルの応用力をつけるだけでなく、組み合わせの仕方や考え方のコツを一緒に学んでいきましょう(^^)/
今回解説する方法の使用タイミングについて
基本的には、エクセルでデータ作成をする際、あとでカウントしたいような項目はセルを分けて入力することが多く、ひとつのセルに膨大な量の文字列が入ることは少ないです。
しかし、ビジネスでは、CSVファイルなど、ひとつのセルに多くの情報が入った状態のデータをエクセルファイルにエクスポートしたりすることも多いです。
そのようなエクセルデータを集計する際は、基本的に『データの区切り位置の設定』などで項目ごとにセルを分けて、見やすいデータを作っていくのが通例ですが、ケースによってはうまくいかない場合や、のちにカウントするのが大変な場合があります。
ニッチなケースではありますが、そんなときに今回紹介する方法を使っていただければと思います。
今回使用する関数
LEN関数
指定したセルのなかにある文字数を数える関数。
=LEN(文字列)
SUBSTITUTE関数
対象の文字列を指定した文字列に置き換える関数。
数式の最後にある置換対象は、項目が複数ある場合に何番目に登場した文字列を置き換えるかを指定できる。
=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象(省略可能)])
①LEN関数で対象セルの文字列を数えよう
見やすいように図解していきますので、下図をご覧ください(>_<)
図のように、ひとつのセル内、今回のケースだとＢ列に、様々な情報がいっぺんに入ってしまっているケースを想定しています。
このようなケースで、各顧客が『PC機器』カテゴリの商品を購入した数をカウントしたいとしましょう。
これを直接一発で実現する関数はありませんので、既存の関数を組み合わせて実現してみます。
まず、B列の情報の文字数をLEN関数で取得します。
②SUBSTITUTE関数で数えたい文字列を空白に置き換えよう
今回、『PC機器』という文字列の登場回数をカウントすることで目的を実現できそうなので、まずは『PC機器』という文字列を省いた文字数のカウントを目指してみます。
図解の通り、SUBSTITUTE関数で『PC機器』という文字を空白に置き換えることができます。
関数内で文字列を設定するときは、該当の文字列に””をつけます。
今回の場合だと、”PC機器”となります。
これを空白に置き換えたい場合、空白を表すためには””のなかに何も文字列を入れなければOKです。
つまり、””だけで大丈夫ということになります。
③探したい文字列が該当セルに何文字あるかを求めよう(①-②)
次に、先ほど導いた、『PC機器』以外の文字列の文字数をLEN関数でカウントします。
それを、もともとのB列購入詳細列のセルの文字数からマイナスすれば、セル内の『PC機器』という文字列が合計何文字あるのかを導くことができますね(*’ω’*)
④探したい文字列の時数で③を割れば、何回その文字列が登場したかが導ける!!
最後に、PC機器という文字列の文字数(今回の場合は計算などしなくても4文字とわかりますが)で、先ほど計算した、セル内のPC機器という文字列の文字数を割ることで、PC機器という文字列が何回登場したのかを計算することができるのです。
つまり、以下の式をたてます。
=(LEN(B2)-LEN(SUBSTITUTE(B2, “PC機器”, “”)))/LEN(“PC機器”)
実際のエクセルの画面で計算してみましたので、見てみましょう!!
いかがでしたか？
エクセルでは、これはできなさそうだとぱっと見で思えるものでも、既存の関数を上手に使うことで実現できることが非常に多いです(>_<)
困ったときには、当ブログをぜひ参考にしてみてくださいね!!
ではまた(^^)/
