엑셀로 명단이나 거래처, 상품 목록 등을 관리하다 보면 동일한 데이터가 여러 번 입력되는 경우가 있습니다.
데이터가 많지 않다면 직접 확인해서 삭제할 수도 있지만 수백 개 이상의 데이터에서는 중복값을 하나씩 찾아 삭제하기가 쉽지 않습니다.
엑셀에는 이런 상황에서 사용할 수 있는 ‘중복된 항목 제거’ 기능이 기본으로 제공됩니다.
별도의 함수를 입력하지 않아도 몇 번의 클릭만으로 중복 데이터를 찾아 하나만 남기고 삭제할 수 있습니다.
이번 글에서는 엑셀 중복값 제거 방법부터 특정 열을 기준으로 중복을 삭제하는 방법, 사용 시 주의해야 할 점까지 알아보겠습니다.
엑셀 중복값 제거하는 가장 쉬운 방법
예를 들어 A열에 다음과 같은 상품명이 입력되어 있다고 가정해 보겠습니다.
| 상품명 |
|---|
| 사과 |
| 배 |
| 포도 |
| 사과 |
| 딸기 |
| 배 |
| 수박 |
여기에서는 ‘사과’와 ‘배’가 각각 두 번씩 입력되어 있습니다.
중복값을 제거하면 다음과 같이 정리할 수 있습니다.
| 상품명 |
|---|
| 사과 |
| 배 |
| 포도 |
| 딸기 |
| 수박 |
방법은 간단합니다.
먼저 중복값을 제거할 데이터 범위를 선택합니다.
그다음 엑셀 상단 메뉴에서
데이터 → 중복된 항목 제거
를 선택합니다.
중복된 항목 제거 창이 나타나면 중복 여부를 판단할 열을 선택하고 확인을 누릅니다.
작업이 완료되면 몇 개의 중복값이 제거되었고 몇 개의 고유한 값이 남았는지 안내 메시지가 표시됩니다.
중복값을 제거하면 어떤 데이터가 남을까?
중복된 항목 제거 기능을 처음 사용한다면 어떤 값이 삭제되는지도 알아두는 것이 좋습니다.
엑셀은 같은 값이 여러 개 있을 경우 일반적으로 위쪽에 있는 첫 번째 데이터를 남기고 이후에 나타나는 중복 데이터를 제거합니다.
예를 들어
사과
배
사과
포도
사과
순서로 입력되어 있다면 첫 번째 ‘사과’는 남겨두고 이후에 나타나는 ‘사과’가 제거됩니다.
따라서 데이터의 입력 순서가 중요한 문서라면 중복 제거를 실행하기 전에 어떤 행을 남겨야 하는지 확인하는 것이 좋습니다.
여러 열이 있을 때 중복값 제거하기
실제 업무에서 사용하는 엑셀 문서는 하나의 열만 있는 경우보다 여러 열로 구성된 경우가 많습니다.
예를 들어 다음과 같은 고객 명단이 있다고 가정해 보겠습니다.
| 이름 | 지역 | 등급 |
|---|---|---|
| 홍길동 | 서울 | A |
| 김철수 | 부산 | B |
| 홍길동 | 서울 | A |
| 홍길동 | 대전 | A |
여기에서 중복된 항목 제거를 실행하면 어떤 열을 기준으로 중복을 판단할 것인지 선택할 수 있습니다.
이 부분이 매우 중요합니다.
특정 열을 기준으로 중복값 제거하기
위의 예시에서 ‘이름’ 열만 선택해 중복을 제거한다고 가정해 보겠습니다.
그러면 엑셀은 이름이 같은지만 확인합니다.
따라서
홍길동 / 서울 / A
와
홍길동 / 대전 / A
는 지역이 서로 다르더라도 이름이 같기 때문에 중복으로 판단될 수 있습니다.
반대로
이름 + 지역 + 등급
세 개의 열을 모두 선택하면 선택된 모든 열의 내용이 동일한 행을 중복으로 판단합니다.
즉,
홍길동 / 서울 / A
와
홍길동 / 서울 / A
는 중복이지만,
홍길동 / 서울 / A
와
홍길동 / 대전 / A
는 서로 다른 데이터로 판단합니다.
따라서 여러 열로 구성된 데이터를 정리할 때는 어떤 열을 기준으로 중복을 판단할 것인지 정확하게 선택하는 것이 핵심입니다.
머리글이 있는 데이터라면 확인하기
중복된 항목 제거 창을 보면 ‘내 데이터에 머리글 표시’와 관련된 옵션을 확인할 수 있습니다.
첫 번째 행에
이름 / 지역 / 전화번호 / 상품명 / 판매량
처럼 각 열의 제목이 입력되어 있다면 머리글이 있는 데이터로 처리해야 합니다.
그래야 첫 번째 행의 제목이 일반 데이터로 인식되는 것을 방지할 수 있습니다.
특히 범위를 직접 선택해서 중복 제거를 실행하는 경우 첫 번째 행이 제목인지 데이터인지 확인해 주는 것이 좋습니다.
중복값을 먼저 확인한 후 제거하는 방법
중복 데이터를 바로 삭제하는 것이 불안하다면 먼저 어떤 값이 중복되어 있는지 확인하는 방법도 있습니다.
중복값을 확인할 범위를 선택한 다음
홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값
을 선택합니다.
그러면 중복된 데이터가 색상으로 표시됩니다.
먼저 강조된 데이터를 확인한 다음 실제로 삭제해도 문제가 없는 데이터인지 검토하고 데이터 → 중복된 항목 제거를 실행하면 됩니다.
중요한 업무 자료라면 바로 삭제하기보다는 이 방법으로 한 번 확인하는 것을 권장합니다.
중복된 항목 제거와 조건부 서식의 차이
두 기능은 비슷해 보이지만 목적이 다릅니다.
조건부 서식 → 중복 값은 중복된 데이터를 찾아서 색상으로 표시만 해주는 기능입니다.
원본 데이터는 삭제되지 않습니다.
반면
데이터 → 중복된 항목 제거는 중복된 행을 실제로 삭제하는 기능입니다.
따라서 중복 여부만 확인하고 싶다면 조건부 서식을 사용하고, 중복 데이터를 정리해서 하나만 남기고 싶다면 중복된 항목 제거를 사용하는 것이 좋습니다.
UNIQUE 함수로 중복 없이 목록 만들기
사용 중인 엑셀 버전에서 UNIQUE 함수를 지원한다면 원본 데이터를 삭제하지 않고 중복값을 제외한 새로운 목록을 만들 수도 있습니다.
예를 들어 A2:A100에 데이터가 있다면 다른 셀에 다음과 같이 입력합니다.
=UNIQUE(A2:A100)
A열에
사과
배
사과
포도
배
가 있다면 결과는
사과
배
포도
처럼 표시됩니다.
중복된 항목 제거와 가장 큰 차이는 원본 데이터를 삭제하지 않는다는 점입니다.
따라서 원본 자료를 유지하면서 별도의 고유값 목록을 만들고 싶다면 UNIQUE 함수가 편리합니다.
중복값이라고 무조건 삭제하면 안 되는 이유
엑셀에서 같은 값이 반복된다고 해서 모두 잘못된 중복 데이터인 것은 아닙니다.
예를 들어 다음과 같은 판매 자료가 있다고 가정해 보겠습니다.
| 상품명 | 판매량 |
|---|---|
| 사과 | 10 |
| 배 | 5 |
| 사과 | 20 |
상품명만 보면 ‘사과’가 중복되어 있습니다.
하지만 두 행이 각각 다른 판매 건이라면 정상적인 데이터입니다.
이 상태에서 상품명을 기준으로 중복된 항목을 제거하면 판매량 20에 해당하는 데이터가 삭제될 수 있습니다.
이런 경우에는 중복 제거가 아니라 같은 값을 기준으로 판매량을 합치는 작업이 필요합니다.
예를 들어 SUMIF 함수를 사용하면 같은 상품의 판매량을 합산할 수 있습니다.
=SUMIF(A:A,"사과",B:B)
위의 예에서는 사과의 판매량이 30으로 계산됩니다.
따라서 중복 데이터를 발견했다면 먼저 잘못해서 두 번 입력된 데이터인지, 실제로 여러 번 발생한 정상적인 데이터인지 확인해야 합니다.
중복값 제거 전 원본을 보관하는 것이 좋은 이유
‘중복된 항목 제거’는 실제 데이터를 삭제하는 기능입니다.
잘못된 열을 기준으로 중복을 제거하면 필요한 데이터까지 함께 사라질 수 있습니다.
특히 수백 개 이상의 데이터를 한꺼번에 정리할 때는 어떤 데이터가 삭제되었는지 나중에 확인하기 어려울 수 있습니다.
따라서 중요한 문서라면 작업 전에
파일을 별도로 복사하거나 원본 데이터가 있는 시트를 복제
해 두는 것이 안전합니다.
실수로 제거했다면 작업 직후 실행 취소(Ctrl+Z)를 사용할 수도 있지만 파일을 저장하고 닫는 등 상황에 따라 되돌리기 어려워질 수 있으므로 원본을 따로 보관하는 습관이 좋습니다.
엑셀 중복값 제거 방법 정리
엑셀에서 중복 데이터를 처리하는 방법은 목적에 따라 선택하면 됩니다.
| 원하는 작업 | 추천 방법 |
|---|---|
| 중복값이 있는지 확인 | 조건부 서식 |
| 중복값을 색상으로 표시 | 조건부 서식 → 중복 값 |
| 중복 데이터를 실제로 삭제 | 중복된 항목 제거 |
| 특정 열을 기준으로 중복 삭제 | 중복된 항목 제거에서 기준 열 선택 |
| 원본을 유지하고 고유값만 추출 | UNIQUE 함수 |
| 같은 값의 숫자를 합산 | SUMIF 또는 피벗 테이블 |
단순히 잘못 입력된 중복 데이터를 정리하는 것이 목적이라면 중복된 항목 제거 기능이 가장 간단합니다.
반대로 원본 데이터를 유지해야 한다면 UNIQUE 함수를 활용하는 방법이 좋습니다.
마무리
엑셀에서 중복값을 제거하려면 데이터를 선택한 다음 데이터 → 중복된 항목 제거를 이용하면 됩니다.
별도의 함수를 사용할 필요가 없어 많은 데이터를 빠르게 정리할 수 있다는 장점이 있습니다.
하지만 중복된 항목 제거는 데이터를 실제로 삭제하기 때문에 실행하기 전에 중복 여부를 판단할 열을 정확하게 선택하는 것이 중요합니다.
특히 여러 열로 구성된 표에서는 하나의 열만 선택했을 때와 여러 열을 함께 선택했을 때 중복으로 판단되는 기준이 달라질 수 있습니다.
중복된 값을 먼저 확인하고 싶다면 조건부 서식을 사용하고, 원본을 그대로 유지하면서 중복 없는 목록이 필요하다면 UNIQUE 함수를 활용하면 됩니다.
그리고 같은 값이 여러 번 나타나는 이유가 정상적인 거래나 판매 기록 때문이라면 삭제하지 말고 SUMIF 또는 피벗 테이블을 이용해 같은 값끼리 합치는 방법을 사용하는 것이 좋습니다.