엑셀에서 정확한 데이터 분석을 위해서는 중복 레코드 관리가 필수적입니다. 중복 데이터는 계산 결과를 왜곡하고 파일 용량을 불필요하게 늘리며, 데이터 해석에 혼란을 초래할 수 있습니다. 다행히 엑셀은 다양한 방식으로 중복 데이터를 제거할 수 있는 기능을 제공합니다.
이 글에서는 데이터 손실 없이 엑셀에서 중복 항목을 제거할 수 있는 8가지 방법을 단계별로 소개합니다.
1. 중복된 항목 제거 도구 활용
엑셀에 내장된 '중복된 항목 제거' 기능은 직관적인 인터페이스로 빠르게 중복을 정리할 수 있는 가장 간편한 방법입니다.
사용 방법:
- 머리글 행을 포함한 데이터 범위를 선택합니다.
- 데이터 탭 >> 데이터 도구 그룹 >> 중복된 항목 제거를 클릭합니다.
- 중복 판단 기준이 될 열을 선택하거나 선택 해제합니다.
- 확인을 클릭합니다.
실행 예시:
- 모든 열을 기준으로 실행하면 1행과 완전히 동일한 6행(주문ID 1002)만 삭제됩니다.
- 엑셀은 "중복 값 1개를 찾아 제거했습니다"라는 메시지를 표시합니다.
- 주문ID 열의 선택을 해제하고 나머지 열만 체크하면,
- 주문ID를 제외한 나머지 정보가 동일한 5행, 6행, 8행이 중복으로 판정되어 삭제됩니다.
장점:
- 시각적인 인터페이스로 누구나 쉽게 사용 가능
- 데이터에 바로 적용되어 작업이 빠름
- 중복 판단 기준 열을 세밀하게 지정 가능
유의 사항:
- 중복 행이 영구적으로 삭제됨
- 각 레코드의 첫 번째 항목만 유지됨
- 백업이 없다면 원본 데이터 복구 불가
전문가 팁: 원본 데이터세트를 보존해야 한다면 이 기능을 사용하기 전에 반드시 다른 시트에 데이터를 복사해 두세요.
2. 고급 필터(고유한 레코드만)
고급 필터는 고유한 레코드만 새 위치로 복사할 수 있어 더 세밀한 제어가 필요할 때 유용합니다.
사용 방법:
- 데이터에 머리글 행이 포함되도록 정리합니다.
- 데이터 탭 >> 정렬 및 필터 그룹 >> 고급을 클릭합니다.
- 다른 위치에 복사를 선택합니다.
- 목록 범위에 A1:G12를 입력합니다.
- 복사 위치에 J1을 입력합니다.
- 고유한 레코드만에 체크합니다.
- 확인을 클릭합니다.
그러면 중복 행인 6행(주문ID 1002)은 결과에서 제외되고, J1 셀부터 아래쪽으로 중복 없는 데이터가 복사됩니다.
심화 활용: 주문ID와 상관없이 동일한 상품 정보를 가진 거래 내역을 찾으려면 다음과 같이 할 수 있습니다.
- 머리글(B1:G1)을 포함한 조건 범위를 설정합니다.
- 이 조건 범위로 고급 필터를 적용해 동일한 거래를 찾아냅니다.
장점:
- 원본 데이터가 그대로 보존됨
- 고유 레코드만 별도 위치로 복사 가능
- 필요시 복잡한 조건과 함께 사용 가능
유의 사항:
- 결과를 저장할 충분한 공간이 필요함
- 원본 데이터가 변경되면 수동으로 다시 실행해야 함
3. 파워 쿼리(Power Query) 활용
파워 쿼리는 원본 데이터를 보존하면서 대용량 데이터세트까지 효율적으로 처리할 수 있는 강력한 솔루션입니다.
사용 방법:
- 데이터를 선택합니다.
- 데이터 탭 >> 데이터 가져오기 및 변환 그룹 >> 표/범위에서를 클릭합니다.
- 비교 기준이 될 열을 선택합니다(전체 열 또는 특정 열만 선택 가능).
- 홈 탭 >> 행 제거 그룹 >> 중복된 행 제거를 클릭합니다.
- 닫기 및 로드를 클릭해 결과를 새 시트로 가져옵니다.
예시:
- 모든 열을 기준으로 중복을 제거하면 6행(주문ID 1002)만 삭제됩니다.
- 주문ID를 제외하고 고객, 상품, 수량, 가격, 날짜 열만 기준으로 하면 4행과 8행 모두 중복으로 식별됩니다.
결과:
장점:
- 새로 고침이 가능한 별도의 결과 집합 생성
- 대용량 데이터세트도 효율적으로 처리
- 원본 데이터 보존
- 반복 가능한 자동화 프로세스로 구축 가능
- 다양한 고급 변환 기능 제공
유의 사항:
- 파워 쿼리에 대한 기본적인 이해 필요
- 매우 큰 데이터세트에서는 리소스를 많이 사용함
- 복잡한 중복 제거 로직에는 추가 단계 필요
4. UNIQUE 함수(엑셀 365/2021)
최신 버전의 엑셀에서는 UNIQUE 함수를 사용해 고유 값을 동적으로 추출할 수 있습니다.
중복 행 제거:
- I1 셀을 선택하고 아래 수식을 입력합니다.
수식을 입력하면 전체 고유 행이 자동으로 확산(spill)되어 표시되며, 원본 데이터를 수정하면 목록도 실시간으로 갱신됩니다. 이 수식은 데이터세트에서 중복된 6행을 제외한 모든 고유 행을 반환합니다.
카테고리별 고유 상품 추출: 상품과 카테고리의 고유 조합을 추출하려면,
- 원하는 셀을 선택하고 아래 수식을 입력합니다.
이 수식은 모든 고유 상품 목록이 담긴 확산 범위를 반환합니다.
고유 고객 목록 추출: 고객 명단의 중복 없는 목록이 필요하다면,
- 원하는 셀을 선택하고 아래 수식을 입력합니다.
이 수식은 고유한 고객 이름 목록을 반환합니다.
장점:
- 원본 데이터 변경 시 자동으로 갱신되는 동적 결과 생성
- 원본 데이터를 훼손하지 않음
- 다른 함수와 자유롭게 결합 가능
- 여러 열에 걸친 고유 조합 반환 가능
유의 사항:
- 엑셀 365와 엑셀 2021에서만 사용 가능
- 자동으로 확장되는 배열 수식(스필 범위) 형태로 생성됨
- 결과가 표시될 작업 공간 계획 필요
- 매우 복잡한 중복 제거 로직은 처리 어려움
5. 조건부 서식 + 필터 조합
이 방법은 시각적으로 중복 항목을 먼저 표시한 후 필터링할 수 있어, 삭제 전에 중복 패턴을 파악하는 데 유용합니다.
사용 방법:
- 데이터 범위를 선택합니다.
- 홈 탭 >> 조건부 서식 >> 셀 강조 규칙 >> 중복 값을 선택합니다.
- 서식 스타일로 연한 빨간색 채우기와 진한 빨간색 텍스트를 선택합니다.
- 확인을 클릭합니다.
- 엑셀이 열 단위로 중복 셀을 강조 표시합니다.
- 데이터 탭 >> 필터를 선택합니다.
- 셀 색 기준으로 필터링하여 중복 값 또는 고유 값만 표시합니다.
- 고유 값은 자동, 중복 값은 색 옵션을 선택합니다.
고유 값만 표시한 화면:
중복 값만 표시한 화면:
중복 행 전체를 강조하고 싶다면 아래와 같은 수식 규칙을 사용하세요.
- 홈 탭 >> 조건부 서식 >> 새 규칙을 선택합니다.
- 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다.
- 아래 수식을 입력합니다.
=COUNTIFS($A$2:$A$12, $A2, $B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2)>1
- 채우기 색을 지정합니다.
- 확인을 클릭합니다.
장점:
- 삭제 전에 중복 항목을 시각적으로 확인 가능
- 모든 데이터가 보존됨
- 선택적으로만 삭제 가능
- 모든 엑셀 버전에서 사용 가능
- 중복 패턴 이해에 도움이 됨
유의 사항:
- 여러 단계를 거치는 절차임
- 필터링을 수동으로 적용해야 함
- 자동 갱신되지 않음
- 데이터가 변경되면 필터를 다시 적용해야 함
6. 피벗 테이블 방법
피벗 테이블은 데이터를 자연스럽게 집계하기 때문에 과정에서 중복 항목이 효과적으로 정리됩니다.
사용 방법:
- 데이터를 선택합니다.
- 삽입 탭 >> 피벗 테이블을 클릭합니다.
- 기존 워크시트와 삽입 위치를 지정합니다.
- 확인을 클릭합니다.
- 피벗 테이블 필드 창에서;
- 다음 필드를 행 영역으로 끌어다 놓습니다:
- 주문ID, 고객, 상품, 카테고리, 날짜
- 다음 필드를 값 영역으로 끌어다 놓습니다:
- 수량, 가격
중복 행(3행과 6행)이 하나로 합쳐져 수량의 합계(5+5=10)와 가격의 합계(129.5+129.5=259)가 표시됩니다.
주문ID를 제외한 상태로 중복 상품과 고객을 확인하고 싶다면:
- 먼저 행 영역에서 주문ID 필드를 제거합니다.
- 그러면 주문ID를 제외한 나머지 정보가 모두 같은 거래만 피벗 테이블에 표시됩니다.
장점:
- 중복 데이터를 단순히 제거하는 것이 아니라 요약 분석 가능
- 대용량 데이터세트를 효율적으로 처리
- 숫자 값이 자동으로 집계됨
유의 사항:
- 피벗 테이블에 대한 기본적인 이해 필요
- 결과 추출 후 추가 서식 작업이 필요할 수 있음
- 숫자 값이 기본적으로 합계로 집계됨(일부 필드에는 부적합할 수 있음)
7. COUNTIF 보조 열 활용
이 방법은 보조 열을 추가해 각 레코드의 최초 등장 여부를 식별하는 방식입니다.
사용 방법:
- H열에 "중복 확인"이라는 머리글로 보조 열을 추가합니다.
- 수식을 사용해 고유 행을 식별합니다.
- 필터를 적용해 고유 레코드만 표시합니다.
완전한 중복 식별:
- H2 셀을 선택하고 아래 수식을 입력합니다.
=IF((COUNTIFS($A$2:$A$12, $A2, $B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2))=1, "Unique","Duplicate")
이 수식은 행 전체가 중복인 경우 "Duplicate"로 표시합니다.
주문ID 제외 판정: 주문ID와 무관하게 거래 내역 기준으로 중복을 식별하려면:
=IF((COUNTIFS($B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2))=1, "Unique","Duplicate")
이 수식은 거래 내역이 중복되는 2행, 3행, 5행, 6행, 10행을 "Duplicate"로 표시합니다.
장점:
- 어떤 레코드가 중복인지 보여주면서 원본 데이터도 유지
- 복잡한 조건으로 커스터마이징 가능
- 중복인 행을 명확하게 식별
유의 사항:
- 추가 열이 하나 필요함
- 열이 많아지면 수식이 복잡해질 수 있음
- 데이터가 변경되면 수식 조정 필요
- 새 데이터가 생기면 수식을 아래로 복사해야 함
8. 수식 기반 추출(INDEX/MATCH 또는 FILTER)
고급 사용자라면 INDEX, MATCH 등의 함수를 조합해 고유 값을 추출할 수 있습니다.
INDEX/MATCH 사용(구버전 엑셀): 별도 위치에 고유한 고객 이름을 추출합니다.
- 원하는 셀을 선택하고 아래 수식을 입력합니다.
=IFERROR(INDEX($B$2:$B$12,MATCH(0,COUNTIF($I$1:I1,$B$2:$B$12),0)),"")
FILTER 함수 사용(엑셀 365/2021): 모든 열을 유지하면서 고유 레코드만 추출합니다.
- 원하는 셀을 선택하고 아래 수식을 입력합니다.
=FILTER(A2:G12, MATCH(A2:A12&B2:B12&C2:C12&D2:D12&E2:E12&F2:F12&G2:G12, A2:A12&B2:B12&C2:C12&D2:D12&E2:E12&F2:F12&G2:G12, 0)=ROW(A2:A12)-ROW(A2)+1)
장점:
- 매우 높은 커스터마이징 자유도
- 다른 방법이 통하지 않는 상황에서도 활용 가능
- 복잡한 논리 조건을 포함할 수 있음
- 원본 데이터를 훼손하지 않음
- 원본 데이터 변경 시 동적으로 갱신됨
유의 사항:
- 고급 엑셀 지식 필요
- 구현과 유지보수가 비교적 복잡함
- 구버전 엑셀에서는 배열 수식 입력이 필요할 수 있음
- 대용량 데이터에서는 리소스 부담이 클 수 있음
결론
엑셀에서 중복 데이터를 제거하는 일은 더 이상 위험하거나 복잡한 작업이 아닙니다. 작은 보고서든 대규모 데이터세트든, 엑셀은 중복을 안전하고 유연하게 식별·제거할 수 있는 다양한 방법을 제공합니다. 데이터의 특성에 맞게 위 8가지 방법 중 적절한 것을 선택하면 데이터 손실 없이 중복을 정리할 수 있습니다. 어떤 방법이 최선인지는 사용 중인 엑셀 버전, 데이터 크기, 그리고 수식 선호 여부에 따라 달라집니다. 이 8가지 기술을 익혀 두면 어떤 중복 정리 작업에도 당황하지 않고 대응할 수 있습니다. 특히 원본 데이터세트를 직접 수정하는 방법을 사용할 때는 반드시 사전에 데이터를 백업하는 습관을 들이세요.