엑셀 조건부 서식과 색상 필터를 이용하여 중복 데이터 찾기

 

 

포스트 목차.

• 01. 관련 포스트
• 02. 중복데이터 표기를 위한 조건부 서식
• 03. 로또 데이터를 이용한 예제

 

본 포스트에서는 조건부서식, COUNTIF함수, 색상 필터를 이용하여, 중복데이터를 찾는 방법에 대해 소개한다. 이전 포스트에서 다룬 로또번호 생성기에서 생성한 로또 번호 중에 중복된 숫자를 찾는 예제를 소개한다.

01. 관련 포스트

(1) COUNTIF 함수 사용법

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

 

(2) 조건부 서식 기본

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

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

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

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

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

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

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

(6) 엑셀 조건부서식/COUNTIF 함수로 중복데이터 표시하기

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

 

(7) 로또 번호 생성기 (VBA)

Excel 매크로 (VBA) - 022. 로또 번호 생성기를 만들어 보자

 

(8) 로또 번호 생성기

엑셀로 로또 번호 생성기를 만들어 보자

 

02. 중복데이터 표기를 위한 조건부 서식

중복 데이터에 대해 셀의 채우기 색을 다르게 설정하는 방법에 대해서 더 자세히 알고 싶다면 위의 (6)번 포스트를 참고하기 바란다.  

 

다음과 같은 데이터에서 중복데이터에 채우기 색을 다르게 설정하는 방법을 간단하게 소개한다.

 

(1) 서식을 지정할 데이터 영역 선택

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

 (3) COUNTIF를 이용한 수식 작성

=COUNTIF($A2:$A$10,A2)>1

 (4) 서식 지정 (채우기 색 설정)

 (5) 서식적용

 (6) 필터 적용  

 (6) 셀 색 기준 필터 적용

 (7) 결과확인

위의 작업은 데이터의 양이 적기 때문에 필터까지 사용할 이유는 없을 것이다. 그러나 데이터가 한페이지를 넘어가는 순간 조건부 서식을 사용하여 중복데이터를 다른 색으로 표기한다해도, 어떤 데이터가 중복인지를 찾는 것은 힘든 작업이 된다. 

조건부 서식에서 사용한 배경색을, 필터로 사용하면 중복데이터 또는 원하는 데이터를 빨리 찾는 것이 가능하다.

 

03. 로또 데이터를 이용한 예제

위에서 소개한 방법은 데이터가 많은 경우에도 활용할 수 있다.  예제로 사용할 데이터는 관련 포스트 (7) 로또 번호 생성기 (VBA)를 통해 생성한 약 1만개의 로또 번호이다. 

 

 

로또 번호가 중복되었다는 것은 6개의 숫자가 모두 일치한다는 것의 의미한다. 로또 번호를 유니크하게 식별할 수 있는 ID를 하나 만든 후에, ID가 중복된다면, 로또 번호가 중복된 거싱라고 판단할 수 있을 것이다.

 

(1) 중복 데이터 확인을 위한 ID 만들기

ID를 만드는 방법은 여러가지가 있을 수 있다. 

위의 로또 번호는 동일 세트안에서는 오름차순으로 정렬되어 있기 때문에, 번호 사이에 기호를 넣어서 "번호1_번호2_번호3_번호4_번호5_번호6" 과 같은 형태로 ID를 만들어도 된다.

 

여기서는 각각의 로또 번호가 45보다 크게 차이가 날 수 없다는 사실을 이용하여, 각각의 자리에 50의 제곱수를 곱해서 ID를 만들어보려 한다.

 

위의 수식처럼, 각각의 자리에 50의 제곱수를 곱하여, 50진수를 만들면, 6개의 숫자를 중복되지 않게 표기할 수 있다.

채우기 핸들이나, 복사기능을 이용하여 모든 데이터에 대해 ID를 만들어준다.

 

(2) 필터 적용하기

 

(3) ID열에 조건부 서식 입력하기

  - COUNTIF를 이용한 조건부 서식을 적용한다.

(4) ID열에 색 기준 필터 적용

(5) 결과확인

로또 번호 생성기로 생성한 숫자 중에 중복된 숫자를 바로 검색할 수 있다.