여러 곳에서 모은 자료를 정리하다 보면 같은 값이 두세 번씩 들어가곤 합니다. 이럴 때 필요한 작업이 엑셀 중복값 제거입니다.
이 글에서는 데이터 탭의 중복된 항목 제거 기능부터 조건부 서식으로 중복값 찾기, UNIQUE 함수를 이용한 중복 제거까지 단계별로 정리했습니다. 상황에 맞는 방법을 골라 바로 실무에 적용할 수 있습니다.
엑셀 중복값 제거가 필요한 이유
엑셀 중복값 제거 3가지 방법
지우는 방식·확인하는 방식·보존하는 방식으로 나뉩니다.
| 방법 | 원본 보존 | 버전 조건 | 이럴 때 적합 |
|---|---|---|---|
| 중복된 항목 제거 | 삭제됨 | 모든 버전 | 바로 지워도 될 때 |
| 조건부 서식 | 보존 | 모든 버전 | 지우기 전 확인할 때 |
| UNIQUE 함수 | 보존 | 2021·365 이상 | 고유목록 자동 갱신 |
엑셀 중복값 제거는 명단, 주문 내역, 거래처 목록처럼 데이터가 많을수록 꼭 필요한 작업입니다. 중복이 섞여 있으면 집계가 틀어지고 분석 결과가 왜곡되기 때문입니다.
방법은 크게 세 가지로 나뉩니다. 중복을 바로 지우는 방식, 지우기 전에 눈으로 확인하는 방식, 원본을 보존하며 고유값만 뽑는 방식입니다. 상황에 따라 어울리는 방법이 다르므로 각각의 특징을 알아두면 좋습니다.
중복된 항목 제거 기능으로 바로 지우기
출처: 엑셀러 권현욱
가장 빠른 방법은 엑셀에 내장된 중복된 항목 제거 기능입니다. 이 기능은 중복 데이터를 한 번에 영구 삭제하므로, 작업 전에 원본 데이터를 다른 시트로 복사해 두는 것이 안전합니다.
먼저 중복을 없앨 셀 범위를 선택한 뒤 데이터 탭에서 중복 제거를 클릭합니다. 이어서 나타나는 창에서 중복 판단 기준이 될 열을 지정하고 확인을 누르면, 몇 개가 삭제되고 몇 개의 고유값이 남았는지 결과가 표시됩니다.
여러 열을 기준으로 삭제할 때
열을 하나만 선택하면 그 열의 값이 같은 행이 중복으로 처리됩니다. 반면 여러 열을 함께 선택하면 선택한 모든 열의 값이 전부 일치해야 중복으로 인식됩니다.
예를 들어 ‘이름’과 ‘전화번호’를 함께 기준으로 잡으면 두 값이 모두 같은 행만 삭제됩니다. 동명이인이 섞인 명단을 다룰 때 이름만으로 지우면 실제로 다른 사람이 삭제될 수 있으므로, 이런 경우 여러 열을 함께 기준으로 삼는 것이 안전합니다. 참고로 부분합이나 윤곽선이 설정돼 있으면 중복 제거가 작동하지 않으니 먼저 해제해야 합니다.
조건부 서식으로 중복값 찾기
출처: 오빠두엑셀
지우기 전에 어떤 값이 중복인지 눈으로 먼저 확인하고 싶다면 조건부 서식을 쓰면 됩니다. 이 방법은 원본 데이터를 손상시키지 않고 중복된 셀에 색상만 입혀 강조해 줍니다.
먼저 확인할 범위를 선택한 뒤 홈 탭에서 조건부 서식 > 셀 강조 규칙 > 중복 값을 차례로 선택합니다. 그다음 적용할 서식(예: 진한 빨강 텍스트)을 고르고 확인을 누르면 중복 값이 한눈에 표시됩니다.
COUNTIF 함수로 중복 확인하기
색상 강조 외에 함수로 중복 여부를 판별할 수도 있습니다. COUNTIF 함수는 특정 값이 범위 안에 몇 번 나타나는지 세어 주는데, 이 값이 1보다 크면 중복이라는 뜻입니다.
예를 들어 옆 셀에 각 값의 개수를 세는 수식을 넣으면 어떤 항목이 몇 번 중복됐는지까지 파악할 수 있습니다. COUNTIF 방식은 엑셀 버전과 관계없이 모든 버전에서 작동하므로, 조건부 서식과 함께 쓰면 중복값 찾기가 한결 정확해집니다.
중복 제거 함수 UNIQUE 활용하기
중복 제거·확인 함수 모음
=UNIQUE(A2:A100)범위에서 중복을 뺀 고유값만 반환합니다. 데이터가 바뀌면 결과도 자동으로 갱신됩니다.
=SORT(UNIQUE(A2:A100))중복 제거와 오름차순 정렬을 한 번에 처리합니다.
=UNIQUE(TOCOL(A2:C100))여러 열을 하나의 열로 합친 뒤 중복을 제거합니다.
=COUNTIF($A$2:$A$100, A2)값이 나타난 횟수를 셉니다. 결과가 1보다 크면 중복이라는 뜻입니다.
원본은 그대로 두고 별도 영역에 고유값만 뽑고 싶다면 중복 제거 함수인 UNIQUE를 사용합니다. 이 함수는 범위에서 중복을 제거한 결과를 동적 배열로 돌려주며, 데이터가 바뀌면 결과도 자동으로 갱신됩니다.
사용법은 간단합니다. 빈 셀에 =UNIQUE(A2:A100) 형태로 입력하면 해당 범위에서 중복을 뺀 고유 목록이 나타납니다. 단, UNIQUE 함수는 엑셀 2021과 Microsoft 365 이상 버전에서만 사용할 수 있다는 점을 기억해야 합니다.
SORT 함수로 정렬까지 한 번에
UNIQUE 함수의 결과를 SORT 함수로 감싸면 중복 제거와 정렬을 동시에 처리할 수 있습니다. 예를 들어 =SORT(UNIQUE(A2:A100)) 형태로 입력하면 고유값을 오름차순으로 깔끔하게 정렬해 보여줍니다.
여러 열에 흩어진 데이터에서 중복을 제거할 때는 TOCOL 함수를 함께 씁니다. TOCOL로 여러 열을 하나의 열로 합친 뒤 UNIQUE를 적용하면 되며, TOCOL은 Microsoft 365 버전에서 사용할 수 있습니다. 이렇게 함수를 조합하면 원본을 건드리지 않고도 정돈된 고유 목록을 얻을 수 있습니다.
자주 묻는 질문 (FAQ)
엑셀 중복값 제거와 관련해 많이 나오는 궁금증을 정리했습니다. 작업 전에 짚어두면 실수를 줄일 수 있습니다.
중복된 항목 제거와 UNIQUE 함수 중 무엇을 써야 하나요?
원본에서 중복을 바로 지워도 되면 중복된 항목 제거 기능이 빠릅니다. 반면 원본을 보존하면서 고유 목록을 따로 만들고 자동 갱신까지 원한다면 UNIQUE 함수가 적합합니다. UNIQUE는 엑셀 2021 이상에서만 지원됩니다.
숫자인데 중복으로 인식되지 않아요.
서식이 다른 숫자는 다른 값으로 처리될 수 있습니다. 예를 들어 1과 1.00처럼 표시 형식이 다르면 중복으로 잡히지 않으므로, 작업 전에 셀 서식을 통일해 두는 것이 좋습니다.
지우기 전에 중복을 먼저 확인하고 싶어요.
조건부 서식의 중복 값 강조 기능을 쓰면 원본을 손상시키지 않고 중복된 셀에 색상만 입혀 확인할 수 있습니다. 개수까지 알고 싶다면 COUNTIF 함수를 함께 활용하면 됩니다.
엑셀 중복값 제거, 이렇게 활용하세요
엑셀 중복값 제거는 상황에 맞는 방법을 고르는 것이 핵심입니다. 바로 지울 때는 중복된 항목 제거, 확인 후 지울 때는 조건부 서식, 원본을 보존할 때는 UNIQUE 함수를 쓰면 됩니다.
오늘 정리한 순서대로 내 데이터에 맞는 방법을 골라 적용해 보세요. 엑셀 실무 능력을 더 키우고 싶다면 노션 사용법이나 크롬 확장프로그램 추천 글을, 문서 작업 환경을 정비하려면 오피스365 무료 다운로드 글을 함께 참고하면 도움이 됩니다.
