W tym samouczku zademonstrujemy, jak używać filtru tabeli przestawnej w języku VBA.
Tabele przestawne to wyjątkowo potężne narzędzie do obsługi danych w programie Excel. Tabele przestawne pozwalają nam analizować i interpretować duże ilości danych poprzez grupowanie i podsumowywanie pól i wierszy. Możemy zastosować filtry do naszych tabel przestawnych, aby umożliwić nam szybkie przeglądanie danych, które są dla nas istotne.
Po pierwsze, musimy stworzyć tabelę przestawną dla naszych danych. (Kliknij tutaj, aby zapoznać się z naszym przewodnikiem po tabelach przestawnych VBA).
Tworzenie filtra na podstawie wartości komórki
Możesz filtrować w tabeli przestawnej za pomocą VBA na podstawie danych zawartych w wartości komórki - możemy filtrować według pola Strona lub pola Wiersz (na przykład w polu Dostawca powyżej lub w polu Opera, które znajduje się w kolumnie Etykiety wierszy ).
W pustej komórce po prawej stronie tabeli przestawnej utwórz komórkę do przechowywania filtru, a następnie wpisz dane w komórce, według której chcesz filtrować tabelę przestawną.
Utwórz następujące makro VBA:
| 1234567 | Wartość strony filtra podrzędnego()Przyciemnij pvFld jako pole przestawneDim strFilter As StringUstaw pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Dostawca")strFilter = ActiveWorkbook.Sheets("Arkusz1").Range("M4").ValuepvFld.CurrentPage = filtr str.Napis końcowy |
Uruchom makro, aby zastosować filtr.
Aby wyczyścić filtr, utwórz następujące makro:
| 12345 | Wyczyść filtr pomocniczy()Dim pTbl jako tabela przestawnaUstaw pTbl = ActiveSheet.PivotTables("Tabela przestawna1")pTbl.Wyczyść wszystkie filtryNapis końcowy |
Filtr zostanie wtedy usunięty.
Następnie możemy zmienić kryteria filtrowania, aby filtrować według wiersza w tabeli przestawnej, a nie na bieżącej stronie.
Wpisanie następującego makra umożliwi nam filtrowanie w wierszu (zauważ, że polem przestawnym do filtrowania jest teraz Oper, a nie dostawca).
| 1234567 | Podrzędna wartość wiersza filtra()Przyciemnij pvFld jako pole przestawneDim strFilter As StringUstaw pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Oper")strFilter = ActiveWorkbook.Sheets("Arkusz1").Range("M4").ValuepvFld.PivotFilters.Add2 xlCaptionEquals, , strFilterNapis końcowy |
Uruchom makro, aby zastosować filtr.
Używanie wielu kryteriów w filtrze przestawnym
Możemy dodać do powyższego filtra wartości wiersza, dodając dodatkowe kryteria.
Ponieważ jednak standardowy filtr ukrywa wiersze, które nie są wymagane, musimy przejrzeć kryteria i pokazać te, które są wymagane, jednocześnie ukrywając te, które nie są wymagane. Odbywa się to poprzez utworzenie zmiennej Array i użycie kilku pętli w kodzie.
| 1234567891011121314151617181920212223 | Filtr podrzędnyMultipleRowItems()Dim vArray As VariantDim i As Integer, j As IntegerPrzyciemnij pvFld jako pole przestawneUstaw pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Oper")vArray = Zakres("M4:M5")pvFld.Wyczyść wszystkie filtryZ pvFldDla i = 1 To pvFld.PivotItems.Countj = 1Do While j <= UBound(vArray, 1) - LBound(vArray, 1) + 1Jeśli pvFld.PivotItems(i).Name = vArray(j, 1) WtedypvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = TrueWyjście WykonajW przeciwnym raziepvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = FalseZakończ, jeślij = j + 1PętlaDalej jaKończyć zNapis końcowy |
Tworzenie filtra na podstawie zmiennej
Możemy użyć tych samych koncepcji do tworzenia filtrów na podstawie zmiennych w naszym kodzie, a nie wartości w komórce. Tym razem zmienna filtru (strFilter) jest umieszczana w samym kodzie (np.: Zakodowana na sztywno w makrze).
| 1234567 | Sub FiltrTekstWartości()Przyciemnij pvFld jako pole przestawneDim strFilter As StringUstaw pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Dostawca")strFilter = "THOMAS S"pvFld.CurrentPage = filtr str.Napis końcowy |
