프로젝트 마감 직전에 고객 명단을 정리해야 하는 상황이 있었습니다. 수백 개의 데이터가 섞여 있는데, 같은 이름이 여러 번 들어가 있어 정확한 인원 수를 세는 것만으로도 꽤 시간이 걸리더군요. 그때 엑셀에서 중복값을 깔끔하게 제거하고, 동시에 고유한 값의 개수를 세는 여러 가지 방법을 정리해 두었더니 이후로 비슷한 상황에서 훨씬 수월하게 처리할 수 있었습니다. 아래 내용은 그때 실제로 사용하면서 효과를 본 방법들을 중심으로 정리한 것입니다.
중복값을 직접 제거하고 개수 세기
가장 직관적인 방법은 엑셀의 ‘중복된 항목 제거’ 기능을 이용하는 것입니다. 다만 이 방법은 원본 데이터를 바로 변경하므로, 실제 작업에서는 보통 데이터를 복사해 두고 진행하는 편이 안전합니다.
중복 제거 후 개수를 세는 기본 흐름은 다음과 같습니다.
- 중복을 제거할 열 또는 표 전체를 선택합니다.
- 상단 메뉴에서 데이터 탭을 클릭합니다.
- 데이터 도구 그룹에서 ‘중복된 항목 제거’를 선택합니다.
- 대화 상자에서 중복 여부를 판단할 열을 체크하고 확인을 누릅니다.
- 중복이 제거된 후 남은 행 또는 셀의 개수를 세어 고유값 개수를 확인합니다.
고유값 개수를 셀 때는 다음과 같은 함수가 자주 사용됩니다.
- 행 개수 그대로 세기:
=ROWS(범위) - 빈 셀을 제외한 개수 세기:
=COUNTA(범위)
한 번 중복을 제거하고 나면 되돌리기 전까지는 원래 데이터가 지워지므로, 중요한 데이터라면 작업 전에 전체 범위를 다른 시트나 파일에 복사해 두는 것이 좋습니다.
새로운 열에 고유값 목록 만든 뒤 개수 세기
실무에서는 원본을 절대 건드리지 말아야 하는 경우가 많습니다. 이럴 때는 고유값만 따로 복사해 놓고 그 개수를 세는 방식이 훨씬 안전합니다. 실제로 명단 정리, 상품코드 관리 같은 작업에서 자주 활용되는 방법입니다.
작업 순서는 다음과 같이 진행하면 됩니다.
- 중복을 제거하고 싶은 열(예: 이름, 고객번호 등)을 선택합니다.
- 해당 열을 복사하여 오른쪽이나 새로운 시트의 빈 열에 붙여넣습니다.
- 복사해 온 새 열을 선택합니다.
- 데이터 탭 → ‘중복된 항목 제거’를 클릭합니다.
- 중복 기준으로 사용할 열이 맞는지 확인한 후 확인을 누릅니다.
- 이제 이 열에는 고유값만 남으므로,
=COUNTA(새로운_열_범위)로 개수를 셉니다.
이 방법은 원본은 그대로 두고, 별도의 열에서만 중복을 제거하기 때문에 이후에 비교, 필터, 피벗테이블 등 다른 작업과 병행하기에도 수월합니다.
함수만으로 고유값 개수 구하기
중복값을 실제로 삭제하지 않고, 화면에 보이는 원본 데이터는 그대로 둔 채 ‘고유값이 몇 개인지’만 알고 싶을 때가 있습니다. 이때는 함수만으로 계산하는 방식이 유용합니다. 특히 보고서에 자동화된 셀 하나만 두고 싶을 때 자주 사용됩니다.
SUMPRODUCT + COUNTIF 조합
엑셀 버전 제약이 거의 없고, 별도의 배열 수식 입력 없이 사용할 수 있어 많이 쓰이는 방법입니다.
기본 수식은 다음과 같습니다.
=SUMPRODUCT(1/COUNTIF(범위, 범위))
COUNTIF(범위, 범위)는 각 셀이 범위 안에서 몇 번 나타나는지를 배열 형태로 계산합니다.- 그 값을 1로 나누면 각 값의 등장 횟수에 대한 역수가 됩니다.
- 같은 값이 여러 번 등장해도 역수들의 합은 1이 되므로, 최종적으로 고유값의 개수가 계산됩니다.
이 수식은 기본적으로 빈 셀은 고유값으로 세지 않습니다. 만약 범위 안의 빈 셀까지 포함해 고유값을 세야 하는 경우에는 범위를 정리하거나, 조건을 한 번 더 감싸는 식으로 수정이 필요합니다.
배열 수식을 이용한 방법
조금 더 세밀하게 빈 셀을 제어하고 싶거나, 특정 조건을 만족하는 값만 고유 개수로 세고 싶을 때는 배열 수식을 응용해 사용할 수 있습니다. 예시로 소개되는 수식 구조는 다음과 같습니다.
=SUM(IF(범위<>"", 1/COUNTIF(범위, 범위)))
범위<>""조건으로 빈 셀은 아예 계산 대상에서 제외합니다.- 비어 있지 않은 셀에 대해서만
1/COUNTIF(범위, 범위)를 적용해 고유값 개수를 구합니다.
이 방식은 구버전 엑셀에서는 배열 수식으로 입력해야 하므로, 수식을 입력한 뒤 Enter 대신 Ctrl + Shift + Enter를 함께 눌러야 합니다. Microsoft 365나 최신 버전에서는 일반 수식처럼 작동하는 경우가 많지만, 환경에 따라 다를 수 있어 회사에서 사용하는 버전에 맞춰 한 번 테스트해 보는 것이 좋습니다.
UNIQUE 함수로 고유값 개수 구하기 (엑셀 365/2021 이상)
최근 버전의 엑셀에서는 UNIQUE 함수 덕분에 중복 제거와 고유값 개수 세기가 훨씬 단순해졌습니다. 실제로 Microsoft 365 환경에서는 되도록 이 방법을 쓰는 것이 가장 편합니다.
고유값 개수를 구하는 대표적인 수식은 다음과 같습니다.
=COUNTA(UNIQUE(범위))
UNIQUE(범위)는 지정한 범위에서 중복을 제거한 고유값 목록을 동적 배열 형태로 반환합니다.COUNTA는 그 배열에서 빈 셀을 제외한 항목 수를 세어 줍니다.
한 단계 더 나아가, 여러 열을 한 번에 중복 기준으로 삼거나, 행/열 기준을 조정하는 옵션도 제공되기 때문에 복잡한 데이터에서도 손쉽게 고유값 목록을 만들 수 있습니다. 기존에 SUMPRODUCT나 배열 수식을 사용하며 헷갈렸던 분이라면, UNIQUE 함수가 있는 버전으로 바뀌는 순간 작업 난도가 크게 내려가는 것을 체감하게 됩니다.
상황별로 적합한 방법 선택하기
실제로 일을 하다 보면 “정답은 하나”라기보다, 데이터 상태와 엑셀 버전에 따라 적절한 선택을 하는 것이 중요합니다. 간단히 정리하면 다음과 같이 사용하면 편리합니다.
- 엑셀 365 또는 2021 이상을 사용 중이고, 빠르게 고유값 개수만 알고 싶을 때:
UNIQUE + COUNTA조합을 사용합니다. - 원본을 그대로 두고, 화면에 고유값 목록을 따로 보고 싶을 때: 새 열에 복사 후 ‘중복된 항목 제거’ 기능을 사용합니다.
- 보고서용 셀 하나에서 자동으로 고유값 개수를 계산하고 싶을 때:
SUMPRODUCT + COUNTIF수식을 적용합니다. - 데이터 양이 많지 않고, 중복을 실제로 없애도 되는 상황일 때: 원본을 백업한 뒤 직접 ‘중복된 항목 제거’를 적용하고
ROWS또는COUNTA로 개수를 셉니다.
업무 중에 비슷한 상황을 여러 번 겪다 보면, 자연스럽게 자주 쓰는 패턴이 생깁니다. 처음에는 함수식이 낯설 수 있지만, 실제 데이터에 한 번만 직접 적용해 보고 결과를 눈으로 확인해 보면 원리도 금방 이해되고, 이후에는 몇 초 안에 고유값 개수를 뽑아낼 수 있게 됩니다.