엑셀 중복값 제거 후 고유값 개수 카운트 공식
중복값 제거 후 고유값 개수 카운트 목차 바로가기
함수 공식
=SUMPRODUCT(1/COUNTIF(범위,범위))
' 범위에 빈칸이 있을 경우 #DIV/0! 오류를 반환하므로, 그럴 경우 아래 적어드린 변환 공식을 사용합니다.
' 범위에 빈칸이 있을 경우 #DIV/0! 오류를 반환하므로, 그럴 경우 아래 적어드린 변환 공식을 사용합니다.
오피스 365 사용자
=COUNTA(UNIQUE(범위))
' SUMPRODUCT 공식보다 더욱 빠르게 동작하며, 다중조건을 만족하는 고유값의 개수를 셀 수 있습니다.
' 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! 오류가 반환됩니다.
중복값 제거 후 고유값의 개수를 셀 범위안에 빈칸이 있을 경우, 공식은 #DIV/0!를 반환합니다. 그럴경우, 아래 변환공식을 사용하여 문제를 해결합니다.
범위에 빈칸이 있는 경우
=SUMPRODUCT(1/COUNTIF(범위,범위&""))
' 범위에 빈칸이 있을 경우 사용합니다. 단, 빈칸도 고유값으로 계산됩니다.
' 예) {김, 김, 이, , 이} ==> 3을 반환합니다.범위에 빈칸이 있고 빈칸을 고유값 개수에서 제외하고 싶을 경우
=SUMPRODUCT((범위<>"")/COUNTIF(범위,범위&""))
' 범위에 빈칸이 있고, 빈칸이 고유값에 포함되지 않아야 할 경우 사용합니다.
' 예) {김, 김, 이, , 이} ==> 2를 반환합니다. - COUNTIF 함수를 사용하여 각 항목별 개수를 계산합니다.
'공식의 동작원리' ④의 두번째 단락에서
= SUMPRODUCT( {TRUE, TRUE, TRUE, FALSE, TRUE, TRUE}/{3, 2, 3, 0, 2, 3}) 중
{3, 2, 3, 0, 2, 3}의 4번째 인수 '0'이 아니라 '1' 아닌가요?
제가 설명을 잘못 적어드렸습니다.
위 공식은 다중조건이 아니라 넓은 범위에서 고유값을 추출할 수 있습니다.
조건을 만족하는 고유값 개수를 세려면 아래 공식을 사용해보시겠어요?^^
질문 하나 드립니다.
UNIQUE, FILTER 수식을 사용하지 않고 다중조건을 만족하는 고유값의 개수를 세는 공식이 있을까요?
공식을
=SUMPRODUCT(1/COUNTIFS(범위1,범위1,범위2,범위2))
로 사용해보세요 ^^
저 해당 조건을 사용할 때 값이 없는 경우도 1로 카운트를 하는 경우가 있는데... 혹시 해결가능한 방법이 있을까요?
=SUMPRODUCT(1/(COUNTIFS(범위1,범위1,범위2,범위2)*(범위1<>"")*(범위2<>""))
왜 값이 없으면 1로 카운트되는지... 궁금하긴 하지만 일단
고민하다가 IF문을 써서
해당카운트에 개수가 0이면 0, 0아니면 아래 수식으로 계산하는 방식을 쓰고 있습니다. 답변 감사합니다. ^^
if(countifs(범위,범위1=조건1,범위2=조건2 )=0 , 0,Counta(unique(filter(범위,(범위1=조건1)*(범위2=조건2))
=SUMPRODUCT(1/COUNTIFS(범위1,범위1,범위2,범위2))
로 사용해보세요 ^^
=COUNTA(UNIQUE(범위))
'2020.12.01에 해당하는' 이라는 조건을 추가 반영하고 싶은데 어떻게 하면 될까요??
C열에 날짜가 있고, 카운트할 값은 D열에 있습니다.
매월1일에서 말일까지 데이터 중 중복을 제외한 고유 값을 카운트하려고 하는데 어떻게 구할 수 있을까요?
2016 버전을 사용하고 있어, unique, xfilter 함수를 추가했습니다.