• 公開日:
  • 更新日:

Excelで重複しているデータを1件としてカウントする方法

重複しているデータを1件としてカウントする方法をお伝えします。重複データを1件と換算すると、「種類の数」を求めることができます。

例えば、「トマト」「トマト」「きゅうり」「きゅうり」「とうもろこし」という5つのデータがあったとしたら、3という数字を得る方法です。

ここでは逆数を求めて合計する方法と、オートフィルターで重複データを除く方法をお伝えします。

逆数の合計値で求める方法

手順で使用するフォーマットのダウンロードはこちら

COUNTIF関数と逆算の合計を用いて算出

重複しているデータを用意

いくつか重複しているデータを用意します。

データの個数を求める

COUNTIF関数を用いて、範囲(A2~A16)に入っている野菜データの中から、それぞれの野菜の個数(セルの個数)を返します。

B2セルの数式バーに『=COUNTIF(A$2:A$16,A2)』と入力して、トマトの合計数を出力しました。

他の行に反映

ドラッグして他の行にも反映します。

逆数を求める

C2セルに逆数を求めていきます。逆数とは、その数と掛け合わせて1になる数のことです。

数式バーに『=1/B2』と入力して、C2セルに逆数を算出しました。

他の行に反映

ドラッグして他の行にも反映します。

逆数の合計を求める

逆数を全て合計すると、「種類の数」を求めることができます。

数式バーに『=SUM(C2:C16)』と入力して、逆算の合計値「7」を算出しました。なので7種類の野菜「トマト」「なす」「きゅうり」「ピーマン」「ねぎ」「かぼちゃ」「とうもろこし」のデータが存在していることとなります。

SUMPRODUCT関数を使って一度に求める方法

SUMPRODUCT関数を利用

SUMPRODUCT関数を利用すると短い関数式で、重複しているデータを1件としてカウントすることができます。

数式バーに『=SUMPRODUCT(1/COUNTIF(A2:A16,A2:A16))』と入力すると「7」が算出されました。

オートフィルターで重複データを除いて求める方法

COUNTIF関数の利用

数式バーに『=COUNTIF($A$2:A2,A2)』と入力してA2セルのトマトの数を数えています(1つしかデータが無いので1と出力されます)。

範囲の$A$2(絶対参照):A2(相対参照)にすると、下の行に関数式をコピーするに従って、COUNTIF関数の範囲が1行ずつ拡大されます。

他の行に反映

ドラッグして他の行にも反映します。

それぞれの野菜の個数分、数字がカウントアップされていきます。例えばきゅうりは全部で4つあるので、3行目(1)→9行目(2)→14行目(3)→15行目(4)となります。

SUBTOTAL関数の利用-COUNTA

後にフィルターで絞り込むので、SUBTOTAL関数を利用します。データが入っている行数を知りたいので、集計方法をCOUNTAにします。
※SUBTOTAL関数はリストまたはデータベースの集計値を返します
※COUNTA関数は範囲内の、空白でないセルの個数を返します

SUBTOTAL関数の結果

2行目~16行目まで、全部で15行あります。

フィルターの選択

1行目にフィルターをかけます。【ホーム】タブの【並び替えとフィルター】を選択し、【フィルター】を選択してください。

フィルターの絞り込み

「データの個数」のプルダウンを選択し、データの個数が1だったものだけ絞り込みたいので、【1】のみをチェックし【OK】ボタンを押します。

絞り込み結果

データの個数が1のみ絞り込まれました。1はどの野菜の種類にも必ず含まれている数字であり、かつ「2以降は重複データ」となります。

重複データを取り除くことにより、野菜の種類数を求めることができます。