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

동적 배열 수식은 값을 두 개 이상 돌려주는 수식이에요. 엑셀은 결과를 필요한 만큼의 셀에 나눠 넣어 줘요. 수식은 셀 하나에 한 번만 입력하면, 결과가 아래나 옆으로 분산(spill)돼요. 원본 데이터가 바뀌면 분산된 목록도 저절로 늘어나고, 줄어들고, 순서가 바뀌어요. 수식을 아래로 복사할 필요도, 범위를 늘릴 필요도 없어요.

아래 예시는 모두 A1:D7에 있는 주문 여섯 건을 사용해요.

	A	B	C	D
1	지역	제품	수량	매출
2	East	Core	42	8,400
3	West	Plus	27	7,425
4	East	Plus	35	9,625
5	Central	Core	31	6,200
6	West	Elite	18	7,200
7	Central	Plus	24	6,600

지역, 제품, 수량, 매출이 담긴 주문 여섯 건이에요. F~H열은 분산 결과를 받을 수 있게 비워 뒀어요.

분산은 이렇게 동작해요

F2를 클릭하고 다음 수식을 입력한 뒤 Enter를 누르세요.

=UNIQUE(A2:A7)

엑셀이 F2:F4에 지역 세 개, East, West, Central을 돌려줘요. 수식이 들어 있는 셀은 F2뿐이에요. F3과 F4에 보이는 값은 F2 수식의 결과예요.

	A	B	C	D	F
1	지역	제품	수량	매출
2	East	Core	42	8,400	East
3	West	Plus	27	7,425	West
4	East	Plus	35	9,625	Central
5	Central	Core	31	6,200
6	West	Elite	18	7,200
7	Central	Plus	24	6,600

F2의 수식 하나가 결과 세 개를 F2:F4로 분산해요. 엑셀은 분산 범위 둘레에 파란 테두리를 그려요.

분산 범위(spill range)에 대해 알아 둘 점이 있어요.

첫 번째 셀만 고치세요. F3을 클릭하면 수식 입력줄에 수식이 흐리게 보여요. 읽을 수는 있지만 거기서 고칠 수는 없어요. 결과를 바꾸려면 F2를 고치세요.
크기가 저절로 바뀌어요. A8에 새 지역 North를 추가하고 수식 범위를 A2:A8로 넓히면, 복사하지 않아도 목록이 네 개로 늘어나요. 원본이 엑셀 표라면 새 행을 자동으로 포함해요.
빈 셀이 필요해요. 결과가 들어갈 자리에 무언가 있으면 결과 대신 #SPILL!이 표시돼요.

	A	B	C	D	F
1	지역	제품	수량	매출
2	East	Core	42	8,400	#SPILL!
3	West	Plus	27	7,425
4	East	Plus	35	9,625	확인
5	Central	Core	31	6,200
6	West	Elite	18	7,200
7	Central	Plus	24	6,600

F4에 입력한 '확인'이 분산을 막아서 F2에 #SPILL!이 표시돼요. F4를 지우면 목록이 나타나요.

무엇이 막고 있는지 찾으려면 오류가 난 셀을 클릭하고, 옆에 나타나는 경고 아이콘을 클릭한 다음 방해하는 셀 선택(Select Obstructing Cells)을 고르세요. 그 셀을 지우거나 옮기면 결과가 다시 나타나요.

UNIQUE로 고유 값 목록 만들기

=UNIQUE(array, [by_col], [exactly_once])

array는 값을 가져올 범위예요.
by_col은 생략할 수 있어요. 생략하면 행끼리 비교하는데, 보통의 열에서는 이게 맞아요.
exactly_once도 생략할 수 있어요. TRUE를 넣으면 한 번만 나오는 값만 돌려줘요.

UNIQUE는 값이 처음 나온 순서를 그대로 지켜요. 그래서 East, West, Central 순서예요. 쓸모 있는 변형이 두 가지 있어요.

=UNIQUE(B2:B7,,TRUE)는 주문 한 건에만 나오는 제품인 Elite를 돌려줘요. (Core는 두 번, Plus는 세 번 나와요.) 쉼표 두 개는 by_col 인수를 건너뛰는 거예요.
=COUNTA(UNIQUE(A2:A7))는 서로 다른 지역의 개수인 3을 돌려줘요. "거래한 고객이 몇 곳이지?" 같은 질문에 빠르게 답할 수 있어요.

SORT와 SORTBY로 결과 정렬하기

=SORT(array, [sort_index], [sort_order], [by_col])

sort_index는 배열의 몇 번째 열을 기준으로 정렬할지예요. 첫 번째 열은 1, 두 번째 열은 2예요. 기본값은 1이에요.
sort_order는 오름차순(가나다순, 작은 값부터)이면 1이고 기본값이에요. 내림차순이면 -1이에요.

UNIQUE를 SORT로 감싸면 알파벳순 목록이 돼요.

=SORT(UNIQUE(A2:A7))

결과는 Central, East, West예요. 매출을 큰 값부터 정렬하려면 열 번호와 -1을 넣으세요.

=SORT(D2:D7,1,-1)

결과는 9,625, 8,400, 7,425, 7,200, 6,600, 6,200이에요. 흔한 실수가 =SORT(D2:D7,-1)이에요. 두 번째 인수는 정렬 순서가 아니라 기준 열이라서, 거기에 -1을 넣으면 #VALUE!가 나와요.

SORTBY는 결과에 포함되지 않는 범위를 기준으로도 정렬할 수 있어요. 다음 수식은 매출이 큰 순서대로 각 주문의 지역과 제품을 돌려주고, 매출 자체는 보여 주지 않아요.

=SORTBY(A2:B7,D2:D7,-1)

결과의 첫 행은 East와 Plus(9,625), 마지막 행은 Central과 Core(6,200)예요.

FILTER로 조건에 맞는 행 가져오기

=FILTER(array, include, [if_empty])

array는 돌려줄 범위예요. 열 하나여도 되고 여러 개여도 돼요.
include는 C2:C7>30처럼 행마다 TRUE나 FALSE를 내는 조건이에요. array와 높이가 같아야 해요.
if_empty는 조건에 맞는 행이 없을 때 표시할 값이에요.

수량이 30을 넘는 주문의 제품을 모두 나열하려면 다음과 같이 입력하세요.

=FILTER(B2:B7,C2:C7>30)

결과는 Core, Plus, Core예요(수량이 42, 35, 31인 주문). UNIQUE와 달리 FILTER는 중복을 그대로 남겨요. 고유 값이 아니라 행을 돌려주기 때문이에요.

여러 열을 지정하면 레코드 전체를 가져올 수 있어요. =FILTER(A2:D7,A2:A7="West")는 West 주문 두 건을 네 열 모두 돌려줘요.

조건이 두 개 이상일 때

조건마다 괄호로 감싸고 다음처럼 결합하세요.

모두 참이어야 할 때: 조건끼리 곱하세요. =FILTER(A2:D7,(A2:A7="East")*(C2:C7>40))는 East, Core, 42, 8,400 한 행을 돌려줘요.
하나만 참이어도 될 때: 조건끼리 더하세요. =FILTER(B2:B7,(A2:A7="East")+(A2:A7="Central"))는 Core, Plus, Core, Plus를 돌려줘요.

여기서는 AND나 OR를 쓸 수 없어요. 이 함수들은 행마다 확인하지 않고 범위 전체를 TRUE나 FALSE 하나로 합쳐 버려요.

맞는 행이 없을 때

조건을 통과하는 행이 하나도 없으면 FILTER는 #CALC! 오류를 돌려줘요. 대신 표시할 값을 세 번째 인수로 넣으세요.

=FILTER(A2:A7,C2:C7>50,"없음")

수량이 50을 넘는 주문이 없으니 셀에 없음이 표시돼요.

함수 조합하기: 저절로 바뀌는 정렬 목록

함수마다 배열을 돌려주기 때문에, 한 함수의 결과를 다른 함수에 넘길 수 있어요. 다음 수식은 수량이 30을 넘는 주문의 제품, 수량, 매출을 매출이 큰 순서대로 나열해요.

=SORT(FILTER(B2:D7,C2:C7>30),3,-1)

FILTER가 세 열(제품, 수량, 매출)을 돌려주므로, SORT의 3은 "그중 세 번째 열", 즉 매출을 기준으로 정렬하라는 뜻이에요.

	A	B	C	D	F	G	H
1	지역	제품	수량	매출
2	East	Core	42	8,400	Plus	35	9,625
3	West	Plus	27	7,425	Core	42	8,400
4	East	Plus	35	9,625	Core	31	6,200
5	Central	Core	31	6,200
6	West	Elite	18	7,200
7	Central	Plus	24	6,600

FILTER가 수량 30 초과 주문 세 건을 남기고, SORT가 매출이 큰 순서로 정렬해요. 수량을 하나라도 바꾸면 목록이 업데이트돼요.

C3을 27에서 40으로 바꾸면 West의 Plus 주문이 수식을 고치지 않아도 알맞은 자리에 끼어들어요.

SEQUENCE로 번호 매기기, TAKE와 DROP으로 상위 N개 남기기

=SEQUENCE(rows, [columns], [start], [step])

=SEQUENCE(6)은 열 아래로 1부터 6까지 돌려줘요. =SEQUENCE(3,1,10,10)은 10, 20, 30을 돌려줘요. 번호 개수가 항상 데이터와 맞도록 하려면 목록의 크기를 기준으로 삼으세요.

=SEQUENCE(ROWS(A2:A7))

TAKE는 배열의 앞에서부터(음수를 넣으면 뒤에서부터) 행을 남기고, DROP은 행을 빼요. SORT에 이어 붙이면 상위 N개 목록이 돼요.

=TAKE(SORT(D2:D7,1,-1),3)

결과는 가장 큰 매출 세 개, 9,625, 8,400, 7,425예요. =TAKE(SORT(D2:D7,1,-1),-2)는 마지막 두 개(6,600과 6,200)를, =DROP(SORT(D2:D7,1,-1),1)은 가장 큰 값을 뺀 나머지를 모두 돌려줘요.

#으로 분산 범위 전체 참조하기

분산된 결과를 다른 수식에서 쓰려면 첫 번째 셀 뒤에 #을 붙여 참조하세요. F2에 =UNIQUE(A2:A7)이 있다면 다음과 같아요.

=COUNTA(F2#)

결과는 3이에요. 목록이 늘거나 줄어도 결과가 계속 맞아요. F2#은 언제나 "F2의 분산 범위 전체, 현재 크기 그대로"를 뜻하거든요. 수식을 쓰다가 분산 범위 전체를 선택하면 엑셀이 # 참조를 대신 넣어 줘요.

텍스트 목록에는 COUNTA를 쓰세요. COUNT는 숫자만 세기 때문에 지역 목록에 =COUNT(F2#)를 쓰면 0이 나와요.

자주 하는 실수

남아 있는 데이터 때문에 생기는 #SPILL!. 막고 있는 셀을 비우거나, 공간이 넉넉한 곳으로 수식을 옮기세요. 막는 셀은 방해하는 셀 선택으로 찾을 수 있어요.
엑셀 표 안에 동적 배열 넣기. 분산되는 수식은 표의 열 안에 둘 수 없고 #SPILL!을 돌려줘요. 표 바깥에 수식을 두고 표를 참조하게 하세요.
수식을 아래로 복사하기. 수식은 셀 하나에만 있으면 돼요. 아래로 복사한 수식이 오히려 첫 수식의 분산을 막아요.
FILTER 안에서 AND나 OR 쓰기. TRUE나 FALSE 하나만 돌려줘요. AND 대신 조건을 곱하고, OR 대신 더하세요.
이전 버전 엑셀과 공유하기. Excel 2019 이하에는 이 함수들이 없어서 수식이 동작하지 않아요. 받는 사람이 어떤 버전을 쓰는지 먼저 확인하세요.

연습

저절로 바뀌는 고객 목록 만들기

내 고객 목록이나 판매 목록을 열거나, 위의 주문 여섯 건을 A1:D7에 입력하세요.
빈 열에 UNIQUE로 서로 다른 지역(또는 고객)을 나열하고 SORT로 정렬하세요.
그 옆에 SORT(FILTER(...)) 수식 하나로 기준값을 넘는 주문을 모두 가져와 큰 순서로 정렬하세요.
COUNTA와 분산 범위의 # 참조로 걸러진 행의 개수를 세세요.
원본 데이터에 새 행을 추가하고(표가 아니라면 범위를 늘리고), 모든 분산 결과가 저절로 늘어나는지 확인하세요.

먼저 여기서 연습해 보세요. 답은 모두 값 하나라서, 분산 목록을 COUNTA, INDEX, SUM으로 감싸서 구해요.

직접 해 보기4개 중 0개 완료

F2에 A2:A7에 서로 다른 지역이 몇 개 있는지 세어 보세요.
F3에 알파벳순으로 가장 앞에 오는 지역을 구해 보세요.
F4에 수량이 30을 넘는 주문의 매출 합계를 구해 보세요.
F5에 가장 큰 매출 세 건의 합계를 구해 보세요.

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E	F
1	지역	제품	수량	매출	질문	답
2	East	Core	42	8,400	지역 수
3	West	Plus	27	7,425	알파벳순 첫 지역
4	East	Plus	35	9,625	수량 30 초과 매출
5	Central	Core	31	6,200	매출 상위 3건 합계
6	West	Elite	18	7,200
7	Central	Plus	24	6,600

실력 점검

"분산"이 무슨 뜻인지, F2#이 무엇을 가리키는지 설명할 수 있나요?
수식 하나로 한 열의 고유 값 목록을 만들 수 있나요?
도우미 열 없이 두 조건을 모두 만족하는 행만 걸러 낼 수 있나요?
숫자를 직접 입력하지 않고 번호 목록을 만들 수 있나요?
데이터가 바뀌면 함께 업데이트되는 상위 3개 목록을 만들 수 있나요?

도움이 되었나요? 틀린 부분을 발견하셨나요? 피드백 보내기 · admin@arumsoft.com