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

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

 

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

 

 

사용예 :

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

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

 

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

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

 

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

 

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

 

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

 

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

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

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

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

 

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

 

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

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

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

 

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일이고 상품이 "클립"인 자료가 다음과 같이 조회된다.

 

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

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

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

IF함수를 이용하여

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

방식이다.

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

 

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

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

 

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

 

 

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