엑셀 FILTER 함수 제대로 배워보자

실무에 너무도 필요하고 유용한 엑셀 FILTER 함수를 배워봅니다. 자세한 설명이 들어가 2회 연속 게시합니다.
이번 게시물에서는 기본개념과 기초사용범, 사용예제, 오류시 대처법 등을 다룹니다.

1. FILTER 함수 기본 개념

Excel의 FILTER 함수는 Microsoft 365(Excel 2021 이상 버전)에서 사용할 수 있는 강력한 동적 배열 함수입니다. 이 함수는 지정한 조건에 따라 데이터 범위에서 값을 필터링하여 결과를 반환합니다.

구문

=FILTER(배열, 포함_조건, [빈_값])

매개변수( 인수 )

배열: 필터링할 셀 범위 또는 배열입니다.
포함_조건: TRUE/FALSE 값을 반환하는 조건 배열입니다. ''배열''과 같은 크기여야 합니다.
빈_값: (선택 인수) 조건을 만족하는 값이 없을 때 반환할 값입니다.

반환값( 결과 )

FILTER 함수는 포함_조건에서 TRUE로 평가된 배열의 모든 값을 포함하는 동적 배열을 반환합니다.

📌 NOTE: FILTER 함수는 동적 배열 함수이므로, 결과가 여러 셀로 자동 확장(스필)됩니다. 결과를 담기에 충분한 빈 셀이 없으면 #SPILL! 오류가 발생합니다.

2. FILTER 함수 사용 예제

기본 사용법

아래 예제와 같이 특정 조건에 맞는 데이터를 필터링할 수 있습니다:

=FILTER(A2:D100, B2:B100="서울")

이 수식은 A2:D100 범위에서 B열의 값이 "서울"인 행만 반환합니다.

AND 조건 사용하기

여러 조건을 하나로 결합하려면 논리 연산자( * 또는 + )를 사용합니다:

=FILTER(A2:D100, (B2:B100="서울") * (C2:C100>50000))

이 수식은 B열 값이 "서울"이고 C열 값이 50000보다 큰 행만 반환합니다.

OR 조건 사용하기

=FILTER(A2:D100, (B2:B100="서울") + (B2:B100="부산"))

이 수식은 B열 값이 "서울" 또는 "부산"인 행을 반환합니다.

빈_값 매개변수 사용하기

=FILTER(A2:D100, B2:B100="대전", "조건에 맞는 데이터가 없습니다")

이 수식은 조건에 맞는 행이 없을 경우 "조건에 맞는 데이터가 없습니다" 를 반환합니다.

3. 실전 예제 - 판매 데이터 분석

아래는 판매 데이터를 분석하는 실제 예제입니다.

FILTER 함수 사용예제.xlsx

0.01MB

예제 1: 특정 카테고리 필터링

=FILTER(A2:G11, C2:C11="전자제품")

이 수식은 카테고리가 "전자제품"인 모든 행을 반환합니다.

A13 셀에 포인터를 놓는다.
FILTER 함수 위 수식을 입력한다.
A12:G11의 데이터범위(배열) 에서 C2:C11 이 '전자제품' 인 모든 데이터가 반환된다.

예제 2: 가격 범위로 필터링

=FILTER(A2:G11, (D2:D11>=500000) * (D2:D11<=1000000))

이 수식은 가격이 500,000원 이상 1,000,000원 이하인 제품을 반환합니다.

A13 셀에 포인터를 놓는다.
가격 범위가 50만원 이상인지 조건1, 가격 범위가 100만원 이하인지 조건2, 두 조건을 * 로 연결하여 AND 처리 한다.
결과로는 50만원 이상 100만원 이하의 가격 범위에 있는 행들이 필터링되어 반환된다.

예제 3: 여러 조건 필터링

=FILTER(A2:G11, (B2:B11=B2) + (F2:F11=F2))

이 수식은 노트북 이거나 서울에서 판매된 데이터만을 반환합니다.

A13 셀에 포인터를 놓는다.
데이터 범위로 A2:G11, 조건1로 제품명 범위가 B2(노트북) , 조건2로 지역 범위가 F2(서울) , 조건1과 조건2를 + 로 연결하여 OR 처리
두 조건중 하나라도 만족하는 데이터를 반환한다.

예제 4: 판매량 제품 필터링

=FILTER(A2:G11, G2:G11>10, "해당 판매량의 제품 없음")

이 수식은 판매량이 10개를 초과하는 제품만 반환합니다. 만일 반환값이 없다면 "해당 판매량의 제품 없음" 을 표시한다.

예제 5: 지역 및 날짜로 필터링

=FILTER(A2:G11, (F2:F11="서울") * (E2:E11>=E6))

이 수식은 E2:E11 (판매일자) 가 E6 (2023/02/10) 이후이고 서울에서 판매된 모든 제품을 반환합니다.

4. FILTER 함수 사용 시 주의사항 및 오류 정리

1. #SPILL! 오류

// 아래 셀에 데이터가 있으면 #SPILL! 오류 발생
=FILTER(A2:D11, B2:B11="서울")

📌 NOTE: FILTER 함수는 한 셀에 수식을 입력하고, 결과가 동적 배열로 반환됩니다.
FILTER 함수의 결과가 확장될 공간에 다른 데이터가 있으면 #SPILL! 오류가 발생합니다. 결과를 표시할 충분한 빈 셀 공간이 필요합니다.

2. 크기 불일치 오류

// 배열과 조건의 크기가 다름
=FILTER(A2:D11, B2:B10="서울")

📌 NOTE: ''배열'' 과 ''포함_조건'' 의 크기가 일치해야 합니다. 위 예제에서는 배열은 10행이지만 조건은 9행이므로 오류가 발생합니다.

3. 빈 값 반환 처리

=FILTER(A2:D11, B2:B11="존재하지 않는 값")

이 경우 조건을 만족하는 값이 없어 #CALC! 오류가 발생할 수 있습니다.

// 빈_값 매개변수 사용하여 오류 방지
=FILTER(A2:D11, B2:B11="존재하지 않는 값", "데이터 없음")

📌 NOTE: 조건을 만족하는 값이 없을 때 오류 대신 사용자 지정 메시지를 표시하려면 빈_값 매개변수를 사용하세요.

4. 동적 조건 사용 시 주의점

=FILTER(A2:D11, B2:B11=E1)

📌 NOTE: 셀 참조(E1)를 조건으로 사용할 때, E1이 비어 있으면 모든 행이 반환될 수 있습니다. 조건을 더 구체적으로 작성하는 것이 좋습니다.

5. 계산 오류

=FILTER(A2:D11, D2:D11/0>100)

📌 NOTE: 조건에서 0으로 나누기와 같은 계산 오류가 발생하면 FILTER 함수도 오류를 반환합니다. 계산 오류를 방지하기 위해 IFERROR 함수를 사용할 수 있습니다.

엑셀 FILTER 함수는 2021 버전 부터 사용 가능 하지만 사용해 본 소감은 대만족 입니다. 실무에서 다양한 활용법이 생각날 유용한 함수 입니다.

다음 게시물에서 FILTER 함수와 다른 함수를 조합하여 사용하는 방법과 여러 필터링 방법을 게시합니다. 활용해 보세요.

관련 게시물 :
XLOOKUP 함수 : https://secstart.tistory.com/1077

작성자 : 마늘빵

엑셀 FILTER 함수 제대로 배워보자 - 완벽 가이드1