엑셀 조건부 서식과 COUNTIF를 이용하여 중복 데이터 표시하기

포스트 목차.

• 01. 관련 포스트
• 02. 기존 보유항목에서 중복 여부 확인하기
• 03. 목록 내의 중복 데이터 확인하기

 

본 포스트에서는 조건부 서식과 COUNIF 함수를 이용하여, 중복데이터를 검출하는 방법에 대해 소개한다. 

01. 관련 포스트

이전에 작성한 포스트 중에  연관성이 있는 포스트는 다음과 같다.

 

(1) COUNTIF 함수 사용법

SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수

 

(2) 조건부 서식 기본

엑셀 조건부 서식으로 특정 셀의 서식을 변경하기

(3) 조건부 서식 상위/하위 값들에 다른 서식 적용하기

엑셀 조건부 서식으로 상위/하위 값들에 다른 서식 적용하기

(4) 조건부 서식에 수식 사용하기

엑셀 조건부 서식에 수식 사용하기

(5) 엑셀로 도수분포표 작성하기

Excel 지식인 011. 엑셀을 이용하여 도수분포표 작성하기

 

02. 기존 보유항목에서 중복 여부 확인하기

새로운 데이터를 추가하려고 할 때, 이미 가지고 있는 데이터인지 확인하는 작업이 종종 요구된다. 예를 들어 새로운 물품을 구매할 때, 기존 보유 여부를 확인하고 싶은 일이 발생할 수 있다. 

 

다음과 같은 데이터가 있을 때, 특정 제품명을 입력하고, 해당 제품명이 기 존재하는 지를 알아보는 작업을 조건부 서식을 이용하여 수행해보자. 

 

추가 구매 건을 F열에 입력하고 D열에 입력되어 있는 제품명과 동일한 제품명을 입력했을 때, 특정 서식으로 변경하는 조건부 서식을 활용해보자. 

적용할 서식: 진한 빨간색 글자/볼드/옅은 빨간색 배경

 

(1) 조건부 서식을 적용할 영역을 선택한다.

여기서는 F열에만 조건부 서식을 지정할 예정이므로 F열을 선택한다.

 

(2) 조건부 서식 > 새규칙 > 수식을 사용하여 서식을 지정할 셀 결정

 

(3) COUNTIF를 이용하여 수식을 작성한다.

=COUNTIF($D$3:$D$29,$F3)>=2

 

F열에 있는 데이터가 D3:D29사이에 2개 이상 존재한다면 서식이 적용된다. 

(4) 서식을 지정한다.

완성된 설정 화면은 다음과 같다.

확인을 눌러서 조건부 서식을 적용한다.

 

(5) 데이터를 입력한다. 

 

다음 데이터를 입력했을 때, 조건부서식이 적용되는지를 확인해보자.

 

입력할 데이터

입력 결과는 다음과 같다.

리스트에 이미 존재하는 타타콘과 드래곤퀘스트11에서 우리가 설정한 서식이 적용되는 것을 확인할 수 있다. 

 

 

03. 목록 내의 중복 데이터 확인하기

이번에는 작성하는 목록 내에서 중복데이터가 있을 때 다른 서식을 적용해보자. 문제를 좀 더 복잡하게 하기 위해서 다음과 같은 조건을 추가했다.

 - 중복값이 2개~4개일 때 빨간색 배경 볼드

 - 중복값이 5개 이상일 때 노란색 배경 볼드

 

 작업할 데이터는 위와 같고, A열에 조건부서식을 적용하려고 한다.

 

(1) 조건부 서식을 적용할 영역을 선택한다.

 

(2) 조건부 서식 > 새규칙 > 수식을 사용하여 서식을 지정할 셀 결정

 

(3) 조건부 서식 1 추가 (중복값이 2개~4개일 때 빨간색 배경 볼드)

=AND(COUNTIF($A$2:$A$23,$A2)>=2, COUNTIF($A$2:$A$23,$A2)<=4)

 

A열에 있는 데이터가 A2:A23사이에 2개 이상 ~ 4개 이하 존재한다면, 서식 적용

(4) 조건부 서식 2추가 (중복값이 5개일 때 노란색 배경 볼드)

=COUNTIF($A$2:$A$23,$A2)>=5

A열에 있는 데이터가 A2:A23사이에 5개 이상 존재한다면, 서식 적용

 
(5) 조건부 서식을 적용한 결과는 다음과 같다.

동일한 데이터가 2개씩 존재하는 "용과같이 극", "드래곤퀘스트11", "닌텐도 스위치 (동물의숲 에디션)"의 경우 빨간색 배경과 볼드체가 적용된 것을 알 수 있고, 동일한 데이터가 5개 존재하는 "용과같이 7"의 경우 노란색 배경과 볼드체가 적용된 것을 확인할 수 있다.