[Excel]重複と空白を除いてデータ数をカウントする方法

エクセルでデータの集計をする際、特定の列に記載された文字や値が重複しないデータ数をカウントしたいということはないでしょうか？

わざわざ別のシートに全データをコピペして「重複を削除」してからカウントするのも面倒なので、一発で重複しないデータ数だけカウントしたいですよね。

この記事でわかること

「重複する文字や値、空白を除いたデータ数」を一発でカウントする方法

この記事が役に立つ状況の例

例：取引先への請求内容ごとに一覧化されたデータがあり、重複する請求先と空欄を除いて請求先が何社あるかをカウントしたい。

完成イメージがこちらです。

データリストにはA社,B社,C社,D社,E社,F社の6社が存在するため、結果は「6」と表示されました。

空白はカウント対象から除く処理をしているため、行数が後から追加されたとしても、1,000行目までは重複をカウントせずに請求先の数をカウントすることができるように関数をいれています。

使用した関数は以下の2つです。

この2つの関数を組み合わせることで、

ユニークな行数のみをカウントすることが可能になりました。

=SUMPRODUCT((範囲<>””)/COUNTIF(範囲,範囲&””))

今後データリストに行数が追加される可能性がある場合は、範囲の終わりを広めにとっておくことでいちいちデータの選択範囲を変更することなく集計結果を見ることができます。

上述した完成イメージでも、現状は18行目までしかリストの記載はありませんが、1,000行目までリストが追加されたとしても関数をいじることなく重複抜きでカウントすることができるようになっています。

=SUMPRODUCT((範囲<>””)/COUNTIF(範囲,範囲&””))を分解してみる。

まずこの関数の構造で最も重要なのは、重複する値の数を重複する値の数で割ることで「1」とカウントしているという点です。

ちょっと何言ってるか分からないですよね？

実はこの関数、空白のないデータリストの中から重複を除いてカウントするだけであれば、

=SUMPRODUCT(1/COUNTIF(範囲,範囲))

という関数で足りるのですが、まずはこの関数の意味からご説明させていただきます。

「COUNTIF(範囲,範囲)」では、データリストの中から重複する値の数をカウントしており、「1/COUNTIF(範囲,範囲)」とすることで、1を重複する値の数で割っております。

そして、SUMPRODUCT関数を用いることにより、割った数を重複している行数分かけなおすという処理がされるため、重複している値すべてをひっくるめて「1」と認識してくれています。

そして、SUMPRODUCT関数により、1と認識されたデータが合計され、重複を除いたユニークなデータ数が計算されるというロジックになっています。

今回は、SUMPRODUCT(範囲<>””)とすることで、範囲の中から空白以外のセルを選択するように指定をしています。