엑셀 중복값 찾기 countif 함수 사용 추출

세상에 나랑 똑같이 생긴 사람!! 도플갱어를 보면 죽는 다는 속설이 있습니다.

물론 과학적으로는 있을 수 없는 이야기지만 영화나 만화의 소재로 자주 쓰입니다.

엑셀에서도 중복 데이터는 죽음을 불러오지는 않지만 누군가를 죽이고 싶을 만큼 짜증이 나게 만들기도 합니다. 

범죄자가 되기 전에 중복 데이터를 찾아 내는 방법에 대해 알아보게습니다.

엑셀 데이터 중복값 찾기

하윤 서윤 서연 하은 지유

2017년 1월에서 9월 여아출생자 기준으로 가장 인기가 많은 이름 순위라고 합니다. 1위인 하윤은 2000명가까이 된다고 하니 성을 감안하더라도 엄청난 중복이 발생합니다.

이 아이들이 학교에 다니게 되어 시험을 쳐서 아래와 같이 점수를 받았습니다.

중복되는 갯수 세기

첫번째로는 각 이름별로 중복되는 횟수를 알아내는 방법입니다.

여기에 사용되는 함수는 COUNTIF 함수인데 COUNTIF 말그대로 IF한 조건을 만족하는 셀을 COUNT하라는 함수로 자세한 내용은 아래 링크를 참고하기 바랍니다.

엑셀 함수 정리 COUNTIF 함수 조건 충족 셀의 개수

엑셀 함수 정리 COUNTIF 함수 조건 충족 셀의 개수

각 이름별로 중복 횟수 값을 표시할 셀에서 =COUNTIF($B$3:$B$18,E3)와 같이 입력 후 끌어서 붙여넣기하면 각 이름별로 중복 횟수를 알아 낼 수 있습니다. 체크할 점은 범위는 절대경로 상대경로 변환 단축키 F4를 눌러서 $B$3:$B$18와 같이 절대경로로 바꾸어 주어야 끌어서 붙여넣기 할 때 오류가 나지 않습니다.

중복 여부를 표시하기

다음은 중복 횟수가 아니라 중복되는지 아닌지를 판단하여 중복된 데이터라면 중복이라고 표시되도록 하는 방법입니다. 

첫번째의 중복되는 갯수 세는 방법을 소개한 이유가 바로 중복 여부 표시 이해를 쉽게 하기 위해서 인데 중복된다는 것은 위의 결과처럼 항상 1보다 크게 됩니다.  다시 말해 IF 결과값 > 1 이면 "중복" 이라고 표시를 해주면 된다는 겁니다. 

위와 같이 중복 여부를 표시할 셀에 =IF(COUNTIF($B$3:$B$18,B3)>1,"중복")와 같이 입력 후 끌어서 채우기를 하면 중복된 이름을 가진 셀은 중복이라고 표시 됩니다. 아쉽게도 위에서는 전부 "중복"으로 표시 되네요...

중복 제외 데이터의 개수 구하기

위에서 3행에서 18행까지 16개의 데이터가 있지만 실제로 중복을 제외한 순수 데이터의 갯수가 몇개가 되는지 알려면 SUMPRODUCT 함수를 같이 사용해주면 됩니다.

위와 같이 셀에 =SUMPRODUCT(1/COUNTIF(B3:B18,B3:B18))와 같이 입력하면 중복을 제외하면 <하윤, 서윤, 서연, 하은, 지유> 5개의 순수 데이터가 있음을 알 수 있습니다.

중복 데이터 합, 평균 구하기

중복된 데이터 별로 값들을 가지고 데이터 작업을 해야 할 경우가 있습니다.

예를 들자면 "하윤" 이들의 점수의 합계, 평균 등을 계산하고자 한다면 골치아프게 수식을 사용하지 않고 엑셀에서 제공하는 기본 기능을 가지고 간단히 구할 수 있습니다.

[데이터] 탭의 [데이터 도구] 항목에는 [통합]이라는 기능이 있습니다.

[통합] 도구 창을 열어 함수(F)에서 합계, 개수, 평균 등을 선택 할 수 있으며 참조(R)에 해당 범위를 선택 한 뒤 사용할 레이블 왼쪽 열(L)을 선택 한 뒤 확인을 하면 위와 같이 각 중복 데이터별로 합계, 개수, 평균 등의 값을 간단히 알아 낼 수 있습니다.