一行におなじ数がいくつあるか数えたい

質問

たとえば、1000万件ほどのデータがあるとします。
そのなかで、おなじ数がいくつかあるはずなのですが行のソートを行わずにそのままの形でいくつユニークな数があるか数えたい場合

=SUMPRODUCT(1/COUNTIF(*:*,*,*))

と入力すれば数えられると学びました。
使ったら確かにそれらしい数が出るのですが、これはいったいどういう
関数でどのような操作をしているのか教えてください。

回答No.1

cistronezk · ベストアンサー

やっていることは以下のようなことを一つの式にまとめたものです。
例）
A列にデータ、B列に「1/COUNTIF($A$1:$A$5,A1)」という式。（判り易くB列は分数表示にしました。）
　　A　　B
１　あ　1/2
２　あ　1/2　
３　い　1/3
４　い　1/3
５　い　1/3
このB列の合計である「=SUM($B$1:$B5)」が重複しない項目数です。

例から判るように、項目Xがｎ個あれば、その項目に対応するB列は1/nになります。それがｎ個ですから１になります。つまり項目毎の合計は１になるので、B列の合計が重複しない項目数となります。

作業列を使うことなく一つの式で表わすためには以下のようにします。
「1/COUNTIF($A$1:$A$5,A1)」の代わりに「1/COUNTIF($A$1:$A$5,$A$1:$A$5)」とし、配列の計算をします。F９で確認すると
「1/COUNTIF($A$1:$A$5,A1)」の計算結果は「0.5」ですが、「1/COUNTIF($A$1:$A$5,$A$1:$A$5)」の計算結果は
{0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333}
となります。これはB列と同じ内容です。
そして「SUM」の代わりに配列の合計が出来る「SUMPRODUCT」を使うことでひとつの式で表現できます。

お礼

とても参考になるご回答ありがとうございました。
内容を教えていただいても、とても私にはこのような式を考える能力がないのですが、改めて教えてくださった方を尊敬しました。