엑셀 FILTER 함수 사용법 및 실전예제 총정리 :: 동적배열함수

입력한 조건을 바탕으로 데이터를 필터링하는 FILTER 함수의 사용법 및 주의사항을 알아봅니다.

홈페이지 » 엑셀 FILTER 함수 사용법 및 실전예제 :: 동적배열함수

엑셀 FILTER 함수 사용법 및 실전예제 총정리 :: 동적배열함수

FILTER 함수 목차 바로가기
함수 구문
= FILTER ( 범위, 조건, [결과없음반환값] )
인수 알아보기
인수설명
범위필터링 할 전체 범위 또는 배열입니다.
조건범위에서 필터링을 적용할 조건입니다.
조건의 넓이 또는 높이는 범위와와 반드시 동일해야 합니다.
결과없음반환값
[선택인수]
FILTER 함수의 결과로 아무것도 반환되지 않을경우 출력할 값입니다.
기본값으로 #CALC! 오류를 반환합니다.
호환성
운영체제호환성
Windows 버전오피스 365 버전에서만 지원됩니다.
Mac 버전오피스 365 버전에서만 지원됩니다.

예제파일 다운로드

관련 기초함수
범위에서 일치하는 항목을 찾아 반환합니다. VLOOKUP 상위호한 함수입니다.
입력한 범위에서 고유값만 추출하여 반환합니다.

엑셀 FILTER 함수 사용예제

1. FILTER 함수 기본 사용법 (셀을 참조하여 동적 필터링하기)
=FILTER(범위, (조건범위=조건), "결과없음" )

조건범위에서 조건과 일치하는 값을 찾아 필터링합니다. 조건 연산자로 등호(=) 이외에도 크게(>), 작게(<) 등을 이용할 수 있습니다.

예제파일에 사용된 공식

=FILTER(B9:D18,(B9:B18=G9),"결과없음")
엑셀 FILTER 함수 기초 사용법
FILTER 함수로 부서에서 A팀을 필터링합니다.
2. FILTER 함수 다중조건 필터링 (AND 조건 필터링)
=FILTER(범위, (조건범위1=조건)*(조건범위2=조건2), "결과없음" )

각각의 조건범위에서 해당 조건을 모두 일치하는 값을 찾아 필터링합니다. 즉, 두개의 조건을 '모두 만족'할 경우의 값을 불러옵니다. 예를들어, A부서 '이고 동시에' 판매실적이 4500이상일때의 값을 출력합니다.

AND조건 필터링은 각각의 조건을 '곱셈'으로 계산합니다.

예제파일에 사용된 공식

=FILTER(B9:D18,(B9:B18=G9)*(D9:D18>G10),"결과없음")
FILTER 함수 다중조건 검색
부서와 판매실적 조건을 모두 만족하는 값을 필터링합니다.
3. 다중조건 필터링 빈칸인식 문제해결
=FILTER(범위, IF(조건1="",조건범위1=조건범위1,(조건범위1=조건1))*IF(조건2="",조건범위2=조건범위2,(조건범위2=조건2)), "결과없음" )

AND 조건의 다중조건 필터링 시, 조건 중 하나라도 빈칸이 될 경우 모든 결과값으로 FALSE가 반환되어 다른 조건에 상관없이 항상 '결과없음'이 출력되는 문제가 발생합니다.

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

빈칸인식 문제는 조건안에 IF 함수를 사용하여 간단히 문제를 해결할 수 있습니다.

예제파일에 사용된 공식

=FILTER(B9:D18,IF(G9="",B9:B18=B9:B18,(B9:B18=G9))*(D9:D18>G10),"결과없음")
FILTER 함수 다중조건 문제 해결
IF 함수를 사용하여 FILTER 함수 다중조건 빈칸인식 문제를 해결합니다.
4. FILTER 함수 다중조건 필터링 (OR 조건 필터링)
=FILTER(범위, (조건범위1=조건)+(조건범위2=조건2), "결과없음" )

OR조건으로 다중조건 필터링을 적용할 수도 있습니다. 예를들어, A부서 '이거나 또는' B부서 일 경우의 조건을 모두 필터링하게 됩니다.

OR 조건 필터링은 여러개의 조건을 '덧셈'으로 계산합니다.

예제파일에 사용된 공식

=FILTER(B9:D18,(B9:B18=G9)+(B9:B18=G10),"결과없음")
FILTER함수 OR조건 검색
여러 조건을 덥셈으로 입력하면 OR 조건 필터링이 됩니다.
5. 부분일치(특정문자 포함) 조건으로 필터링
=FILTER(범위, ISNUMBER(SEARCH(조건,조건범위)), "결과없음" )

예를들어, 직원명에서 '임'씨로 시작하는 사람 또는 '서현'이라는 이름을 가진 사람 등 '특정 문자를 포함하는 값'을 모두 필터링 하려면 어떻게 해야할까요?

바로 ISNUMBER-SEARCH 함수 부분일치 공식을 FILTER 함수의 조건으로 사용하면 간단히 해결됩니다.

예제파일에 사용된 공식

=FILTER(B9:D18,ISNUMBER(SEARCH(G9,C9:C18)),"결과없음")
FILTER 함수 부분일치 검색
부분일치검색 공식을 사용하여 특정문자를 포함하는 값을 필터링합니다.
6. 반환값을 원하는 기준으로 정렬하기
=SORT(FILTER(범위, (조건범위=조건), "결과없음"), 열번호, 정렬방향)

SORT 함수를 같이 응용하면, FILTER 함수로 출력된 값을 원하는 기준에 맞춰 정렬할 수 있습니다. 예를들어, A부서로 필터링된 범위를 '판매실적'을 기준으로 '오름차순'으로 정렬하여 값을 출력하게 됩니다.

예제파일에 사용된 공식

=SORT(FILTER(B9:D18,(B9:B18=G9),"결과없음"),3,1)
FILTER함수 정렬
SORT 함수를 응용하여 FILTER 함수 결과값을 원하는 기준으로 필터링 할 수도 있습니다.
7. 필터함수로 출력된 범위만 테두리 칠하기 (조건부서식 응용)
=$A1<>""
'// 알파벳 앞에만 $ 표시를 붙이는 것에 주의합니다. (열절대참조)

FILTER 함수는 동적배열함수로 검색조건에 따라 매번 출력범위가 변합니다. 이럴경우, 출력된 범위에만 테두리를 자동으로 입혀주려면 어떻게 해야 할까요?

조건부서식을 응용하여 아주 간단하게 해결할 수 있습니다.

  1. FILTER 함수로 출력될 예상범위를 넉넉하게 선택합니다.

    FILTER함수 테두리 적용할 범위 선택
    FILTER 함수로 출력될 범위를 넉넉하게 선택합니다.
  2. '홈' - '조건부서식' - '새 규칙'으로 이동합니다.

    조건부 서식 이동
    '조건부서식' - '새규칙'으로 이동합니다.
  3. '수식을 사용하여 서식을 지정할 셀 결정'을 선택한 뒤, 아래 수식을 입력합니다.
    $기준셀(열 절대참조)<>""

    조건부서식 적용
    수식을 입력합니다. '열절대참조'로 셀주소를 입력하는 것에 주의합니다.
  4. [서식]을 클릭한 뒤, '테두리'에서 윤곽선을 선택합니다.

    조건부서식 테두리 설정
    '서식' - '테두리' 에서 '윤곽선'을 선택합니다.
  5. FILTER 함수로 값이 출력될 때마다, 출력된 범위에만 테두리가 칠해집니다.

    FILTER 함수 테두리 완성 GIF
    FILTER 함수로 범위가 출력될 때마다, 값이 입력된 범위에만 테두리가 칠해집니다.

함수 사용시 참고사항

  • 엑셀 FILTER 함수는 가로 또는 세로방향으로 모두 필터링이 가능합니다.
  • 조건으로 입력되는 값의 높이 또는 넓이는 첫번째 인수인 '범위'와 반드시 동일해야 합니다. 그렇지 않을경우 FILTER 함수는 #VALUE!오류를 반환합니다.
  • 만약 FILTER 함수로 반환되는 결과값이 없고 [결과없음반환값]이 입력되지 않으면, FILTER 함수는 #CALC!오류를 반환합니다.
  • 만약 조건으로 입력된 수식이 잘못되어 오류를 반환할 경우(#N/A!. #VALUE! 등), FILTER 함수 또한 항상 오류를 반환하게 됩니다.
  • FILTER 함수가 '다른 통합문서'를 참조하여 작성될 경우, 해당 통합문서는 반드시 열려있어야 합니다. 그렇지 않을 경우, FILTER 함수는 #REF!오류를 반환합니다.
관련 함수공식
범위 내 특정 조건을 만족하는 경우의 고유값을 추출합니다

링크: MS 홈페이지 FILTER 함수 사용법

5 25 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
41 Comments
Inline Feedbacks
View all comments
엑셀지식쌓기
엑셀지식쌓기
2020년 3월 22일 9:18 오후
게시글평점 :
     

오빠두엑셀님~ 회원가입후 댓글2개이상 했으나 일반회원으로 자동등업이 안되어 여기다 문의드립니다~ 질문드리고 싶은 사항이 있는데 글쓰기 권한이 안되어요~

sonrain
sonrain
2020년 3월 28일 11:18 오전

오래된 유투브 구독자 입니다. 앞으로도 좋은 강의 부탁 드립니다. ^^

까까
까까
2020년 3월 24일 10:33 오후
게시글평점 :
     

와 제가 이거 때문에 눈팅만 하다가 가입을 다 했네요

복 받으실꺼예요~

sonrain
sonrain
2020년 3월 28일 11:16 오전
게시글평점 :
     

매크로 이용해서 필터 기능 쓰고 있었는데 실시간필터가 더 유용해 보입니다.
업무 적용 해 볼게요. 감사합니다.

노은
노은
2020년 3월 29일 11:19 오전
게시글평점 :
     

다운 받아서 활용해보려고 완성파일을 다운받았는데 클릭하면 우측부분에 #NAME? 라고 다 떠버리는데 혹시 설정이 있을까요?
제가 엑셀 왕초보라. 왜 이런 현상이 발생했는지 모르겠어서요. 오피스 365사용합니다.

노은
노은
2020년 3월 29일 10:07 오후

혹시 인터넷에서 구매한 평생계정은 업데이트가 안되는걸까요? 따로 기업용을 구매해야 적용이 가능할까요?

레드불
레드불
2020년 3월 29일 5:42 오후
게시글평점 :
     

이 함수 때문에 가입 했습니다.
직장인들에게 신입니다. 오빠두 님

소보
소보
2020년 3월 30일 7:52 오후
게시글평점 :
     

안녕하세요.
로터스부터 현재 엑셀까지 업무에서 빼 놓을 수 없는 스프레스시트?? ㅎㅎㅎ

체계적으로 배울 수 있어 너무 좋습니다.

감사합니다.

빠끼
빠끼
2020년 3월 31일 10:34 오후
게시글평점 :
     

xfilter 함수 보고 오피스365 구매했습니다. 너무 잘 배우고 갑니다.

kenmamin
kenmamin
2020년 4월 2일 4:45 오후
게시글평점 :
     

유튜브로만 보다가 들어와서 봤습니다. 실무하는데 유익한 자료입니다. 감사합니다.

빠끼
빠끼
2020년 4월 3일 12:04 오전
게시글평점 :
     

와 필터함수 대박이네요.

디싫
디싫
2020년 4월 4일 10:14 오전
게시글평점 :
     

2007버전은 필터함수와 유사한 함수가 있을까요?

빡세
빡세
2020년 4월 6일 9:46 오후
게시글평점 :
     

잘만 쓰면 편리할것 같습니다. 아직 초보라 이해하기에 시간이 필요할것 같습니다.
ㅋㅋ

헬로엑셀
헬로엑셀
2020년 4월 7일 3:23 오전
게시글평점 :
     

엑셀이 진화하고 있네요... ㅎㅎㅎ 감사합니다!

감자
감자
2020년 4월 7일 9:36 오전
게시글평점 :
     

추가기능으로 한번 해봐야겠네요..

이병헌0324
이병헌0324
2020년 4월 7일 1:47 오후
게시글평점 :
     

정말 상세한 설명 감사합니다. 매일 동영상 보면서 연습하고 있습니다. 꾸벅~!!

닥코드
닥코드
2020년 4월 7일 2:07 오후
게시글평점 :
     

오빠두엑셀 유튜브 애청자 입니다. 필터함수 정말 회사에서도 사용하고 싶습니다. 너무 좋은거 같아요

헬로엑셀
헬로엑셀
2020년 4월 8일 3:37 오전
게시글평점 :
     

기존에 있던 엑셀 버튼중 고급필터와 어떤 차이가 있나요?
매크로로 고급필터를 적용하여 사용중인데 출력이 범위와 다르게 할수도 있나요? 예를들어, 위 샘플에서 부서와 판매실적만 출력이 가능할까요?

늑대다
늑대다
2020년 4월 9일 8:10 오전
게시글평점 :
     

와.. 잘 알아 갑니다 !!

JMON
JMON
2020년 4월 9일 1:38 오후
게시글평점 :
     

유튜브 영상보고 알게되서 업무에 유용하게 사용하고 있는 함수입니다!감사합니다!

양치는늑대
양치는늑대
2020년 4월 10일 9:45 오전
게시글평점 :
     

나름대로 유튜브에서 오빠두엑셀 보고 공부하다가 저두 이것보구 가입을 했는데요 ㅎㅎㅎ 제가 정부공사 입찰을 보면서 나름대로 분석을 하려다 보니 필터함수가 굉장히 도움이 되는거 같은데요 여기서 주시는 XFILTER를 다운받고 필터함수를 했는데 #VALUE로… 더보기 »

양치는늑대
양치는늑대
2020년 4월 11일 8:59 오전

혹시나 저두 배열함수는 Ctrl + Shift + Enter로 해야 한다구 하셔서 했었습니다. ㅎ
그런데 오늘 아침에 사무실에 나와서 다시한번 시도를 했는데 역시나 입니다.
왜 그런걸까요? ㅠㅠ

토토가
토토가
2020년 4월 12일 10:21 오전
게시글평점 :
     

덕분에 공종별루 내역분개 너무 잘하고있습니다 감사합니다.

피로엔박카스
피로엔박카스
2020년 4월 13일 9:04 오전
게시글평점 :
     

그동안 불편하게 쓰고 있었는데... 정말 감사합니다.

최종훈
2020년 5월 4일 7:19 오전
게시글평점 :
     

안녕하세요. 유투브를 통해 애청하고 있습니다. 명쾌하고 깔끔한 강의에 항상 감사드립니다. 필터 함수 사용해보려고 하는데 참조하려는 표의 값들이 모두 수식으로 되어 있어서 그런지 필터 함수가 적용이 안되는 것 같네요 ㅜㅜ 표의… 더보기 »

슥
2020년 5월 23일 3:17 오후
게시글평점 :
     

안녕하세요 질문이 생겨서 댓글남깁니다 올려주신 예제파일로 연습중인데 함수 그대로 입력했는데도 표의 마지막 데이터가 나오지 않습니다. 예를 들어, [조건]-[부서]에 C팀을 입력했을 때 C팀 임서영 7900 C팀 이하율 3800 나와야 하지만 제… 더보기 »

이연정
이연정
2020년 6월 15일 10:15 오후
게시글평점 :
     

유익한 정보 감사합니다 !
한가지 질문이 있습니다~
filter 함수의 필터링이 자동으로 세로로 되는데 가로로는 어떻게 하나요?
 

Last edited 4 월 전 by 이연정
이연정
이연정
2020년 6월 16일 5:07 오후

답글 감사합니다~
저는 산출된 결과가 세로정렬이 아닌 가로정렬이 되었으면 했는데,
그러려면 산출되는 데이터도 가로로 되어있어야 하는거죠? ㅠㅠ

소가소가
소가소가
2020년 10월 1일 11:03 오전
게시글평점 :
     

안녕하세요. 필터함수 사용할때 최대로 몇개 (결과값이 5개가 있는데 3개까지만 나오도록) 정하는 함수가 따로 있을까요?

엑셀극초보
2020년 10월 13일 6:58 오전
게시글평점 :
     

예제 감사합니다~ 그런데 이 함수는 따로 다운받거나 해서 이전버전에 쓸 수는 없는거죠?

41
0
여러분의 생각을 댓글로 남겨주세요.x