엑셀 FILTER 함수 실전 사용법 및 문제해결, 총정리!

오피스 365에서 새롭게 공개된 FILTER 함수의 기초 사용법과 다양한 실전 응용예제를 단계별로 알아봅니다.

홈페이지 » 엑셀 FILTER 함수 실전 사용법 및 문제해결, 총정리!

엑셀 FILTER 함수 사용법 및 실전예제 총정리

FILTER 함수 실전예제 목차 바로가기
강의요약

2020년 3월 기준, 모든 오피스 365 월채널 구독자 대상으로 엑셀에서 야심차게 준비한 '동적배열함수'가 공개되었습니다. 그중 대표적인 함수로는 XLOOKUP 함수와 FILTER 함수가 있는데요.

이번 강의에서는 FILTER 함수의 기초 사용법 및 다양한 응용방법, 그리고 문제해결법을 단계별로 알아보겠습니다.

엑셀 filter 함수 사용법 동적차트 완성 GIF
FITLER 함수를 사용하여 실시간으로 필터링되는 차트를 제작합니다.
영상강의

예제파일 다운로드

관련 기초함수
문자열에서 특정문자의 시작 위치를 찾습니다.
범위에서 일치하는 항목을 찾아 반환합니다. VLOOKUP 상위호한 함수입니다.

엑셀 FILTER 함수 실전예제 총정리

1. FILTER 함수 기초 사용법

FILTER 함수에는 아래 3개의 인수가 들어갑니다.

= FILTER ( 배열, 포함, [if empty] )
인수설명
배열필터링을 적용할 전체 범위입니다.
포함필터링을 적용할 조건을 '논리식'으로 입력합니다.
if empty
[선택인수]
FILTER 함수로 반환되는 결과값이 없을 경우에 출력할 결과값을 입력합니다.

더욱 자세한 FILTER 함수의 기초 사용법은 아래 관련 포스트에서 안내해드렸습니다.

2. 셀을 참조하여 동적으로 필터링하기

FILTER 함수의 조건으로 셀을 참조하여 수식을 작성하면, 셀에 입력된 값에 따라 실시간으로 필터링을 적용할 수 있습니다. 예제파일의 'FILTER 함수 -실전' 시트로 이동한 뒤, '판매내역' 데이터를 바탕으로 FILTER 함수를 작성해보겠습니다.

  1. 함수 수식을 더욱 쉽게 작성하기 위하여, 범위를 표로 변환합니다. '머릿글 포함'에 반드시 체크하는 것에 주의합니다.

    범위를 표로 변환
    원본 범위를 표로 변환합니다.
  2. 표로 변환하였으면, 상단의 '표 디자인'에서 표 스타일을 없음으로 변경합니다.

    표 스타일 없음으로 변경
    표를 선택한 뒤, 표 스타일을 '없음'으로 변경합니다.
  3. 표의 이름을 'tbl판매내역'으로 변경합니다.

    표 이름 변경하기
    표 이름을 'tbl판매내역'으로 변경합니다.
  4. 표 우측으로 조건을 넣어줄 범위를 생성한 뒤, 셀의 배경색과 테두리를 변경합니다.

    filter 조건 범위
    조건을 넣어줄 범위를 추가한 뒤, 서식을 변경합니다.
  5. 이후 아래의 FILTER 함수를 입력합니다.
    =FILTER(tbl판매내역,tbl판매내역[날짜]=J2,"결과없음")

    filter 함수 입력
    FILTER 함수를 입력합니다.
  6. 현재는 조건이 비어있어 "결과없음"이 결과값으로 출력됩니다.

    filter 함수 결과 없음
    현재는 조건이 비어있어 '결과없음'이 출력됩니다.
  7. 날짜 조건으로 값을 입력합니다. 1/1, 1/2 등 날짜를 조건으로 입력하면 입력한 조건에 따라 데이터가 필터링됩니다.

    filter 함수 출력
    날짜에 값을 입력하면 입력한 날짜로 데이터가 필터링됩니다.
  8. 기존 데이터의 머릿글을 복사하여 필터링 된 범위로 붙여넣기 합니다.

    필터 범위 머릿글 복사
    머릿글을 복사하여 필터링된 범위 위로 붙여넣기합니다.
  9. 필터링 된 범위의 서식을 변경합니다. '날짜' 열을 전체 선택한 뒤, 셀 서식을 '날짜형식'으로 변경합니다.

    filter 함수 출력값 서식 변경
    필터링된 범위의 셀 서식을 변경합니다.
  10. '단가' 및 '가격' 열을 선택한 후, '천단위 구분기호'를 표시하고 '우측정렬'로 서식을 변경합니다.

    filter 함수 서식 변경 천단위
    단가 및 가격 범위의 서식을 변경합니다.
  11. '판매채널' 및 '단가' 열을 숨겨줍니다. (단축키: Ctrl+0)

    필터함수 불필요한 범위 숨기기
    불필요한 열을 숨김처리합니다.
  12. 이제 날짜 조건을 변경하면 입력된 값에 따라 데이터가 실시간으로 필터링됩니다.

    filter 함수 실시간 업데이트
    날짜를 입력하면 조건에따라 데이터가 실시간으로 필터링됩니다.
3. 다중 조건 필터링 적용하기

FILTER 함수의 두번째 인수로 여러개의 조건을 '곱셈'으로 입력할 경우, 다중 조건으로 필터링을 적용할 수 있으며, FILTER 함수의 다중조건 공식은 아래와 같습니다.

= FILTER ( 전체범위, (조건범위1=조건1)*(조건범위2=조건2)*..., [if empty] )

따라서 기존의 FILTER 함수를 아래의 수식으로 대체하면, '날짜'와 '제품명'으로 다중조건 필터링을 적용할 수 있게됩니다.

=FILTER(tbl판매내역,(tbl판매내역[날짜]=J2)*(tbl판매내역[제품명]=J3),"결과없음")
filter 함수 다중조건 필터
다중 조건으로 데이터를 필터링 할 수 있습니다.
4. 다중조건 적용 시, 빈칸 인식문제 해결

하지만 다중조건으로 필터링을 적용할 시, 한가지 문제점이 발생합니다. 바로 여러개의 조건 중에 하나라도 조건이 빈칸이 될 경우, 다른 조건에 상관없이 무조건 '결과없음'으로 반환되는 문제인데요.

필터함수 빈칸 결과없음
다중조건 사용시, 조건이 하나라도 빈칸이되면 '결과없음'을 반환합니다.

이는 FILTER 함수에 입력된 조건인수를 확인하면 원인을 바로 파악할 수 있습니다.

  1. 조건 중, '제품명' 조건을 빈칸으로 변경합니다.

    필터함수 다중조건 문제
    제품명의 조건을 빈칸으로 변경합니다.
  2. FILTER 함수에서 '제품명'에 해당하는 조건을 마우스로 드래그하여 선택합니다.

    filter 함수 조건 인수 목록 확인
    FILTER 함수의 '제품명' 조건을 마우스로 드래그하여 선택합니다.
  3. 키보드 F9키를 눌러 반환되는 값을 확인하면 모두 'FALSE'로 값이 반환되는 것을 확인할 수 있습니다.

    모든 값 false 반환
    인수의 결과값으로 모두 FALSE가 반환되는 것을 볼 수 있습니다.

이렇게 모든 갑이 FALSE로 반환되는 문제는, IF 함수를 사용하여 간단하게 해결할 수 있는데요. 사용되는 공식은 아래와 같습니다.

= FILTER ( 전체범위, IF(조건1="",조건범위1=조건범위1,(조건범위1=조건1))*IF(조건2="",조건범위2=조건범위2,(조건범위2=조건2))*..., [if empty] )

따라서 기존의 FILTER 함수를 아래 수식으로 대체하면, 조건 중 하나가 빈칸이 되더라도 원하는 조건으로 쉽게 필터링할 수 있습니다.

=FILTER(tbl판매내역,IF(J2="",tbl판매내역[날짜]=tbl판매내역[날짜],(tbl판매내역[날짜]=J2))*IF(J3="",tbl판매내역[제품명]=tbl판매내역[제품명],(tbl판매내역[제품명]=J3)),"결과없음")
조건 중 하나 빈칸이여도 필터
조건 중 하나가 빈칸이 되어도 정상적으로 필터링됩니다.
5. 부분 일치로 필터링하기

만약 입력해야 할 조건이 길어서 필터링을 적용하는데에 어려움이 있다면, 유사일치 공식을 사용할 수 있습니다. 유사일치 공식에는 ISNUMBER 함수와 SEARCH 함수가 사용되며, 공식은 아래와 같습니다.

=IF(ISNUMBER(SEARCH(찾을문자,셀)),출력값,"")
엑셀 부분일치 검색 공식 상세설명 바로가기

유사일치 공식을 사용하면, 제품명을 모두 입력하지 않아도 '시스타', '피핀' 등으로 특정 단어를 포함한 모든 제품을 검색할 수 있습니다.
따라서 기존의 FILTER 함수를 아래 수식으로 대체 할 경우, 제품명의 일부분인 '브랜드' 및 '색상'을 검색하면 해당 단어를 포함하는 모든 데이터를 필터링 할 수 있습니다.

=FILTER(tbl판매내역,IF(J2="",tbl판매내역[날짜]=tbl판매내역[날짜],(tbl판매내역[날짜]=J2))*IF(J3="",tbl판매내역[제품명]=tbl판매내역[제품명],(ISNUMBER(SEARCH(J3,tbl판매내역[제품명])))),"결과없음")
filter 함수 부분일치
부분일치 공식으로 '특정단어'를 포함하는 모든 조건을 검색합니다.
6. 3D 검색, 다중 검색조건으로 필터링하기

FILTER 함수는 다양한 방법으로 응용할 수 있는 만능함수입니다. 특히 데이터를 분석하거나 보고서를 작성할 때 아주 유용하게 사용할 수 있는데요.

예를들어, 보다 유연한 자료분석을 위해 아래 3가지 조건으로 날짜를 필터링한다고 가정하겠습니다.

  • 요일
  • 날짜

만약 위 3가지 검색 조건으로 날짜를 핕터링해야 할 경우, 어떻게 해야 할까요? FILTER 함수는 조건으로 '배열'을 받아옵니다. 따라서 조건안에 또 다른 함수를 사용할 수 있다는 엄청난 장점이 있는데요. 아래와 같이 '날짜' 범위에 MONTH 함수를 사용해보겠습니다.

=FILTER(tbl판매내역,IF(J2="",tbl판매내역[날짜]=tbl판매내역[날짜],(MONTH(tbl판매내역[날짜]=J2)))*IF(J3="",tbl판매내역[제품명]=tbl판매내역[제품명],(ISNUMBER(SEARCH(J3,tbl판매내역[제품명])))),"결과없음")

MONTH 함수를 사용하여 날짜범위를 필터링하면 1월, 2월과 같이 특정 월을 만족하는 모든 데이터를 필터링 할 수 있습니다. 같은원리로 WEEKDAY 함수나 WEEKNUM 함수로 특정 요일, 특정 주에 해당하는 값을 필터링 할 수도 있습니다.

filter 함수 월 핕터링
조건범위에 MONTH 함수를 사용하여 특정월로 필터링할 수 있습니다.

FILTER 함수로 3D 검색, 즉 다중 검색 조건으로 필터링 하는 방법을 단계별로 알아보겠습니다.

  1. 날짜 조건 옆 셀을 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다. 이후 제한대상을 '목록'으로 선택한 뒤, 원본으로 '날짜,요일,월'을 입력합니다.

    데이터 유효성 목록 추가
    데이터유효성검사로 '목록상자'를 추가합니다.
  2. 이후 날짜조건이 입력된 셀을 선택한 후, 조건부 서식을 적용합니다. 검색조건으로 '월 또는 요일'이 선택되면 일반서식, '날짜'가 선택되면 날짜서식을 적용하겠습니다.

    날짜 요일에 따른 조건부서식
    검색조건에 따른 조건부서식을 적용합니다.
  3. '조건부서식' - '새규칙'으로 이동한 뒤, 검색조건이 '월' 이거나 '요일'일 경우 '일반서식'이 되도록 조건부서식을 변경합니다.
    =OR($L$2="요일",$L$2="월")

    월 요일 조건부서식 추가
    '월' 또는 '요일'일 경우의 조건부서식을 추가합니다.
  4. 같은 원리로 검색조건이 '날짜'일 경우 '날짜서식'이 되도록 새로운 조건부서식을 추가합니다.
    =$L$2="날짜"

    날짜 조건부서식 추가
    '날짜'일 경우의 조건부서식을 추가합니다.
  5. 기존의 FILTER 함수를 아래 수식으로 대체합니다.
    =FILTER(tbl판매내역,IF(J2="",tbl판매내역[날짜]=tbl판매내역[날짜],IF(L2="월",MONTH(tbl판매내역[날짜])=J2,IF(L2="요일",WEEKDAY(tbl판매내역[날짜])=J2,tbl판매내역[날짜]=J2)))*IF(J3="",tbl판매내역[제품명]=tbl판매내역[제품명],(ISNUMBER(SEARCH(J3,tbl판매내역[제품명])))),"결과없음")
  6. 이제 날짜, 요일 및 월에 따라 다양한 조건으로 데이터를 필터링 할 수 있습니다.

    엑셀 FILTER 함수 실전예제 완성 GIF
    FILTER 함수 실전 공식이 완성되었습니다.

FILTER함수 반환범위를 동적차트로 출력하기

FILTER 함수로 출력된 데이터를 참조하여, 실시간으로 업데이트되는 동적차트를 만들 수 있습니다. 동적범위와 동적차트의 동작원리는 아래 관련 강의에서 더욱 자세히 설명해드렸습니다.

  1. FILTER 함수의 날짜범위를 동적범위로 변환합니다. '수식' - '이름관리자'로 이동하여 [새로만들기]를 클릭한 후, 아래의 표처럼 새로운 동적범위를 추가합니다.
    구분내용
    이름rng날짜
    참조대상=OFFSET($I$6,,,COUNTA($I$6:$I$1000))

    filter 출력 범위 동적범우 ㅣ추가
    이름관리자에서 새로운 동적범위를 추가합니다.
  2. UNIQUE 함수를 사용하여 동적범위에서 고유값만 추출합니다. 우측에 새로운 '날짜'열을 추가한 뒤, 아래 수식을 입력합니다.
    차트의 가로축에 들어갈 데이터 이므로, 반드시 TEXT 함수를 사용하여 텍스트형식으로 변환하는것에 주의합니다.

    =TEXT(UNIQUE(rng날짜),"mm/dd")

    unique 함수 와 text 함수
    UNIQUE 함수로 날짜범위에서 고유값만 추출합니다.
  3. '수량'과 '가격' 열을 추가한 뒤, SUMIF 함수를 사용하여 해당 날짜를 만족하는 수량과 가격의 합계를 계산합니다. 수식은 아래로 넉넉하게 자동채우기합니다.

    sumif 함수 조건별 합계
    추출한 고유값 범위에서 수량 및 가격 합계를 계산합니다.
  4. 차트에 들어갈 동적범위를 추가합니다. (동적범위 수식은 각자 작성한 시트에 맞춰 알맞게 수정합니다.)
    이름참조대상
    rng날짜_차트=OFFSET($P$6,,,COUNTA($P$6:$P$1000))
    rng수량_차트=OFFSET($Q$6,,,COUNTA($P$6:$P$1000))
    rng가격_차트=OFFSET($R$6,,,COUNTA($P$6:$P$1000))
  5. 고유값으로 출력된 범위를 전체 선택한 후, 차트를 생성합니다.

    새로운 차트 추가
    고유값이 출력된 범위에서 차트를 생성합니다.
  6. 차트를 우클릭한 뒤, '데이터 선택'으로 이동합니다. 이후 범례항목 계열의 [편집] 버튼을 클릭하여 각 '수량'과 '가격'의 계열값을 수정합니다.
    범례항목(계열)계열값
    수량='FILTER함수-실전'!rng수량_차트
    가격='FILTER함수-실전'!rng가격_차트

    계열 편집
    범례항목의 계열 값을 동적범위로 수정합니다.
  7. 가로(항목) 축 레이블도 '편집'을 클릭하여 축 레이블 범위를 변경합니다.
    항목내용
    축 레이블 범위='FILTER함수-실전'!rng날짜_차트

    축 레이블 동적범위 추가
    축 레이블 범위의 참조값도 동적범위로 변경합니다.
  8. 차트를 우클릭한 뒤, '차트종류변경'으로 이동합니다. 이후 '혼합'에서 수량을 '보조축'으로 선택합니다.

    혼합 차트 변경
    차트의 '수량' 값을 보조축에 출력합니다.
  9. 차트를 적절히 수정하여 동적차트를 완성합니다. 보고서 작성용 차트를 만드는 차트만들기 5단계 공식은 아래 관련 강의에서 자세히 설명드렸습니다.

    엑셀 filter 함수 사용법 동적차트 완성 GIF
    FILTER 함수를 사용한 동적차트가 완성되었습니다.

링크 : MS 홈페이지 FILTER 함수 상세설명

4.7 18 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
31 Comments
Inline Feedbacks
View all comments
호야
호야
2020년 3월 19일 7:08 오전
게시글평점 :
     

원하던 함수가 생겼네요 잘보고 갑니다.

이원근
이원근
2020년 3월 19일 10:25 오전
게시글평점 :
     

항상 잘 배우고 갑니다. 궁금해서 질문 하나드립니다. 필터함수를 적용할 값에 다른 엑셀 파일에 있는 외부 주소를 적용하고 싶은데요. 예를 들어 A 파일의 데이터를 B 파일의 필터함수 주소로 적용한다고 가정했을 때,… 더보기 »

람보
람보
2020년 3월 24일 5:21 오후
게시글평점 :
     

xFILTER 예제파일 실행하고부터, 모든엑셀파일을 열때마다 에제파일 실행되고나서 엑셀파일들이 열려요
이거 해결방법이 없을까요!!

바우데기
바우데기
2020년 3월 24일 5:33 오후
게시글평점 :
     

DuTool 2번째 강의를 보았습니다.
그런데 제가 DuTool을 설치해서 xFilter로 보고서를 만들어서 다른 직원과 공유하면,
그 직원은 FILTER 기능을 사용할 수 있는 건가요?

JY
JY
2020년 3월 25일 10:45 오전
게시글평점 :
     

정말 잘 배우고 갑니다.

바우데기
바우데기
2020년 3월 25일 2:05 오후
게시글평점 :
     

xFILTER함수를 사용해보려고 DuTool을 설치한 후에, 예제파일로 동영상 강의를 따라가는데 시작부터 난관이 있습니다.
=xFILTER(tbl판매내역,tbl판매내역[날짜]=J2,"결과없음")
으로 입력하는데, 왜 #VALUE! 에러가 뜨는 걸까요?

어디가 잘못되었는지 지적 부탁드립니다.

바우데기
바우데기
2020년 3월 25일 4:44 오후

아하~ 제가 급한 마음에 기본을 잊고 있었네요.
빠른 답변 감사합니다.

공명
공명
2020년 4월 2일 1:36 오후
게시글평점 :
     

와 정말 엑셀로 안되는게 없네요
꼭 필요했던 함수! 회사컴이 2010버전이라 당장 활용할 수 없다는게 아쉽네요
열심히 활동해서 함수 업데이트 해야겠습니다
(혹 포인트 유료충전은 안되려나요 너무 탐나는 함수네요 ㅠㅠ )

채은아빠
채은아빠
2020년 4월 7일 3:46 오후
게시글평점 :
     

필터된 내용에 표서식이 안되는건가요? 아님 방법이 있는데 제가 못찾는 건가요 ㅠㅠ 표서식이 반영되면 정말 쓸 수 있는 곳이 무궁무진해 질거 같은데 방법이 있다면 알려주세요.

빠끼
빠끼
2020년 4월 19일 11:22 오후
게시글평점 :
     

제가 너무 필요했던 함수라 바로 xfilter함수를 사용하여 문서를 하나 만들었습니다. 그런데 다른 문구는 다 찾아지는데 유독 하나만 안 찾아집니다. 그리고 f9를 눌러서 어떻게 나오는지 확인해보니 FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;棊 이렇게 나오더라구요. 棊 이… 더보기 »

Netpiapro
Netpiapro
2020년 4월 20일 3:22 오후
게시글평점 :
     

강의 하나하나가 주옥 같아요. 오빠 덕분에 근자감이 하늘을 찌르네요. 이러다 엑셀로 인공위성도 띄울기셉니다.

하희ddd
하희ddd
2020년 4월 24일 10:51 오후
게시글평점 :
     

3개의 다중조건도 할수있나요?

엑셀야나두
엑셀야나두
2020년 5월 7일 8:51 오전
게시글평점 :
     

안녕하세요~. 엑셀이 OFFICE365 PROPLUS 인데
왜 FILTER함수가 안먹힐까요.ㅠㅠ
너무너무 유용할꺼 같은데.. ㅠㅠ

8번선수
8번선수
2020년 7월 2일 3:42 오후
게시글평점 :
     

감사합니다. 사용할일이 생기면 꼭 써봐야 겠습니다. 근데 필터함수의 조건에 와일드카드 문자도 사용가능한가요?

진형록
진형록
2020년 7월 28일 7:02 오후
게시글평점 :
     

안녕하세요[email protected] 강의 감사합니다! 질문이 있습니다!! 필터로 걸러진 값들을 그대로 선택 혹은 복사하고 싶은데, 해당 값들은 필터 수식으로 나오더라구요.. ㅜㅜ 필터로 걸러진 값이 인터넷주소라서 클릭하면 바로 접속하게 하고 싶은데.. 혹시 어떻게… 더보기 »

진형록
진형록
2020년 7월 29일 11:35 오전

빠른 회신 감사합니다[email protected]!!
아 그렇군요..
더 알아봐야겠네용[email protected]

진형록
진형록
2020년 7월 28일 11:48 오후
게시글평점 :
     

안녕하세요! 혹시 필터값이 나오는 부분을 클릭하면 수식이 아니고 값으로 나오게 할 수 없을까요?? 제가 인터넷주소 링크를 필터값으로 나오게 했는데요.. 그 주소를 누르면 바로 인터넷접속 하게 하고 싶은데.. 값이 아니라 수식으로… 더보기 »

cold1228
cold1228
2020년 7월 31일 1:13 오전
게시글평점 :
     

부분일치 조건으로 필터링하기 관련해서 문의드립니다. 특정 기호 앞에 있는 문자열을 조건으로 필터링이 가능할까요??? 예를들어 모델명이 MAN-2784 라면 -앞에 MAN만 입력하면 MAN-2784내용을 필터링 했으면 좋겠습니다.

프롭
프롭
2020년 9월 1일 6:44 오후
게시글평점 :
     

=IF(ISNUMBER(SEARCH(찾을문자,셀)),출력값,"")
해당 식을 날짜와 제품명에 2군데다 적용은 안될까요? 직접해보니..안되긴하던데;;

효율짱짱
효율짱짱
2020년 9월 24일 8:13 오후
게시글평점 :
     

강의 완전 좋습니다. ^^

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