Jak dynamicznie aktualizować wszystkie źródła danych tabel przestawnych w programie Excel

W poprzednim artykule dowiedzieliśmy się, jak można dynamicznie zmieniać i aktualizować poszczególne tabele przestawne za pomocą kurczących się lub rozszerzanych źródeł danych.

W tym artykule będziemy dowiedz się, jak możemy sprawić, by wszystkie tabele przestawne w skoroszycie automatycznie zmieniały źródło danych. Innymi słowy, zamiast zmieniać jedną tabelę przestawną naraz, spróbujemy zmienić źródło danych wszystkich tabel przestawnych w skoroszycie, aby dynamicznie uwzględnić nowe wiersze i kolumny dodane do tabel źródłowych i natychmiast odzwierciedlić zmianę w tabelach przestawnych.

Napisz kod w arkuszu danych źródłowych

Ponieważ chcemy, aby było to całkowicie automatyczne, użyjemy modułów arkusza do pisania kodu zamiast modułu podstawowego. Umożliwi nam to użycie zdarzeń arkusza.

Jeśli dane źródłowe i tabele przestawne znajdują się w różnych arkuszach, napiszemy kod VBA, aby zmienić źródło danych tabeli przestawnej w obiekcie arkusza zawierającym dane źródłowe ( nie zawiera tabel przestawnych).

Naciśnij CTRL + F11, aby otworzyć edytor VB. Teraz przejdź do eksploratora projektów i znajdź arkusz zawierający dane źródłowe. Kliknij go dwukrotnie.

Otworzy się nowy obszar kodowania. Możesz nie widzieć żadnych zmian, ale teraz masz dostęp do zdarzeń arkusza.

Kliknij menu rozwijane po lewej stronie i wybierz arkusz. Z menu rozwijanego po lewej stronie wybierz opcję dezaktywuj. Zobaczysz puste napisy napisane w nazwie obszaru kodu workheet_deativate. Nasz kod do dynamicznie zmieniających się danych źródłowych i odświeżania tabeli przestawnej zostanie umieszczony w tym bloku kodu. Ten kod będzie działał za każdym razem, gdy przełączysz się z arkusza danych na inny arkusz. Możesz przeczytać o wszystkich zdarzeniach w arkuszu tutaj.

Teraz jesteśmy gotowi do wdrożenia kodu.

Kod źródłowy do dynamicznej aktualizacji wszystkich tabel przestawnych w skoroszycie z nowym zakresem

Aby wyjaśnić, jak to działa, mam skoroszyt. Ten skoroszyt zawiera trzy arkusze. Arkusz1 zawiera dane źródłowe, które mogą ulec zmianie. Sheet2 i Sheet3 zawierają tabele przestawne, które zależą od danych źródłowych sheet2.

Teraz napisałem ten kod w obszarze kodowania Sheet1. Używam zdarzenia Worksheet_Deactivate, więc ten kod działa, aby zaktualizować tabela przestawna za każdym razem, gdy przechodzimy z arkusza danych źródłowych.

Jeśli masz podobny skoroszyt, możesz bezpośrednio skopiować te dane. Wyjaśniłem, że ten kod działa poniżej, abyś mógł go zmodyfikować zgodnie ze swoimi potrzebami.

Efekt tego kodu możesz zobaczyć w gifie poniżej.

Jak ten kod automatycznie się zmienia dane źródłowe i zaktualizować tabele przestawne?

Przede wszystkim użyliśmy zdarzenia workheet_deactivate. To zdarzenie jest wyzwalane tylko wtedy, gdy arkusz zawierający kod jest przełączany lub dezaktywowany. Tak więc kod działa automatycznie.

Aby dynamicznie pobrać całą tabelę jako zakres danych, określamy ostatni wiersz i ostatnią kolumnę.

lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row

lstcol = Cells (1, Columns.Count) .End (xlToL eft) .Column

Używając tych dwóch liczb, definiujemy source_data. Jesteśmy pewni, że zakres danych źródłowych zawsze będzie zaczynał się od A1. Możesz zdefiniować własne odwołanie do komórki początkowej.

Set source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))

Teraz mamy dane źródłowe, które są dynamiczny. Musimy go po prostu użyć w tabeli przestawnej.

Ponieważ nie wiemy, ile tabel przestawnych będzie zawierał skoroszyt naraz, przejdziemy przez każdy arkusz i tabele przestawne każdego arkusza. nie pozostała żadna tabela przestawna. W tym celu używamy zagnieżdżonych pętli for.

For Each ws In ThisWorkbook.Worksheets

For Each pt In w.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Next pt

Next ws

Pierwsza pętla przechodzi przez każdy arkusz. Druga pętla iteruje po każdej tabeli przestawnej w arkuszu.

Tabele przestawne są przypisane do zmiennej pt. Używamy metody ChangePivotCache obiektu pt. Dynamicznie tworzymy pivot cache przy użyciu metody ThisWorkbook.PivotCaches.Create

Metoda ta przyjmuje dwie zmienne SourceType i SourceData. Jako typ źródła deklarujemy xlDatabase i jako SourceData przekazujemy zakres source_data, który obliczyliśmy wcześniej.

I że i to jest to. Nasze tabele przestawne są zautomatyzowane. Spowoduje to automatyczną aktualizację wszystkich tabel przestawnych w skoroszycie.

A więc tak, w ten sposób możesz dynamicznie zmieniać zakresy źródeł danych wszystkich tabel przestawnych w skoroszycie w programie Excel. Mam nadzieję, że wystarczająco wyjaśniłem. Jeśli masz jakieś pytania dotyczące tego artykułu, daj mi znać w sekcji komentarzy poniżej.

Jak dynamicznie aktualizować zakres źródła danych tabeli przestawnej w programie Excel: Aby dynamicznie zmieniać zakres danych źródłowych tabel przestawnych, używamy pamięci podręcznych przestawnych. Tych kilka wierszy może dynamicznie aktualizować dowolną tabelę przestawną, zmieniając zakres danych źródłowych.

Jak automatycznie odświeżyć tabele przestawne za pomocą VBA: Aby automatycznie odświeżyć tabele przestawne, możesz użyć zdarzeń VBA. Użyj tej prostej linii kodu, aby automatycznie zaktualizować tabelę przestawną. Możesz użyć jednej z 3 metod automatycznego odświeżania tabel przestawnych.

Uruchom makro, jeśli jakakolwiek zmiana dokonana na arkuszu w określonym zakresie: w praktyce VBA będziesz musiał uruchamiać makra, gdy określony zakres lub zmiany komórek. W takim przypadku, aby uruchomić makra po zmianie zakresu docelowego, używamy zdarzenia zmiany.

Uruchom makro po wprowadzeniu jakiejkolwiek zmiany na arkuszu | Aby więc uruchomić makro po każdej aktualizacji arkusza, używamy zdarzeń arkusza 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 jest naprawdę przydatne, gdy chcesz, aby makra były uruchamiane, gdy wystąpi określone zdarzenie w arkuszu.

Popularne artykuły:

50 skrótów programu Excel zwiększających produktywność | Szybciej wykonuj swoje zadanie. Te 50 skrótów sprawi, że będziesz pracować jeszcze szybciej 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 w różnych zakresach i arkuszach.

LICZ.JEŻELI w programie Excel 2016 | Policz wartości w warunkach, korzystając z tej niesamowitej funkcji. Nie musisz filtrować danych, aby policzyć określoną wartość. Funkcja Countif jest niezbędna do przygotowania pulpitu nawigacyjnego.

Jak korzystać z funkcji SUMIF w programie Excel | Jest to kolejna podstawowa funkcja pulpitu nawigacyjnego. Pomaga to podsumować wartości na określonych warunkach.