Excelで重複を除いてカウントする方法

Excelで大量のデータを扱う際、重複を除いた値や種類をカウントしたい場合があります。

この記事では、Excelで重複を除いてカウントする方法について解説します。

関数を使用した方法のほか、Excelのデータ ツールを使用した方法もご説明しています。

Excelで重複を除いてカウントする方法

Excellで重複を除いてカウントする方法をご紹介します。

SUMPRODUCT関数/COUNTIF関数

SUMPRODUCT関数/COUNTIF関数で重複を除いた数をカウントする方法は、以下のとおりです。

B列「カラー」の重複を除いた数をカウントしたい場合を例にご説明します。

任意のセル（例：D2）に「=SUMPRODUCT(1/COUNTIF(B2:B16,B2:B16))」と入力し、Enterを押します。

数式については、以下の「SUMPRODUCT関数/COUNTIF関数の数式について」セクションで詳しくご説明します。

D2セルに、B列の重複を除いた数をカウントすることができました。

SUMPRODUCT関数/COUNTIF関数の数式について

上記「SUMPRODUCT関数/COUNTIF関数」セクションで使用した「=SUMPRODUCT(1/COUNTIF(B2:B16,B2:B16))」という数式についてご説明します。

「1/COUNTIF(B2:B16,B2:B16)」はSUMPRODUCT関数で「配列数式」となり、セルごとに検索条件は変わります。例えば、B2セルの検索条件値は「レッド」、B4セルの検索条件値は「グリーン」となります。

カウントする範囲も「B2:B16」なので、結果「種類ごとの数」が返ります。さらにその値で1を割っているので分母を「種類ごとの数」にした値になります。

例えば、「B2:B16」に「レッド」は3つあるので、B2セルは「=1/COUNTIF(B2:B16,B2:B16)」で「1/3」を返します。

そして、SUMPRODUCT関数は上記の数式ではカッコ内の値を合計します。SUMPRODUCT関数のカッコ内は、「レッドは1/3が3つ」、「グリーンは1/3が3つ」…という形になります。

レッド同士を足すと「1/3+1/3+1/3＝1」、グリーン同士も足すと「1/3+1/3＝1」です。

つまり、重複分だけ分母が増え、それを合計するので、種類ごとの計はすべて「1」になります。

掛け算した結果が複数あり、それを合計したい場合、SUMPRODUCT関数が便利です。SUM関数でも求めることができますが、SUMPRODUCT関数なら1つの数式で可能です。

下記の記事では、SUMPRODUCT関数を詳しく解説しているほか、「重複しているデータを1件としてカウントする（COUNTIF関数）」セクションでは、COUNTIF関数との組み合わせについてもご紹介しています。

ExcelのSUMPRODUCT関数の使い方｜範囲または配列の積を合計する

COUNTA関数/UNIQUE関数/FILTER関数

COUNTA関数/UNIQUE関数/FILTER関数で重複を除いた数をカウントする方法は、以下のとおりです。

なお、UNIQUE関数はExcel 2021以降またはMicrosoft 365で使用可能です。Excel2019以前のバージョンでは使用できないため、ご注意ください。

B列「カラー」の重複と空白セルを除いた数をカウントしたい場合を例にご説明します。

任意のセル（例：D2）に「=COUNTA(UNIQUE(FILTER(B2:B18,B2:B18<>"")))」と入力し、Enterを押します。

数式については、以下の「COUNTA関数/UNIQUE関数/FILTER関数の数式について」セクションで詳しくご説明します。

D2セルに、B列の重複と空白セルを除いた数をカウントすることができました。

COUNTA関数/UNIQUE関数/FILTER関数の数式について

上記「COUNTA関数/UNIQUE関数/FILTER関数」セクションで使用した「=COUNTA(UNIQUE(FILTER(B2:B18,B2:B18<>"")))」という数式についてご説明します。

「UNIQUE(FILTER(B2:B18,B2:B18<>""」では、UNIQUE関数で範囲を指定し、FILTER関数を用いて空白を無視させます。

UNIQUE関数とは、指定した範囲から重複したデータを取り除いてくれる関数です。下記の記事「UNIQUE関数を使って重複データをまとめる」セクションでは、UNIQUE関数の使い方を詳しくご説明しています。

エクセルで重複データをまとめる方法

FILTER関数は、エクセルのフィルター機能と同じように条件に該当するデータを抽出できる関数です。対象のリストから特定の条件で絞り込んだ結果を表示させることができます。

ExcelのFILTER関数の使い方｜必要なデータを抽出する

「=COUNTA」で、COUNTA関数を使用し、指定した範囲の重複と空白を除いた数をカウントします。

COUNT関数は、数値が入っているセルの個数を数える関数ですが、COUNTA関数は、空白以外のセルの個数を数える関数になります。

ExcelのCOUNTA関数の使い方｜空白ではないセルの個数を返す

データ ツールでカウントする

データ ツールで重複を除いた数をカウントする方法は、以下のとおりです。

①カウントしたい範囲のいずれかのセル（例：A2）、②「データ」タブ、③「重複の削除」の順に選択します。

「重複の削除」ダイアログボックスが表示されます。

①今回の例だとA列の「No.」は通し番号のため、重複のチェックからは外し、②「OK」ボタンを押します。

「〇個の重複する値が見つかり、削除されました。▲個の一意の値が残ります。カウントには空のセルやスペースが含まれる場合があることに注意してください。」というポップアップが表示されるので、「OK」ボタンを押します。

「▲個の一意の値が～」の「▲」が重複を除いた数になります。

重複するデータを削除することができましたが、例のように通し番号を付けていた場合、赤矢印で示す通り番号がずれてしまいます。

データの量が膨大な場合など、重複データの削除後に修正が手間な場合は、以下の対処法をお試しください。

通し番号のセル（例：A3）に「=ROW()-2」と入力し、Enterを押します。

ROW関数は、指定したセル参照の行番号を返します。

そのため、「=ROW()」だけの場合、A3セルは3行目にあるので、ROW関数の値は「3」となります。

通し番号の始まりは「1」にしたいので、「3」を「1」にするため「-2」を付けます。

ExcelのROW関数の使い方｜セルの行番号を求める

①関数を入力したセル（例：A3）を選択します。

②セルの右下にマウスオーバーし、+マークを表示させて下方向にドラッグします。

オートフィルでA列にROW関数を適用したため、重複データの削除をしても通し番号が崩れなくなりました。