Excel VBA Odśwież tabelę przestawną

Kiedy wstawiamy tabelę przestawną do arkusza, gdy dane zmieniają się dane tabeli przestawnej nie zmieniają się same, musimy to zrobić ręcznie, ale w VBA istnieje instrukcja odświeżania tabeli przestawnej, która jest wyrażeniem. Odświeżanie, używając tego możemy odświeżyć tabela przestawna, odwołując się do arkusza, w którym się znajduje, lub możemy odwołać się do całych tabel przestawnych w arkuszach i odświeżyć je wszystkie naraz.

Tabela przestawna jest niezbędna do analizy ogromnej ilości danych. Pomaga już w analizowaniu, podsumowywaniu, a także dokonywaniu użytecznej interpretacji danych. Jednak jednym z problemów z tą tabelą przestawną jest to, że nie zostanie ona automatycznie odświeżona w przypadku jakiejkolwiek zmiany w danych źródłowych, użytkownik musi odświeżyć tabelę przestawną, przechodząc do konkretnej tabeli przestawnej za każdym razem, gdy nastąpi zmiana. Pożegnaj się jednak z procesem ręcznym, ponieważ tutaj mamy metodę odświeżania tabeli przestawnej, gdy tylko wprowadzisz jakąkolwiek zmianę w tabeli przestawnej.

Jak automatycznie odświeżyć kod VBA danych tabeli przestawnej?

Tabela przestawna wymaga aktualizacji tylko wtedy, gdy nastąpią jakiekolwiek zmiany w danych źródłowych tabeli przestawnej, do której się odnosimy.

Na przykład spójrz na poniższe dane i tabelę przestawną.

Teraz zmienię liczby w danych źródłowych tj. Z A1 na B17.

W komórce B9 muszę zmienić wartość z 499 na 1499, tj. 1000 wzrostu danych, ale jeśli spojrzysz na pivot, nadal pokazuje wynik jako 4295 zamiast 5295. Muszę ręcznie odświeżyć moją tabelę przestawną, aby zaktualizować tabelę przestawną.

Aby rozwiązać ten problem, musimy napisać prosty kod makra programu Excel, aby odświeżyć tabelę przestawną po każdej zmianie w danych źródłowych.

Możesz pobrać ten szablon Excel odświeżania tabeli przestawnej VBA tutaj - szablon Excel odświeżania tabeli przestawnej VBA

# 1 - Proste makro do odświeżania całej tabeli

Krok 1: Zmień wydarzenie w arkuszu danych

Musimy wywołać zdarzenie zmiany w arkuszu danych. W edytorze Visual Basic kliknij dwukrotnie arkusz danych.

Po dwukrotnym kliknięciu arkusza wybierz „Arkusz roboczy” i wybierz wydarzenie jako „Zmień”.

Zobaczysz automatyczną procedurę podrzędną otwartą jako Worksheet_Change (ByVal Target As Range)

Krok 2: Użyj obiektu arkusza roboczego

Zapoznaj się z arkuszem danych przy użyciu obiektu Arkusze kalkulacyjne.

Krok 3: Sprawdź tabelę przestawną według nazwy

Odwołaj się do nazwy tabeli przestawnej, używając nazwy tabeli przestawnej.

Krok 4: Użyj metody odświeżania tabeli

Wybierz metodę jako „Odśwież tabelę”.

Teraz ten kod odświeży tabelę przestawną „PivotTable1” po każdej zmianie w źródłowym arkuszu danych. Możesz użyć poniższego kodu, wystarczy zmienić nazwę tabeli przestawnej.

Kod:

 Private Sub Worksheet_Change (ByVal Target As Range) Worksheet („Data Sheet”). PivotTables („PivotTable1”). RefreshTable End Sub

# 2 - Odśwież wszystkie tabele przestawne tego samego arkusza

Jeśli masz wiele tabel przestawnych w tym samym arkuszu, możesz odświeżyć wszystkie tabele przestawne jednym kliknięciem. Użyj poniższego kodu, aby odświeżyć wszystkie tabele przestawne w arkuszu.

Kod:

 Sub Refresh_Pivot_Tables_Example1 () Worksheets ("Data Sheet"). Wybierz With ActiveSheet .PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable .PivotTables ("Table4"). RefreshTable .PivotTables („Table5”). RefreshTable kończy się na End Sub

Musisz zmienić nazwę arkusza roboczego i nazwy tabel przestawnych zgodnie ze szczegółami arkusza.

# 3 - Odśwież wszystkie tabele w skoroszycie

Jest bardzo mało prawdopodobne, że wszystkie tabele przestawne znajdują się w tym samym arkuszu. Zwykle do każdego raportu staramy się dodawać oddzielne tabele przestawne w oddzielnych arkuszach. W takich przypadkach nie możemy dalej pisać kodu dla każdej tabeli przestawnej do odświeżenia.

Tak więc możemy zrobić z pojedynczym kodem, używając pętli, które możemy przejrzeć wszystkie tabele przestawne w skoroszycie i odświeżyć je jednym kliknięciem przycisku.

Poniższy kod przejdzie przez każdą tabelę przestawną i odświeży je.

Kod 1:

 Sub Refresh_Pivot_Tables_Example2 () Dim PT As PivotTable dla każdego PT w ActiveWorkbook.PivotTables PT.RefreshTable Next PT End Sub

Kod 2:

 Sub Refresh_Pivot_Tables_Example3 () Dim PC As PivotCache dla każdego komputera w ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub

Oba kody będą odświeżać tabele przestawne.

Jeśli chcesz, aby tabela przestawna została odświeżona, gdy tylko nastąpi zmiana w arkuszu danych arkusza przestawnego, musisz skopiować i wkleić powyższe kody do zdarzenia Zmiana arkusza roboczego w tym skoroszycie.

# 4 - Unikaj czasu ładowania, używając arkusza Dezaktywuj wydarzenie

Kiedy używamy zdarzenia „Zmiana arkusza roboczego”, jest ono odświeżane nawet wtedy, gdy nie ma żadnych zmian w źródle danych, ale jeśli jakakolwiek zmiana nastąpi w arkuszu.

Nawet jeśli wprowadzisz jedną kropkę w arkuszu, spróbuje odświeżyć tabelę przestawną. Aby tego uniknąć, możemy użyć metody „Dezaktywuj arkusz roboczy” zamiast metody „Zmiana arkusza roboczego”.