엑셀 FILTER 함수 사용법

함수 요약

엑셀 FILTER 함수는 범위에서 조건을 만족하는 데이터를 필터링하는 함수입니다.

함수 구문

=FILTER(범위,조건,[결과없음출력값])

•

범위

필터를 적용할 범위 또는 배열입니다.

•

조건

필터링을 적용할 조건입니다.

조건의 배열 크기는 필터리할 범위와 동일해야 합니다.
크기가 다를 경우 FILTER 함수는 #VALUE 오류를 반환합니다.

•

결과없음출력값

[선택인수]FILTER 함수의 결과가 비어있을 경우 출력할 값입니다.

이 인수가 생략되면 기본값으로 #CALC! 오류를 출력합니다.

상세 설명

엑셀 FILTER 함수는 범위에서 조건을 만족하는 데이터의 필터링 결과를 출력하는 함수입니다.

=FILTER(범위,조건)/ / 범위에서 조건을 만족하는 데이터를 필터링합니다.

FILTER 함수를 사용하면, 기존의 VLOOKUP 함수와 자동 필터 기능만으로는 구현이 어려웠던 실시간 필터링 보고서를 편리하게 만들 수 있습니다.

=FILTER(범위,제조사="삼성")/ / 제조사가 "삼성"인 데이터를 필터링합니다.
=FILTER(범위,ISNUMBER("i7",모델명)))/ / 모델명에 "i7"을 포함하는 데이터를 필터링합니다.

엑셀-실시간-필터링-보고서 — FILTER 함수로 실시간 필터링 보고서를 편리하게 만들 수 있습니다.

오빠두Tip : FILTER 함수와 단어 포함 검색 공식으로 실시간 필터링 보고서를 만드는 방법은 아래 영상 강의에서단계별로 꼼꼼히 정리했으니 참고하세요!👇

엑셀 2021 이후 버전에 새롭게 추가된 분산 범위를 활용하면, FILTER 함수의 결과를 특정 필드 기준으로 정렬하거나 상위 n개 데이터만 출력하는 등 실무에서 필요한 다양한 필터링 업무에 활용할 수 있습니다.

=SORT(FILTER(범위,지역="서울"))
/ / 지역이 서울인 데이터를 필터링한 후, 첫번째 열을 기준으로 오름차순 정렬합니다.
=TAKE(SORT(FILTER(범위,지역="서울")),3)
/ / 필터와 정렬이 적용된 데이터의 상위 3개 항목을 출력합니다.

FILTER 함수 간단 예제

1) FILTER 함수 기본 예제

=FILTER(B9:D18,(B9:B18=G9),"결과없음")
/ /범위에서 부서가 "A팀"인 데이터를 필터링합니다.

엑셀 FILTER 함수 기초 사용법 — FILTER 함수로 부서에서 A팀을 필터링합니다.

2) 여러 조건을 만족하는 데이터 필터링 예제

엑셀에서는 서로 다른 조건 배열을 '곱셈'으로 연산하면 여러 조건을 동시에 만족하는 데이터를 필터링할 수 있습니다. 또한 두 배열을 '덧셈'으로 연결하면 여러 조건 중 하나라도 만족하는 데이터를 필터링 할 수 있습니다.

=FILTER(범위, (조건범위1=조건) * (조건범위2=조건2), "결과없음" )
/ / 두 조건을 모두 만족하는 데이터를 필터링합니다. (곱셈 : AND조건)
=FILTER(범위, (조건범위1=조건) + (조건범위2=조건2), "결과없음" )
/ / 두 조건 중 하나라도 만족하는 경우를 필터링합니다. (덧셈 : OR조건)

예를 들어, 부서가 A팀이고 판매실적이 4,500 이상인 데이터를 필터링 할 경우, 다음과 같이 FILTER 함수를 작성하면 됩니다.

=FILTER(B9:D18,(B9:B18="A팀")*(D9:D18>4500),"결과없음")

FILTER 함수 다중조건 검색 — 부서와 판매실적 조건을 모두 만족하는 값을 필터링합니다.

오빠두Tip : 두 배열의 연산으로 다중 조건을 계산하는 방법의 자세한 이론 설명은 아래 기초 영상 강의를 참고하세요!👇

3) 부분일치(특정 단어 포함) 조건 필터링 예제

ISNUMBER-SEARCH 공식을 사용하면 범위에서 특정 단어의 포함여부를 확인할 수 있습니다.

=ISNUMBER(SEARCH(단어,범위))

이 공식을 FILTER 함수에 적용하면 다음과 같이 특정 단어 포함하는 조건으로 데이터를 필터링하는 공식을 완성할 수 있습니다.

=FILTER(범위,ISNUMBER(SEARCH(조건,조건범위)),"결과없음")
=FILTER(B9:D18,ISNUMBER(SEARCH("임",C9:C18)),"결과없음")
/ / 이름 중 "임"을 포함하는 직원의 데이터를 필터링합니다.

FILTER 함수 부분일치 검색 — 부분일치검색 공식을 사용하여 특정문자를 포함하는 값을 필터링합니다.

4) 다중 조건 필터링 빈칸 인식 문제 해결

다중 조건으로 필터를 적용한 경우, 조건 하나가 비어있을 때 다른 조건에 상관없이 항상 '결과없음'이 출력되는 문제가 발생할 수 있습니다.

FILTER함수 다중조건 문제점 — FILTER 함수 다중조건 사용시, 조건중 하나가 빈칸일 경우 모든 결과가 FALSE로 반환됩니다.

이러한 경우, 다음과 같이 IF 함수를 활용하여 조건이 비어있을 때에는 TRUE 배열을 반환하도록 공식을 작성해서 문제를 해결할 수 있습니다.

=FILTER(범위, IF(조건1="",조건범위1=조건범위1,(조건범위1=조건1))*IF(조건2="",조건범위2=조건범위2,(조건범위2=조건2)), "결과없음" )
/ / 조건이 비어있을 경우, 모든 조건을 대상으로 필터를 적용합니다.

예를 들어, 다음과 같이 FILTER 함수를 작성하면 조건으로 부서가 비어있을 때에는 모든 부서를 검색하게 됩니다.

=FILTER(B9:D18,IF(G9="",B9:B18=B9:B18,(B9:B18=G9))*(D9:D18>G10),"결과없음")

FILTER 함수 다중조건 문제 해결 — IF 함수를 사용하여 FILTER 함수 다중조건 빈칸인식 문제를 해결합니다.

5) 필터링 결과를 원하는 기준으로 정렬하기

SORT 함수를 활용하면 필터링 결과를 원하는 기준으로 실시간 정렬할 수 있습니다.

=SORT(FILTER(범위,조건),기준열,정렬순서)
/ / 필터링 결과를 열번호 기준으로 오름차순/내림차순 정렬합니다.

예를들어, A부서로 필터링된 결과를 '판매실적' 기준으로 '오름차순'으로 정렬할 경우 다음과 같이 수식을 작성합니다.

=SORT(FILTER(B9:D18,(B9:B18=G9),"결과없음"),3,1)
/ / 필터링 된 결과를 3번째 열 기준으로 오름차순 정렬합니다.

FILTER함수 정렬 — SORT 함수를 응용하여 FILTER 함수 결과값을 원하는 기준으로 필터링 할 수도 있습니다.

FILTER 함수 상세 가이드

1) FILTER 함수 보고서에 실시간 테두리 칠하기 (조건부서식)

엑셀의 조건부서식을 활용하면 조건이 바뀔 때마다 매번 달라지는 FILTER 함수 출력 범위에 동적으로 테두리를 적용할 수 있습니다.

=$A1<>""
/ / 알파벳 앞에만 $ 표시를 추가합니다. (열절대참조)

오빠두Tip : 참조방식을 활용한 조건부서식의 자세한 이론 설명은 아래 기초 입문 강의에서 알기 쉽게 정리했으니 참고해주세요!👇

FILTER 함수로 출력되는 범위를 넉넉하게 선택합니다.
FILTER 함수로 출력될 범위를 넉넉하게 선택합니다.
'홈' - '조건부서식' - '새 규칙'으로 이동합니다.
'조건부서식' - '새규칙'으로 이동합니다.
'수식을 사용하여 서식을 지정할 셀 결정'을 선택한 후, 아래 수식을 입력합니다.
```
$기준셀(열 절대참조)<>""
```
수식을 입력합니다. '열절대참조'로 셀주소를 입력하는 것에 주의합니다.
[서식]을 클릭한 후, '테두리'에서 윤곽선을 선택합니다.
'서식' - '테두리' 에서 '윤곽선'을 선택합니다.
이제 FILTER 함수로 출력된 범위에만 동적으로 테두리가 적용됩니다.
FILTER 함수로 범위가 출력될 때마다, 값이 입력된 범위에만 테두리가 칠해집니다.