엑셀에서 중복값 찾기는 어렵다기 보다는 정확히 이해하기가 까다로운 편이다. 우선 엑셀 조건부 서식에 대해 이해해야하고 COUNTIFS 함수가 어떻게 동작하는지와 절대주소와 상대주소에 대한 기초적인 이해도 필요하다. (다음링크를 참조하길 바랍니다.)
2015/06/11 - [엑셀] - 엑셀 조건부서식 사용방법
2015/07/15 - [엑셀] - 엑셀 COUNTIF 함수 조건에 따른 셀 개수 세기
2015/06/01 - [엑셀] - 컴퓨터활용능력 엑셀기초 수식,참조(절대주소,상대주소)
COUNTIFS 함수 예제
COUNTIFS( 셀범위, 값 )
COUNTIFS 함수의 두번째 인수에 특정 값을 정하고 이 값이 COUNTIFS 함수의 첫 번째 인수로 지정된 셀 범위 내에서 몇 개나 있는지를 세어주는 기능을 한다. 따라서 COUNTIFS 함수가 2이상을 반환한다면 해당 값은 지정된 셀 범위내에서 중복해서 나온다는 것을 알 수 있다. 이런 성질을 엑셀 중복값 찾기에 이용할 수 있다.
그런데 엑셀 표가 위와 같이 하나의 열로 이루어졌다기 보단 다수의 열로 이루어지는 것이 일반적이다. 따라서 중복값을 검사할 때 위 표와 같이 하나의 열에 대해서만 검사할지 아니면 다수의 열에 대해서 검사해야할지 결정해야 한다.
위 그림은 두 개의 열에 대한 중복값을 찾아서 주황색으로 표시한 것이다. B열과 C열에 있는 값이 모두 같은 경우에만 중복값으로 여겨지고 표시되는데 COUNTIFS 함수에는 추가된 열에 대한 부분만 추가적인 인수를 지정해 주기만 하면된다. 다음 내용에서 방법을 설명한다.
조건부 서식을 이용한 엑셀 중복값 찾기
▼우선 엑셀 중복값 찾기를 할 표의 셀 범위(여기에서는 B11:C19)를 선택하고 홈->[스타일]조건부서식에 가서 새 규칙을 선택한다.
새 서식 규칙에서 아래그림과 같이 "수식을 사용하여 서식을 지정할 셀 결정"을 선택을 한다.
수식을 넣는 칸에 넣을 수식은 다음과 같다.
=COUNTIFS($B$11:$B$19,$B11,$C$11:$C$19,$C11)>1
이 수식의 내용은 좀 살펴볼 필요가 있는데 해석은 잠시 뒤로 미루고 서식 버튼을 눌러서 중복값을 표시할 서식을 선택한다. 여기에서는 셀에 적용할 서식으로 채우기->주황색으로 하겠다.
이렇게 설정해 놓으면 표에 중복된 행들이 모두 주황색으로 표시될 것이다.
엑셀 중복값 찾기 수식 분석
이제 이 수식이 어떻게 적용되는지 분석할 필요가 있다. COUNTIFS 함수는 인수로 (범위, 찾는 값, 범위, 찾는 값, ........) 이렇게 범위와 찾는 값, 두개의 인수가 한 쌍을 이룬다. 위 수식에서 범위는 표의 각각의 열이고 찾는 값은 각 범위의 첫 번째 셀로 정해져 있다.
=COUNTIFS($B$11:$B$19,$B11, $C$11:$C$19,$C11)>1
우선 처음 조건부 서식을 적용하기 전에 표 전체를 지정했으므로 수식이 적용되는 셀들 역시 표의 모든 셀들이라는 점을 알아야 한다.
▼아마 여기서 드는 의문점은 찾는 값을 왜 각 열의 첫 번째 셀로 지정하냐는 것이다.
▼잘 살펴보면 범위주소는 모두 $가 붙어 절대주소로 되어있지만 찾을 값은 행에대해서는 상대주소로 $를 붙이지 않았다. 이렇게 하면 각각의 셀에 수식이 적용될 때 찾을 값이 해당 셀의 값이 되기 때문이다.
그리고 이 수식이 반환하는 값이 1보다 큰 경우에 셀 서식(주황색)이 적용되도록 조건부 서식이 지정된 것이다.
위 내용을 응용하면 중복값의 개수에 따라서 차별적으로 셀 서식을 정할 수도 있다.
엑셀 중복값 찾기는 어렵다기보다는 엑셀의 기본적인 내용을 정확히 이해하지 못하면 생각보다는 까다롭게 느껴질 수 있다.