엑셀] 배열수식으로 중복값(또는 동명이인) 찾기

엑셀에서 데이터를 관리할 때 필요한 "중복값 처리" 문제에 대해서 정리해본다. 

아래 그림과 같은 데이터가 있다고 가정해보자. 

연번만 다를뿐 부서, 직급, 직렬, 성명이 동일한 중복값도 있고, 성명은 같지만 부서 또는 직렬은 다른 동명이인도 있다. 

중복값 제거

메뉴에서 [데이터 탭 - 중복된 항목 제거]를 선택한다. 

[중복된 항목 제거 팝업 메뉴]에서 중복 값을 체크할 열을 지정해준다. 

여기서는 연번을 제외하고 모두 동일한 값일 경우를 체크했다. 

3개의 중복된 값이 제거되었다. 

그런데 이렇게 확 지워버리면 왠지 불안하지 않은가???  ^^;;

엑셀을 믿고(?) 작업할 수 있지만, 어떤 데이터값이 중복된 것인지 먼저 확인하고 삭제하는게 마음 편하지 않은가???? 

(트리플 A형이라 소심의 극치라 그런건지도.. )

조건부 서식 - 셀강조 규칙 - 중복값

[조건부 서식 - 중복값]을 사용하면 좋을듯 한데 위 표처럼 동명이인이 있는 경우는 조건부서식을 사용할 수 없다. 

표 전체를 Ctrl + A로 선택한 상태에서 [조건부 서식 - 셀 강조 규칙 - 중복값]을 선택하면 표에서 중복된 값이면 무조건 중복값으로 처리한다. 

(연번의 1,2,3도 직급 데이터의 1급, 2급, 3급과 중복되기 때문에 붉은 색으로 표시된다)

이름이 있는 E열만 범위로 지정해서 조건부 서식을 사용하면 동명이인을 포함해서 중복값으로 인식하게 된다. 

조건부 서식이 아닌 다른 방법으로 중복값을 표시해보자

배열수식으로 중복값 표시하기

중복값을 표시하기 위해 열 하나를 추가하고 맨 윗줄에 다음과 같이 수식을 입력하고 [Ctrl + Shift + Enter]로 배열수식으로 만들어준다. 

아래 수식은 B, C, D, E열의 값을 바로 윗줄의 값과 비교해서 모두 같을 경우 "중복"이라고 표시하는 함수이다.

=IF(AND(B2:E2=B1:E1), "중복", "")

배열수식이 만들어지면 자동채우기 핸들을 더블클릭해서 맨 아래까지 수식을 채워준다. 

부서명, 직급, 직렬, 성명이 같은 경우 "중복"으로 표시한다. 

덧. 

위에 나오는 배열수식으로 중복값을 표시하는 것은 B열~E열까지의 값이 한 행 위에 있는 B열~E열의 값과 모두 같은가를 조사하는 방식이기 때문에 사전에 정렬을 통해서 중복값이 위, 아래 행으로 배치되도록 만들어야 하는 문제가 있다. 

아래 그림처럼 서로 중복되는 행이 붙어있지 않고 떨어져있으면(1,3행이 중복이고, 3,6행이 중복) 위 배열 수식으로 중복값을 찾을 수 없다.

< 관련 블로그 이전 글 >

- 엑셀] 조건부 서식

- 엑셀] 배열 수식의 기초적인 이해

- 엑셀] 배열수식 활용 - and 조건과 or 조건으로 값 구하기

- 엑셀] 중복값(중복된 행) 찾기

- 엑셀] 중복값 갯수 세기