Microsoft Excel에는 고급 필터(Advanced Filter)라는 강력한 기능이 있습니다. 이 기능을 활용하면 특정 조건에 맞는 데이터를 손쉽게 추출할 수 있습니다. 이 글에서는 실전 예제 5가지를 통해 엑셀 고급 필터를 자세히 살펴보겠습니다.
일반 필터와 고급 필터의 차이점
이름은 비슷해 보이지만, 일반 필터와 고급 필터는 몇 가지 중요한 차이가 있습니다. 주요 차이점을 한눈에 정리하면 다음과 같습니다.
- 고급 필터는 필터링된 데이터를 다른 위치로 동시에 복사·추출할 수 있지만, 일반 필터로는 불가능합니다.
- 일반 필터는 목록에 표시된 단순한 조건에만 머무는 반면, 고급 필터는 복잡한 다중 조건 범위를 자유롭게 설정할 수 있습니다.
- 고급 필터는 데이터 집합에서 중복을 제거한 고유 레코드만 추출할 때 매우 유용합니다.
엑셀 고급 필터의 5가지 유용한 활용 예제
여기서는 커피 관련 키워드 검색 샘플 데이터를 사용합니다. 각 키워드별 검색량(Search Volume)과 트래픽(Traffic) 비율이 함께 표시되어 있습니다.
이제 이 데이터를 대상으로 다양한 상황에서 고급 필터를 적용하는 방법을 알아보겠습니다.
1. 엑셀 고급 필터에 조건 적용하기
고급 필터는 리본 메뉴의 도구일 뿐만 아니라 다양한 방식으로 수행할 수 있는 명령입니다. 여기서는 서로 다른 조건을 적용하는 방법을 살펴봅니다.
1.1 단어 하나로 조건 걸기
첫 번째 예제에서는 고급 필터를 사용해 특정 단어 하나를 찾아보겠습니다.
- 먼저 필터링할 데이터 내의 아무 셀이나 선택합니다.
- 그다음 데이터(Data) 탭으로 이동하여 정렬 및 필터(Sort & Filter) 그룹에서 필터(Filter)를 선택합니다.
- 열 머리글의 필터 아이콘을 클릭하고, 텍스트 필터(Text Filters) 입력란에 원하는 단어(예: "best")를 입력합니다.
- 확인(OK)을 클릭하면 아래 이미지와 같이 필터링된 데이터를 확인할 수 있습니다.
1.2 두 개의 단어로 조건 걸기
두 단어를 동시에 만족하는 데이터를 찾으려면 앞서 설명한 단순한 방법으로는 부족합니다. 하지만 같은 필터 아이콘을 사용해 두 단어 조건을 설정할 수 있습니다. 아래 단계를 따라 해보세요.
- 먼저 드롭다운 목록에서 텍스트 필터(Text Filters) 명령을 선택한 후 사용자 지정 필터(Custom Filter)를 클릭합니다.
- 그러면 사용자 지정 자동 필터(Custom AutoFilter) 대화상자가 나타납니다.
- 이 대화상자에서 두 단어 조건을 설정할 수 있습니다. 첫 번째와 두 번째 드롭다운에서 모두 포함(contains)을 선택합니다.
- 오른쪽 입력란에 각각 best와 machine을 입력합니다.
- 대화상자 하단에는 And와 Or라는 두 개의 옵션 버튼이 있습니다.
"And" 사용 시
- "And"를 선택하면 "best"와 "machine" 두 단어를 모두 포함한 키워드만 필터링됩니다.
"Or" 사용 시
- 이번에는 "Or" 버튼을 선택했을 때 어떻게 달라지는지 확인해 보겠습니다.
1.3 세 개 이상의 단어로 복합 조건 걸기
데이터 분석을 하다 보면 더 복잡한 조건으로 필터링해야 하는 경우가 있습니다. 다음과 같은 조건을 생각해 볼 수 있습니다.
- Keywords 열에 "best", "machine", "reviews"가 포함되어야 함
- Search Volume 열 값은 200 이상
- Traffic 열 값은 1.5 이하
이제 위 조건에 맞춰 데이터를 필터링해 보겠습니다.
- 먼저 데이터(Data) 탭의 정렬 및 필터(Sort & Filter) 그룹에서 고급(Advanced) 명령을 클릭합니다.
- 그다음 목록 범위(List range) 입력란에 필터링할 데이터 범위를 지정합니다.
- 이어서 조건 범위(Criteria range) 입력란에 조건식을 작성해 둔 범위를 지정합니다.
- 마지막으로 확인(OK)을 누르면 결과가 표시됩니다.
2. 엑셀 수식을 활용한 고급 필터
간단한 수식을 이용해서도 고급 필터 조건을 만들 수 있습니다. 아래 단계를 따라 해보세요.
- 먼저 조건 키워드를 셀 범위 F5:F6에 입력합니다.
- 그다음 셀 G5에 아래 수식을 입력합니다.
=B5>700
- 이후 Enter 키를 누릅니다.
- 키워드가 해당 조건을 충족하면 결과가 TRUE로 표시됩니다.
- 마지막으로 같은 수식을 셀 G6에도 적용하고 결과를 확인합니다.
3. 엑셀 고급 필터에서 와일드카드 문자 활용하기
상황에 따라 와일드카드 문자(?, *, ~)를 활용하면 시간을 크게 절약할 수 있습니다. 예를 들어, 회사명이 "e"로 시작하는 데이터만 표시하고 싶다고 가정해 보겠습니다.
- 먼저 필터링할 데이터 내의 셀을 선택합니다.
- 그다음 앞서 설명한 방법대로 필터를 적용합니다.
- 이후 열 머리글의 필터 아이콘을 클릭하고 텍스트 필터(Text Filters) 입력란에 "e*"를 입력합니다.
- 확인(OK)을 클릭하면 아래 이미지처럼 "e"로 시작하는 데이터만 필터링됩니다.
4. 고급 필터로 고유 목록(중복 제거) 추출하기
반복되는 값이 많은 데이터에서 중복 없이 고유한 값만 가져오고 싶다면(첨부 이미지 참고) 이 방법이 도움이 됩니다.
아래 과정을 확인해 보세요.
- 앞서 설명한 것처럼 데이터(Data) 탭에서 고급 필터(Advanced Filter) 대화상자를 엽니다.
- 여기서 목록 범위(List range)에 B4:D15를 입력합니다.
- 이어서 동일한 레코드는 하나만(Unique records only) 체크박스를 선택합니다.
- 그다음 확인(OK)을 누릅니다.
- 완료되었습니다! 중복 없이 고유한 키워드 목록을 얻었습니다.
5. 열 구간(Interval)별 고급 필터 적용하기
구간(interval)이 무엇인지 아시나요? 1~5, 8~15처럼 값의 범위를 의미합니다. 특정 열을 여러 구간 조건으로 필터링하고 싶다면 아래 단계를 따라 해보세요.
- 먼저 아래 이미지처럼 조건 범위(Criteria range)를 작성합니다.
- 이제 고급 필터 대화상자의 각 입력란에 목록 범위와 조건 범위를 지정합니다.
- 마지막으로 확인(OK)을 누르면 구간별로 필터링된 결과를 확인할 수 있습니다.
고급 필터로 다른 위치에 결과 복사하기
앞서 언급했듯이 고급 필터는 필터링된 데이터를 다른 위치로 복사할 수 있는 강력한 기능입니다. 실제 사용 방법을 살펴보겠습니다.
- 먼저 기존 시트 옆에 새 시트를 하나 만듭니다.
- 그다음 새 시트의 F5, G5, H5 셀에 조건을 입력합니다.
- 이제 고급 필터(Advanced Filter) 대화상자를 엽니다.
- 여기서 다른 위치에 복사(Copy to another location)를 선택합니다.
- 그다음 데이터에 맞게 목록 범위(List range)를 지정합니다.
- 이어서 조건 범위(Criteria range)를 지정합니다.
- 마지막으로 복사 위치(Copy to) 입력란에 결과를 붙여넣을 새 시트의 위치를 지정합니다.
- 마지막으로 확인(OK)을 누르고 최종 결과를 확인합니다.
[해결 방법] 엑셀 고급 필터가 작동하지 않을 때
엑셀 고급 필터가 작동하지 않는 데에는 여러 가지 이유가 있습니다. 가장 흔한 두 가지 원인을 확인해 보세요.
- 데이터셋과 조건 범위의 머리글(헤더) 제목이 아래 이미지처럼 서로 일치하지 않는지 확인합니다.
- 그렇지 않다면 아래처럼 오류 값이나 빈 셀이 있는지 찾아봅니다.
머리글 제목을 정확히 일치시키거나 올바른 값을 입력해 문제를 해결하면 고급 필터가 정상적으로 작동합니다.
마무리
이 글이 엑셀의 고급 필터를 이해하고 활용하는 데 도움이 되기를 바랍니다. 추가로 알고 싶은 내용이나 엑셀 데이터 필터링에 관한 특별한 노하우가 있다면 댓글로 공유해 주세요. 소중한 의견 감사히 받겠습니다. 앞으로도 이런 유용한 글들을 계속 확인하려면 ExcelDemy에 주목해 주세요.