Countif関数について教えてください。

質問

アンケートメールをCSV出力したものを集計しようと考えています。
Excelで、countif関数を使ってキーワードを元に数を出そうと思ったのですが、
正しい結果が出ません。

[hoge.xls]
　　　　　Ａ
１　りんごジュースは
　　りんごからできている

２　りんごとみかんは
　　違う果物だ

３　みかんとバナナでは
　　みかんの方がすっぱい

といったデータがあるときに、「りんご」というキーワードが
含まれるセルの個数を出したいと考えています。
結果を出すのは、実際には別のファイルで、A列のキーワードが何個あるかをB列に出したいと考えています。
上記の例だと

　　　Ａ　　　　Ｂ
１　りんご　　　2
２　バナナ　　　1

といった感じです。
そこでこういう関数を出してみました。

COUNTIF([hoge.xls]Sheet1!$A$1:$A$3,"*"&A1&"*")

データが少ない場合には、この関数で正しく結果が出るのですが、
実際には300件以上のデータがあり、結果が実際の
件数よりもずっと少なく出ているような感じがします。
メールですので、元データには改行も含まれますし、
１つのセルに2つ以上、同一のキーワードが含まれることもあります。

原因らしきものの検討がつきません。
どうかお知恵をお貸しください。
＃あるいは他のデータ個数抽出方法があれば、教えてください。
よろしくお願いします。

回答No.5

imogasi

下記でできないでしょうか。
配列数式になじみがないかもそれないが。やってみるとうまくいきそう。
Ａ１：Ａ１０に
a
we
as
df
ghj
zaa
sd
saer
aaadr
qqwe
を入れます。
この各行の中でaを含んでいる行を数えると、
５行あります。
それはＡ１２（どこでもよい）に
=SUM(IF(ISERROR(SEARCH("a*",$A$1:$A$10)),0,1))
といれてＳＨＩＦＴキーとＣＴＲＬキーを左手指で
抑えたまま、右手指でＥＮＴＥＲを押す。
両端に｛｝が現れますが配列数式のしるしです。
ＳＥＡＲＣＨ関数を使ったのは、＊というワイルド
カードを使えるからでＦＩＮＤではダメです。
意味は各行でaを含む文字列である場合１をそれ以外
（Ｅｒｒｏｒになる）では０をあたえ、それらを
第１－１０行にわたって加える（ＳＵＭ）ことをせよというものです。
aは実際の例の語句を置き換えてやってみてください。
その他の英字も語句の連なりと考えてください。
a=りんご
b=ジュースの場合はabは「りんごジュース」という連語のことをあらわしています。

お礼

返事が遅くなり、すみません。
データが少ない場合は、ご指摘の方法でできました。ありがとうございました。
ただ、400件近くのメールデータとなると、何が悪いのか、やはり期待値より
少ない値が出てしまいます。
文字列ということで、何かが悪さをしているのかもしれません。

Excelじゃなくて、別の方法で集計することも考えてみます。
ありがとうございました。

回答No.4

mu2011

NO.3です。
関数エラーは、余計な括弧が挿入された為ですので、以下の様に変えて下さい。
=SUM((LEN([hoge.xls]Sheet1!$A$1:$A$300)-LEN(SUBSTITUTE([hoge.xls]Sheet1!$A$1:$A$300,A1,"")))/LEN(A1))
この関数は、検索文字列の個数を計数する為で、ご質問者の期待値になりません、理解不足ですみませんでした。

尚、セル数のみであれば、COUNTIF関数で問題ありませんが、検索範囲をA列指定（A:A）でお試しください。

お礼

ありがとうございます。
A列指定にしたのですが、やはりダメでした。
期待値より少ない数が出てしまいます。
メール本文ということで、何か邪魔なものがついてるんでしょうか。
CSV形式で保存されていたものなので、問題ないと思ったのですが。。

実は、こちらの回答↓を参考にしていたのですが、
http://okweb.jp/kotaeru.php3?q=705462
No.3の回答中にある「同じセルに同じ単語が２つあるとダメ」というのが気になっています。
なぜダメなんでしょうか。

mu2011さんにお尋ねするのはちょっと違うかもしれませんが、理由をご存知なら教えてください。
何度もすみませんm(_ _)m

回答No.3

mu2011

CONTIF関数の計数はセル数になりますので、期待値より少になります。以下の方法をお試しください。

=SUM((LEN(([hoge.xls]Sheet1!$A$1:$A$300)-LEN(SUBSTITUTE(([hoge.xls]Sheet1!$A$1:$A$300,A1,"")))/LEN(A1))
入力完了時、ｃｔｒｌ＋ｓｈｉｆｔ＋ｅｎｔｅｒとして数式が｛ ｝で囲まれるようにして下さい。

補足

回答ありがとうございました。
説明不足ですみませんでした。

セル内に２つ以上同一キーワードがある場合でも、
求めたいのは「セルの個数」なので、countif を使えばいいのかなと考えた次第です。

せっかく教えていただいたのに、申し訳ありません。
教えていただいた関数も試しに入れてみたのですが、
「正しくない」とエラーではじかれてしまいました。

回答No.2

mshr1962

単純ですが上記の式だとA1:A3の3セルしか見ていません。
COUNTIF([hoge.xls]Sheet1!$A:$A,"*"&A1&"*")
で列全体を指定してみてください。

>１つのセルに2つ以上、同一のキーワードが含まれることもあります。
=SUMPRODUCT((LEN([hoge.xls]Sheet1!$A:$A)-LEN(SUBSTITUTE([hoge.xls]Sheet1!$A:$A,A1,"")))/LEN(A1))

補足

ご回答ありがとうございます。
列全体を指定してみましたが、結果は同じでした。

書いていただいた SUMPRODUCT...から始まる関数を試してみましたが、
NUMエラーが出てしまいました。

不勉強ですみませんが、もしよければ、この関数の意味を教えていただいてもいいでしょうか。

回答No.1

onara931

「"*"&A1&"*"」の部分ですが、キーワードがひとつしか認識されないのではないでしょうか？
２つ以上の同一キーワードがあっても、カウントでは「１つ」とだけ認識されてしまう気がします。

※じゃあどうすればいいのか、っていのが、まだ思いつきません。すいません。

補足

回答ありがとうございます。
私の説明不足でした。すみません。

数えたいのは「セルの個数」なので、セル内に同一キーワードが
２つ以上あっても、１カウントで問題ないです。

上記の例では「りんごというキーワード」は３つですが、
求めたいのは「りんごというキーワードを含むセルの個数」なので、結果が２であればOKです。