ユニークな個数をカウントする方法

Pocket

Excelで個数をカウントするときはCOUNTを使います。
では重複なしで個数をカウントするときはどうすればいいでしょうか。

例えばA列にa,b,c,a,a,bと入力されていたとします。

普通にカウントすると6になります。
一方、重複なしでカウントすると3になります。
これをExcelの関数を使って求めるには次のようにします。

=SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6))

「なんじゃこれ」としか言えませんが、計算結果は「3」となり、正しいです。

仕組みについて丁寧に説明したいと思います。

COUNTIF(A1:A6,A1:A6)の部分

A列にa,b,c,a,a,bと入力します。
B1に「=COUNTIF(A1:A6,A1:A6)」と入力します。
計算結果は「3」となります。

ここが分かりにくいです。
一般にCOUNTIFは「=COUNTIF(A1:A6,A1)」のように範囲とセルを指定します。
しかしここでは範囲と範囲を指定しています。
このように、セルで指定する代わりに範囲を指定した場合、計算結果は配列になります。

次のようにすると分かります。
「=COUNTIF(A1:A6,A1:A6)」と入力した後、エンターを押します。
セルの内容が「{=COUNTIF(A1:A6,A1:A6)}」に変わります。
そしてB列が3,2,1,3,3,2となります。
このような使い方を配列数式といいます。
引数を値(セル)で与える代わりに配列(範囲)で与え、同じような計算を一気に行う仕組みです。

この場合は
「aであるセルが3個」「bであるセルが2個」…
という計算を一気にやっています。
先程、「計算結果は「3」となります。」と書きましたが、これは配列の先頭の要素が表示されていたことになります。

1/COUNTIF(A1:A6,A1:A6)の部分

「=COUNTIF(A1:A6,A1:A6)」の結果は
3,2,1,3,3,2
になります。

次に「=COUNTIF(A1:A6,A1:A6)」の結果は
1/3,1/2,1/1,1/3,1/3,1/2
になります。
1/3が3個、1/2が2個、1/1が1個となっていることに注意してください。

SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6))の部分

SUMPRODUCTは範囲と範囲を指定しそれぞれのペアの積を算出し、合計します。
引数として配列を一つだけ与えた場合は、単に配列の要素の合計を計算します。

今回のケースは配列を一つだけ与えたケースなので要素の合計、つまり
1/3,1/2,1/1,1/3,1/3,1/2
の合計なので3になります。

[ 2021年4月12日 | カテゴリー: Excel | タグ: ]

« | »

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

送信してください。


タグ

カテゴリー

最近の投稿

最近のコメント

固定ページ

アーカイブ

stabucky

写真

メタ情報