엑셀 입력한 조건으로 데이터 필터링 해주는 FILTER 함수 사용방법

엑셀의 원본 데이터에서 요약 테이블을 만들거나 필요한 데이터만 추출하는 작업을 할 때가 많다.

목적에 따라 다양한 함수가 필요할텐데 이번에는 그 중에서도 입력한 조건을 바탕으로 데이터를 필터링 해주는,

FILTER 함수에 대한 사용 방법과 쉬운 예제를 공유해보고자 한다.

FILTER 함수는 AND, OR 함수나 IF 함수, SORT 함수 등 여러 다양한 함수와 병행하여 활용할 수 있기 때문에 활용 범위가 무궁무진하다.

 

[목차여기]

 

엑셀 FILTER 함수

엑셀 FILTER 함수는 입력한 조건을 기반으로 데이터를 필터링 해주는 함수이다.

FILTER 함수는 엑셀 2021 또는 M365 사용자에게만 제공되는 함수로 비교적 최근 업데이트 된 함수이다.

예전 버전을 사용하고 있는 사용자들은 FILTER 함수를 쓸 수 없으니 참고할 것! 

 

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

 

• 범위 : 필터링 할 범위, 배열
• 조건 : 범위 내에서 필터링 할 조건
• 결과없음 반환값 : 범위 내 조건에 해당하는 데이터가 없을때 반환할 값. 기본값은 #CALC! 오류를 반환 

 

참고로 FILTER 함수를 적용하는 셀을 기준으로 범위로 지정한 넓이와 높이, 즉 행과 열이 확보되어 있어야 한다.

조건에 따라 범위에 해당하는 모든 데이터를 반환해주기 때문에 행과 열이 확보되지 않는다면 #분산! 이라는 오류가 발생한다.   

 

엑셀 FILTER 함수 예제

날짜별로 내가 마신 커피 메뉴와 지역, 가격을 정리한 데이터가 있다고 가정하자.

내가 지정한 조건을 기준으로 해당 조건에 충족되는 데이터만 끌고 오고 싶다.

이럴 때 내가 입력한 조건을 기반으로 데이터를 필터링 해주는 FILTER 함수를 활용할 수 있다.

 

기본 예제 

엑셀 FILTER 함수 예제

 

가장 왼쪽의 데이터가 원본 데이터이고 F 열에 내가 필터링하고자 하는 데이터란을 따로 확보해두었다.

조건은 굳이 저렇게 표로 만들지 않아도 되지만 이해를 돕기 위해 만들어두었다.

 

첫 번째 조건은 메뉴가 "아이스아메리카노" 인 데이터만 가져오는 것이다.

데이터를 가져올 범위는 원본 데이터 전체이니 A6:D17 까지를 범위로 선택한다.

 

=FILTER(A6:D17

 

그 다음은 조건에 해당한다.

메뉴는 B 열에 있고 B 열에 있는 데이터 중 "아이스아메리카노"에 해당하는 값을 가져오는 것이 나의 조건이다.

B 열 전체 범위 중 값이 "아이스아메리카노" 인 것을 조건에 작성한다.

 

=FILTER(A6:D17, B6:B17=G2

=FILTER(A6:D17, B6:B17="아이스아메리카노" 

 

조건 데이터를 따로 만들어놓아서 아이스아메리카노라고 되어있는 G2 셀을 참조하도록 했지만,

따로 조건을 만들어놓지 않고 "아이스아메리카노"라는 값으로 바로 명기해도 전혀 문제 없다.

마지막으로는 조건에 해당되는 데이터가 없을 경우 어떤 값을 반환할 것인지를 정해야 한다.

없으면 그냥 빈칸으로 나오도록 쌍따옴표만 넣어주었다.

선택항목으로 값을 따로 넣지 않아도 함수는 실행되며, 기본값은 #CALC! 오류가 반환되도록 되어있다.

 

=FILTER(A6:D17, B6:B17=G2, "") 

 

 

다른 함수와 함께 활용 예제

조건을 여러개를 두고 AND 또는 OR 함수를 활용해서 넣거나,

그 외 조건에 해당하는 란에 여러 다른 함수를 함께 활용하여 응용 문제를 풀 수도 있다.

아니면 FILTER 함수를 통해 나온 데이터를 SORT 함수를 추가로 활용하여 다시 정렬하여 표출되도록 할 수도 있다.

이번 활용 예제에서는 LEFT 함수를 조건에 같이 활용하여 써보도록 하겠다.

 

지역을 보면 서울신사, 경기수원 등 큰 지역과 세부지역이 같이 표현되어 있다.

나는 서울에서 먹은 커피들에 대한 데이터만 추출하고 싶다면 어떻게 해야할까?

조건에 일일히 서울신사, 서울양재, 서울잠실 등 다 포함해서 하는건 무리가 있을 것이고..

앞에 두 글자인 서울만 따서 가져오면 한 번에 해결할 수 있다.

FILTER 함수 LEFT 함수와 함께 활용

 

LEFT 함수는 문자열의 왼쪽에서부터 원하는 개수만큼 문자를 추출하는 함수이다.

지역 앞에 서울, 경기, 대구, 부산 등 큰 지역이 모두 두 글자로 되어 있으니,

LEFT 함수로 왼쪽에서부터 2개 문자만 추출하여 서울 지역에 해당하는 데이터들을 모두 가져오도록 할 수 있다.

 

=FILTER(A6:D17, LEFT(C6:C17, 2) = G3, "")     

 

LEFT 함수를 간략히 소개하면 첫 번째 인수가 가져올 문자 또는 배열이 되고,

그 뒤 두 번째 인수가 왼쪽에서부터 몇 개의 문자를 가져올 것인지이다.

지역에 해당하는 C6:C17에서 왼쪽에서부터 2개 문자를 가져오고,

이 문자가 G3 셀에 해당하는 "서울"과 일치하는 데이터만 가져오도록 조건을 만든 것이다.

서울신사, 서울양재, 서울잠실 등 서울에 해당하는 모든 데이터들이 추출되었다.

 

피벗테이블을 활용해서도 조건에 따른 행과 열, 값들을 필터링하고 평균, 최소값, 최대값 등 다양한 기능을 부가하여 데이터를 추출할 수도 있다.

하지만 FILTER 함수를 활용해서 간단하게 원하는 조건의 데이터만 필터링 해올 수 있으니 조금 더 간편하게 활용할 수 있다는 장점이 있다.

데이터를 요약하거나 여러 뷰로 데이터를 확인하고자 할 때 FILTER 함수를 써보도록 하자!  

 

함께보면 좋은 글

엑셀 여러 조건을 함께 고려하는 AND, OR 논리 함수 사용방법 (IF 함수와 같이 활용하기)

엑셀 원하는 텍스트, 문자 바꿔주는 SUBSTITUTE 함수 활용 방법과 REPLACE 함수와의 차이점