[2025 최신] 엑셀 필터 혁명의 시작! FILTER 함수 역대급 실무 활용법

FILTER 함수 기초 사용법

실무에서 엑셀로 보고서를 작성할 때, 반복되는 '필터' 작업은 업무 시간의 상당 부분을 차지합니다. 물론 엑셀의 '고급 필터'나 VBA로 일부 자동화할 수 있지만, 초보자에게는 진입장벽이 높아 실무에 바로 적용하기 어려운데요.

이번에 살펴볼 FILTER 함수를 활용하면, 엑셀 2021 이후 버전에서 누구나 손쉽게 필터링 자동화 보고서를 만들 수 있습니다. 이번 강의에서는 3가지 상황별 예제를 통해, 조건만 입력하면 실시간으로 데이터가 필터링되는 자동화 보고서를 만드는 방법을 살펴보겠습니다.

1. 먼저 FILTER 함수의 기초 사용법을 살펴보겠습니다. FILTER 함수는 범위에서 조건을 만족하는 데이터를 필터링하는 함수로, 다음과 같이 사용합니다.
   = FILTER ( 범위, 조건, [결과없음출력값] )
   ' 범위에서 조건을 만족하는 값을 필터링합니다.

   ---
2. 예제파일의 [FILTER] 시트로 이동한 후, 왼쪽 데이터에서 제조사가 '삼성전자'인 값만 필터링해보겠습니다. 결과를 출력할 시작셀인 F9셀을 선택한 후, 다음과 같이 FILTER 함수를 작성합니다.

   =FILTER(B9:D18,B9:B18=G7)

   

3. 함수를 실행하면 제조사가 삼성전자인 값만 필터링됩니다.
4. 이제 G7셀의 값을 변경하면 특정 제조사의 데이터를 실시간으로 필터링할 수 있습니다.

유형1: 다중 조건 필터링 (배열 연산)

실무에서는 단일 조건보다 여러 조건을 조합해 필터를 적용해야 할 때가 많습니다. 예를 들어 "거래처가 A사이면서 품목이 B인 데이터"처럼 여러 조건을 동시에 비교해야 하는 상황이 자주 발생하는데요. 이때 배열 연산을 활용하면 실무에서 마주치는 거의 모든 조건을 깔끔하게 처리할 수 있습니다. 아래 두 가지 규칙은 '꼭' 기억해두세요!

1. 다중 조건 필터링의 핵심 이론인 '배열 연산'부터 살펴보겠습니다. [다중조건FILTER] 시트에서 E열과 F열의 조건1, 조건2를 모두 만족하는 경우를 비교해보겠습니다. E10셀과 F10셀에 다음과 같이 수식을 입력해 제조사와 구분 범위의 값이 "삼성전자"와 "DRAM"인 경우를 비교합니다.
   · E10셀 : =B10:B19=E8
   · F10셀 : =C10:C19=F8

   

2. 계산할 결과는 "제조사 = 삼성전자"와 "구분 = DRAM"을 모두 만족하는 경우(AND)이므로 두 조건을 곱셈으로 계산합니다. G10셀에 다음과 같이 수식을 작성하면 두 조건이 모두 TRUE인 경우 1로 계산됩니다.

   =E10#*F10#

   

   오빠두Tip : 분산 배열(#)을 활용한 배열 계산의 기초 이론은 진짜쓰는 실무엑셀 입문 강의를 참고하세요!👇
   
3. 배열 연산 원리를 FILTER 함수 조건에 적용하면 여러 조건을 동시에 만족하는 값을 필터링할 수 있습니다. 오른쪽 '실전 활용' 예제에서 O10셀에 다음과 같이 수식을 작성합니다.

   =FILTER(K10:M20,(K10:K20=O8)*(L10:L20=P8))

   

4. 수식을 실행하면 아래 그림과 같이 제조사와 구분 조건을 동시에 만족하는 데이터가 필터링됩니다.

유형2: 여러 항목 필터링 (MATCH 활용)

이번에는 특정 필드에서 여러 항목을 동시에 필터링하는 경우를 살펴보겠습니다. 예를 들어 제조사가 "삼성전자, SK하이닉스, 샌디스크"인 데이터를 한 번에 확인해야 할 경우, 여러 항목 필터링 공식을 활용합니다.

1. 여러 항목 필터링의 조건에는 MATCH 함수가 사용됩니다. MATCH 함수는 본래 '범위에서 특정 값이 위치하는 순번'을 찾을 때 사용합니다. 예를 들어 [MATCH] 시트에서 "황호성"이라는 값이 이름 범위인 C8:C16에서 몇 번째에 위치하는지 검색할 때 사용합니다. G8셀에 다음과 같이 MATCH 함수를 작성하면, 황호성의 순번으로 6이 반환됩니다.
   오빠두Tip : MATCH 함수는 INDEX/MATCH 검색 공식에도 중요하게 사용됩니다. INDEX/MATCH 공식 사용법은 아래 입문 강의를 참고하세요!👇
   
2. 이 MATCH 함수의 '값'과 '범위'를 치환해 역으로 사용하면, 여러 항목의 포함 여부를 한 번에 확인할 수 있습니다. [OR검색MATCH] 시트의 검색결과 셀인 F8셀에 다음과 같이 MATCH 함수를 입력하면 "제조사1" 범위의 값이 제조사2 목록에 있을 경우 숫자를 출력하고, 그렇지 않을 경우 오류를 반환합니다.

   =MATCH(B8:B16,D8:D13,0)

   

3. 위 MATCH 함수의 결과가 숫자라면 목록에 존재하는 것이므로, ISNUMBER 함수로 묶어 값이 숫자인 경우 TRUE를 반환하도록 공식을 완성합니다.

   =ISNUMBER(MATCH(B8:B16,D8:D13,0))

   

4. 여러 항목의 포함 여부를 확인하는 "ISNUMBER+MATCH" 조건을 FILTER 함수에 적용하면 여러 항목 필터링 공식이 완성됩니다. [여러항목FILTER] 시트의 결과 시작셀인 H9셀에 다음과 같이 FILTER 함수를 작성하면 '제조사' 목록에 포함된 모든 값이 한 번에 필터링됩니다.

   =FILTER(B9:D18,ISNUMBER(MATCH(B9:B18,F9:F13,0)))

   

유형3: 단어 포함 필터링 (ISNUMBER+SEARCH)

지금까지 '다중 조건'과 '여러 항목' 필터링 공식을 살펴봤습니다. 이제 마지막으로, 범위에서 특정 단어를 포함하는 모든 데이터를 깔끔하게 필터링하는 '단어 포함' 필터링 공식을 살펴보겠습니다. 단어 포함 공식은 쉽게 외우는 방법이 있는데요. 단어가 있는지 없는지 검색하는 "있어?(ISNUMBER+SEARCH)" 공식으로 기억해보세요!👍

1. 먼저 특정 단어의 포함 여부를 확인하는 ISNUMBER+SEARCH 공식의 기초 사용법부터 살펴보겠습니다. 예제파일 마지막 시트인 [단어포함검색공식]에서 포함여부 시작셀인 C8셀에 다음과 같이 ISNUMBER+SEARCH 공식을 작성합니다.

   =ISNUMBER(SEARCH(H7,B8:B11))

   

2. 공식을 입력하면 아래 그림과 같이 H7셀에 작성된 단어가 포함된 경우 TRUE를 반환하는 조건이 출력됩니다.
3. 이제 단어 포함 공식을 FILTER 함수에 적용해보겠습니다. 아래 표에서 필터링 결과를 출력할 시작셀인 G15셀에 다음과 같이 FILTER 함수를 작성합니다.

   =FILTER(B15:D27,ISNUMBER(SEARCH(I13,D15:D27)))

   

4. 수식을 입력하면 아래 그림과 같이 '제품명' 범위에서 검색 단어가 포함된 값이 필터링됩니다.
5. 한 단계 더 나아가, 모든 값을 합친 더미 데이터 필드를 검색 조건 범위로 사용하면 모든 데이터에서 단어 포함 여부를 검색하는 실시간 필터링 보고서를 만들 수 있습니다. 시트 위쪽 [+] 버튼을 클릭하면 아래 그림과 같이 E열에 더미데이터 필드가 표시됩니다.
6. 더미 데이터 필드의 시작셀에 다음과 같이 CONCAT 함수를 작성한 후 자동채우기를 적용하면, 행마다 '제조사, 구분, 제품명'이 한 문장으로 합쳐진 더미데이터 필드가 완성됩니다.
7. G15셀에 작성했던 FILTER 함수에서 단어 포함 여부의 검색 범위를 더미 데이터가 작성된 E15:E27로 바꾼 후 입력하면, 모든 범위를 대상으로 단어 포함 여부를 확인하는 실시간 필터링 보고서가 완성됩니다.
   오빠두Tip : 엑셀 2019 이전 버전에서도 사용 가능한 실시간 필터링 보고서 제작 방법은 이전 영상 강의를 참고하세요!👇
   
8. 이 외에도 엑셀 2024 이후 버전에 새롭게 추가된 BYROW 함수와 TOROW 함수를 활용하면 다중 조건 + 여러 항목 + 단어 포함을 동시에 적용할 수 있습니다. 최신 버전에서 실시간 필터링 공식을 작성하는 과정은 토요 라이브 전체 영상을 참고하세요.