 |
| 엑셀에서 대량의 데이터를 정리할 때 활용하는 중복값 탐색 및 제거 방법 |
엑셀 실무에서 대량의 명단이나 거래 내역을 취합하다 보면 동일한 데이터가 중복 입력되어 분석 오류를 유발하는 경우가 빈번합니다. 중복 데이터를 처리하는 방식은 시각적 확인, 원본 직접 삭제, 동적 분리 추출의 세 가지 접근법으로 나뉩니다.
단순히 데이터를 지우는 것보다 원본 데이터의 보존 여부와 자동 갱신 필요성을 따져 적절한 방식을 선택해야 데이터 유실 사고를 방지할 수 있습니다.
📋 목차 및 진행 순서
1. 조건부 서식으로 중복값 시각적 강조 및 검토 방법
데이터를 영구 삭제하기 전 어떤 행이 겹치는지 육안으로 검토해야 할 때는 조건부 서식이 가장 유용합니다. 원본 데이터를 전혀 훼손하지 않고 겹치는 항목에만 배경색이나 글자색을 적용하여 선별 검토가 가능합니다.
설정 순서는 다음과 같습니다. 먼저 검사할 데이터 열이나 셀 범위를 마우스로 드래그하여 선택합니다. 상단 메뉴에서 [홈] ➔ [조건부 서식] ➔ [셀 강조 규칙] ➔ [중복 값]을 순서대로 클릭합니다.
설정 창이 나타나면 값 선택 상자에서 [중복]을 지정하고 적용할 서식(예: 진한 빨강 텍스트가 있는 연한 빨강 채우기)을 선택한 뒤 [확인]을 누릅니다.
이후 데이터 필터(단축키: Ctrl + Shift + L)를 실행하여 [색상 기준 필터]를 선택하면 중복된 행만 모아서 정밀 검토할 수 있습니다. 자세한 서식 옵션 규정은 마이크로소프트 공식 지원 센터의 스프레드시트 활용 문서를 참조할 수 있습니다.
 |
| 조건부 서식을 활용하여 중복된 셀 데이터를 시각적으로 강조하는 단계 |
2. 데이터 탭의 '중복된 항목 제거' 기능으로 완전 삭제하기
수만 건 이상의 대용량 데이터에서 완전히 동일한 레코드를 영구적으로 없애야 할 때는 리본 메뉴의 내장 도구를 사용하는 것이 처리 속도 면에서 가장 빠릅니다. 이 방식은 실행 즉시 중복된 행을 삭제하고 고유한 데이터만 테이블에 남깁니다.
작업 경로는 [데이터 표 내부 셀 클릭] ➔ [데이터] ➔ [데이터 도구] ➔ [중복된 항목 제거] 순으로 진입합니다.
대화 상자가 뜨면 상단의 [내 데이터에 머리글 표시] 체크박스를 반드시 확인해야 첫 번째 제목 행이 데이터로 인식되어 삭제되는 실수를 막을 수 있습니다.
특정 열(예: 전화번호, 사번) 기준만 일치해도 지울 것인지, 모든 열의 내용이 100% 동일할 때만 지울 것인지 체크박스를 통해 선택한 후 [확인]을 누르면 처리 결과 건수 팝업이 출력됩니다.
| 구분 | 조건부 서식 | 중복된 항목 제거 | UNIQUE 함수 |
|---|---|---|---|
| 작업 목적 | 시각적 강조 및 검토 | 원본 내 직접 삭제 | 고유값 신규 추출 |
| 원본 데이터 | 100% 보존됨 | 영구 삭제 변형 | 100% 보존됨 |
| 자동 갱신 여부 | 데이터 변경 시 실시간 반영 | 수동 재실행 필요 | 실시간 자동 반영 |
 |
| 검사 기준 열을 지정하여 완전히 일치하는 행을 제거하는 대화상자 |
3. UNIQUE 함수를 활용한 원본 보존 고유값 추출법
원본 데이터베이스를 그대로 유지한 채 보고서용 고유 목록만 별도의 열이나 시트에 뽑아내야 할 때는 동적 배열 함수인 UNIQUE를 활용합니다.
원본의 값이 수정되거나 새로운 행이 추가되면 추출된 고유값 리스트도 즉시 자동 갱신됩니다.
기본 수식 구조는 매우 단순합니다. 고유값을 나열할 빈 셀을 클릭하고 다음과 같이 수식을 입력합니다.
=UNIQUE(A2:A100)다중 열(예: 성명과 연락처가 모두 일치하는 행)을 기준으로 검사하고자 할 때는 범위를 =UNIQUE(A2:B100)처럼 다중 열로 지정하면 됩니다. 가나다 순으로 깔끔하게 정렬된 결과를 얻으려면 =SORT(UNIQUE(A2:A100)) 형태로 SORT 함수와 중첩하여 사용하는 것이 실무 표준입니다.
해당 기능은 Microsoft 365, 웹용 엑셀, Office 2021 이상 버전에서만 지원되므로 이전 버전 사용자에게 파일을 전달할 때는 수식 결과를 값으로 복사(단축키: Ctrl + Alt + V ➔ 값 선택)해 전달해야 오류를 피할 수 있습니다.
💡 [전문가 분석] 실무/실생활 관점에서의 팁 및 주의사항
실무에서 중복값 제거 작업을 진행할 때 가장 흔하게 발생하는 문제는 보이지 않는 공백(Space)과 줄바꿈 문자로 인한 불일치 오류입니다. 사람의 눈에는 동일한 "홍길동"으로 보이지만 한쪽 텍스트 끝에 띄어쓰기가 한 칸 포함되어 있으면 엑셀은 이를 서로 다른 고유 데이터로 인식하여 중복 판별에서 누락시킵니다.
따라서 데이터 정제 전에는 반드시 =TRIM() 함수를 거쳐 양끝 불필요한 공백을 제거하고, =CLEAN() 함수로 웹이나 사내 ERP에서 복사해 온 인쇄 불가능한 제어 문자를 사전에 제거하는 전처리 작업이 선행되어야 합니다.
사소해 보이지만 이 기초 공사 같은 전처리 1분을 거치느냐에 따라 나중에 데이터 검증에 들어가는 수십 분의 시간을 아낄 수 있습니다.
두 번째 주의점은 [중복된 항목 제거] 기능의 되돌리기 한계입니다. 대용량 파일에서 이 기능을 실행한 뒤 다른 작업을 연이어 수행하다가 뒤늦게 오삭제를 인지하면 원본 복구가 사실상 불가능합니다.
파괴적 편집 작업을 수행하기 전에는 반드시 시트 전체를 별도로 복사(Ctrl + 시트 탭 드래그)하여 백업본을 만든 후 원본 시트에서 작업을 진행하는 습관이 안전합니다.
더 다양한 엑셀 함수 활용법과 직장인 실무 데이터 정리 팁은 사무·생산성 카테고리 모음에서 확인해 보세요.
엑셀 중복값 처리는 상황에 따라 도구를 달리해야 합니다. 단순 검토는 조건부 서식, 영구 정리는 중복된 항목 제거, 실시간 원본 보존은 UNIQUE 함수를 사용하는 것이 작업 오류를 줄이는 가장 효과적인 프로세스입니다.
데이터 전처리 규칙을 철저히 지켜 안전하게 스프레드시트를 관리해 보시길 권장합니다.
댓글
댓글 쓰기