엑셀에서 대량의 데이터 중 내가 원하는 조건에 맞는 행만 골라내어 새로운 표로 구성해야 할 때가 많습니다.
이럴 때 가장 강력하고 유용하게 쓰이는 기능이 바로 FILTER 함수입니다.
과거에는 복잡한 수식이나 고급 필터를 번거롭게 거쳐야 했지만, 최신 엑셀 함수를 활용하면 조건에 일치하는 행 데이터를 순식간에 추출할 수 있습니다.
이번 글에서는 단일 조건부터 특정 문자열 포함 조건, 그리고 두 가지 이상의 다중 조건까지 상황별 데이터 추출 방법을 차근차근 살펴보겠습니다.
1. 단일 조건에 해당되는 행 가져오기
가장 기본이 되는 형태는 하나의 셀 값을 기준으로 일치하는 행 전체를 가져오는 방식입니다.
엑셀의 FILTER 함수 구조를 이해하면 누구나 쉽게 응용할 수 있습니다.
FILTER 함수 기본 구조와 사용법
FILTER 함수는 기본적으로 =FILTER(범위, 조건, [if_empty]) 형태의 구조를 가집니다.
여기서 첫 번째 인수는 가져올 전체 데이터 범위이고, 두 번째 인수는 참과 거짓을 판별할 조건 범위입니다.
예를 들어 제품명(C3:C11) 범위에서 검색조건 셀(B13)에 입력된 ‘햄’과 일치하는 행을 찾고 싶다면 =FILTER(A3:E11, C3:C11=B13, "없음") 형태로 수식을 작성합니다.
지정한 조건에 맞는 데이터가 존재하면 해당 행들이 자동으로 뿌려지고, 일치하는 값이 없다면 지정한 빈값 처리 문구가 출력됩니다.
단일 조건 추출 시 주의할 점
조건을 지정할 때 비교할 열의 크기와 전체 데이터의 행 개수가 정확하게 일치해야 에러가 발생하지 않습니다.
예를 들어 데이터 범위가 3행부터 11행까지라면 조건 범위 역시 반드시 3행부터 11행까지로 맞춰주어야 합니다.
또한 검색 조건이 입력되는 셀 주소가 올바르게 지정되었는지 수식 입력줄을 통해 다시 한번 확인하는 습관이 중요합니다.
2. 특정 단어가 포함된 행 가져오기
정확하게 일치하는 값뿐만 아니라, 특정 단어나 문구가 포함된 텍스트를 기준으로 행을 추출해야 할 때가 있습니다.
이때는 FILTER 함수 단독 사용이 아니라 ISNUMBER와 FIND 함수를 함께 조합해야 합니다.
FIND와 ISNUMBER 함수 조합하기
특정 단어가 포함된 위치를 찾는 FIND 함수는 검색어가 없으면 에러를 반환하는 특성이 있습니다.
이 에러 값을 숫자로 변환해 주는 ISNUMBER 함수를 감싸주어야 FILTER 함수의 조건으로 원활하게 사용할 수 있습니다.
예를 들어 지점(A3:A11) 열에서 ‘본점’이라는 단어가 포함된 모든 행을 찾고 싶다면 =FILTER(A3:E11, ISNUMBER(FIND(B13, A3:A11)), "없음") 형태의 수식을 입력합니다.
이렇게 하면 ‘서울본점’, ‘부산본점’처럼 특정 단어가 텍스트 중간이나 끝에 포함되어 있더라도 정확하게 찾아낼 수 있습니다.
텍스트 검색 시 대소문자 및 공백 주의사항
FIND 함수는 대소문자를 구분하며, 찾으려는 텍스트에 의도치 않은 공백이 포함되어 있으면 일치 결과를 찾지 못할 수 있습니다.
검색 조건 셀에 단어를 입력할 때 앞뒤로 공백이 들어가지 않았는지 꼼꼼하게 체크하는 것이 좋습니다.
텍스트 기반의 조건 추출은 고객 명단이나 주소록, 제품명 목록을 정리할 때 실무에서 매우 자주 쓰이는 유용한 팁입니다.
3. 조건 2가지 이상에 해당되는 행 가져오기
실무에서는 한 가지 조건만 만족하는 데이터뿐만 아니라, 두 가지 이상의 조건을 동시에 만족하는 행을 추출해야 하는 상황이 빈번합니다.
엑셀 수식 안에서 여러 조건을 연결할 때는 곱셈 연산자(*)를 활용해 ‘AND’ 조건을 구현할 수 있습니다.
다중 조건(AND) 수식 작성 방법
두 가지 조건을 동시에 만족하는 행을 가져오려면 각 조건을 괄호로 묶은 뒤 별표(*) 기호로 연결해 줍니다.
예를 들어 특정 지점 이면서 동시에 특정 제품명을 만족하는 데이터를 추출하려면 =FILTER(A3:E11, (C3:C11=C13) * ISNUMBER(FIND(B13, A3:A11)), "없음") 형식으로 수식을 완성합니다.
여기서 괄호로 감싸진 각 조건식은 참(TRUE)인 경우 1, 거짓(FALSE)인 경우 0으로 계산되며, 두 조건이 모두 참일 때만 최종적으로 1이 되어 데이터가 추출됩니다.
다중 조건 활용 시 괄호 누락 방지 팁
여러 조건을 묶을 때 가장 흔하게 발생하는 실수는 개별 조건에 괄호를 누락하는 것입니다.
엑셀은 연산자 우선순위에 따라 수식을 해석하기 때문에, 각 조건은 반드시 독립적으로 괄호 ( ) 안에 넣어주어야 오류를 막을 수 있습니다.
조건의 개수가 3개 이상으로 늘어나더라도 동일하게 괄호와 곱셈 기호로 계속 확장해서 연결할 수 있으므로 복잡한 데이터 분석 시 유용하게 써먹을 수 있습니다.
자주 묻는 질문
Q1. FILTER 함수를 사용할 때 #CALC! 오류가 뜨는 이유는 무엇인가요?
A1. 지정한 조건에 맞는 데이터가 단 하나도 존재하지 않을 경우 #CALC! 오류가 발생할 수 있습니다. 함수 마지막에 빈값 처리 인수를 추가하여 오류 대신 원하는 문구가 출력되도록 설정하면 해결됩니다.
Q2. 대소문자를 구분하지 않고 텍스트를 검색하고 싶다면 어떻게 해야 하나요?
A2. FIND 함수 대신 SEARCH 함수를 사용하면 대소문자를 구분하지 않고 원하는 단어가 포함된 행을 정확하게 추출할 수 있습니다.
Q3. 추출된 결과가 자동으로 아래로 확장되는데 중간에 값을 수정할 수 있나요?
A3. FILTER 함수로 출력된 결과 영역은 스필(Spill) 기능이 적용되므로 데이터가 출력된 첫 번째 셀의 수식만 수정할 수 있습니다. 결과 영역의 중간 셀을 직접 수정하려고 하면 에러가 발생하므로 원본 범위나 수식 자체를 수정해야 합니다.
추천글