엑셀에서 데이터를 다루다 보면, 원본 데이터의 복잡성 때문에 원하는 정보를 한눈에 파악하기 어려운 경우가 많습니다.
이때 필요한 것이 바로 데이터 정제와 선별 작업인데요. 엑셀의 강력한 동적 배열 함수인 UNIQUE, SORT, FILTER를 조합하면, 방대한 데이터 속에서 원하는 인사이트를 빠르고 정확하게 찾아낼 수 있습니다.
오늘은 이 세 함수를 엮어 실무에서 바로 활용 가능한 최상급 고급 예제들을 함께 살펴보겠습니다.
엑셀 동적 배열 함수의 강력함
UNIQUE, SORT, FILTER 함수는 모두 동적 배열 함수입니다.
이는 결과를 하나의 셀에만 반환하는 것이 아니라, 결과의 크기에 따라 자동으로 확장되어 여러 셀에 표시된다는 의미입니다.
덕분에 복잡한 수식 없이도 유연한 데이터 처리가 가능해집니다.
1. UNIQUE 함수: 중복 없는 고유 값 추출
- 역할: 지정된 범위에서 중복 값을 제거하고 고유한 값들의 목록을 반환합니다.
- 구문: =UNIQUE(array, [by_col], [exactly_once])
- 예시: A열의 상품명 중 고유한 이름만 추출: =UNIQUE(A:A)
2. SORT 함수: 데이터 정렬
- 역할: 지정된 범위나 배열을 특정 기준에 따라 정렬합니다.
- 구문: =SORT(array, [sort_index], [sort_order], [by_col])
- 예시: A열의 상품명을 오름차순으로 정렬: =SORT(A:A)
3. FILTER 함수: 조건에 맞는 데이터 선별
- 역할: 지정된 조건을 만족하는 행(또는 열)만 필터링하여 반환합니다.
- 구문: =FILTER(array, include, [if_empty])
- 예시: B열의 판매액이 100 이상인 행만 필터링: =FILTER(A:C, B:B>=100)
UNIQUE, SORT, FILTER 함수 조합: 최상급 고급 실무 예제
이제 이 세 함수를 조합하여 실무에서 마주할 수 있는 복잡한 데이터 분석 시나리오를 해결해 보겠습니다.
예제 1: 특정 조건 만족하는 고유 데이터 목록을 정렬하여 추출
시나리오: 우리 회사는 여러 지역에 지점을 두고 다양한 상품을 판매하고 있습니다. 현재 판매 중인 상품 중 '프리미엄' 등급이면서, '서울' 지점에서 판매된 적이 있는 상품들의 목록을 중복 없이 알파벳 순서로 보고 싶습니다.
원본 데이터 (예시):
| 지점 | 상품명 | 상품 등급 | 판매량 |
| 서울 | 노트북 | 일반 | 100 |
| 부산 | TV | 프리미엄 | 50 |
| 서울 | 스마트폰 | 프리미엄 | 200 |
| 대구 | 노트북 | 일반 | 80 |
| 서울 | TV | 프리미엄 | 120 |
| 부산 | 스마트폰 | 프리미엄 | 150 |
목표: '서울' 지점 & '프리미엄' 등급인 상품명 목록 (중복 제거, 오름차순 정렬)
수식: =SORT(UNIQUE(FILTER(B2:B7, (A2:A7="서울") * (C2:C7="프리미엄"))))
해설:
- FILTER(B2:B7, (A2:A7="서울") * (C2:C7="프리미엄"))
- 가장 안쪽의 FILTER 함수는 B2:B7 (상품명) 범위에서 데이터를 필터링합니다.
- 조건은 두 가지입니다: A2:A7="서울" (지점이 '서울') 그리고 C2:C7="프리미엄" (상품 등급이 '프리미엄').
- * 연산자는 AND 조건을 의미합니다. 즉, 두 조건을 모두 만족하는 상품명만 추출합니다. 이 단계의 결과는 {"스마트폰";"TV"}와 같이 중복이 포함된 배열이 될 수 있습니다. - UNIQUE(...)
- FILTER 함수로 추출된 배열에서 중복되는 상품명을 제거합니다. {"스마트폰";"TV"}가 {"스마트폰";"TV"}로 정리됩니다. - SORT(...)
- UNIQUE 함수를 거쳐 나온 고유한 상품명 목록을 알파벳 순서(기본 오름차순)로 정렬합니다. {"스마트폰";"TV"}가 {"TV";"스마트폰"}으로 정렬됩니다.
결과:
| 상품명 |
| TV |
| 스마트폰 |
예제 2: 여러 조건에 따른 고유한 '조합'을 정렬하여 추출
시나리오: 고객의 구매 내역 데이터에서 '구매 금액이 50,000원 이상'인 주문 중, '상품 카테고리'와 '결제 수단'의 고유한 조합을 찾고 싶습니다. 이 조합들을 '카테고리-결제수단' 형태로 만들고, 카테고리별로 정렬하여 보고 싶습니다.
원본 데이터 (예시):
| 상품 카테고리 | 결제 수단 | 구매 금액 |
| 의류 | 신용카드 | 70,000 |
| 식품 | 간편결제 | 45,000 |
| 의류 | 신용카드 | 90,000 |
| 전자제품 | 계좌이체 | 120,000 |
| 식품 | 신용카드 | 60,000 |
| 전자제품 | 신용카드 | 80,000 |
목표: 구매 금액 50,000원 이상인 '카테고리-결제수단' 조합 (중복 제거, 카테고리별 정렬)
수식:
=SORT(
UNIQUE(
FILTER(
A2:A7 & "-" & B2:B7,
C2:C7 >= 50000
)
)
)
해설:
- A2:A7 & "-" & B2:B7
- & 연산자를 사용하여 A열(상품 카테고리)과 B열(결제 수단)의 각 행을 "-"로 연결하여 새로운 문자열 조합("의류-신용카드", "식품-간편결제" 등)을 만듭니다. 이 부분이 FILTER의 array 인수로 사용됩니다. - FILTER(..., C2:C7 >= 50000)
- 생성된 조합 문자열 배열에서 C2:C7 (구매 금액)이 50,000원 이상인 것만 필터링합니다. 이 단계의 결과는 {"의류-신용카드";"의류-신용카드";"전자제품-계좌이체";"식품-신용카드";"전자제품-신용카드"} 와 같은 배열이 됩니다. - UNIQUE(...)
- 필터링된 조합 문자열 배열에서 중복을 제거합니다. {"의류-신용카드";"전자제품-계좌이체";"식품-신용카드";"전자제품-신용카드"} 가 {"의류-신용카드";"전자제품-계좌이체";"식품-신용카드"} 로 정리됩니다. - SORT(...)
- 최종 고유 조합 목록을 알파벳 순서로 정렬합니다.
결과:
| 조합 |
| 의류-신용카드 |
|
전자제품-계좌이체
|
| 식품-신용카드 |
|
전자제품-신용카드
|
예제 3: 특정 조건 만족하는 데이터의 상위 N개 고유 항목 추출 (LARGE/SMALL과 조합)
시나리오: 특정 캠페인(VIP 고객 대상)에 참여한 고객들 중 가장 많이 구매한 상위 3개 상품의 이름을 중복 없이 오름차순으로 보고 싶습니다. (동일 상품 중복 구매는 1회로 간주)
원본 데이터 (예시):
| 캠페인 | 고객명 | 상품명 | 구매 금액 |
| 일반 | 김철수 | 바나나 | 10000 |
| VIP 고객 대상 | 이영희 | 사과 | 15000 |
| VIP 고객 대상 | 박민수 | 오렌지 | 20000 |
| 일반 | 최지아 | 사과 | 8000 |
| VIP 고객 대상 | 이영희 | 사과 | 15000 |
| VIP 고객 대상 | 김철수 | 포도 | 12000 |
| VIP 고객 대상 | 한수진 | 오렌지 | 18000 |
목표: 'VIP 고객 대상' 캠페인에 참여한 고객이 구매한 상품 중 상위 3개 상품 (중복 제거, 오름차순 정렬)
수식:
=SORT(
UNIQUE(
FILTER(
C2:C8,
(A2:A8="VIP 고객 대상") *
(B2:B8 = XLOOKUP(LARGE(D2:D8, SEQUENCE(3)), D2:D8, B2:B8,,0,-1))
)
)
)
참고: 이 수식은 '판매 금액이 높은 순서대로 고객명'을 찾은 뒤 해당 고객이 구매한 '상품명'을 필터링하는 복잡한 로직을 가지고 있습니다. XLOOKUP의 search_mode 인자(-1, 오름차순으로 검색하며 대상보다 작거나 같은 다음 항목)를 활용하여 가장 가까운 상위 구매 금액을 찾을 수 있습니다. 하지만 실제로는 판매 금액이 동일한 경우 등 다양한 변수가 있으므로, 이 방식보다는 PIVOT TABLE 또는 SUMPRODUCT/SUMIFS/COUNTIFS로 직접 계산하는 것이 더 안전할 수 있습니다.
더 간단하고 명확한 접근법 (필터링된 상품 중 고유한 목록을 얻는 것에 집중):
실제로 위와 같은 '상위 N개 상품'을 정확한 판매량 기준으로 추출하려면, SORTBY와 FILTER를 결합하여 먼저 데이터를 정렬하고 고유 값을 찾는 방식이 더 효율적입니다.
수정된 접근 방식 (상위 판매 상품명 추출):
판매량을 기준으로 VIP 고객 대상 캠페인에 참여한 상품을 필터링 한 후, 그 상품명 중 고유한 값을 가져와 판매량 기준으로 정렬하는 방식으로 접근할 수 있습니다.
1단계: VIP 캠페인 상품 추출 및 판매량 정렬 (SORTBY + FILTER)
=SORTBY(FILTER(C2:C8, A2:A8="VIP 고객 대상"), FILTER(D2:D8, A2:A8="VIP 고객 대상"), -1)
이 수식은 VIP 고객 대상 캠페인 상품들을 판매량 기준으로 내림차순 정렬하여 보여줍니다. (예: {"오렌지";"사과";"포도";"오렌지";"사과"})
2단계: 고유값 추출 및 상위 N개 선택 (UNIQUE)
여기서 UNIQUE 함수를 사용하면 중복을 제거할 수 있습니다.
그리고 INDEX와 ROWS를 조합하여 상위 N개만 선택할 수 있습니다.
=UNIQUE(SORTBY(FILTER(C2:C8, A2:A8="VIP 고객 대상"), FILTER(D2:D8, A2:A8="VIP 고객 대상"), -1))
이렇게 하면 "VIP 고객 대상" 캠페인에서 판매된 상품들을 판매량 내림차순으로 정렬한 뒤, 그중 고유한 상품명만 가져옵니다. 예를 들어, {"오렌지";"사과";"포도"}
3단계: 최종 상위 N개 상품만 선택 및 정렬 (INDEX + SEQUENCE)
=SORT(INDEX(UNIQUE(SORTBY(FILTER(C2:C8, A2:A8="VIP 고객 대상"), FILTER(D2:D8, A2:A8="VIP 고객 대상"), -1)), SEQUENCE(MIN(3,ROWS(UNIQUE(SORTBY(FILTER(C2:C8, A2:A8="VIP 고객 대상"), FILTER(D2:D8, A2:A8="VIP 고객 대상"), -1)))))))
이 수식은 매우 복잡해 보이지만, 핵심은 다음과 같습니다:
- 안쪽 UNIQUE(SORTBY(FILTER(...))) 부분은 'VIP 고객 대상' 캠페인의 상품들을 판매량 내림차순으로 정렬한 후 고유한 상품명 목록을 반환합니다.
- ROWS(...)는 이 고유 상품 목록의 행 개수를 셉니다.
- MIN(3, ROWS(...))는 고유 상품이 3개 미만일 경우 그 개수만큼만 선택하고, 3개 이상일 경우 3개를 선택하도록 합니다.
- SEQUENCE(...)는 선택할 개수만큼의 순차적인 숫자를 만듭니다.
- INDEX(..., SEQUENCE(...))는 고유 상품 목록에서 순차적인 숫자에 해당하는 상위 상품을 선택합니다.
가장 바깥쪽 SORT(...)는 최종 선택된 상위 상품들을 오름차순으로 정렬합니다.
결과 (예시):
| 상품명 |
| 오렌지 |
| 사과 |
| 포도 |
UNIQUE, SORT, FILTER 함수는 엑셀의 동적 배열 시대를 연 핵심 함수들입니다.
이들을 조합하는 것은 단순히 데이터를 나열하는 것을 넘어, 숨겨진 패턴을 발견하고 의미 있는 인사이트를 도출하는 데 필수적인 기술입니다.
오늘 소개해드린 최상급 고급 실무 예제들을 통해 여러분의 엑셀 데이터 분석 역량이 한 단계 더 성장하셨기를 바랍니다.