특정 셀 값에 따라 Excel에서 피벗 테이블을 필터링하는 방법은 무엇입니까?

Excel에서 피벗 테이블은 데이터를 요약, 분석 및 탐색하는 데 널리 사용됩니다. 기본적으로 피벗 테이블 내의 필터링은 일반적으로 필터 드롭다운 메뉴에서 원하는 항목을 선택하여 수행됩니다. 이 접근 방식은 유연성을 제공하지만, 보다 동적인 필터링 방법이 필요한 시나리오도 있습니다 — 예를 들어, 특정 워크시트 셀에 입력된 값에 따라 피벗 테이블 결과가 자동으로 변경되기를 원할 수 있습니다. 이는 대시보드를 준비하거나, 작업 흐름을 자동화하거나, 수동 필터링에 익숙하지 않은 최종 사용자를 위한 상호작용형 보고서를 작성할 때 특히 유용합니다.

Excel은 셀의 값을 피벗 테이블 필터에 직접 연결하는 표준 기능을 제공하지 않습니다(코드를 사용하지 않는 경우). 그러나 이 요구 사항을 해결하기 위한 여러 실용적인 기법들이 있으며, 각각 장단점과 고려해야 할 점들이 있습니다. 본 튜토리얼에서는 먼저 VBA를 사용하여 셀을 피벗 테이블 필터에 직접 연결하는 간단한 방법을 소개합니다. 이렇게 하면 셀 값이 변경됨에 따라 피벗 테이블이 즉시 업데이트됩니다. 또한, GETPIVOTDATA, FILTER와 같은 Excel 수식을 사용하여 필터링된 결과를 표시하거나, Slicer를 그래픽 필터 컨트롤로 사용하는 방법도 다룹니다. 이러한 옵션들을 이해하면 자신의 Excel 작업 흐름과 사용자 경험에 가장 적합한 방법을 선택하는 데 도움이 됩니다.

VBA 코드를 사용하여 특정 셀 값에 따라 피벗 테이블 필터링

셀에 값을 입력하면 피벗 테이블 필터가 자동으로 반응하는 진정한 동적 상호작용을 원한다면, VBA는 이를 직접 해결해주는 솔루션입니다. 이는 대시보드, 동료를 위한 템플릿 또는 단일 셀의 값을 변경하여 신속하게 필터 조정이 필요한 상황에서 특히 유용합니다. 하지만 이 방법은 VBA 에디터에 대한 기본 지식이 필요하며, 모든 매크로와 마찬가지로 통합 문서는 매크로 사용이 가능한 형식(.xlsm)으로 저장되어야 합니다.

다음 VBA 코드를 사용하면 워크시트 셀을 피벗 테이블 필터에 동적으로 연결할 수 있습니다. 다음 단계를 신중히 따르고, 필요에 따라 워크시트 이름, 피벗 테이블 이름 및 필드 참조를 수정하세요.

1단계: 피벗 테이블을 필터링하려는 값을 워크시트 셀에 입력하세요 (예: H6 셀에 필터링 값을 입력하거나 선택하세요).

2단계: 대상 피벗 테이블이 포함된 워크시트를 엽니다. Excel 하단의 시트 탭을 우클릭하고 컨텍스트 메뉴에서 코드 보기(View Code)를 선택하세요. 이렇게 하면 해당 워크시트에 대한 VBA 에디터 창이 열립니다.

3단계: 열린 Microsoft Visual Basic for Applications(VBA) 창에서 다음 코드를 워크시트의 코드 모듈(표준 모듈이 아님)에 붙여넣으세요.

VBA 코드: 셀 값에 따른 피벗 테이블 필터링

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim xStr As String
    On Error Resume Next
    If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
    Set xPFile = xPTable.PivotFields("Category")
    xStr = Target.Text
    xPFile.ClearAllFilters
    xPFile.CurrentPage = xStr
    Application.ScreenUpdating = True
End Sub

4단계: Alt + Q를 눌러 VBA 에디터를 닫고 Excel로 돌아갑니다.

이제 피벗 테이블은 H6에 입력된 값과 일치하는 데이터만 자동으로 필터링하여 표시해야 합니다. 이 매크로는 H6의 값이 변경될 때마다 실행되므로 데이터 요약을 동적으로 조정하기 쉽습니다.

필터 셀의 값을 언제든지 수정할 수 있습니다. 셀 내용이 변경되거나 대체되면 피벗 테이블이 즉시 업데이트됩니다.

Excel 수식 – 셀 값에 기반하여 필터링된 피벗 테이블 결과 표시

매크로를 활성화하지 않으려는 사용자의 경우, Excel에서는 특정 셀 값에 따라 피벗 테이블 결과를 표시하기 위한 수식 기반 접근 방식을 제공합니다. GETPIVOTDATA 및 FILTER와 같은 함수들은 피벗 테이블의 필터 설정을 실제로 변경하지는 않지만, 사용자 입력에 반응하는 요약 결과를 동적으로 참조하고 표시할 수 있습니다.

사용자가 입력한 변경 가능한 기준을 반영하는 맞춤 요약 테이블, 대시보드 또는 보고서를 작성할 때 특히 유용합니다 — 원래의 피벗 테이블 뷰를 변경하지 않고도 가능합니다.

GETPIVOTDATA 사용하기:

귀하의 피벗 테이블(“PivotTable2”라고 명명된)이 카테고리별로 매출을 요약하고, 필터 값이 H6 셀에 입력되었다고 가정해봅시다. GETPIVOTDATA를 사용하여 H6에 지정된 카테고리에 대한 총 매출을 표시할 수 있습니다.

1. 요약 결과를 표시하려는 셀을 선택하세요 (예: I6):

=GETPIVOTDATA("Sum of Sales", $A$4, "Category", $H$6)

2. Enter를 누릅니다. H6의 값을 변경하면 I6의 결과가 자동으로 업데이트되어 피벗 테이블에서 해당 요약 결과를 반영합니다.

피벗 테이블이 다른 필드 이름이나 레이아웃을 사용하는 경우, 수식을 그에 맞게 조정하세요. GETPIVOTDATA 수식을 자동으로 생성하려면 셀에 =를 입력한 다음 피벗 테이블 내부의 값 셀을 클릭하세요. Excel이 적절한 수식을 삽입하며, 이후 이를 필요에 따라 편집할 수 있습니다.

헬퍼 테이블과 함께 FILTER 사용하기:

원본 데이터 세트에서 상세한 레코드를 추출하고자 하고(피벗 테이블 요약만이 아닌) Excel 365 또는 Excel 2019를 사용한다면, FILTER 함수는 셀 값에 따라 동적으로 필터링할 수 있습니다.

소스 데이터가 A1:C100 범위에 있고 카테고리가 A 열에 있다고 가정합니다.

1. 필터링된 레코드가 나타날 시작 셀을 선택하세요 (예: J6):

=FILTER(A2:C100, A2:A100 = H6, "No data")

2. Enter를 누릅니다. 일치하는 행이 인접한 셀로 쏟아져 나오며, 카테고리가 H6의 값과 일치하는 모든 레코드를 나열합니다. H6을 업데이트하면 결과가 즉시 새로 고쳐집니다.

피벗 테이블 그룹화와 일치시키거나 여러 기준으로 필터링하려면 GETPIVOTDATA와 FILTER를 결합하거나 논리 조건을 추가하여 수식을 확장하는 것을 고려하세요.

기타 Excel 기본 제공 방법 – Slicer를 상호작용형 피벗 테이블 필터로 사용

VBA 또는 수식 기반 솔루션이 워크플로우에 완전히 부합하지 않는다면, Excel의 Slicer는 피벗 테이블을 필터링하기 위한 또 다른 상호작용형 방법을 제공합니다. Slicer는 사용자가 간단한 클릭 인터페이스로 데이터를 필터링할 수 있는 시각적 필터 컨트롤입니다. 셀 값에 직접 연결할 수는 없으며(즉, 셀을 변경하여 Slicer를 제어할 수는 없습니다), 비기술적인 사용자가 드롭다운 필터를 사용하지 않고도 쉽게 데이터를 필터링할 수 있어 대시보드 및 보고서에 직관적이고 매우 효과적입니다.

Slicer를 추가하고 사용하는 방법:

1. 피벗 테이블 내의 아무 셀이나 선택하세요.
2. 피벗테이블 분석 탭(또는 이전 버전의 분석 탭)으로 이동하여 Slicer 삽입을 클릭하세요.
3. 삽입 Slicer 대화 상자에서 필터링할 필드를 선택한 다음(예: 카테고리) 확인을 클릭하세요.
4. Slicer가 워크시트에 나타납니다. 해당 값으로 피벗 테이블을 필터링하려면 버튼을 클릭하세요. Ctrl 키를 길게 누르면 여러 항목을 선택할 수 있습니다.

Slicer는 서식을 지정하고 크기를 조정하며 여러 피벗 테이블에 연결하여 다양한 보고서에서 동기화된 필터링을 수행할 수 있습니다. 이는 드롭다운 필터에 익숙하지 않은 사용자들이 VBA나 수식을 편집하지 않고도 쉽게 데이터를 필터링할 수 있어 대시보드나 공유 통합 문서에서 특히 유용합니다.

제한 사항: Slicer는 셀 값에 대한 기본 연결을 지원하지 않습니다. 워크플로우에서 셀 입력에 의해 제어되는 동적 필터링이 필요한 경우, Slicer는 VBA 또는 수식 기반 방법의 대체재라기보다는 보완 도구로 간주해야 합니다.

데이터가 피벗 테이블이 아닌 Excel 테이블에 저장된 경우에도 테이블을 선택하고 테이블 디자인 탭 > Slicer 삽입으로 이동하여 Slicer를 사용할 수 있습니다.

문제 해결: Slicer가 피벗 테이블을 필터링하지 않는 것처럼 보이는 경우, Slicer 또는 분석 탭 아래의 보고서 연결(Report Connections)을 확인하여 의도된 피벗 테이블에 올바르게 연결되었는지 확인하세요.

위의 방법 각각은 다른 목적을 위해 사용됩니다: VBA는 셀 연결된 필터링을 허용하고, 수식은 결과의 동적 표시를 제공하며, Slicer는 사용자 친화적인 그래픽 필터링을 제공합니다. 자동화, 유연성 및 사용 용이성 요구 사항에 가장 잘 맞는 방법을 선택하세요. 전통적인 피벗 테이블 드롭다운 필터는 기본적인 대체 옵션으로 남아 있습니다.

관련 기사:

• Excel에서 여러 시트를 하나의 피벗 테이블로 결합하는 방법은 무엇입니까?
• Excel에서 텍스트 파일로부터 피벗 테이블을 만드는 방법은 무엇입니까?
• Excel에서 피벗 테이블 필터를 특정 셀에 연결하는 방법은 무엇입니까?

최고의 오피스 생산성 도구

🤖	Kutools AI 도우미: 데이터 분석에 혁신을 가져옵니다. 방법: 지능형 실행   |  코드 생성  |  사용자 정의 수식 생성  |  데이터 분석 및 차트 생성  |  Kutools Functions 호출…
	인기 기능: 중복 찾기, 강조 또는 중복 표시   |  빈 행 삭제   |  데이터 손실 없이 열 또는 셀 병합   |   반올림(수식 없이) ...
	슈퍼 LOOKUP: 다중 조건 VLOOKUP  |   다중 값 VLOOKUP  |   다중 시트 조회   |   퍼지 매치 ....
	고급 드롭다운 목록: 드롭다운 목록 빠르게 생성   |  종속 드롭다운 목록   |  다중 선택 드롭다운 목록 ....
	열 관리자: 지정한 수의 열 추가  |  열 이동  |  숨겨진 열의 표시 상태 전환  |  범위 및 열 비교 ...
	추천 기능: 그리드 포커스   |  디자인 보기   |   향상된 수식 표시줄   |  통합 문서 & 시트 관리자   |  자동 텍스트 라이브러리   |  날짜 선택기   |  데이터 병합   |  셀 암호화/해독   |  목록으로 이메일 보내기   |  슈퍼 필터   |   특수 필터(굵게/이탤릭/취소선 필터 등) ...
	15대 주요 도구 세트: 12 가지 텍스트 도구(텍스트 추가, 특정 문자 삭제, ...)  |  50+ 종류의 차트(간트 차트, ...)  |  40+ 실용적 수식(생일을 기반으로 나이 계산, ...)  |  19 가지 삽입 도구(QR 코드 삽입, 경로에서 그림 삽입, ...)  |  12 가지 변환 도구(단어로 변환하기, 통화 변환, ...)  |  7 가지 병합 & 분할 도구(고급 행 병합, 셀 분할, ...)  |  ... 등 다양

Kutools는 사용자가 선호하는 언어로 이용할 수 있습니다 – 영어, 스페인어, 독일어, 프랑스어, 중국어 및40개 이상의 언어 지원!

Kutools for Excel과 함께 엑셀 능력을 한 단계 끌어 올리고, 이전에 없던 효율성을 경험하세요. Kutools for Excel은300개 이상의 고급 기능으로 생산성을 높이고 저장 시간을 단축합니다.  가장 필요한 기능을 바로 확인하려면 여기를 클릭하세요...

Kutools for Excel의 자세한 정보... 무료 다운로드...

---

Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 더욱 간편하게 만듭니다

• Word, Excel, PowerPoint에서 탭 편집 및 읽기를 활성화합니다.
• 새 창 대신 같은 창의 새로운 탭에서 여러 파일을 열고 생성할 수 있습니다.
• 생산성이50% 증가하며, 매일 수백 번의 마우스 클릭을 줄여줍니다!

Office Tab의 자세한 정보... 무료 다운로드...

---

모든 Kutools 추가 기능. 한 번에 설치

Kutools for Office 제품군은 Excel, Word, Outlook, PowerPoint용 추가 기능과 Office Tab Pro를 한 번에 제공하여 Office 앱을 활용하는 팀에 최적입니다.

• 올인원 제품군 — Excel, Word, Outlook, PowerPoint 추가 기능 + Office Tab Pro
• 설치 한 번, 라이선스 한 번 — 몇 분 만에 손쉽게 설정(MSI 지원)
• 함께 사용할 때 더욱 효율적 — Office 앱 간 생산성 향상
• 30일 모든 기능 사용 가능 — 회원가입/카드 불필요
• 최고의 가성비 — 개별 추가 기능 구매 대비 절약

Kutools for Office의 자세한 정보... 무료 다운로드...