VBA를 사용하여 Excel 피벗 테이블 필터링

VBA를 사용하여 Excel 피벗 테이블 필터링

저는 VBA를 사용하여 엑셀의 피벗 테이블을 필터링하기 위해 인터넷에서 솔루션을 복사하고 붙여넣기를 계속 시도했습니다.아래 코드가 작동하지 않습니다.

Sub FilterPivotTable()
    Application.ScreenUpdating = False
    ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True
    ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").CurrentPage = "K123223"
    ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False
    Application.ScreenUpdating = True
End Sub

Saved Family Code K123223이 있는 모든 행을 볼 수 있도록 필터링하고 싶습니다.피벗 테이블의 다른 행은 보고 싶지 않습니다.저는 이것이 이전 필터와 상관없이 작동하기를 원합니다.저는 당신이 이 일을 도와주길 바랍니다.감사합니다!

---

당신이 올린 글에 근거해서 노력하고 있습니다.

Sub FilterPivotField()
    Dim Field As PivotField
    Field = ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode")
    Value = Range("$A$2")
    Application.ScreenUpdating = False
    With Field
        If .Orientation = xlPageField Then
            .CurrentPage = Value
        ElseIf .Orientation = xlRowField Or .Orientation = xlColumnField Then
            Dim i As Long
            On Error Resume Next ' Needed to avoid getting errors when manipulating fields that were deleted from the data source.
            ' Set first item to Visible to avoid getting no visible items while working
            .PivotItems(1).Visible = True
            For i = 2 To Field.PivotItems.Count
                If .PivotItems(i).Name = Value Then _
                    .PivotItems(i).Visible = True Else _
                    .PivotItems(i).Visible = False
            Next i
            If .PivotItems(1).Name = Value Then _
                .PivotItems(1).Visible = True Else _
                .PivotItems(1).Visible = False
        End If
    End With
    Application.ScreenUpdating = True
End Sub

안타깝게도 Run time 오류 91: 개체 변수 또는 With block variable not set.이 오류의 원인은 무엇입니까?

Field.CurrentPage는 필터 필드(페이지 필드라고도 함)에만 적용됩니다.
행/열 필드를 필터링하려면 다음과 같이 개별 항목을 순환해야 합니다.

Sub FilterPivotField(Field As PivotField, Value)
    Application.ScreenUpdating = False
    With Field
        If .Orientation = xlPageField Then
            .CurrentPage = Value
        ElseIf .Orientation = xlRowField Or .Orientation = xlColumnField Then
            Dim i As Long
            On Error Resume Next ' Needed to avoid getting errors when manipulating PivotItems that were deleted from the data source.
            ' Set first item to Visible to avoid getting no visible items while working
            .PivotItems(1).Visible = True
            For i = 2 To Field.PivotItems.Count
                If .PivotItems(i).Name = Value Then _
                    .PivotItems(i).Visible = True Else _
                    .PivotItems(i).Visible = False
            Next i
            If .PivotItems(1).Name = Value Then _
                .PivotItems(1).Visible = True Else _
                .PivotItems(1).Visible = False
        End If
    End With
    Application.ScreenUpdating = True
End Sub

그러면 그냥 전화를 걸게 될 겁니다.

FilterPivotField ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode"), "K123223"

당연히 현장에 개별적으로 다른 아이템들이 많을수록 이 속도는 느려집니다.필요에 따라 이름 대신 소스 이름을 사용할 수도 있습니다.

피벗 테이블을 다음과 같이 구성합니다.

이제 코드가 범위("B1")에서 간단히 작동할 수 있으며 피벗 테이블이 필요한 Saveed Family Code로 필터링됩니다.

Sub FilterPivotTable()
Application.ScreenUpdating = False
    ActiveSheet.Range("B1") = "K123224"
Application.ScreenUpdating = True
End Sub

원하시면 확인 가능합니다. :)

SaveedFamilyCode가 Report Filter에 있는 경우 다음 코드를 사용합니다.

 Sub FilterPivotTable()
   Application.ScreenUpdating = False
   ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True

   ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").ClearAllFilters

   ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").CurrentPage = _
      "K123223"

  ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False
  Application.ScreenUpdating = True
  End Sub

그러나 저장된 패밀리 코드가 열 또는 행 레이블에 있는 경우 다음 코드를 사용합니다.

 Sub FilterPivotTable()
     Application.ScreenUpdating = False
     ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True

      ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").ClearAllFilters

      ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").PivotFilters. _
    Add Type:=xlCaptionEquals, Value1:="K123223"

  ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False
  Application.ScreenUpdating = True
  End Sub

도움이 되길 바랍니다.

저는 당신의 질문을 이해하고 있다고 생각합니다.열 레이블 또는 행 레이블에 있는 항목을 필터링합니다.코드의 마지막 두 섹션은 당신이 원하는 것이지만 당신이 어떻게 실행되는지 정확히 볼 수 있도록 모든 것을 붙여넣는 것입니다. 정의된 모든 것으로 마무리하기 시작합니다.저는 분명히 이 코드를 다른 사이트에서 가져왔습니다. fyi.

코드의 끝에 가까운 "WardClinic_Category"는 피벗 테이블의 열 레이블과 내 데이터의 열입니다.IVUDDC 인디케이터(내 데이터에는 열이지만 피벗 테이블의 행 레이블에는 있음)에도 마찬가지입니다.

이것이 다른 사람들에게 도움이 되기를 바랍니다... 매크로 레코더와 비슷한 코드를 사용하지 않고 "적절한 방식"으로 처리한 코드를 찾는 것이 매우 어렵다는 것을 알았습니다.

Sub CreatingPivotTableNewData()


'Creating pivot table
Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField

'determine the worksheet which contains the source data
Set wsData = Worksheets("Raw_Data")

'determine the worksheet where the new PivotTable will be created
Set wsPvtTbl = Worksheets("3N3E")

'delete all existing Pivot Tables in the worksheet
'in the TableRange1 property, page fields are excluded; to select the entire PivotTable report, including the page fields, use the TableRange2 property.
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Delete existing PivotTable!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl


'A Pivot Cache represents the memory cache for a PivotTable report. Each Pivot Table report has one cache only. Create a new PivotTable cache, and then create a new PivotTable report based on the cache.

'set source data range:
Worksheets("Raw_Data").Activate
Set rngData = wsData.Range(Range("A1"), Range("H1").End(xlDown))


'Creates Pivot Cache and PivotTable:
Worksheets("Raw_Data").Activate
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData.Address, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")

'Default value of ManualUpdate property is False so a PivotTable report is recalculated automatically on each change.
'Turn this off (turn to true) to speed up code.
PvtTbl.ManualUpdate = True


'Adds row and columns for pivot table
PvtTbl.AddFields RowFields:="VerifyHr", ColumnFields:=Array("WardClinic_Category", "IVUDDCIndicator")

'Add item to the Report Filter
PvtTbl.PivotFields("DayOfWeek").Orientation = xlPageField


'set data field - specifically change orientation to a data field and set its function property:
With PvtTbl.PivotFields("TotalVerified")
.Orientation = xlDataField
.Function = xlAverage
.NumberFormat = "0.0"
.Position = 1
End With

'Removes details in the pivot table for each item
Worksheets("3N3E").PivotTables("PivotTable1").PivotFields("WardClinic_Category").ShowDetail = False

'Removes pivot items from pivot table except those cases defined below (by looping through)
For Each PivotItem In PvtTbl.PivotFields("WardClinic_Category").PivotItems
    Select Case PivotItem.Name
        Case "3N3E"
            PivotItem.Visible = True
        Case Else
            PivotItem.Visible = False
        End Select
    Next PivotItem


'Removes pivot items from pivot table except those cases defined below (by looping through)
For Each PivotItem In PvtTbl.PivotFields("IVUDDCIndicator").PivotItems
    Select Case PivotItem.Name
        Case "UD", "IV"
            PivotItem.Visible = True
        Case Else
            PivotItem.Visible = False
        End Select
    Next PivotItem

'turn on automatic update / calculation in the Pivot Table
PvtTbl.ManualUpdate = False


End Sub

엑셀의 최신 버전에는 슬라이서라는 새로운 도구가 있습니다.VBA에서 슬라이서를 사용하는 것이 실제로 더 신뢰할 수 있습니다.CurrentPage(수많은 필터 옵션을 루프하는 동안 버그 보고가 있었습니다).슬라이서 항목을 선택하는 간단한 예는 다음과 같습니다(관련이 없는 슬라이서 값을 모두 선택 취소해야 함).

Sub Step_Thru_SlicerItems2()
Dim slItem As SlicerItem
Dim i As Long
Dim searchName as string

Application.ScreenUpdating = False
searchName="Value1"

    For Each slItem In .VisibleSlicerItems
        If slItem.Name <> .SlicerItems(1).Name Then _
            slItem.Selected = False
        Else
            slItem.Selected = True
        End if
    Next slItem
End Sub

스마트 카토와 같은 서비스는 대시보드나 보고서를 설정하거나 코드를 수정하는 데 도움이 됩니다.

Excel 2007 이후 버전에서는 더 정확한 참조를 사용하여 훨씬 더 간단한 코드를 사용할 수 있습니다.

dim pvt as PivotTable
dim pvtField as PivotField

set pvt = ActiveSheet.PivotTables("PivotTable2")
set pvtField = pvt.PivotFields("SavedFamilyCode")

pvtField.PivotFilters.Add xlCaptionEquals, Value1:= "K123223"

언급URL : https://stackoverflow.com/questions/11071662/filter-excel-pivot-table-using-vba