엑셀 함수 > 찾기 및 참조영역 함수 > FILTER 함수 - 원하는 조건으로 필터링하기

FILTER 함수 - 원하는 조건으로 필터링하기

엑셀에서 범위의 데이터를 원하는 조건으로 조회(필터링)하려면 다음과 같이 리본메뉴 > 데이터 > '필터' 기능을 이용하여 원본데이터 자체를 필터링했지만...

엑셀 필터 메뉴

엑셀 2021 이상 버전부터 제공되는 FILTER 함수를 이용하면 원본 데이터를 손대지 않고 조건에 따라 별도의 범위에 조회 결과를 표시할 수 있습니다.

 

구문(Syntax)

FILTER

=FILTER(배열, 포함, [if_empty]) 

배열 또는 범위에서 원하는 조건으로 데이터를 조회(필터링)한다.

인수 :

  • 배열 : 조회(필터링)할 배열 또는 범위
  • 포함 : 조회(필터링)의 조건
  • [if_empty] : 찾는 값이 없을 때 표시할 값
엑셀 2021 이상 버전에서 사용 가능
실습용 엑셀파일 다운로드 : FILTER-함수사용법.xlsx

 

사용 예

1) 범위에서 특정 조건에 해당하는 자료를 조회하기

다음과 같이 "거래처별 판매실적" 자료가 있다고  할 때 FILTER 함수를 이용하여 상품이 '노트'인 것만 조회해 보겠습니다.

[B18] 셀에 다음 수식을 입력합니다.

=FILTER($B$6:$E$13,($D$6:$D$13="노트"),"찾는 자료가 없음")

FILTER 함수로 범위에서 특정 조건에 해당하는 자료를 조회하기

 

수식이 입력되면 다음과 같이 거래처별 판매실적 자료에서 상품이 '노트'인 것만 가져옵니다.

FILTER 함수로 범위에서 특정 조건에 해당하는 자료를 조회하기

 

수식을 풀이해 보면 다음과 같습니다.

FILTER 함수로 범위에서 특정 조건에 해당하는 자료를 조회하기 수식 풀이

 

※ FILTER 함수는 여러 셀에 결과를 가져올 수 있는 동적 배열 함수입니다. 따라서 정렬된 결과를 범위로 가져오도록 지정했는데 가져올 범위에 이미 값이 있으면 다음과 같이 #SPILL! 오류가 발생하므로 결과를 가져올 범위가 비어 있어야 합니다.

FILTER 함수 SPILL 오류

 

 

2) 여러 조건에 해당하는 자료를 조회하기(AND 조건)

이번에는 상품이 '노트'이고 판매금액이 30,000원보다 큰 자료를 조회해 보겠습니다.

[B27] 셀에 다음 수식을 입력합니다.

=FILTER($B$6:$E$13,($D$6:$D$13="노트")*($E$6:$E$13>30000),"찾는 자료가 없음")

FILTER 함수로 여러 조건에 해당하는 자료를 조회하기(AND 조건)

 

수식이 입력되면 다음과 같이 거래처별 판매실적 자료에서 상품이 '노트'이고 판매금액이 30,000원보다  큰 것만 가져옵니다.

FILTER 함수로 여러 조건에 해당하는 자료를 조회하기(AND 조건)

 

수식을 풀이해 보면 다음과 같습니다.

FILTER 함수로 여러 조건에 해당하는 자료를 조회하기(AND 조건) 수식 풀이

=FILTER($B$6:$E$13,($D$6:$D$13="노트")*($E$6:$E$13>30000),"찾는 자료가 없음")

위 수식에서 두번째 인수인 ($D$6:$D$13="노트")*($E$6:$E$13>30000)를 자세히 보면

찾는 조건 범위가 2개인데 중간에 곱셈표시(*)가 붙어 있습니다.
'*' 은 곱셈연산자라고 하는데 AND 조건을 적용합니다.

[$D$6:$D$13] 범위에서 상품이 '노트'이고  [$E$6:$E$13] 범위에서 판매금액이 30,000보다 큰 것을 찾는다는 뜻입니다.

 

 

3) 여러 조건에 해당하는 자료를 조회하기(OR 조건)

이번에는 상품이 '노트' 또는  판매금액이 30,000원보다 큰 자료를 조회해 보겠습니다.

앞에서 예를 든 수식과 다른 점은 '*' 대신에 '+'를 사용하는 점만 다릅니다.

[B35] 셀에 다음 수식을 입력합니다.

=FILTER($B$6:$E$13,($D$6:$D$13="노트")+($E$6:$E$13>30000),"찾는 자료가 없음")

FILTER 함수로 여러 조건에 해당하는 자료를 조회하기(OR 조건)

 

수식이 입력되면 다음과 같이 거래처별 판매실적 자료에서 상품이 '노트' 또는 판매금액이 30,000원보다  큰 것만 가져옵니다.

FILTER 함수로 여러 조건에 해당하는 자료를 조회하기(OR 조건)

 

수식을 풀이해 보면 다음과 같습니다.

FILTER 함수로 여러 조건에 해당하는 자료를 조회하기(OR 조건) 수식 풀이

=FILTER($B$6:$E$13,($D$6:$D$13="노트")+($E$6:$E$13>30000),"찾는 자료가 없음")

위 수식에서 두번째 인수인 ($D$6:$D$13="노트")+($E$6:$E$13>30000)를 자세히 보면

찾는 조건 범위가 2개인데 중간에 덧셈표시(+)가 붙어 있습니다.
'+' 은 덧셈연산자라고 하는데 OR 조건을 적용합니다.

[$D$6:$D$13] 범위에서 상품이 '노트' 또는  [$E$6:$E$13] 범위에서 판매금액이 30,000보다 큰 것을 찾는다는 뜻입니다.

 

 

4) 조건값을 표에 입력해서 조회하기

이번에는 다음과 같이 조건값을 표에 입력한 후 수식에서 조건값을 참조하여  결과를 조회해 보겠습니다.

FILTER 함수로 조건 조건을 표에 입력해서 조회하기

다운로드한 실습 엑셀파일의 [함수응용-조건설정] 시트를 선택하고 [B22] 셀에 다음 수식을 입력합니다.

=FILTER($B$6:$E$13,IF($C$17="",($B$6:$B$13=$B$6:$B$13),($B$6:$B$13=$C$17))*IF($C$18="",($D$6:$D$13=$D$6:$D$13),($D$6:$D$13=$C$18))*IF(C19="",($E$6:$E$13=$E$6:$E$13),($E$6:$E$13>=$C$19)),"찾는 자료가 없음")

 

판매일자가 2022년 7월 6일이고 상품이 '클립'인 자료가 조회됩니다.

FILTER 함수로 조건 조건을 표에 입력해서 조회하기 - 결과

 

입력된 수식을 풀어보면 다음과 같습니다.

FILTER 함수로 조건 조건을 표에 입력해서 조회하기 - 수식 풀이

각각의 조회조건에 해당하는 값을 조회하게 되는데

IF($C$17="",($B$6:$B$13=$B$6:$B$13),($B$6:$B$13=$C$17))

IF함수를 이용하여

  • 조회조건에 값이 비어 있다면 모든 값을 가져오고,
  • 비어 있지 않으면 입력된 조회조건에 해당하는 값을 가져오는

방식입니다.

조회조건 필드가 3개이므로 위의 형태를 3개를 만들어서 곱셈연산자(*)를 이용하여 AND조건으로 연결하여 수식을 완성하였습니다.

※ 주의 :

만약 IF를 함수를 이용하지 않고 다음과 수식을 입력했을 때

=FILTER($B$6:$E$13,($B$6:$B$13=$C$17)*($D$6:$D$13=$C$18)*($E$6:$E$13>=$C$19),”찾는 자료가 없음”)

  • 조회조건에 값을 전부 입력하면 조회가 되지만
  • 비어 있는 필드가 있으면 조회되지 않는다.

 

 


관련 글

UNIQUE 함수 사용법 UNIQUE 함수 - 중복 제거하기 - 엑셀의 UNIQUE함수는 유일한 결과를 구해주는 함수인데 정확히는 두 가지 기능을 가지고  있습니다. 중복을 제거한 결과를 구해주는 기능과 목록에서 한 번만…

UNIQUE 함수 - 중복 제거하기 더 보기 »

엑셀 동적 배열 수식 엑셀의 새로운 기능, 동적 배열 수식 - Microsoft 365, Excel 2021 버전부터 기존의 배열 수식(레거시 배열수식이라고 함)과는 다른 동적 배열 수식을 사용할 수 있습니다.  동적 배열 수식은…

엑셀의 새로운 기능, 동적 배열 수식 더 보기 »

SORTBY 함수 사용법 SORTBY 함수 - 범위의 값을 기준으로 데이터 정렬하기 - 엑셀에서 범위의 데이터를 정렬하려면 다음과 같이 리본 메뉴에 있는 '정렬' 기능을 이용하여 원본데이터 자체를 정렬했지만...   엑셀 2021 이상 버전부터 제공되는 SORT…

SORTBY 함수 - 범위의 값을 기준으로 데이터 정렬하기 더 보기 »

SORT 함수 사용법 SORT 함수 - 데이터 정렬하기 - 엑셀에서 범위의 데이터를 정렬하려면 다음과 같이 리본 메뉴에 있는 '정렬' 기능을 이용하여 원본데이터 자체를 정렬했지만... 엑셀 2021 이상 버전부터 제공되는…

SORT 함수 - 데이터 정렬하기 더 보기 »

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

 자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

“FILTER 함수 - 원하는 조건으로 필터링하기”의 4개의 댓글

  1. 감사합니다, filter는 엑셀 2019에는 없나요? 저는 그게 안 나오더라구요..

    1. FILTER함수는 다음 제품에서만 작동합니다.
      Microsoft 365용 Excel, Microsoft 365용 Excel(Mac용), 웹용 Excel, iPad용 Excel, iPhone용 Excel, Android 태블릿용 Excel, Android 휴대폰용 Excel

  2. 유용한 정보 감사 드립니다.
    Filter를 날짜로 지정해서 해당 일자만 필터링 되게 하고 싶은데,
    저렇게 지정 후 한셀에 날짜 입력하여, = 사용 동일 일자를 필터링 해라 라고 지정했더니,
    되지가 않아서요. 특수 일자만 지정될 수 있도록 바꿀 수 있을까요?

    1. 날짜로도 필터링 됩니다.
      날짜가 F17셀에 입력되어 있으면 다음과 같이 수식을 입력하면 됩니다.
      =FILTER(B6:E13,B6:B13=F17)
      *F17셀에 입력된 날짜가 날짜 형식이어야 합니다.

댓글 남기기

Scroll to Top