이 글에서는 Excel의 '고급(Advanced)' 명령을 활용하여 한 열을 여러 조건에 따라 필터링하는 5가지 방법을 소개합니다. Excel의 고급 필터 기능은 다양한 논리 조건을 적용해 데이터 집합을 폭넓게 필터링할 수 있는 강력한 도구입니다. 지금부터 예제를 통해 이 유용한 기능을 자세히 살펴보겠습니다.
Excel에서 한 열에 여러 조건을 적용하는 고급 필터의 5가지 방법
한 열에 여러 조건을 적용한 고급 필터를 시연하기 위해 아래 데이터 집합을 사용합니다. 이 데이터에는 여러 도시에서 판매된 다양한 카테고리 제품의 판매 수량 목록이 담겨 있습니다. 특정 열에 고급 필터 기능으로 여러 조건을 적용해 보겠습니다.
1. 고급 필터 기능으로 한 열을 여러 조건에 따라 필터링하기
이 예제에서는 'Bars' 또는 'Snacks' 카테고리에 속한 제품만 데이터 집합에서 필터링하겠습니다. 조건 범위는 원본 데이터와 동일한 헤더 이름인 "Category"로 정의했습니다.
이제 다음 단계를 따라 Bars 및 Snacks 카테고리 제품만 필터링해 보겠습니다.
- 전체 데이터 집합을 선택합니다.
- Excel 리본 메뉴에서 [데이터] 탭으로 이동합니다.
- [고급] 버튼을 클릭합니다.
- 아래 스크린샷처럼 고급 필터 창에 선택된 목록 범위 $B$8:$F$27(즉, 전체 데이터 집합)가 표시됩니다.
- 이제 조건 범위 입력 상자 오른쪽의 화살표 버튼을 클릭합니다.
- 마우스로 드래그하여 조건 범위(D4:D6 셀)를 선택한 후 Enter 키를 누릅니다.
- Enter 키를 누르면 고급 필터 창이 다시 나타나며 목록 범위와 조건 범위가 선택된 것을 확인할 수 있습니다. "목록 같은 위치에 필터링(Filter the list, in-place)" 옵션이 선택되어 있는지 확인합니다.
- 마지막으로 [확인]을 클릭하여 저장합니다.
- Bars 또는 Snacks 카테고리 제품으로 데이터가 성공적으로 필터링되었습니다.
더 읽어보기: Excel VBA 범위에서 여러 조건으로 고급 필터 사용하기 (5가지 방법)
2. 보조 열을 활용해 고급 명령으로 여러 조건에 따라 한 열 필터링하기
여러 조건으로 열을 필터링하기 위해 이번에는 보조 열(helper column)을 사용합니다. 다음 단계를 따라 해 보세요.
- "Helper Column"이라는 새 열을 추가합니다.
- G9 셀에 아래 수식을 입력합니다.
=COUNTIF($D$5:$D$6,D9)여기서 COUNTIF 함수는 D9 셀의 카테고리(Bars)가 조건 범위 D5:D6(Bars 및 Snacks)에 존재하는지 확인합니다. 일치하는 항목을 찾으면 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다. 마찬가지로 각 카테고리 이름을 조건 목록과 대조하게 되는데, 이를 위해 조건 범위에는 절대 참조를 사용했습니다.
- Enter 키를 누릅니다.
- G9 셀 오른쪽 하단 모서리의 채우기 핸들을 찾아 데이터 집합의 마지막 행까지 아래로 드래그합니다.
- 이 작업으로 수식이 보조 열의 모든 셀에 복사됩니다. 아래 스크린샷에서 볼 수 있듯이 Bars 또는 Snacks 카테고리 제품이 있는 행은 보조 열에서 1이 출력되고, 그렇지 않으면 0이 출력됩니다.
- 이제 보조 열을 선택합니다.
- Excel 리본 메뉴에서 [데이터] 탭으로 이동합니다.
- [필터] 옵션을 클릭합니다.
- 보조 열 헤더의 오른쪽 모서리에 아래쪽 화살표가 나타납니다. 화살표를 클릭하고 숫자 1 확인란을 선택한 후 [확인]을 누릅니다.
- 마지막으로 Bars 및 Snacks 카테고리로 필터링된 데이터 집합을 얻었습니다.
더 읽어보기: Excel VBA 조건이 있는 고급 필터 예제 (6가지 조건)
함께 읽으면 좋은 글
- Excel에서 여러 조건으로 고급 필터 사용하기 (15가지 실전 예제)
- Excel에서 조건 범위에 텍스트가 포함된 경우 고급 필터 사용법
- Excel에서 고급 필터로 다른 위치에 복사하기
- Excel 고급 필터: "포함하지 않음" 적용하기 (2가지 방법)
3. 고급 명령으로 한 열에서 여러 조건에 따른 고유 값 필터링하기
다음 데이터 집합에서 4개의 서로 다른 카테고리에 속한 고유한 제품 이름을 찾고 싶다고 가정해 보겠습니다.
이를 위해서는 예제 1의 단계를 따르되, 아래와 같이 설정합니다.
목록 범위: $B$9:$C$28
조건 범위: $B$3:$B$7
그리고 "고유 레코드만(Unique records only)" 확인란을 클릭합니다.
최종 결과로 4개의 서로 다른 카테고리에서 7개의 고유한 제품을 얻었습니다. 이처럼 필터링 조건을 수정하면 해당 카테고리의 고유한 제품 이름을 손쉽게 파악할 수 있습니다.
더 읽어보기: Excel에서 고유 레코드만 표시하는 고급 필터 사용법
4. 와일드카드를 활용한 한 열의 여러 조건 고급 필터
Excel에서 사용할 수 있는 와일드카드는 3가지입니다.
* (별표): 텍스트에서 임의 개수의 문자를 찾습니다.
? (물음표): 텍스트에서 단일 문자를 찾습니다.
~ (물결표): 텍스트에서 와일드카드 문자 자체를 찾습니다.
이 예제에서는 이름에 'Chip' 또는 'Potato' 문자열이 포함된 제품을 찾아 보겠습니다.
따라서 필터링 조건에서 * (별표)를 사용해 두 문자열을 감싸야 합니다. 예제 1의 단계를 따라 목록 범위를 $B$7:$B$26으로, 조건 범위를 $D$2:$D$4로 설정합니다. 추가로 "고유 레코드만" 확인란을 클릭합니다.
그 결과 Chocolate Chips와 Potato Chips 두 제품을 얻었습니다.
다음 스크린샷은 이름이 C로 시작하거나 Potato가 포함된 제품을 보여줍니다.
더 읽어보기: Excel 고급 필터 [여러 열 및 조건, 수식 활용, 와일드카드]
5. 계산된 데이터에 대한 한 열의 여러 조건 고급 필터
이 예제에서는 계산된 데이터를 사용해 한 열에 여러 조건을 적용합니다. 여기서는 수량이 50보다 크고 100보다 작은 제품을 찾습니다. 이를 위해 Quantity 열의 첫 번째 셀(F4)에 아래 수식을 적용하고, 결과를 임의의 셀(E22)에 임의의 헤더 이름(데이터 집합의 헤더 이름과 일치하지 않아야 함)과 함께 출력합니다. 수식은 다음과 같습니다.
=IF(AND(F4<100,F4>50),F4,FALSE)E22 셀의 출력 값은 FALSE입니다. 수량 33이 해당 범위에 속하지 않기 때문입니다.
이후 전체 데이터 집합을 목록 범위로, E21:E22 셀을 조건 범위로 설정합니다.
마지막으로 [확인]을 누르면 수량이 50~100 범위에 있는 제품 목록이 결과로 표시됩니다.
더 읽어보기: Excel 고급 필터가 작동하지 않을 때 (원인 2가지 및 해결책)
참고 사항
'고급' 명령은 Excel의 '필터' 기능과 몇 가지 핵심적인 차이점이 있습니다. 별도의 원본 범위와 조건 범위를 사용해 데이터를 필터링한다는 점입니다. 이 기능을 활용하면 예제 5에서 했던 것처럼 함수와 수식으로 조건을 설정할 수 있습니다. 또한 하나 또는 여러 조건을 기반으로 원본 목록에서 고유한 데이터를 손쉽게 추출하는 옵션도 제공합니다.
결론
이제 5가지 예제를 통해 Excel의 고급 필터링 기능으로 한 열을 여러 조건에 따라 필터링하는 방법을 익혔습니다. 이 글이 실무에서 이 방법들을 더욱 자신 있게 활용하는 데 도움이 되기를 바랍니다. 질문이나 제안 사항이 있다면 언제든지 아래 댓글로 남겨 주세요.
관련 글
- Excel에서 고급 필터로 빈 셀 제외하기 (3가지 쉬운 요령)
- VBA 고급 필터로 다른 시트에 데이터 복사하기
- Excel에서 고급 필터로 데이터를 다른 시트에 복사하는 방법
- VBA에서 고급 필터 사용하기 (단계별 가이드)
- 동적 고급 필터 Excel (VBA 및 매크로)
- Excel VBA 예제: 조건이 있는 고급 필터 사용 (6가지 조건)