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

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

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

 

구문(Syntax)

 

사용 예

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

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

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

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

 

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

 

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

 

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

 

 

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

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

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

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

 

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

 

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

=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),"찾는 자료가 없음")

 

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

 

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

=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) 조건값을 표에 입력해서 조회하기

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

다운로드한 실습 엑셀파일의 [함수응용-조건설정] 시트를 선택하고 [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일이고 상품이 '클립'인 자료가 조회됩니다.

 

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

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

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

IF함수를 이용하여

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

방식입니다.

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