Excel。セル内にある○×それぞれの文字を数えるにはどうしたらいいの？
＜LEN+SUBSTITUTE関数＞
やりたいことは簡単でも、それをどうやって「数式化」したらいいのか、悩むケースがあります。
例えば、テキストファイルなどデータが送られてきて開いてみたら、セル内に○×の結果が詰まっていたというのが次の表。
B列のデータは、アンケート結果の○×が詰まったデータというわけです。
そのデータに○がいくつ含まれているのか？
×はいくつ含まれているのか？ということを知りたい場合、目視で数えるというのは、大変以外の何物でもありません。
Excel VBAでマクロを作成するとか、あるいは、一文字ごと、セルひとつずつに、○や×を入力し直すというのも大変ですし、MID関数とか使うのも面倒です。
今回のような場合、LEN関数とSUBSTITUTE関数のコンビネーションで、比較的簡単に問題を解決することができるのです。
とりあえず、D2にLEN+SUBSTITUTE関数のネストの数式を作ってみましょう。
D2に作る数式は、
=LEN(SUBSTITUTE(B2,"×",""))
E2の数式は、D2でつくった数式と異なります。
E2の数式は、
=LEN(B2)-D2
あとは、オートフィルで数式をコピーしてみましょう。
とても簡単に算出することができました。
仮に、○×▲のような３つであっても、作り方としては、同じです。
それでは、数式を確認していくことにしましょう。
D2に設定した数式。
=LEN(SUBSTITUTE(B2,"×",""))
LEN関数は、セル内の文字数を算出する関数です。
単純に=LEN(B2)とすれば、10と返してくれるわけです。
では、引数でつかっている、
SUBSTITUTE(B2,"×","")
を確認していきます。
SUBSTITUTE関数は、検索した文字を、希望する文字に置換してくれる関数です。
今回は、「×」を「””」つまり空白に置換させているわけです。
考え方のポイントとして、「○」だけ数えるには、「×」がなければいいわけです。
文字数を数えてくれる関数は、LEN関数というのがあるわけですから、「○」だけにしたい。
だったら、「×」を消しちゃえばいいのでは、という発想したわけです。
だから、「×」を「空白」にするために、SUBSTITUTE関数をつかったというわけです。
B2のセルは、×が3個あるので、それが3個の空白に置換されるわけですから、残った文字数を数えれば算出されるというわけです。
「○」の数がわかれば、あとは、全体から「○」の数を減算すれば、「×」の数を算出することができるわけですから、E2の数式は、
=LEN(B2)-D2
となるわけですね。
文字関係は、色々なアイディアで、算出したり、変えたりすることができますので、文字列関係の関数を知っておくといいのかもしれませんね。
