엑셀에서 직원의 부서명이나 거래처, 상품 분류처럼 같은 값이 반복되는 데이터를 정리하다 보면 중복값을 제외하고 고유한 값만 따로 추출해야 할 때가 있습니다.
엑셀의 중복된 항목 제거 기능을 사용할 수도 있지만 원본 데이터가 추가될 때마다 다시 작업해야 한다는 불편함이 있습니다.
이럴 때 활용하기 좋은 것이 UNIQUE 함수입니다.
UNIQUE 함수를 사용하면 데이터에서 중복값을 제외한 항목만 자동으로 추출할 수 있으며, 원본 데이터가 변경되면 결과도 자동으로 반영됩니다.
특히 FILTER, SORT 함수와 조합하거나 드롭다운 목록의 원본 데이터를 만드는 용도로 활용하면 더욱 편리합니다.
이번 글에서는 엑셀 UNIQUE 함수 사용법부터 중복 제거 및 자동 목록 만드는 방법까지 알아보겠습니다.
엑셀 UNIQUE 함수란?
UNIQUE 함수는 지정한 범위에서 중복된 값을 제외하고 고유한 값만 추출하는 함수입니다.
기본 구조는 다음과 같습니다.
=UNIQUE(array,[by_col],[exactly_once])
각 인수의 의미는 다음과 같습니다.
array
중복값을 확인할 데이터 범위입니다.
by_col
행과 열 중 어떤 방향을 기준으로 비교할지 지정하는 선택 인수입니다.
일반적인 세로 목록에서는 생략해도 됩니다.
exactly_once
한 번만 나타나는 값만 추출할 것인지 설정하는 선택 인수입니다.
일반적으로 중복을 제거한 목록을 만드는 목적이라면 생략합니다.
따라서 실제로 가장 많이 사용하는 형태는 매우 간단합니다.
=UNIQUE(A2:A100)
이것만으로 A2:A100에서 중복값을 제외한 목록을 만들 수 있습니다.
UNIQUE 함수로 중복 제거하기
다음과 같이 B열에 직원들의 부서가 입력되어 있다고 가정해 보겠습니다.
| 이름 | 부서 |
|---|---|
| 김철수 | 영업팀 |
| 이영희 | 총무팀 |
| 박민수 | 영업팀 |
| 김민지 | 개발팀 |
| 최영수 | 영업팀 |
| 정수진 | 개발팀 |
부서 목록에는
영업팀
총무팀
영업팀
개발팀
영업팀
개발팀
처럼 같은 값이 반복되어 있습니다.
중복을 제외한 부서 목록을 만들려면 다음 수식을 입력합니다.
=UNIQUE(B2:B7)
그러면 결과는 자동으로
영업팀
총무팀
개발팀
과 같이 표시됩니다.
중복된 영업팀과 개발팀은 한 번씩만 표시되는 것입니다.
기존 ‘중복된 항목 제거’와 UNIQUE 함수 차이
엑셀에는 이미
데이터 → 중복된 항목 제거
기능이 있습니다.
그렇다면 UNIQUE 함수와 어떤 차이가 있을까요?
가장 큰 차이는 원본 데이터 유지 여부와 자동 업데이트 여부입니다.
중복된 항목 제거 기능은 선택한 데이터에서 중복된 항목 자체를 제거합니다.
반면 UNIQUE 함수는 원본 데이터를 그대로 유지하면서 별도의 위치에 중복을 제외한 결과를 만들어 줍니다.
또한 원본 데이터가 변경되면 UNIQUE 함수 결과도 자동으로 다시 계산됩니다.
따라서 한 번만 중복값을 정리하는 작업이라면 중복된 항목 제거 기능을 사용해도 되지만, 계속 변경되는 데이터를 바탕으로 자동 목록을 만들고 싶다면 UNIQUE 함수가 편리합니다.
UNIQUE 함수로 자동 목록 만들기
UNIQUE 함수의 장점은 원본 데이터가 변경되면 추출된 결과도 자동으로 변경된다는 점입니다.
예를 들어 B2:B100에 부서명이 입력되어 있고 다음 수식을 사용했다고 가정하겠습니다.
=UNIQUE(B2:B100)
현재 데이터에
영업팀
총무팀
개발팀
만 있다면 세 가지 항목이 나타납니다.
이후 원본 데이터에 인사팀이 추가되면 UNIQUE 함수의 결과에도 인사팀이 추가될 수 있습니다.
따라서 부서, 직급, 상품분류, 담당자, 거래처 등 중복되지 않는 기준 목록을 자동으로 만들 때 활용하기 좋습니다.
UNIQUE 함수 결과 정렬하기
UNIQUE 함수는 고유값을 추출하는 함수이기 때문에 결과를 원하는 순서로 정렬하는 기능까지 담당하지는 않습니다.
중복을 제거하면서 결과까지 정렬하고 싶다면 SORT 함수와 UNIQUE 함수를 함께 사용할 수 있습니다.
예를 들어 A2:A100의 중복값을 제거한 후 정렬하려면 다음과 같이 작성합니다.
=SORT(UNIQUE(A2:A100))
함수의 안쪽부터 계산되므로 먼저
UNIQUE(A2:A100)
에서 중복을 제외한 목록을 만들고
SORT(…)
에서 해당 목록을 정렬합니다.
텍스트로 구성된 목록을 깔끔하게 정리해야 할 때 유용한 조합입니다.
UNIQUE 함수에서 빈칸 제외하기
실제 엑셀 파일에서는 데이터 범위를 넉넉하게 A2:A1000처럼 지정하는 경우가 많습니다.
이때 범위 안에 빈 셀이 포함되어 있으면 결과 목록에도 빈 항목이 나타날 수 있습니다.
빈칸을 제외하면서 고유값만 추출하려면 FILTER 함수와 UNIQUE 함수를 함께 사용할 수 있습니다.
수식은 다음과 같습니다.
=UNIQUE(FILTER(A2:A100,A2:A100<>””))
FILTER 함수가 먼저 A2:A100에서 빈칸이 아닌 데이터만 추출하고 UNIQUE 함수가 그 결과에서 중복값을 제거합니다.
정렬까지 하고 싶다면 다음과 같이 사용할 수 있습니다.
=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>””)))
이렇게 하면
빈칸 제외 → 중복 제거 → 정렬
과정을 하나의 수식으로 처리할 수 있습니다.
한 번만 등장하는 값 찾기
UNIQUE 함수의 세 번째 인수인 exactly_once를 이용하면 단순한 중복 제거가 아니라 전체 범위에서 정확히 한 번만 등장하는 값도 찾을 수 있습니다.
예를 들어 다음과 같은 데이터가 있다고 가정하겠습니다.
사과
배
사과
포도
배
수박
일반적인 UNIQUE 함수를 사용하면
=UNIQUE(A2:A7)
결과는
사과
배
포도
수박
이 됩니다.
하지만 다음과 같이 세 번째 인수를 TRUE로 지정하면
=UNIQUE(A2:A7,,TRUE)
전체 목록에서 정확히 한 번만 등장하는
포도
수박
만 추출할 수 있습니다.
따라서 중복되지 않고 딱 한 번만 입력된 데이터를 찾을 때 활용할 수 있습니다.
여러 열을 기준으로 중복 제거하기
UNIQUE 함수는 하나의 열뿐만 아니라 여러 열로 구성된 데이터에서도 사용할 수 있습니다.
예를 들어 A열에는 부서, B열에는 직급이 있다고 가정하겠습니다.
| 부서 | 직급 |
|---|---|
| 영업팀 | 대리 |
| 영업팀 | 사원 |
| 영업팀 | 대리 |
| 개발팀 | 대리 |
| 개발팀 | 사원 |
다음과 같이 두 열을 범위로 지정합니다.
=UNIQUE(A2:B6)
그러면 두 열의 조합이 동일한 행을 하나의 중복 데이터로 판단합니다.
결과는
| 부서 | 직급 |
|---|---|
| 영업팀 | 대리 |
| 영업팀 | 사원 |
| 개발팀 | 대리 |
| 개발팀 | 사원 |
처럼 표시됩니다.
즉, 부서만 비교하는 것이 아니라 부서와 직급의 조합을 기준으로 고유한 목록을 만들 수 있습니다.
UNIQUE 함수로 드롭다운 목록 만들기
UNIQUE 함수가 특히 유용한 활용법 중 하나가 엑셀 드롭다운 목록의 원본 데이터 만들기입니다.
예를 들어 B2:B100에 직원들의 부서명이 입력되어 있다고 가정하겠습니다.
H2 셀에 다음 수식을 입력합니다.
=SORT(UNIQUE(FILTER(B2:B100,B2:B100<>””)))
그러면 H2부터 중복과 빈칸이 제거되고 정렬된 부서 목록이 자동으로 만들어집니다.
예를 들어
개발팀
영업팀
총무팀
처럼 결과가 만들어졌다고 가정해 보겠습니다.
UNIQUE 같은 동적 배열 함수의 결과 범위는 # 연산자로 참조할 수 있습니다.
H2에서 시작된 전체 결과 범위는
=H2#
처럼 표현할 수 있습니다.
드롭다운을 구성할 때는 사용하는 엑셀 환경과 데이터 유효성 검사 설정 방식에 따라 이 동적 배열 범위를 이름 정의 등으로 연결해 목록 원본으로 활용할 수 있습니다.
이렇게 구성하면 원본 데이터에 새로운 부서가 추가되었을 때 UNIQUE 결과도 갱신되므로 드롭다운의 기준 목록을 일일이 다시 작성하는 작업을 줄일 수 있습니다.
UNIQUE + FILTER 함수로 조건별 고유값 추출하기
UNIQUE 함수와 FILTER 함수를 조합하면 특정 조건에 해당하는 데이터에서 중복값만 제거하는 것도 가능합니다.
예를 들어
A열 : 이름
B열 : 부서
C열 : 직급
으로 구성되어 있다고 가정하겠습니다.
영업팀 직원들의 직급만 중복 없이 가져오고 싶다면 다음과 같이 작성할 수 있습니다.
=UNIQUE(FILTER(C2:C100,B2:B100=”영업팀”))
FILTER 함수가 먼저 영업팀 직원의 직급만 추출하고 UNIQUE 함수가 해당 결과에서 중복을 제거합니다.
따라서 영업팀에
사원
대리
대리
과장
사원
이 있다면 결과는
사원
대리
과장
처럼 나타납니다.
이 방법은 연동 드롭다운 목록을 구성할 때도 응용할 수 있습니다.
드롭다운 + UNIQUE + FILTER 조합 활용하기
조금 더 발전시키면 UNIQUE 함수로 만든 목록을 드롭다운에 사용하고, 드롭다운에서 선택한 값에 따라 FILTER 함수로 데이터를 조회하도록 구성할 수 있습니다.
예를 들어
F2 : 부서 선택 드롭다운
이 있고 직원 데이터가 A2:D100에 있다면 다음과 같이 사용할 수 있습니다.
=FILTER(A2:D100,B2:B100=F2,”결과 없음”)
전체적인 구조는 다음과 같습니다.
① UNIQUE 함수
원본 데이터에서 중복 없는 부서 목록 생성
↓
② 데이터 유효성 검사
부서 선택용 드롭다운 생성
↓
③ FILTER 함수
드롭다운에서 선택한 부서에 해당하는 직원 자동 추출
이렇게 구성하면 직접 부서명을 입력하지 않고 목록에서 선택하는 것만으로 원하는 데이터를 조회할 수 있습니다.
직원 관리표, 거래처 관리, 재고관리, 매출 조회표 등에 활용하기 좋은 방식입니다.
UNIQUE 함수에서 #SPILL! 오류가 발생한다면?
UNIQUE 함수는 결과가 여러 개인 경우 아래쪽 셀로 자동 확장되는 동적 배열 함수입니다.
예를 들어 H2에 UNIQUE 함수를 입력하고 결과가 5개라면 H2부터 아래쪽 셀까지 자동으로 결과가 표시됩니다.
그런데 결과가 표시되어야 할 영역에 이미 다른 데이터가 있거나 병합된 셀이 있으면 정상적으로 확장할 수 없습니다.
이때 #SPILL! 오류가 나타날 수 있습니다.
따라서 UNIQUE 함수에서 #SPILL! 오류가 발생한다면 결과가 표시될 주변 셀에 기존 값이나 병합된 셀이 있는지 먼저 확인해 보는 것이 좋습니다.
UNIQUE 함수가 안 될 때 확인할 사항
UNIQUE 함수는 동적 배열을 지원하는 엑셀 환경에서 사용할 수 있는 함수입니다.
따라서 사용 중인 엑셀에서
=UNIQUE(
를 입력했는데 함수가 나타나지 않거나 #NAME? 오류가 발생한다면 현재 사용하는 엑셀 버전에서 UNIQUE 함수가 지원되는지 확인해야 합니다.
오래된 엑셀 버전을 사용하는 경우 UNIQUE 함수 대신 고급 필터, 중복된 항목 제거 기능 등을 이용해야 할 수 있습니다.
엑셀 UNIQUE 함수 사용법 정리
엑셀 UNIQUE 함수의 가장 기본적인 사용법은 다음과 같습니다.
=UNIQUE(A2:A100)
지정한 범위에서 중복을 제외한 고유값만 추출합니다.
중복 제거 후 정렬까지 하고 싶다면
=SORT(UNIQUE(A2:A100))
빈칸까지 제외하려면
=UNIQUE(FILTER(A2:A100,A2:A100<>””))
빈칸 제외와 정렬을 모두 적용하려면
=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>””)))
처럼 활용할 수 있습니다.
UNIQUE 함수는 단순히 중복값을 제거하는 용도로만 사용하는 것보다 FILTER, SORT 함수와 함께 사용했을 때 활용도가 더욱 높아집니다.
특히 UNIQUE 함수로 자동 목록을 만들고 이를 드롭다운과 FILTER 함수에 연결하면 원본 데이터가 변경되어도 관리하기 편리한 자동 조회 시스템을 엑셀 안에서 구성할 수 있습니다.
부서명이나 거래처, 상품분류처럼 중복 데이터가 많은 엑셀 파일을 자주 사용한다면 UNIQUE 함수의 기본 사용법과 함께 FILTER·SORT 함수 조합도 알아두시기 바랍니다.
“엑셀 UNIQUE 함수 사용법 (중복 제거 및 자동 목록)”에 대한 1개의 생각