VBA – 피벗 테이블 필터

이 튜토리얼에서는 VBA에서 피벗 테이블 필터를 사용하는 방법을 보여줍니다.

피벗 테이블은 Excel의 매우 강력한 데이터 도구입니다. 피벗 테이블을 사용하면 필드와 행을 그룹화하고 요약하여 많은 양의 데이터를 분석하고 해석할 수 있습니다. 피벗 테이블에 필터를 적용하면 관련 데이터를 빠르게 확인할 수 있습니다.

먼저 데이터에 대한 피벗 테이블을 만들어야 합니다. (VBA 피벗 테이블 가이드를 보려면 여기를 클릭하세요)

셀 값에 기반한 필터 만들기

이 예제에서는 셀 값을 기준으로 피벗 테이블을 필터링합니다.

피벗 테이블 오른쪽의 빈 셀에 필터를 입력할  셀을 만들고 피벗 테이블을 필터링할 데이터를 해당 셀에 입력합니다.

 

다음과 같이 VBA 매크로를 만듭니다:

Sub FilterPageValue()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Supplier")
   strFilter = ActiveWorkbook.Sheets("Sheet1").Range("M4").Value
   pvFld.CurrentPage = strFilter
End Sub

매크로를 실행하여 필터를 적용합니다.

필터를 지우려면 다음 매크로를 참고해 주세요:

Sub ClearFilter()
   Dim pTbl As PivotTable
   Set pTbl = ActiveSheet.PivotTables("PivotTable1")
   pTbl.ClearAllFilters
End Sub

매크로를 실행하면 필터가 제거됩니다.

그런 다음 현재 필터가 아닌 피벗 테이블의 행을 필터링하도록 필터 기준을 수정할 수 있습니다.

 

다음 코드를 실행하면 피벗테이블의 행을 필터링할 수 있습니다(필터링할 피벗 필드는 이제 “Supplier”가 아닌 “Open”입니다).

Sub FilterRowValue()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Oper")
   strFilter = ActiveWorkbook.Sheets("Sheet1").Range("M4").Value
   pvFld.PivotFilters.Add2 xlCaptionEquals, , strFilter
End Sub

매크로를 실행하여 필터를적용합니다.

피벗 필터에서 여러 기준 사용하기

위의 행 값 필터에 기준을 추가하여 피벗테이블의 필터로 적용할 수 있습니다.

표준 필터는 필요하지 않은 행을 숨기므로 기준을 반복하여 필요한 행은 표시하고 필요하지 않은 행은 숨겨야 합니다. 이 작업은 배열 변수를 만들고 코드에서 두 개의 중첩된 반복문을 사용하여 수행합니다.

Sub FilterMultipleRowItems()
  Dim vArray As Variant
  Dim i As Integer, j As Integer
  Dim pvFld As PivotField
  Set pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Oper")
  vArray = Range("M4:M5")
  pvFld.ClearAllFilters

  With pvFld
    For i = 1 To pvFld.PivotItems.Count
      j = 1
       Do While j <= UBound(vArray, 1) - LBound(vArray, 1) + 1
         If pvFld.PivotItems(i).Name = vArray(j, 1) Then
           pvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = True
           Exit Do
        Else
          pvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = False
        End If
        j = j + 1
      Loop
    Next i
  End With
End Sub

변수를 기반으로 필터 만들기

동일한 개념을 사용하여 셀의 값이 아닌 코드의 변수를 기반으로 필터를 만들 수 있습니다. 이번에는 필터 변수(strFilter)가 코드 자체에 채워집니다(이 예제에서는 필터변수를 하드코딩하였습니다).

Sub FilterTextValue()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Supplier")
   strFilter = "THOMAS S"
   pvFld.CurrentPage = strFilter
End Sub