엑셀 중복값 제거 후 고유값 개수 구하기 :: 엑셀 공식

엑셀 중복값 제거 후 고유값 개수 카운트 공식

=SUMPRODUCT(1/COUNTIF(범위,범위))
' 범위에 빈칸이 있을 경우 #DIV/0! 오류를 반환하므로, 그럴 경우 아래 적어드린 변환 공식을 사용합니다.

오피스 365 사용자

=COUNTA(UNIQUE(범위))
' SUMPRODUCT 공식보다 더욱 빠르게 동작하며, 다중조건을 만족하는 고유값의 개수를 셀 수 있습니다.

인수	설명
범위	중복값 제거 후 고유값 개수를 셀 범위입니다.

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

아래 상황을 가정하겠습니다. 총 20명의 직원이 있는데, 각 직원명에서 '성'의 고유 개수를 세고 싶습니다. 그럴 경우 아래 공식을 사용할 수 있습니다.

또는 이런 상황에서도 사용할 수 있습니다. 아래와 같이 2000여개가 넘는 거래목록이 있는데, 이 거래목록에는 여러 거래처가 중복입력되어 있습니다. 이럴 경우, 중복값을 제거한 고유 거래처의 개수를 계산할 때에도 아래 공식을 사용할 수 있습니다.

COUNTIF 함수를 사용하여 각 항목별 개수를 계산합니다.
= COUNTIF({김, 이, 김, 박, 이, 김}, {김, 이, 김, 박, 이, 김})
= {3, 2, 3, 1, 2, 3}
반환된 배열이 분모로 입력되면서, SUMPRODUCT(1/{배열})이 계산됩니다.
= SUMPRODUCT(1/COUNTIF(범위,범위))
= SUMPRODUCT(1/{3, 2, 3, 1, 2, 3}}
= 1/3 + 1/2 + 1/3 + 1/1 + 1/2 + 1/3
= 3
만약 범위에 빈칸이 있을경우, 분모에 '0'이 입력되어 해당 공식은 #DIV/0! 오류를 반환합니다.
= SUMPRODUCT(1/{3, 2, 3, "", 2, 3}}
= 1/3 + 1/2 + 1/3 + #DIV/0! + 1/2 + 1/3
= #DIV/0!
따라서 범위에 빈칸이 있을 경우 변환공식을 사용하여 문제를 해결합니다.
= SUMPRODUCT(({3, 2, 3, "", 2, 3}}<>"")/COUNTIF({김, 이, 김, , 이, 김},{김, 이, 김, , 이, 김}&""))
= SUMPRODUCT( {TRUE, TRUE, TRUE, FALSE, TRUE, TRUE}/{3, 2, 3, 0, 2, 3}
= SUMPRODUCT( {1, 1, 1, 0, 1, 1}/{3, 2, 3, 0, 2, 3} )
= 1/3 + 1/2 + 1/3 + 0 + 1/2 + 1/3
= 2

중복값 제거 후 고유값의 개수를 셀 범위안에 빈칸이 있을 경우, 공식은 #DIV/0!를 반환합니다. 그럴경우, 아래 변환공식을 사용하여 문제를 해결합니다.

범위에 빈칸이 있는 경우

=SUMPRODUCT(1/COUNTIF(범위,범위&""))
' 범위에 빈칸이 있을 경우 사용합니다. 단, 빈칸도 고유값으로 계산됩니다.
' 예) {김, 김, 이, , 이} ==> 3을 반환합니다.

범위에 빈칸이 있고 빈칸을 고유값 개수에서 제외하고 싶을 경우

=SUMPRODUCT((범위<>"")/COUNTIF(범위,범위&""))
' 범위에 빈칸이 있고, 빈칸이 고유값에 포함되지 않아야 할 경우 사용합니다.
' 예) {김, 김, 이, , 이} ==> 2를 반환합니다.

4.8 33 투표

게시글평점