실무에서 데이터를 다룰 때, 엑셀에서 중복 값을 제거하면서 동시에 고유한 값의 개수를 세는 작업은 자주 마주하는 상황이었습니다. 처음에는 버전에 따라 방법이 달라져 번거로웠고, 무슨 수식을 써야 하는지 헷갈리기도 했습니다. 다행히 버전 차이를 명확히 이해하면 작업이 훨씬 수월해집니다. 아래 내용은 제가 실제로 사용하면서 경험한 바를 바탕으로 정리한 것입니다. 가능하면 최신 기능을 먼저 활용하고, 구버전은 대체 방법으로 커버하는 방향으로 설명합니다.
최신 엑셀 버전 (Microsoft 365, Excel 2021 이상) – UNIQUE 함수 활용
최신 버전에서는 UNIQUE 함수를 이용해 중복을 제거한 고유 값 목록을 얻고, 이를 COUNTA 또는 COUNT로 셀 수 있습니다. 이 방식이 가장 간단하고 직관적입니다.
- UNIQUE(범위): 지정된 범위에서 중복 값을 제거하고 고유 값들의 배열을 반환합니다.
- COUNTA(배열): 배열 내의 비어 있지 않은 모든 셀의 개수를 셉니다.
공식은 다음과 같습니다.
=COUNTA(UNIQUE(범위))
예시로, A1:A10 범위의 고유 값 개수를 구하고 싶다면 아래와 같이 입력합니다.
=COUNTA(UNIQUE(A1:A10))
주의사항
- UNIQUE 함수는 동적 배열 함수이므로 결과가 표시될 셀 영역으로 자동 확장됩니다. 다른 셀이 비어 있는지 확인하고 입력하시길 권합니다.
- COUNTA 함수는 텍스트, 숫자, 오류값 등 비어 있지 않은 모든 값을 셉니다. 숫자만 세려면 COUNT를 사용하면 됩니다.
이전 엑셀 버전 (Excel 2019 이하) – 배열 수식 활용
이전 버전에는 UNIQUE 함수가 없기 때문에 배열 수식을 사용해 중복 값을 제거하고 개수를 세야 합니다. 아래 두 가지 방법 중 상황에 맞는 방법을 선택하실 수 있습니다.
핵심 원리
- COUNTIF(범위, 조건): 범위에서 조건에 해당하는 셀의 개수를 셉니다.
- MATCH(찾을값, 찾을범위, [찾기옵션]): 찾을값이 찾을범위에서 몇 번째 위치에 있는지 반환합니다. 찾기옵션을 0으로 두면 정확히 일치하는 값을 찾습니다.
- ROW(참조): 참조 셀의 행 번호를 반환합니다.
- IF(조건, 참일때값, 거짓일때값): 조건에 따라 다른 값을 반환합니다.
- SUM(숫자1, [숫자2], …): 인수로 주어진 숫자들의 합계를 반환합니다.
방법 1: COUNTIF와 MATCH를 이용한 배열 수식
이 방법은 각 항목이 범위에서 처음 나타나는 경우에만 1을 부여하고, 그 값들을 합산합니다. 입력 후 Ctrl+Shift+Enter를 눌러 배열 수식으로 완성해야 합니다.
=SUM(IF(COUNTIF(범위, 범위)=1, 1, 0))
또는 좀 더 일반적인 형태로 작성합니다.
=SUM(IF(MATCH(범위, 범위, 0)=ROW(범위)-MIN(ROW(범위))+1, 1, 0))
예시로, A1:A10 범위의 고유 값 개수를 구하고자 할 때의 배열 수식 예시는 아래와 같습니다. 입력 후 Ctrl+Shift+Enter를 눌러 합치면 됩니다.
=SUM(IF(COUNTIF(A1:A10, A1:A10)=1, 1, 0))
=SUM(IF(MATCH(A1:A10, A1:A10, 0)=ROW(A1:A10)-MIN(ROW(A1:A10))+1, 1, 0))
주요 주의사항
- 이 공식은 배열 수식이므로 입력 후 Ctrl+Shift+Enter를 눌러야 합니다. 올바르게 입력되면 수식 입력란에 중괄호가 자동으로 추가됩니다.
- COUNTIF(범위, 범위)=1 부분은 중복되지 않는 고유 값만 선택합니다.
- MATCH를 이용하는 버전은 각 항목이 범위 내에서 처음으로 나타나는 위치를 찾고, 현재 행의 순서와 일치하는 경우에만 1을 부여합니다.
- 빈 셀은 0으로 처리되므로, 빈 셀을 제외하고 싶다면 IF 또는 다른 조건식을 추가해 주입해야 할 수 있습니다.
방법 2: SUMPRODUCT를 이용한 배열 수식 (Ctrl+Shift+Enter 불필요)
SUMPRODUCT 함수를 사용하면 배열 수식 입력 없이도 중복 값을 제거한 고유 값의 개수를 구할 수 있습니다.
=SUMPRODUCT(1/COUNTIF(범위, 범위))
예시로, A1:A10 범위의 고유 값 개수를 구하는 경우 아래와 같이 입력합니다.
=SUMPRODUCT(1/COUNTIF(A1:A10, A1:A10))
주의사항
- COUNTIF의 결과가 0이 되면 나눗셈으로 인해 오류가 발생할 수 있습니다. 빈 셀을 제외하고 싶다면 아래와 같이 수정합니다.
=SUMPRODUCT((범위<>"")/COUNTIF(범위, 범위&""))
예시(빈 셀 제외):
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10, A1:A10&""))
어떤 공식을 사용해야 할까요?
- 최신 엑셀 버전(Microsoft 365, Excel 2021 이상): =COUNTA(UNIQUE(범위))가 가장 간단하고 직관적입니다.
- 이전 엑셀 버전(Excel 2019 이하):
- 특별한 제약이 없고 비교적 간편한 방법으로는 =SUMPRODUCT(1/COUNTIF(범위, 범위))를 추천합니다(빈 셀 주의).
- 혹은 배열 수식인
=SUM(IF(COUNTIF(범위, 범위)=1, 1, 0))또는=SUM(IF(MATCH(범위, 범위, 0)=ROW(범위)-MIN(ROW(범위))+1, 1, 0))를 사용할 수 있습니다. 다만 Ctrl+Shift+Enter로 입력해야 합니다.
가능하다면 최신 기능인 UNIQUE 함수를 우선 활용하는 것이 가장 편리합니다. 구버전이라면 SUMPRODUCT 방식으로도 충분히 해결할 수 있습니다.
필요한 경우, 실제 데이터 범위에 맞춰 예시를 응용해 보시길 권합니다. 데이터의 특성에 따라 빈 셀 처리나 숫자만 세는 방식 등 미세 조정이 필요할 수 있습니다.