엑셀에서 데이터를 다루다 보면 중복 값이 의도치 않게 분석 결과를 흐리게 만드는 경험을 자주 하게 됩니다. 처음엔 눈으로 하나씩 확인하느라 시간이 많이 걸렸지만, 중복 값을 자동으로 찾아 표시하거나 제거하는 방법을 배우면서 작업 속도가 크게 올라갔습니다. 아래에서는 제 경험에 바탕을 두고, 중복 값을 찾고 관리하는 대표적인 방법들을 정리해 드리겠습니다.
조건부 서식을 이용한 중복 값 찾기
조건부 서식은 중복된 셀을 자동으로 시각적으로 강조해 주어 한 번에 파악할 수 있게 해 줍니다. 데이터가 바뀌면 서식도 자동으로 업데이트되어 편리합니다.
- 중복 값을 찾을 범위를 선택합니다. 예를 들어 A열 전체나 특정 구간 A2:A100를 선택합니다.
- 홈 탭에서 스타일 그룹의 조건부 서식을 클릭합니다.
- 드롭다운 메뉴에서 셀 강조 규칙의 중복 값을 선택합니다.
- 중복 값으로 판단될 서식을 설정합니다. 필요에 따라 색상이나 글꼴을 선택합니다.
- 확인 버튼을 클릭합니다.
결과적으로 선택한 범위 내의 중복 값이 지정한 서식으로 강조되어 나타납니다. 고유 값은 서식이 적용되지 않습니다.
장점
- 중복 값을 시각적으로 빠르게 확인할 수 있습니다.
- 데이터가 변경되더라도 서식이 자동으로 갱신되어 편리합니다.
단점
- 중복의 개수를 숫자로 바로 확인되지는 않습니다. 단순히 시각적 강조에 그칩니다.
- 중복 여부를 텍스트로 표시해 주지는 않습니다.
수식을 이용한 중복 값 찾기
수식을 사용하면 중복 횟수를 세거나 중복 여부를 텍스트로 표시하는 등 더 다양한 활용이 가능합니다.
2-1. COUNTIF를 이용한 중복 횟수 세기
COUNTIF 함수는 특정 범위에서 조건에 맞는 셀의 개수를 셉니다. 이를 활용해 각 값이 범위에서 몇 번 나타나는지 확인할 수 있습니다.
- 데이터가 있는 범위를 정합니다. 예를 들어 A열의 데이터가 A2:A10에 있다고 합시다.
- 중복 횟수를 표시할 셀(B2 등)에 수식을 입력합니다. 예를 들어
=COUNTIF($A:$A, A2)또는 특정 범위에 한정하면=COUNTIF($A$2:$A$10, A2)입니다. - 수식을 아래로 복사하여 다른 행에도 적용합니다.
결과로 B열에는 각 행의 값이 A열에서 나타난 횟수가 숫자로 표시됩니다. 1은 고유, 2 이상은 중복을 의미합니다.
2-2. IF + COUNTIF를 이용한 “중복” 또는 “고유” 표시
COUNTIF의 결과를 바탕으로 IF 함수를 사용해 텍스트로 구분합니다.
- 데이터가 있는 범위를 준비합니다. 예를 들어 A2:A10.
- B열(B2 등에) 수식을 입력합니다. 예시:
=IF(COUNTIF($A:$A, A2)>1, "중복", "고유")또는=IF(COUNTIF($A$2:$A$10, A2)>1, "중복", "고유"). - 수식을 아래로 복사합니다.
결과로 B열에 각 값의 중복 여부가 “중복” 또는 “고유”로 표시됩니다.
2-3. 다중 열에서 중복 값 찾기
두 개 이상의 열 조합이 중복되는 경우에는 COUNTIFS 또는 SUMPRODUCT를 사용합니다. 아래 예는 두 열의 조합이 중복인 경우를 찾는 방법입니다.
- 예: A열(이름)과 B열(지역)의 조합이 중복인지를 확인하려면
=IF(COUNTIFS($A:$A, A2, $B:$B, B2)>1, "중복", "고유")를 사용합니다. - 수식을 아래로 복사합니다.
참고로 조건이 더 복잡하다면 SUMPRODUCT를 활용해 여러 조건을 한 번에 확인할 수 있습니다.
중복 값 제거하기 (데이터 탭)
중복 값을 단순히 찾는 것을 넘어서 중복된 행을 제거하고 고유한 값만 남기고 싶을 때는 데이터 탭의 중복된 항목 제거 기능을 사용합니다.
- 중복 여부를 판단할 범위를 포함한 전체 데이터를 선택합니다(헤더 포함).
- 데이터 탭으로 이동합니다.
- 데이터 도구 그룹에서 중복된 항목 제거를 클릭합니다.
- 중복 여부를 판단할 열(들)을 선택합니다. 예를 들어 이름만 동일하면 중복으로 보려면 A열만, 이름과 주소가 모두 같아야 중복으로 보려면 A열과 B열을 함께 선택합니다.
- 머리글 포함 여부를 확인하고 확인을 클릭합니다.
결과로 중복된 행이 제거되고 고유한 행만 남습니다. 제거된 항목의 수는 작업이 끝난 뒤 안내됩니다.
실무 팁
- 큰 데이터셋인 경우 정확한 열 범위를 지정해 성능을 높이세요.
- 중복 제거를 실행하기 전 원본 데이터를 백업해 두면 실수에 대비할 수 있습니다.