엑셀 FILTER 함수 사용법 및 실전예제 총정리
FILTER 함수 실전예제 목차 바로가기
강의요약
2020년 3월 기준, 모든 오피스 365 월채널 구독자 대상으로 엑셀에서 야심차게 준비한 '동적배열함수'가 공개되었습니다. 그중 대표적인 함수로는 XLOOKUP 함수와 FILTER 함수가 있는데요.
이번 강의에서는 FILTER 함수의 기초 사용법 및 다양한 응용방법, 그리고 문제해결법을 단계별로 알아보겠습니다.
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] FILTER 함수 실전사용법 총정리예제파일[엑셀고급] FILTER 함수 실전사용법 총정리완성파일
관련 기초함수
엑셀 FILTER 함수 실전예제 총정리
1. FILTER 함수 기초 사용법
FILTER 함수에는 아래 3개의 인수가 들어갑니다.
= FILTER ( 배열, 포함, [if empty] )인수 설명 배열 필터링을 적용할 전체 범위입니다. 포함 필터링을 적용할 조건을 '논리식'으로 입력합니다. if empty
[선택인수]FILTER 함수로 반환되는 결과값이 없을 경우에 출력할 결과값을 입력합니다. 더욱 자세한 FILTER 함수의 기초 사용법은 아래 관련 포스트에서 안내해드렸습니다.
2. 셀을 참조하여 동적으로 필터링하기
FILTER 함수의 조건으로 셀을 참조하여 수식을 작성하면, 셀에 입력된 값에 따라 실시간으로 필터링을 적용할 수 있습니다. 예제파일의 'FILTER 함수 -실전' 시트로 이동한 뒤, '판매내역' 데이터를 바탕으로 FILTER 함수를 작성해보겠습니다.
- 함수 수식을 더욱 쉽게 작성하기 위하여, 범위를 표로 변환합니다. '머릿글 포함'에 반드시 체크하는 것에 주의합니다.
원본 범위를 표로 변환합니다. - 표로 변환하였으면, 상단의 '표 디자인'에서 표 스타일을 없음으로 변경합니다.
표를 선택한 뒤, 표 스타일을 '없음'으로 변경합니다. - 표의 이름을 'tbl판매내역'으로 변경합니다.
표 이름을 'tbl판매내역'으로 변경합니다. - 표 우측으로 조건을 넣어줄 범위를 생성한 뒤, 셀의 배경색과 테두리를 변경합니다.
조건을 넣어줄 범위를 추가한 뒤, 서식을 변경합니다. - 이후 아래의 FILTER 함수를 입력합니다.
=FILTER(tbl판매내역,tbl판매내역[날짜]=J2,"결과없음")
FILTER 함수를 입력합니다. - 현재는 조건이 비어있어 "결과없음"이 결과값으로 출력됩니다.
현재는 조건이 비어있어 '결과없음'이 출력됩니다. - 날짜 조건으로 값을 입력합니다. 1/1, 1/2 등 날짜를 조건으로 입력하면 입력한 조건에 따라 데이터가 필터링됩니다.
날짜에 값을 입력하면 입력한 날짜로 데이터가 필터링됩니다. - 기존 데이터의 머릿글을 복사하여 필터링 된 범위로 붙여넣기 합니다.
머릿글을 복사하여 필터링된 범위 위로 붙여넣기합니다. - 필터링 된 범위의 서식을 변경합니다. '날짜' 열을 전체 선택한 뒤, 셀 서식을 '날짜형식'으로 변경합니다.
필터링된 범위의 셀 서식을 변경합니다. - '단가' 및 '가격' 열을 선택한 후, '천단위 구분기호'를 표시하고 '우측정렬'로 서식을 변경합니다.
단가 및 가격 범위의 서식을 변경합니다. - '판매채널' 및 '단가' 열을 숨겨줍니다. (단축키: Ctrl+0)
불필요한 열을 숨김처리합니다. - 이제 날짜 조건을 변경하면 입력된 값에 따라 데이터가 실시간으로 필터링됩니다.
날짜를 입력하면 조건에따라 데이터가 실시간으로 필터링됩니다.
3. 다중 조건 필터링 적용하기
FILTER 함수의 두번째 인수로 여러개의 조건을 '곱셈'으로 입력할 경우, 다중 조건으로 필터링을 적용할 수 있으며, FILTER 함수의 다중조건 공식은 아래와 같습니다.
= FILTER ( 전체범위, (조건범위1=조건1)*(조건범위2=조건2)*..., [if empty] )따라서 기존의 FILTER 함수를 아래의 수식으로 대체하면, '날짜'와 '제품명'으로 다중조건 필터링을 적용할 수 있게됩니다.
=FILTER(tbl판매내역,(tbl판매내역[날짜]=J2)*(tbl판매내역[제품명]=J3),"결과없음")
다중 조건으로 데이터를 필터링 할 수 있습니다. 4. 다중조건 적용 시, 빈칸 인식문제 해결
하지만 다중조건으로 필터링을 적용할 시, 한가지 문제점이 발생합니다. 바로 여러개의 조건 중에 하나라도 조건이 빈칸이 될 경우, 다른 조건에 상관없이 무조건 '결과없음'으로 반환되는 문제인데요.
다중조건 사용시, 조건이 하나라도 빈칸이되면 '결과없음'을 반환합니다. 이는 FILTER 함수에 입력된 조건인수를 확인하면 원인을 바로 파악할 수 있습니다.
- 조건 중, '제품명' 조건을 빈칸으로 변경합니다.
제품명의 조건을 빈칸으로 변경합니다. - FILTER 함수에서 '제품명'에 해당하는 조건을 마우스로 드래그하여 선택합니다.
FILTER 함수의 '제품명' 조건을 마우스로 드래그하여 선택합니다. - 키보드 F9키를 눌러 반환되는 값을 확인하면 모두 '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 함수가 사용되며, 공식은 아래와 같습니다.
유사일치 공식을 사용하면, 제품명을 모두 입력하지 않아도 '시스타', '피핀' 등으로 특정 단어를 포함한 모든 제품을 검색할 수 있습니다.
따라서 기존의 FILTER 함수를 아래 수식으로 대체 할 경우, 제품명의 일부분인 '브랜드' 및 '색상'을 검색하면 해당 단어를 포함하는 모든 데이터를 필터링 할 수 있습니다.=FILTER(tbl판매내역,IF(J2="",tbl판매내역[날짜]=tbl판매내역[날짜],(tbl판매내역[날짜]=J2))*IF(J3="",tbl판매내역[제품명]=tbl판매내역[제품명],(ISNUMBER(SEARCH(J3,tbl판매내역[제품명])))),"결과없음")
부분일치 공식으로 '특정단어'를 포함하는 모든 조건을 검색합니다. 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 함수로 특정 요일, 특정 주에 해당하는 값을 필터링 할 수도 있습니다.
조건범위에 MONTH 함수를 사용하여 특정월로 필터링할 수 있습니다. FILTER 함수로 3D 검색, 즉 다중 검색 조건으로 필터링 하는 방법을 단계별로 알아보겠습니다.
- 날짜 조건 옆 셀을 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다. 이후 제한대상을 '목록'으로 선택한 뒤, 원본으로 '날짜,요일,월'을 입력합니다.
데이터유효성검사로 '목록상자'를 추가합니다. - 이후 날짜조건이 입력된 셀을 선택한 후, 조건부 서식을 적용합니다. 검색조건으로 '월 또는 요일'이 선택되면 일반서식, '날짜'가 선택되면 날짜서식을 적용하겠습니다.
검색조건에 따른 조건부서식을 적용합니다. - '조건부서식' - '새규칙'으로 이동한 뒤, 검색조건이 '월' 이거나 '요일'일 경우 '일반서식'이 되도록 조건부서식을 변경합니다.
=OR($L$2="요일",$L$2="월")
'월' 또는 '요일'일 경우의 조건부서식을 추가합니다. - 같은 원리로 검색조건이 '날짜'일 경우 '날짜서식'이 되도록 새로운 조건부서식을 추가합니다.
=$L$2="날짜"
'날짜'일 경우의 조건부서식을 추가합니다. - 기존의 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판매내역[제품명])))),"결과없음")
- 이제 날짜, 요일 및 월에 따라 다양한 조건으로 데이터를 필터링 할 수 있습니다.
FILTER 함수 실전 공식이 완성되었습니다.
FILTER함수 반환범위를 동적차트로 출력하기
FILTER 함수로 출력된 데이터를 참조하여, 실시간으로 업데이트되는 동적차트를 만들 수 있습니다. 동적범위와 동적차트의 동작원리는 아래 관련 강의에서 더욱 자세히 설명해드렸습니다.
- FILTER 함수의 날짜범위를 동적범위로 변환합니다. '수식' - '이름관리자'로 이동하여 [새로만들기]를 클릭한 후, 아래의 표처럼 새로운 동적범위를 추가합니다.
구분 내용 이름 rng날짜 참조대상 =OFFSET($I$6,,,COUNTA($I$6:$I$1000)) 
이름관리자에서 새로운 동적범위를 추가합니다. - UNIQUE 함수를 사용하여 동적범위에서 고유값만 추출합니다. 우측에 새로운 '날짜'열을 추가한 뒤, 아래 수식을 입력합니다.
차트의 가로축에 들어갈 데이터 이므로, 반드시 TEXT 함수를 사용하여 텍스트형식으로 변환하는것에 주의합니다.=TEXT(UNIQUE(rng날짜),"mm/dd")
UNIQUE 함수로 날짜범위에서 고유값만 추출합니다. - '수량'과 '가격' 열을 추가한 뒤, SUMIF 함수를 사용하여 해당 날짜를 만족하는 수량과 가격의 합계를 계산합니다. 수식은 아래로 넉넉하게 자동채우기합니다.
추출한 고유값 범위에서 수량 및 가격 합계를 계산합니다. - 차트에 들어갈 동적범위를 추가합니다. (동적범위 수식은 각자 작성한 시트에 맞춰 알맞게 수정합니다.)
이름 참조대상 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)) - 고유값으로 출력된 범위를 전체 선택한 후, 차트를 생성합니다.
고유값이 출력된 범위에서 차트를 생성합니다. - 차트를 우클릭한 뒤, '데이터 선택'으로 이동합니다. 이후 범례항목 계열의 [편집] 버튼을 클릭하여 각 '수량'과 '가격'의 계열값을 수정합니다.
범례항목(계열) 계열값 수량 ='FILTER함수-실전'!rng수량_차트 가격 ='FILTER함수-실전'!rng가격_차트 
범례항목의 계열 값을 동적범위로 수정합니다. - 가로(항목) 축 레이블도 '편집'을 클릭하여 축 레이블 범위를 변경합니다.
항목 내용 축 레이블 범위 ='FILTER함수-실전'!rng날짜_차트 
축 레이블 범위의 참조값도 동적범위로 변경합니다. - 차트를 우클릭한 뒤, '차트종류변경'으로 이동합니다. 이후 '혼합'에서 수량을 '보조축'으로 선택합니다.
차트의 '수량' 값을 보조축에 출력합니다. - 차트를 적절히 수정하여 동적차트를 완성합니다. 보고서 작성용 차트를 만드는 차트만들기 5단계 공식은 아래 관련 강의에서 자세히 설명드렸습니다.
FILTER 함수를 사용한 동적차트가 완성되었습니다.
