엑셀 필터 혁명의 시작! FILTER 함수 역대급 실무 활용법
엑셀 FILTER 완벽 정리 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 FILTER 함수 자동화, 초보자 맞춤 15분 완벽 정리예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
FILTER 함수 기초 사용법
회사에서 엑셀로 보고서를 작성할 때, 반복되는 '필터' 작업은 업무 시간의 상당 부분을 차지합니다. 물론 엑셀의 '고급 필터'나 'VBA'를 사용해 일부 자동화할 수 있지만, 초보자가 다가가기엔 내용이 어려워 실무에 바로 적용하기엔 어려움이 있는데요.
FILTER 함수로 필터링 업무를 100% 자동화해보세요!✨ 오늘 알아보는 FILTER 함수를 활용하면, 엑셀 2021 이후 버전에서 누구나 쉽게 필터링 자동화 보고서를 만들 수 있습니다. 오늘은 3가지 상황별 예제를 통해, 조건을 입력하면 실시간으로 데이터가 필터링되는 자동화 보고서를 만드는 방법을 알아보겠습니다.
- 먼저 FILTER 함수의 기초 사용법을 알아보겠습니다. FILTER 함수는 범위에서 조건을 만족하는 데이터를 필터링하는 함수로 아래와 같이 사용합니다.
= FILTER ( 범위, 조건, [결과없음출력값] )
' 범위에서 조건을 만족하는 값을 필터링합니다.
- 예제파일의 [FILTER] 시트로 이동한 후, 왼쪽에 있는 데이터에서 제조사가 '삼성전자'인 값만 필터링해보겠습니다. 결과를 출력할 시작셀인 F9셀을 선택한 후, 다음과 같이 FILTER 함수를 작성합니다.
=FILTER(B9:D18,B9:B18=G7)
FILTER 함수를 작성합니다. - 함수를 실행하면 제조사가 삼성전자인 값만 필터링됩니다.
제조사가 삼성전자인 값이 필터링됩니다. - 이제 G7셀의 값을 변경해 특정 제조사의 데이터를 실시간으로 필터링 할 수 있습니다.
조건을 변경하면 데이터가 실시간으로 갱신됩니다.
유형1: 다중 조건 필터링 (배열 연산)
실무에서는 하나의 조건보다는 여러개의 조건을 조합해 필터를 적용할 때가 많습니다. 예를 들어 "거래처가 A사이면서, 품목이 B인 데이터" 등 여러 조건을 동시에 비교해야 하는 상황이 종종 발생하는데요. 그럴 때, 배열 연산을 사용하면 실무에서 발생하는 모든 조건을 완벽하게 처리할 수 있습니다. 아래 두가지 규칙은 '꼭' 기억하세요!
A. 엑셀은 TRUE = 1, FALSE = 0 으로 계산합니다.
B. 모든 조건을 만족하는 경우(AND)는 곱셈, 둘 중 하나라도 만족하는 경우(OR)는 덧셈으로 계산합니다.- 다중 조건 필터링의 핵심 이론인 '배열 연산'을 알아보겠습니다. [다중조건FILTER] 시트에서 E열과 F열의 조건1, 조건2를 모두 만족하는 경우를 비교해보겠습니다. E10셀과 F10셀에 아래와 같이 수식을 입력해 제조사와 구분 범위의 값이 "삼성전자"와 "DRAM"인 경우를 비교합니다.
· E10셀 : =B10:B19=E8
· F10셀 : =C10:C19=F8
조건을 비교한 결과 배열을 출력합니다. - 계산할 결과는 "제조사 = 삼성전자"와 "구분 = DRAM"을 모두 만족하는 경우(AND)이므로 두 조건을 곱셈으로 계산합니다. G10셀에 다음과 같이 수식을 작성하면 두 조건이 모두 TRUE인 경우 1로 계산됩니다.
=E10#*F10#
두 배열을 곱하면 모든 조건을 만족 시 1이 반환됩니다. 오빠두Tip : 분산 배열(#)을 활용한 배열 계산의 기초 이론은 진짜쓰는 실무엑셀 입문 강의를 참고하세요!👇
- 배열 연산 원리를 FILTER 함수 조건에 적용하면 여러 조건을 동시에 만족하는 값을 필터링할 수 있습니다. 오른쪽 '실전 활용' 예제에서 O10셀에 다음과 같이 수식을 작성합니다.
=FILTER(K10:M20,(K10:K20=O8)*(L10:L20=P8))
두 조건을 모두 만족할 경우 데이터를 필터링합니다. - 수식을 실행하면 아래 그림과 같이 제조사와 구분을 동시에 만족하는 데이터가 필터링됩니다.
다중조건 필터링 보고서가 완성됩니다.
유형2: 여러 항목 필터링 (MATCH 활용)
이번에는 특정 필드에서 여러 항목을 동시에 필터링하는 경우를 알아보겠습니다. 예를 들어 제조사가 "삼성전자, SK하이닉스, 샌디스크" 인 경우를 봐야아 할 경우, 여러 항목 필터링 공식을 활용합니다.
- 여러 항목 필터링의 조건에는 MATCH 함수가 사용됩니다. MATCH 함수는 원래 '범위에서 특정 값이 위치하는 순번'을 찾을 때 사용합니다. 예를 들어 [MATCH] 시트에서 "황호성"이라는 값이 이름 범위인 C8:C16 에서 몇 번째 위치하는지 검색할 때 사용합니다. G8셀에 다음과 같이 MATCH 함수를 작성하면, 황호성의 순번으로 6이 반환됩니다.
범위에서 값이 위치한 순번을 반환합니다. 오빠두Tip : MATCH 함수는 INDEX/MATCH 검색 공식에도 중요하게 사용됩니다. INDEX/MATCH 공식 사용법은 아래 입문 강의를 참고하세요!👇
- 이 MATCH 함수의 '값'과 '범위'를 치환해서 역으로 사용하면, 여러 항목의 포함 여부를 한 번에 확인할 수 있습니다. [OR검색MATCH] 시트에서 검색결과인 F8셀에 다음과 같이 MATCH 함수를 입력하면 "제조사1" 범위의 값이 제조사2 목록에 있을 경우 숫자를 출력하고, 그렇지 않을 경우 오류를 반환합니다.
=MATCH(B8:B16,D8:D13,0)
범위의 값이 목록에 있을 경우 숫자를 반환합니다. - 따라서 위 MATCH 함수의 결과가 숫자라면 목록에 존재하는 것이므로, ISNUMBER 함수로 묶어서 값이 숫자인 경우 TRUE를 반환하도록 공식을 완성합니다.
=ISNUMBER(MATCH(B8:B16,D8:D13,0))
목록에 값이 있으면 TRUE, 없으면 FALSE가 반환됩니다. - 여러 항목의 포함 여부를 확인하는 "ISNUMBER+MATCH" 조건을 FILTER 함수에 적용하면 여러 항목 필터링 공식이 완성됩니다. [여러항목FILTER] 시트에서 결과를 출력할 시작셀인 H9셀에 아래와 같이 FILTER 함수를 작성하면 '제조사' 목록에 포함된 모든 값이 한 번에 필터링됩니다.
=FILTER(B9:D18,ISNUMBER(MATCH(B9:B18,F9:F13,0)))
여러 항목 필터링 보고서가 완성됩니다.
유형3: 단어 포함 필터링 (ISNUMBER+SEARCH)
지금까지 '다중 조건'과 '여러 항목' 필터링 공식을 알아봤습니다. 이제 마지막으로 범위에서 특정 단어를 포함하는 모든 데이터를 깔끔하게 필터링하는 '단어 포함' 필터링 공식을 알아보겠습니다. 단어 포함 공식은 쉽게 외우는 방법이 있는데요. 단어가 있는지 없는지 검색하는 "있어?(ISNUMBER+SEARCH)" 공식으로 기억해보세요!👍
- 먼저 특정 단어의 포함 여부를 확인하는 ISNUMBER+SEARCH 공식의 기초 사용법을 알아보겠습니다. 예제파일 마지막 시트인 [단어포함검색공식] 에서 포함여부의 시작셀은 C8셀에 다음과 같이 ISNUMBER+SEARCH 공식을 작성합니다.
=ISNUMBER(SEARCH(H7,B8:B11))
단어 포함 검색 공식을 입력합니다. - 공식을 입력하면 아래 그림과 같이 H7셀에 작성된 단어가 포함된 경우 TRUE를 반환하는 조건이 출력됩니다.
단어를 포함한 경우 TRUE를 반환합니다. - 이제 단어 포함 공식을 FILTER 함수에 활용해보겠습니다. 아래에 있는 표에서 필터링 결과를 출력할 시작셀인 G15셀에 다음과 같이 FILTER 함수를 작성합니다.
=FILTER(B15:D27,ISNUMBER(SEARCH(I13,D15:D27)))
단어 포함 여부를 필터링하는 FILTER 함수를 작성합니다. - 수식을 입력하면 아래 그림과 같이 '제품명' 범위에서 검색 단어가 포함된 값이 필터링됩니다.
단어 포함 필터링 보고서가 완성됩니다. - 한 단계 더 나아가, 단어 포함 여부를 검색할 조건 범위로 모든 값을 합친 더미 데이터 필드를 입력하면 모든 데이터에서 단어 포함 여부를 검색하는 실시간 필터링 보고서를 만들 수 있습니다. 시트 위에 [+] 버튼을 클릭하면 아래 그림과 같이 E열에 더미데이터 필드가 표시됩니다.
+ 버튼을 클릭해 더미데이터 필드를 펼칩니다. - 더미 데이터 필드의 시작셀에 다음과 같이 CONCAT 함수를 작성한 후, 자동채우기 하면 각 행별로 '제조사, 구분, 제품명'이 한 문장으로 합쳐진 더미데이터 필드가 완성됩니다.
CONCAT 함수로 범위의 값을 한 문장으로 합칩니다. - G15셀에 작성했던 FILTER 함수에서 단어 포함 여부의 검색 범위를 더미 데이터가 작성된 E15:E27로 바꾼 후 입력하면, 모든 범위를 대상으로 단어 포함 여부를 확인하는 실시간 필터링 보고서가 완성됩니다.
더미 필드를 기준으로 FILTER 함수를 작성하면 모든 범위를 대상으로 단어 포함 공식이 동작합니다. 오빠두Tip : 엑셀 2019 이전 버전에서도 사용 가능한 실시간 필터링 보고서 제작 방법은 이전 영상 강의를 참고하세요!👇
- 이 외에도 엑셀 2024 이후 버전에 새롭게 추가된 BYROW 함수와 TOROW 함수를 사용하면 다중 조건 + 여러 항목 + 단어 포함을 동시에 적용할 수 있습니다. 최신 버전에서 실시간 필터링 공식을 작성하는 과정은 토요 라이브 전체 영상을 참고하세요.
- 먼저 FILTER 함수의 기초 사용법을 알아보겠습니다. FILTER 함수는 범위에서 조건을 만족하는 데이터를 필터링하는 함수로 아래와 같이 사용합니다.
