FILTER·SORT·UNIQUE 함수 3가지로 목록 뽑기
매달 초가 되면 담당자별로 "완료" 상태인 항목만 골라서 팀장에게 보고해야 하는 사람들이 있다. 원본 표에 필터를 걸고, 보이는 셀만 복사해서 새 시트에 붙여넣고, 다음 달이 되면 또 같은 작업을 반복한다. 문제는 원본 데이터가 중간에 몇 줄 추가되거나 상태 값이 바뀌었을 때다. 필터 조건을 다시 거는 걸 깜빡하고 예전 결과를 그대로 보고서에 붙여넣는 실수가 생긴다.
실무에서는 원본 데이터가 계속 늘어나는 상황에서 매번 필터를 다시 걸거나, 정렬 순서를 손으로 맞추는 경우가 흔하다. 특히 여러 사람이 같이 보는 보고서라면 "이거 언제 기준 데이터야?"라는 질문을 받기 쉽다. FILTER, SORT, UNIQUE 세 함수를 쓰면 조건에 맞는 목록을 수식 하나로 뽑아내고, 원본이 바뀌는 순간 결과도 같이 갱신되게 만들 수 있다. 마우스로 필터를 거는 대신 수식이 알아서 목록을 관리해주는 셈이다.
1. FILTER 함수로 조건에 맞는 행만 뽑기
FILTER 함수는 표에서 조건에 맞는 행만 골라내는 함수다. 기본 구조는 다음과 같다.
=FILTER(범위, 조건범위=조건값)
예를 들어 A2:D8에 담당자·카테고리·상태·금액이 있고, 상태 값이 "완료"인 행만 보고 싶다면 다음처럼 쓴다.
=FILTER(A2:D8, C2:C8="완료")
F2셀 하나에 이 수식을 넣으면 조건에 맞는 행이 F2를 기준으로 아래쪽에 자동으로 펼쳐진다. 이렇게 수식 하나의 결과가 여러 셀에 걸쳐 자동으로 채워지는 것을 "스필(Spill)"이라고 부른다. 별도 셀에 값을 복사해 넣을 필요 없이, 원본 데이터가 바뀌면 F2의 결과도 그대로 따라 바뀐다.
조건을 여러 개 걸고 싶다면 AND 조건은 *, OR 조건은 +로 연결한다.
=FILTER(A2:D8, (C2:C8="완료")*(B2:B8="홍보"))
2. UNIQUE 함수로 중복 제거하기
UNIQUE 함수는 범위에서 중복된 값을 제거하고 고유한 값만 남긴다. 지점명이 여러 번 반복해서 입력된 목록에서 지점 이름만 뽑고 싶을 때 쓴다.
=UNIQUE(B2:B9)
세 번째 인자를 TRUE로 주면 "딱 한 번만 등장한 값"만 남기는 것도 가능하다.
=UNIQUE(B2:B9, FALSE, TRUE)
3. SORT 함수와 중첩해서 정렬까지 한 번에
UNIQUE로 뽑은 목록은 원본에 등장한 순서 그대로 나온다. 여기에 정렬까지 적용하려면 SORT 함수로 감싸면 된다.
=SORT(UNIQUE(B2:B9), 1, 1)
두 번째 인자는 정렬 기준 열(1이면 첫 번째 열), 세 번째 인자는 정렬 방향(1은 오름차순, -1은 내림차순)이다. 이렇게 하면 중복 없이 정렬된 목록을 수식 하나로 얻을 수 있다.
4. 스필(Spill) 범위, 왜 이렇게 동작하는지 이해하기
FILTER, SORT, UNIQUE는 모두 "동적 배열 함수"라고 불린다. 결과값이 몇 개가 나올지 미리 정해져 있지 않고, 수식이 계산될 때마다 필요한 만큼 셀을 채운다는 뜻이다. 원본 데이터에 행이 추가되거나 조건에 맞는 값이 늘어나면 스필 범위도 자동으로 넓어진다. 반대로 결과가 줄어들면 스필 범위도 자동으로 줄어든다.
이 구조 덕분에 별도로 범위를 다시 지정하거나 수식을 아래로 끌어서 복사할 필요가 없다. 수식은 F2 한 칸에만 들어 있고, 나머지는 모두 "스필된 결과"다. 그래서 스필된 셀을 클릭해보면 수식 입력줄에 옅은 테두리로 스필 범위 전체가 표시된다.
스필된 범위 전체를 참조하고 싶다면 함수가 들어간 셀 뒤에#을 붙이면 된다. 예를 들어 F2에 FILTER 수식이 있다면F2#은 스필된 결과 전체를 가리킨다. SUM(F2#)처럼 다른 함수와 조합할 때 자주 쓰인다.
자주 막히는 지점 세 가지
1. #SPILL! 오류가 뜬다
증상: 수식을 입력했는데 결과 대신 #SPILL! 오류가 표시된다.
원인: 결과가 펼쳐져야 할 자리에 이미 다른 값이 입력되어 있어서 스필이 막힌 것이다. 표 형태로 데이터를 정리하다 보면 근처 셀에 메모나 다른 값을 남겨두는 경우가 있는데, 이게 스필 범위와 겹치면 오류가 난다.
해결: 스필될 것으로 예상되는 범위 안의 셀들을 모두 비워둔다. 특히 결과 개수가 늘어날 가능성이 있는 방향(보통 아래쪽)은 넉넉히 비워두는 게 안전하다.
2. 함수 이름을 입력했는데 #NAME? 오류가 뜬다
증상: FILTER, SORT, UNIQUE를 입력했더니 함수 자체를 인식하지 못하고 #NAME? 오류가 뜬다.
원인: 이 함수들은 Microsoft 365 구독 버전과 Excel 2021 이상에서만 지원된다. Excel 2019 이하 버전이나 회사에서 배포하는 오래된 버전에서는 함수 자체가 없다.
해결: 버전을 확인하고, 업그레이드가 어렵다면 기존 방식대로 자동 필터나 배열 수식(예: IFERROR와 INDEX/SMALL 조합)으로 대체해야 한다. 구글 스프레드시트에서는 FILTER, SORT, UNIQUE를 동일한 이름으로 지원한다.
3. 여러 조건을 걸었더니 결과가 이상하게 나온다
증상: 조건을 두 개 이상 걸었는데 결과가 하나도 안 나오거나, 예상보다 훨씬 많이 나온다.
원인: AND 조건에 +를 쓰거나 OR 조건에 *를 써서 연산자가 뒤바뀐 경우가 대부분이다. *는 두 조건을 모두 만족해야 하는 AND, +는 둘 중 하나만 만족해도 되는 OR로 동작한다.
해결: 조건마다 괄호로 묶고 원하는 논리에 맞는 연산자를 다시 확인한다. 조건이 세 개 이상으로 복잡해지면 조건마다 이름을 붙여 별도 셀에 나눠 계산한 뒤, 그 결과를 FILTER 조건으로 참조하는 방식이 오히려 더 읽기 쉽다.
자주 묻는 질문
FILTER 결과를 다른 함수와 같이 쓸 수 있나요?
가능하다. FILTER는 결과값을 배열로 반환하기 때문에 SUM, COUNTA, AVERAGE 같은 함수의 인자로 바로 넣을 수 있다. 예를 들어 =SUM(FILTER(D2:D8, C2:C8="완료"))처럼 쓰면 완료된 항목의 금액 합계를 별도 표 없이 바로 구할 수 있다.
스필된 결과를 값으로 고정하고 싶어요.
스필 범위를 선택한 뒤 복사하고, 같은 자리 또는 다른 위치에 "값으로 붙여넣기"를 하면 수식이 아닌 고정된 값으로 바뀐다. 특정 시점의 결과를 보관해야 할 때 유용하다. 단, 이후 원본 데이터가 바뀌어도 값으로 붙여넣은 결과는 더 이상 자동으로 갱신되지 않는다.
UNIQUE로 값이 몇 번 등장했는지도 알 수 있나요?
UNIQUE 자체는 등장 횟수를 세어주지 않는다. 등장 횟수가 필요하면 COUNTIF를 별도 열에서 함께 쓰는 방식을 추천한다. 예를 들어 UNIQUE로 뽑은 목록 옆 열에 =COUNTIF(B2:B9, F2#)처럼 스필 범위를 참조해서 각 항목의 등장 횟수를 나란히 표시할 수 있다.
조건을 셀 참조로 바꿔서 매번 다르게 쓸 수 있나요?
가능하다. C2:C8="완료" 대신 C2:C8=H1처럼 조건값이 들어 있는 셀을 참조로 바꾸면, H1 셀의 값만 바꿔서 원하는 조건으로 즉시 결과를 바꿀 수 있다. 드롭다운(데이터 유효성 검사)과 함께 쓰면 조건을 클릭 몇 번으로 바꾸는 간단한 필터 도구를 만들 수 있다.
구글 스프레드시트에서도 똑같이 동작하나요?
FILTER, SORT, UNIQUE는 구글 스프레드시트에서도 같은 이름과 비슷한 문법으로 지원된다. 다만 세부 인자 순서나 옵션에서 약간의 차이가 있을 수 있으므로, 옮겨서 쓸 때는 결과를 한 번 확인하는 게 안전하다.
정리
- FILTER는 조건에 맞는 행만 걸러내고, 결과는 수식이 있는 셀을 기준으로 자동으로 펼쳐진다(스필).
- UNIQUE는 중복을 제거하고, SORT와 중첩하면 정렬까지 한 번에 처리할 수 있다.
- 스필 범위 안에 다른 값이 있으면 #SPILL! 오류가 나므로 결과가 펼쳐질 자리를 비워둬야 한다.
- Excel 2019 이하에서는 이 함수들을 쓸 수 없으니 버전을 먼저 확인해야 한다.
- 조건을 셀 참조로 바꿔두면 값만 바꿔서 결과를 즉시 다시 뽑을 수 있는 간단한 도구가 된다.
관련 글
- 피벗테이블에 슬라이서 붙여서 대시보드처럼 만들기 — 함수 대신 슬라이서로 조건별 데이터를 걸러 보는 방법이 궁금하다면 함께 참고할 만하다.
- 반복 작업 줄이기 - 매크로 기록 — 매크로로 자동화하는 방법과 비교해보고 싶다면 이 글도 참고할 만하다.
댓글
댓글 쓰기