엑셀 중복 데이터 검사 방법에 모든 것.

 
엑셀에서 중복 데이터를 검사하는 방법에 대해 자세히 알아보겠습니다. 엑셀을 처음 사용하시는 분들도 쉽게 따라할 수 있도록 차근차근 설명해 드리겠습니다.
 
중복 데이터란?
먼저, 중복 데이터가 무엇인지 이해해야 합니다. 중복 데이터란 동일한 정보가 두 번 이상 입력된 경우를 말합니다. 예를 들어, 학생 명단에서 같은 이름이 두 번 나오거나, 판매 기록에서 동일한 주문 번호가 여러 번 등장하는 경우입니다.
 
중복 데이터 검사의 중요성
중복 데이터는 여러 가지 문제를 일으킬 수 있습니다:

• 데이터 분석 오류
• 저장 공간 낭비
• 업무 효율성 저하

따라서 중복 데이터를 찾아 처리하는 것은 매우 중요합니다.
 
엑셀에서 중복 데이터를 검사하는 방법
엑셀에서는 여러 가지 방법으로 중복 데이터를 검사할 수 있습니다. 주요 방법 4가지를 자세히 살펴보겠습니다.
 
1. 조건부 서식을 이용한 방법
범례: [홈] 탭 > [조건부 서식] > [셀 강조 규칙] > [중복 값]
예시:
다음과 같은 학생 명단이 있다고 가정해봅시다.

학번	이름	학과
1001	김철수	컴퓨터공학
1002	이영희	경영학
1003	박민수	화학공학
1004	김철수	컴퓨터공학
1005	정수진	경영학

 

이제 이 데이터에 조건부 서식을 적용해 보겠습니다.

1. 전체 데이터 범위(A1:C6)를 선택합니다.
2. [홈] 탭에서 [조건부 서식] 버튼을 클릭합니다.
3. [셀 강조 규칙]에서 [중복 값]을 선택합니다.
4. 원하는 서식(예: 밝은 빨강 채우기)을 선택하고 [확인]을 클릭합니다.

결과:

학번	이름	학과
1001	김철수 (빨강)	컴퓨터공학 (빨강)
1002	이영희	경영학
1003	박민수	화학공학
1004	김철수 (빨강)	컴퓨터공학 (빨강)
1005	정수진	경영학

 

이렇게 하면 중복된 '김철수'와 '컴퓨터공학'이 빨간색으로 강조되어 표시됩니다.
장점:

• 시각적으로 중복 데이터를 쉽게 확인할 수 있습니다.
• 원본 데이터를 변경하지 않고 중복을 확인할 수 있습니다.

단점:

• 대량의 데이터에서는 모든 중복을 한눈에 파악하기 어려울 수 있습니다.

반응형

2. 중복 항목 제거 기능 사용
범례: [데이터] 탭 > [데이터 도구] > [중복 항목 제거]
예시:
위의 학생 명단을 다시 사용해 보겠습니다.

1. 전체 데이터 범위(A1:C6)를 선택합니다.
2. [데이터] 탭에서 [중복 항목 제거] 버튼을 클릭합니다.
3. 중복을 확인할 열을 선택합니다 (여기서는 '이름' 열을 선택합니다).
4. [확인]을 클릭합니다.

결과:

학번	이름	학과
1001	김철수	컴퓨터공학
1002	이영희	경영학
1003	박민수	화학공학
1005	정수진	경영학

 

이 방법을 사용하면 중복된 '김철수' 중 하나가 자동으로 제거됩니다.
장점:

• 중복 데이터를 빠르게 제거할 수 있습니다.
• 대량의 데이터에서도 효과적으로 사용할 수 있습니다.

단점:

• 원본 데이터가 변경되므로 주의가 필요합니다.
• 중복 데이터 중 어떤 것이 삭제될지 예측하기 어렵습니다.

3. 데이터 정렬을 통한 중복 확인
범례: [데이터] 탭 > [정렬 및 필터] > [정렬]
예시:
다시 원래의 학생 명단으로 돌아가겠습니다.

1. 전체 데이터 범위(A1:C6)를 선택합니다.
2. [데이터] 탭에서 [정렬] 버튼을 클릭합니다.
3. '정렬 기준'을 '이름'으로 선택하고 [확인]을 클릭합니다.

결과:

학번	이름	학과
1001	김철수	컴퓨터공학
1004	김철수	컴퓨터공학
1002	이영희	경영학
1003	박민수	화학공학
1005	정수진	경영학

 

이렇게 정렬하면 중복된 '김철수'가 서로 인접하게 되어 쉽게 확인할 수 있습니다.
장점:

• 간단하고 빠르게 중복을 확인할 수 있습니다.
• 원본 데이터의 순서만 바뀔 뿐, 데이터 자체는 변경되지 않습니다.

단점:

• 대량의 데이터에서는 모든 중복을 찾기 위해 여러 번 정렬해야 할 수 있습니다.

4. 피벗 테이블을 이용한 중복 확인
범례: [삽입] 탭 > [피벗 테이블]
예시:
원래의 학생 명단을 사용하겠습니다.

1. 전체 데이터 범위(A1:C6)를 선택합니다.
2. [삽입] 탭에서 [피벗 테이블] 버튼을 클릭합니다.
3. 새 워크시트에 피벗 테이블을 만듭니다.
4. 피벗 테이블 필드 목록에서 '이름'을 행으로, '학번'을 값으로 드래그합니다.
5. 값 필드 설정을 '개수'로 변경합니다.

결과:

이름	학번 개수
김철수	2
이영희	1
박민수	1
정수진	1

 

이 방법을 사용하면 '김철수'가 2번 등장했다는 것을 쉽게 확인할 수 있습니다.
장점:

• 중복 데이터의 개수를 정확히 파악할 수 있습니다.
• 대량의 데이터에서도 효과적으로 사용할 수 있습니다.

단점:

• 피벗 테이블 사용법을 익혀야 합니다.
• 원본 데이터의 위치를 직접 확인할 수 없습니다.

728x90

중복 데이터 처리 시 주의사항
중복 데이터를 검사하고 처리할 때는 다음 사항들을 주의해야 합니다:

1. 데이터 백업: 중요한 데이터를 처리할 때는 반드시 원본을 백업해두세요.
2. 의도적인 중복 확인: 때로는 중복 데이터가 의도적으로 존재할 수 있습니다. 삭제 전 확인이 필요합니다.
3. 부분 중복 주의: 일부 열만 중복인 경우도 있으므로, 전체 행을 고려해야 합니다.
4. 대소문자 구분: 엑셀은 기본적으로 대소문자를 구분하지 않습니다. 필요시 추가 설정이 필요합니다.

1. 실제 활용 사례

이제 실제 업무에서 중복 데이터 검사가 어떻게 활용될 수 있는지 살펴보겠습니다.
 
사례 1: 고객 데이터베이스 정리
마케팅 부서에서 고객 데이터베이스를 관리하고 있습니다. 여러 채널에서 수집된 데이터가 통합되면서 중복 고객 정보가 발생했습니다.

고객ID	이름	이메일	전화번호
C001	김영수	kim@email.com	010-1234-5678
C002	이미라	lee@email.com	010-2345-6789
C003	박준호	park@email.com	010-3456-7890
C004	김영수	kim@email.com	010-1234-5678
C005	최서연	choi@email.com	010-4567-8901

 

이 경우, 조건부 서식을 사용하여 중복을 시각화한 후, 중복 항목 제거 기능을 사용하여 정리할 수 있습니다.

1. 조건부 서식 적용 (이메일 열 기준)
2. 중복 항목 제거 (이메일 열 기준)

결과:

고객ID	이름	이메일	전화번호
C001	김영수	kim@email.com	010-1234-5678
C002	이미라	lee@email.com	010-2345-6789
C003	박준호	park@email.com	010-3456-7890
C005	최서연	choi@email.com	010-4567-8901

 

이렇게 하면 중복된 고객 정보를 제거하고 깨끗한 데이터베이스를 유지할 수 있습니다.
 
사례 2: 판매 데이터 분석
온라인 쇼핑몰에서 월간 판매 데이터를 분석하고 있습니다. 같은 주문 번호가 여러 번 등장하는 경우가 있어, 이를 정리해야 합니다.

주문번호	상품명	수량	금액
O001	티셔츠	2	40000
O002	청바지	1	50000
O001	모자	1	20000
O003	신발	1	80000
O004	가방	1	60000
O002	양말	3	15000

 

이 경우, 피벗 테이블을 사용하여 주문별 총 금액을 계산할 수 있습니다.

1. 피벗 테이블 생성 (주문번호를 행으로, 금액을 값으로 설정)
2. 값 필드 설정을 '합계'로 변경

결과:

주문번호	금액 합계
O001	60000
O002	65000
O003	80000
O004	60000

 

이렇게 하면 중복된 주문 번호를 하나로 합쳐 총 주문 금액을 정확히 파악할 수 있습니다.
 
고급 중복 검사 기법
지금까지 기본적인 중복 검사 방법을 살펴보았습니다. 이제 조금 더 복잡한 상황에서 사용할 수 있는 고급 기법을 알아보겠습니다.
1. COUNTIF 함수를 이용한 중복 검사
COUNTIF 함수는 특정 조건을 만족하는 셀의 개수를 세는 함수입니다. 이를 이용해 중복을 검사할 수 있습니다.
범례: =COUNTIF(범위, 기준)
예시:
다음과 같은 직원 목록이 있다고 가정해봅시다.

사원번호	이름	부서
E001	김철수	영업
E002	이영희	인사
E003	박민수	영업
E004	김철수	마케팅
E005	정수진	인사

 

이제 'D' 열에 다음 수식을 입력합니다: =COUNTIF($B$2:$B$6, B2)
결과:

사원번호	이름	부서	중복 횟수
E001	김철수	영업	2
E002	이영희	인사	1
E003	박민수	영업	1
E004	김철수	마케팅	2
E005	정수진	인사	1

 

이 방법을 사용하면 각 이름이 몇 번 등장하는지 쉽게 확인할 수 있습니다.
 
2. INDEX와 MATCH 함수를 이용한 첫 번째 중복 위치 찾기
때로는 중복된 항목 중 첫 번째 항목의 위치를 찾아야 할 때가 있습니다. 이럴 때 INDEX와 MATCH 함수를 조합하여 사용할 수 있습니다.
범례: =INDEX(범위, MATCH(찾을값, 검색범위, 0))
예시:
위의 직원 목록을 다시 사용해보겠습니다.
'E' 열에 다음 수식을 입력합니다: =IF(COUNTIF($B$2:B2,B2)>1, "중복", INDEX($A$2:$A$6, MATCH(B2, $B$2:$B$6, 0)))
결과:

사원번호	이름	부서	중복횟수	중복위치
E001	김철수	영업	2	E001
E002	이영희	인사	1	E002
E003	박민수	영업	1	E003
E004	김철수	마케팅	2	중복
E005	정수진	인사	1	E005

 

이 방법을 사용하면 중복된 항목의 경우 첫 번째 등장 위치 또는 "중복" 표시를 확인할 수 있습니다.

SMALL

 
중복 데이터 처리의 실제 적용
중복 데이터 검사와 처리는 다양한 업무 상황에서 활용될 수 있습니다. 몇 가지 실제 적용 사례를 더 살펴보겠습니다.
 
1. 이메일 마케팅 리스트 정리
마케팅 팀에서 이메일 캠페인을 준비하고 있습니다. 여러 소스에서 수집된 이메일 주소 목록을 정리해야 합니다.

이름	이메일	가입 경로
김철수	kim@email.com	웹사이트
이영희	lee@email.com	페이스북
박민수	park@email.com	인스타그램
김철수	kim@email.com	이벤트
정수진	jung@email.com	웹사이트

 

이 경우, 다음 단계를 따라 중복을 제거할 수 있습니다:

1. 데이터를 선택하고 [데이터] 탭의 [중복 항목 제거]를 클릭합니다.
2. '이메일' 열을 기준으로 중복을 제거합니다.

결과:

이름	이메일	가입 경로
김철수	kim@email.com	웹사이트
이영희	lee@email.com	페이스북
박민수	park@email.com	인스타그램
정수진	jung@email.com	웹사이트

 

이렇게 하면 중복된 이메일 주소를 제거하여 각 고객에게 한 번씩만 이메일을 보낼 수 있습니다.
 
2. 재고 관리 시스템 정리
물류 팀에서 재고 관리 시스템의 데이터를 정리하고 있습니다. 제품 코드가 중복 입력된 경우가 있어 이를 수정해야 합니다.

제품 코드	제품명	수량	위치
P001	노트북	50	A-1
P002	마우스	100	B-2
P001	노트북	30	C-3
P003	키보드	80	B-1
P004	모니터	40	A-2

 

이 경우, 피벗 테이블을 사용하여 중복된 제품 코드의 수량을 합산할 수 있습니다:

1. 데이터를 선택하고 [삽입] 탭의 [피벗 테이블]을 클릭합니다.
2. 행에 '제품 코드'와 '제품명'을, 값에 '수량'을 드래그합니다.
3. 수량의 값 필드 설정을 '합계'로 변경합니다.

결과:

제품 코드	제품명	수량 합계
P001	노트북	80
P002	마우스	100
P003	키보드	80
P004	모니터	40

 

이렇게 하면 중복된 제품 코드의 수량이 합산되어 정확한 재고 현황을 파악할 수 있습니다.

1. 중복 데이터 예방 전략

중복 데이터를 처리하는 것도 중요하지만, 처음부터 중복 데이터가 발생하지 않도록 예방하는 것이 더 효과적입니다. 다음은 중복 데이터 예방을 위한 몇 가지 전략입니다:
 
1. 데이터 입력 규칙 설정
엑셀의 데이터 유효성 검사 기능을 사용하여 중복 입력을 방지할 수 있습니다.
범례: [데이터] 탭 > [데이터 도구] > [데이터 유효성 검사]
예시:
'사원번호' 열에 중복 입력을 방지하는 규칙을 설정해보겠습니다.

1. '사원번호' 열을 선택합니다.
2. [데이터] 탭에서 [데이터 유효성 검사]를 클릭합니다.
3. '설정' 탭에서 '허용' 옵션을 '사용자 지정'으로 선택합니다.
4. '수식' 란에 다음을 입력합니다: =COUNTIF($A$2:$A$1000,A2)=1
5. '오류 알림' 탭에서 오류 메시지를 설정합니다.

이렇게 하면 이미 존재하는 사원번호를 입력하려 할 때 오류 메시지가 표시됩니다.
 
2. 데이터베이스 정규화
큰 규모의 데이터를 다룰 때는 데이터베이스 정규화 원칙을 적용하는 것이 좋습니다. 이는 데이터를 여러 테이블로 나누어 중복을 최소화하는 방법입니다.
예를 들어, 주문 정보와 고객 정보를 별도의 테이블로 관리하고 고객 ID로 연결하면 고객 정보의 중복을 피할 수 있습니다.
 
3. 데이터 입력 자동화
가능한 경우, 데이터 입력을 자동화하여 사람의 실수로 인한 중복 입력을 방지할 수 있습니다. 예를 들어, 바코드 스캐너를 사용하거나 온라인 폼을 통해 데이터를 직접 데이터베이스에 입력하는 방식을 사용할 수 있습니다.
 
 
엑셀에서 중복 데이터를 검사하고 처리하는 방법은 데이터 관리의 핵심 기술 중 하나입니다. 우리는 조건부 서식, 중복 항목 제거, 데이터 정렬, 피벗 테이블 등 다양한 방법을 살펴보았습니다. 또한 COUNTIF, INDEX, MATCH 등의 함수를 활용한 고급 기법도 알아보았습니다.
중요한 것은 각 상황에 맞는 적절한 방법을 선택하는 것입니다. 때로는 단순히 중복을 제거하는 것이 목적일 수 있고, 때로는 중복의 패턴을 분석하는 것이 목적일 수 있습니다. 항상 데이터의 특성과 분석 목적을 고려하여 가장 적합한 방법을 선택해야 합니다.
또한, 중복 데이터 처리는 단순히 기술적인 문제가 아니라는 점을 기억해야 합니다. 데이터의 정확성과 일관성은 비즈니스 의사결정의 기반이 되므로, 중복 데이터 관리는 데이터 품질 관리의 중요한 부분입니다.
마지막으로, 중복 데이터 처리는 한 번으로 끝나는 작업이 아닙니다. 지속적인 모니터링과 관리가 필요합니다. 정기적으로 데이터를 검토하고, 필요에 따라 중복 검사와 정리 작업을 수행해야 합니다. 이를 통해 항상 깨끗하고 신뢰할 수 있는 데이터를 유지할 수 있습니다.
엑셀의 중복 데이터 검사 및 처리 기능을 마스터하면, 여러분은 더 효율적이고 정확한 데이터 분석을 수행할 수 있을 것입니다. 이는 단순히 엑셀 기술을 넘어, 데이터 기반의 의사결정을 위한 중요한 역량이 될 것입니다. 계속해서 연습하고 실제 데이터에 적용해보면서 여러분만의 노하우를 쌓아가시기 바랍니다. 화이팅!