COUNTIF関数 この場合の模範式は？

質問

いつもお世話になってます。下記の質問についてご教示の程よろしくお願いします。

[sheet1]のA1:A100に[リンゴ]と入力してあるセルがあります。

[sheet2]のC1列に果物の品名が記載されてますので、C2列にC1列の品名を検索条件に[sheet1]のA1:A100に[リンゴ]と入力してあるセルの数を「COUNTIF関数」を使って表示させようと思ってます。

ただし、[sheet1]のA1:A10に[リンゴ]と入力してあるセルの中には[リンゴ　]と入力してあるものや[リンゴ　　　　]と入力してあるセルもあり、"ゴ"の後のスペースが無いものや１つ２つ入れて入力してあるものなどあります。

左隣のセルの文字と同じものが入力された他シートの検索範囲にある、スペースの有無、長さが混在したセル数を数えるにはどのような式を使えばいいのでしょうか？

よろしくお願いします。

※ エクセル2003

回答No.5

EL-SUR · ベストアンサー

#1、#2です。

>>=COUNTIF(Sheet1!A$1:$A$100,"*"&C2&"*")
>↑この式の["*"&C2&"*"]部分の意味がよく解かりませんが。

↑は質問でしょうか？　一応お答えしておきます。
結局、検索条件の品名は Sheet2のどのセルにあるんでしょうか？
　　C　　　　　D
1　品名　　　個数
2　リンゴ
3　みかん
4　ぶどう
・
#1、#2の式は Sheet2が↑のようになっていると推測してのものです。
C2セルに検索条件の品名があって、その隣のD列に式を入れる。
だから D2セルに入れる式は↓のようになる、ということです。
=COUNTIF(Sheet1!A$1:$A$100,"*"&C2&"*")

C3から下にも品名が入っているなら D2の式をそのまま下にフィルコピーすればOK。
　◇
ついでだからもう少し説明しておきます。
検索条件に使うアスタリスク「*」には特別な意味があります。
検索条件が「リンゴ」だけだと完全に一致するものしか対象になりません。うしろにスペースが入っていてもそれはちがうものとみなされます。

「リンゴ*」のようにうしろだけにアスタリスクをつけると「リンゴ」で始まるすべての品名、「リンゴジュース」とか「リンゴ」のあとにスペースが入っているものが対象になります。

「*リンゴ」のように前だけにアスタリスクをつけると「リンゴ」で終わるすべての品名、「青リンゴ」とか「リンゴ」の前にスペースが入っているものが対象になります。

「*リンゴ*」のように前後にアスタリスクをつけると「リンゴ」という文字列を含むすべての品名が対象になります。「リンゴジュース」も「青リンゴ」もスペースが「リンゴ」の前にあろうがうしろにあろうがすべてです。

このアスタリスクを COUNTIF関数で使うと
=COUNTIF(Sheet1!A$1:$A$100,"*リンゴ*")

のようになるということです。
スペースが品名のうしろにしか入っていないなら↓でもかまいません。
=COUNTIF(Sheet1!A$1:$A$100,"リンゴ*")

別のセル（ たとえば C2セル ）に「リンゴ」と入っていて、それを検索条件にする場合は
=COUNTIF(Sheet1!A$1:$A$100,"*C2*")
とはできません。↓のようにします。
=COUNTIF(Sheet1!A$1:$A$100,"*"&C2&"*")　とか
=COUNTIF(Sheet1!A$1:$A$100,C2&"*")

これで疑問への回答になっているでしょうか？
★ただし#2のくり返しになりますが、アスタリスクを使うと「リンゴ」だけでなく「リンゴ」を含む品名（ 「青リンゴ」とか「リンゴジュース」とか ）も対象になってしまうので、そういう品名がほかにある場合は使えません。

そういう場合は #2の
=SUMPRODUCT((TRIM(Sheet1!$A$1:$A$100)=C2)*1)

を使ってください。=C2 は、C2セルに「リンゴ」という検索条件があるということです。これだとスペースの有無に関わらず、対象は「リンゴ」だけになります。

なぜSheet1の品名にスペースが入っているのかわかりませんが、いちばんいいのは、これも #2のくり返しになりますが置換でスペースを取ってしまうことです、取ってしまってもいいならですが。

お礼

ありがとうございます。大変参考になりました！今後にも活かしたいと思います。m(__)m

回答No.4

imogasi

りんごはリンゴもありえます。ここまで考えると収集がつかないので
（１）リンゴ＋後なし
（２）リンゴ＋半角スペース１こ（２個以上でも可）
（３）リンゴ＋全角スペース１こ（２個以上でも可）
に限るなら、リンゴジュースの混入カウントも省けて、下記でどうですか。
例データ
Ａ列　
りんご （１）のケース
りんご （２）のケース
りんご　 （３）のケース
バナナ
みかん
りんご　 （３）のケース
として
Ｂ１にリンゴを入れる。

配列数式ですが
=SUM(IF((A1:A10=B1)+(A1:A10=B1&" ")+(A1:A10=B1&"　"),1,""))
とＣ１にでも入れて、ＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲキーを同時に押す。＋は「または」の意味で入れるものです。
結果　４
Ｓｈｅｅｔが別ならA1:A10をＳｈｅｅｔ１！Ａ１：Ａ１０のように変えてください。

補足

とりいそぎお礼申し上げます。後ほど職場で試してみます。

C1列 C2列 と言いましたが 1列 2列の誤りです。

この場合に使用する使い勝手の良い他の関数をご存知でしたら教えて下さい。ありがとうございました。

回答No.3

noname#204879

「C1列」「C2列」ってどういう意味ですか？

》 C1列に果物の品名が記載されてます…

「果物の品名」って[リンゴ]のこと？

》セルの数を「COUNTIF関数」を使って表示させよう…

COUNTIF関数に固執する理由は？

お礼

ありがとうございます。

回答No.2

EL-SUR

#1ですが、ちょっと補足。

#1のワイルドカードを使う場合、もし Sheet1のA列に「リンゴジュース」などの品名もあるとそれもカウントしてしまいます。

スペースだけの問題で、上のような場合もあるなら
=SUMPRODUCT((TRIM(Sheet1!$A$1:$A$100)=C2)*1)

の方がいいかもしれません。

またSheet1のA列のスペースをとってしまってもいいなら
Sheet1のA列を選択して、メニューの編集～置換

置換する文字列→全角スペースをひとつ入れ、
置換後の文字列には何も入れないで「すべて置換」をクリック

半角スペースも入っているなら上の操作を半角スペースで繰り返してください。

以上でスペースはなくなりますので普通に COUNTIF関数で数えることができます。

お礼

あぁ。こういう質問に対しての回答以外にも「ちなみ」的に教えてくださる情報って凄くまさに朗報です。φ(.. )ありがとうございます。

回答No.1

EL-SUR

そういう場合はワイルドカード「*」を使います。

=COUNTIF(Sheet1!A$1:$A$100,"*リンゴ*")

Sheet2のC2セルに「リンゴ」と入っているなら
=COUNTIF(Sheet1!A$1:$A$100,"*"&C2&"*")

下にフィルコピーしてください。

お礼

ありがとうございます。

>=COUNTIF(Sheet1!A$1:$A$100,"*リンゴ*")

OKです！。スペースの有無は関係ないのですね。

>=COUNTIF(Sheet1!A$1:$A$100,"*"&C2&"*")
↑この式の["*"&C2&"*"]部分の意味がよく解かりませんが。