엑셀 배열수식 FILTER SORT UNIQUE 함수 사용법 - 동적 배열 함수

기존 엑셀 함수는 하나의 셀에 하나의 결과만 반환했어요.
Excel 365와 2021 버전부터는 하나의 수식이 여러 셀에 결과를 자동으로 채우는 "동적 배열" 기능이 추가됐습니다.

 

FILTER, SORT, UNIQUE는 대표적인 동적 배열 함수예요.
별도의 도우미 열이나 복잡한 수식 없이도 데이터를 조건별로 추출하고, 정렬하고, 중복을 제거할 수 있습니다.

 

사용 가능 버전: Excel 365, Excel 2021 이상

동적 배열이란

동적 배열 함수는 결과가 여러 값일 때, 인접한 빈 셀에 자동으로 결과를 "흘려보내(spill)" 줘요.
수식은 첫 번째 셀에만 입력하면 됩니다.

 

결과가 흘러나온 범위를 "스필 범위"라고 하고, 파란색 테두리로 표시돼요.
스필 범위에 이미 데이터가 있으면 #SPILL! 오류가 발생합니다.

UNIQUE 함수 - 중복 제거

=UNIQUE(범위)

 

범위에서 고유한 값만 추출해요.
별도 작업 없이 수식 하나로 중복 없는 목록을 만들 수 있습니다.

 

예시 데이터 (A열: 부서):

A
영업팀
개발팀
영업팀
기획팀
개발팀
인사팀

 

C1에 입력:

=UNIQUE(A2:A7)

결과 (C1:C4에 자동 표시):

C
영업팀
개발팀
기획팀
인사팀

 

데이터가 추가되면 UNIQUE 결과도 자동 갱신돼요.
9편에서 다룬 드롭다운 목록의 원본으로 활용하면, 데이터 기반의 동적 드롭다운을 만들 수 있습니다.

SORT 함수 - 정렬

=SORT(범위, [정렬기준열], [정렬순서])

 

인수	설명
범위	정렬할 데이터 범위
정렬기준열	몇 번째 열 기준으로 정렬할지 (기본: 1)
정렬순서	1 = 오름차순 (기본), -1 = 내림차순

 

예시 데이터:

	A	B
1	제품	매출
2	노트북	520
3	모니터	380
4	키보드	150
5	마우스	90

 

D1에 입력 (매출 기준 내림차순):

=SORT(A2:B5, 2, -1)

결과:

D	E
노트북	520
모니터	380
키보드	150
마우스	90

 

원본 데이터는 그대로 유지되고, 정렬된 결과만 별도로 표시돼요.

FILTER 함수 - 조건에 맞는 데이터 추출

=FILTER(범위, 조건, [일치없을때])

 

인수	설명
범위	추출할 데이터 범위
조건	TRUE/FALSE 배열을 반환하는 조건식
일치없을때	조건에 맞는 데이터가 없을 때 표시할 값 (선택)

 

예시 데이터:

	A	B	C
1	이름	부서	매출
2	김민수	영업팀	450
3	이지은	개발팀	200
4	박준호	영업팀	380
5	최수정	기획팀	310
6	정민우	영업팀	520

 

영업팀만 추출:

=FILTER(A2:C6, B2:B6="영업팀")

결과:

김민수	영업팀	450
박준호	영업팀	380
정민우	영업팀	520

 

매출 400 이상만 추출:

=FILTER(A2:C6, C2:C6>=400)

결과:

김민수	영업팀	450
정민우	영업팀	520

여러 조건 결합

AND 조건 (두 조건 모두 충족):

=FILTER(A2:C6, (B2:B6="영업팀")*(C2:C6>=400))

 

OR 조건 (하나라도 충족):

=FILTER(A2:C6, (B2:B6="영업팀")+(B2:B6="기획팀"))

 

AND는 곱셈(*), OR는 덧셈(+)으로 조건을 연결해요.

일치하는 데이터가 없을 때

세 번째 인수를 지정하지 않으면 #CALC! 오류가 나요.
오류 방지를 위해 메시지를 지정하세요.

=FILTER(A2:C6, B2:B6="재무팀", "해당 데이터 없음")

함수 조합 - SORT + FILTER

FILTER로 추출한 결과를 SORT로 정렬할 수 있어요.

 

영업팀 데이터를 매출 기준 내림차순으로:

=SORT(FILTER(A2:C6, B2:B6="영업팀"), 3, -1)

결과:

정민우	영업팀	520
김민수	영업팀	450
박준호	영업팀	380

SORTBY 함수 - 별도 열 기준 정렬

=SORTBY(정렬할범위, 기준범위, [정렬순서])

 

SORT는 범위 안의 열 번호로 기준을 지정하지만, SORTBY는 범위 밖의 열도 기준으로 사용할 수 있어요.

=SORTBY(A2:A6, C2:C6, -1)

이름(A열)을 매출(C열) 기준 내림차순으로 정렬합니다.

자주 하는 실수 / 주의사항

1. #SPILL! 오류

결과가 흘러나올 셀에 이미 데이터가 있으면 발생해요.
스필 범위의 셀을 비우면 해결됩니다.

2. Excel 2019 이하에서는 사용 불가

FILTER, SORT, UNIQUE는 Excel 365 또는 2021 이상에서만 작동해요.
이전 버전에서는 고급 필터, 정렬 기능, 중복 제거 기능을 대신 사용해야 합니다.

3. 스필 범위를 참조할 때는 # 기호 사용

동적 배열 결과 전체를 다른 수식에서 참조하려면 첫 셀 뒤에 #을 붙여요.

=SUM(E1#)

E1에서 시작하는 스필 범위 전체의 합계를 구합니다.

관련 함수 안내

동적 배열 함수에는 SEQUENCE(연속 숫자 생성), RANDARRAY(랜덤 배열 생성) 등도 있어요.
13편에서 다룬 OFFSET 동적 범위를 FILTER 함수로 대체하면 더 간결한 수식을 만들 수 있습니다.

 

다음 글에서는 오류 처리 함수 IFERROR, ISERROR, IFNA를 다룰 예정이에요.
수식 오류를 깔끔하게 처리하는 방법입니다.