엑셀 FILTER 함수 사용법 총정리

업데이트 강의 안내

초보자를 위한 5분 핵심 요약! 아래 5분 핵심정리 강의에서 FILTER 함수의 주요 내용을 빠르게 확인할 수 있습니다.

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

2020년 3월 기준, 모든 오피스 365 월채널 구독자를 대상으로 엑셀의 '동적배열함수'가 공개되었습니다. 대표적인 함수로는 XLOOKUP 함수와 FILTER 함수가 있습니다.

이번 강의에서는 FILTER 함수의 기초 사용법과 다양한 응용 방법, 그리고 자주 발생하는 문제의 해결법을 단계별로 살펴봅니다.

1. FILTER 함수 기초 사용법

FILTER 함수에는 다음 3개의 인수가 사용됩니다.

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

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

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

1. 수식을 더욱 쉽게 작성하기 위해 범위를 표로 변환합니다. 이때 '머리글 포함'에 반드시 체크해야 합니다.
2. 표로 변환한 뒤, 상단 '표 디자인' 메뉴에서 표 스타일을 '없음'으로 변경합니다.
3. 표의 이름을 'tbl판매내역'으로 변경합니다.
4. 표 우측에 조건을 입력할 범위를 생성한 뒤, 셀의 배경색과 테두리를 적절히 변경합니다.
5. 이후 아래의 FILTER 함수를 입력합니다.
   =FILTER(tbl판매내역,tbl판매내역[날짜]=J2,"결과없음")

   

6. 현재는 조건이 비어 있으므로 "결과없음"이 결과값으로 출력됩니다.
7. 날짜 조건으로 값을 입력합니다. 1/1, 1/2 등 날짜를 조건으로 입력하면 입력한 조건에 따라 데이터가 필터링됩니다.
8. 기존 데이터의 머리글을 복사하여 필터링된 범위에 붙여넣기 합니다.
9. 필터링된 범위의 서식을 변경합니다. '날짜' 열을 전체 선택한 뒤, 셀 서식을 '날짜 형식'으로 변경합니다.
10. '단가'와 '가격' 열을 선택한 뒤, '천 단위 구분 기호'를 표시하고 '우측 정렬'로 서식을 변경합니다.
11. '판매채널'과 '단가' 열을 숨겨 줍니다. (단축키: Ctrl+0)
12. 이제 날짜 조건을 변경하면 입력된 값에 따라 데이터가 실시간으로 필터링됩니다.

3. 다중 조건 필터링 적용하기

FILTER 함수의 두 번째 인수에 여러 개의 조건을 '곱셈'으로 입력하면 다중 조건 필터링을 적용할 수 있습니다. FILTER 함수의 다중 조건 공식은 다음과 같습니다.

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

4. 다중 조건 적용 시 빈칸 인식 문제 해결

다중 조건으로 필터링을 적용할 때 한 가지 문제가 발생합니다. 여러 조건 중 하나라도 빈칸이 되면 다른 조건과 상관없이 결과가 무조건 '결과없음'으로 반환되는 현상입니다.

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

1. 여러 조건 가운데 '제품명' 조건을 빈칸으로 변경합니다.
2. FILTER 함수에서 '제품명'에 해당하는 조건 부분을 마우스로 드래그하여 선택합니다.
3. 키보드 F9키를 눌러 반환되는 값을 확인하면 모든 값이 'FALSE'로 반환되는 것을 확인할 수 있습니다.

이렇게 모든 값이 FALSE로 반환되는 문제는 IF 함수를 함께 사용하여 간단하게 해결할 수 있습니다. 사용되는 공식은 다음과 같습니다.

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

5. 부분 일치로 필터링하기

입력해야 할 조건의 글자 수가 길어 필터링 적용에 어려움이 있다면, 유사 일치 공식을 사용할 수 있습니다. 유사 일치 공식에는 ISNUMBER 함수와 SEARCH 함수를 사용하며, 공식은 다음과 같습니다.

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

6. 3D 검색, 다중 검색 조건으로 필터링하기

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

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

위 3가지 검색 조건으로 날짜를 필터링하려면 어떻게 해야 할까요? FILTER 함수는 조건으로 '배열'을 받습니다. 따라서 조건 안에 또 다른 함수를 사용할 수 있다는 큰 장점이 있습니다. 아래와 같이 '날짜' 범위에 MONTH 함수를 적용해 보겠습니다.

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

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 함수 반환 범위를 동적 차트로 출력하기

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

1. FILTER 함수의 날짜 범위를 동적 범위로 변환합니다. '수식' - '이름 관리자'로 이동하여 [새로 만들기]를 클릭한 뒤, 아래 표와 같이 새로운 동적 범위를 추가합니다.
   

   구분	내용
   이름	rng날짜
   참조 대상	=OFFSET($I$6,,,COUNTA($I$6:$I$1000))

   

2. UNIQUE 함수를 사용하여 동적 범위에서 고유값만 추출합니다. 우측에 새로운 '날짜' 열을 추가한 뒤 아래 수식을 입력합니다.
   차트의 가로축에 들어갈 데이터이므로 반드시 TEXT 함수를 사용하여 텍스트 형식으로 변환해야 합니다.
   =TEXT(UNIQUE(rng날짜),"mm/dd")

   

3. '수량'과 '가격' 열을 추가한 뒤, 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단계 공식은 아래 관련 강의에서 자세히 설명합니다.
   
   

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