Tabele przestawne są jednym z najpotężniejszych narzędzi w Excelu. W praktyce zaobserwowałem jeden drobny problem związany z ich aktualizacją. Odświeżenie tabeli przestawnej czasami powoduje usunięcie założonych na nią filtrów. Jak tego uniknąć? O tym dowiesz się w tym tekście.
Odświeżenie gubi filtry
W praktyce przeważnie korzystam z rozwiązania, w którym tabela przestawna jest już utworzona w arkuszu pomocniczym. Po zaczytaniu nowych danych, jest ona tylko odświeżana. Nie ma bowiem potrzeby, aby tworzyć ją, za każdym razem od nowa makrem.
Często interesują mnie cząstkowe wyniki, które mogę uzyskać zakładając filtry. Gdy mam przefiltrowaną tabelę przestawną – mogę za pomocą prostej formuły zaczytać wyniki do docelowej tabeli.
Rozwiązanie sprytne, ale czasami rodzi pewien istotny problem…. Otóż, w momencie gdy makro odświeża tabelę przestawną, ona czasami… lubi gubić filtry. Jest to dośc niebezpieczne z naszego punktu widzenia. 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 odświeżenie tabeli przestawnej gubi filtry i zamiast danych cząstkowych otrzymujemy dane pełne…
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.
Odświeżenie buforów i dodanie filtrów
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
Private Const msMODUL As String = "MOdswiezeniePivota" Option Explicit 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 |
Wynik działania makra
