W tym artykule dowiemy się, jak automatycznie odświeżać dane tabeli przestawnej w programie Excel.
Scenariusz:
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. Musimy odświeżyć tabele przestawne, aby zobaczyć zmiany za każdym razem, gdy otwieramy skoroszyt programu Excel. A jeśli wyślesz zaktualizowany plik bez odświeżania tabel przestawnych, możesz poczuć się zakłopotany. Dowiedz się, jak znaleźć opcję odświeżania podczas korzystania z tabeli przestawnej
Odśwież dane podczas otwierania pliku w programie Excel
Najpierw utwórz tabelę przestawną, a następnie kliknij prawym przyciskiem myszy dowolną komórkę tabeli przestawnej.
Przejdź do opcji tabeli przestawnej > karta Dane > zaznacz pole, które mówi Odśwież dane podczas otwierania pliku
Umożliwi to automatyczną aktualizację danych po każdym otwarciu pliku.
Przykład :
Wszystko to może być trudne do zrozumienia. Rozumiem, jak korzystać z funkcji na przykładzie. Tutaj mamy trochę danych i musimy najpierw utworzyć tabelę przestawną, a następnie znaleźć opcje umożliwiające automatyczną aktualizację tabel przestawnych.
Utwórz tabelę przestawną, a następnie kliknij prawym przyciskiem myszy dowolną komórkę tabeli przestawnej, jak pokazano poniżej.
Wybierz Opcje tabeli przestawnej, co spowoduje otwarcie okna dialogowego Opcje tabeli przestawnej.
Wybierz kartę danych, a następnie zaznacz pole, które mówi Odśwież dane podczas otwierania pliku. Możesz to zrobić bez otwierania i zamykania pliku za pomocą VBA.
Korzystanie z VBA
Tutaj 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 obiekcie arkusza danych
Prywatny arkusz pomocniczy_Dezaktywuj() nazwa_arkusza_tabeli_przestawnej.Tabele przestawne("nazwa_tabeli_przestawnej").PivotCache.Refresh Napis końcowy |
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ą.
| Prywatny arkusz pomocniczy_Dezaktywuj()
Sheet1.PivotTables("Tabela przestawna1").PivotCache.Refresh Napis końcowy |
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.
| Prywatny arkusz pomocniczy_Dezaktywuj()
'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh Dla każdego komputera w ThisWorkbook.PivotCaches pc.Odśwież Następny komputer Napis końcowy |
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("Tabela przestawna1").PivotCache.Refresh Napis końcowy |
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 Napis końcowy |
Notatka : 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.
Mam nadzieję, że ten artykuł o tym, jak automatycznie odświeżać dane tabeli przestawnej w programie Excel, jest objaśniający. Więcej artykułów na temat obliczania wartości i powiązanych formuł programu Excel znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
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 : Zdarzenia arkusza roboczego są naprawdę przydatne, gdy chcesz, aby makra były uruchamiane, gdy w arkuszu wystąpi określone zdarzenie.
Popularne artykuły :
50 skrótów Excela, które zwiększą Twoją produktywność : Szybsze wykonywanie zadań w programie Excel. Te skróty pomogą Ci zwiększyć wydajność pracy w programie Excel.
Jak korzystać z funkcji 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.
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.