매번 색칠하던 이상값 확인, 고급 필터 ‘수식 조건’으로 한 번에 뽑기
매출표를 검토하다 보면 이상하게 시간이 많이 잡아먹히는 일이 있습니다. 예를 들면 마진율이 낮은 건, 할인율이 높은 건, 비고에 ‘확인’이라고 적힌 건만 따로 빼서 팀장에게 전달하는 작업입니다. 처음에는 자동 필터를 켜고 조건을 하나씩 걸면 될 것 같지만, 조건이 여러 개로 늘어나는 순간 꽤 번거로워집니다. 마진율 필터 걸고, 할인율 필터 걸고, 비고 검색하고, 보이는 행만 복사하고, 다시 원본으로 돌아와 필터를 풀고… 이 과정에서 누락이 생기기 쉽습니다.
이럴 때 의외로 잘 안 쓰이지만 실무에서는 꽤 강력한 기능이 고급 필터의 수식 조건입니다. 일반 필터처럼 드롭다운을 만지는 방식이 아니라, 별도의 조건 영역에 수식을 넣어두고 조건에 맞는 행만 한 번에 다른 위치로 복사하는 방식입니다. 특히 “A이거나 B이거나 C인 행”처럼 조건이 섞여 있을 때 손이 확 줄어듭니다.
Before / After로 보면 차이가 확실합니다
| 구분 | 기존 방식 | 개선 후 방식 |
|---|---|---|
| 작업 흐름 | 필터 드롭다운에서 조건을 여러 번 선택 | 조건 수식 1개를 작성하고 고급 필터 실행 |
| 조건 변경 | 필터 해제 후 다시 설정 | 조건 수식만 수정 |
| 복사 작업 | 보이는 셀 선택 여부를 매번 확인 | 조건에 맞는 행을 지정 위치로 바로 복사 |
| 누락 위험 | 필터 조합을 잘못 걸면 빠질 수 있음 | 수식 기준이 명확해 재현성이 좋음 |
| 반복 보고 | 매번 같은 작업을 다시 수행 | 조건 영역을 보관해두고 재사용 가능 |
예제로 이런 매출 검토표가 있다고 가정하겠습니다. 원본 표의 머리글은 4행, 실제 데이터는 5행부터 시작합니다.
| 주문일 | 거래처 | 담당자 | 품목군 | 매출액 | 원가 | 할인율 | 마진율 | 비고 |
|---|---|---|---|---|---|---|---|---|
| 2026-06-03 | 한빛상사 | 김대리 | 소모품 | 1,250,000 | 1,110,000 | 8% | 11.2% | |
| 2026-06-04 | 동일유통 | 박과장 | 장비 | 3,900,000 | 3,720,000 | 28% | 4.6% | 단가 확인 |
| 2026-06-05 | 서진몰 | 이주임 | 소모품 | 820,000 | 710,000 | 12% | 13.4% |
여기서 검토 대상으로 뽑고 싶은 조건은 다음과 같습니다.
- 마진율이 10% 미만인 행
- 할인율이 25%를 초과한 행
- 비고에 ‘확인’이라는 글자가 들어간 행
일반 필터로도 불가능한 작업은 아닙니다. 하지만 이 조건은 서로 다른 열에 흩어져 있고, 하나라도 해당되면 뽑아야 하는 OR 조건입니다. 이럴 때 고급 필터의 수식 조건을 쓰면 기준을 깔끔하게 한 줄로 정리할 수 있습니다.
조건 영역은 작게, 수식은 명확하게
원본 표 오른쪽 빈 공간에 조건 영역을 하나 만듭니다. 예를 들어 K4 셀에는 원본 머리글과 겹치지 않는 이름을 적습니다. 저는 보통 검토대상처럼 의미가 보이는 이름을 적어둡니다. 그리고 K5 셀에 아래 수식을 입력합니다.
=OR($H5<10%,$G5>25%,ISNUMBER(SEARCH("확인",$I5)))| 셀 | 입력 내용 | 설명 |
|---|---|---|
| K4 | 검토대상 | 원본 표 머리글과 겹치지 않는 조건 제목 |
| K5 | =OR($H5<10%,$G5>25%,ISNUMBER(SEARCH("확인",$I5))) | 첫 번째 데이터 행 기준으로 TRUE/FALSE 판단 |
여기서 중요한 포인트는 수식이 첫 번째 데이터 행을 바라봐야 한다는 점입니다. 원본 머리글이 4행이고 데이터가 5행부터 시작한다면, 조건 수식도 H5, G5, I5처럼 5행을 기준으로 작성해야 합니다. 고급 필터는 이 수식을 각 행에 적용해보면서 TRUE가 되는 행만 골라냅니다.
수식에서 열에는 달러 표시를 붙이고 행에는 붙이지 않은 것도 이유가 있습니다. $H5처럼 쓰면 마진율 열은 고정하되, 행은 데이터 행에 맞춰 내려가며 판단됩니다. 초보자라면 이 부분이 조금 낯설 수 있는데, 쉽게 말해 “항상 마진율 열을 보되, 행은 현재 검사 중인 행을 보라”는 뜻입니다.
고급 필터 실행은 짧게 끝납니다
조건 영역을 만들어두었다면 실행은 생각보다 간단합니다. 원본 범위 안 아무 셀이나 클릭한 뒤, 데이터 탭에서 고급을 선택합니다. 대화상자가 열리면 아래처럼 지정합니다.
| 항목 | 지정 예시 | 확인할 점 |
|---|---|---|
| 목록 범위 | $A$4:$I$500 | 머리글 행을 반드시 포함 |
| 조건 범위 | $K$4:$K$5 | 조건 제목과 수식 셀을 함께 선택 |
| 복사 위치 | $M$4 | 결과를 놓을 빈 위치의 시작 셀 |
| 동작 | 다른 장소에 복사 | 원본을 건드리지 않고 결과표 생성 |
이렇게 실행하면 마진율이 낮거나, 할인율이 높거나, 비고에 확인 문구가 있는 행만 M열 이후에 따로 복사됩니다. 원본 필터 상태를 바꾸지 않아도 되고, 검토 결과만 별도로 남길 수 있어 보고용 시트 만들 때 편합니다.
흔한 실수는 거의 정해져 있습니다
고급 필터 수식 조건은 한 번 익히면 강력하지만, 처음 사용할 때는 몇 가지 지점에서 자주 막힙니다. 특히 아래 항목은 결과가 하나도 안 나오거나 엉뚱하게 나올 때 먼저 확인해볼 만합니다.
| 증상 | 가능한 원인 | 해결 방법 |
|---|---|---|
| 결과가 아무것도 안 나옴 | 조건 수식이 머리글 행을 참조함 | 첫 데이터 행을 기준으로 수식 작성 |
| 모든 행이 다 나옴 | 수식이 항상 TRUE가 되도록 작성됨 | 빈 셀 비교, 텍스트 비교 조건을 다시 확인 |
| 고급 필터가 조건을 못 알아봄 | 조건 제목이 원본 머리글과 충돌 | 수식 조건 제목은 원본에 없는 이름 사용 |
| 퍼센트 조건이 이상함 | 10을 10%로 착각해 입력 | 10%는 0.1 또는 10%로 입력 |
| 비고 검색에서 오류 발생 | SEARCH 결과를 그대로 조건에 사용 | ISNUMBER로 감싸 TRUE/FALSE로 변환 |
특히 SEARCH("확인",$I5)만 단독으로 쓰면 해당 문구가 없을 때 오류가 납니다. 고급 필터 조건은 TRUE 또는 FALSE로 판단되는 형태가 안전하므로 ISNUMBER로 감싸주는 습관을 들이면 좋습니다.
조건을 조금 바꾸면 활용 범위가 넓어집니다
수식 조건의 장점은 현업 기준을 그대로 반영하기 쉽다는 점입니다. 예를 들어 담당자가 특정 인원이고, 동시에 마진율이 낮은 건만 보고 싶다면 OR 대신 AND를 사용합니다.
=AND($C5="김대리",$H5<10%)반대로 여러 담당자 중 하나이면서 할인율이 높은 건을 찾고 싶다면 이런 식으로 작성할 수 있습니다.
=AND(OR($C5="김대리",$C5="박과장"),$G5>20%)날짜 조건도 자주 씁니다. 이번 달 주문 중에서 검토 대상만 뽑고 싶다면 주문일 열을 함께 조건에 넣습니다. 날짜는 텍스트처럼 보이더라도 실제 날짜값이어야 제대로 비교됩니다.
=AND($A5>=DATE(2026,6,1),$A5<=DATE(2026,6,30),OR($H5<10%,$G5>25%))실무에서는 월이 바뀔 때마다 수식을 고치기 귀찮으니, 시작일과 종료일을 별도 셀에 빼두는 방식도 좋습니다. 예를 들어 K2에 시작일, K3에 종료일을 넣어두고 조건 수식에서 해당 셀을 참조하면 됩니다.
=AND($A5>=$K$2,$A5<=$K$3,OR($H5<10%,$G5>25%))일반 필터와 같이 쓰면 더 편한 장면
고급 필터가 항상 일반 필터를 대체하는 것은 아닙니다. 빠르게 특정 거래처 하나만 보고 싶을 때는 일반 필터가 더 빠릅니다. 하지만 검토 기준이 여러 열에 걸쳐 있고, 같은 기준을 반복해서 써야 한다면 고급 필터 쪽이 훨씬 안정적입니다.
| 상황 | 추천 방식 | 이유 |
|---|---|---|
| 거래처 1개만 잠깐 확인 | 일반 필터 | 클릭 몇 번으로 바로 확인 가능 |
| 마진, 할인, 비고 조건을 동시에 확인 | 고급 필터 수식 조건 | 복합 조건을 수식으로 관리 가능 |
| 검토 결과를 별도 표로 남김 | 고급 필터 | 다른 위치로 복사 기능이 편리 |
| 매월 같은 기준으로 보고서 작성 | 고급 필터 + 조건 셀 | 기간만 바꾸고 재사용 가능 |
자료가 매번 새로 쌓이는 구조라면 원본 범위를 표로 만들어두는 것도 도움이 됩니다. 다만 고급 필터 조건 수식 자체는 구조적 참조보다 일반 셀 참조로 작성하는 편이 오류를 줄이기 쉽습니다. 원본 데이터가 늘어날 가능성이 크다면 목록 범위를 넉넉하게 잡거나, 표 범위를 기준으로 다시 지정하는 습관을 두면 됩니다.
검토 전 마지막 체크
고급 필터 결과를 보고 바로 전달하기 전에 저는 보통 세 가지만 봅니다. 첫째, 조건 수식 셀에서 TRUE/FALSE가 정상적으로 나오는지 확인합니다. 둘째, 원본 목록 범위에 머리글이 포함되어 있는지 확인합니다. 셋째, 결과표 건수가 예상과 너무 다르지 않은지 대략 검산합니다.
이 기능은 화려해 보이는 기능은 아니지만, 손으로 색칠하고 복사하던 검토 업무를 꽤 많이 줄여줍니다. 특히 “이번 달 위험 건만 따로 주세요”, “할인율 높은 건만 다시 확인해 주세요”처럼 조건이 자주 바뀌는 보고에는 수식 조건을 만들어두는 쪽이 훨씬 편합니다. 필터를 여러 번 만지던 작업이 조건 수식 하나로 정리되면, 검토 기준도 더 선명하게 남습니다.