엑셀 조건부서식과 피벗 테이블을 이용한 중복값 찾기

오늘은 조건부 서식과 피벗 테이블을 이용해
중복값을 찾는 법을 배워보겠습니다.

저번에는 중복값을 제거하는 방법을 말씀드렸는데요,
중복값 제거는 중복값을 제거해주지 찾기에는 어려움이 있어서,
오늘은 중복값을 바로 삭제하지 않고 '찾기'에 중점을 맞추어서
진행해보도록 하겠습니다.

 

중복값 예제.xlsx

0.01MB

 

조건부 서식을 이용한 중복값 찾기

 

1. 위의 raw 데이터에 대해서 중복되는 값을 조건부 서식을 이용해서 찾아보겠습니다.

 

중복되는 값을 찾을 테이블을 지정한 후,

홈 > 조건부 서식 > 셀 강조 규칙 > 중복 값 으로 들어가 주세요.

 

여기서 중복되는 값을 찾을 테이블의 경우,
관할구의 중복을 찾고 싶으시다면 B1:B35를 선택하시고,
담당자가 중복되는 것을 찾고 싶다면 C1:C35를 선택해주세요.

저는 관할구가 몇개 중복되어 있는지 알아보기 위해
관할구를 선택해보겠습니다.

 

 

 

 

2. 조건부 서식을 이용해서 중복되는 값을 찾으면

이런 식으로 진한 빨간 텍스트와 연한 빨강으로 셀이 채워집니다.

 

만약 중복에 적용할 서식을 바꾸고 싶다면 ▼를 눌러서
사용자 지정 서식으로 바꿀 수도 있습니다.

어차피 중복을 찾는게 목적이니
저는 새로 서식을 적용하지 않고 확인을 눌러주었습니다.

 

 

 

3. 위처럼 색이 채워지고 나면 중복된 항목이 어디어디 있는지는 알겠는데,

문제는 어떤 값이 중복인지 알 수가 없습니다.

 

그래서 저는 B,C 열에 필터를 걸어주겠습니다.

 

 

필터의 ▼ 를 누르면 색을 기준으로도 필터링을 할 수 있습니다.

우리가 중복값일 경우 서식을 셀 배경은 연한 빨강 / 글꼴 텍스트는 진한 빨강으로 했기 때문에

셀 색과 글꼴 색 두가지로 필터를 할 수 있습니다.

 

이 테이터 테이블에는 서식을 준 것이 중복값 밖에 없기 때문에

각각 한가지 경우 밖에 나타나지 않지만,

만약 본인이 셀 서식을 추가한 것이 있을 경우에는 그 서식에 대한 색도 기준 필터에 나타납니다.

 

그러므로 만약 다른 셀 서식이 있다면 어떤 것이 중복값에 대한 서식이었는지 기억해두셔야 합니다.

 

저희의 중복값에 대한 셀 서식은 빨강이고, 저는 보기 쉽게 셀 색을 기준으로 필터해보겠습니다.

 

 

 

 

4. 그러면 이와 같이 중복된 값이 있는 것은

관악구, 마포구, 종로구, 용산구, 성북구, 노원구, 은평구 이렇게 7구 인 것을 알 수 있습니다.

 

 

이렇게 갯수가 몇개 안 될 경우에는 본인이 일일이 세어도 되고,

아니면 이전에 알려드린 '중복된 항목 제거'를 이용하면 어떤 값이 중복이 되었는지 알 수 있습니다.

 

 

 

피벗 테이블을 이용한 중복값 찾기

 

피벗 테이블은 중복되는 값의 갯수가 몇 개인지 알아내기에 유용합니다.

 

 

1. 데이터가 있는 셀을 클릭하거나 직접 지정한 후,

삽입 > 피벗 테이블 > 표 또는 범위를 선택 > 확인 을 눌러주세요.

 

데이터가 있는 셀을 클릭한 경우 엑셀에서 자동으로 지정해주는데,

자동으로 지정한 영역이 일치하지 않는 경우 오른쪽의 버튼을 클릭해 영역을 지정할 수 있습니다.

 

 

2. 그러면 아래와 같은 피벗 테이블이 생성됩니다.

 

 

피벗 테이블에서 열/행 레이블을 간단히 설명해드리자면 아래와 같습니다.

열/행 레이블은 데이터 머릿글을 열로 구성할지, 행으로 구성할지를 정하는 것입니다.

<열>

<행>

 

우리가 원하는 것은 "관할구"의 "중복 갯수"입니다.

 

기준이 되어야 할 레이블은 관할구이므로 행에 관할구,

우리가 값을 구해야 할 것도 관할구의 갯수이므로 값에 관할구를 넣습니다.

 

 

종종 피벗 테이블에 따라 값이 '개수'가 되어 있지 않은 경우가 있습니다.

이 경우에는 다음과 같이 조정할 수 있습니다.

 

(1) 값에 해당하는 ▼를 눌러 값 필드 설정을 눌러주세요.

(2) 그러면 값의 요약 기준이 뜨는데 여기서 본인이 원하는 값에 맞게 설정해주세요.
우리가 원하는 것은 '중복 갯수' 이므로 갯수를 체크하고 확인을 눌러줍니다.

 

 

3. 그러면 아래와 같이 개수가 나타납니다.

이를 보면 개수가 3개 중복되는 구는 관악구, 마포구, 은평구

2개가 중복되는 구는 노원구, 성북구, 용산구, 종로구 이렇게 되어 있음을 알 수 있습니다.

 

 

 

다음 포스팅에서 만나요~:)

 

 

2020/11/04 - 엑셀에서 중복값 제거하는 방법!

2020/10/29 - 내배내적 엑셀 - Weekday 함수를 이용해서 요일 표시하기

2020/10/28 - 내배내적 엑셀 - If 함수 중첩 사용하기

2020/10/21 - 내배내적 엑셀 - Sumif 함수(feat.특정 값만 더해보자)

2020/10/20 - 내배내적 엑셀 - Countif 함수(feat. 조건에 맞는 갯수를 세는 함수)

2020/10/15 - 처음보면 헷갈리는 절대참조와 상대참조 이해하기

2020/10/14 - 내배내적 엑셀 - index-match 함수 다중조건(중첩조건)