엑셀 Excel 다중 조건 중복 데이터 찾아서 삭제하는 방법

데이터베이스에서 엑셀로 백업을 받았거나 많은 양의 자료를 가공할 때 가장 먼저 해야 할 일이 중복 데이터 처리 입니다. 눈으로 구분할 수 있는 한계를 넘어서면 엑셀에서 제공하는 함수로 한번에 추출해야 합니다. 필터 기능은 함수로 추출한 중복 데이터를 한번에 제거하는 용도로 사용할 것입니다.

 

 

 

▼ 먼저 자료에서 중복 데이터를 판단하기 위한 수식을 만들어 보겠습니다. 자료에서 “부서”와 “이름”이 동시에 같은 데이터는 중복으로 간주하겠습니다. 그럼 두 조건을 동시에 만족하는 데이터 개수가 1개 이상이면 중복이 되겠죠. 동일한 데이터의 개수를 세는 COUNTIF 함수를 사용합니다. 드래그해서 모든 셀에 수식을 한번에 적용하기 위해서는 첫 번째 인수의 영역은 절대값으로 해야 합니다.

 

 

여기서 중요한 것은 $B$5:$B5 에서 종료를 가리키는 $B5 입니다. 영역의 끝이 아닌 시작 지점을 가리킵니다. 그리고 $B 만 절대 주소이고 5 는 아래로 내려가면서 범위를 증가해야 되기 때문에 절대 주소가 아닙니다. 그래야 중복되는 시점에서 데이터에 표시가 가능하기 때문입니다.

 

▼ “부서” 의 중복 개수를 판단했다면 “성명” 중복 체크를 위한 수식도 만들어야겠죠. COUNTIF 의 첫 번째 인수인 $C$5:$C5 범위에서 $C5 처럼 행 번호는 절대 주소로 설정하지 않습니다. 

 

▼ 부서와 성명 모두 같은 데이터를 찾기 위해 부서에서 찾은 중복 개수와 성명에서 찾은 개수 모두 1 이상이어야 합니다. 두 조건을 만족해야 하므로 AND 조건이 되겠죠. AND 함수를 사용해서 두 조건 모두 참인 것은 중복이 되는 것입니다. 수식에서 TRUE 를 반환하면 중복인 것입니다.

 

=AND(COUNTIF($B$5:$B5,B5)>1,COUNTIF($C$5:$C5,C5)>1)

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 함수 MATCH, INDEX 이용해서 행과 열에 일치하는 데이터 찾기 ▶ 엑셀 Excel 데이터에서 VLOOKUP 함수 이용해서 자료 찾는 방법 ▶ 엑셀 Excel 조건에 맞는 행의 다른 값들 가져오기, VLOOKUP 함수 사용 ▶ 엑셀 Excel VLOOKUP 함수, 원하는 값 찾을 사용 ▶ 엑셀 Excel 여러 셀과 비교해서 다른 값이 있는 데이터 빠르게 표시하기

 

 

 

 

▼ 중복 여부를 판단하는 열에 TRUE/FALSE 로 표시하니 알아 보기 힘들죠. IF 함수를 사용해서 TRUE 일 때만 “중복”이라고 표시했습니다. 

=IF(AND(COUNTIF($B$5:$B5,B5)>1,COUNTIF($C$5:$C5,C5)>1), "중복", "")

 

 

▼ 이제 “중복” 으로 표시된 데이터를 한번에 삭제해야 합니다. 이것은 필터 기능을 이용해서 처리할 것입니다. 상단 제목 부분을 선택하고 [홈] 탭 > [정렬 및 필터] > [필터 리본] 메뉴를 선택합니다. 

▼ “중복” 열에서 화살표를 눌러 “중복” 필터 값을 선택합니다. 그럼 중복으로 판단된 데이터만 남게 되겠죠.

 

▼ 그림과 같이 걸러 진 중복 데이터는 드래그해서 선택한 후 오른 마우스를 눌러 [행 삭제] 메뉴를 선택해 주시면 됩니다. 이상으로 중복 데이터를 걸러내고 삭제하는 방법에 대해 알아 보았습니다.

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 함수 MATCH, INDEX 이용해서 행과 열에 일치하는 데이터 찾기 ▶ 엑셀 Excel 데이터에서 VLOOKUP 함수 이용해서 자료 찾는 방법 ▶ 엑셀 Excel 조건에 맞는 행의 다른 값들 가져오기, VLOOKUP 함수 사용 ▶ 엑셀 Excel VLOOKUP 함수, 원하는 값 찾을 사용 ▶ 엑셀 Excel 여러 셀과 비교해서 다른 값이 있는 데이터 빠르게 표시하기