엑셀에서 데이터를 관리하다 보면 특정 조건에 맞는 자료만 가져오거나, 중복된 항목을 제거하고, 결과를 원하는 순서대로 정렬해야 하는 경우가 많습니다.
이러한 작업을 보다 간편하게 처리할 수 있는 기능이 바로 동적 배열 함수입니다.
대표적으로 많이 사용하는 함수가
FILTER
SORT
UNIQUE
입니다.
FILTER 함수로 원하는 데이터만 추출하고, UNIQUE 함수로 중복값을 제거한 뒤 SORT 함수로 자동 정렬하는 식으로 서로 조합해서 사용할 수도 있습니다.
특히 원본 데이터가 변경되면 결과도 자동으로 다시 계산되기 때문에 직원 명단, 고객 관리, 재고 관리, 매출 조회 등 반복적으로 업데이트되는 엑셀 문서에서 활용도가 높습니다.
이번 글에서는 엑셀 동적 배열 함수란 무엇인지부터 FILTER·SORT·UNIQUE 함수의 사용법과 조합 방법까지 한 번에 정리해 보겠습니다.
엑셀 동적 배열이란?
기존 엑셀에서는 여러 개의 결과를 표시하기 위해 수식을 여러 셀에 복사하거나 배열 수식을 사용해야 하는 경우가 많았습니다.
동적 배열을 지원하는 함수는 다릅니다.
예를 들어 다음 수식을 하나의 셀에 입력해 보겠습니다.
=UNIQUE(B2:B100)
B열에 서로 다른 값이 5개 있다면 수식을 입력한 셀을 시작으로 아래쪽에 5개의 결과가 자동으로 표시됩니다.
즉, 수식을 결과 셀마다 복사할 필요가 없습니다.
결과의 개수가 늘어나거나 줄어들면 표시되는 범위 역시 자동으로 변경됩니다.
이처럼 하나의 수식에서 나온 여러 결과가 필요한 셀까지 자동으로 확장되는 방식을 이해하면 FILTER, SORT, UNIQUE 함수를 훨씬 쉽게 활용할 수 있습니다.
동적 배열 함수 3가지 한눈에 보기
먼저 세 함수의 역할을 간단하게 정리하면 다음과 같습니다.
| 함수 | 주요 기능 | 대표적인 활용 |
|---|---|---|
| FILTER | 조건에 맞는 데이터 추출 | 특정 부서 직원 조회 |
| SORT | 데이터 자동 정렬 | 실적 높은 순서로 정렬 |
| UNIQUE | 중복값 제거 | 중복 없는 부서 목록 생성 |
쉽게 기억하면
FILTER = 골라내기
SORT = 정렬하기
UNIQUE = 중복 제거하기
라고 생각하면 됩니다.
각각 하나씩 알아보겠습니다.
1. FILTER 함수 사용법
FILTER 함수는 지정한 범위에서 조건을 만족하는 데이터만 추출하는 함수입니다.
기본 구조는 다음과 같습니다.
=FILTER(array,include,[if_empty])
예를 들어 다음과 같은 직원 명단이 있다고 가정하겠습니다.
| 이름 | 부서 | 직급 | 실적 |
|---|---|---|---|
| 김철수 | 영업팀 | 대리 | 120 |
| 이영희 | 총무팀 | 과장 | 80 |
| 박민수 | 영업팀 | 사원 | 95 |
| 김민지 | 개발팀 | 대리 | 110 |
| 최영수 | 영업팀 | 과장 | 150 |
| 정수진 | 개발팀 | 사원 | 90 |
A2:D7에 위 데이터가 입력되어 있다면 영업팀 직원만 추출하는 수식은 다음과 같습니다.
=FILTER(A2:D7,B2:B7=”영업팀”,”결과 없음”)
결과는 자동으로
| 이름 | 부서 | 직급 | 실적 |
|---|---|---|---|
| 김철수 | 영업팀 | 대리 | 120 |
| 박민수 | 영업팀 | 사원 | 95 |
| 최영수 | 영업팀 | 과장 | 150 |
처럼 표시됩니다.
원본 데이터는 그대로 유지하면서 조건에 맞는 행만 별도의 위치에 가져올 수 있습니다.
FILTER 함수 다중조건 사용하기
FILTER 함수에서는 여러 조건을 동시에 적용할 수도 있습니다.
예를 들어
영업팀이면서 실적이 100 이상
인 직원만 추출하려면 다음과 같이 사용합니다.
=FILTER(A2:D7,(B2:B7=”영업팀”)*(D2:D7>=100),”결과 없음”)
*를 사용하면 두 조건을 모두 만족해야 하는 AND 조건을 만들 수 있습니다.
반대로
영업팀 또는 개발팀
처럼 둘 중 하나를 만족하면 되는 경우에는 +를 사용할 수 있습니다.
=FILTER(A2:D7,(B2:B7=”영업팀”)+(B2:B7=”개발팀”),”결과 없음”)
정리하면
AND 조건 → *
OR 조건 → +
로 기억하면 편리합니다.
2. UNIQUE 함수 사용법
UNIQUE 함수는 지정한 범위에서 중복값을 제외하고 고유한 값만 추출합니다.
기본적인 사용법은 매우 간단합니다.
=UNIQUE(array)
예를 들어 B2:B7에 다음과 같은 부서가 있다고 가정하겠습니다.
영업팀
총무팀
영업팀
개발팀
영업팀
개발팀
다음 수식을 입력합니다.
=UNIQUE(B2:B7)
결과는
영업팀
총무팀
개발팀
처럼 나타납니다.
같은 값이 여러 번 있어도 한 번씩만 표시됩니다.
UNIQUE 함수는 일반 중복 제거와 무엇이 다를까?
엑셀에는
데이터 → 중복된 항목 제거
기능도 있습니다.
이 기능은 선택한 데이터에서 중복 항목을 직접 제거합니다.
반면 UNIQUE 함수는 원본 데이터를 건드리지 않고 별도의 위치에 고유값 목록을 만들어 준다는 차이가 있습니다.
또한 원본 데이터가 변경되면 UNIQUE 함수의 결과 역시 자동으로 다시 계산됩니다.
따라서 한 번만 중복값을 삭제한다면 일반 중복 제거 기능도 편리하지만, 계속 변경되는 데이터에서 자동 목록을 만들고 싶다면 UNIQUE 함수가 유용합니다.
3. SORT 함수 사용법
SORT 함수는 데이터를 지정한 기준에 따라 자동으로 정렬합니다.
기본 구조는 다음과 같습니다.
=SORT(array,[sort_index],[sort_order],[by_col])
예를 들어 앞의 직원 명단을 실적이 높은 순서대로 정렬해 보겠습니다.
A2:D7에서 실적은 네 번째 열입니다.
따라서 다음과 같이 사용합니다.
=SORT(A2:D7,4,-1)
여기에서
4
는 네 번째 열을 정렬 기준으로 사용한다는 의미이고,
-1
은 내림차순을 의미합니다.
따라서 실적이
150
120
110
95
90
80
순서로 정렬됩니다.
SORT 오름차순과 내림차순
SORT 함수의 정렬 방향은 다음과 같이 구분합니다.
1 = 오름차순
작은 값 → 큰 값
A → Z
가나다순
-1 = 내림차순
큰 값 → 작은 값
Z → A
역순
예를 들어 실적이 낮은 순서대로 정렬하려면
=SORT(A2:D7,4,1)
실적이 높은 순서대로 정렬하려면
=SORT(A2:D7,4,-1)
을 사용할 수 있습니다.
FILTER·SORT·UNIQUE 차이 정리
세 함수는 모두 동적 배열을 활용하지만 목적이 다릅니다.
예를 들어 직원 데이터가 있다고 생각하면 이해하기 쉽습니다.
FILTER
영업팀 직원만 보여줘.
UNIQUE
어떤 부서들이 있는지 중복 없이 보여줘.
SORT
실적이 높은 직원부터 보여줘.
따라서 원하는 작업에 따라 적절한 함수를 선택하면 됩니다.
하지만 동적 배열 함수의 진짜 장점은 여러 함수를 서로 조합할 수 있다는 것입니다.
FILTER + SORT 함수 조합
먼저 영업팀 직원만 가져온 다음 실적이 높은 순서대로 정렬해 보겠습니다.
수식은 다음과 같습니다.
=SORT(FILTER(A2:D7,B2:B7=”영업팀”),4,-1)
수식은 안쪽부터 실행됩니다.
먼저
FILTER(A2:D7,B2:B7=”영업팀”)
에서 영업팀 직원만 추출합니다.
그 결과를
SORT(…,4,-1)
가 실적 내림차순으로 정렬합니다.
즉,
조건 검색 → 자동 정렬
과정을 하나의 수식으로 처리할 수 있습니다.
UNIQUE + SORT 함수 조합
중복값을 제거한 뒤 결과를 정렬하는 것도 가능합니다.
예를 들어 B2:B100에서 중복 없는 부서 목록을 만들고 정렬하려면 다음과 같이 사용합니다.
=SORT(UNIQUE(B2:B100))
먼저 UNIQUE 함수가 중복된 부서명을 제거합니다.
그 결과를 SORT 함수가 다시 정렬합니다.
따라서 부서나 거래처, 상품분류처럼 중복 없는 자동 목록을 만들어야 할 때 활용하기 좋습니다.
FILTER + UNIQUE 함수 조합
이번에는 특정 조건에 해당하는 데이터에서 중복값을 제거해 보겠습니다.
예를 들어
B열 : 부서
C열 : 직급
이라고 가정하고 영업팀 직원들의 직급만 중복 없이 추출해 보겠습니다.
=UNIQUE(FILTER(C2:C100,B2:B100=”영업팀”))
FILTER 함수가 먼저 영업팀 직원들의 직급을 추출하고 UNIQUE 함수가 중복을 제거합니다.
영업팀에
사원
대리
대리
과장
사원
이 있다면 결과는
사원
대리
과장
으로 표시됩니다.
FILTER + UNIQUE + SORT 한 번에 사용하기
세 가지 함수를 모두 함께 사용할 수도 있습니다.
예를 들어 영업팀 직원들의 직급에서
빈칸 제외 → 중복 제거 → 자동 정렬
을 하고 싶다고 가정해 보겠습니다.
다음과 같이 구성할 수 있습니다.
=SORT(UNIQUE(FILTER(C2:C100,(B2:B100=”영업팀”)*(C2:C100<>””))))
수식이 길어 보이지만 안쪽부터 보면 어렵지 않습니다.
1단계 FILTER
영업팀이면서 직급이 빈칸이 아닌 데이터만 추출
↓
2단계 UNIQUE
중복된 직급 제거
↓
3단계 SORT
최종 목록 정렬
이처럼 여러 동적 배열 함수를 조합하면 기존에 여러 단계로 처리하던 작업을 하나의 수식으로 만들 수 있습니다.
빈칸 제외 + 중복 제거 + 정렬하기
UNIQUE 함수를 사용할 때 실무에서 자주 사용하는 수식 중 하나가 다음과 같습니다.
=SORT(UNIQUE(FILTER(B2:B100,B2:B100<>””)))
이 수식은 B열에서
빈칸을 제외하고
↓
중복값을 제거한 다음
↓
결과를 자동 정렬합니다.
부서, 담당자, 거래처, 상품분류 등의 기준 목록을 만들 때 활용하기 좋습니다.
특히 드롭다운 목록의 기준 데이터를 자동으로 만들 때 유용합니다.
드롭다운과 동적 배열 함수 연결하기
FILTER, SORT, UNIQUE 함수는 드롭다운과 함께 사용하면 활용도가 더욱 높아집니다.
예를 들어 직원 명단의 B열에 부서가 입력되어 있다면 다음 수식으로 중복 없는 부서 목록을 만들 수 있습니다.
=SORT(UNIQUE(FILTER(B2:B100,B2:B100<>””)))
이 목록을 이용해 부서를 선택할 수 있는 드롭다운을 구성합니다.
그리고 F2가 부서를 선택하는 셀이라면 다음 수식을 사용할 수 있습니다.
=SORT(FILTER(A2:D100,B2:B100=F2,”결과 없음”),4,-1)
이제 F2에서 영업팀을 선택하면
영업팀 직원만 추출 → 실적순 자동 정렬
됩니다.
F2를 개발팀으로 변경하면 결과도 개발팀 데이터로 자동 변경됩니다.
이렇게 구성하면 엑셀 안에서 간단한 데이터 검색 및 조회 화면을 만들 수 있습니다.
동적 배열 범위의 # 기호란?
동적 배열 함수를 사용하다 보면 다음과 같은 표현을 볼 수 있습니다.
=H2#
여기에서 #은 H2에서 시작된 동적 배열 결과 전체 범위를 의미합니다.
예를 들어 H2에
=UNIQUE(B2:B100)
수식을 입력했고 결과가 H2:H5까지 표시되었다고 가정하겠습니다.
이 결과 전체를 참조할 때
=H2#
처럼 사용할 수 있습니다.
이후 UNIQUE 결과가 늘어나 H2:H10까지 확장되어도 H2#은 변경된 전체 동적 배열 범위를 참조합니다.
동적으로 늘어나는 결과를 다른 수식과 연결할 때 알아두면 유용한 기능입니다.
동적 배열에서 #SPILL! 오류가 발생하는 이유
FILTER, SORT, UNIQUE 함수를 사용하다 보면 #SPILL! 오류가 나타날 수 있습니다.
동적 배열 함수는 결과가 여러 개라면 주변 셀로 자동 확장되어야 합니다.
그런데 결과가 표시될 위치에 기존 데이터가 있거나 병합된 셀이 있으면 배열을 확장할 수 없습니다.
예를 들어 H2에 UNIQUE 함수를 입력했고 결과가 H2:H6까지 표시되어야 하는데 H4에 다른 데이터가 있다면 정상적으로 결과를 표시할 수 없습니다.
이때 #SPILL! 오류가 발생할 수 있습니다.
따라서 오류가 나타난다면 수식 결과가 표시될 주변 셀에 다른 데이터나 병합된 셀이 있는지 먼저 확인하는 것이 좋습니다.
원본 데이터가 늘어날 때 범위도 자동으로 확장하려면?
동적 배열 함수 자체는 결과를 자동으로 확장하지만 다음과 같이 범위를 고정해 사용하면
=FILTER(A2:D100,B2:B100=”영업팀”)
101행 이후에 새로 추가된 데이터는 지정한 범위 밖이므로 포함되지 않습니다.
데이터가 계속 추가되는 문서라면 원본 데이터를 엑셀 표(Table)로 만들어 사용하는 방법을 고려할 수 있습니다.
표는 데이터가 추가되면 범위가 자동으로 확장되기 때문에 계속 업데이트되는 직원 명단이나 거래내역, 재고 데이터 등을 관리하기 편리합니다.
동적 배열 함수와 표를 함께 활용하면 데이터 추가에 대응하기 쉬운 구조를 만들 수 있습니다.
SORTBY 함수도 함께 알아두면 좋은 이유
SORT 함수와 함께 알아두면 좋은 동적 배열 함수가 SORTBY입니다.
SORT 함수는
=SORT(A2:D100,4,-1)
처럼 정렬할 데이터 범위 안에서 몇 번째 열을 기준으로 사용할지 지정합니다.
SORTBY 함수는
=SORTBY(A2:D100,D2:D100,-1)
처럼 정렬 기준 범위를 직접 지정합니다.
특히
=SORTBY(A2:D100,B2:B100,1,D2:D100,-1)
처럼 부서 오름차순 → 실적 내림차순 등 여러 조건을 적용할 때 SORTBY를 활용할 수 있습니다.
따라서 기본적인 자동 정렬은 SORT 함수부터 익히고, 여러 정렬 조건이 필요하다면 SORTBY 함수까지 함께 알아두는 것이 좋습니다.
동적 배열 함수가 안 될 때 확인할 사항
FILTER, SORT, UNIQUE 등의 함수는 동적 배열을 지원하는 엑셀 환경에서 사용할 수 있습니다.
함수 이름이 인식되지 않거나 #NAME? 오류가 발생한다면 사용 중인 엑셀 버전에서 해당 함수를 지원하는지 확인해야 합니다.
또한 함수는 정상적으로 인식되지만 결과가 나타나지 않는다면 #SPILL! 오류 여부와 결과가 확장될 셀 영역도 확인해 보시기 바랍니다.
엑셀 동적 배열 함수 사용법 정리
동적 배열 함수를 처음 접한다면 우선 세 가지 함수의 역할부터 기억하면 쉽습니다.
FILTER
조건에 맞는 데이터만 추출
=FILTER(A2:D100,B2:B100=”영업팀”,”결과 없음”)
UNIQUE
중복값을 제외한 고유값 추출
=UNIQUE(B2:B100)
SORT
데이터 자동 정렬
=SORT(A2:D100,4,-1)
각각의 함수를 익혔다면 다음 단계로 서로 조합해서 사용할 수 있습니다.
중복 제거 + 정렬
=SORT(UNIQUE(B2:B100))
조건에 맞는 데이터 + 정렬
=SORT(FILTER(A2:D100,B2:B100=”영업팀”),4,-1)
빈칸 제외 + 중복 제거 + 정렬
=SORT(UNIQUE(FILTER(B2:B100,B2:B100<>””)))
처럼 활용할 수 있습니다.
FILTER, UNIQUE, SORT 같은 동적 배열 함수의 가장 큰 장점은 원본 데이터 변화에 맞춰 결과 역시 자동으로 변경된다는 것입니다.
따라서 반복적으로 필터를 설정하거나 중복값을 제거하고 다시 정렬하는 작업을 줄일 수 있습니다.
처음에는 각각의 함수를 따로 익힌 다음
FILTER → 원하는 데이터 추출
UNIQUE → 중복 제거
SORT → 결과 정렬
순서로 조합해 보면 어렵지 않게 활용할 수 있습니다.
직원 관리, 고객 관리, 거래처 목록, 재고 관리, 매출 조회 등 데이터가 계속 추가되고 변경되는 엑셀 파일을 자주 사용한다면 세 가지 동적 배열 함수를 함께 알아두시기 바랍니다.
“엑셀 동적 배열 함수 사용법 (FILTER·SORT·UNIQUE)”에 대한 1개의 생각