今日はハテナコがExcelでお悩み中です
うーん、なんかいい方法ないかしら・・。
何してるの、ハテナコちゃん。
今、Excelでこんなデータの集計作業をしているんだけど
この中で、例えば「きゅうり」という単語が何回出てくるかを調べたいの。 でも、うまくいかなくて悩んでたのよ。
数えるっていうと、COUNTIF関数使えばできそうなイメージだけど、それじゃだめなの？
そうなのよ、それだとね・・
そこから先はワシの担当じゃ。
うわっ！びっくりしたぁ。じゃあせんせー早速教えてくださいよー。
COUNTIFが返すのは、あくまで「セル」の個数
では、まず、ハテナコが言いかけた、COUNTIF関数ではうまくいかない理由から説明するぞ。 ズバリ、COUNTIF関数で求められるのは「セル」の個数だからじゃ。
セルの個数？
そうじゃ。だから例えば、一つのセルに該当する単語が複数あったとしても、返す値は「セルの個数」だから、1個としてカウントされてしまうのじゃ。
じゃあどうしたらいいんですか？せんせー。
関数をうまく組み合わせ、計算して求める
ドンピシャでそういった値を返す関数は、残念ながらないんじゃ。だがその代わりに3つの関数を使って上手に計算する方法がある。それを使うといいぞ。
もったいぶらずに、早く教えてくださいよ、せんせー！
まぁそう慌てるでない。使う関数は、SUM関数とLEN関数、SUBSTITUTE関数じゃ。
- SUM関数：合計を求める関数
- LEN関数：文字列の長さ（＝文字数）を返す関数
- SUBSTITUTE関数：文字を置き換える関数
・・・でしたよね。
そうじゃな。ポイントは、文字数を上手に使うことじゃ。 計算の大まかな内容としては、
- まず、全文字数を求める。→ A
- そして、該当単語を削除した（""に置き換えた）場合の文字数を求める。→ B
- AからBを引いて出た差＝該当単語だけの合計文字数となる。
それを該当単語文字数で割れば回数が出る
・・・という流れじゃ。
でもせんせー。LEN関数で複数セルの文字数って求められたっけ？LEN関数の引数にセル範囲を書いても、エラーになるよね？
ほほう。もやたろう、いいところに気付いたのぉ。確かにもやたろうの言う通りじゃ。そこで必要になるのが、もう一つのポイント、配列数式じゃ。
配列数式にすることをお忘れなく
配列数式を使うことで、LEN関数で求めた各セルの文字数を要素とした配列とすることができる。そうすればSUM関数でその配列内の合計を求められるのじゃ。
配列数式にはするには、確かちょっと変わった入力の仕方をしないといけないんですよね。ええっと、どういうふうだったっけ・・。
Ctrl+Shift+Enterじゃ。数式を入力した後、単にEnterキーだけ押しても、配列数式にはならないぞ。Ctrl+Shift+Enterで数式を確定させる必要があるから注意が必要じゃ。うまく入力できると、式の最初と最後に｛｝がついているはずじゃ。
以上のことを踏まえて式を作ると、こうなるぞ。
=SUM(LEN(文字数を求めたいセル範囲)-LEN(SUBSTITUTE(文字数を求めたいセル範囲,該当単語,"")))/LEN(該当単語)
こうして、式を作って最後確定時に、Ctrl+Shift+Enterで確定させると、
{=SUM(LEN(文字数を求めたいセル範囲)-LEN(SUBSTITUTE(文字数を求めたいセル範囲,該当単語,"")))/LEN(該当単語)}
という風に、最初と最後に｛｝がついた状態になるぞ。
どれどれ、早速やってみよう。範囲と該当単語をそれぞれ当てはめてっと。こうね！
せんせー、できました！！｛｝も忘れずに入れてバッチリでしょ！
やったね、ハテナコちゃん！
皆さんもぜひ試してくだされ。