アイマニ備忘録

自称IT好きが、パソコンやスマホ、Office等に関して思うことを書いています。その他、ゲームや日常についても語るかも。

【Excel】関数で単語の出現回数を数える方法

f:id:ai_mani:20161009192128p:plain

今日はハテナコがExcelでお悩み中です

ハテナコ

うーん、なんかいい方法ないかしら・・。

もやたろう

何してるの、ハテナコちゃん。

ハテナコ

今、Excelでこんなデータの集計作業をしているんだけど

f:id:ai_mani:20161010003700p:plain

この中で、例えば「きゅうり」という単語が何回出てくるかを調べたいの。 でも、うまくいかなくて悩んでたのよ。

もやたろう

数えるっていうと、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(該当単語)}

という風に、最初と最後に{}がついた状態になるぞ。

ハテナコ

どれどれ、早速やってみよう。範囲と該当単語をそれぞれ当てはめてっと。こうね!

f:id:ai_mani:20161010003903p:plain

せんせー、できました!!{}も忘れずに入れてバッチリでしょ!

もやたろう

やったね、ハテナコちゃん!

せんせー

皆さんもぜひ試してくだされ。

© 2016 アイマニ備忘録

スポンサードリンク