엑셀 FILTER 함수로 다중 조건과 검색 목록을 자동 필터링하는 방법은?

TL;DR

FILTER 함수에 데이터 범위와 조건을 지정하면 입력값이 바뀔 때 결과도 실시간으로 갱신됩니다. 여러 조건을 모두 만족시키려면 조건식을 곱하고, 하나라도 만족시키려면 더하며, 목록 일치는 ISNUMBER와 MATCH, 단어 포함 검색은 ISNUMBER와 SEARCH를 결합합니다. 모든 필드를 검색하려면 CONCAT으로 행 데이터를 합친 보조 열을 검색 범위로 사용합니다.

Transcript

여러분 안녕하세요. 오빠드 엑셀 전진관입니다. 자, 직장애이 엑셀에서 가장 많이 쓰는 기능 두 개를 꼽으라면 아마도 피벗 테이블과 필터 기능이 될 것 같습니다. 어, 그중에서도이 필터의 경우에는 필터 조건에 따라서 그 난이도가 천차 만별로 달라지는데요. 자, 예를 들어 보겠습니다.이 데이터에서 삼성전자와 하이닉스만 필터를 건다고 하면 사실 필터에서 클릭 몇 번으로 간단하게 해결할 수 있습니다. 하지만 오른쪽에 있는 것처럼이 제품 코드 목록을 모두 필터링해야 한다고 하면 이거는 상당한 시간이 걸리겠죠. 어, 물론 이런 작업을 위해서 고급 필터를 사용할 수도 있겠지만 사실 이마저도 엑셀 초보자에게는 그리 쉽지 않은 작업일 ... Read More

Key Insights

• FILTER 함수는 필터링할 전체 데이터 범위와 포함 여부를 나타내는 조건만 지정해 조건에 맞는 행을 반환합니다.
• 조건 입력 셀을 FILTER 수식과 연결하면 제조사나 제품 구분을 바꿀 때 결과 범위가 실시간으로 다시 계산됩니다.
• 엑셀은 조건 비교 결과의 참을 1, 거짓을 0으로 계산하므로 여러 조건을 산술식으로 결합할 수 있습니다.
• 두 조건을 모두 만족하는 행은 조건식끼리 곱해 찾으며, 모든 비교 결과가 참인 행만 1로 계산됩니다.
• 두 조건 중 하나라도 만족하는 행은 조건식을 더해 찾으며, 합계가 0보다 큰 행을 포함 조건으로 사용할 수 있습니다.
• ISNUMBER와 MATCH를 결합하면 각 데이터 값이 별도 검색 목록에 존재하는지를 참 또는 거짓으로 판별할 수 있습니다.
• ISNUMBER와 SEARCH를 결합하면 제품명 같은 텍스트 범위에서 입력한 단어가 포함된 행만 동적으로 추출할 수 있습니다.
• CONCAT으로 각 행의 여러 필드를 하나의 더미 문자열로 합치면 특정 열이 아닌 전체 필드를 대상으로 검색할 수 있습니다.
• 🎬 이 채널의 다른 영상:
  • 직장인은 ChatGPT로 회의록·학습·식단 업무를 어떻게 효율적으로 자동화할까?
  • 구글 시트와 AI로 무료 웹 앱을 만들고 배포하려면 어떻게 해야 하나?
  • NotebookLM에서 회사 템플릿형 슬라이드를 정확하게 만드는 방법은?
• 📺 이 채널: 오빠두엑셀 l 엑셀 강의 대표채널

Questions & Answers

Q: 엑셀 FILTER 함수의 기본 수식은 어떻게 구성하나요?

FILTER 함수에는 먼저 결과로 반환할 전체 데이터 범위를 넣고, 다음으로 각 행의 포함 여부를 결정하는 조건을 넣습니다. 예를 들어 제조사 범위를 입력 셀의 삼성전자와 비교하면 해당 제조사의 행만 반환되며, 입력 셀을 SK하이닉스나 웨스턴디지털로 바꾸면 결과도 실시간으로 갱신됩니다.

Q: FILTER 함수에서 여러 조건을 모두 만족하는 행은 어떻게 찾나요?

모든 조건을 만족하는 행을 찾으려면 각각의 조건식을 괄호로 묶은 뒤 곱합니다. 엑셀은 참을 1, 거짓을 0으로 계산하므로 제조사가 삼성전자라는 조건과 구분이 디램이라는 조건을 곱하면 두 조건이 모두 참인 행만 1이 되어 FILTER 결과에 포함됩니다.

Q: FILTER 함수에서 두 조건 중 하나라도 만족하는 행은 어떻게 추출하나요?

둘 중 하나라도 만족하는 행을 추출하려면 두 조건식을 곱하지 않고 더합니다. 어느 한 조건이라도 참이면 계산 결과가 0보다 커지고, 두 조건이 모두 거짓일 때만 0이 되므로 제조사 또는 제품 구분 가운데 하나가 일치하는 행을 선별할 수 있습니다.

Q: 여러 제조사 목록을 한 번에 필터링하려면 어떤 수식을 사용하나요?

여러 제조사 목록을 한꺼번에 적용하려면 MATCH로 각 제조사 값이 검색 목록에 있는지 확인하고, 그 결과를 ISNUMBER로 감쌉니다. 목록에 존재하면 MATCH가 숫자를 반환해 ISNUMBER가 참이 되고, 존재하지 않으면 오류가 발생해 거짓이 되므로 이 결과를 FILTER 조건으로 사용할 수 있습니다.

Q: MATCH 함수의 찾을 값에 전체 범위를 넣으면 어떻게 작동하나요?

MATCH 함수의 찾을 값 자리에 하나의 셀이 아니라 전체 데이터 범위를 넣으면 범위의 값을 하나씩 검색 목록과 비교합니다. 일치하는 값에는 목록 내 위치를 나타내는 숫자가 반환되고, 일치하지 않는 값에는 오류가 반환되므로 여러 행의 목록 포함 여부를 동시에 계산할 수 있습니다.

Q: 제품명에 특정 단어가 포함된 행만 필터링하려면 어떻게 하나요?

제품명에 검색어가 포함된 행만 찾으려면 SEARCH로 입력 단어를 제품명 범위에서 검색하고 그 결과를 ISNUMBER로 감싼 뒤 FILTER 조건으로 넣습니다. 단어가 발견된 행은 SEARCH가 위치 숫자를 반환하므로 참이 되고, 발견되지 않은 행은 거짓이 되어 결과에서 제외됩니다.

Q: 엑셀의 모든 필드를 대상으로 단어를 검색하려면 어떻게 하나요?

모든 필드를 검색하려면 CONCAT으로 각 행의 제조사부터 제품명까지 여러 필드 값을 하나의 문장처럼 합친 더미 열을 만듭니다. 이후 FILTER 수식의 SEARCH 대상 범위를 특정 제품명 열이 아니라 이 더미 열로 바꾸면 제조사, 구분, 제품명 등 합쳐진 전체 데이터에서 검색어를 찾을 수 있습니다.

Q: FILTER 조건 수식이 길어질 때 괄호는 어떻게 확인하나요?

긴 FILTER 수식에서는 시작 괄호와 짝을 이루는 끝 괄호가 검은색으로 표시될 때까지 닫는 방법으로 구조를 확인할 수 있습니다. 영상에서는 다중 조건이나 ISNUMBER와 SEARCH를 중첩한 수식처럼 괄호가 여러 번 필요한 경우, 색상 표시를 보며 필요한 닫는 괄호 수를 점검하라고 설명합니다.

Q: FILTER 함수는 어떤 엑셀 버전에서 사용할 수 있나요?

설명에 따르면 FILTER 함수는 엑셀 2021 이후 버전에서 제공됩니다. 엑셀 2019 이전 버전을 사용하는 경우에는 같은 예제를 웹 엑셀에서 실습하도록 안내하고 있으므로, 데스크톱 버전에서 함수가 지원되지 않는다면 웹 환경을 이용할 수 있습니다.

Q: 여러 검색어를 단어 포함 조건으로 동시에 처리할 수 있나요?

여러 검색어를 동시에 단어 포함 조건으로 처리하는 것도 가능하다고 설명합니다. M365에 추가된 BYROW 함수와 TOROW 함수를 활용하는 방식이 언급되지만, 구체적인 수식과 작성 절차는 이 강의에서 다루지 않았으므로 제공된 내용만으로 세부 공식을 확정할 수는 없습니다.

Summary & Key Takeaways

• FILTER 함수는 필터링할 데이터 범위와 포함 조건을 받아 조건을 만족하는 행만 반환합니다. 제조사 범위를 입력 셀과 비교하는 조건식을 사용하면 삼성전자, SK하이닉스, 웨스턴디지털처럼 입력값을 바꿀 때마다 결과가 실시간으로 갱신됩니다.

• 다중 조건은 참을 1, 거짓을 0으로 계산하는 원리를 이용합니다. 제조사와 구분을 모두 만족해야 한다면 두 조건식을 곱하고, 둘 중 하나만 만족해도 된다면 더합니다. 제조사와 디램 또는 낸드 같은 조건을 입력 셀로 연결하면 재사용 가능한 보고서가 됩니다.

• 여러 목록 값은 ISNUMBER와 MATCH를 결합하고, 부분 문자열은 ISNUMBER와 SEARCH를 결합해 FILTER의 조건으로 사용합니다. 제품명뿐 아니라 모든 필드를 검색하려면 CONCAT으로 제조사부터 제품명까지 합친 더미 열을 만들고 그 열에서 검색어 포함 여부를 검사합니다.