[엑셀 실무] 조건부 서식을 활용한 중복값 표시 방법

이번에는 엑셀에서 중복값을 삭제하고 유일값만 남기는 것이 아닌 중복값을 다른 값과는 다르게 표시하여 사용자가 쉽게 중복값임을 파악할 수 있게 하는 방법입니다.

엑셀의 조건부 서식을 활용합니다. 조건부 서식은 조건을 만족하는 값에만 설정한 서식을 적용하는 것입니다. 조건부 서식도 스타일의 조건부 서식 메뉴에서 마우스 클릭으로 적용할 수 있는 간단한 방법이 있고 이번 게시물처럼 수식을 사용하는 방법이 있습니다. 수식을 사용하는 엑셀 조건부 서식 방법이 보다 어렵긴 하지만 보다 다양한 원하는 조건을 구분하는데 필요합니다. 수식활용 조건부서식으로 중복값을 표시해 봅니다.

2. 엑셀의 조건부 서식을 활용하여 중복값을 표시합니다.

오피스 엑셀 2007 이상의 경우는 이렇게 값을 선택하고 홈 탭 > 스타일 그룹 > 조건부서식 > 셀 강조 규칙 > 중복값 을 선택하시면 중복된 값에 서식을 주어 쉽게 확인하실 수 있습니다. 하지만 이는 선택범위의 모든 셀을 각각 비교하고 중복체크합니다. 아래와 값이 행단위로 중복값을 체크해야 할 경우는 수식을 사용하여야 합니다.

[그림4] 엑셀 '조건부 서식' 활용 중복값 표시

1) 서식 적용될 범위를 선택하고 조건부 서식의 '새 규칙' 을 선택합니다.
2) '새 규칙' 작성창이 뜹니다. 편집창에서 수식을 사용한 규칙을 선택합니다.

[그림5] 엑셀 중복값 찾기 조건부 서식과 수식

3) 다음의 수식을 지정하고 '서식...' 버튼을 클릭하여 중복값에만 적용할 서식을 설정합니다. ( 셀 서식 창이 뜹니다 )

=COUNTIF($B$4:$B$11, $B4)>1
=COUNTIF(중복체크범위, 중복체크시작셀)>1

노트1 : 위의 수식을 풀어 봅니다. COUNTIF 함수는 조건을 만족하는 값의 수를 구하는 함수입니다. B4:B11의 범위에서 B4 셀의 값의 수를 찾습니다. 중복체크범위가 절대참조되어 있고 B4 셀이 혼합참조 되어 있기에 두번째 인수는 B4~B11 까지 바뀌면서 중복 체크되는 것입니다. 그리고 뒤의 '>1' 의 추가로 전체 수식은 논리 수식이 되어 참과 거짓 을 최종 반환하는 것입니다. 참이라면 지정한 서식이 적용되는 것이지요.

엑셀 조건을 만족하는 수 COUNTIF 함수 : http://secstart.tistory.com/168
엑셀 상대 참조, 절대참조, 혼합참조 : http://secstart.tistory.com/154
엑셀 논리 연산 수식 : http://secstart.tistory.com/565

[그림6] 엑셀 조건부 서식 규칙 관리자

4) '서식 규칙 편집' 창에서 '확인' 버튼을 클릭합니다. '조건부 서식 규칙 관리자' 창이 뜹니다.
5) 필요에 따라 규칙 편집으로 편집하거나 서식 적용대상 범위를 수정할 수 있습니다.

[그림7] 엑셀 조건부 서식 중복값 표시 완료

3번에서 셀 서식의 글꼴을 파랑으로 바꿨습니다. 과정명 '엑셀' 인 두 행이 모두 파랑으로 글꼴색 변경되었습니다. 위의 중복을 찾는 엑셀 수식은 어떤 데이터에 적용하여도 동일한 효과를 발휘합니다. 위의 엑셀 COUNTIF 함수식은 기억해 두시면 편리할 것입니다.

엑셀 조건부 서식을 잘 활용한다면 보다 쉬운 엑셀 작업 화면을 구성할 수 있습니다. 중복 데이터들이 파란색으로 표시되기에 한 눈에 해당 데이터의 중복을 확인 할 수 있는 것입니다. 활용해 보시기 바랍니다.

3. 엑셀 데이터 유효성 검사로 중복 입력을 막는 방법 : http://secstart.tistory.com/653

마늘빵.