- 공유 링크 만들기
- X
- 이메일
- 기타 앱
이 글의 목적은 엑셀에서 조건에 맞는 데이터만 빠르게 추출하는 모든 방법을 체계적으로 정리하여 실무자가 상황별 최적 해법을 즉시 선택할 수 있도록 돕는 것이다.
1. 어떤 방법을 언제 쓰는가
| 방법 | 권장 버전 | 강점 | 제약·주의 | 대표 사용 시나리오 |
|---|---|---|---|---|
| 자동필터(필터) | 모든 버전 | 즉시 적용, UI 직관적이다 | 결과가 다른 시트로 복사되지 않는다 | 빠른 조회, 다중 선택, 텍스트/숫자/날짜 조건 |
| 고급 필터 | 모든 버전 | 다중 조건·복합 OR/AND, 다른 시트·범위로 복사 가능하다 | 조건범위 규칙을 정확히 이해해야 한다 | 보고서용 별도 범위 추출, 중복 제거 후 결과 복사 |
| FILTER 함수(동적 배열) | Microsoft 365 / 2021 이상 | 수식만으로 자동 갱신, 다중조건·연쇄 필터링이 쉽다 | 구버전 호환이 어렵다 | 대시보드, 실시간 조건 연동, 다단 정렬·고유값 결합 |
| 표(Table)+슬라이서 | 2013 이상 권장 | 클릭형 필터, 다중 선택·시각화가 쉽다 | 수식 출력이 아닌 보기(View) 중심이다 | 현장 조회용 리스트, 운영자용 필터 패널 |
| SUMIFS/COUNTIFS 등 | 모든 버전 | 조건 합계·개수를 즉시 계산한다 | 행 단위 추출이 아닌 집계 중심이다 | 조건부 KPI 산출, 조건부 통계 |
| DSUM/DGET 등 데이터베이스 함수 | 모든 버전 | 고급 필터와 동일 규칙의 조건범위를 사용한다 | 조건범위 관리가 번거롭다 | 문서화된 조건표 기반 집계 |
주의 : 조직 내 파일 공유 시 구버전 사용자에게는 FILTER 등 동적 배열 수식이 표시되지 않을 수 있으므로, 최종 공유본은 값 붙여넣기 또는 고급 필터로 결과를 복사해 제공하는 것이 안전하다.
2. 자동필터(기본 필터)로 빠르게 걸러내기
목록 머리글을 포함한 범위를 선택하고 Ctrl+Shift+L을 누르면 머리글에 드롭다운이 생성된다. 텍스트 필터·숫자 필터·날짜 필터를 통해 조건을 결합할 수 있다.
- 텍스트 필터: 포함, 시작 문자, 끝 문자, 와일드카드(?)·별표(*) 지원이다.
- 숫자 필터: 보다 큼/작음/사이 범위, 상위/하위 n개 항목이다.
- 날짜 필터: 연/분기/월/주/오늘·이번주·다음달 등 상대 기준이다.
예: "품목" 열에서 '밸브'를 포함하는 행만 보기 - 머리글 ▼ > 텍스트 필터 > 포함 > 밸브여러 열에 동시 조건을 적용하면 모두 AND로 연결된다. 같은 열에서 복수 값을 체크하면 OR로 결합된다.
주의 : 필터 결과에서 복사 시 숨겨진 행이 함께 붙는 문제가 있으면 Alt+;로 보이는 셀만 선택 후 복사한다.
3. 고급 필터: 다른 위치로 조건부 추출
고급 필터는 조건범위를 별도로 만들어 다중 조건의 AND/OR 논리를 명시하고, 결과를 다른 위치로 복사할 수 있다. 중복 레코드 제거도 가능하다.
3.1 조건범위 구성 규칙
- 조건범위의 첫 행은 원본 머리글과 동일한 캡션이어야 한다.
- 같은 행의 조건은 AND, 다른 행은 OR로 해석한다.
- 같은 열에 여러 조건을 세로로 나열하면 OR, 가로로 나란히 두면 AND이다.
- >=, <=, <>, = 연산자와 와일드카드를 지원한다.
| 원본 열 | 조건 예시 | 의미 |
|---|---|---|
| 지역 | =서울 | 지역이 정확히 서울이다 |
| 매출 | >=1000000 | 매출이 100만 이상이다 |
| 품목 | *밸브* | 품목에 "밸브"를 포함한다 |
| 일자 | >=2025-01-01 | 2025-01-01 이후이다 |
3.2 실행 절차
- 원본 범위를 머리글 포함으로 지정한다.
- 조건범위를 별도 영역에 작성한다.
- 데이터 > 고급을 클릭한다.
- 목록 범위, 조건 범위, 다른 위치에 복사할 경우 복사 위치를 지정한다.
- 필요 시 중복 레코드만 제거를 체크한다.
복합 조건 예시(AND+OR) [조건범위] 지역 | 매출 서울 | >=1000000 부산 | >=1500000
해석:
(지역=서울 AND 매출≥1,000,000) OR (지역=부산 AND 매출≥1,500,000)주의 : 조건범위 열 머리글이 원본과 한 글자라도 다르면 결과가 비거나 전체가 반환된다. 머리글 공백·숨은 문자에 특히 유의해야 한다.
4. FILTER 함수로 동적 조건 추출
FILTER는 배열을 조건식으로 거른 결과를 동적 범위로 반환한다. 원본 데이터가 바뀌면 결과가 자동 갱신된다.
4.1 기본 구문
=FILTER(array, include, [if_empty])array는 반환할 원본 범위이고, include는 TRUE/FALSE 배열이다. if_empty는 결과가 없을 때 표시할 값을 지정한다.
4.2 단일 조건
=FILTER(A2:D100, C2:C100>=DATE(2025,1,1), "해당 없음")4.3 다중 AND 조건
=FILTER(A2:D100, (B2:B100="서울")*(C2:C100>=1000000))곱셈(*)은 논리 AND를 의미한다.
4.4 다중 OR 조건
=FILTER(A2:D100, (B2:B100="서울")+(B2:B100="부산"))덧셈(+)은 논리 OR를 의미한다.
4.5 부분 일치(포함)
=FILTER(A2:D100, ISNUMBER(SEARCH("밸브", D2:D100)))4.6 날짜 범위
=FILTER(A2:D100, (C2:C100>=DATE(2025,1,1))*(C2:C100<=DATE(2025,12,31)))4.7 동적 기준 셀 연동
기준: G1=지역, G2=서울 수식: =FILTER(A2:D100, INDEX(A1:D1, XMATCH(G1, A1:D1, 0))=G2, "없음")4.8 UNIQUE·SORT와 결합
=SORT(FILTER(A2:D100, B2:B100="서울"), 3, -1) =UNIQUE(FILTER(B2:B100, C2:C100>=1000000))주의 : FILTER 결과는 스필 범위이므로 수식 우측·하단에 데이터가 있으면 #SPILL! 오류가 발생한다. 주변을 비우거나 LET로 범위를 명시해 충돌을 방지한다.
5. 다중 선택 UI를 위한 표(Table)·슬라이서
원본 범위를 표로 변환하면 구조적 참조와 슬라이서를 활용할 수 있다.
- 범위 선택 > Ctrl+T로 표 만들기이다.
- 표 도구 > 슬라이서 삽입 > 필드를 선택한다.
- 슬라이서에서 다중 값을 클릭해 필터링한다.
표 필터 상태는 SUBTOTAL로 집계 갯수를 표시할 수 있다.
=SUBTOTAL(103, 테이블1[품목]) // 103은 COUNTA(숨김 제외)이다6. 실무 예제: 복합 조건 보고서 추출
요구사항: 2025년 매출 ≥ 1,000,000이면서 지역이 서울 또는 부산인 거래를 일자 오름차순으로 정렬해 별도 시트로 보여준다.
=LET( src, 원본!A1:F10000, hdr, TAKE(src,1), body, DROP(src,1), dateCol, 3, cityCol, 2, amtCol, 5, cond, (INDEX(body,,dateCol)>=DATE(2025,1,1))* (INDEX(body,,dateCol)<=DATE(2025,12,31))* ((INDEX(body,,cityCol)="서울")+(INDEX(body,,cityCol)="부산"))* (INDEX(body,,amtCol)>=1000000), result, VSTACK(hdr, SORT(FILTER(body, cond), dateCol, 1)), result )구버전 사용자에게 동일 결과를 제공하려면 고급 필터로 조건범위를 구성한 후 다른 위치에 복사한다.
7. 텍스트·숫자·날짜별 조건 팁
7.1 텍스트
- 대소문자 무시 비교는 기본 동작이다.
- 정확 일치가 필요하면 "="와 동일한 길이를 강제한다.
=FILTER(A2:D100, EXACT("ABC", B2:B100)) // 대소문자 구분 정확 일치이다7.2 숫자
- 텍스트로 입력된 숫자는 VALUE로 변환 후 비교한다.
=FILTER(A2:D100, VALUE(C2:C100)>=1000)7.3 날짜
- TEXT로 표시된 날짜는 DATEVALUE로 변환한다.
=FILTER(A2:D100, (DATEVALUE(C2:C100)>=DATE(2025,1,1))*(DATEVALUE(C2:C100)<=DATE(2025,12,31)))주의 : 시스템 날짜 형식 설정이 다른 PC로 전달되면 날짜 비교가 어긋날 수 있다. 항상 DATE, DATEVALUE 등 함수로 날짜를 생성하여 지역 설정 영향을 줄이는 것이 좋다.
8. 다단 필터와 정렬 파이프라인
다수 조건을 순차로 적용하는 대신 하나의 FILTER에 논리를 모으거나, 여러 FILTER를 중첩할 수 있다.
=SORT( FILTER( FILTER(A2:F1000, B2:B1000="서울"), E2:E1000>=1000000 ), 3, 1 )또는 간결화를 위해 BYROW·MAP로 조건을 래핑한다.
=LET( arr, A2:F1000, keep, BYROW(arr, LAMBDA(r, INDEX(r,2)="서울") )* BYROW(arr, LAMBDA(r, INDEX(r,5)>=1000000)), FILTER(arr, keep) )9. 집계형 조건 추출 대체안
행 추출이 아니라 합계·개수만 필요하면 SUMIFS·COUNTIFS가 빠르다.
=SUMIFS(매출범위, 지역범위, "서울", 일자범위, ">="&DATE(2025,1,1), 일자범위, "<="&DATE(2025,12,31)) =COUNTIFS(품목범위, "*밸브*", 매출범위, ">=1000000")데이터베이스 함수는 고급 필터의 조건범위를 재사용한다.
=DSUM(A1:F1000, "매출", H1:I3) =DGET(A1:F1000, "거래처", H1:I3) // 조건 충족 단일 레코드 반환이다10. 중복 제거 vs 조건 추출
조건 후 고유값만 필요하면 UNIQUE를 결합한다.
=UNIQUE(FILTER(B2:B1000, C2:C1000>=1000000))고급 필터 대화상자의 중복 레코드만 선택은 결과 범위에만 적용되므로 원본을 변경하지 않는다. 원본을 바꾸려면 데이터 > 중복 제거를 사용한다.
11. 성능 최적화
- 대용량에서는 전체 열 참조 대신 정확 범위를 사용한다.
- LET로 중복 계산을 변수화한다.
- 필터링 열에 정렬 인덱스 열을 미리 생성해 SORT 비용을 줄인다.
=LET(src, A2:F200000, key, CHOOSECOLS(src,3), keep, key>=DATE(2025,1,1), FILTER(src, keep))12. 고급 필터 오류와 대처
| 증상 | 원인 | 해결 |
|---|---|---|
| 결과가 비어 있다 | 조건 머리글 불일치, 숨은 공백, 병합셀이다 | TRIM으로 머리글 정리, 병합 해제, 정확 매칭 확인이다 |
| 전체가 반환된다 | 조건 연산자 누락 또는 조건범위 공란이다 | >=, <= 등 연산자 포함, 조건 행에 값 입력이다 |
| #SPILL! 오류 | FILTER 스필 범위 충돌이다 | 주변 셀 비우기 또는 결과 위치 변경이다 |
| 부분 일치가 안 된다 | 와일드카드 미사용 또는 검색 함수 오용이다 | *키워드* 패턴, SEARCH/ FIND 사용이다 |
13. 입력 검증과 데이터 정리
- 데이터 유효성 검사로 기준 목록을 강제하면 필터 품질이 향상된다.
- TRIM, CLEAN, VALUE, DATEVALUE 등으로 형식을 정규화한다.
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")) // 비가시 공백 제거이다 =VALUE(B2) // 텍스트 숫자 변환이다 =DATEVALUE(C2) // 텍스트 날짜 변환이다14. 버튼 UI로 필터 자동화(매크로 없이)
양식 컨트롤 콤보상자에 선택 값을 셀에 반환하게 하고, 해당 셀을 FILTER 조건으로 연결하면 무매크로 인터랙티브 보고서를 만들 수 있다.
=LET(sel, $G$2, FILTER(A2:F1000, B2:B1000=sel, "해당 없음"))15. Power Query와의 경계
반복적 정제·조인·여러 파일 통합에는 Power Query가 적합하다. 그러나 사용자 상호작용에 따른 즉시 조건 변경과 빠른 조회는 FILTER·고급 필터가 유리하다.
16. 체크리스트
| 항목 | 확인 | 비고 |
|---|---|---|
| 머리글 정확 일치 | □ | 공백·숨은 문자 제거이다 |
| 조건 범위 규칙 적용 | □ | AND=같은 행, OR=다른 행이다 |
| 스필 충돌 점검 | □ | #SPILL! 예방이다 |
| 구버전 호환 전략 | □ | 값 붙여넣기 또는 고급 필터 복사이다 |
| 성능 최적화 | □ | 정확 범위+LET이다 |
17. 실습용 데이터 레이아웃 예시
열: 거래ID | 지역 | 일자 | 품목 | 매출 예시: T0001 | 서울 | 2025-01-03 | 볼밸브 | 1500000 T0002 | 부산 | 2025-02-10 | 체크밸브 | 2200000 T0003 | 대전 | 2024-12-20 | 게이트밸브 | 900000 T0004 | 서울 | 2025-03-01 | 플랜지 | 1300000위 데이터로 다음 수식을 검증한다.
=FILTER(A2:E100, (B2:B100={"서울","부산"})*(C2:C100>=DATE(2025,1,1))*(E2:E100>=1000000))18. 보이는 셀만 복사와 인쇄
필터 후 요약만 전달하려면 보이는 셀만 복사한다.
보이는 셀만 선택: Alt+; 인쇄 시 숨겨진 행 제외: 파일 > 인쇄 > 설정 > 선택 영역 인쇄이다19. 고급: 다중 기준 범주 매핑 후 필터
세부 품목을 상위 카테고리로 매핑하고 카테고리 단위로 필터링한다.
매핑표: 품목코드 | 카테고리 XLOOKUP으로 상위 카테고리 열 추가: =LET(code, D2:D1000, XLOOKUP(code, 매핑!A:A, 매핑!B:B, "기타")) 그 후: =FILTER(A2:F1000, F2:F1000="밸브류")20. 품질 보증을 위한 테스트 케이스
- 조건 결과가 0건일 때 if_empty 표시가 적절한가 확인한다.
- 특수문자·비가시 공백을 포함한 텍스트가 제대로 필터되는지 확인한다.
- 날짜 경계값 포함 여부(≥, ≤)를 명확히 검증한다.
- 슬라이서 다중 선택과 수식 결과가 일치하는지 비교한다.
FAQ
고급 필터로 다른 시트에 바로 복사할 수 있나?
가능하다. 고급 필터 대화상자에서 "다른 위치에 복사"를 선택하고 대상 시트의 셀을 선택하면 된다.
FILTER와 고급 필터의 가장 큰 차이는 무엇인가?
FILTER는 수식 기반 동적 결과이며 원본 변경 시 자동 갱신한다. 고급 필터는 명령 기반으로 한 번 실행하면 결과가 고정되며 보고서 복사에 유리하다.
부분 일치 검색이 느릴 때 개선 방법은?
SEARCH를 다수 열에 동시에 걸지 말고 필요한 열만 대상으로 제한한다. 데이터 전처리로 키 컬럼을 만들어 두면 성능이 개선된다.
구버전 사용자와 파일을 공유해야 한다.
최종 시트의 스필 결과를 값으로 붙여넣기 하거나 고급 필터 복사본을 제공한다.
필터 후 보이는 값만 합계하려면?
SUBTOTAL 또는 AGGREGATE를 사용한다. 예: =SUBTOTAL(9, E2:E1000)이다.