FILTER · SORT · UNIQUE로 데이터 전처리 끝! (조건 필터·다중 정렬·중복 제거 1분 컷)
피벗 전 전처리 파이프라인을 한 줄 수식으로 자동화! OR/AND, 날짜 범위, 사용자 정의 정렬, 고유 목록까지 이 글 하나로 끝냅니다.
검수 고지: 이 글은 편집자가 실제 Excel 365에서 재현·검증했으며, 초안 정리 단계에서 AI 도구를 보조적으로 사용했습니다.
문법 & 빠른 시작
| 함수 | 형식 | 포인트 |
|---|---|---|
| FILTER | =FILTER(array, include, [if_empty]) | 조건식 TRUE/FALSE 배열 |
| SORT | =SORT(array,[sort_index],[sort_order],[by_col]) | 단일 열 기준 간편 |
| SORTBY | =SORTBY(array, by_array1, sort_order1, [by_array2, sort_order2]…) | 열별 세밀 제어 |
| UNIQUE | =UNIQUE(array,[by_col],[exactly_once]) | 행/열 고유, 1회만 값 |
// 예: 2025년, 지역=Seoul, 매출 내림차순 TOP 20
=TAKE(SORTBY(FILTER(A2:F10000,
(YEAR(Date)=2025)*(Region="Seoul")), Sales, -1), 20)FILTER — 조건식 레시피
AND / OR / NOT
// AND: 곱(*)
=FILTER(Data, (Region="Seoul")*(Category="Keyboard"))
// OR: 합(+)
=FILTER(Data, (Region="Seoul")+(Region="Busan"))
// NOT: 부정
=FILTER(Data, Category<>"Returns")부분일치/와일드카드
=FILTER(Data, ISNUMBER(SEARCH("pro", LOWER(Product))))날짜 범위(시각 섞임 안전형)
=FILTER(Data, (Date>=&DATE(2025,1,1))*(Date<EOMONTH(DATE(2025,1,1),2)+1))결측치 제외
=FILTER(Data, (Value<>"")*(Value<>#N/A))SORT / SORTBY — 다중 정렬
단일 키
=SORT(Data, 4, -1) // 4번째 열 내림차순다중 키
=SORTBY(Data, Region, 1, Sales, -1)사용자 정의 순서(라벨 우선순위)
=SORTBY(Data, XLOOKUP(Priority, {"High","Medium","Low"}, {1,2,3}), 1, Date, -1)UNIQUE — 고유 목록·개수
고유 목록
=UNIQUE(Category)고유 개수
=ROWS(UNIQUE(CustomerID))“한 번만 나타난 값”
=UNIQUE(Category,, TRUE) // exactly_once=TRUE파이프라인 조합
// 1) 필터 → 정렬 → 중복 제거
=UNIQUE(SORTBY(FILTER(A2:D, Dept="Sales"), Date, -1))
// 2) 고유 고객의 최신 주문
=LET(c, UNIQUE(Customer),
latest, MAP(c, LAMBDA(x, MAX(FILTER(Date, Customer=x)))),
HSTACK(c, latest))실무 패턴 12가지
① 최근 30일 상위 매출 10건
=TAKE(SORTBY(FILTER(Data, Date>=TODAY()-30), Sales, -1), 10)② 지역×월 교차표 원본 만들기
=LET(m, EOMONTH(Date,0), r, UNIQUE(Region),
MAKEARRAY(ROWS(r), ROWS(UNIQUE(m)),
LAMBDA(i,j, SUM(FILTER(Sales, (Region=INDEX(r,i))*(m=INDEX(UNIQUE(m),j)))))))③ ‘재구매 고객’만
=FILTER(Data, COUNTIF(Customer, Customer)>1)④ 품절 제외 + 재고 오름차순
=SORTBY(FILTER(Data, Stock>0), Stock, 1)⑤ 키워드 다건 OR 검색
=FILTER(Data, BYROW(Product, LAMBDA(r, SUM(--ISNUMBER(SEARCH(TRANSPOSE(Keywords), r)))>0 )))⑥ 담당자별 최신 상태
=LET(u, UNIQUE(Owner),
HSTACK(u, MAP(u, LAMBDA(x, TAKE(SORTBY(FILTER(Data, Owner=x), Date, -1),1)))))⑦ 정확히 한 번만 등장한 주문
=FILTER(Data, COUNTIF(OrderID, OrderID)=1)⑧ 고객 고유 목록 + 첫 구매일
=LET(c, UNIQUE(Customer), HSTACK(c, MAP(c, LAMBDA(x, MIN(FILTER(Date, Customer=x))))))⑨ 상위 N% 매출만
=LET(t, LARGE(Sales, ROUNDUP(COUNTA(Sales)*0.2,0)),
FILTER(Data, Sales>=t))⑩ 우선순위(사용자 정의) → 날짜 내림
=SORTBY(Data, XLOOKUP(Priority,{"P0","P1","P2","P3"},{0,1,2,3}), 1, Date, -1)⑪ 부서별 고유 인원수
=LET(d, UNIQUE(Dept), HSTACK(d, MAP(d, LAMBDA(x, ROWS(UNIQUE(FILTER(Name, Dept=x)))))))⑫ 필터 결과가 없으면 메시지
=IFERROR(FILTER(Data, Region="Mars"), "조건에 맞는 데이터가 없습니다")구버전 대체식 (FILTER/SORT/UNIQUE 미지원)
// FILTER ≈ INDEX/SMALL/IF (배열식; 365는 일반식)
=IFERROR(INDEX(A:A, SMALL(IF(조건, ROW(A:A)), ROWS($A$1:A1))), "")
// SORT ≈ SORTBY 대체: 헬퍼열로 키를 만들고 INDEX로 재배열
=INDEX(Data, MATCH(SMALL(키, ROWS($A$1:A1)), 키, 0), )
// UNIQUE ≈ 고급 필터 또는 피벗테이블 → 고유값TIP 배포용 파일은 표(CTRL+T)로 만들고 열 이름으로 수식을 작성하면 안정적입니다.
자주 하는 실수 & 체크
- #SPILL! → 스필 범위가 비어 있는지 확인(병합/메모/숨김 열 제거).
- 데이터 형식 → 날짜/숫자/텍스트 형식 혼합 시 NUMBERVALUE/DATEVALUE로 정규화.
- OR 기대 → FILTER의 OR은
+합. AND는*곱. - 성능 → 대규모 시트는 LET로 중복 계산 캐시, 범위는 표 열로 한정.
요약
| 목표 | 대표 공식 |
|---|---|
| 조건 필터 | FILTER(array, 조건) |
| 다중 정렬 | SORTBY(array, 키1,정렬, 키2,정렬) |
| 중복 제거/개수 | UNIQUE(array) / ROWS(UNIQUE(...)) |
| 파이프라인 | FILTER → SORTBY → UNIQUE |