엑셀에서 중복 데이터 제거하기

'엑셀 데이터 처리 - 텍스트 나누기' 글에서도 설명을 했지만 엑셀을 제대로, 쉽게 사용하려면 데이터가 먼저 정리되어 있어야 합니다.

데이터가 정리되지 않은 상태에서는 수식을 복잡하게 꼬거나 난해한 방법으로 문제를 해결해야 하는 경우가 많으므로 가능하면 데이터를 미리 정리하는 것이 좋습니다.

데이터를 정리하는 방법은 다음과 같이 여러 가지가 있습니다.

• 하나의 필드에 입력되어 있는 텍스트를 나누기
• 중복 데이터 제거
• 불필요한 값 제거
• 잘못된 값을 바꾸기 등

이번 글에서는 이 중에서 '중복 데이터를 제거하는 방법'을 알아 보겠습니다.

 

1. 1개의 열에서 중복된 데이터 찾아서 제거하기

실습 시트: [1개열 중복]

아래와 같은 상품코드 정보가 있는데 누군가가 실수로 데이터를 중복으로 입력해 놓았습니다.

상품코드가 동일한 것이 2쌍이 있습니다.  상품코드가 NP0012인 것과 NP0015인 것이 중복으로 입력되어 있습니다.

데이터가 이런 상태로 계속 존재하면 동일한 상품코드인데 누군가는 실수로 다른 단가를 입력하는 등 문제가 생길 수 있으므로 반드시 중복데이터를 찾아서 제거해야 합니다.

참고)A열은 상품코드 정보에서는 실제로 필요없는 것인데 삭제 대상을 구별하기 쉽게 일부러 추가한 것입니다. 

 

1) 조건부 서식을 이용하여 중복 데이터 표시

중복데이터를 바로 제거해도 되지만 제거하기 전에 중복건이 몇개나 있는지 엑셀의 조건부 서식을 이용해서 찾아 보겠습니다.

01 ① 중복 값을 체크 할 B열 전체를 선택하고 ② 리본 메뉴에서 [홈] 탭 → [스타일] 그룹 → [조건부 서식]→  [셀 강조 규칙]→ ③ [중복 값]를 선택합니다.

 

02 [중복 값] 대화상자의 ① [드롭다운 리스트]에서 '중복'을 선택하고 ② [적용할 서식]에서 '진한 빨강 텍스트가 있는 연한 빨강 채우기'를 선택하고 ③ <확인>을 누릅니다.

 

03 상품 코드가 중복된 데이터에 빨간색 표시가 된 것을 확인할 수 있습니다.

 

2) 중복 데이터 제거

이제 중복데이터를 삭제해 보겠습니다.

중복 데이터 중 아래에 있는 것이 제거 되므로 No.6, No.12가 제거되어야 정상적으로 제거 된 것입니다.

01 중복데이터가 있는 ① [B] 열 전체를 선택하고

 

② 리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [중복된 항목 제거]를 누르면 [중복된 항목 제거 경고] 대화상자가 나타납니다.
* 이 대화상자는 하나의 열만 선택하고 중복데이터를 제거하려고 할 때 나타납니다.

 

02 [중복된 항목 제거 경고] 대화상자에서 ① '선택 영역 확장'을 선택하고 ② <중복된 항목 제거>를 누릅니다.

 

03 ① [중복 값 제거] 대화상자의 [열] 표시에서 중복이 있는 열을 선택하고 ② <확인>을 누릅니다.

사실 '상품코드' 단위로 중복이 발생했기 때문에 '상품코드'열만 선택해도 됩니다만, 누군가가 동일한 상품코드인데 다른 상품명, 제조사, 단가를 입력했을 수도 있으므로 '상품코드,상품명,제조사,단가'를 모두 선택합니다.
* 만약 상품코드가 동일한데 상품명,제조사,단가를 다르게 입력한 경우가 있다면 상품코드 단위로 삭제되지 않고 상품명,제조사,단가 단위로 중복인 데이터만 삭제됩니다.

 

아래와 같은 처리 메시지가 나타나면 ③ <확인> 버튼을 클릭합니다.

 

04 No. 6인 NP0012와 No. 12인 NP0015 상품코드 행이 삭제된 것을 확인할 수 있습니다.

 

[알아 두면 좋아요] 선택한 열에서만 중복을 제거할 경우 - ‘현재 선택 영역으로 정렬’

현재 선택 영역으로 정렬을 선택하고

 

B열만 선택하면 선택할 열 안에서만 중복을 제거해 줍니다.

 

[B] 열에서만 중복을 제거했으므로 맨 아래에 빈 자리가 생기고 다른 열은 아무런 변화가 없습니다. 따라서 이 기능은 특정 열의 중복만 제거하고 싶을 때 사용합니다.

 

2. 2개의 이상의 열에서 중복된 데이터 찾아서 제거하기

실습 시트: [2개이상의열 중복] 

어떤 회사에서 직원은 하루에 한번 출근한다고 하면 근무 기록 데이터는 하루에 하나만 입력되어야 합니다.

그런데 아래 그림과 같이 직원 박민수, 강영찬의 근무기록은 같은 날짜에 2건이 입력되어 있습니다(10월 4일에 2번 근무한 것으로 되어 있음). 누군가가 실수로 잘못 입력한 중복데이터 입니다.

참고)A열은 근무 정보에서는 실제로 필요없는 것인데 삭제 대상을 구별하기 쉽게 일부러 추가한 것입니다. 

데이터가 중복으로 존재하면 근무시간이 실제 보다 많이 계산되는 등 많은 문제를 유발시킬 수 있으므로 반드시 중복데이터를 찾아서 제거해야 합니다.

 

1) 수식을 이용하여 중복 데이터 표시

중복을 제거하기 전에 먼저 어떤 데이터가 중복인지 찾아서 확인하는 것이 좋습니다. 위의 예에서는  1개의 열에서 중복된 행을 찾을 때  조건부 서식을 이용했는데  2개 이상의 열이 동시에 중복인 것은 조건부 서식의 '셀 강조 규칙'의 '중복 값' 표시 기능으로는 찾을 수 없고 다음 두 가지 방법 중에서 사용하기 편한 방법으로 찾으면 됩니다.

수식으로 중복인 개수를 표시

조건부 서식 대신에 다음과 같이 수식으로 중복인 표시하여 찾을 수 있습니다.

[K2]셀에 다음 수식을 입력하고 나머지 [K3:K13] 범위에는 자동채우기를 합니다.

=COUNTIFS($B$2:$B$13,B2,$C$2:$C$13,C2,$D$2:$D$13,D2)

이 수식은 사번, 이름, 근무일이 같은 행의 개수를 구해 줍니다.

COUNTIFS 함수는 여러 조건을 만족하는 셀의 개수를 구해주므로 이 수식은 첫 번째 인수인 사번 범위에서 동일한 사번이면서,  이름 범위에서 동일한 이름이고, 근무일 범위에서 동일 근무일인 행의 개수를 구해줍니다. 결과적으로 행의 개수가 1보다 크면 중복건입니다.

[ 참고 - COUNTIFS 함수 - 여러 조건을 만족하는 셀의 개수 구하기 ]

아래 그림에서 K열의 값이 2인 것은 동일 사번, 동일 이름, 동일 근무일이라서 중복인 것을 알 수 있습니다.

 

조건부 서식에서 수식으로 서식 지정 기능 사용

01 아래와 같이 ① 중복 데이터가 있는 범위를 선택합니다.

 

② [홈] 탭 → [스타일] 그룹 → [조건부 서식] → [새 규칙] → [규칙 유형 선택] 목록에서 '수식을 사용하여 서식을 지정할 셀 결정' 선택 → ③ 다음 수식을 입력하고 ④ 중복인 경우 표시할 서식을 지정하고 ⑤ <확인>을 누릅니다.

=COUNTIFS($B$2:$B$13,$B2,$C$2:$C$13,$C2,$D$2:$D$13,$D2)>1

 

02 사번, 이름, 근무일이 같은 행이 1개 이상이면 색상으로 중복을 표시해 줍니다.

 

2) 중복 데이터 제거

중복인 데이터는 확인했으니 이제 중복데이터를 제거해 보겠습니다.

중복 데이터 중 아래에 있는 것이 제거 되므로 No.4, No.11이 제거되어야 정상적으로 제거 된 것입니다.

01 ① 근무 기록이 입력된 전체 범위를 선택합니다.

 

② 리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [중복된 항목 제거]를 누르면 [중복 값 제거] 대화상자가 나타납니다.

 

02 [중복 값 제거] 대화상자의 ① [열] 표시에서 중복이 있는 열을 선택한 후 ② [확인]을 누릅니다.

사실 '사번, 근무일' 단위로 중복이 발생했기 때문에 '사번, 근무일'열만 선택해도 됩니다만, 누군가가 동일한 사번인데 다른 이름을 입력했을 수도 있으므로 '사번, 이름, 근무일'를 모두 선택합니다.
* 만약 사번이 동일한데 이름을 다르게 입력한 경우가 있다면 사번, 근무일 단위로 삭제되지 않고 사번,이름,근무일 단위로 중복인 데이터만 삭제됩니다.

 

03 No.4, No.11인 행이 삭제된 것을 확인할 수 있습니다.

 

참고사이트- 데이터를 정리하는 10가지 주요 방법 : https://support.microsoft.com/ko-kr/office/%eb%8d%b0%ec%9d%b4%ed%84%b0%eb%a5%bc-%ec%a0%95%eb%a6%ac%ed%95%98%eb%8a%94-10%ea%b0%80%ec%a7%80-%ec%a3%bc%ec%9a%94-%eb%b0%a9%eb%b2%95-2844b620-677c-47a7-ac3e-c2e157d1db19?ui=ko-kr&rs=ko-kr&ad=kr

 

 

---

엑셀 수식 강좌 

[ 수식 다루기 ]

• 엑셀 수식 알아보기(수식의 입력, 수정, 구성)
• 엑셀 수식 연산자와 연산 우선순위
• 엑셀에서 와일드카드가 뭔가요?

[ 엑셀에서 입력과 표시방법 ]

• 엑셀에서 값을 입력하고 표시하는 원리
• 셀서식을 지정하여 원하는 형태로 표시하기
• 엑셀에서 복사하고 붙여넣기의 모든 것
• 엑셀 자동 채우기의 모든 것

[ 수식에서 셀과 범위 참조 방식 ]

• 엑셀 상대참조/절대참조/혼합참조 완벽 정리
• 엑셀에서 다른 시트 참조하기
• 엑셀에서 3차원 참조로 한번에 자료 집계하기
• 다른 엑셀 파일을 연결하여 한번에 실적 집계하기

[ 엑셀을 제대로 쓰는 데이터처리 ]

• 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
• 수식으로 한 번에 텍스트 나누기
• 엑셀 고급필터로 다양하게 자료 필터링하기
• 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
• 엑셀 오류데이터 처리하기
• 파워 쿼리(Power Query) 소개
• 파워쿼리로 외부 데이터 가져오기
• 엑셀에서 중복 데이터 제거하기
• 엑셀 텍스트 나누기의 모든 것
• 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
• 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
• 사용자 지정 수식으로 데이터 유효성 검사하기
• 데이터 유효성 검사로 허용된 데이터만 입력
• 엑셀 데이터 다루기 첫 단추는 데이터 구조화

[ 엑셀 이름정의와 표기능 ]

• 직관적인 엑셀 표 구조적 참조
• 엑셀 표 기능으로 자료 관리하기
• 엑셀 동적 범위에 이름 정의하기
• 엑셀 이름표, 이름 정의

[ 수식을 분석하고 오류 해결하기 ]

• 엑셀 순환참조 문제 해결하기
• 수식 계산 단계 알아보기
• 엑셀 수식 오류 검사하고 추적하기
• 엑셀에서 참조 셀 추적하기
• 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법