처음으로 엑셀에서 중복값을 다루어야 했던 날이 아직도 생생합니다. 고객 목록이 여러 시트에 흩어져 있었고, 이름과 연락처가 중복으로 남아 있어 보고서에 오차가 생길 위험이 컸습니다. 그때부터 저는 다양한 방법으로 중복을 찾아 관리하는 과정을 차근차근 배웠고, 지금까지도 같은 원리를 여러 상황에 적용하고 있습니다. 이 글은 그때의 경험을 바탕으로, 엑셀에서 중복값을 찾고 관리하는 주요 방법들을 정리한 이야기입니다.
COUNTIF 함수를 이용한 중복값 찾기 (조건부 서식 활용)
중복 찾기에서 가장 흔하고 강력한 방법 중 하나가 COUNTIF를 활용하는 방식입니다. 특정 값이 범위에서 몇 번 나타나는지 세고, 그 결과가 1보다 크면 중복으로 간주하여 조건부 서식을 적용합니다. 아래의 핵심 아이디어와 절차를 참고해 보시기 바랍니다. 자세한 내용은 공식 문서를 확인하시면 도움이 됩니다
- 핵심 아이디어: 특정 셀의 값이 전체 범위에서 몇 번 나타나는지 세어봅니다. 등장 횟수가 2회 이상인 경우를 중복으로 판단합니다.
- 수식 예시: =COUNTIF(범위, 셀)
- 범위 예시: A1:A10, 셀 예시: A1
- 활용 방법(조건부 서식):
- 중복값을 찾고자 하는 셀 범위를 선택합니다. 예: A1:A10
- 홈 탭 → 조건부 서식 → 새 규칙을 클릭합니다.
- 수식으로 서식을 지정할 셀 결정 선택 후 다음 수식을 입력합니다.
- 다음 수식이 참인 값의 서식 지정에 =COUNTIF($A$1:$A$10, A1)>1 를 입력합니다.
- 서식 버튼으로 중복값에 적용할 채우기 색상 등을 지정하고 확인합니다.
- 새 서식 규칙 창에서 확인을 눌러 규칙을 적용합니다.
- 예시: A열에 이름 목록이 있을 때, 중복된 이름을 강조하고 싶다면 A1:A10 범위를 선택하고 위 수식을 적용합니다.
COUNTIF 함수를 이용한 중복값 찾기 (별도 열에 표시)
조건부 서식이 아닌, 별도의 열에 중복 여부를 표시하고 싶을 때도 COUNTIF를 활용할 수 있습니다. 핵심 아이디어는 각 셀이 범위 내에서 몇 번 나타나는지 계산해 그 값을 표시하는 것입니다. 자세한 내용은 위와 같은 방식으로 적용합니다. 예시 수식은 동일하게 =COUNTIF(범위, 셀)입니다.
- 데이터 옆에 새로운 열을 추가합니다. 예: A열 데이터 옆에 B열
- 첫 번째 셀에 =COUNTIF($A$1:$A$10, A1) 를 입력합니다.
- 아래로 복사하면 모든 셀에 대해 등장 횟수를 확인할 수 있습니다.
- 응용: 중복 여부를 텍스트로 표시하려면 IF와 COUNTIF를 조합합니다. 예: =IF(COUNTIF($A$1:$A$10, A1)>1, “중복”, “고유”)
SUMPRODUCT 함수를 이용한 중복값 찾기 (다중 조건)
SUMPRODUCT는 배열 연산을 편리하게 처리하는 기능으로, 여러 조건을 결합해 중복을 판단할 때 유용합니다. 예를 들어 이름과 나이가 모두 같은 경우를 찾는 데 사용할 수 있습니다. 핵심 아이디어는 조건에 맞는 배열의 곱을 통해 중복 여부를 판단하는 것입니다. 예시 수식은 아래와 같습니다. 다중 조건의 예시로 A열의 이름과 B열의 나이가 모두 같은 경우를 들 수 있습니다.
- 수식 예시: =SUMPRODUCT((A1=$A$1:$A$10)*(B1=$B$1:$B$10))
- TRUE/FALSE 배열이 곱해져서 1이 되면 두 조건이 모두 일치하는 항목의 수를 반환합니다.
- 활용:
- 조건부 서식에서 같은 방식으로 >1을 만족하는 경우를 중복으로 표시
- 별도 열에 표시하려면 =IF(SUMPRODUCT((A1=$A$1:$A$10)*(B1=$B$1:$B$10))>1, “중복”, “고유”)
중복값 제거 기능 활용 (가장 빠르고 간편한 방법)
수식을 사용하지 않고 엑셀의 내장 기능으로 중복값을 제거하는 방법은 가장 빠르고 간편합니다. 아래 절차를 따라 해보시길 권합니다.
- 중복값을 제거할 데이터 범위를 선택합니다.
- 데이터 탭 → 데이터 도구 그룹에서 중복된 항목 제거를 클릭합니다.
- 중복된 값 제거 대화 상자에서 어떤 열을 기준으로 중복을 판단할지 선택합니다. 모든 열을 선택하면 행 전체가 완전히 동일한 경우에만 중복으로 간주합니다. 특정 열만 선택하면 해당 열의 값이 같을 때만 중복으로 제거됩니다.
- 머리글 포함 여부를 확인하고 확인을 클릭합니다.
- 엑셀이 중복 값을 제거하고 제거된 개수와 남은 고유 값의 수를 알려줍니다.
- 주의: 이 기능은 중복된 데이터를 영구적으로 삭제합니다. 원본 데이터를 보존하고 싶다면 작업 전에 백업해 두는 것을 권합니다.
어떤 방법을 선택해야 할까요?
다양한 상황에 따라 적절한 방법이 다릅니다. 아래 가이드를 참고해 보시길 바랍니다.
- 중복값을 시각적으로 강조하고 싶다면: COUNTIF와 조건부 서식의 조합이 가장 직관적입니다.
- 중복 여부를 별도 열에 표시하고자 한다면: COUNTIF 혹은 IF와 COUNTIF의 조합을 사용합니다.
- 여러 열의 조건을 조합해 중복을 찾고 싶다면: SUMPRODUCT를 활용합니다.
- 중복된 데이터를 완전히 삭제하고 싶다면: 중복값 제거 기능이 가장 빠르고 효율적입니다.
이 외에도 MATCH와 ROW를 조합한 방법이나 파워 쿼리(Power Query)를 활용하는 방식도 존재합니다. 다만 앞에서 소개한 방법들이 엑셀에서 중복값을 다루는 데 가장 기본적이면서도 실무에서 널리 활용되는 수식 활용법임은 분명합니다. 실제로 직접 엑셀에서 실습해 보시면 어떤 상황에서 어떤 방법이 더 편한지 자연스럽게 느껴지실 겁니다. 추가로 궁금하신 점이 있으시면 언제든지 질문해 주세요.