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

이번 게시물에서는 엑셀 FILTER 함수를 다른 함수와 조합하여 사용하는 법을 배웁니다. 또, 여러 다양한 필터링 방법도 함께 실습해 봅니다. 엑셀에 추가된 함수들의 최대의 적은 엑셀 버전입니다. FILTER 함수도 엑셀 2021이상 버전이나 365 버전에서만 사용할 수 있습니다.

이전 게시물 : FILTER 함수의 기본 설명 - 완벽가이드1

5. FILTER 함수와 다른 함수 조합하기

FILTER 함수는 다른 함수와 함께 사용할 때 더욱 강력해집니다.

SORT 함수와 조합하기

=SORT(FILTER(A2:G11, C2:C11="전자제품"), 4, -1)

이 수식은 전자제품만 필터링한 다음 가격(4번째 열)을 기준으로 내림차순(-1) 정렬합니다.
(SORT 함수는 데이터를 정렬하는 함수입니다)

A13 셀에 포인터를 놓습니다.
SORT 와 FILTER 함수를 중첩한 수식을 입력합니다.
FILTER 함수를 필터링된 값(전자제품)이 SORT 함수(4열:가격기준, -1:내림차순)로 정렬되어 표시됩니다.

UNIQUE 함수와 조합하기

=UNIQUE(FILTER(C2:C11, F2:F11="서울"))

이 수식은 서울에서 판매된 제품의 고유한 카테고리 목록을 반환합니다.

2번행이 1행과 같은 값을 가졌다면 UNIQUE 함수로 한 번만 표시됩니다.

(UNIQUE 함수는 중복값을 배제하고 고유한 하나의 값만을 취하는 함수입니다)

SUMIFS 대신 FILTER와 SUM 사용하기

=SUM(FILTER(D2:D11*G2:G11, C2:C11="전자제품"))

이 수식은 전자제품 카테고리의 총 매출(가격 × 판매량)을 계산합니다.

D13에 셀 포인터를 놓습니다.
위의 SUM 과 FILTER 함수식 을 입력합니다.
FILTER 함수에서 가격 범위와 판매량 범위 를 곱하여 데이터 범위(반환값)로 사용합니다.
조건은 카테고리 범위가 '전자제품' 인 행입니다.
이렇게 필터링된 값들을 SUM 함수로 합계를 구합니다.
전자제품의 총 합계를 구할 수 있습니다.

AVERAGEIF 대신 FILTER와 AVERAGE 사용하기

=AVERAGE(FILTER(G2:G11, F2:F11="서울"))

이 수식은 서울 지역의 평균 판매량을 계산합니다.

6. 고급 필터링 기법

날짜 기반 필터링

=FILTER(A2:G11, MONTH(E2:E11)=2)

이 수식은 2월(2월 1일~28일)에 판매된 모든 제품을 반환합니다.

A13 셀에 포인터를 둡니다.
판매일자 범위에서 MONTH 함수로 월을 얻고 2 인 행을 반환합니다.
A2:G11 데이터 범위에서 2월인 행들만 추출됩니다.

부분 텍스트 매칭

=FILTER(A2:G11, ISNUMBER(SEARCH("폰", B2:B11)))

이 수식은 제품명에 "폰"이 포함된 모든 제품을 반환합니다.

A13 셀에 포인터를 둡니다.
A2:G11배열 범위에서 제품명에 '폰' 을 포함하는 데이터를 찾습니다.
SEARCH 함수는 '폰' 을 제품명에서 찾고, 포함되었다면 그 위치를 숫자로 반환합니다.
ISNUMBER 함수는 숫자를 포함하고 있으면 1을 반환합니다.
FILTER 함수가 4번 단계로 부분 일치 단어 필터링을 끝냅니다.

상위/하위 항목 필터링

=FILTER(A2:G11, G2:G11>=LARGE(G2:G11,3))

이 수식은 판매량이 상위 3위 안에 드는 제품을 반환합니다.

A13 셀에 포인터 위치.
LARGE 함수로 판매량 3번째로 큰값을 판매량과 비교 상위 3개 추출
2의 조건으로 FILTER 함수가 필터링.

여러 열 기준 필터링

=FILTER(A2:G11, (MOD(ROW(A2:A11),2)=0)*(C2:C11="전자제품"))

이 수식은 짝수 행에 있는 전자제품만 반환합니다.

A13 셀에 포인터.
ROW 함수로 행번호를 얻고 2로 나누어 나머지(MOD)가 없다면 짝수행.
데이터 배열 범위에서 짝수행에 있고 '전자제품' 인 행만을 필터링.

7. FILTER 함수 활용 팁

동적 기준으로 필터링

=FILTER(A2:G11, F2:F11=H1)

여기서 H1에 필터링할 지역을 입력하면, 해당 지역의 데이터만 표시됩니다.

이전 FILTER 함수 사용과 동일 하고, 다른점은 조건비교값을 H1 으로 한다.
H1 셀의 값이 다른 지역으로 바뀌면 A13 셀의 수식도 갱신되어 필터링된 결과가 달라진다.

OR 조건을 간결하게 표현

=FILTER(A2:G11, MMULT(--((F2:F11={"대구","인천","부산"})),{1;1;1})>0)

이 수식은 대구, 인천, 부산 중 어느 한 지역과 일치하는 모든 행을 반환합니다.

핵심은 MMULT 함수, 이 함수는 행렬곱을 구하는 함수입니다.
지역 범위와 대구,인천,부산 을 비교하여 그 결과를 논리값으로 배열화 합니다.
이 값에 - 한번 사용해 논리값을 숫자로 바꿉니다. (음양이 뒤집힙니다), 다시한번 - 을 붙여 원래의 숫자값으로 반환됩니다.
대구,인천,부산에 한해서 MMULT 결과는 1일 것입니다.
결국, OR 처리와 같습니다.

동적 범위와 함께 사용

=FILTER(INDIRECT("A2:G"&COUNTA(B:B)), INDIRECT("C2:C"&COUNTA(B:B))="전자제품")

이 수식은 데이터 크기가 변경되더라도 자동으로 전체 범위를 고려합니다.

필터링 할 원본 범위가 증가/감소 할 수 있습니다.
이에 12번 셀에 데이터가 추가 되더라도 INDIRECT 함수가 적용되어 있고,
COUNTA 함수로 행의 수를 계산하여 종료행을 가변화 합니다.

📌 NOTE: 위에서 보기 편하게 수식을 A16 셀에 입력했지만, 이렇게 되면 처음 데이터 출력뒤 B 열에 데이터가 변경되어 순환 참조가 발생할 수 있습니다. 실제 사용시는 다른 열에서 작업해 주세요.

계산된 열 추가

=LET(
    filtered, FILTER(A2:G11, C2:C11="전자제품"),
    result, HSTACK(filtered, INDEX(filtered,,4) * INDEX(filtered,,7)),
    result
)

이 수식은 필터링된 결과에 총 매출액(가격 × 판매량) 열을 추가합니다.

'filtered' 라는 변수에 전자제품 필터링 한 값을 LET 함수로 저장합니다.
HSTACK 함수로 'filtered' 변수와 INDEX 함수로 얻은 4열과 7열의 곱의 결과를 수평으로 추가합니다.
2의 결과를 result 변수에 저장하고 최종 반환합니다.
1. LET: 계산 결과에 이름을 붙여서 가독성과 성능 향상 (변수 생성 함수)
2. INDEX(filtered,,4): 필터된 데이터의 4번째 열 (D열)
3. INDEX(filtered,,7): 필터된 데이터의 7번째 열 (G열)
4. HSTACK(...): 원본 필터된 데이터 오른쪽에 계산된 열 추가

8. 결론

Excel의 FILTER 함수는 데이터 분석과 보고서 작성을 위한 매우 유용한 도구입니다. 동적 배열 함수의 특성을 활용하면 복잡한 조건으로 데이터를 필터링하고, 이를 다른 함수와 조합하여 강력한 분석을 수행할 수 있습니다.

FILTER 함수를 사용할 때 주의할 점은 다음과 같습니다:

결과를 표시할 충분한 빈 셀 공간이 필요합니다(#SPILL! 오류 방지).
배열과 조건의 크기가 일치해야 합니다.
조건을 만족하는 데이터가 없을 때를 대비해 빈_값 매개변수를 사용하는 것이 좋습니다.
복잡한 조건을 구현할 때는 논리 연산자(* 및 +)를 올바르게 사용하세요.
다른 동적 배열 함수(SORT, UNIQUE 등)와 함께 사용하면 더욱 강력한 결과를 얻을 수 있습니다.

FILTER 함수는 엑셀 2021 이상, Microsoft 365 사용자만 사용할 수 있지만, 그만큼 기존의 필터링 방식보다 더 효율적이고 유연한 데이터 처리가 가능합니다. 이 함수를 마스터하면 복잡한 IF, SUMIFS, COUNTIFS 함수 조합을 단순화하고, 더 직관적인 데이터 분석이 가능해집니다.

Excel을 더 효율적으로 활용하고 싶다면, FILTER 함수와 함께 SORT, UNIQUE, SEQUENCE, XLOOKUP과 같은 다른 동적 배열 함수도 함께 살펴보시기 바랍니다.

앞으로 천천히 하나씩 함께 배워 봅니다. 행복한 Excel 작업 되세요! 📊

작성자 : 마늘빵

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