วิธีกรอง pivottable โดยใช้ vba (พร้อมตัวอย่าง)

คุณสามารถใช้วิธีการต่อไปนี้เพื่อกรองตารางสาระสำคัญใน Excel โดยใช้ VBA:

วิธีที่ 1: กรองตารางสาระสำคัญตามค่า

 SubFilterPivotTable ()
   Dim pf As PivotField
   Dim myFilter As String
   Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
   myFilter = ActiveWorkbook.Sheets(" Sheet1 ").Range(" J2 ").Value
   pf.PivotFilters.Add2 xlCaptionEquals, , myFilter
End Sub

มาโครนี้จะกรองตาราง Pivot ที่เรียกว่า PivotTable1 เพื่อแสดงเฉพาะแถวโดยที่ค่าในคอลัมน์ ตำแหน่ง ของตาราง Pivot เท่ากับค่าในเซลล์ J2 ของ Sheet1

วิธีที่ 2: กรอง PivotTable ตามค่าหลายค่า

 Sub FilterPivotTableMultiple()
  Dim v Ace Variant
  Dim i As Integer, j As Integer
  Dim pf As PivotField
  Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
  
  'specify range with values to filter on
  v = Range(" J2:J3 ")
  
  'clear existing filters
  pf.ClearAllFilters
  
  'apply filter to pivot table
  With pf
    For i = 1 TB pf.PivotItems.Count
      j = 1
      Do While j <= UBound(v, 1) - LBound(v, 1) + 1
         If pf.PivotItems(i).Name = v(j, 1) Then
           pf.PivotItems(pf.PivotItems(i).Name).Visible = True
           Exit Do
        Else
          pf.PivotItems(pf.PivotItems(i).Name).Visible = False
       End If
        j = j + 1
      Loop
    Next i
 End With
End Sub

มาโครนี้จะกรองตาราง Pivot ที่เรียกว่า PivotTable1 เพื่อแสดงเฉพาะแถวโดยที่ค่าในคอลัมน์ ตำแหน่ง ของตาราง Pivot เท่ากับค่าใดค่าหนึ่งในช่วงเซลล์ J2:J3

วิธีที่ 3: เอาตัวกรอง PivotTable ออก

 SubClearPivotTableFilter ()
   Dim pt As PivotTable
   Set pt = ActiveSheet.PivotTables(" PivotTable1 ")
   pt.ClearAllFilters
End Sub

มาโครนี้จะล้างตัวกรองทั้งหมดออกจากตารางสรุปที่เรียกว่า PivotTable1

ตัวอย่างต่อไปนี้แสดงวิธีการใช้แต่ละวิธีในทางปฏิบัติ

ตัวอย่างที่ 1: กรองตารางสรุปตามค่า

สมมติว่าเราสร้างตารางสรุปผลจากชุดข้อมูลใน Excel เพื่อสรุปคะแนนที่ผู้เล่นบาสเก็ตบอลจากทีมและตำแหน่งต่างๆ ทำได้:

สมมติว่าเราต้องการกรองตารางสรุปเพื่อแสดงเฉพาะแถวที่มีค่าในคอลัมน์ตำแหน่งเป็น Guard

เราสามารถสร้างมาโครต่อไปนี้เพื่อทำสิ่งนี้:

 SubFilterPivotTable ()
   Dim pf As PivotField
   Dim myFilter As String
   Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
   myFilter = ActiveWorkbook.Sheets(" Sheet1 ").Range(" J2 ").Value
   pf.PivotFilters.Add2 xlCaptionEquals, , myFilter
End Sub

เมื่อเราเรียกใช้มาโครนี้ ตาราง Pivot จะถูกกรองโดยอัตโนมัติเพื่อแสดงเฉพาะแถวที่มีค่าในคอลัมน์ตำแหน่งเป็น Guard:

ตารางสรุปได้รับการกรองเพื่อแสดงเฉพาะแถวที่มีค่าในคอลัมน์ตำแหน่งเป็น Guard

ตัวอย่างที่ 2: กรองตารางสรุปข้อมูลตามค่าหลายค่า

สมมติว่าเราต้องการกรองตารางสรุปเพื่อแสดงเฉพาะแถวที่มีค่าในคอลัมน์ตำแหน่งเป็น Guard หรือ Center

เราสามารถสร้างมาโครต่อไปนี้เพื่อทำสิ่งนี้:

 Sub FilterPivotTableMultiple()
  Dim v Ace Variant
  Dim i As Integer, j As Integer
  Dim pf As PivotField
  Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
  
  'specify range with values to filter on
  v = Range(" J2:J3 ")
  
  'clear existing filters
  pf.ClearAllFilters
  
  'apply filter to pivot table
  With pf
    For i = 1 TB pf.PivotItems.Count
      j = 1
      Do While j <= UBound(v, 1) - LBound(v, 1) + 1
         If pf.PivotItems(i).Name = v(j, 1) Then
           pf.PivotItems(pf.PivotItems(i).Name).Visible = True
           Exit Do
        Else
          pf.PivotItems(pf.PivotItems(i).Name).Visible = False
       End If
        j = j + 1
      Loop
    Next i
 End With
End Sub

เมื่อเราเรียกใช้มาโครนี้ ตาราง Pivot จะถูกกรองโดยอัตโนมัติเพื่อแสดงเฉพาะแถวที่มีค่าในคอลัมน์ตำแหน่งเป็น Guard หรือ Center:

ตารางสรุปได้รับการกรองเพื่อแสดงเฉพาะแถวที่มีค่าในคอลัมน์ตำแหน่งเป็น Guard หรือ Center

แหล่งข้อมูลเพิ่มเติม

บทช่วยสอนต่อไปนี้จะอธิบายวิธีการทำงานทั่วไปอื่นๆ ใน VBA:

VBA: วิธีรีเฟรชตารางเดือย
VBA: วิธีลบค่าที่ซ้ำกัน
VBA: วิธีนับจำนวนแถวในช่วง