Home > 엑셀 함수 > 찾기 및 참조영역 함수 > 엑셀함수 FILTER – 원하는 조건으로 데이터 조회(필터링)하기

엑셀함수 FILTER – 원하는 조건으로 데이터 조회(필터링)하기

  • by

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

엑셀 필터

엑셀 필터

 

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

 

구문(Syntax)

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

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

인수 :

  • 배열 : 조회(필터링)할 배열 또는 범위
  • 포함 : 조회(필터링)의 조건
  • if_empty : 찾는 값이 없을 때 표시할 값

 

사용예 :

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

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

엑셀 FILTER 함수

 

FILTER함수는 조회 결과를 원하는 범위에 표시해 주므로 아래와 같이 적당한 위치에 수식을 입력한다.

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

엑셀 FILTER 함수

 

수식이 정상적으로 입력되었으면 다음과 같이 조회 결과가 표시된다.

엑셀 FILTER 함수

 

※ 주의 : 조회 결과를 범위로 가져오도록 지정했는데 가져올 범위에 이미 값이 있으면 다음과 같이 #SPILL! 오류가 발생하므로 결과를 가져올 범위가 비어 있어야 한다.

엑셀 FILTER 함수

 

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

엑셀 FILTER 함수

 

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

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

엑셀 FILTER 함수

FILTER함수는 조회 결과를 원하는 범위에 표시해 주므로 아래와 같이 적당한 위치에 수식을 입력한다.

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

엑셀 FILTER 함수

 

수식이 정상적으로 입력되었으면 다음과 같이 조회 결과가 표시된다.

엑셀 FILTER 함수

 

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

엑셀 FILTER 함수

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

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

엑셀 FILTER 함수

 

B38셀에 다음 수식을 입력한다.

=FILTER($B$6:$E$13,IF($C$33=””,($B$6:$B$13=$B$6:$B$13),($B$6:$B$13=$C$33))*IF($C$34=””,($D$6:$D$13=$D$6:$D$13),($D$6:$D$13=$C$34))*IF(C35=””,($E$6:$E$13=$E$6:$E$13),($E$6:$E$13>=$C$35)),”찾는 자료가 없음”)

 

수식이 정상적으로 입력되었으면 판매일자가 2020년 7월 6일이고 상품이 “클립”인 자료가 다음과 같이 조회된다.

엑셀 FILTER 함수

 

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

엑셀 FILTER 함수

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

IF($C$33=””,($B$6:$B$13=$B$6:$B$13),($B$6:$B$13=$C$33))

IF함수를 이용하여

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

방식이다.

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

※ 주의 :

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

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

=FILTER($B$6:$E$13,($B$6:$B$13=$C$33)*($D$6:$D$13=$C$34)*($E$6:$E$13>=$C$35),”찾는 자료가 없음”)

 

조건값을 입력하는 방식을 사용하면 다음과 같이 조회시스템처럼 수식을 바꾸지 않고 원하는 자료를 조회할 수 있다.

판매일자가 2020년 7월 6일이고 판매금액이 20,000원보다 크거나 같은 것이 조회되었다.

엑셀 FILTER 함수

 

조회조건을 하나도 입력하지 않으면 모든 자료가 조회된다.

엑셀 FILTER 함수

 

 

[ 엑셀 찾기 및 참조영역 함수 목록 ]

댓글 남기기

이메일은 공개되지 않습니다.