엑셀에서 중복 데이터 개수 세는 방법

본 글은 대용량 데이터에서 중복 레코드의 개수를 정확하고 신속하게 산출하기 위한 엑셀 기법을 총망라하여 제공함으로써, 실무자가 데이터 정제와 보고서 작성 시간을 최소화하는 것을 목표로 하다.

중복 데이터 개수를 세어야 하는 실무 시나리오

재고 SKU, 고객 ID, 거래번호처럼 고유값이 필수인 열에서 중복 여부를 파악하는 일은 전산·물류·재무 전 분야에서 빈번하게 발생한다. 중복 갯수 산정이 요구되는 대표적인 상황은 다음과 같다.

• ERP 마스터 정비 : 상품 코드 중복으로 인한 재고 불일치 예방.
• 매출 리포트 검증 : 동일 거래가 두 번 집계되지 않았는지 점검.
• 이메일·전화번호 중복 제거 : 마케팅 대상 리스트 클린징.
• 품질 관리(불량 Lot) : 생산 일자·Lot 기준 중복 건수 분석.

중복 개수 산출을 위한 6가지 핵심 방법

방법	주요 수식·도구	특징	권장 시나리오
COUNTIF 단일 기준	=COUNTIF(A:A,A2)	한 열 기준, 즉시 결과 확인	고객번호, 제품코드 중복 파악
COUNTIFS 복합 기준	=COUNTIFS(A:A,A2,B:B,B2)	두 열 이상 조합 비교	날짜+품목, 지역+ID 중복
피벗테이블 값 개수	행: 기준 열, 값: 개수	대용량 처리·보고서 형태	월간 중복 거래 통계
고급 필터 + SUBTOTAL	고급 필터로 고유값 추출 후 =ROWS()	파일 용량 감소, 수식 최소	고유 고객 수 계산
Power Query 그룹화	행 그룹화 → 행 개수	수십만 행도 빠르게 처리	자동화·정기 보고
VBA 딕셔너리	Scripting.Dictionary	초고속, 다중 워크시트 지원	매일 반복 대량 배치 작업

방법별 상세 가이드

1. COUNTIF로 단일 열 중복 횟수 구하기

기준 열이 A라면 B열에 아래 수식을 입력하여 각 값이 몇 번 등장했는지 확인할 수 있다.

=COUNTIF($A:$A,$A2)

필터를 사용해 1을 제외한 행을 남기면 중복 행만 추려낼 수 있다. Ctrl + Shift + L로 필터 후 조건 2 이상을 선택하면 된다.

2. COUNTIFS로 복합 기준 중복 세기

날짜·제품코드 두 열이 모두 같을 때를 중복으로 정의한다면 다음과 같이 작성한다.

=COUNTIFS($A:$A,$A2,$B:$B,$B2)

중복 건수가 1 초과인 행만 서식 강조 규칙으로 색칠하여 시각적으로 표시할 수도 있다.

3. 피벗테이블로 중복 건수 요약

1. 데이터 범위를 선택하고 Alt + N + V로 피벗테이블을 삽입한다.
2. 행 레이블에 기준 열을 배치한다.
3. 값 영역에 같은 열을 끌어오고 집계 방식을 “개수(Count)”로 변경한다.

개수가 2 이상인 항목만 슬라이서를 통해 필터링하면 중복 값 목록과 빈도수를 한눈에 파악할 수 있다.

4. 고급 필터와 SUBTOTAL을 이용한 고유건·중복건 계산

데이터 탭 → 고급을 클릭하고 “고유 기록만” 옵션을 체크해 별도 범위에 결과를 복사한다. 이어서 =ROWS() 함수로 고유 건수를 구한 뒤 전체 건수에서 빼면 중복 건수를 얻는다.

중복건수 = 전체행수 - 고유행수

5. Power Query 그룹화로 대용량 중복 세기

10만 행 이상에서도 수 초 만에 결과를 얻을 수 있는 방법이다.

1. 데이터 → 데이터 가져오기 및 변환 → 표/범위에서.
2. Power Query 편집기에서 홈 → 그룹화를 선택.
3. 그룹 기준에 중복 판단 열(또는 열 조합)을 지정하고, 새 열에 행 개수(Count Rows)를 선택.
4. 로드를 완료하면 원본 옆에 중복 빈도가 매핑된 새 테이블이 나타난다.

6. VBA 딕셔너리로 초고속 집계

' Sub CountDuplicates()
Sub CountDuplicates()
    Dim dict As Object, rng As Range, v As Variant
    Set dict = CreateObject("Scripting.Dictionary")
    Set rng  = Range("A2", Cells(Rows.Count, "A").End(xlUp))
    For Each v In rng.Value
        If dict.exists(v) Then
            dict(v) = dict(v) + 1
        Else
            dict.Add v, 1
        End If
    Next
    '결과 출력
    Sheet2.Range("A2").Resize(dict.Count, 2).Value = _
        Application.Transpose(Array(dict.Keys, dict.Items))
    MsgBox "중복 개수 계산 완료", vbInformation
End Sub

실행 후 Sheet2에 고유값과 개수가 2열 형태로 정리되어 보고서에 바로 활용할 수 있다.

실무 예시: 고객 목록 중복 정리 프로세스

국내 유통사 C사는 CRM에서 추출한 25만 행 고객 CSV에서 이메일·전화번호 이중 등록을 제거해야 했다.

1. 사전 준비 : CSV를 Power Query로 로드하고 열 유형을 텍스트로 고정하였다.
2. 그룹화 적용 : 이메일 열 기준 행 개수를 구해 2개 이상 그룹만 필터링하였다.
3. 보고서 작성 : 중복 횟수별 건수를 피벗으로 요약 후 경영진에게 리포트하였다.
4. 결과 : 중복 이메일 4,812건을 삭제하여 메일 발송 비용 15 % 절감 효과를 거두었다.

자주 발생하는 실수와 예방책

• 빈 셀 포함 범위 : COUNTIF는 빈 셀에 대해 0이 아닌 1을 반환하는 오류가 발생하므로 <> 조건으로 제외한다.
• 숫자·텍스트 혼합 : 숫자 00123과 텍스트 "00123"은 COUNTIF 기준이 다르므로 TEXT()나 VALUE()로 형식을 통일한다.
• 피벗 새로 고침 누락 : 원본이 갱신된 뒤 피벗을 갱신하지 않으면 개수가 불일치한다. Alt + F5로 강제 새로 고침한다.
• Power Query 단계 순서 : 그룹화 후 열 삭제 단계가 선행되면 참조 오류가 발생한다. 단계 순서를 확인한다.

TIP : 중복건수 +1은 실제 중복 횟수를 의미하지 않는다. 예를 들어 COUNTIF 결과가 3이면 “2건 중복”이므로, 중복 건수만 필요하면 =COUNTIF()-1을 사용한다.

FAQ

• 질문 1: COUNTIF를 사용하면 속도가 느려지는 이유는?
  답변: 동일 열을 반복 계산하기 때문이다. =UNIQUE()와 =COUNTIF()를 조합하거나 피벗·Power Query로 대체하면 속도가 빨라진다.
• 질문 2: 여러 시트에서 중복을 한 번에 찾으려면?
  답변: VBA 딕셔너리, Power Query Append 기능, 또는 365 함수 =LET()·=SCAN()으로 통합 후 COUNTIF를 적용한다.
• 질문 3: 중복 행을 아예 삭제하고 싶으면?
  답변: 데이터 탭 → 중복 제거가 가장 빠르다. 원본 보존이 필요하면 복사본에서 실행한다.
• 질문 4: COUNTIFS 조건이 3개 이상이면 속도는?
  답변: 열 개수보다 행 개수 증가에 더 민감하다. 10만 행 이상이라면 Power Query 그룹화가 유리하다.
• 질문 5: Office 웹버전에서도 VBA 방식이 가능한가?
  답변: 불가능하다. 대신 Office Script 또는 Power Automate를 통해 동일 로직을 구현한다.