Excel。セル内に同じ文字がいくつあるのか求めたいけど、どうやったらいいの？
＜LEN関数・SUBSTITUTE関数＞
ある商品の評価結果の資料があります。
一人ずつの評価がセルごとに入力されていればいいのですが、ひとつのセルにまとめて入力されてしまっています。
これでは、Aが何件、Bが何件と数えたい場合、目視で確認するしか方法はないのでしょうか？
左から1文字目は、A。
2文字目はBとセルに文字をいれる数式をつくってから、数えるという考え方もありますし、あるいは、Excel VBAでマクロをつくるという方法もあるとは思いますが、LEN関数・SUBSTITUTE関数をつかうだけで、簡単に、セル内に同じ文字がいくつあるのか算出することができるのです。
LEN関数・SUBSTITUTE関数をつかった数式をつかうことで、次のように集計することができます。
どのような数式をつくるといいのかを確認していきましょう。
C2につくった数式は、
=LEN($B2)-LEN(SUBSTITUTE($B2,C$1,""))
この数式を、オートフィルで数式をコピーすれば、セル内の同じ文字がいくつあるのかを簡単に算出することができます。
この数式を説明しておきましょう。
LEN($B2)は、B2のセル内の文字数を算出しています。
LEN関数はセル内の文字を数える関数ですね。
セル内の文字数から、LEN(SUBSTITUTE($B2,C$1,""))を減算しています。
LEN(SUBSTITUTE($B2,C$1,""))を見ていきましょう。
最初のLEN関数は、文字を数える関数でした。
何を数えているのかというと、
SUBSTITUTE($B2,C$1,"")
SUBSTITUTE関数は、指定した文字を置換する関数です。
B2の中にある、C1＝「A」を、「””」＝空白に置換させています。
H2に、
=SUBSTITUTE(B2,"A","")
の結果を見ると、何をやっているのかがわかります。
要するに、「A」を除いた文字列をつくっていたわけです。
この文字数を数えて、Aも含めた最初のLEN関数で算出したあたいから、減算すれば、「A」がいくつあったのかがわかるという仕組みです。
発想としては、どうしても「A」を残すように考えてしまいますが、Aだけを残すというのが、難しいので、逆転の発想で、算出させてみました。
今回のように、全体の数から減算するというのは、よくある方法ですので、覚えておくと、いざという時にいいのかもしれませんね。
なお、同じようなパターンとしては、「○」「×」で、「○」の数はいくつなのかということもできます。
2種類の場合は、一つ算出できれば、おのずと、もう一つも算出されるので、今回のケースよりも、もっと簡単に算出することもできます。