엑셀 FILTER·SORT·UNIQUE 완전정복 — 필터·정렬·중복제거를 수식 한 줄로 자동화하기

매달 원본 데이터를 복사해서 필터 걸고, 정렬하고, 중복 제거하고… 이 3단 작업을 수식 한 줄로 끝낼 수 있습니다. FILTER·SORT·UNIQUE 세 함수만 익히면 원본만 갈아 끼워도 결과가 자동으로 갱신됩니다.

필터·정렬·중복제거를 손으로 하던 시대는 끝났습니다

1. 먼저 확인 — 내 엑셀에서 되는 함수인가

세 함수 모두 엑셀 2021 이상 또는 Microsoft 365에서만 작동합니다. 2019·2016에서는 함수 이름을 쳐도 #NAME?가 뜹니다. 구글 스프레드시트에서는 셋 다 예전부터 사용 가능합니다.

함수	대체하는 기능	구문
FILTER	데이터 > 필터 / 고급 필터	=FILTER(배열, 조건, [없을때])
SORT	데이터 > 정렬	=SORT(배열, [열번호], [1/-1], [행열])
UNIQUE	데이터 > 중복된 항목 제거	=UNIQUE(배열, [열기준], [1회만])
SORTBY	보이지 않는 열 기준 정렬	=SORTBY(배열, 기준범위, [1/-1], …)

결정적인 차이는 원본을 건드리지 않는다는 점입니다. 리본 메뉴의 필터·정렬은 원본 표 자체를 바꾸지만, 이 함수들은 다른 위치에 결과만 뿌립니다. 원본 데이터가 바뀌면 결과도 실시간으로 따라옵니다.

2. FILTER — 조건에 맞는 행만 통째로 뽑기

예시 데이터는 B6:E13 범위에 [판매일자 / 거래처 / 상품 / 판매금액]이 들어 있다고 가정합니다.

■ 기본 — 상품이 '노트'인 행만
=FILTER($B$6:$E$13, $D$6:$D$13="노트", "찾는 자료가 없음")

■ AND 조건 — 곱셈(*)으로 연결
=FILTER($B$6:$E$13, ($D$6:$D$13="노트")*($E$6:$E$13>30000), "없음")

■ OR 조건 — 덧셈(+)으로 연결
=FILTER($B$6:$E$13, ($D$6:$D$13="노트")+($E$6:$E$13>30000), "없음")

■ 특정 열만 가져오기 (거래처·금액만)
=FILTER(CHOOSECOLS($B$6:$E$13,2,4), $D$6:$D$13="노트")

■ 특정 월만 (판매일자가 B열일 때)
=FILTER($B$6:$E$13, TEXT($B$6:$B$13,"yyyymm")="202608", "없음")

여기서 실수가 제일 많이 나옵니다. AND는 AND() 함수가 아니라 곱셈 *, OR은 OR()가 아니라 덧셈 +를 씁니다. 배열 대 배열 비교이기 때문에 일반 논리 함수는 하나의 TRUE/FALSE로 뭉개져서 원하는 결과가 나오지 않습니다.

3. 조건 칸을 비워두면 전체가 나오게 하는 법

실무에서 가장 쓸모 있는 패턴입니다. 조회 조건 셀(C17: 거래처, C18: 상품)을 만들어 두고, 비워두면 전체, 채우면 필터가 되게 합니다.

=FILTER($B$6:$E$13,
  IF($C$17="", ($B$6:$B$13=$B$6:$B$13), ($B$6:$B$13=$C$17)) *
  IF($C$18="", ($D$6:$D$13=$D$6:$D$13), ($D$6:$D$13=$C$18)),
  "찾는 자료가 없음")

원리는 간단합니다. 조건이 비어 있으면 범위=범위라는 항상 참인 식을 넣어 전체를 통과시키고, 값이 있으면 실제 비교를 합니다. 조건 필드가 3개면 같은 형태를 3개 만들어 *로 이으면 됩니다. IF 없이 그냥 =C17로 쓰면 조건 하나만 비어도 결과가 0건이 되니 주의하세요.

조건을 표에 입력하는 방식으로 만들면 수식을 다시 안 고쳐도 됩니다

4. SORT·SORTBY — 정렬 버튼 없이 정렬하기

■ 4번째 열(금액) 내림차순
=SORT($B$6:$E$13, 4, -1)

■ 2개 기준 정렬 (거래처 오름차순 → 금액 내림차순)
=SORT($B$6:$E$13, {2,4}, {1,-1})

■ 결과 표에 없는 열을 기준으로 정렬
=SORTBY($B$6:$D$13, $E$6:$E$13, -1)

■ 필터 + 정렬 조합
=SORT(FILTER($B$6:$E$13, $D$6:$D$13="노트"), 4, -1)

■ 상위 3건만
=TAKE(SORT(FILTER($B$6:$E$13,$D$6:$D$13="노트"),4,-1), 3)

정렬 방향은 1이 오름차순, -1이 내림차순입니다. 여러 기준을 쓸 때는 중괄호 배열 {2,4}로 넣습니다. TAKE 함수는 M365·2024 버전부터 쓸 수 있으니, 안 되면 =SORT(...) 결과 위쪽 3행만 참조하면 됩니다.

5. UNIQUE — 중복 제거와 "딱 한 번만 나온 값"

UNIQUE에는 잘 안 알려진 기능이 하나 더 있습니다. 세 번째 인수를 TRUE로 주면 중복 제거가 아니라 단 한 번만 등장한 값만 뽑습니다. 중복 입력 잡을 때 유용합니다.

■ 거래처 목록에서 중복 제거
=UNIQUE($C$6:$C$13)

■ 중복 제거 + 가나다순
=SORT(UNIQUE($C$6:$C$13))

■ 딱 한 번만 나온 값 (중복 아닌 것만)
=UNIQUE($C$6:$C$13, FALSE, TRUE)

■ 조건에 맞는 것 중 고유값만
=UNIQUE(FILTER($C$6:$C$13, $D$6:$D$13="노트"))

■ 두 열 조합의 고유 쌍
=UNIQUE($C$6:$D$13)

6. 실전 조합 — 자동 갱신 보고서 3종 세트

세 함수를 엮으면 매달 손댈 게 없는 요약 시트가 만들어집니다. 아래 두 수식을 A2, B2에 넣으면 부서 목록과 부서별 합계가 자동으로 만들어집니다.

■ A2 : 고유 거래처 목록 (가나다순)
=SORT(UNIQUE($C$6:$C$13))

■ B2 : 각 거래처 합계 — A2#으로 스필 범위 전체 참조
=SUMIF($C$6:$C$13, A2#, $E$6:$E$13)

■ C2 : 각 거래처 건수
=COUNTIF($C$6:$C$13, A2#)

여기서 A2#가 핵심입니다. 뒤에 붙는 #(스필 범위 연산자)는 "A2에서 흘러나온 결과 전체"를 뜻합니다. 거래처가 5개든 50개든 범위를 다시 잡을 필요가 없습니다. 드롭다운 목록의 원본으로 =A2#를 지정해두면 목록도 같이 자동 갱신됩니다.

7. 오류 3가지와 해결법

오류	원인	해결
#SPILL!	결과가 뿌려질 아래·오른쪽 칸에 이미 값이 있음	해당 칸을 비우거나 수식을 빈 영역으로 이동
#CALC!	FILTER 결과가 0건인데 세 번째 인수를 안 넣음	=FILTER(…, …, "없음")처럼 대체값 지정
#NAME?	엑셀 2019 이하 — 함수 자체가 없음	고급 필터 또는 INDEX/SMALL 배열수식으로 대체

추가로 알아둘 함정 하나. 표(Ctrl+T로 만든 엑셀 표) 안에서는 스필이 작동하지 않습니다. 표 안에 FILTER를 넣으면 #SPILL!이 뜨니, 수식은 표 바깥에 두고 표를 참조하는 방식으로 쓰세요.

정리 — 오늘 딱 3줄만 외우면 됩니다

조건으로 뽑을 땐 FILTER, 순서를 바꿀 땐 SORT, 목록을 만들 땐 UNIQUE. 그리고 이 셋을 겹쳐 쓰면 =SORT(UNIQUE(FILTER(…))) 한 줄로 파워쿼리 없이도 자동 보고서가 완성됩니다. 마지막으로 스필 범위 연산자 #까지 익히면, 데이터가 몇 줄로 늘어나든 수식을 다시 고칠 일이 없어집니다.