Odśwież tabelę przestawną w VBA
Zwykle tworzymy tabelę przestawną, gdy potrzebujemy stworzyć jakieś wykresy lub wykresy lub przeprowadzić na niej analizę. Przygotowując tabelę przestawną, możemy uzyskać ogólny widok i wyobrażenie o tym, co faktycznie znajduje się w danych. Jest to najlepszy sposób na znalezienie sposobu na uzyskanie dostępu do danych. I za każdym razem, gdy wprowadzamy jakiekolwiek zmiany w danych, musimy również odświeżyć tabelę przestawną. W ten sposób zapełni również zaktualizowaną liczbę danych. Odświeżenie dowolnej tabeli przestawnej jest bardzo łatwe, ale co jeśli mamy 10s tabel przestawnych w jednym pliku Excela, który musimy zaktualizować. Zamiast więc odświeżać wszystkie tabele przestawne jeden po drugim, możemy bezpośrednio odświeżyć wszystkie tabele przestawne za jednym razem za pomocą VBA Refresh Pivot Table.
Jak korzystać z odświeżania tabeli przestawnej w programie Excel VBA?
Poniżej znajdują się różne przykłady użycia Odśwież tabelę przestawną w programie Excel przy użyciu kodu VBA.
Możesz pobrać ten szablon Excela Odśwież tabelę przestawną VBA tutaj - Szablon Excela Odśwież tabelę przestawną VBATabela przestawna odświeżania VBA - przykład nr 1
Aby odświeżyć tabelę przestawną, najpierw musimy utworzyć tabelę przestawną. Poniżej mamy dane elementu zamówienia 1000s, za pomocą którego będziemy tworzyć tabelę przestawną.
Na powyższym zrzucie ekranu widzimy ostatni wiersz na 1001, a dane te zawierają informacje o klientach w stosunku do ilości sprzedanej im.
Teraz przejdź do zakładki menu Wstaw i kliknij opcję Tabela przestawna .
Otrzymamy pole Utwórz tabelę przestawną. Stamtąd najpierw wybierz zakres tabeli, który chcemy uwzględnić w tabeli przestawnej. Następnie wybierz dowolną lokalizację w bieżącym arkuszu dla tabeli przestawnej. Możemy również wybrać nowy arkusz roboczy.
Po zakończeniu kliknij OK.
Utworzymy tabelę przestawną. Teraz przeciągnij wymagane pola do różnych obszarów, aby uzyskać rzeczywistą tabelę przestawną. Przeciągamy tutaj Nazwę klienta do WIERSZY, a Ilość do KOLUMN, jak pokazano poniżej.
To kończy tworzenie tabeli przestawnej. Musimy jednak zaktualizować tabelę przestawną po zmianie czegokolwiek w danych, można to zrobić po prostu odświeżając opcję z listy rozwijanej menu po kliknięciu prawym przyciskiem myszy, jak pokazano poniżej.
Ale ten sam proces można również zautomatyzować za pomocą VBA. Do tego potrzebowalibyśmy modułu. Więc,
Krok 1: Przejdź do zakładki menu Wstaw i wybierz opcję Moduł z rozwijanej listy, jak pokazano poniżej.
Krok 2: W nowo otwartym module napisz podkategorię VBA Pivot Refresh lub możemy wybrać dowolną nazwę według naszego wyboru.
Kod:
Sub Pivot_Refresh2 () End Sub
Krok 3: Najpierw zdefiniuj zmienną jako PivotCache, jak pokazano poniżej. PivotCache używa pamięci podręcznej Pivot, a nie danych używanych do tworzenia tabeli przestawnej.
Kod:
Sub Pivot_Refresh2 () Dim Table Jako PivotCache End Sub
Krok 4: Teraz użyjemy pętli For-Each . Więc otwórz pętlę For-Each, jak pokazano poniżej.
Kod:
Sub Pivot_Refresh2 () Dim Table jako PivotCache dla każdej następnej tabeli Sub Sub
Krok 5: Teraz w pętli For-End napiszemy warunek, w którym wybierzemy aktualnie otwarty arkusz roboczy, który ma tabelę przestawną z PivotCache .
Kod:
Sub Pivot_Refresh2 () Dim Table jako PivotCache dla każdej tabeli w ThisWorkbook.PivotCaches Następny koniec tabeli Sub
Krok 6: Teraz użyjemy polecenia Refresh, przypisując mu zdefiniowaną tabelę zmiennych.
Kod:
Sub Pivot_Refresh2 () Dim Table as PivotCache For Each Table In ThisWorkbook.PivotCaches Table.Refresh Next Table End Sub
Krok 7: To kończy kod, teraz skompiluj kod do błędu pliku, naciskając klawisz funkcyjny F8. Teraz, aby sprawdzić, czy kod napisany nie działa, zmieniliśmy liczbę Klientów 14 na 69 .
Krok 8: Teraz uruchom kod. Zobaczymy, że łączna liczba w stosunku do Customer14 została zaktualizowana do 2183, która jest podświetlona na żółty kolor.
Tabela przestawna odświeżania VBA - przykład 2
Istnieje inny sposób odświeżenia tabeli przestawnej za pomocą VBA. Zanim przejdziemy do przodu, możemy rozważyć zmianę nazwy tabeli przestawnej lub możemy użyć nazwy domyślnej. Spróbujmy nadać nową nazwę tabeli przestawnej. W tym celu wybierz tabelę przestawną i przejdź do karty menu Analizuj, jak pokazano poniżej.
W pierwszej sekcji Nazwy tabeli przestawnej widzimy domyślną nazwę jako Tabela przestawna1 .
Teraz zmienimy tę nazwę. Rozważ zapisanie nowej nazwy jako Danych klienta, jak pokazano poniżej.
Krok 1: Następnie otwórz nowy moduł i napisz podkategorię VBA Refresh, jak pokazano poniżej.
Kod:
Sub Pivot_Refresh3 () End Sub
Krok 2: Teraz zdefiniuj zmienną jako tabelę przestawną . W tabeli przestawnej uwzględnimy pełne dane źródłowe.
Kod:
Sub Pivot_Refresh3 () Dim Table Jako PivotTable End Sub
Krok 3: Teraz użyj Ustaw ze zdefiniowaną zmienną i wybierz arkusz, który jest aktualnie otwarty.
Kod:
Sub Pivot_Refresh3 () Dim Table As Table PivotTable Set Table = ActiveSheet. Napis końcowy
Krok 4: Wybierz nazwę tabeli przestawnej, którą chcemy odświeżyć, wraz ze zmiennym typem danych, którego użyliśmy jako tabelę przestawną .
Kod:
Sub Pivot_Refresh3 () Dim Table As Table PivotTable Set Table = ActiveSheet.PivotTables („Data Customer”) End Sub
Krok 5: W końcu użyj zmiennej z funkcją RefreshTable, aby ją wykonać.
Kod:
Sub Pivot_Refresh3 () Dim Table As Table PivotTable Set Table = ActiveSheet.PivotTables („Dane klienta”) Table.RefreshTable End Sub
Krok 6: Dokonajmy dalszych zmian w danych, aby uzyskać rzeczywistą grafikę zastosowanego kodu.
Zmieniliśmy liczbę Klientów 2 na 56 . Teraz, jeśli uruchomimy kod, w tabeli przestawnej nazwy klienta, powinny nastąpić zmiany w sumie ilości dla klienta2 .
Krok 7: Teraz przejdź do okna VBA i skompiluj kod. Jeśli nie znaleziono błędu, uruchom go, klikając przycisk odtwarzania znajdujący się poniżej paska menu, jak pokazano poniżej. Zauważymy, że suma ilości sprzedanej klientowi 2 wzrasta teraz do 1724, dokonując zmian w tabeli źródłowej.
Dzięki temu możemy uwzględnić więcej niż jedno źródło danych i stworzyć inną tabelę przestawną. Zautomatyzowanie tych tabel przestawnych jest również łatwe, ponieważ wystarczy podać nazwę tabeli przestawnej i arkusz, w którym znajduje się tabela.
Plusy tabeli przestawnej odświeżania VBA
- Odświeżenie tabeli przestawnej za pomocą kodu VBA zajmuje znacznie mniej czasu.
- Jest bardzo łatwy do wdrożenia.
Rzeczy do zapamiętania
- Możemy dodać tabelę wielu źródeł danych i zautomatyzować je za pomocą kodu VBA.
- Aby zobaczyć zmiany, które się wydarzyły, trzymaj okno VBA i arkusz Excel równolegle względem siebie.
- Lepiej jest nazwać każdą tabelę przestawną, jeśli masz do czynienia z wieloma źródłami danych i tabelami przestawnymi i obsługujesz je.
- Kod z nazwą tabeli przestawnej jest łatwy do zrozumienia, śledzenia i zlokalizowania.
Polecane artykuły
Jest to przewodnik po VBA Refresh Pivot Table. Tutaj omawiamy sposób korzystania z programu Excel VBA Odśwież tabelę przestawną wraz z praktycznymi przykładami i szablonem programu Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -
- VBA FileSystemObject (FSO)
- Funkcja Excel podciągu
- Długość ciągu VBA
- Formuła Excel ISNUMBER