엑셀에서 범위의 데이터를 원하는 조건으로 조회(필터링)하려면 다음과 같이 리본메뉴 > 데이터 > '필터' 기능을 이용하여 원본데이터 자체를 필터링했지만...
엑셀 2021 이상 버전부터 제공되는 FILTER 함수를 이용하면 원본 데이터를 손대지 않고 조건에 따라 별도의 범위에 조회 결과를 표시할 수 있습니다.
* 엑셀 2021 이상 버전에서는 데이터 메뉴에서 제공하던 기능을 다음과 같이 함수로 제공합니다.
구문(Syntax)
=FILTER(배열, 포함, [if_empty])
배열 또는 범위에서 원하는 조건으로 데이터를 조회(필터링)한다.
인수 :
- 배열 : 조회(필터링)할 배열 또는 범위
- 포함 : 조회(필터링)의 조건
- [if_empty] : 찾는 값이 없을 때 표시할 값
사용 예
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조건으로 연결하여 수식을 완성하였습니다.
※ 주의 :
만약 IF를 함수를 이용하지 않고 다음과 수식을 입력했을 때
=FILTER($B$6:$E$13,($B$6:$B$13=$C$17)*($D$6:$D$13=$C$18)*($E$6:$E$13>=$C$19),”찾는 자료가 없음”)
- 조회조건에 값을 전부 입력하면 조회가 되지만
- 비어 있는 필드가 있으면 조회되지 않는다.
관련 글
감사합니다, filter는 엑셀 2019에는 없나요? 저는 그게 안 나오더라구요..
FILTER함수는 다음 제품에서만 작동합니다.
Microsoft 365용 Excel, Microsoft 365용 Excel(Mac용), 웹용 Excel, iPad용 Excel, iPhone용 Excel, Android 태블릿용 Excel, Android 휴대폰용 Excel
유용한 정보 감사 드립니다.
Filter를 날짜로 지정해서 해당 일자만 필터링 되게 하고 싶은데,
저렇게 지정 후 한셀에 날짜 입력하여, = 사용 동일 일자를 필터링 해라 라고 지정했더니,
되지가 않아서요. 특수 일자만 지정될 수 있도록 바꿀 수 있을까요?
날짜로도 필터링 됩니다.
날짜가 F17셀에 입력되어 있으면 다음과 같이 수식을 입력하면 됩니다.
=FILTER(B6:E13,B6:B13=F17)
*F17셀에 입력된 날짜가 날짜 형식이어야 합니다.