[엑셀] 중복값 제외 한 고유 데이터 카운트 하기 (배열수식)

수 많은 데이터에서 서로 중복되지 않은 순수 데이터의 개수를 세고 싶을때 어떤 함수를 쓸까요?

아쉽게도 엑셀에서 이런 함수는 지원하고 있지 않습니다. 그렇다고 셀 수 없느냐? 아니죠!

배열수식을 이용하면 충분히 중복된 데이터는 추려내고 각각의 고유한 데이터의 개수를 셀수 있습니다.

※ 배열수식 만들기

수식 입력 후 Ctrl + Shift + Enter 합니다. 그러면 수식 {=수식} 처럼 처음과 끝에 대괄호 { }가 생성됩니다.

중복하지 않은 고유 데이터 카운트

품명에는 귤, 딸기, 배, 배, 사과, 사과, 산딸기, 수박, 수박, 수박 이렇게 있습니다. 이중 일부는 서로 같은 값이 존재하죠. 이경우 데이터에서 중복된 값을 제거하지 않고 그 값을 카운트 해보겠습니다. 결과는 귤, 딸기, 배, 사과, 산딸기, 수박 이렇게 6개 입니다.

☞ 수식설명

1번 : {=SUM(1/COUNTIF($C$4:$C$13,$C$4:$C$13))}

1. COUNTIF 함수 : 품명에 찾을 값의 개수를 반환

2. 1/COUNTIF : COUNTIF의 결과를 분모에 둠

3. SUM : 1/COUNTIF 의 값을 더함

2번 : =SUMPRODUCT(1/COUNTIF(C4:C13,C4:C13))

1. 각 사용함수의 사용은 1번과 같음

2. SUMPRODUCT 함수 : SUMPRODUCT 함수는 배열함수로 SUM과 같이 Ctrl + Shift + Enter 가 필요없이 함수 사용

※ 배열함수 SUMPRODUCT 함수는 그 자체가 배열함수로 { 배열수식 } 을 입력하지 않아도 됨(단, 수식에 따른 예외 있음)

배열수식 이해하기

수식 {=SUM(1/COUNTIF($C$4:$C$13,$C$4:$C$13))} 을 하나씩 분해 하면 위와 같습니다.

☞ 배열수식 설명

1. COUNTIF 함수

COUNTIF($C$4:$C$13,C4) : range(품명) 에서 각 셀의 개수와 일치하는 셀의 개수

COUNTIF($C$4:$C$13,$C$4:$C$13) : COUNTIF($C$4:$C$13,C4) ~ COUNTIF($C$4:$C$13,C13) 까지의 배열수식

2. 1 / COUNT 함수

위 그림에서 보는 바와 같이 1/1, 1/1, 1/2, 1/2 .... 1/3, 1/3, 1/3 의 값

3. SUM 함수

1/COUNT 의 결과를 모두 합 =  1/1 + 1/1 + 1/2 + 1/2 + .... + 1/3 + 1/3 + 1/3 = 6

※ 배열수식은 이해도 어렵고 설명도 어렵습니다. 개인적으로 필요한 배열수식을 만들 필요가 있을때는 위와 같이 각각의 값을 배열하여 역으로 계산 해보는 것도 좋은 방법입니다.

첨부파일을 참조하세요 ^^

관련글 보기

수식으로 정렬(배열수식)		중복값 제거(COUNTIF함수)

첨부파일 보기

중복값 제외 카운트.xlsx