이 글의 목적은 엑셀에서 ‘중복 항목 제거’가 예상대로 동작하지 않을 때 발생 원인을 체계적으로 진단하고, 실무에서 바로 적용할 수 있는 해결 절차와 대안을 단계별로 제공하는 것이다.
1. 중복 제거 동작 원리와 성공 조건 이해
중복 제거는 사용자가 지정한 열 조합을 기준으로 동일한 값이 반복되는 행을 찾아 첫 번째 항목만 남기고 나머지를 삭제하는 기능이다. 비교는 셀의 표시값이 아니라 실제 저장 값(값·형식·숫자/텍스트 구분)에 의해 좌우되므로 데이터 정규화가 선행되어야 한다. 또한 범위가 머지(병합) 없이 직사각형으로 연속되어 있어야 하며, 표 객체(테이블) 상태에서는 표 전체 또는 선택 열을 기준으로 수행된다.
주의 : 중복 제거는 ‘비교 기준’에 포함되지 않은 열의 차이는 무시한다. 예를 들어 이름과 전화번호 두 열을 기준으로 중복을 제거하면 주소 열이 달라도 같은 행으로 판단한다.
2. 자주 발생하는 증상과 즉시 점검 항목
| 증상 | 주요 원인 | 즉시 조치 |
|---|---|---|
| 중복이 명확히 보이는데 제거 결과가 “0개”로 나온다 | 숫자와 텍스트 혼재, 숨은 공백·개행, 유니코드 비가시 문자, 앞에 작은따옴표 | 데이터 형식 통일, TRIM/CLEAN/SUBSTITUTE로 정리, 유니코드 공백 제거, 값으로 붙여넣기 |
| 일부만 제거되고 나머지는 남아 있다 | 비교 열 선택 오류, 범위 일부만 선택, 필터로 숨긴 행 존재, 병합 셀 포함 | 전체 표 선택 후 기준 열 재지정, 필터 해제, 병합 해제 후 재시도 |
| 버튼 클릭이 비활성이다 | 공유 통합 문서, 보호 시트, 외부 연결 편집 제한, 매크로 제한 정책 | 시트 보호 해제, 공유 모드 해제, 편집 권한 확보 |
| 보이는 값은 같지만 다른 값으로 인식된다 | 표시 형식에 의한 시각적 동일, 소수점 이진 반올림 오차, 텍스트 서식 숫자 | 반올림 고정, 값 반올림 처리, 숫자 변환, 정밀 비교 보조열 추가 |
| 특정 열만 중복 제거가 안 된다 | 유니코드 정규화 차이(NFC/NFKC), 전각/반각 차이, NBSP(160) 공백 | 정규화 함수 또는 치환 스크립트 적용, 전각→반각 변환, NBSP 제거 |
3. 원인별 정밀 진단 체크리스트
3.1 공백·개행·비가시 문자
데이터 끝·처음의 공백, 줄바꿈(CHAR(10)), 유니코드 NBSP(160), 얇은 공백(8239) 등은 화면에서 구별하기 어렵지만 값 비교에는 영향을 준다.
=LET(x,A2, SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( TRIM(CLEAN(x)), CHAR(160),""), UNICHAR(8239),""), UNICHAR(8203),""))위 수식은 일반 공백과 NBSP·얇은 공백·제로폭 공간을 제거한다. 대량 처리 시 보조열에 적용 후 값으로 붙여넣기한다.
주의 : TRIM은 연속 공백을 하나로 줄이고 양끝 공백을 제거하지만 NBSP와 제로폭 문자는 제거하지 못한다.
3.2 숫자·텍스트 혼재
같은 모양의 “12345”가 숫자와 텍스트로 혼재하면 서로 다른 값으로 취급한다. 다음 절차로 통일한다.
- 텍스트 → 숫자: 1을 곱하거나 VALUE 함수를 사용한다.
=--A2 =VALUE(A2)- 숫자 → 텍스트: TEXT로 자릿수·형식을 고정한다.
=TEXT(A2,"0") ' 선행 0 유지 =TEXT(A2,"000000") ' 고정 길이 ID주의 : 선행 0이 필요한 ID는 셀 서식 대신 텍스트 또는 TEXT 함수로 고정해야 한다.
3.3 표시 형식과 실값의 불일치
날짜·시간·통화 서식은 같은 모양으로 보이게 하지만 실값은 다를 수 있다. 예를 들어 2025-10-25 09:00와 2025-10-25 09:00:00.500은 표시상 동일해도 서로 다르다. 비교 전 반올림·자르기를 적용한다.
=ROUND(A2,2) ' 소수 2자리 반올림 =INT((A2*24*60*60))/86400 ' 초 단위로 자르기 =DATE(YEAR(A2),MONTH(A2),DAY(A2)) ' 날짜만3.4 유니코드 정규화와 전각/반각
“é”는 조합형과 단일형이 존재하며 코드 포인트가 다르면 다른 값으로 인식한다. 한글·일문 전각/반각도 동일하다.
' Power Query: 변환 > 텍스트 정규화(NFKC) 사용 ' 수식 기반 치환 예(간이): =SUBSTITUTE(SUBSTITUTE(A2,"－","-"),"（","(")대량·정밀 정규화는 Power Query의 텍스트 정규화 또는 스크립트를 활용한다.
3.5 병합 셀·필터·숨김 행
병합 셀은 범위의 직사각형성을 깨뜨려 기능이 회색이 되거나 결과가 불완전해진다. 필터로 숨긴 행은 제거 기준에 포함되지만, 사용자가 범위를 좁게 선택하면 일부만 적용될 수 있다. 병합 해제, 전체 표 선택 후 다시 시도한다.
3.6 보호·공유 모드 제한
시트 보호, 통합 문서 공유(옛 기능), 공동 편집 제한, 외부 연결 잠금은 명령을 비활성화한다. 보호 해제와 편집 권한을 확보 후 다시 시도한다.
4. 표준 절차: “진단 → 정규화 → 검증 → 제거”
4.1 진단: 차이의 실체 파악
=LEN(A2) ' 길이 비교 =CODE(RIGHT(A2,1)) ' 마지막 문자 코드 =IF(EXACT(A2,B2),"동일","상이") ' 대/소문자 포함 동일성 =SUMPRODUCT(--(A:A<>B:B)) ' 열 간 상이 건수보조열로 길이·문자코드·대소문자 구분 동일성을 표시하면 숨은 차이를 빠르게 찾을 수 있다.
4.2 정규화: 형식과 문자 통일
- 공백·개행 제거: TRIM + CLEAN + NBSP/제로폭 치환을 적용한다.
- 숫자/텍스트 통일: VALUE 또는 TEXT로 일괄 변환한다.
- 날짜·시간 스냅: 반올림 또는 잘라내기로 단위를 고정한다.
- 정규화: 전각→반각, 조합형→정규형으로 변환한다.
- 값 고정: 복사→선택하여 붙여넣기→값을 실행한다.
4.3 검증: 중복 후보 가시화
=COUNTIF($A:$A,A2) ' 단일 열 중복 카운트 =COUNTIFS($A:$A,A2,$B:$B,B2) ' 다중 열 조합 중복 =IF(COUNTIFS($A:$A,A2,$B:$B,B2)>1,"중복","고유")조건부 서식의 ‘수식을 사용하여 서식 지정’을 활용해 중복을 시각적으로 강조한다.
4.4 제거: 기능 실행과 결과 확인
- 범위 또는 표 전체를 정확히 선택한다.
- 데이터 > 중복된 항목 제거를 클릭한다.
- 열 선택 대화상자에서 비교 기준 열을 지정한다.
- 결과 메시지에서 제거 건수와 남은 고유 값 건수를 확인한다.
주의 : 되돌리기 전까지는 복구가 가능하나 저장·다른 작업 후에는 어려울 수 있다. 실행 전 시트를 복제하거나 백업본을 만든다.
5. 대안 워크플로: Power Query·고급 필터·피벗·동적 배열
5.1 Power Query로 중복 제거
- 데이터 > 테이블/범위에서 가져오기를 선택한다.
- 쿼리 편집기에서 비교 열을 선택한 후 홈 > 행 제거 > 중복 항목 제거를 클릭한다.
- 변환 > 형식 변경, 텍스트 정규화(NFKC), 앞/뒤 공백 제거를 적용한다.
- 닫기 및 로드하여 결과를 시트로 반환한다.
Power Query는 프로세스를 기록하여 재실행이 용이하고 유니코드 정규화 등 고급 정제를 지원한다.
5.2 고급 필터의 ‘고유 레코드만’
- 데이터 > 고급을 클릭한다.
- 리스트 범위를 지정하고, 다른 위치에 복사 및 ‘고유 레코드만’에 체크한다.
원본을 보존하면서 고유 목록을 만드는 데 유용하다.
5.3 피벗테이블로 고유 목록 추출
- 삽입 > 피벗테이블을 클릭한다.
- 필요 열을 행 레이블로 두면 자동으로 고유 목록이 생성된다.
5.4 동적 배열 함수
=UNIQUE(A2:A1000) ' 단일 열 =UNIQUE(A2:C1000,0,1) ' 열 조합 기준 고유 행 =SORT(UNIQUE(FILTER(A2:A1000,A2:A1000<>""))) ' 공백 제외동적 배열은 파워쿼리 없이도 재계산 기반 고유 목록을 즉시 생성한다.
6. 오류 재현과 해결 예시
사례 1: 전화번호가 같아 보이는데 제거되지 않는다
원인: NBSP 공백 포함, 일부는 텍스트, 일부는 숫자이다. 조치: 보조열에 정규화 수식 적용 후 값 붙여넣기하고 해당 열을 기준으로 중복 제거한다.
사례 2: 날짜가 중복으로 안 잡힌다
원인: 시간 정보가 00:00:00과 00:00:00.500으로 상이하다. 조치: DATE 함수로 날짜만 추출한 보조열을 기준으로 제거한다.
사례 3: ID 앞 0이 날아간 뒤 섞여 있다
원인: 일부는 숫자 123, 일부는 텍스트 "0123"이다. 조치: TEXT(A2,"0000")로 통일 후 값 붙여넣기한다.
7. 대량 정리 자동화 스크립트
7.1 VBA: 선택 영역 정규화 매크로
Sub NormalizeSelection() Dim c As Range For Each c In Selection.Cells If Not IsEmpty(c.Value) Then Dim s As String s = CStr(c.Value) s = WorksheetFunction.Clean(s) s = WorksheetFunction.Trim(Replace(Replace(Replace(s, Chr(160), " "), ChrW(8239), " "), ChrW(8203), "")) c.Value = s End If Next c End Sub선택 범위를 지정하고 실행하면 CLEAN/TRIM과 NBSP·얇은 공백·제로폭 공간을 제거한다.
7.2 VBA: 텍스트 숫자 변환
Sub TextToNumber() Dim c As Range For Each c In Selection If VarType(c.Value) = vbString Then If IsNumeric(c.Value) Then c.Value = CDbl(c.Value) End If Next c End Sub8. 신뢰성 확보를 위한 운영 팁
- 중복 제거 전후 행 수를 기록하여 영향도를 확인한다.
- 실행 이력을 위해 원본 시트를 복제하고 결과 시트를 별도 저장한다.
- 정규화 수식은 보조열에 작성하고 최종적으로 값으로 고정한다.
- 반복 작업은 Power Query로 절차를 저장해 재사용한다.
- 협업 환경에서는 보호·공유 상태를 점검하고 단일 책임자가 수행한다.
9. 문제 유형별 솔루션 매핑
| 문제 유형 | 핵심 레버리지 | 권장 도구 |
|---|---|---|
| 숨은 문자·공백 | TRIM+CLEAN+UNICHAR 치환으로 값 동질화 | 수식, VBA |
| 숫자/텍스트 혼재 | VALUE/-- 또는 TEXT로 단일 형식 고정 | 수식, 선택하여 붙여넣기 |
| 날짜·시간 미세 차이 | 반올림·자르기·DATE로 비교 단위 통일 | 수식 |
| 유니코드 정규화 | NFKC 정규화, 전각→반각 변환 | Power Query |
| 범위 구조 문제 | 병합 해제, 필터 해제, 표 범위 재선정 | 리본 명령 |
| 권한·보호 문제 | 보호 해제, 공유 모드 해제, 권한 획득 | 검토 탭 |
10. 현장에서 바로 쓰는 체크리스트
- 시트 복제 백업을 만든다.
- 범위의 병합과 필터를 해제한다.
- 보조열에 정규화 수식을 적용한다.
- 숫자/텍스트를 요구 형식으로 통일한다.
- 필요 시 날짜·시간을 비교 단위로 스냅한다.
- 보조열을 값으로 붙여넣기한다.
- 데이터 > 중복된 항목 제거를 실행한다.
- 결과 행 수·건수를 검증한다.
FAQ
보이는 값은 같은데 중복으로 안 잡히는 이유는 무엇인가?
표시 형식이 같을 뿐 실값이 다르기 때문이다. 공백·개행·NBSP·유니코드 정규화 차이, 소수점 잔차, 텍스트/숫자 혼재가 대표적이다. 정규화와 형식 통일 후 다시 시도한다.
표 상태와 범위 상태 중 어느 쪽이 안정적인가?
반복 처리와 열 추가·삭제가 많은 경우 표가 안정적이다. 표는 구조적 참조와 자동 확장, Power Query 연계를 제공한다. 단발성 청소라면 범위도 충분하다.
삭제 대신 고유 목록만 얻고 싶다. 방법이 있는가?
고급 필터의 ‘고유 레코드만’을 사용하거나 UNIQUE 함수를 사용한다. 원본은 보존되고 결과만 새 영역에 생성된다.
대용량 데이터에서 속도가 느리다. 대안은 무엇인가?
Power Query로 로드하여 중복 제거 후 로드하는 것이 효율적이다. 수식은 계산 비용이 누적되므로 결과를 값으로 고정한다.
국제 데이터에서 전각/반각 차이가 섞여 있다. 어떻게 표준화하나?
Power Query의 텍스트 정규화를 사용하거나 변환 규칙을 만든 후 SUBSTITUTE로 일괄 치환한다. 일본어·중국어 기호는 전각을 반각으로 통일하는 것이 일반적이다.