W poprzednim artykule dowiedzieliśmy się, jak dynamicznie zmieniać i aktualizować poszczególne tabele przestawne przy zmniejszaniu lub rozszerzaniu źródeł danych.
W tym artykule dowiemy się, jak 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, do pisania kodu użyjemy modułów arkusza zamiast modułu podstawowego. Umożliwi nam to korzystanie ze zdarzeń arkusza roboczego.
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, który zawiera dane źródłowe (nie zawiera tabel przestawnych).
Naciśnij klawisze 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ń w arkuszu.
Kliknij lewe menu rozwijane i wybierz arkusz. Z menu rozwijanego po lewej stronie wybierz dezaktywuj. Zobaczysz pustą podpisaną w nazwie obszaru kodowego worksheet_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 zostanie uruchomiony za każdym razem, gdy przejdziesz z arkusza danych do dowolnego innego arkusza. Tutaj możesz przeczytać o wszystkich zdarzeniach w arkuszu.
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 zeszyt ćwiczeń. Ten skoroszyt zawiera trzy arkusze. Arkusz1 zawiera dane źródłowe, które mogą ulec zmianie. Arkusze Arkusz2 i Arkusz3 zawierają tabele przestawne, które zależą od danych źródłowych arkusza2.
Teraz napisałem ten kod w obszarze kodowania Sheet1. Używam zdarzenia Worksheet_Deactivate, aby ten kod był uruchamiany w celu aktualizacji tabeli przestawnej za każdym razem, gdy przełączamy się ze źródłowego arkusza danych.
Private Sub Worksheet_Deactivate() Dim source_data As Range 'Określanie ostatniego wiersza i numeru kolumny lstrow = Cells(Rows.Count, 1).End(xlUp).Row lstcol = Cells(1, Columns.Count).End(xlToLeft).Column 'Ustawianie nowego zakresu Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol)) 'Kod w pętli przez każdy arkusz i tabelę przestawną For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables pt. ChangePivotCache _ ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=source_data) Next pt Next ws End Sub
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 z własnymi potrzebami.
Możesz zobaczyć efekt tego kodu w gif poniżej.
W jaki sposób ten kod automatycznie zmienia dane źródłowe i aktualizuje tabele przestawne?
Przede wszystkim użyliśmy zdarzenia worksheet_deactivate. To zdarzenie jest wyzwalane tylko wtedy, gdy arkusz zawierający kod zostanie przełączony lub dezaktywowany. Tak więc kod uruchamia się automatycznie.
Aby dynamicznie uzyskać 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(xlToLeft).Column
Używając tych dwóch liczb definiujemy source_data. Jesteśmy zdecydowani, że zakres danych źródłowych będzie zawsze zaczynał się od A1. Możesz zdefiniować własne odwołanie do komórki początkowej.
Ustaw source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))
Teraz mamy dane źródłowe, które są dynamiczne. Wystarczy go użyć w tabeli przestawnej.
Ponieważ nie wiemy, ile tabel przestawnych będzie zawierać jednocześnie skoroszyt, będziemy przechodzić przez każdy arkusz i tabele przestawne każdego arkusza. Aby nie pozostała żadna tabela przestawna. W tym celu używamy zagnieżdżonych pętli for.
Dla każdego ws In ThisWorkbook.Worksheets
Dla każdego punktu w ws.PivotTables
pkt.ZmieńPivotCache _
ThisWorkbook.PivotCaches.Create( _
Typ źródła:=xlBaza danych, _
Dane źródłowe:=dane_źródłowe)
Następny pkt
Następny
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 pkt. Używamy metody ChangePivotCache obiektu pt. Dynamicznie tworzymy pamięć podręczną przestawną za pomocą ThisWorkbook.PivotCaches.Create
Metoda. Ta metoda przyjmuje dwie zmienne SourceType i SourceData. Jako typ źródła deklarujemy xlDatabase, a jako SourceData przekazujemy wcześniej obliczony przez nas zakres source_data.
I to wszystko. Nasze tabele przestawne są zautomatyzowane. Spowoduje to automatyczną aktualizację wszystkich tabel przestawnych w skoroszycie.
Tak, 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ó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.
Jak automatycznie odświeżać tabele przestawne za pomocą VBA: Aby automatycznie odświeżyć tabele przestawne, możesz użyć zdarzeń VBA. Użyj tego prostego wiersza kodu, aby automatycznie zaktualizować tabelę przestawną. Możesz użyć jednej z 3 metod automatycznego odświeżania tabel przestawnych.
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.