EXCELでの重複データカウント方法について（過去問読みましたがわかりません）

質問

いつもお世話になっております。excel97の関数に関してです。
過去問を検索し、
http://oshiete1.goo.ne.jp/kotaeru.php3?q=118918において、

質問：会社名のデータが1万件あります。
その中には、同じ会社名が重複しているものがあります。
そこで、重複しているデータは１つのものとしてカウントし、全部で何件の会社が存在するかカウントする方法はあるでしょうか？

回答：関数でやるとすると。。。。
データが、Ａ１～Ａ１０に入っているとします。
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))
とＡ１１に数式を入力します。
これではいかがでしょうか？？
解説
COUNTIF関数でそれぞれの会社の数をカウントし、
SUMPRODUCT関数で配列の積をもとめます。

というのがあり、未熟者の私は理屈はよく分からないまま、この式でやってみたあと実際に数えてみたのですが、いつも正解数より１多くなってしまうのですが、この式の最後に-1を付ければいいのかな？ と思ったのですが、いかがでしょうか？

回答No.8

hiromuy · ベストアンサー

ＧＯ！　・・・(*_*)?

※一応、適当な箇所で会社名を" "に変えてみて、結果が正しいかご確認を・・・

お礼

無理を聞いて頂き、ありがとうございます！
確認もしました。

お礼にhiromuyさまの質問が、私のわかることならお答えしようと思い（パソコン関係は無理そうですが（＾＾；）、検索してみましたが、残念ながら、ひっかかりませんでした。（文章中に名前が出てこないと、ひっかからないそうですね。）
感謝の表しようがありませんが、本当に助かりました。

回答No.7

hiromuy

hiromuyです。
あれ！？そうですか。う～ん？
数式後半のIF文中で、
COUNTIF(A1:A10,0)=0が条件式ですが、これはA1:A10の範囲にあるブランクセル（数式は入力されている）の個数が０の場合ということで、
TRUEの場合、０
FALSEの場合、１
を返すようにしています。
従って、ブランクセルが１つ以上あれば、FALSEの場合の「１」が返り、－１されると思ったんですが・・・
（ブランクセルが０個（ない）場合はTRUEの「０」が返り、－１はされない）
IF(COUNTIF(A1:A10,0)=0,0,1)
これでいけると思ったんですが、最後の並びが(0,1,0)でうまくいくのであれば、他の原因があるのでしょうか？
色々想像してみたのですが、申し訳ありませんが今のところ思いつきません。

補足

たびたびほんとうにありがとうございます。遅くなり申し訳ありません(風邪でダウンしておりました）。
確かに、そう言われてみると、理屈としては(0,1,0)ですね。
それで、ブランクをゼロと認識しているのかどうなのかがそもそも疑問なので、（A1:A10," "）と、この部分を0の代わりに" "としてみました。そうしたら、IF(COUNTIF(A1:A10," ")=0,0,1)
この式で、正しい結論が導けるようです。
hiromuy様、また甘えて申し訳ありませんが、これで良し！ と、ゴーサインを頂けると大変心強いのですが...。

回答No.6

hiromuy

遅ればせながら再登場です。
ブランクセル（数式は入力されている）は「０」と見なされている様ですので、下記のように、
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))-IF(COUNTIF(A1:A10,0)=0,0,1)
として、ブランクが１つでも存在した場合に－１するようにしてみるのはどうでしょうか。

補足

ありがとうございます。
やってみました。もしや、最後の（・・・=0,0,1）は、（・・・=0,1,0）ではないでしょうか？ それだと理屈上も納得ですし計算もうまくいくのですが。
手取り足取り教えて頂かないとだめなもので、いちいちホント申し訳ありません。

回答No.5

comv

再び　こんばんは！

＞ブランクセルを１と数えてしまうようです。
MATCH()関数ではブランクセルがあると#N/Aとなりますので
たぶんスペースが入力されているセルがあるのでは？

・両方（ブランク　スペース）をカウントしない式

配列数式です

　=SUM(IF(TRIM(A1:A10)="",0,(MATCH(A1:A10,$A$1:$A$10,0)=ROW(A1:A10))*1))

と数式バーに入力後（数式バーにカーソルがある状態で）
　[Ctrl]+[Shift]を押したまま[Enter]　で入力確定

お礼

本当にたびたびありがとうございます。計算式をすべてコピーペーストしたので、スペースが入力されたセルは無いと思います。今回は、no.6の方が教えて下さったやり方で行こうと思うのですが、後学の為に、もしよろしければ教えて下さい。[Ctrl]+[Shift]を押したまま[Enter]　というのは、単にEnterで入力確定するのと、どう違うのでしょうか。やってみたところ、｛｝のカッコが自動的に付いた気がするのですが、それは関係無いでしょうか？

回答No.4

comv

こんにちは！

A1からA10であれば　記載された式でも問題なく
結果が返る筈ですが、皆さんの書かれている通り
項目行を含めているのでは？

ただし範囲が大きいと非常に重くなります。
配列数式の中でCOUNTIFで最終範囲まで配列検索
しますから・・・・

以下の式にすれば

　=SUMPRODUCT((MATCH(A1:A10,$A$1:$A$10,0)=ROW(A1:A10))*1)

MATCH()はヒットしたところで検索を終了しますので
多少は軽くなると思います。

範囲がA2:A1000の場合でも　ROW()内の範囲は必ず
A1から初めて同配列数で終了にして下さい→ A1:A999

　=SUMPRODUCT((MATCH(A2:A1000,$A$2:$A$1000,0)=ROW(A1:A999))*1)

お礼

ニューアイディアをありがとうございます。

やってみましたが、やはり、１多くなってしまいました。

下にも書きましたが、ブランクセルを１と数えてしまうようです。
もー、嫌いっ変なパソコン！ （八つ当たりですね（＾＾;）

回答No.3

nishi6

＞理屈はよく分からないまま．．．

=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))
の意味を考えてみると、これは、

　1/COUNTIF(A1:A10,A1)
　1/COUNTIF(A1:A10,A2)
　1/COUNTIF(A1:A10,A3)
　　　　：
　1/COUNTIF(A1:A10,A10)

の合計でしょう。例えば、SUMPRODUCT(B1:B10)=SUM(B1:B10)が成立するのと同じ理屈です。
配列の積を求めているよりも、配列の和の機能（×１をしてたしている）ですね。

上の式を具体化してみると、６行あったとして、

　ＡＡ　３　1/3
　ＡＡ　３　1/3
　ＡＡ　３　1/3
　ＢＢ　２　1/2
　ＢＢ　２　1/2
　ＣＣ　１　1/1

となります。２つ目が『COUNTIF(A1:A10,A1)』に対応、３つ目が 『1/COUNTIF(A1:A10,A1)』に対応。
３つ目を全部たすと『3』になるわけです。
重複個数を数えて、例えば10個あればその価値を1/10にして、10個たして『1』が出てくるわけです。

そう考えると、算式で誤差がでるとは考えにくいですね。『-1』するよりも、先頭行に『会社名』とかの表題が入っていないでしょうか。その場合は、算式のA1をA2に変えればいいと思います。

参考になった？

お礼

ありがとうございます！

1/COUNTIF(A1:A10,A1)
　1/COUNTIF(A1:A10,A2)
　1/COUNTIF(A1:A10,A3)
　　　　：
　1/COUNTIF(A1:A10,A10)
これを見て、理屈が分かりました！ 感動です。
でも、下にも書きましたが、空欄をカウントしているようなのです・・・。

回答No.2

hiromuy

関数に問題はなさそうですので、同じ会社名でも文字が全角／半角で違っていたり、会社名の最後または途中にスペースが入ってないか確認してみてください。
もし、上記のようなものがあれば、違う内容と判断されてしまいます。

お礼

あっ、昨日お世話になったhiromuyさま、この質問にもお答え頂き、ありがとうございます！
「会社名」は私の場合会社コードで、しかも元データからそのままコピー＆ペーストしているので、間違いないはずなのです。
ただ、下にも書きましたが、空欄を１とカウントしてしまうようなのです。

回答No.1

bin-chan

> =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))
これを実行すると「10」が取得できます。

「会社名が９件なのに」ということならば「タイトル行」を加算してませんか？

補足

ありがとうございます！
タイトル行加算していません。
どうやら、ブランクを１とカウントしてしまうようなのです。
どの列もブランク（計算式は入っているが、その結果ブランクとなっている）のセルがあるので、それで、必ず、１加算されてしまうようなのです。
変ですよね？