UNIQUE/FILTER 함수 심화 – 동적 고유값 파이프라인 만들기

“고급 필터로 고유값 뽑고, 조건 바뀌면 다시…” 매번 반복하시나요? 엑셀 365의 동적 배열 함수인 UNIQUE와 FILTER를 조합하면, 원본 데이터가 바뀔 때 자동으로 갱신되는 동적 고유값 파이프라인을 만들 수 있습니다. 저는 실제 CRM·재고·설문 데이터에서 이 루틴을 표준화해, 주간 리포트 제작 시간을 절반 이하로 줄였습니다. 이 글에서 실무 경험을 바탕으로 세팅법과 핵심 공식을 단계별로 정리합니다.

1) 왜 동적 고유값 파이프라인인가?

실무에선 “지역=서울, 등급=A 고객의 고유 이메일 목록”처럼 조건에 따라 달라지는 고유값을 자주 원합니다. 과거엔 고급 필터를 매번 다시 돌려야 했지만, 지금은 FILTER로 조건을 걸고 그 결과에 UNIQUE를 얹으면 끝. 원본이 늘거나 값이 바뀌면 결과가 자동으로 재계산됩니다. 특히 캠페인 대상 추출, 재고 SKU 목록, 설문 고유 응답자 리스트에서 효과가 큽니다.

2) UNIQUE/FILTER 기본 문법과 동작 원리

UNIQUE

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

array: 고유값을 뽑을 범위
by_col: 열 기준 고유값이면 TRUE(기본은 행 기준)
exactly_once: 정확히 1회만 등장한 값만(중복이 한 번이라도 있으면 제외)

FILTER

=FILTER(array, include, [if_empty])

include: TRUE/FALSE 배열(조건)
if_empty: 결과가 없을 때 표시할 값(옵션)

스필(Spill): 동적 배열은 결과가 여러 셀로 자동 ‘스필’됩니다. 기준 셀에 표시되는 #을 붙여 참조(예: =B2#)하면 범위 확장에 자동 대응합니다.

3) 파이프라인 설계: 정규화 → 필터 → 고유값 → 정렬

현업에서 가장 잘 먹히는 4단 루틴입니다.

① 정규화(전처리)

공백 제거·대소문자 통일·불필요 문자 제거 등 텍스트 정리로 중복 오탐지를 줄입니다.

=TRIM(CLEAN(SUBSTITUTE(LOWER(A2),"  "," ")))

열 전체에 적용할 땐 보조열에 위 공식을 두고, 이후 단계에서 이 보조열을 참조하세요.

② 조건 필터

예: 지역이 “서울”이고 최근 접촉일이 지난 30일 이내

=FILTER(A2:D1000, (B2:B1000="서울") * (D2:D1000>=TODAY()-30), "결과 없음")

③ 고유값 추출

필터 결과에서 이메일(열 C)의 고유값만

=UNIQUE( FILTER(C2:C1000, (B2:B1000="서울") * (D2:D1000>=TODAY()-30) ) )

④ 정렬(선택)

사전순 정렬:

=SORT( UNIQUE( FILTER(C2:C1000, (B2:B1000="서울") * (D2:D1000>=TODAY()-30)) ) )

현장 팁: LET으로 중복 계산을 줄이면 성능과 가독성이 좋아집니다.

=LET(
  cities, B2:B1000,
  emails, C2:C1000,
  recent, D2:D1000,
  flt, FILTER(emails, (cities="서울")*(recent>=TODAY()-30)),
  SORT(UNIQUE(flt))
)

4) 다중 조건·다중 열 고유값(복합키) 처리

“지역+제품” 조합처럼 두 열 이상의 고유 조합이 필요할 때는 열을 묶어 UNIQUE에 투입합니다.

HSTACK로 열 결합 후 고유 조합

=UNIQUE( HSTACK(B2:B1000, C2:C1000) )

정렬까지 포함:

=SORT( UNIQUE( HSTACK(B2:B1000, C2:C1000) ), {1,2}, {1,1} )

조건 + 복합키

=UNIQUE( HSTACK( FILTER(B2:B1000, A2:A1000="서울"), FILTER(C2:C1000, A2:A1000="서울") ) )

정확히 1회만 등장(중복 전혀 없는 순수 고유)

=UNIQUE( C2:C1000, , TRUE )

마케팅에서 “이메일이 정확히 한 번만 등장한 순수 신규 유입” 같은 케이스에 유용합니다.

6) 데이터 품질 이슈(공백·대소문자·오타) 처리

① 빈 셀 제거 후 고유값

=UNIQUE( FILTER(C2:C1000, C2:C1000<>"") )

② 대소문자 통일

=UNIQUE( LOWER( FILTER(C2:C1000, C2:C1000<>"") ) )

③ 앞뒤 공백·숨은 문자 제거

=UNIQUE( TRIM( CLEAN( FILTER(C2:C1000, C2:C1000<>"") ) ) )

④ “비슷한 값” 통합(오타 방지)

완전 자동은 어렵지만, 사전 테이블을 만들어 XLOOKUP으로 표준화하면 정확도가 올라갑니다.

=LET(src, C2:C1000,
 map, XLOOKUP(src, 표준[원본값], 표준[표준값], src),
 UNIQUE(TRIM(LOWER(map)))
)

7) 성능·에러(#SPILL!) 최적화 팁

#SPILL!: 스필 결과 영역에 값/병합/도형이 있나 확인 → 비워주세요.
범위 한정: C:C 전체열 대신 C2:C100000처럼 상한을 두면 속도가 안정됩니다.
LET으로 중간 결과를 변수화해 중복 계산을 줄이세요.
대용량(10만+): 파워쿼리로 전처리하고 결과 테이블만 동적 함수로 받으면 쾌적합니다.

8) UNIQUE/FILTER vs 고급 필터/중복 제거 비교

기능	UNIQUE/FILTER(동적)	고급 필터	중복 제거
자동 갱신	✅ 원본 변경 시 즉시	❌ 수동 재실행	❌ 수동·원본을 직접 변경
원본 보존	✅ 보존(계산식)	✅ 결과만 복사	❌ 원본 수정
조건 유연성	✅ 수식으로 무한	✅ 조건 범위로 유연	❌ 기준 열 선택만
학습 난이도	△ 수식 이해 필요	△ 레이아웃 이해 필요	✅ 매우 쉬움

결론: 반복·자동화·재사용성은 UNIQUE/FILTER가 압도적입니다. 일회성 대청소는 ‘중복 제거’, 조건 추출+원본 보존은 ‘고급 필터’도 여전히 유효합니다.

9) FAQ – 자주 묻는 질문

Q1. 제 엑셀에 UNIQUE/FILTER가 없어요.

동적 배열은 Microsoft 365 최신 버전에 포함됩니다. 구버전에선 고급 필터의 ‘고유 레코드만’ 또는 파워쿼리로 대체하세요.

Q2. 스필 범위를 다른 시트에서 드롭다운으로 쓸 수 있나요?

가능합니다. 이름 정의로 =A2# 범위를 만들고, 유효성 검사 원본에 =이름을 넣으세요.

Q3. 대소문자 구분 고유값이 필요합니다.

UNIQUE는 기본이 대소문자 구분 없음입니다. 대소문자까지 구분하려면 EXACT 기반 표준화나 LOWER/UPPER 통일 후 보조 정보로 구분하는 전략이 현실적입니다.

Q4. 여러 열을 묶은 고유값을 리스트로 보여주고 싶어요.

HSTACK으로 열을 붙여 UNIQUE 후, TEXTAFTER/BEFORE 등으로 표시 문자열을 만들거나, 표시용 열을 따로 구성하세요.

10) 마무리 및 실무 적용 체크리스트

① 원본 정리(공백·대소문자 통일·숨은 문자 제거)
② FILTER로 조건 배열 생성
③ UNIQUE로 고유값 추출 → SORT로 정렬
④ 스필 범위 #로 이름 정의 → 유효성 검사 목록 연결
⑤ 대용량은 파워쿼리 전처리 + 동적 함수 조합

이 파이프라인을 템플릿화하면, 캠페인 대상·SKU 리스트·설문 응답자 등 반복 업무의 재작업이 사라집니다. 제 경험상 팀 전체 보고서 품질과 속도가 동시에 개선됩니다.

IT가이드

UNIQUE/FILTER 함수 심화 – 동적 고유값 파이프라인 만들기