이 글의 목적은 엑셀에서 중복 데이터의 개수를 정확하고 빠르게 계산하는 다양한 방법을 정리하여 실무에서 바로 적용할 수 있도록 돕는 것이다.
1. 중복 집계의 기본 개념
중복 데이터 개수 세기는 크게 두 가지로 구분한다.
- 항목별 중복 개수 : 각 값이 몇 번 나타나는지 세는 방법이다.
- 전체 중복 개수 : 동일 값이 2회 이상 등장한 건수나, 순수하게 “중복으로 간주되는 추가 발생분”을 세는 방법이다.
주의 : “중복 항목 수”와 “중복 횟수”의 정의를 먼저 확정해야 한다. 예를 들어 A가 3번 나타나면 항목별 중복 개수는 A:3회이지만, “중복으로 추가된 개수”는 2개로 계산한다.
예시 데이터 가정: A열(A2:A11)에 고객ID 목록이 있다.
| 행 | A(고객ID) |
|---|---|
| 2 | K001 |
| 3 | K002 |
| 4 | K001 |
| 5 | K003 |
| 6 | K002 |
| 7 | K004 |
| 8 | K001 |
| 9 | K005 |
| 10 | K003 |
| 11 | K006 |
이 데이터에서 K001은 3회, K002는 2회, K003은 2회, K004·K005·K006은 1회이다.
2. COUNTIF로 항목별 중복 개수 세기
가장 직관적이며 범용성이 높다.
- B2 셀에 다음 수식을 입력한다.
=COUNTIF($A$2:$A$11, A2) - B2를 아래로 채우기하여 각 고객ID의 등장 횟수를 얻는다.
특정 항목의 중복 수만 따로 확인하려면 다음과 같이 조건만 바꾸면 된다.
=COUNTIF($A$2:$A$11, "K001") 주의 : COUNTIF는 대소문자를 구분하지 않는다. 대소문자까지 구분해야 한다면 EXACT 함수와 배열수식 또는 SUMPRODUCT를 사용한다.
3. UNIQUE와 COUNTIF로 항목별 요약표 만들기(Office 365·Excel 2021 이상)
동적 배열을 지원하는 버전에서는 고유 목록을 먼저 뽑은 뒤 각 빈도수를 붙이는 방식이 가장 빠르다.
- D2 셀에 고유값 목록을 만든다.
=SORT(UNIQUE(A2:A11)) - E2 셀에 각 고유값의 빈도를 계산한다.
=COUNTIF($A$2:$A$11, D2#) D2#은 D2에 의해 spill된 전체 고유 목록을 참조하여 열 단위 결과를 한 번에 반환한다.
4. 전체 중복 건수 계산(“2회 이상” 항목의 개수)
값이 2회 이상 등장한 서로 다른 항목의 개수만 세려면 다음과 같이 한다.
- 동적 배열 버전
=COUNTIF(COUNTIF(A2:A11, UNIQUE(A2:A11)) , ">=2") - 호환 버전(보조열 B에 개수 산출 후)
=SUM(--(FREQUENCY(IF(B2:B11>=2, MATCH(A2:A11, A2:A11, 0)), MATCH(A2:A11, A2:A11, 0))>0)) 위 FREQUENCY 수식은 배열수식이다. 구버전에서는 Ctrl+Shift+Enter로 확정한다.
5. “중복으로 추가된 개수” 계산
총 행 수에서 서로 다른 고유 항목 수를 빼면 중복으로 추가된 건수를 얻는다. 예시에서 총 10행(데이터)이고 고유 항목 수가 6이면, 중복으로 추가된 개수는 4이다.
- 동적 배열 버전
=ROWS(A2:A11) - ROWS(UNIQUE(A2:A11)) - 호환 버전
=ROWS(A2:A11) - SUM(1/COUNTIF(A2:A11, A2:A11)) 주의 : 호환 버전 수식은 배열수식이다. 구버전에서는 Ctrl+Shift+Enter로 확정한다.
6. 다중 열 기준으로 완전 동일 행의 중복 개수 세기
예를 들어 A열=고객ID, B열=상품코드, C열=일자 조합이 같은 행의 중복을 판정하려면 키를 결합하여 COUNTIFS로 센다.
=COUNTIFS($A$2:$A$11, A2, $B$2:$B$11, B2, $C$2:$C$11, C2) 또는 결합키를 보조열에 만든 뒤 COUNTIF로 처리한다.
보조열 D2: =A2&"|"&B2&"|"&TEXT(C2,"yyyymmdd") E2: =COUNTIF($D$2:$D$11, D2) 7. SUMPRODUCT로 조건 조합과 대소문자 구분 처리
SUMPRODUCT는 배열 계산을 자동으로 수행하므로 강력하다.
- 특정 값의 개수(대소문자 구분 포함)
=SUMPRODUCT(--(EXACT(A2:A11, "K001"))) - 두 조건 이상 동시 충족 건수
=SUMPRODUCT(--(A2:A11="K001"), --(B2:B11="P100")) EXACT는 대소문자를 구분하여 완전 일치만 TRUE로 반환한다.
8. 피벗 테이블로 중복 빈도 분포표 만들기
피벗은 대량 데이터를 빠르게 요약한다.
- 원본 범위를 표로 변환한다. 삽입 → 표 또는 Ctrl+T를 사용한다.
- 삽입 → 피벗 테이블을 선택하고 “새 워크시트”를 지정한다.
- 행 레이블에 고객ID를 배치한다.
- 값 영역에 고객ID를 다시 배치하고 “값 필드 설정 → 개수”로 바꾼다.
- 필요하면 정렬과 상위 필터를 적용한다.
피벗 결과로 항목별 등장 횟수를 한 번에 얻을 수 있다.
9. Power Query로 완전 중복 행 집계(대량 데이터 권장)
Power Query는 데이터 준비 단계에서 중복을 집계하고 내보내는 데 유용하다.
- 데이터 탭 → 데이터 가져오기 → 테이블/범위에서를 선택한다.
- 쿼리 편집기에서 중복 판단에 사용할 열들을 모두 선택한다.
- 홈 탭 → 그룹화 버튼을 클릭한다.
- 그룹화 창에서 “고급”을 선택하고 그룹화 기준 열을 설정한다.
- 새 열 이름을 “개수”로 하고 연산은 “행 개수”를 선택한다.
- 닫기 및 로드로 결과를 워크시트에 반환한다.
이 방법은 다중 열 완전 일치 기준과 대용량 데이터 처리에 안정적이다.
10. 조건부 서식과 조합하여 중복 위치 하이라이트
중복 개수 집계와 함께 위치 표시가 필요하면 조건부 서식을 병행한다.
- 범위를 선택한다.
- 홈 탭 → 조건부 서식 → 셀 강조 규칙 → 중복 값을 선택한다.
- 서식 스타일을 지정하여 시각적으로 표시한다.
이후 COUNTIF·피벗과 함께 사용하면 분석과 정리가 동시에 가능하다.
11. 텍스트 정규화로 “위장 중복” 교정
공백, 보이지 않는 문자, 형식 차이로 인해 같은 값이 다른 값처럼 보이는 문제가 있다. 집계 전 정규화를 권장한다.
| 문제 | 원인 | 정규화 방법 |
|---|---|---|
| 앞뒤 공백 | 수작업 입력 | =TRIM(A2)로 보조열 생성 후 값 붙여넣기 |
| 중복 공백 | 복사·붙여넣기 | =TEXTJOIN(" ",TRUE,FILTERXML("<t>"&SUBSTITUTE(TRIM(A2)," ","</t><t>")&"</t>","//t")) |
| 대소문자 혼재 | 케이스 불일치 | =UPPER(A2) 또는 =LOWER(A2)로 통일 |
| 숨은 문자 | 웹 스크래핑 | =CLEAN(A2)로 제어문자 제거 |
| 날짜 서식 차이 | 텍스트/날짜 혼재 | =DATEVALUE(A2)로 날짜화 후 표시 형식 통일 |
주의 : 정규화 보조열을 만든 뒤 “값 붙여넣기”로 원본을 치환해야 COUNTIF·피벗 결과가 안정적으로 일치한다.
12. 고급: 중복 순번(RANK)과 첫 발생·다음 발생 구분
중복의 첫 등장과 이후 등장을 구분하면 손실 데이터 제거나 최신값 유지에 유용하다.
- 행별 중복 순번
=COUNTIF($A$2:A2, A2) 해석: 현재 행까지의 범위에서 A2의 누적 발생횟수를 반환한다. 1이면 첫 등장, 2 이상이면 중복 등장이다.
- 첫 등장만 플래그
=--(COUNTIF($A$2:A2, A2)=1) - 중복 등장만 플래그
=--(COUNTIF($A$2:A2, A2)>1) 13. 고급: “정확히 N회 등장”하는 항목 개수
예를 들어 정확히 3번 나타나는 고객ID의 개수를 세려면 다음과 같이 한다.
- 동적 배열 버전
=COUNTIF(COUNTIF(A2:A11, UNIQUE(A2:A11)), 3) - 호환 버전(보조열 B에 등장 횟수 산출 후)
=SUM(--(FREQUENCY(IF(B2:B11=3, MATCH(A2:A11, A2:A11, 0)), MATCH(A2:A11, A2:A11, 0))>0)) 14. 응용: 월별·그룹별 중복 개수(피벗·SUMPRODUCT)
날짜가 포함된 데이터에서 월별로 특정 값의 중복 개수를 세려면 보조열로 “연월”을 만든 뒤 피벗 또는 SUMPRODUCT를 쓴다.
보조열 연월: =TEXT(C2,"yyyy-mm") 월별·특정고객 건수: =SUMPRODUCT(--(A2:A1000="K001"), --(TEXT(C2:C1000,"yyyy-mm")="2025-09")) 피벗에서는 행에 연월, 열에 고객ID, 값에 개수를 배치해 교차표를 만든다.
15. 대소문자 구분 전체 빈도표(EXACT+SUMPRODUCT+UNIQUE)
케이스 센서티브 요약표가 필요하면 다음 수식을 쓴다.
=LET( u, UNIQUE(A2:A11), MAP(u, LAMBDA(x, SUMPRODUCT(--(EXACT(A2:A11, x))))) ) 위 수식은 동적 배열 버전에서 u에 고유값을 담고 각 값에 대해 EXACT 기반 합계를 반환한다. 반환 범위를 옆열에 배치하려면 다음과 같이 한다.
=HSTACK(UNIQUE(A2:A11), LET(u, UNIQUE(A2:A11), MAP(u, LAMBDA(x, SUMPRODUCT(--(EXACT(A2:A11, x))))))) 16. 숫자·텍스트 혼합 열의 중복 오판 방지
예를 들어 “00123”과 123이 혼재하면 동일성 판정이 엇갈린다. 입력 형식을 통일한 뒤 계산해야 한다.
- 모든 값을 텍스트로:
=TEXT(A2,"0")또는 자릿수 고정 서식을 적용한다. - 모든 값을 숫자로:
=--A2또는 데이터 → 텍스트 나누기 마법사로 숫자 변환한다.
주의 : 서식만 바꾸면 내부 데이터형은 그대로일 수 있다. 값 자체를 변환해야 COUNTIF·피벗 결과가 일관된다.
17. 에러 값이 섞인 경우의 중복 개수 세기
에러를 제외하고 중복을 세려면 IFERROR로 우회값을 지정한다.
=COUNTIF(IFERROR($A$2:$A$11,""), A2) 배열수식으로 입력하거나, 사전 정규화로 에러를 제거한 뒤 집계한다.
18. 고유 항목과 중복 항목만 분리 추출
고유값만, 또는 중복값만 따로 목록화할 때는 FILTER와 COUNTIF를 조합한다.
- 중복값만
=SORT(UNIQUE(FILTER(A2:A11, COUNTIF(A2:A11, A2:A11)>1))) - 한 번만 등장하는 값만
=SORT(UNIQUE(FILTER(A2:A11, COUNTIF(A2:A11, A2:A11)=1))) 19. 성능 최적화 팁(대용량 데이터)
- 원본을 표(Table)로 변환하여 구조적 참조를 사용하면 범위 관리가 단순해진다.
- 가능하면 피벗이나 Power Query로 빈도 요약을 먼저 만든 뒤 작업한다.
- 동적 배열을 사용할 때
UNIQUE·SORT로 집합을 축소한 후 COUNTIF를 적용한다. - 수식 복잡도가 높을수록 보조열로 분해하여 재활용한다.
- 반복 계산을 줄이기 위해 이름 정의와 LET을 활용한다.
20. 실전 레시피 모음
| 요구사항 | 권장 수식/도구 | 비고 |
|---|---|---|
| 항목별 등장 횟수 | =COUNTIF($A$2:$A$100000, A2) | 범용, 간단하다 |
| 고유 목록과 빈도 요약 | =HSTACK(SORT(UNIQUE(A2:A100000)), COUNTIF(A2:A100000, SORT(UNIQUE(A2:A100000)))) | 동적 배열 권장 |
| 정확히 N회 등장 항목 수 | =COUNTIF(COUNTIF(A2:A100000, UNIQUE(A2:A100000)), N) | 배열 계산 자동 |
| 중복으로 추가된 개수 | =ROWS(A2:A100000) - ROWS(UNIQUE(A2:A100000)) | 정의 명확히 할 것 |
| 다중 열 완전 일치 빈도 | =COUNTIFS(A:A, A2, B:B, B2, C:C, C2) | 결합키 대안 있음 |
| 대소문자 구분 빈도 | =SUMPRODUCT(--(EXACT(A2:A100000, "코드"))) | EXACT 사용 |
| 월별·그룹별 빈도 | 피벗 테이블 또는 SUMPRODUCT | 연월 보조열 |
| 중복만 필터링 | =FILTER(A2:A100000, COUNTIF(A2:A100000, A2:A100000)>1) | 동적 배열 |
21. 오류 대처 체크리스트
- #N/A 발생 시: 참조 범위가 일치하는지 확인한다.
- #VALUE! 발생 시: 텍스트·숫자 형식 혼재 여부를 점검한다.
- #SPILL! 발생 시: spill될 영역에 값이 있는지 비워준다.
- #NAME? 발생 시: 함수명을 잘못 입력했는지 또는 버전 미지원인지 확인한다.
- 계산 지연 시: 범위를 열 전체 대신 정확 범위로 한정하고, 보조열로 분해한다.
22. 실무 예제: 고객 중복 발생 보고서 자동화
- 원본 A열에 고객ID를 둔다.
- D2에 고유 목록을 만든다.
=SORT(UNIQUE(A2:INDEX(A:A, MATCH("zzz", A:A)))) - E2에 빈도 계산을 입력한다.
=COUNTIF(A:A, D2#) - F2에 중복 여부 플래그를 만든다.
=IF(E2#>1, "중복", "고유") - 필요 시 G2에 “중복으로 추가된 개수”를 산출한다.
=SUM(E2#-1*(E2#>=1)) 위 레이아웃으로 보고서가 자동 갱신되며, 새 데이터가 추가되어도 즉시 반영된다.
23. VBA로 초대용량 중복 집계 가속(선택)
수백만 행에서 수식이 느릴 경우 Dictionary를 사용하면 빠르다.
Option Explicit Sub CountDuplicatesFast() Dim dict As Object, rng As Range, c As Range Set dict = CreateObject("Scripting.Dictionary") Set rng = Range("A2", Cells(Rows.Count, "A").End(xlUp)) Application.ScreenUpdating = False For Each c In rng If Len(c.Value) > 0 Then dict(c.Value) = dict(c.Value) + 1 End If Next c '결과 출력 Dim i As Long: i = 2 Dim k For Each k In dict.Keys Cells(i, "D").Value = k Cells(i, "E").Value = dict(k) i = i + 1 Next k Application.ScreenUpdating = True End Sub 주의 : 매크로 사용 전 신뢰할 수 있는 위치에 파일을 저장하고, 서명 정책을 준수한다.
24. 제거가 아닌 “세기”만 할 때의 주의점
- 데이터 → 중복된 항목 제거 기능은 삭제이다. 집계 목적이라면 원본을 복사한 뒤 사용한다.
- 중복 집계 결과와 정제 결과를 분리 저장한다.
- Power Query에서는 “중복 제거” 대신 “그룹화 → 행 개수”로 세는 절차를 선택한다.
25. 최종 점검: 선택 가이드
상황에 따라 가장 단순하고 유지보수 가능한 방식을 고른다.
- 소량·단일 열 : COUNTIF가 최적이다.
- 최신 Excel : UNIQUE+COUNTIF 조합이 최적이다.
- 다중 열 완전 일치 : COUNTIFS 또는 Power Query 그룹화가 적합하다.
- 케이스 구분 : EXACT+SUMPRODUCT를 사용한다.
- 대용량 : 피벗 또는 Power Query를 우선 고려한다.
FAQ
중복으로 추가된 개수와 2회 이상 항목 수의 차이는 무엇인가?
중복으로 추가된 개수는 총행수에서 고유항목수를 뺀 값이며, 2회 이상 항목 수는 빈도가 2 이상인 서로 다른 값의 개수이다.
COUNTIF가 느릴 때 대안은 무엇인가?
피벗 테이블로 빈도 요약을 만들거나 Power Query 그룹화를 사용한다. 동적 배열이 가능하면 UNIQUE로 집합을 먼저 줄인다.
공백이나 보이지 않는 문자로 중복이 안 잡힌다. 어떻게 하나?
TRIM·CLEAN으로 정규화한 보조열을 만든 뒤 그 열을 대상으로 COUNTIF를 수행한다.
대소문자 구분이 필요한데 COUNTIF로 가능한가?
COUNTIF는 대소문자를 구분하지 않는다. EXACT와 SUMPRODUCT를 함께 사용한다.
날짜 기준으로 월별 중복을 집계하려면?
연월 보조열(TEXT(날짜,"yyyy-mm"))을 만든 뒤 피벗 행에 배치하거나 SUMPRODUCT로 조건을 결합한다.