Jak odświeżyć tabelę przestawną programu Excel, ręcznie lub automatycznie. Kroki dla normalnej tabeli przestawnej lub tabeli przestawnej modelu danych.
UWAGA: W przypadku komunikatów o błędach podczas odświeżania przejdź do strony Błędy tabeli przestawnej.
Wprowadzenie
Gdy zmieniasz informacje w danych źródłowych tabeli przestawnej, tabela przestawna nie wyświetla automatycznie najnowszych informacji. Musisz odświeżyć tabelę przestawną ręcznie lub automatycznie, a wtedy pojawią się najnowsze dane.
Poniżej przedstawiono kroki, które pokazują, jak odświeżyć tabelę przestawną:
- ręcznie
- automatycznie przy otwieraniu pliku
- automatycznie po liczniku czasu (dla połączeń)
- automatycznie za pomocą makra
UWAGA : Podczas odświeżania tabeli przestawnej jej pamięć podręczna przestawna jest odświeżana. WSZYSTKIE tabele przestawne używające tej samej pamięci podręcznej przestawnej również zostaną odświeżone.
Odśwież ręcznie
Szybkim i łatwym sposobem na odświeżenie tabeli przestawnej po zmianie danych jest ręczna aktualizacja:
- Kliknij prawym przyciskiem myszy dowolną komórkę w tabeli przestawnej, a następnie kliknij Odśwież.
Tabela przestawna w chronionym arkuszu
Gdy arkusz jest chroniony, nie można odświeżyć tabel przestawnych w tym arkuszu. Po kliknięciu tabeli przestawnej prawym przyciskiem myszy polecenie Odśwież jest nieaktywne out.
Oto jak obejść ten problem:
- Ręcznie: Jeśli znasz hasło, ręcznie wyłącz ochronę arkusza, odśwież tabelę przestawną, a następnie ponownie zabezpiecz arkusz.
- Makra: Jeśli próbujesz odświeżyć tabelę przestawną za pomocą makra, dodaj kod, aby wyłączyć ochronę arkusza, odśwież tabelę przestawną, a następnie ponownie zabezpiecz arkusz. W arkuszu Ochrona tabeli przestawnej znajdują się przykłady.
Ostrzeżenie ssage
Jeśli inne tabele przestawne używają tej samej pamięci podręcznej przestawnej, możesz zobaczyć komunikat ostrzegawczy, jeśli co najmniej jedna z tych tabel przestawnych znajduje się na chronionych arkuszach. Komunikat ostrzegawczy brzmi:
- Tego polecenia nie można wykonać, gdy chroniony arkusz zawiera inny raport w formie tabeli przestawnej oparty na tych samych danych źródłowych.
Aby usunąć ochronę z arkusza, w którym znajduje się drugi raport, kliknij kartę arkusza, a następnie kliknij opcję Nie chroń arkusza (karta Recenzja, grupa Zmiany). Następnie spróbuj ponownie wykonać polecenie.
Zgodnie z komunikatem należy również wyłączyć ochronę innych arkuszy , zanim będzie można odświeżyć którąkolwiek z tabel przestawnych, które używają tej samej pamięci podręcznej przestawnej.
Jeśli odświeżasz za pomocą makr, to makro pokazuje, jak wyłączyć ochronę wszystkich arkuszy, odświeżyć tabele przestawne, a następnie zabezpieczyć arkusze ponownie.
Odśwież po otwarciu pliku
W niektórych skoroszytach możesz chcieć odświeżyć tabelę przestawną zaraz po otwarciu skoroszytu, na wypadek gdyby ktoś wprowadził zmiany w źródle danych.
Aby to zrobić, ustaw opcję tabeli przestawnej, aby automatycznie odświeżała tabelę przestawną:
- Kliknij prawym przyciskiem myszy komórkę w tabeli przestawnej i wybierz Opcje tabeli przestawnej.
- W opcjach danych dodaj znacznik wyboru przy opcji „Odśwież dane podczas otwierania pliku”
- Kliknij przycisk OK, aby zapisać ustawienie opcji.
Odśwież na czas
W przypadku tabel przestawnych opartych na OLAP można także odświeżyć d na liczniku czasu w jego połączeniu.
UWAGA: Po utworzeniu tabeli przestawnej, jeśli dodano jej dane do modelu danych, jest ona oparta na modelu OLAP.
Aby ustawić automatyczne odświeżanie licznika czasu połączenia, wykonaj następujące kroki:
- Na wstążce programu Excel kliknij kartę Dane
- Kliknij Zapytania & Połączenia
- W oknie Zapytania & Połączenia kliknij kartę Połączenia
- Kliknij prawym przyciskiem myszy WorksheetConnection i kliknij Właściwości
-
- Na karcie Użycie dodaj znacznik zaznacz opcję „Odśwież co x minut”
- Wpisz liczbę w polu minut, aby ustawić minutnik
-
- Wskazówka: jeśli dane źródłowe często się zmieniają, a zbiór danych nie jest zbyt duży, użyj małej liczby. W przypadku innych skoroszytów użyj większej liczby, aby praca nie była opóźniona podczas oczekiwania na odświeżenie połączenia
- Kliknij OK, aby zapisać ustawienia gs
Odśwież za pomocą makr
Innym sposobem aktualizowania tabeli przestawnej jest programowanie. Możesz użyć makra programu Excel, aby automatycznie odświeżyć tabelę przestawną po uaktywnieniu arkusza tabeli przestawnej.
Dodaj następujące makro do modułu kodu arkusza tabeli przestawnej. Zobacz instrukcje kopiowania kodu VBA do własnych plików.
UWAGA: Jeśli próbujesz odświeżyć tabele przestawne w chronionych arkuszach, na arkuszu Ochrona tabeli przestawnej znajdują się przykłady makr.
Makro dla jednej tabeli przestawnej
Umieść następujący kod w module arkusza, jeśli w arkuszu jest tylko 1 tabela przestawna.Spowoduje to automatyczne odświeżenie tabeli przestawnej, gdy tylko przejdziesz do jej arkusza.
Private Sub Worksheet_Activate() Application.EnableEvents = False Me.PivotTables(1).RefreshTable Application.EnableEvents = TrueEnd Sub
Makro dla wielu tabel przestawnych
Jeśli tam to wiele tabel przestawnych na arkuszu, z różnymi danymi źródłowymi, użyj tego kodu, wklejonego do modułu arkusza. Spowoduje to automatyczne odświeżenie wszystkich tabel przestawnych w arkuszu:
Private Sub Worksheet_Activate() Dim pt As PivotTable Application.EnableEvents = False For Each pt In Me.PivotTables pt.RefreshTable Next pt Application.EnableEvents = True End Sub
Odśwież wiele pamięci podręcznych przestawnych
Jeśli istnieją co najmniej dwie tabele przestawne w skoroszycie, w oparciu o różne źródła danych, nie wszystkie zostaną zaktualizowane po odświeżeniu jednej z tabel przestawnych.
Zamiast tego możesz użyć przycisku Odśwież wszystko.
Uwaga : Użycie polecenia Odśwież wszystko również odświeża wszystkie zakresy danych zewnętrznych w aktywnym skoroszycie i wpływa na widoczne i ukryte arkusze w aktywnym skoroszycie.
Aby odświeżyć wszystkie tabele przestawne i zakresy danych zewnętrznych, w aktywny skoroszyt w tym samym czasie:
- Na Wstążce kliknij kartę Dane
- W grupie Połączenia kliknij górną sekcję polecenia Odśwież wszystko
WSKAZÓWKA: Możesz dodać przycisk Odśwież wszystko do paska narzędzi szybkiego dostępu, aby był łatwiejszy w użyciu.
Zatrzymaj odświeżanie
Zwykle odświeżanie przebiega szybko, ale czasami można długi czas do biegu. Jeśli chcesz zatrzymać odświeżanie, użyj jednej z poniższych metod.
Klawisz Esc
Aby zatrzymać długie odświeżanie, naciśnij klawisz Esc na klawiaturze.
Pasek stanu
Jeśli odświeżanie działa jako zapytanie w tle, wykonaj następujące kroki, aby zatrzymać odświeżanie:
- Kliknij wskaźnik Odśwież na pasku stanu
- W oknie dialogowym Stan odświeżania danych zewnętrznych wybierz zapytanie z listy.
- Następnie kliknij przycisk Zatrzymaj odświeżanie i kliknij Zamknij, aby zamknąć okno dialogowe.
Dziwny błąd odświeżania
Prosta zmiana danych może spowodować dziwną tabelę przestawną błąd odświeżania, jeśli dodano dane tabeli przestawnej do modelu danych. Dziękuję UniMord za poinformowanie mnie o tym błędzie i sposobach zapobiegania mu.
UWAGA: To jest krótki opis błędu i jego naprawy. W przypadku dłuższej wersji, zawierającej więcej zrzutów ekranu i szczegółów, zobacz artykuł na moim blogu Contextures Excel.
Oto zredagowana wersja tej wiadomości, z przeniesioną częścią tekstu, dzięki czemu możesz przeczytać wszystkie tego.
A oto wersja tekstowa wiadomości, na wypadek gdyby ktoś szukał pomocy z tym komunikatem o błędzie :
- Nie mogliśmy pobrać danych z modelu danych. Oto komunikat o błędzie, który otrzymaliśmy:
- Wystąpił nieoczekiwany błąd (plik „pcminorobjcoll.inl”, wiersz 109, funkcja PCMinorObjectCollection < klasa PCProperty, klasa NameHashSupport > :: SetNameAndUpdateCollection „)
Ten komunikat o błędzie pojawił się, gdy jeden z nagłówków danych źródłowych został zmieniony z WIELKIEJ wielkości liter na Właściwy przypadek. Stworzyło to drugą instancję pola w modelu danych, którą można było zobaczyć na liście pól tabeli przestawnej.
Napraw problem
Aby naprawić ten pro blem, jeśli zobaczysz ten komunikat o błędzie:
- W tabeli danych źródłowych zmień nagłówek z powrotem na jego pierwotną wielkość – zmieniłem dzień z powrotem na DZIEŃ
- Następnie odśwież tabela przestawna
- Dodatkowe pole powinno zniknąć z listy pól tabeli przestawnej
Uniknij problemu
Aby uniknąć tego błędu, jeśli chcesz zmień wielkość liter w nagłówku pola:
- W tabeli danych źródłowych zmień wielkość liter w nagłówku
- Na karcie Dane wstążki programu Excel kliknij polecenie Zarządzaj modelem danych
- W oknie dodatku Power Pivot dla programu Excel na karcie Strona główna kliknij polecenie Widok projektu
- Na diagramie kliknij prawym przyciskiem myszy zmienioną nazwę pola i kliknij opcję Zmień nazwę
-
- Wpisz nazwę pola, tak jak wpisałeś ją w źródłowej tabeli danych
- Zamknij okno Power Pivot
UWAGA: Aby uzyskać więcej zrzutów ekranu i szczegółowe informacje o tym błędzie, zobacz artykuł na moim blogu Contextures Excel.
Downl oad the Sample File
Pobierz przykładowy plik odświeżania tabeli przestawnej i postępuj zgodnie z instrukcjami. Skompresowany plik jest w formacie xlsx i skoroszyt nie zawiera makr. Istnieją 2 tabele przestawne, Model danych i Normalny, więc po otwarciu pliku zobaczysz komunikat z ostrzeżeniem o połączeniu.
Więcej linków
Zaplanuj i skonfiguruj tabelę przestawną
Błędy tabeli przestawnej
Często zadawane pytania – tabele przestawne
Makra pamięci podręcznej przestawnej
Wprowadzenie do tabeli przestawnej
Funkcje podsumowujące
Blog tabeli przestawnej
Indeks artykułów tabeli przestawnej
Darmowy dodatek do tabeli przestawnej