엑셀] 중복값 갯수 세기

데이터에서 중복값을 제거하고 순수한 개별값의 수를 구하는 경우가 있다. 

 

아래 그림과 같은 데이터에서 출신 대학교가 다양한 경우 모두 몇 개의 대학교 출신들이 모인걸까??

 

 

 

 

간단하게 구하려면 출신대학교 데이터 열만 복사해서 [데이터 탭 - 중복된 항목 제거]를 클릭하면 중복된 데이터를 삭제하고 순수 데이터의 종류가 몇 개인지 알려준다. 

 

 

 

 

이렇게 간단하게 구하고 끝내면 좋겠지만 이걸 수식으로 계산해서 이 값을 다른 계산에 반영해야 하는 경우라면 어떻게든 계산해 내야 한다. ^^;;

아래 그림처럼 날짜 데이터가 어떤 날은 여러개이고, 어떤 날은 데이터가 없다면 총 며칠의 데이터에 해당하는지 구해서 다른 계산에 적용해야 할 경우를 생각해볼 수도 있겠다. 

 

 

먼저 결론부터 이야기하면 다음의 배열수식을 이용해서 중복값을 제외한 개별 데이터의 개수를 구할 수 있다. 

{=SUM(1/COUNTIF(범위:범위))}

 

 

이제 천천히 저 수식에 대해 알아보자. 

 

countif함수는 첫번째 인수 범위에서 두번째 인수에 해당하는 값이 몇 개인지 찾는 함수이다. 

아래 그림처럼 4종류의 과일이 각각 있을 때 countif함수는 전체 범위에서 해당 값이 몇 개씩 포함되어 있는지 반환한다. 

 

 

 

 

이 반환값을 모수로 하는 분수 형태로 만들면 다음과 같다. 

 

 

 

 

 

이 계산은 같은 항목의 합이 1이 된다. 

즉, 2개의 중복값이 있는 사과의 경우 0.5*2=1이 되고, 

4개의 중복값이 있는 수박의 경우 0.25*4=1이 된다. 

 

결국 이 값을 모두 합하면 중복을 제거한 개별 과일의 종류 수인 4가 된다. 

 

 

 

 

 

이 계산과정을 배열수식으로 한 번에 계산하면 제일 위에 나왔던 식이 된다. 

(배열수식은 수식을 입력한 후 Ctrl + Shift + Enter를 입력해야 한다)

 

 

 

 

덧

 

위 수식의 경우 목록에 빈 셀이 있을 경우 #DIV/0 에러가 나오기 때문에 다음과 같이 수식을 수정해준다. 

 

{=sum(if(범위<>"", 1/countif(범위:범위)))}

 

 

 

 

 

 

 

< 관련 블로그 글>

 

- 엑셀] 갯수 세기 - COUNT, COUNTA, COUNTIF, COUNTIFS

- 엑셀] 배열 수식의 기초적인 이해

- 엑셀] 배열수식으로 중복값(또는 동명이인) 찾기

- 엑셀] 중복값(중복된 행) 찾기