Excel에서 피벗 테이블 필터를 특정 셀에 연결하는 방법은 무엇입니까?

Excel에서는 사용자가 특정 셀의 값을 반영하도록 피벗 테이블 필터가 동적으로 업데이트되는 대화형 보고서를 자주 작성하려고 할 수 있습니다. 이를 통해 사용자는 한 곳에서 필터 값을 선택하거나 입력하면 피벗 테이블이 해당 입력을 기반으로 동적으로 업데이트됩니다. 이 방법은 특히 대시보드나 데이터 탐색을 위한 맞춤형 필터 인터페이스를 설계할 때 유용합니다.

이 문서에서는 VBA 기반 접근법 및 기타 Excel 내장 메소드를 포함한 여러 실용적인 솔루션을 제공하여 피벗 테이블 필터를 셀 값에 연결하거나 유사한 동적 보고 효과를 달성하는 데 도움을 줍니다.

• VBA 코드를 사용하여 피벗 테이블 필터를 특정 셀에 연결하기
• Excel 수식 - 슬라이서 또는 보고서 필터 참조와 함께 수식(GETPIVOTDATA 등) 사용
• 기타 Excel 내장 메소드 - 피벗 테이블 슬라이서 및 대시보드 연결하여 상호작용형 필터링

VBA 코드를 사용하여 피벗 테이블 필터를 특정 셀에 연결하기

셀과 피벗 테이블 필터 간의 직접적인 연결이 필요한 경우, 즉 셀 값이 변경될 때마다 피벗 테이블 필터가 자동으로 업데이트되도록 하려면 VBA를 사용하면 이를 실현할 수 있습니다. 이 방법은 사용자가 하나의 셀에서 데이터를 빠르게 제어할 수 있는 대화형 대시보드나 보고서에 적합합니다.

이 기술이 작동하려면 피벗 테이블에 필터 필드가 포함되어 있어야 합니다. 필터 필드의 이름은 VBA 코드를 올바르게 구성하는 데 중요합니다.

다음 예를 고려해 보세요: 피벗 테이블에는 "비용"과 "판매"라는 두 가지 필터 값이 있는 "카테고리"라는 필터 필드가 있습니다. 셀을 피벗 테이블 필터에 연결하면 선택한 셀에 "비용" 또는 "판매"를 입력하여 표시되는 데이터를 제어할 수 있습니다.

이를 구현하려면:

• 필터 컨트롤러로 사용할 셀(예: H6 셀)을 선택하고 미리 필터 값 중 하나를 입력하세요. 값이 피벗 테이블 필터 필드에 있는 값과 정확히 일치해야 합니다.
• 피벗 테이블이 포함된 워크시트로 이동하여 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 메뉴에서 '코드 보기'를 선택하세요. 그러면 Visual Basic for Applications 창이 열립니다.

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")) 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

참고:

코드를 붙여넣은 후 Alt + Q를 눌러 VBA 편집기 창을 닫고 Excel로 돌아갑니다.

이제 피벗 테이블의 필터 상태는 H6 셀의 내용에 의해 제어됩니다. H6 셀의 값(“판매” 또는 “비용”)을 변경하기만 해도 피벗 테이블의 표시가 즉시 업데이트됩니다. 문제가 발생하면 참조된 셀 값이 피벗 테이블의 필터 값과 정확히 일치하는지 확인하고, 코드의 이름이 올바르게 할당되었는지 다시 확인하세요.

셀의 내용을 수정할 때마다 피벗 테이블은 해당 필터링된 데이터를 새로 고칩니다.

팁 및 문제 해결: 셀의 필터 필드 값이 사용 가능한 항목과 정확히 일치하지 않으면(대소문자 및 공백 포함) 코드가 예상대로 필터를 적용하지 않을 수 있습니다. 항상 VBA 코드에서 필드 및 테이블 이름이 올바르게 철자되었는지 확인하세요. 이 설정을 여러 피벗 테이블에서 사용하려면 코드를 추가로 조정하거나 루프를 사용하여 확장할 수 있습니다.

Kutools AI로 엑셀의 마법을 풀다

• 스마트 실행: 셀 작업 수행, 데이터 분석 및 차트 생성 - 간단한 명령어로 모든 것을 처리합니다.
• 사용자 정의 수식: 작업을 간소화하기 위한 맞춤형 수식을 생성합니다.
• VBA 코딩: 손쉽게 VBA 코드를 작성하고 실행합니다.
• 수식 해석: 복잡한 수식도 쉽게 이해할 수 있습니다.
• 텍스트 번역: 스프레드시트 내 언어 장벽을 허물어 보세요.

AI 기반 도구로 엑셀 활용 능력을 강화하세요. 지금 다운로드하여 그 어느 때보다 높은 효율성을 경험해 보세요!

Excel 수식 - 슬라이서 또는 보고서 필터 참조와 함께 수식(GETPIVOTDATA 등) 사용

Excel에서는 피벗 테이블의 필터를 셀에 직접 바인딩하는 순수하게 네이티브한 수식 방법을 제공하지 않지만, GETPIVOTDATA와 같은 수식을 슬라이서 또는 보고서 필터와 결합하여 동적 보고 및 관련 값을 표시할 수 있습니다. 이 솔루션은 요약 값이 필터 선택 또는 다른 셀의 입력에 따라 즉시 업데이트되는 대시보드를 만들 때 유용하며, 데이터 분석을 더욱 대화형으로 만듭니다.

적용 가능한 시나리오에는 표시된 결과가 슬라이서 선택을 따르거나 셀 내용과 관련된 데이터를 반영하는 동적 보고 패널, 대시보드 또는 비교 요약이 포함됩니다. 이 방법의 주요 장점은 업데이트된 요약 데이터를 표시하는 데 효과적이라는 것입니다. 그러나 피벗 테이블의 실제 필터 상태는 셀 수식만으로 프로그래밍 방식으로 설정할 수 없습니다.

예: 셀 값에 기반한 피벗 테이블 요약 표시

카테고리별(예: “판매”, “비용”) 판매를 요약하는 피벗 테이블이 있다고 가정합니다. GETPIVOTDATA를 사용하여 셀에 지정된 카테고리에 대한 관련 값을 추출할 수 있습니다.

1. H6 셀에 표시하려는 카테고리(예: “판매”)가 있다고 가정합니다. 다음 수식을 요약 셀(예: I6)에 입력하세요:

=GETPIVOTDATA("Sum of Amount",$B$4,"Category",H6)

2. I6에 수식을 입력한 후 Enter를 누르세요. 이제 H6을 유효한 카테고리(예: “비용” 또는 “판매”)로 변경할 때마다 I6은 현재 피벗 테이블에 따라 해당 카테고리의 합계를 즉시 업데이트합니다.

팁: 이 방법은 피벗 테이블 자체 내부의 필터를 변경하지는 않지만, 셀에 의해 필터링된 것처럼 결과 데이터를 효과적으로 표시하여 대상 셀 입력과 연결된 동적 표시를 제공합니다. 이 방법을 사용하여 차트, 요약 테이블 또는 대시보드를 강화할 수도 있습니다.

문제 해결: 수식이 #REF! 또는 #VALUE! 오류를 반환하는 경우 셀 참조가 올바른지, 입력된 카테고리가 피벗 테이블에 존재하는지, 필드/합계 이름이 정확히 일치하는지 확인하세요.

기타 Excel 내장 메소드 - 피벗 테이블 슬라이서 및 대시보드 연결하여 상호작용형 필터링

Excel의 슬라이서 및 보고서 필터 도구는 VBA 코드를 작성하지 않고도 상호작용형 필터링을 위한 사용자 친화적이고 내장된 옵션을 제공합니다. 이러한 방법을 사용하여 여러 피벗 테이블이나 디스플레이를 하나 이상의 슬라이서에 연결하여 대시보드와 같은 효과를 얻을 수 있습니다.

일반적인 방법 중 하나는 피벗 테이블 필드(예: “카테고리”)에 연결된 슬라이서를 삽입하는 것입니다. 사용자는 슬라이서에서 원하는 항목을 클릭하기만 하면 피벗 테이블이 그에 따라 업데이트됩니다. 동일한 데이터 소스를 기반으로 하는 여러 피벗 테이블이 있는 경우 단일 슬라이서를 모든 테이블에 연결하여 동기화된 필터링을 수행할 수 있으며, 이는 보고 인터페이스를 더욱 직관적이고 일관성 있게 만듭니다.

슬라이서를 생성하고 연결하려면:

• 피벗 테이블을 클릭하고 피벗테이블 분석(또는 Excel 버전에 따라 옵션 탭) > 슬라이서 삽입으로 이동하세요.
• 원하는 필드(예: 카테고리)를 선택하고 확인을 클릭하세요. 슬라이서는 시트에 나타나며 사용자가 시각적으로 필터링할 수 있도록 합니다.
• 하나의 슬라이서를 여러 피벗 테이블에 연결하려면 슬라이서를 마우스 오른쪽 버튼으로 클릭하고 보고서 연결 (또는 피벗테이블 연결을 선택하고 동기화하려는 모든 피벗 테이블을 선택하세요.
  이는 다양한 시각화가 사용자 필터에 따라 함께 반응하는 대시보드 시나리오에서 특히 강력합니다.

장점: 대부분의 상호작용형 필터링 요구 사항에 매우 사용하기 쉽고 매크로 또는 사용자 정의 코드가 필요하지 않습니다. 단순성과 신뢰성이 중요한 대시보드 또는 공유 보고서에 적합합니다. 제한 사항은 절대적인 셀-필터 자동화(셀-필터 바인딩)가 기본적으로 지원되지 않는다는 점이며, 직접 값-필터 할당에는 VBA 또는 외부 도구가 필요합니다.

문제 해결: 슬라이서가 여러 피벗 테이블에 연결되지 않으면 모든 테이블이 동일한 캐시/데이터 소스에서 작성되었는지 확인하세요. 테이블이 호환되는 경우에만 보고서 연결 옵션이 표시됩니다.

요약 제안: 피벗 테이블 필터를 셀 값에 연결하거나 대화형 대시보드를 구축하기 위한 최적의 방법을 선택할 때 자동화 수준, Excel 버전 제한 및 환경에서 VBA/매크로가 허용되는지 여부를 고려하세요. 기본적인 요구 사항의 경우 슬라이서와 수식(GETPIVOTDATA)이 빠르고 강력한 결과를 제공합니다. 고급 자동화의 경우 VBA 솔루션이 더 큰 제어력을 제공합니다. 항상 필드 이름과 필터 항목이 정확한 결과를 위해 일관되게 사용되었는지 확인하세요. 오류가 발생하면 셀 입력 값을 확인하고 코드, 수식 및 데이터 세트 간의 모든 이름이 정확히 일치하는지 확인하세요.

관련 기사:

• Excel에서 여러 시트를 피벗 테이블로 결합하는 방법은 무엇입니까?
• Excel에서 텍스트 파일로부터 피벗 테이블을 생성하는 방법은 무엇입니까?
• Excel에서 특정 셀 값에 기반하여 피벗 테이블을 필터링하는 방법은 무엇입니까?