엑셀에서 데이터를 다루다 보면 동일한 값이 여러 번 등장하는 경우가 많습니다. 예를 들어 고객명단, 제품코드, 지역명, 거래처 목록 등에서 고유한 값의 개수만 세고 싶을 때가 있습니다. 단순히 COUNT나 COUNTA 함수를 사용하면 중복까지 포함해 계산되므로, 정확한 고유값 개수를 얻기 위해서는 중복을 제거하는 방식이 필요합니다.
기본적인 방법 1: 최신 버전 함수 활용
Office 365나 Excel 2021 이상 버전에서는 UNIQUE 함수와 COUNTA 함수를 조합하면 간단하게 중복을 제외한 개수를 구할 수 있습니다.
=COUNTA(UNIQUE(A2:A20))UNIQUE(A2:A20): 지정한 범위에서 중복을 제거한 고유값 목록을 반환합니다.COUNTA(...): 반환된 고유값 목록의 개수를 셉니다.
이 방법은 배열수식 입력이 필요 없고, 빈칸이 있어도 오류 없이 처리됩니다.
기본적인 방법 2: SUMPRODUCT + COUNTIF 배열수식
구버전 엑셀이나 UNIQUE 함수를 지원하지 않는 환경에서는 SUMPRODUCT와 COUNTIF를 조합한 배열수식을 사용할 수 있습니다.
=SUMPRODUCT(1/COUNTIF(A2:A20, A2:A20))
동작 원리
COUNTIF(A2:A20, A2:A20)- 각 셀 값이 범위 내에서 몇 번 등장하는지 계산합니다.
- 예: {서울, 부산, 서울, 대구, 부산} → {2, 2, 2, 1, 2}
1/COUNTIF(...)- 등장 횟수의 역수를 계산합니다.
- 예: {0.5, 0.5, 0.5, 1, 0.5}
SUMPRODUCT(...)- 모든 역수를 합산하여 고유값 개수를 구합니다.
- 0.5 + 0.5 + 0.5 + 1 + 0.5 = 3
빈칸이 있는 경우
범위에 빈칸이 있으면 #DIV/0! 오류가 발생할 수 있습니다. 이때는 다음과 같이 수정합니다.
=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20, A2:A20&""))이렇게 하면 빈칸은 계산에서 제외됩니다.
조건부 중복 제거 카운트
특정 조건을 만족하는 데이터에서만 중복을 제외하고 개수를 세고 싶을 때는 COUNTIFS를 활용합니다.
예: B열에 지역, C열에 제품명이 있을 때, ‘서울’ 지역의 고유 제품 개수
=SUMPRODUCT((B2:B20="서울")/COUNTIFS(B2:B20, B2:B20, C2:C20, C2:C20))이 수식은 B열이 ‘서울’인 행만 대상으로 C열의 고유값 개수를 계산합니다.
피벗테이블을 활용한 방법
함수 사용이 익숙하지 않다면 피벗테이블을 이용할 수도 있습니다.
- 데이터 범위를 선택하고 삽입 → 피벗테이블을 클릭합니다.
- ‘데이터 모델에 이 데이터 추가’ 옵션을 체크합니다.
- 값 필드 설정에서 ‘고유 개수’를 선택하면 중복을 제외한 개수를 바로 확인할 수 있습니다.
엑셀 중복값 제거 후 고유값 개수 구하기 :: 엑셀 공식 - 오빠두엑셀
엑셀 중복값 제거 후 고유값 개수 구하기 :: 엑셀 공식 - 오빠두엑셀
COUNTIF 함수를 사용하여 각 항목별 개수를 계산합니다. = COUNTIF({김, 이, 김, 박, 이, 김}, {김, 이, 김, 박, 이, 김}) = {3, 2, 3, 1, 2, 3} 따라서 범위에 빈칸이 있을 경우 변환공식을 사용하여 문제를 해결
www.oppadu.com
엑셀에서 중복값을 제거한 후 카운트하는 방법은 버전과 상황에 따라 다양하게 선택할 수 있습니다.
- 최신 버전:
UNIQUE+COUNTA - 구버전:
SUMPRODUCT+COUNTIF배열수식 - 조건부 계산:
COUNTIFS조합 - 비함수형 접근: 피벗테이블 고유 개수 기능
데이터의 특성과 환경에 맞는 방법을 선택하면, 보다 정확하고 효율적으로 고유값 개수를 계산할 수 있습니다.