마이크로소프트 엑셀의 FILTER 함수는 반드시 익혀 두어야 할 가장 중요한 함수 중 하나입니다. 이 함수 없이는 필요한 데이터를 찾는 데 상당한 어려움을 겪게 됩니다. 지금부터 엑셀에서 FILTER를 활용하는 방법을 단계별로 살펴보겠습니다.
참고로 FILTER 함수가 엑셀에서 데이터를 필터링하는 유일한 방법은 아닙니다. 자동 필터(Auto Filter)나 고급 필터(Advanced Filter) 같은 도구로도 동일한 작업을 수행할 수 있습니다. 다만 몇 가지 중요한 차이점이 있는데, 이 가이드에서 함께 확인해 보겠습니다.
FILTER 함수란 무엇인가?
함수, 즉 엑셀 수식은 엑셀의 기본이자 핵심입니다. 대규모 데이터셋의 평균을 구하거나 정규분포 곡선 그래프를 만드는 등의 작업을 가능하게 해주죠. 각 함수에는 고유한 구문이 있으며, 보통 엑셀에 함수 이름만 입력하면 해당 구문을 확인할 수 있습니다.
엑셀 FILTER 함수는 이름 그대로 지정된 범위의 값을 특정 조건에 따라 '필터링'하는 데 사용됩니다. 범위와 조건을 모두 함수 인수로 입력하기 때문에 매우 유연하게 커스터마이징할 수 있다는 것이 특징입니다.
적절한 매개변수만 설정하면 스프레드시트 전체를 일일이 훑어보며 일치하는 항목을 찾을 필요 없이 원하는 정보를 정확하게 추출할 수 있습니다. 또한 결과가 셀에 출력되기 때문에 다른 함수와 연결해 추가 계산을 수행하거나 그래프로 시각화하는 것도 가능합니다.
고급 필터보다 FILTER 함수를 선호하는 이유
대부분의 엑셀 초보자는 함수 구문을 배우는 대신 엑셀에 내장된 데이터 필터링 도구를 사용합니다. 자동 필터는 메뉴 기반 인터페이스로 열을 제외하고 필터 조건을 설정할 수 있어 가장 간편합니다. 여러 조건을 적용해 복잡한 필터링 체계를 구현할 수 있는 고급 필터도 있죠.
그렇다면 굳이 FILTER 함수를 사용할 이유가 있을까요?
엑셀 함수를 다른 도구나 프로그램으로 수동 작업하는 것보다 활용할 때의 가장 큰 장점은 바로 '동적'이라는 점입니다. 자동 필터나 고급 필터는 원본 데이터가 변경되어도 결과가 갱신되지 않는 일회성 결과를 제공합니다. 반면 FILTER 함수는 데이터가 변경될 때마다 결과를 자동으로 업데이트합니다.
FILTER 함수 구문
FILTER 수식의 구문은 매우 간단합니다.
=FILTER(array, include, [if_empty])
array(배열)는 스프레드시트의 직사각형 영역으로, 왼쪽 위 셀과 오른쪽 아래 셀 사이의 범위를 지정해 표현합니다. 예를 들어 A3:E10은 A열부터 E열까지, 3행부터 10행까지로 구성된 배열입니다.
두 번째 매개변수는 필터에 사용할 조건으로, 좀 더 기술적으로 표현하면 부울(Boolean) 배열입니다. 셀 범위(주로 한 열)의 값을 평가하여 TRUE 또는 FALSE를 반환하는 식의 형태로 입력합니다. 예를 들어 A3:A10="Pass"는 셀 값이 지정된 문자열과 일치할 때 TRUE를 반환합니다.
마지막으로 조건에 맞는 행이 하나도 없을 때 FILTER 함수가 반환할 값을 지정할 수 있습니다. "No Records Found"처럼 간단한 문자열이면 충분합니다.
관련 글: 표본 데이터가 더 큰 데이터셋에 얼마나 정확히 들어맞는지 확인하고 싶으신가요? 엑셀에서 표준 오차를 계산하는 방법에 대한 가이드도 참고해 보세요.
FILTER 함수의 구문을 이해했으니, 이제 실제 스프레드시트에서 FILTER를 사용하는 방법을 살펴보겠습니다.
이번 데모에서 사용할 샘플 데이터는 A2부터 F11까지의 배열로, 10명 학생의 생물학 점수와 정규분포 값을 정리한 표입니다.
시험 점수(D열)를 기준으로 30점 미만인 학생만 걸러내는 함수를 작성해 보겠습니다. 구문은 다음과 같습니다.
=FILTER(A2:F11,D2:D11<30,"No Matches Found")
필터링된 결과는 배열의 하위 집합이므로, 함수를 입력하는 셀 주변에 결과가 표시될 충분한 공간이 있어야 합니다. 여기서는 원본 표 아래에 함수를 입력했습니다.
예상대로 결과가 나왔습니다. 30점 미만인 모든 항목이 선택되어 원본과 동일한 표 형식으로 표시됩니다.
조건은 하나로 제한되지 않습니다. AND 연산자(*)를 사용해 여러 식을 하나의 매개변수로 연결하면 더욱 복잡한 필터를 만들 수 있습니다.
이번에는 30점 초과 70점 미만에 해당하는 항목만 반환하는 함수를 만들어 보겠습니다. 구문과 결과는 다음과 같습니다.
=FILTER(A2:F11,(D2:D11>30)*(D2:D11<70),"No Matches Found")
양쪽 조건 중 하나만 충족해도 되는 경우에는 OR 연산자(+)를 사용할 수 있습니다. 이 경우 포함된 조건 중 하나라도 TRUE로 평가되면 필터에 일치합니다.
다음 수식에서는 30점 미만 또는 70점 초과 결과를 필터링하여 이상치를 찾아봅니다.
=FILTER(A2:F11,(D2:D11<30)+(D2:D11>70),"No Records Found")
마지막으로, FILTER 함수가 아무것도 찾지 못했을 때 단순한 문자열 하나를 반환하는 대신 열별로 값을 지정해 출력 형식을 항상 일관되게 유지할 수도 있습니다.
먼저 거짓임이 분명한 조건을 넣어 기본 상태에서 어떻게 표시되는지 확인해 보겠습니다.
=FILTER(A2:F11,D2:D11>90,"No Matches Found")
결과가 문자열 하나만 표시되어 기대했던 표 형식과 맞지 않습니다. 평소에는 큰 문제가 아니지만, 결과(또는 그중 일부 값)를 다른 수식에 넣어 사용하려면 불편할 수 있습니다.
그렇다면 배열 항목과 같은 형식으로 기본값을 지정해 보겠습니다. 중괄호 안에 쉼표로 구분된 값을 나열하면 됩니다.
=FILTER(A2:F11,D2:D11>90,{"No Record","No Record","No Record",0})
이제 나머지 스프레드시트 형식과 일관된, 훨씬 보기 좋은 결과를 얻을 수 있습니다.
FILTER 함수, 사용할 가치가 있을까?
MS 엑셀을 단순히 기록 관리 용도로만 사용하고 복잡한 계산을 할 계획이 없더라도, FILTER 함수는 꼭 익혀 두면 좋은 몇 안 되는 함수 중 하나입니다.
통합 문서가 어느 정도 규모에 이르면 데이터를 수동으로 찾는 일은 고통스러워집니다. 자동 필터와 고급 필터 도구도 유용하지만, 함수를 사용하면 결과가 자동으로 업데이트되고 다른 함수와 손쉽게 결합할 수 있어 장기적으로 훨씬 더 편리합니다.