엑셀 중복값 제거, 찾기: COUNTIF 함수

엑셀 중복값 제거·찾기: COUNTIF 함수 완전 정복

데이터가 방대해질수록 가장 먼저 부딪히는 문제가 중복값입니다. 고객 목록, 제품 코드, 거래 내역 등에서 중복을 잡아내지 못하면 보고서 오류, 재고 과다 계산, 잘못된 통계 분석 같은 치명적인 실수가 발생할 수 있죠. 이번 포스팅에서는 엑셀 중복값 찾기 함수인 COUNTIF 함수를 중심으로 중복값을 찾고, 엑셀 중복값제거하는 모든 방법을 다룹니다.

최신 Microsoft 365(오피스 2025) 기준으로 설명하지만, Excel 2016 이상이라면 대부분 동일하게 따라 하실 수 있습니다.

---

COUNTIF 함수 기초

COUNTIF 기본 문법

• 구성: =COUNTIF(범위, 조건)
• 설명: 지정한 범위에서 조건에 맞는 셀 개수를 반환합니다.

=COUNTIF(A:A, A2)   // A열 전체에서 A2 값과 동일한 셀 개수를 계산

조건 활용 팁

• 정확히 일치: "=서울"
• 포함 검색: "*팁" (*는 와일드카드)
• 숫자 비교: ">100"

---

중복값 찾기 실전 예제

중복 열별 표시 플래그 만들기

1. 데이터가 있는 A2:A1000 범위를 가정합니다.
2. B2 셀에 다음 수식을 입력 후 아래로 복사합니다.

$$=IF(COUNTIF($A$2:A2, A2)=1, "최초", "중복")$$

3. "중복"이 표시된 행을 필터로 쉽게 골라낼 수 있습니다.

고유값 카운트 vs 중복값 카운트

• 고유값 개수
  • 배열 수식이므로 Ctrl+Shift+Enter(Excel 2019 이하) 또는 Enter(Microsoft 365)로 입력합니다.

$=SUM(1/COUNTIF(A2:A1000, A2:A1000))$

• 중복값 개수

$=ROWS(A2:A1000)-SUM(1/COUNTIF(A2:A1000, A2:A1000))$

---

중복값 제거 방법 총정리

조건부 서식으로 시각화

1. 범위 선택 → 홈 - 조건부 서식 - 새 규칙
2. 수식을 사용하여 서식을 지정 선택 후

$$=COUNTIF($A$2:$A$1000, A2)>1$$

3. 원하는 색상 지정 → 확인.
   • 중복값이 즉시 하이라이트되어 눈으로 파악하기 쉽습니다.

데이터 탭 - 중복 제거 기능

• 경로: 데이터 - 중복 제거
• 특징:
  • 클릭 몇 번으로 중복 행뿐 아니라 다중 열 조합까지 제거.
  • Undo(되돌리기) 가능하므로 실수해도 안전.
• 주의: 원본 데이터를 삭제하므로, 반드시 백업 또는 복사본에서 실행하세요.

UNIQUE 함수 (Microsoft 365 전용)

• 문법: =UNIQUE(배열[, by_col, exactly_once])
• 중복 제거 리스트 생성

  =UNIQUE(A2:A1000)   // 중복 없이 고유 목록 반환

• 정확히 한 번만 등장한 값

  =UNIQUE(A2:A1000,,TRUE)   // 딱 한 번 나온 값만 반환

고급 필터(Advanced Filter)

1. 데이터 복사본을 만들 시 탁월.
2. 데이터 - 고급 선택 → 복사 위치 지정.
3. 고유 레코드만 체크.
   • 다중 열 기준으로 고유 행을 추출할 때 유용.

Power Query로 자동화

• 데이터 - 데이터 가져오기/파워쿼리 편집기
• 홈 - 중복 제거 버튼으로 수천·수만 행도 순식간에 처리.
• 쿼리를 새로 고침만 하면 원본이 바뀌어도 중복 제거가 재실행됩니다.

---

대용량 데이터 최적화 전략

계산 필드 최소화

• COUNTIF가 수천 행에서 반복되면 속도가 저하됩니다.
• 동적 배열 수식 한 줄로 처리하거나, 계산을 Power Query로 이동하여 워크시트 부담을 줄이세요.

인덱스 열 삽입

• 정렬 후 인덱스를 부여하면 중복 제거 후 원본 순서를 보존할 수 있습니다.

VBA 매크로 활용

• 반복 작업이 많다면 RemoveDuplicates 메서드로 자동화 스크립트를 작성해 보세요.

Sub RemoveDup()
    With Worksheets("Sheet1")
        .Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
    End With
End Sub

---

자주 묻는 질문(FAQ)

Q1. COUNTIF가 255자 이상 텍스트를 인식하지 못해요.

• 대안: =SUMPRODUCT(--(EXACT(A2:A1000, A2))) 또는 Power Query 사용.

Q2. 대소문자 구분 중복 검사는?

• EXACT 함수와 SUMPRODUCT를 조합해 구분 가능.

$$=SUMPRODUCT(--EXACT($A$2:$A$1000,A2))$$

Q3. 피벗 테이블로도 중복 확인이 되나요?

• 네, 피벗 테이블 값 영역에 숫자 필드를 넣고 값 필터 - 한 번 이상으로 중복 건수를 집계할 수 있습니다.

---

결론

COUNTIF 함수는 단순히 세는 기능을 넘어, 중복값을 탐지·제거·관리하는 데 가장 기초이자 강력한 도구입니다. 여기에 UNIQUE, 조건부 서식, Power Query까지 결합하면 팀 내 데이터 품질을 획기적으로 개선할 수 있습니다. 실무에서는 한 가지 방법만 고집하기보다, 데이터 규모·업무 흐름·협업 환경에 맞춰 최적의 조합을 선택하세요. 이제 대용량 시트도 두려울 필요 없습니다!

---