저는 데이터 관리 업무를 하면서 매달 엑셀 시트를 다듬을 때 중복값을 제외한 고유한 값의 개수를 세는 일이 자주 필요하다는 것을 경험했습니다. 처음에는 수작업으로 목록을 정리하고 고유값을 하나씩 세느라 시간이 많이 들었고, 가끔은 같은 데이터라도 버전 차이에 따라 적용 방법이 달라져 헷갈리곤 했습니다. 그러다 버전에 맞춰 여러 가지 방법을 체계적으로 정리하게 되었고, 상황에 맞춰 선택하면 훨씬 효율적으로 일을 처리할 수 있다는 것을 알았습니다. 아래에는 제 경험을 바탕으로 실제로 자주 쓰는 방법들을 정리했습니다. 엑셀 중복값 제외 개수 세는 법 입니다.
UNIQUE 함수와 COUNTA 함수 조합 (Excel 365 / 웹 버전)
가장 쉽고 직관적인 방법으로, 고유값의 개수만 필요하고 추출한 목록까지 함께 보고 싶을 때 유용합니다.
- 수식:
=COUNTA(UNIQUE(범위)) - 예시: A1:A100 범위에서 고유값 개수를 세려면
=COUNTA(UNIQUE(A1:A100)) - 설명:
UNIQUE(범위)가 범위 내 중복되지 않는 값을 배열로 반환하고,COUNTA가 그 배열의 비어 있지 않은 값의 개수를 셉니다. 공백 셀은 자동으로 제외되는 효과를 얻을 수 있습니다. 자세한 내용은 Microsoft 지원의 UNIQUE 함수 설명을 참고해 주세요.
SUMPRODUCT와 COUNTIF 함수 조합 (모든 엑셀 버전)
호환성을 최우선으로 두고 싶다면 이 방법을 추천합니다. 수식 하나로 고유값의 개수를 구할 수 있으며, 데이터의 크기에 따라 성능에 영향이 있을 수 있습니다.
- 수식:
=SUMPRODUCT(1/COUNTIF(범위, 범위)) - 예시:
=SUMPRODUCT(1/COUNTIF(A1:A100, A1:A100)) - 설명: 각 값의 등장 횟수를
COUNTIF로 구하고, 그 역수를 합산하여 고유값의 개수를 산출합니다. 공백 셀도 제외하고 싶다면=SUMPRODUCT(--(범위<>"")/COUNTIF(범위, 범위))형태로 작성하는 것이 안전합니다. 이 방법에 대한 자세한 설명은 SUMPRODUCT 함수 가이드를 참고해 주세요.
피벗 테이블(Pivot Table) 활용
피벗 테이블은 고유값의 개수를 세는 동시에 각 고유값의 분포나 추가 분석을 함께 보고 싶을 때 특히 유용합니다.
- 데이터 범위를 선택합니다. 예를 들어 항목 열 전체 또는 A1:A100 범위를 선택합니다.
- 삽입 탭에서 피벗 테이블을 클릭합니다. 새 워크시트나 기존 워크시트 중 하나를 선택하고 확인합니다.
- 피벗 테이블 필드에서 고유값이 있는 열을 행 영역으로 드래그합니다. 같은 열을 다시 값 영역으로도 드래그합니다.
- 값 필드 설정에서 고유한 개수(Distinct Count) 또는 고유 값 수를 선택합니다. 이 옵션은 Excel 2013 이상에서 지원되며, 데이터 모델과의 연결 여부에 따라 보일 수 있습니다. 자세한 내용은 피벗 테이블 도움말을 참조해 주세요.
고급 필터(Advanced Filter) 활용
고급 필터를 이용해 먼저 고유값 목록을 추출한 뒤, 그 목록의 개수를 세는 방식도 있습니다. 직관적으로 목록을 확인하고 바로 사용할 수 있다는 점이 장점입니다.
- 데이터를 선택하고 데이터 탭의 정렬 및 필터 그룹에서 고급을 클릭합니다.
- 고급 필터 대화 상자에서 결과를 다른 위치에 복사하도록 설정합니다. 목록 범위가 정확한지 확인하고, 복사 위치를 지정합니다. 고유한 레코드만 체크박스를 선택합니다.
- 새 위치에 복사된 고유값 목록의 셀 수를
COUNTA로 셉니다. 예를 들어 C열에 복사되었다면=COUNTA(C:C)또는=COUNTA(C1:C100)처럼 입력합니다.
추가 팁과 고려사항
데이터를 다룰 때는 형식 차이에 따른 오해를 피하기 위해 몇 가지를 점검하는 것이 좋습니다. 예를 들어 숫자와 텍스트로 표시된 같은 값이 다른 값으로 인식될 수 있으며, 공백 문자나 앞뒤 공백으로 인해 중복 여부가 다르게 판정될 수 있습니다. 이럴 때는 데이터 정제 작업을 선행하는 것이 좋습니다. 예를 들어 TRIM을 이용해 불필요한 공백을 제거하고, 대소문자 차이를 통일하는 등의 전처리를 통해 정확도를 높일 수 있습니다. Excel의 다양한 기능은 Excel 기능 모음에서 더 자세히 확인하실 수 있습니다.
또한 고유 값을 계산하는 방법은 제공하는 데이터의 성격에 따라 다르게 적용될 수 있습니다. 예를 들어 대소문자 차이로 인한 중복 판단이나 데이터 형식의 혼합이 있는 경우, 먼저 데이터 표준화를 진행하는 것이 바람직합니다. 필요에 따라 여러 방법을 교차 확인하는 습관을 들이면 신뢰도 높은 결과를 얻을 수 있습니다.
참고로, 특정 상황에서 FREQUENCY와 MATCH를 함께 사용하는 배열 수식도 있고, 이 방법은 구 버전의 엑셀에서도 활용 가능합니다. 예를 들어 =SUM(IF(FREQUENCY(MATCH(범위, 범위, 0), MATCH(범위, 범위, 0))>0, 1))는 배열 수식으로 입력해야 하며(Enter 대신 Ctrl+Shift+Enter), 데이터 크기에 따라 처리 속도가 느려질 수 있습니다. 자세한 내용은 배열 수식 안내를 참고해 주세요.
위의 여러 방법은 각자의 장단점이 있어, 사용 환경에 맞춰 적절히 선택하는 것이 가장 중요합니다. 버전에 따라 사용할 수 있는 기능이 다르므로, 실제 데이터와 목적에 맞춰 한 가지 방법을 선택한 뒤 필요하면 다른 방법으로 교차 확인하는 방식이 실무에서 가장 안정적입니다.