Computer >> 컴퓨터 >  >> 소프트웨어 >> Office

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

데이터를 분석하다 보면 원하는 결과만 깔끔하게 추출하기 위해 피벗 테이블을 필터링해야 하는 경우가 자주 생깁니다. 다행히 엑셀에는 이를 위한 다양한 기능이 마련되어 있습니다. 이 글에서는 엑셀에서 피벗 테이블을 필터링할 수 있는 가장 효과적이고 널리 쓰이는 8가지 방법을 자세히 소개합니다.

내용이 다소 길어질 수 있으니 아래 목차를 참고해 필요한 부분부터 살펴보세요. 편의상 가장 간단한 예제부터 시작해 여러 열을 동시에 필터링하는 방법, 그리고 마지막에 VBA를 활용한 고급 기법까지 순서대로 다룹니다.

엑셀 피벗 테이블 필터링 8가지 방법

예제로 사용할 데이터셋은 미국 각 주(State)별 제품 카테고리의 주문 날짜, 수량, 매출액 정보입니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

위 데이터셋으로 피벗 테이블을 새로 만드는 과정이 궁금하다면 ‘피벗 테이블 만들기’ 관련 글을 먼저 참고하시면 좋습니다. 여기서는 이미 생성된 아래 피벗 테이블을 기준으로 설명합니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

그럼 본격적으로 피벗 테이블 필터링 방법을 하나씩 알아보겠습니다.

1. 보고서 필터(Report Filter) 활용하기

가장 기본적인 방법은 보고서 필터를 사용하는 것입니다.

예를 들어 애리조나(Arizona) 주 전체 제품 카테고리의 매출 합계를 구하고 싶다고 가정해 봅시다.

⏩ 피벗 테이블 필드 목록에서 States 필드를 Filters(필터) 영역으로 드래그합니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 그러면 States 필드 옆에 드롭다운 화살표가 나타납니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 드롭다운을 클릭하면 모든 주 목록이 표시됩니다. Arizona를 선택하고 확인(OK)을 누릅니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 아래와 같이 애리조나 주의 매출 합계만 표시된 결과를 얻을 수 있습니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

2. 값 필터(Value Filters) 활용하기

숫자 값을 기준으로 필터링할 때는 값 필터가 가장 적합합니다. 값 필터 안에는 다양한 조건이 있으며, 그중 자주 쓰이는 세 가지 방법을 살펴보겠습니다.

2.1. 상위 N개 항목 추출하기

매출 합계 기준 상위 5개 항목만 보고 싶다면 다음과 같이 진행합니다.

⏩ 행 레이블(Row Labels)의 드롭다운 화살표를 클릭합니다.

⏩ 값 필터(Value Filters) > Top 10을 선택합니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 기본값 10을 5로 변경한 뒤 확인을 누릅니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 매출 상위 5개 항목만 표시됩니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

2.2. 상위 백분율(%) 추출하기

전체 매출 중 상위 50%에 해당하는 항목만 보고 싶을 때도 같은 경로를 이용합니다.

⏩ Top 10 필터 창에서 값을 50으로 입력하고 조건을 백분율(Percent)로 선택한 후 확인을 누릅니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 전체 매출의 50%를 차지하는 상위 제품만 표시됩니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

2.3. 특정 값 이상 필터링하기

특정 값을 직접 지정해 전체 피벗 테이블을 걸러낼 수도 있습니다. 예를 들어 매출 합계가 2,500을 초과하는 항목만 보고 싶다면:

⏩ 행 레이블 드롭다운 > 값 필터 > 보다 큼(Greater Than)을 선택합니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 값 입력란에 2500을 입력하고 확인을 누릅니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 매출 합계가 2,500보다 큰 항목만 즉시 표시됩니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

3. 레이블 필터(Label Filters) 활용하기

값 필터를 살펴보다 보면 바로 아래에 레이블 필터 메뉴가 있는 것을 알 수 있습니다. 텍스트(항목 이름) 기준으로 필터링할 때 유용합니다.

예를 들어 제품 카테고리 중 Books(도서)가 포함된 항목, 즉 도서 매출만 확인하고 싶다면:

⏩ 행 레이블 드롭다운 > 레이블 필터 > 포함(Contains)을 선택합니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 레이블 필터 대화상자에 Books라고 입력합니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 확인을 누르면 Books 관련 데이터만 남은 필터링된 피벗 테이블을 볼 수 있습니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

4. 날짜 필터(Date Filters) 활용하기

날짜를 기준으로 피벗 테이블을 분리하는 방법도 있습니다. 우리 데이터셋에는 Order Date(주문 날짜) 필드가 있으므로, 예를 들어 2022-02-15 ~ 2022-05-10 구간의 데이터만 보고 싶은 경우 다음처럼 진행합니다.

⏩ Order Date 필드를 Rows(행) 영역으로 드래그합니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 레이블 필터 > 사이(Between)를 선택합니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 레이블 필터 대화상자에 시작일과 종료일을 입력합니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 확인을 누르면 해당 기간의 데이터만 표시됩니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

5. 검색 상자(Search Box) 활용하기

검색 상자를 이용하는 방법은 정말 간단합니다. 필터 창의 검색란에 원하는 단어(예: Ohio)만 입력하면 됩니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 그러면 Ohio 주에 해당하는 데이터만 남은 피벗 테이블이 바로 표시됩니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

6. 자동 필터(AutoFilter) 활용하기

엑셀을 어느 정도 사용해 본 분이라면 일반 데이터 범위에 쓰이는 필터(Filter) 기능을 알고 있을 겁니다. 하지만 아래 그림처럼 피벗 테이블 내부 셀을 선택한 상태에서는 필터 버튼이 활성화되지 않습니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

그런데 커서를 피벗 테이블 바로 옆 빈 셀에 두면 필터 옵션이 작동합니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

필터 버튼을 클릭하면 모든 열에 드롭다운 화살표가 생기며, 이후에는 별도의 복잡한 절차 없이 원하는 열을 자유롭게 필터링할 수 있습니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

7. 여러 항목 동시에 필터링하기

엑셀 피벗 테이블 필터링 중 가장 강력하고 널리 쓰이는 방법은 여러 열·여러 항목을 한 번에 필터링하는 것입니다. 시간을 크게 절약할 수 있어 실무 활용도가 높습니다.

7.1. 슬라이서(Slicer)로 여러 항목 필터링

슬라이서를 사용하면 주(State)별 필터링을 훨씬 빠르게 할 수 있습니다.

⏩ 피벗 테이블 내부의 아무 셀이나 선택합니다.

⏩ 삽입(Insert) 탭 > 필터(Filters) 그룹에서 슬라이서(Slicer)를 클릭합니다.

⏩ 슬라이서 삽입 대화상자에서 States를 체크합니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

⏩ 화면에 이동 가능한 States 필터 패널이 나타납니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

사용법은 간단합니다. 아래 그림처럼 패널에서 Arizona 같은 주를 클릭하면 왼쪽 피벗 테이블에 즉시 반영됩니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

7.2. TEXTJOIN 함수로 필터 조건 한 셀에 표시하기

선택한 필터 조건을 하나의 셀에 표시하고 싶다면 TEXTJOIN 함수를 활용할 수 있습니다.

예를 들어 G4 셀에 아래 수식을 입력합니다.

=TEXTJOIN(",",TRUE,B6:B15)

여기서 ","는 구분 기호, TRUE는 빈 셀 무시 옵션, B6:B15는 데이터셋의 제품 카테고리 범위입니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

수식을 입력한 뒤 슬라이서에서 항목을 선택하면, 선택된 필터 조건이 G4 셀에 한 줄로 표시됩니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

위 예시에서는 Books, Sports 두 조건이 G4 셀에 표시되는 것을 확인할 수 있습니다.

7.3. 보고서 연결(Report Connections)로 두 피벗 테이블 함께 필터링

두 개의 피벗 테이블을 연결해 하나의 슬라이서로 동시에 필터링하는 방법도 있습니다.

먼저 기존 피벗 테이블을 Ctrl + C로 복사한 뒤 근처 위치에 Ctrl + V로 붙여넣습니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

복사본(두 번째 피벗 테이블)에는 행 레이블만 남겨둡니다. 이제 슬라이서에서 임의의 주(예: Florida)를 선택하면 두 피벗 테이블이 동시에 필터링됩니다.

첫 번째 테이블은 제품 카테고리 기준으로, 두 번째 테이블은 주(State) 기준으로 필터링됩니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

이것이 가능한 원리는 무엇일까요? 슬라이서를 마우스 오른쪽 버튼으로 클릭하고 보고서 연결(Report Connections)을 선택해 보세요.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

아래 그림처럼 첫 번째 피벗 테이블(PivotTable19)과 두 번째 피벗 테이블(PivotTable21)이 서로 연결되어 있음을 확인할 수 있습니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

8. VBA로 셀 값 기준 피벗 테이블 필터링하기

마지막으로, 특정 셀의 값을 기준으로 피벗 테이블을 자동 필터링하는 VBA 방법을 소개합니다.

예를 들어 E7 셀에 있는 Florida 값을 기준으로 전체 피벗 테이블을 필터링한다고 가정해 봅시다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

1단계: VBA 편집기 열기

개발 도구(Developer) > Visual Basic을 클릭해 편집기를 엽니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

이어서 삽입(Insert) > 모듈(Module)을 선택해 새 모듈을 만듭니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

2단계: 코드 입력

아래 코드를 모듈에 붙여넣습니다.

Sub Filter_UsingVBA()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("PivotTable23").PivotFields("States")
   strFilter = ActiveWorkbook.Sheets("Sheet14").Range("E7").Value
   pvFld.CurrentPage = strFilter
End Sub

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

코드에서는 pvFld를 PivotField 형식으로, strFilter를 String 형식으로 선언한 뒤 각각의 출처를 지정했습니다. 코드의 핵심 요소는 다음 네 가지입니다.

  • 피벗 테이블 이름: PivotTable23
  • 필드 이름: States
  • 활성 시트 이름: Sheet14
  • 필터 값 위치: E7 셀

3단계: 매크로 실행

F5(노트북은 Fn + F5) 키로 코드를 실행하면 E7 셀의 값인 Florida 기준으로 필터링된 결과가 즉시 표시됩니다.

엑셀 피벗 테이블 필터링 방법 8가지 – 초보자도 바로 따라 하는 완벽 가이드

마치며

지금까지 엑셀에서 피벗 테이블을 필터링하는 8가지 방법을 살펴봤습니다. 상황에 맞는 필터 기능을 잘 활용하면 데이터 분석 속도와 정확도가 크게 향상될 것입니다. 궁금한 점이나 추가하고 싶은 팁이 있다면 댓글로 알려주세요.

함께 읽으면 좋은 글

  • 엑셀 텍스트 필터 활용법 (5가지 예제)
  • 엑셀에 필터 추가하는 방법 (4가지 방법)
  • 엑셀 색상별 필터링 방법 (2가지 예제)
  • 엑셀 사용자 지정 필터 수행 방법 (5가지 방법)