Obecnie możemy dynamicznie zmieniać lub aktualizować tabele przestawne za pomocą tabel Excel lub dynamicznych nazwanych zakresów. Ale te techniki nie są niezawodne. Ponieważ nadal będziesz musiał ręcznie odświeżyć tabelę przestawną. Jeśli masz duże dane, które zawierają tysiące wierszy i kolumn, tabele Excela niewiele Ci pomogą. Zamiast tego sprawi, że twój plik będzie ciężki. Pozostaje więc tylko VBA.
W tym artykule dowiemy się, jak sprawić, by nasza tabela przestawna automatycznie zmieniała źródło danych. Innymi słowy, zautomatyzujemy ręczny proces zmiany źródła danych, aby dynamicznie uwzględnić nowe wiersze i kolumny dodane do tabel źródłowych i natychmiast odzwierciedlić zmianę w tabeli przestawnej.
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 tabeli przestawnej).
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 tabeli przestawnej o nowy zakres
Aby wyjaśnić, jak to działa, mam zeszyt ćwiczeń. Ten skoroszyt zawiera dwa arkusze. Arkusz1 zawiera dane źródłowe, które mogą ulec zmianie. Arkusz2 zawiera tabelę przestawną, która zależy 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 pt As PivotTable Dim pc As PivotCache Dim source_data As Range lstrow = Cells(Rows.Count, 1)).End(xlUp).Row lstcol = Cells(1, Columns.Count).End(xlToLeft). Zestaw kolumn source_data = Range(Cells(1, 1), Cells(lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data) Set pt = Sheet2.PivotTables("PivotTable1") pt.ChangePivotCache pc Koniec Sub
Jeśli masz podobny skoroszyt, możesz bezpośrednio skopiować te dane. Wyjaśniłem, że ten kod działa poniżej.
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.
Teraz, aby zmienić dane źródłowe tabeli przestawnej, zmieniamy dane w pamięci podręcznej przestawnej.
Tabela przestawna jest tworzona przy użyciu pamięci podręcznej. Pamięć podręczna przestawna zawiera stare dane źródłowe, dopóki tabela przestawna nie zostanie ręcznie odświeżona lub zakres danych źródłowych nie zostanie ręcznie zmieniony.
Stworzyliśmy odniesienia do tabel przestawnych nazwa pt, pamięci podręcznej przestawnej o nazwie pc i zakresu o nazwie źródło_dane. Dane źródłowe będą zawierały całe dane.
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.
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.
Przechowujemy te dane w pamięci podręcznej osi, ponieważ wiemy, że pamięć podręczna osi przechowuje wszystkie dane.
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)
Następnie definiujemy tabelę przestawną, którą chcemy zaktualizować. Ponieważ chcemy zaktualizować tabelę przestawną1 (nazwę tabeli przestawnej. Możesz sprawdzić nazwę tabeli przestawnej na karcie analizy podczas wybierania tabeli przestawnej.) w arkuszu1, ustawiamy pt, jak pokazano poniżej.
Ustaw pt = Arkusz2.Tabele przestawne("Tabela przestawna1")
Teraz po prostu używamy tej pamięci podręcznej do aktualizacji tabeli przestawnej. Używamy metody changePivotCache obiektu pt.
pt.ChangePivotCache pc
I mamy zautomatyzowaną tabelę przestawną. Spowoduje to automatyczną aktualizację tabeli przestawnej. Jeśli masz wiele tabel z tym samym źródłem danych, użyj tej samej pamięci podręcznej w każdym obiekcie tabeli przestawnej.
Więc tak, w ten sposób można dynamicznie zmieniać zakres źródeł danych 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 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.