Home > 강좌 > 엑셀수식강좌 > 04. 엑셀을 제대로 쓰는 데이터처리 > 엑셀에서 중복 데이터 표시하고 제거하기

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

  • by

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

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

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

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

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

실습용 엑셀파일 다운로드 : 데이터처리-중복제거.xlsx

 

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

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

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

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

엑셀에서 1개의 열에서 중복 제거하기

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

 

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

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

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

엑셀에서 1개의 열에서 중복 제거하기

 

아래와 같이 [중복 값] 대화상자가 나타납니다.

⑤ [드롭다운 리스트]에서 '중복'을 선택하고
⑥ [적용할 서식]에서 '진한 빨강 텍스트가 있는 연한 빨강 채우기'를 선택하고 확인 버튼을 누르면

엑셀에서 1개의 열에서 중복 제거하기

 

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

엑셀에서 1개의 열에서 중복 제거하기

 

2) 중복 데이터 제거

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

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

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

엑셀에서 1개의 열에서 중복 제거하기

 

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

③ [중복된 항목 제거 경고] 대화상자에서 '선택 영역 확장'을 선택하고

④ <중복된 항목 제거> 버튼을 누르면

엑셀에서 1개의 열에서 중복 제거하기

 

다음과 같이 [중복값 제거] 대화상자 나타납니다.

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

엑셀에서 1개의 열에서 중복 제거하기

 

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

엑셀에서 1개의 열에서 중복 제거하기

 

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

엑셀에서 1개의 열에서 중복 제거하기

 

[주의!] '선택 영역 확장"을 하지 않고 '현재 선택 영역으로 정렬'을 선택하고

엑셀에서 1개의 열에서 중복 제거하기

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

엑셀에서 1개의 열에서 중복 제거하기

B열에서만 중복을 제거했으므로 아래와 같이 중복 제거한 후에 빈자리가 맨 아래에 생기고 다른 열은 아무런 변화가 없습니다. 열을 하나만 지정하고 제거하는 기능은 특정 열의 중복만 제거하고 싶을 때만 사용해야 합니다.

엑셀에서 1개의 열에서 중복 제거하기

 

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

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

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

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

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

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

 

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

중복을 제거하기 전에 먼저 어떤 데이터가 중복인지 찾아서 확인하는 것이 좋습니다. 위의 예에서는  1개의 열에서 중복된 행을 찾을 때  조건부 서식을 이용했는데  2개 이상의 열이 동시에 중복인 것은 조건부 서식으로는 표시할 수 없습니다. 조건부 서식은 단순히 1개의 열단위로 중복인 것만 보여줄 뿐입니다.

따라서 다음과 같이 수식으로 중복인 것을 찾아야 합니다.

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

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

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

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

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

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

2개의 이상의 열에서 중복된 값 수식으로 찾기

 

2) 중복 데이터 제거

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

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

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

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

 

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

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

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

아래와 같은 처리 메시지가 나타나면 ⑤ <확인> 버튼을 누릅니다.

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

 

최종적으로 No.4, No.11인 행이 삭제된 것을 확인할 수 있습니다.

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

 

참고사이트- 데이터를 정리하는 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

 

 

[ 엑셀 수식 강좌 ]

답글 남기기

이메일 주소는 공개되지 않습니다.