【エクセル】一列中にある文字列の種類をカウントする関数

質問

お世話になります。

ちょっと、解決に時間がかかっている問題なのですが、
「一列の中で、何種類の文字列パターンがあるか」、
を数える関数（同じ言葉は、一回しか数えないで、列中に何種類あるかを数えたいのです】がないか、ずっと探しています。

count関数でもうまくいかないですし・・・
ピボットだったらできるんですけど、
レイアウト上、関数でできれば、とても助かります。

お手数かけますが、ご指導よろしくお願い致します。

回答No.1

mshr1962 · ベストアンサー

=SUMPRODUCT(1/COUNTIF($A$1:$A$60000,$A$1:$A$60000))
ですね。

お礼

ありがとうございます。

お蔭様で、うまくいきました。
この関数知らなかったですぅ。
勉強になりました！

60000の行も任意に変えていったら、
いいんですね。

回答No.4

shiotan99

こんにちは～

> 一列の中で、何種類の文字列パターンがあるか

範囲は A1：A100 のように固定ですか？ それとも変動するのでしょうか？

範囲が固定だとしても、範囲内に空白セルが 1つでもあれば、No.1さん、No.3さんのやり方ではエラーになると思います。
（ No.1さんのように 60000行まで指定すれば、PCがフリーズするかも‥ ）

★範囲が一定していない、または範囲内に空白セルもありうる場合、
（ 最大 100行だとすれば ）

=SUM(IF(LEN(A1:A100),1/COUNTIF(A1:A100,A1:A100),0))

または
=SUM(IF(LEN(A1:A100),MATCH(A1:A100,A1:A100,0)=ROW(A1:A100))*1,0)

と入れて、配列数式ですので Ctrl＋Shiftキーを押しながら Enterで確定してください。
数式が { } で囲まれたら OKです。
囲まれなかったら NGですので、F2キーを押して編集モードにしてから、もう一度 Ctrl＋Shiftキーを押しながら Enterを押してください。

★配列数式ですので、範囲が広いと重くなります。

お礼

shirotan99さん、こんにちは～。

丁寧ねアドバイス、ありがとうございました。
確かに今後、空白セルを含むケースが出てくる可能性があるので、
色々なやり方を教えていただけて、助かります。

LENとCOUNTIFを組み合わせて、こんなことができるのですね！
はずかしながら、なぜ、COUNTIFの後ろに、二つA1:A100
が来る意味もわかっていないので、
しっかり理解して、成長につなげたいと思います。

＃No.3のコメントで書いた範囲指定の意味も、こちらの解説で分かりました。ありがとうございます。
配列数式は、重くなるんですね！

回答No.3

mu2011

以下のサイト方法ですが如何でしょうか。

{=ROUND(SUM(1/COUNTIF(A1:A100,A1:A100)),0)}
{ }は関数入力最後にshift＋ctrl＋enterを同時入力して下さい。

参考URL：

http://www.ichikura.com/excel-contents/ex-con.htm

お礼

mu2011さん、ありがとうございました。

試してみたら、うまくいきました。

自分ひとりだと全くうまくいかなかったんですけど、
色々な方法があるんですねぇ。

恥ずかしながら、{ }や、shift＋ctrl＋enterの意味も
分かっていないので、もう一度、教本を見返して、
分からなかったら、またgooに登校させていただくかもしれません。

補足

教えていただいたサイトもいいですねぇ。

列挙式でいろいろ書かれているので、
関数のチェックと手法の気付きに
活用させていただこうと思います。

回答No.2

Wendy02

こんにちは。

例えば、以下のように、1列の行の文字列が、10,000個あるとすれば、明示的に範囲を決めて、以下のように計算されることは出来ます。それは、#1 さんの式でも同様です。

=SUMPRODUCT((MATCH(A1:A10000,A1:A10000,0)=ROW(A1:A10000))*1)

しかし、暗黙的に、予め決め打ちして範囲を1列（仮に、10,000行でも）定めることは不可能ではないか、と思います。ExcelのVersion によっては、ハングするはずです。暗黙的に、1列の文字列のパターンをカウントするのは、式として長くなりますが、

=SUMPRODUCT((MATCH(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A1:A"&COUNTA(A:A)),0)=ROW(INDIRECT("A1:A"&COUNTA(A:A))))*1)

ということぐらいだと思います。しかし、これも、1列全ての65536個の文字列の場合や列の中間に、空白(Empty)がある場合はできません。

少なくとも、空白(Empty）が、間に含まれる場合は、以下のような式にして、

=SUM(IF(INDIRECT("A1:A"&COUNTA(A:A))="",0,IF(MATCH(INDIRECT("A1:A"&COUNTA(A:A)),INDIRECT("A1:A"&COUNTA(A:A)),0)=ROW(INDIRECT("A1:A"&COUNTA(A:A))),1,0)))

配列式ですから、F2を押して、『ShiftとCtrlを押しながらEnterキー』を押して、再確定して、答えを出すようにしなければならないと思います。

もし、ワークシート内の見栄えを考慮するなら、ユーザー定義関数ぐらいしかないか、と私は思います。

お礼

Wendy02さん、とても丁寧な解説、ありがとうございました。

確かに、決め打ちが不可能だったり、列間に空白があったりする可能性はありますので、とても参考になります。

しかし、INDIRECTの意味すらおぼつかないので、
もう一度、テキストをしっかり確認しようと思います（笑）
がんばらねば・・・