- 公開日:
- 更新日:
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関数を利用すると短い関数式で、重複しているデータを1件としてカウントすることができます。
数式バーに『=SUMPRODUCT(1/COUNTIF(A2:A16,A2:A16))』と入力すると「7」が算出されました。
オートフィルターで重複データを除いて求める方法
数式バーに『=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関数は範囲内の、空白でないセルの個数を返します
2行目~16行目まで、全部で15行あります。
1行目にフィルターをかけます。【ホーム】タブの【並び替えとフィルター】を選択し、【フィルター】を選択してください。
「データの個数」のプルダウンを選択し、データの個数が1だったものだけ絞り込みたいので、【1】のみをチェックし【OK】ボタンを押します。
データの個数が1のみ絞り込まれました。1はどの野菜の種類にも必ず含まれている数字であり、かつ「2以降は重複データ」となります。
重複データを取り除くことにより、野菜の種類数を求めることができます。