Tabele przestawne pozwalają bardzo uprościć naszą pracę. Zamiast tworzyć skomplikowane formuły np. do sumowania danych pod kątem różnych zmiennych, można użyć popularnego “pivota”.
Usunięcie Filtrów
W praktyce bardzo często korzystam z rozwiązania, w którym tabela przestawna jest już utworzona w arkuszu pomocniczym, zaś po zaczytaniu nowych danych, jest ona tylko odświeżana. Nie ma bowiem potrzeby, aby tworzyć ją od nowa za każdym razem.
Zazwyczaj interesują mnie wyniki tylko dla pewnego obszaru danych, cząstkowe informacje, które mogę uzyskać zakładając filtry. Gdy już mam przefiltrowaną tabelę przestawną, mogę za pomocą bardzo prostej formuły zaczytać wyniki do docelowej tabeli.
Rozwiązanie bardzo sprytne, ale czasami rodzi pewien istotny problem…. Otóż, w momencie gdy tabela przestawna jest odświeżana, czasami zdarza się, że … gubione są filtry. Jest to bardzo niebezpieczne ponieważ przechwytujemy wtedy błędną informację i przekazujemy ją dalej. Jak sobie z tym radzić?
Makro Dodające Filtry
Załóżmy, że mamy raport, który przedstawia poziom zapasów dla produktów na czterech magazynach. My chcemy zsumować zapas, ale tylko dla magazynów “A” i “B”.
W tym celu założyliśmy filtr na tabelę przestawną i zaznaczyliśmy tylko te dwa magazyny. Po zaimportowaniu nowych danych do tabeli źródłowej, uruchamiamy makro OdswiezBufory, które aktualizuje nam tabelę przestawną.
Właśnie w tym momencie często zdarza się, że nasze filtry są gubione i wyświetlane są wszystkie dane. Aby temu zapobiec możemy uruchomić makro UstawFiltryNaMagazyny, które wyświetla dane dla magazynu “A” lub “B” i zarazem ukrywa dane dla pozostałych magazynów.
Private Const msMODUL As String = "MOdswiezeniePivota"
Option Explicit
Public Sub OdswiezBufory()
'// Makro odświeża wszystkie bufory w pliku
Dim pvcBufor As PivotCache ' Zmienna obiektowa przechowująca obiekt bufora
1 On Error Resume Next ' Może w pliku nie być żadnych buforów
2 For Each pvcBufor In ThisWorkbook.PivotCaches ' Pętla po buforach
3 With pvcBufor
4 .MissingItemsLimit = xlMissingItemsNone ' Czyści filtry ze starych wartości
5 .Refresh ' Odświeża bufor
6 End With
7 Next pvcBufor
End Sub
Public Sub UstawFiltryNaMagazyny()
'// Makro zakłada filtr na magazyny "A" i "B"
Dim pvtPivot As PivotTable 'Obiekt tabeli przestawnej ("pvtZapasy")
Dim pvtField As PivotField 'Kolumna z magazynem
Dim pvtWpis As PivotItem 'Pojedyncza wartość dla kolumny z magazynem
Const sPROC As String = "UstawFiltryNaMagazyny"
'Aktywuj obsługę błędów na starcie
1 On Error GoTo ObslugaBledu
'Zdefiniuj zmienne obiektowe
2 Set pvtPivot = wksZapasy.PivotTables("pvtZapasy")
3 Set pvtField = pvtPivot.PivotFields("Magazyn")
'Przejedź w pętli po wszystkich magazynach - pokaż tylko "A" i "B"
4 For Each pvtWpis In pvtField.PivotItems
'Pokaż magazyny A i B
5 Select Case pvtWpis.Name
Case "A", "B"
6 pvtWpis.Visible = True
7 Case Else
8 pvtWpis.Visible = False
9 End Select '// Select Case pvtWpis.Name
10 Next pvtWpis '// For Each pvtWpis In pvtField.PivotItems
Wyjscie:
11 Set pvtPivot = Nothing
12 Set pvtField = Nothing
13 Set pvtWpis = Nothing
14 On Error GoTo 0
15 Exit Sub
ObslugaBledu:
16 MsgBox "Wystąpił błąd nr " & Err.Number & " (" & Err.Description & ")." & _
vbCr & vbCr & "Linia kodu nr " & Erl & " w procedurze " & _
"'" & sPROC & "' modułu '" & msMODUL & "'.", vbInformation, "BŁĄD!"
17 Resume Wyjscie
End Sub
Tabela Wynikowa
