Jak wszyscy wiemy, za każdym razem, gdy wprowadzamy zmiany w danych źródłowych tabeli przestawnej, nie są one natychmiast odzwierciedlane w tabeli przestawnej. Aby zobaczyć zmiany, musimy odświeżyć tabele przestawne. A jeśli wyślesz zaktualizowany plik bez odświeżania tabel przestawnych, możesz poczuć się zakłopotany.
W tym artykule dowiemy się, jak automatycznie odświeżyć tabelę przestawną za pomocą VBA. W ten sposób jest łatwiej niż sobie wyobrażałeś.
Jest to prosta składnia do automatycznego odświeżania tabel przestawnych w skoroszycie.
'Kod w źródłowym arkuszu danych Object Private Sub Worksheet_Deactivate() nazwa_arkusza_tabeli_przestawnej.PivotTables("nazwa_tabeli_przestawnej").PivotCache.Refresh End Sub
Co to są pamięci podręczne przestawne?
Każda tabela przestawna przechowuje dane w pamięci podręcznej przestawnej. Dlatego pivot jest w stanie pokazać poprzednie dane. Kiedy odświeżamy tabele przestawne, aktualizuje pamięć podręczną o nowe dane źródłowe, aby odzwierciedlić zmiany w tabeli przestawnej.
Potrzebujemy więc tylko makra do odświeżenia pamięci podręcznej tabel przestawnych. Zrobimy to za pomocą zdarzenia arkusza roboczego, dzięki czemu nie musimy ręcznie uruchamiać makra.
Gdzie kodować, aby automatycznie odświeżać tabele przestawne?
Jeśli dane źródłowe i tabele przestawne znajdują się w różnych arkuszach, kod VBA powinien znajdować się w arkuszu danych źródłowych.
Tutaj użyjemy zdarzenia Worksheet_SelectionChange. Dzięki temu kod będzie uruchamiany za każdym razem, gdy przełączymy się ze źródłowego arkusza danych do innego arkusza. Wyjaśnię później, dlaczego skorzystałem z tego wydarzenia.
Tutaj mam dane źródłowe w arkuszu 2 i tabele przestawne w arkuszu1.
Otwórz VBE za pomocą klawisza CTRL+F11. W eksploratorze projektów możesz zobaczyć trzy obiekty, Arkusz1, Arkusz2 i Skoroszyt.
Ponieważ Sheet2 zawiera dane źródłowe, kliknij dwukrotnie obiekt Sheet2.
Teraz możesz zobaczyć dwa menu rozwijane w górnej części obszaru kodu. Z pierwszego menu wybierz arkusz. A z drugiego menu rozwijanego wybierz Dezaktywuj. Spowoduje to wstawienie pustej nazwy podrzędnej Worksheet_Deactivate. Nasz kod zostanie zapisany w tym podpunkcie. Wszystkie wiersze zapisane w tym podrzędnym get są wykonywane, gdy tylko użytkownik przełączy się z tego arkusza do innego arkusza.
W arkuszu 1 mam dwie tabele przestawne. Chcę odświeżyć tylko jedną tabelę przestawną. W tym celu muszę znać nazwę tabeli przestawnej. Aby poznać nazwę dowolnej tabeli przestawnej, wybierz dowolną komórkę w tej tabeli przestawnej, przejdź do karty Analiza tabeli przestawnej. Po lewej stronie zobaczysz nazwę tabeli przestawnej. Tutaj możesz również zmienić nazwę tabeli przestawnej.
Teraz znamy nazwę tabeli przestawnej, możemy napisać prostą linię, aby odświeżyć tabelę przestawną.
Private Sub Worksheet_Deactivate() Sheet1.PivotTables("PivotTable1").PivotCache.Refresh End Sub
I gotowe.
Teraz, gdy przełączysz się z danych źródłowych, ten kod vba zostanie uruchomiony, aby odświeżyć tabelę przestawną1. Jak widać na poniższym gifie.
Jak odświeżyć wszystkie tabele przestawne w skoroszycie?
W powyższym przykładzie chcieliśmy odświeżyć tylko jedną konkretną tabelę przestawną. Ale jeśli chcesz odświeżyć wszystkie tabele przestawne w skoroszycie, wystarczy wprowadzić niewielkie zmiany w kodzie.
Private Sub Worksheet_Deactivate() 'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh dla każdego komputera w ThisWorkbook.PivotCaches pc.Refresh Następny komputer End Sub
W tym kodzie używamy pętli For, aby przejść przez każdą pamięć podręczną osi w skoroszycie. Obiekt ThisWorkbook zawiera wszystkie pamięci podręczne osi. Aby uzyskać do nich dostęp, używamy ThisWorkbook.PivotCaches.
Dlaczego warto korzystać z Worksheet_Deactivate Event?
Jeśli chcesz odświeżyć tabelę przestawną natychmiast po wprowadzeniu jakiejkolwiek zmiany w danych źródłowych, użyj zdarzenia Worksheet_Change. Ale nie polecam. Spowoduje to, że skoroszyt będzie uruchamiał kod za każdym razem, gdy dokonasz jakiejkolwiek zmiany w arkuszu. Być może będziesz musiał dokonać setek zmian, zanim będziesz chciał zobaczyć wynik. Ale Excel odświeży tabelę przestawną po każdej zmianie. Doprowadzi to do strat czasu i zasobów przetwarzania. Jeśli więc masz tabele przestawne i dane w różnych arkuszach, lepiej użyć opcji Arkusz Dezaktywuj zdarzenie. Pozwala dokończyć pracę. Po przełączeniu się na arkusze tabeli przestawnej, aby zobaczyć zmiany, wprowadza on zmiany.
Jeśli masz tabele przestawne i dane źródłowe na tym samym arkuszu i chcesz, aby tabele przestawne automatycznie się odświeżały, możesz użyć zdarzenia Worksheet_Change Event.
Private Sub Worksheet_Change(ByVal Target As Range) Sheet1.PivotTables("PivotTable1").PivotCache.Refresh Koniec Sub
Jak odświeżyć wszystko w skoroszytach po wprowadzeniu zmiany w danych źródłowych?
Jeśli chcesz odświeżyć wszystko w skoroszycie (wykresy, tabele przestawne, formuły itp.), możesz użyć polecenia ThisWorkbook.RefreshAll.
Private Sub Worksheet_Change(ByVal Target As Range) ThisWorkbook.RefreshAll Koniec Sub
Należy pamiętać, że ten kod nie zmienia źródła danych. Jeśli więc dodasz dane poniżej danych źródłowych, ten kod nie uwzględni tych danych automatycznie. Do przechowywania danych źródłowych można używać tabel programu Excel. Jeśli nie chcesz używać tabel, możemy również użyć VBA do dołączania nowych danych. Dowiemy się tego w następnym tutorialu.
Więc tak, w ten sposób możesz automatycznie odświeżać tabele przestawne w programie Excel. Mam nadzieję, że wyjaśniłem wystarczająco, a ten artykuł dobrze ci służył. Jeśli masz jakieś pytania dotyczące tego tematu, możesz zadać mi je w sekcji komentarzy poniżej.
Jak dynamicznie aktualizować zakres źródeł danych tabeli przestawnej w programie Excel: Aby dynamicznie zmieniać zakres danych źródłowych tabel przestawnych, używamy pamięci podręcznych przestawnych. Te kilka wierszy może dynamicznie aktualizować dowolną tabelę przestawną, zmieniając zakres danych źródłowych. W VBA użyj obiektów tabeli przestawnej, jak pokazano poniżej…
Uruchom makro, jeśli w arkuszu w określonym zakresie wprowadzono jakiekolwiek zmiany: W swoich praktykach VBA będziesz musiał uruchamiać makra, gdy zmieni się określony zakres lub komórka. W takim przypadku, aby uruchomić makra po wprowadzeniu zmiany w zakresie docelowym, używamy zdarzenia change.
Uruchom makro po wprowadzeniu jakiejkolwiek zmiany w arkuszu | Aby uruchomić makro za każdym razem, gdy arkusz się zaktualizuje, używamy Zdarzeń arkusza roboczego VBA.
Najprostszy kod VBA do podświetlania bieżącego wiersza i kolumny za pomocą | Użyj tego małego fragmentu kodu VBA, aby podświetlić bieżący wiersz i kolumnę arkusza.
Zdarzenia arkusza roboczego w Excel VBA | Zdarzenie arkusza roboczego jest naprawdę przydatne, gdy makra mają być uruchamiane, gdy w arkuszu wystąpi określone zdarzenie.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Funkcja WYSZUKAJ.PIONOWO w programie Excel | Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
LICZ.JEŻELI w Excelu 2016 | Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby policzyć konkretną wartość. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.