고급 필터의 진정한 핵심이자 가장 강력한 기능인 '수식 기반 조건'은 엑셀이 데이터를 동적으로 판단하고 필터링하도록 만들어, 정교한 보고서 자동화와 데이터 분석을 가능하게 합니다.
고급 필터의 수식 기반 조건의 원리부터 시작하여, TODAY(), AVERAGE() 등 함수와 복합 논리 함수를 사용하여 동적으로 필터링 조건을 설정하는 방법을 집중적으로 다룰 예정입니다. 엑셀 데이터 탐색의 정교함을 완성하고, 여러분의 엑셀 스킬을 한 단계 업그레이드해 봅시다!
왜 고급 필터의 수식 기반 조건을 알아야 할까요?
자동 필터나 일반 고급 필터의 조건 범위는 특정 값("서울", >=100)을 직접 입력하는 방식입니다. 하지만 실무에서는 다음과 같은 더 복잡하고 동적인 필터링이 필요합니다.
- "오늘 날짜를 기준으로 마감일이 지난 프로젝트만 보고 싶다."
- "팀의 평균 매출액을 초과하는 직원의 실적만 보고 싶다."
- "두 개 이상의 열의 값을 비교하여 조건에 맞는 데이터를 찾고 싶다."
- "특정 조건 A를 만족하면서 동시에 특정 조건 B 또는 C를 만족하는 데이터를 필터링하고 싶다."
수식 기반 조건은 이러한 복잡한 논리(IF, AND, OR 등), 날짜 함수(TODAY), 통계 함수(AVERAGE) 등을 필터링 조건에 직접 활용할 수 있게 해줍니다. 이는 수동 필터링의 한계를 완전히 뛰어넘어, 보고서의 자동 업데이트와 데이터 기반 의사결정의 유연성을 극대화합니다.
수식 기반 조건의 핵심 원리 및 작성 규칙
고급 필터에서 수식을 조건으로 사용할 때, 다음 규칙들을 반드시 지켜야 합니다.
- 조건 범위의 머리글: 수식 조건이 있는 셀의 머리글(바로 위 셀)은 원본 데이터의 열 머리글과 달라야 합니다. 보통 빈 칸으로 두거나, "조건", "필터 기준"과 같이 원본 데이터에 없는 고유한 텍스트를 입력합니다. 엑셀은 이 머리글을 무시하고 오직 수식의 결과(TRUE/FALSE)만으로 필터링합니다.
- 수식은 첫 번째 데이터 행을 기준: 수식은 반드시 원본 데이터의 첫 번째 데이터 행(헤더 바로 아래 행)을 기준으로 작성해야 합니다. 엑셀이 이 수식을 다른 모든 행에 상대 참조로 적용하면서 필터링을 수행합니다.
- 예를 들어, 데이터가 A1:D100에 있고 헤더가 1행에 있다면, 수식은 A2, B2, C2 등을 기준으로 작성합니다.
- 결과는 TRUE 또는 FALSE: 수식의 결과는 필터링할 행에 대해 TRUE 또는 FALSE로 평가되어야 합니다.
- TRUE로 평가되면 해당 행이 필터링되어 표시됩니다.
- FALSE로 평가되면 해당 행이 숨겨집니다.
주요 내용들
다음 고급 필터의 수식 기반 조건 활용법들을 실무 예제와 함께 자세히 살펴보겠습니다.
- 날짜 함수를 이용한 동적 필터링: TODAY(), MONTH(), YEAR() 등
- 논리 함수를 이용한 복합 조건 필터링: AND, OR 함수 결합
- 통계 함수를 이용한 동적 필터링: AVERAGE() 등
- 다른 셀의 값 또는 함수의 결과에 따른 동적 필터링
실무 예제로 배우는 엑셀 필터링 마스터하기
각 수식 기반 조건을 실제 비즈니스 시나리오에 적용하여 데이터 탐색 및 관리의 고급 기술을 보여드리겠습니다.
샘플 데이터는 아래 '판매 기록' 테이블을 사용하겠습니다.
| 주문 ID | 고객명 | 제품 카테고리 | 제품명 | 판매액 (천 원) | 주문일 | 지역 |
| ORD001 | 김철수 | 전자제품 | 노트북 | 1,200 | 2025-05-10 | 서울 |
| ORD002 | 이영희 | 의류 | 티셔츠 | 80 | 2025-05-12 | 부산 |
| ORD003 | 박지성 | 전자제품 | 스마트폰 | 950 | 2025-05-15 | 서울 |
| ORD004 | 최민수 | 식품 | 과일 | 50 | 2025-05-18 | 대구 |
| ORD005 | 김철수 | 의류 | 바지 | 150 | 2025-05-20 | 부산 |
| ORD006 | 이영희 | 전자제품 | 태블릿 | 800 | 2025-06-01 | 서울 |
| ORD007 | 박지성 | 식품 | 채소 | 40 | 2025-06-05 | 대구 |
| ORD008 | 최민수 | 의류 | 스커트 | 110 | 2025-06-08 | 부산 |
| ORD009 | 김철수 | 전자제품 | 노트북 | 1,100 | 2025-06-10 | 대전 |
| ORD010 | 이영희 | 의류 | 코트 | 300 | 2025-06-15 | 서울 |
1. 날짜 함수를 이용한 동적 필터링
현재 날짜를 기준으로 '오늘', '이번 달', '지난달' 등 시시각각 변하는 조건을 필터링에 적용할 수 있습니다.
예제 목표: 오늘 날짜를 기준으로 '주문일'이 2025년 6월에 해당하는 주문만 표시하세요. (현재 날짜는 2025년 6월 21일입니다.)
해결 방법:
- 조건 범위 생성: 시트의 비어있는 공간(예: I1:I2)에 조건 범위를 만듭니다.
- I1 셀: 비워둡니다 (또는 "월 필터" 등 고유한 텍스트 입력).
- I2 셀: 다음 수식을 입력합니다. =MONTH(F2)=MONTH(TODAY())
- F2: 원본 데이터의 첫 번째 데이터 행인 '주문일' 셀을 참조합니다. (상대 참조)
- MONTH(F2): '주문일'의 월을 추출합니다.
- MONTH(TODAY()): 현재 날짜의 월을 추출합니다 (6월).
- 결과적으로 6=6 (TRUE) 또는 5=6 (FALSE) 등으로 평가됩니다.
- 데이터 범위 내 아무 셀 클릭 (예: A1).
- 데이터 탭 → 정렬 및 필터 그룹에서 고급 버튼 클릭.
- 고급 필터 대화 상자에서:
- 결과: 현재 위치에 필터 또는 다른 장소에 복사 선택.
- 목록 범위: 자동으로 A1:G11이 잡히는지 확인.
- 조건 범위: I1:I2를 선택.
- 확인을 클릭합니다.
결과: 2025년 6월에 발생한 주문(ORD006, ORD007, ORD008, ORD009, ORD010)만 표시됩니다. 다음 달(7월)에 파일을 열면 자동으로 7월 데이터가 필터링됩니다.
최상급 실무 활용: 월별/주간 보고서 자동 업데이트, 기한 임박 알림, 특정 기간의 데이터 모니터링 등 날짜 기반의 동적 필터링이 필요한 모든 상황에 활용됩니다. YEAR(), DAY(), WEEKNUM() 등 다양한 날짜 함수를 조합하여 더욱 세밀한 시간 조건을 만들 수 있습니다.
2. 논리 함수를 이용한 복합 조건 필터링
AND, OR 함수를 수식 조건에 활용하여, 여러 열에 걸쳐 복잡한 AND/OR 조합을 구현할 수 있습니다.
예제 목표: '지역'이 '서울'이면서 (AND) '판매액'이 800천 원 이상인 (AND) 주문이거나, '지역'이 '부산'이면서 (AND) '제품 카테고리'가 '의류'인 (AND) 주문만 표시하세요. (복합 AND + OR 조건)
해결 방법:
- 조건 범위 생성: J1:K3 셀에 조건을 입력합니다.
- J1 셀: 비워둡니다.
- K1 셀: 비워둡니다.
- J2 셀: OR(AND(G2="서울", E2>=800), AND(G2="부산", C2="의류"))
- G2: 원본 데이터의 '지역' 첫 셀
- E2: 원본 데이터의 '판매액' 첫 셀
- C2: 원본 데이터의 '제품 카테고리' 첫 셀
- OR 조건이므로 두 개의 AND 조건을 다른 행이 아닌, 하나의 수식 내에서 OR 함수로 묶어줍니다.
- 데이터 범위 내 아무 셀 클릭 (예: A1).
- 데이터 탭 → 고급 클릭.
- 고급 필터 대화 상자에서:
- 목록 범위: A1:G11
- 조건 범위: J1:K3 (조건 수식이 1개이므로 헤더 아래 한 줄만 사용하고, K열은 비워둡니다)
- 확인을 클릭합니다.
결과:
- ('서울'이면서 '판매액'이 800 이상): ORD001, ORD003, ORD006, ORD010
- ('부산'이면서 '제품 카테고리'가 '의류'): ORD002, ORD005, ORD008
- 두 조건 중 하나라도 만족하는 모든 주문이 표시됩니다.
최상급 실무 활용: 이것은 일반적인 자동 필터나 단순한 고급 필터 조건 범위로는 구현하기 매우 어려운 복잡한 필터링입니다. 고객 세분화, 특정 캠페인 대상 추출, 재고 관리의 복합 기준 적용 등 비즈니스 로직이 복잡할 때 매우 강력한 도구입니다.
3. 통계 함수를 이용한 동적 필터링
AVERAGE(), MAX(), MIN() 등 통계 함수의 결과를 기준으로 데이터를 필터링할 수 있습니다.
예제 목표: 전체 주문 중 판매액이 평균을 초과하는 주문만 표시하세요.
해결 방법:
- 조건 범위 생성: L1:L2 셀에 조건을 입력합니다.
- L1 셀: 비워둡니다 (또는 "평균 초과" 등 고유한 텍스트 입력).
- L2 셀: =E2>AVERAGE($E$2:$E$11)
- E2: 원본 데이터의 '판매액' 첫 셀을 참조합니다. (상대 참조)
- AVERAGE($E$2:$E$11): 전체 판매액의 평균을 계산합니다. (절대 참조로 고정)
- 데이터 범위 내 아무 셀 클릭 (예: A1).
- 데이터 탭 → 고급 클릭.
- 고급 필터 대화 상자에서:
- 목록 범위: A1:G11
- 조건 범위: L1:L2
- 확인을 클릭합니다.
결과: 판매액이 평균(약 593천 원)을 초과하는 주문(ORD001, ORD003, ORD006, ORD009, ORD010)만 표시됩니다. 데이터가 추가되어 평균이 변해도 자동으로 필터링 결과가 업데이트됩니다.
최상급 실무 활용: 고성과자 식별, 비효율적인 프로세스 감지, 이상치(Outlier) 분석, 특정 기준을 넘어서는 데이터 모니터링 등에 활용됩니다. 이는 단순히 고정된 값을 기준으로 하는 필터링이 아니라, 데이터 자체의 통계적 특성을 기반으로 하는 동적인 필터링이므로, 보다 깊이 있는 데이터 통찰력을 제공합니다.
4. 다른 셀의 값 또는 함수의 결과에 따른 동적 필터링
수식 조건은 특정 셀에 입력된 값이나 다른 함수의 결과를 직접 참조하여 필터링 조건을 만들 수 있습니다.
예제 목표: 특정 셀(M2)에 입력된 '지역' 값에 해당하는 주문만 표시하세요.
해결 방법:
- 필터링할 지역 입력 셀: M2 셀에 '서울'을 입력합니다.
- 조건 범위 생성: N1:N2 셀에 조건을 입력합니다.
- N1 셀: 비워둡니다 (또는 "선택 지역" 등 고유한 텍스트 입력).
- N2 셀: =G2=$M$2
- G2: 원본 데이터의 '지역' 첫 셀을 참조합니다. (상대 참조)
- $M$2: 필터링할 지역이 입력된 셀을 참조합니다. (절대 참조로 고정)
- 데이터 범위 내 아무 셀 클릭 (예: A1).
- 데이터 탭 → 고급 클릭.
- 고급 필터 대화 상자에서:
- 목록 범위: A1:G11
- 조건 범위: N1:N2
- 확인을 클릭합니다.
결과: M2 셀에 '서울'을 입력하면 서울 지역 주문만, '부산'을 입력하면 부산 지역 주문만 표시됩니다.
최상급 실무 활용: 사용자 정의 보고서, 대시보드의 필터 컨트롤, 특정 기준에 따른 시뮬레이션 등 사용자 인터페이스(UI)를 구축할 때 필수적입니다.
데이터 유효성 검사로 M2 셀에 드롭다운 목록을 만들면, 사용자가 목록에서 선택하는 값에 따라 보고서가 자동으로 필터링되는 완벽하게 자동화된 대시보드를 구현할 수 있습니다.
고급 필터의 가장 강력한 기능인 '수식 기반 조건'을 집중적으로 다루었습니다. 날짜 함수, 논리 함수, 통계 함수 등을 활용하여 복잡하고 동적인 필터링 조건을 설정하는 방법을 실무 예제를 통해 알아보았습니다.
이 기술은 엑셀에서 수동 작업을 최소화하고, 자동으로 업데이트되는 정교한 보고서를 만들며, 심층적인 데이터 통찰력을 얻는 데 필수적인 역량입니다.