Podsumowanie: Dowiedz się, jak używać prostego makra do automatycznego odświeżania tabel przestawnych po wprowadzeniu zmian w danych źródłowych. Udostępniam również rozwiązanie inne niż makra do aktualizowania tabel przestawnych po otwarciu pliku. Zawiera samouczek wideo i pobieranie pliku Excel.
Poziom umiejętności: średniozaawansowany
Pobierz plik Excela
Jeśli najlepiej uczysz się, robiąc to samodzielnie, możesz pobierz plik, którego używam w filmie, aby kontynuować. Oto plik programu Excel zawierający kod VBA.
Automatycznie odśwież tabelę przestawną.xlsm (41,5 KB)
Aktualizuj tabele przestawne automatycznie
Czy tabele przestawne mogą być aktualizowane natychmiast i automatycznie po zmianie danych źródłowych?
Oczywiście. Wymaga to użycia naprawdę prostego makra, które pokażę poniżej.
Jeśli nie znasz zbyt dobrze makr i VBA, proponuję obejrzeć moją bezpłatną trzyczęściową serię filmów na temat rozpoczynania pracy z makrami & VBA.
Ponadto, jeśli nie masz doświadczenia z tabelami przestawnymi, Mam serię, w której dowiesz się, czym one są i jak ich używać. Obejrzyj pierwszy film z tej serii w tabelach przestawnych & Pulpity nawigacyjne
Aby automatycznie zaktualizować nasze tabele przestawne, napiszemy makro z jedną prostą instrukcją. Ta instrukcja zasadniczo mówi: kiedy wprowadzam zmianę w moim arkuszu, odśwież wszystkie tabele przestawne i połączenia danych. Oto kroki tworzenia makra.
Otwórz Edytor Visual Basic.
Możesz to zrobić, klikając przycisk Visual Basic na karcie Deweloper na wstążce.
Skrót klawiaturowy służący do otwierania edytora Visual Basic to Alt + F11.
Jeśli nie widzisz na karcie Deweloper, możesz ją wyświetlić, korzystając z instrukcji tutaj. Wystarczy zrobić to raz, a karta Deweloper będzie zawsze widoczna za każdym razem, gdy otworzysz program Excel w przyszłości.
Otwórz moduł arkusza zawierający dane źródłowe.
W oknie Project Explorer edytora Visual Basic zlokalizuj skoroszyt, który chcesz zmienić. Pod tym skoroszytem są wymienione arkusze w skoroszycie. Wybierz arkusz zawierający dane źródłowe. Następnie kliknij go dwukrotnie.
Jeśli nie widzisz okna Eksplorator projektu, możesz je włączyć z poziomu widoku menu (skrót klawiszowy: Ctrl + R).
Dodaj nowe zdarzenie do zmian w arkuszu.
Dwukrotne kliknięcie arkusza otwiera moduł kodu dla tego obiektu. W ramach modułu kodu chcemy stworzyć makro zdarzenia. Aby to zrobić, wybierz opcję Arkusz w menu rozwijanym Obiekt po lewej stronie.
Spowoduje to dodanie zdarzenia Worksheet_SelectionChange do moduł, którego tak naprawdę nie chcemy, więc usuniemy go za chwilę. Zanim to zrobimy, przejdźmy do menu rozwijanego Procedura po prawej stronie i wybierz opcję Zmień.
To dodaje nowy zdarzenie u góry o nazwie Worksheet_Change. Teraz podświetlimy i usuniemy niepotrzebny kod poniżej.
Makro zdarzenia Worksheet_Change będzie uruchamiane za każdym razem, gdy zostanie wprowadzona zmiana do komórek w tym arkuszu. Możemy dodać kod VBA do zdarzenia Worksheet_Change, aby wykonywać akcje, gdy użytkownik edytuje komórki.
Uwaga: Zdarzenie SelectionChange, które jest dodawane domyślnie, będzie uruchamiane za każdym razem, gdy użytkownik wybierze komórkę w arkuszu. Ponieważ chcemy, aby kod działał tylko wtedy, gdy użytkownik edytuje / zmienia komórki, używamy zdarzenia Change. Sprawdź mój artykuł o modułach kodu VBA & Jak uruchamiać makra na podstawie zdarzeń użytkownika, aby dowiedzieć się więcej o modułach arkusza i zdarzeniach.
Dodaj kod VBA, aby odświeżyć wszystkie tabele przestawne.
Następnie, tuż pod linią Worksheet_Change, wpisz tę instrukcję:
ThisWorkbook.RefreshAll
Metoda RefreshAll odświeży wszystkie tabele przestawne, zapytania i połączenia danych w skoroszycie. Ta czynność jest taka sama, jak w przypadku ręcznego kliknięcia przycisku Odśwież na karcie Dane.
Dodanie tej linii kodu do zdarzenia Worksheet_Change spowoduje odświeżenie skoroszytu za każdym razem, gdy w arkuszu zostanie wprowadzona zmiana wskazująca, że kod jest in.
Tabela przestawna & Dane źródłowe w tym samym arkuszu
Aleksandrs zadał świetne pytanie w komentarzach do filmów w YouTube. Jeśli tabela przestawna i dane źródłowe znajdują się w tym samym arkuszu, musisz dodać kod, aby wyłączyć zdarzenia.
Odświeżenie umieszcza zdarzenie w cyklicznej pętli i może zakończyć się awarią programu Excel. Oto kod, który ma temu zapobiec.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Sprawdzanie, czy makro działa.
Jeden sposób sprawdzenia, czy makro działa to przetestować to. Wprowadź zmiany w danych źródłowych i zobacz, czy są one odzwierciedlone w tabeli przestawnej.Jeśli zmiana nie jest łatwa do wykrycia, ponieważ masz za dużo danych lub z innego powodu, istnieje inny sposób sprawdzenia, czy Twoje makro się uruchamia.
W edytorze VB możesz kliknąć szara kolumna po lewej stronie makra Worksheet_Change. Spowoduje to pojawienie się czerwonego koła. Podkreśla również tę linię kodu na czerwono.
Nazywa się to stop lub punktem przerwania.
Skrót klawiaturowy służący do włączania / wyłączania punktu przerwania to: F9
Teraz za każdym razem, gdy nastąpi akcja wyzwalająca makro, program Excel przeskoczy do edytora VB i wstrzyma makro, aby można było sprawdzić kod. W naszym przypadku jest to każda zmiana wprowadzana w arkuszu.
Następnie możesz nacisnąć klawisz F8, aby przejść przez każdą linii lub naciśnij klawisz F5, aby przejść do końca (lub następnego punktu przerwania).
Jeśli wprowadzisz zmianę w arkuszu, a program Excel nie wciągnie Cię do Edytora VB, wiesz, że jest problem z makro nie działa. W takim przypadku prawdopodobnie plik nie został zapisany jako skoroszyt z obsługą makr (.xlsm) i / lub włączone makra. Może być konieczne zapisanie &, zamknięcie pliku, a następnie ponowne otwarcie go i włączenie makr.
Aby usunąć punkt przerwania umieszczony w makrze, wystarczy kliknąć czerwone kółko, aby zniknęło (skrót klawiaturowy: F9).
Skrót klawiaturowy do wyczyszczenia wszystkich punktów przerwania to: Ctrl + Shift + F9
Odświeżanie tabel przestawnych bez Makro
Jedną z wad używania tego makra do odświeżania tabel przestawnych jest to, że każda historia cofania jest tracona przy każdym uruchomieniu makra. Innymi słowy, kiedy klikniesz przycisk Cofnij (lub naciśniesz Ctrl + Z), Excel nie pamięta ostatniej rzeczy, którą zrobiłeś, więc nie może tego cofnąć. W konsekwencji nic się nie stanie, a Twoja ostatnia zmiana nie zostanie cofnięta.
Istnieje alternatywa, która pozwala zachować historię cofania. Jednak ta alternatywa odświeża tabelę przestawną tylko po otwarciu skoroszytu, a nie za każdym razem, gdy wprowadzana jest zmiana. Oto, jak możesz użyć tej opcji.
Rozpoczynając od dowolnej komórki w tabeli przestawnej:
- Przejdź do karty Analiza na wstążce.
- Wybierz przycisk Opcje.
- Przejdź do karty Dane w nowym oknie, które zostanie otwarte.
- Zaznacz pole „Odśwież dane podczas otwierania pliku”.
Po kliknięciu OK może pojawić się następujący komunikat ostrzegawczy, jeśli masz wiele tabel przestawnych utworzonych z tego samego zakresu danych źródłowych. Po prostu kliknij OK, aby przejść przez to.
Jeszcze raz, dla porównania, Jeśli użyjesz tej opcji, zachowasz historię cofania, ale odświeży ona tabelę przestawną tylko po zamknięciu i ponownym otwarciu skoroszytu. Jeśli użyjesz opcji makra, utracisz historię cofania, ale tabela przestawna automatycznie się zaktualizuje tes za każdym razem, gdy wprowadzana jest jakakolwiek zmiana w skoroszycie.
Odmiany odświeżania tabel przestawnych
Makro, które oglądaliśmy, nie tylko odświeży tabele przestawne, ale także odświeży wszelkie zapytania . Jeśli chcesz odświeżyć tylko tabele przestawne, możesz zamiast tego zastąpić polecenie „ThisWorkbook.RefreshAll” tym kodem:
Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
Każda tabela przestawna jest połączona z bazowa pamięć podręczna przestawna, która jest połączona z danymi źródłowymi. Kod przechodzi przez wszystkie pamięci podręczne przestawne w skoroszycie i odświeża każdą z nich. Tabele przestawne z tego samego zakresu źródłowego mogą współużytkować pamięci podręczne przestawne, więc ta metoda jest szybsza niż przechodzenie przez wszystkie tabele przestawne .
Podobnie, powiedzmy, że chcesz odświeżyć tylko jedną konkretną tabelę przestawną. W takim przypadku możesz zamienić kod „ThisWorkbook.RefreshAll” na poniższy kod.
I na koniec, jeśli używasz dodatku Power Query i chcesz wyłączyć odświeżanie w tle, aby zapytania były odświeżane PRZED tabelami przestawnymi. Napisałem artykuł wyjaśniający, jak to zrobić, wyłączając odświeżanie w tle w zapytaniach.
Użyj Zamiast tego zdezaktywuj zdarzenie
Inną opcją jest użycie zdarzenia Worksheet_Deactivate zamiast Worksheet_Change. Zdarzenie Worksheet_Deactivate będzie uruchamiane za każdym razem, gdy użytkownik opuści arkusz i wybierze inny arkusz. Dzięki temu użytkownik może wprowadzić wszystkie zmiany dane źródłowe, a następnie tabela przestawna zostanie automatycznie odświeżona, gdy przejdą do innego arkusza, w tym arkuszy, które zawierają tabelę przestawną.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Ten kod nadal będzie umieszczony w module arkusza, który zawiera dane źródłowe. Jest to dobra opcja, jeśli tabele przestawne lub połączenia danych przyjmują kilka sekund lub dłużej na aktualizację i nie chcesz czekać za każdym razem, gdy wprowadzana jest zmiana w danych źródłowych.
Jedyny przypadek, w którym możesz nie chcieć tego używać, to sytuacja, gdy tabela przestawna i źródło dane znajdują się na tym samym arkuszu.Zwykle będzie to rzadki przypadek i coś, czego generalnie nie polecam.
Dzięki sugestii Teda w tej sprawie.
Oszczędź czas & Zawstydzenie
Mam nadzieję, że ten artykuł pomoże Ci zaoszczędzić czas i ułatwi użytkownikom Twoich plików. Może również pomóc w uniknięciu zakłopotania, gdy zapomnisz odświeżyć tabele przestawne przed wysłaniem raportów. Uwierz ja, popełniłem ten błąd więcej razy, niż chciałbym się przyznać… 🙂
