Tabela przestawna Excel VBA
Tabela przestawna VBA pomaga podsumowywać raporty z dużego zestawu danych. Pivot można uznać za niewielką formę całego zestawu danych. Szybki podgląd dużego zestawu danych jest możliwy dzięki tabeli przestawnej. Tabela przestawna to prosty sposób na odpowiednie filtrowanie danych. Z dostępnych danych możesz podświetlić dane tak, jak chcesz. Tabela przestawna pozwala łączyć ogromne dane, analizować dane i tworzyć raporty spełniające wymagania biznesowe.
Program Excel oferuje wbudowaną tabelę przestawną, którą można łatwo utworzyć, stosując tabelę przestawną na arkuszu danych. Możliwe jest automatyczne generowanie raportu w programie Excel po dostarczeniu danych. Kody VBA pomogą Ci stworzyć automatyczną tabelę przestawną.
Jak utworzyć tabelę przestawną za pomocą Excel VBA?
Typowe kroki w celu wstawienia tabeli przestawnej to najpierw wstawienie tabeli przestawnej z menu Wstaw, a następnie wybranie tabel, które chcesz zmienić, w tabelę przestawną. Wybrana tabela stanie się danymi źródłowymi, a tabela przestawna zostanie odpowiednio utworzona.
Aby zbudować raport przestawny, musisz wybrać pola filtrów, wartości itp. W podobny sposób kroki te można zautomatyzować za pomocą kodów VBA. Potrzebujemy danych arkusza programu Excel do działania z tabelą przestawną. Nauczmy się, jak utworzyć tabelę przestawną za pomocą kodów Excel VBA.
Możesz pobrać ten szablon Excel tabeli przestawnej VBA tutaj - Szablon Excel tabeli przestawnej VBAKrok 1 : Tabela przestawna powinna być traktowana jako obiekt odniesienia. Utwórz obiekt dla tabeli przestawnej na dalszych kodach, ten obiekt będzie używany do wskazywania tabeli przestawnej. Nazwij zmienną jako tabelę pvt. Zadeklaruj funkcję i obiekt tabeli przestawnej.
Kod:
Sub PivotTable () Dim pvtable Jako Sub PivotTable End Sub
Krok 2: Aby zachować pliki danych, potrzebujemy bufora przestawnego, aby zadeklarować źródło danych. Przed utworzeniem tabeli przestawnej zadeklaruj zmienną pieniężną przestawną. Zadeklaruj zmienną pvcache dla bufora przestawnego.
Kod:
Dim pvcache As PivotCache
Krok 3: Dane znajdują się w arkuszu i powinny określać zakres trafienia w potrzebną komórkę. W tabeli przestawnej dane źródłowe są rozłożone w postaci wierszy i kolumn, więc aby wskazać konkretny zakres, potrzebujemy zmiennej. Zdefiniuj pvrange jako zmienną zakresu.
Kod:
Dim pvrange As Range
Krok 4: Arkusz roboczy musi wstawić tabelę przestawną, którą należy utworzyć. Zadeklaruj zmienną jako arkusz roboczy. Zdefiniuj arkusz pv jako arkusz roboczy.
Kod:
Dim pvsheet As Worksheet
Krok 5: Potrzebujesz podobnej zmiennej, aby użyć arkusza danych, który powinien zawierać dane, które chcesz wykreślić jako tabelę przestawną. Zatem zmienna arkusza danych jest zadeklarowana jako pdsheet.
Kod:
Dim pdsheet As Worksheet
Krok 6: Potrzebujesz dwóch dodatkowych zmiennych jako długiego typu danych, aby wskazać ostatnio używany wiersz i kolumnę do utworzenia tabeli przestawnej. Może to być dowolny wiersz lub kolumna, więc istnieje szansa, że liczba wierszy i kolumn będzie większa niż całkowity limit danych. Nazwijmy to plr i plc.
Kod:
Dim plr As Long Dim plc As Long
Krok 7: Następnym krokiem jest usunięcie, jeśli istnieje już jakaś tabela przestawna. Pomoże to uniknąć nieporozumień dotyczących tabeli, na której mają być drukowane dane źródłowe. Więc usuń poprzedni arkusz tabeli przestawnej i utwórz nowy arkusz, aby wstawić tabelę przestawną.
Kod:
W przypadku błędu Wznów następny Application.DisplayAlerts = Fałszywy Application.ScreenUpdating = Fałszywy arkusz roboczy („arkusz pvs”). Usuń „, aby usunąć istniejącą tabelę przestawną w arkuszu. Dodaj:: ActiveSheet”, aby dodać nowy arkusz ActiveSheet.Name = ” pvsheet ”, aby zmienić nazwę arkusza roboczego na„ pvsheet ”w przypadku błędu GoTo 0
Krok 8: Przypisz zmienną obiektową odpowiednio dla arkusza przestawnego i arkusza danych do zmiennych pvsheet i pdsheet. Zostanie to wykorzystane dalej do określenia arkuszy.
Kod:
Ustaw pvsheet = Arkusze („pvsheet”) Ustaw pdsheet = Arkusze („pdsheet”)
Krok 9: Po ustawieniu arkuszy roboczych następnym elementem jest to, że potrzebujemy ostatnio używanego wiersza i kolumny do utworzenia raportu przestawnego. Znajdź ostatnio używany wiersz i kolumnę za pomocą deklarowanych zmiennych plr i plc.
Kod:
'dwie zmienne do znalezienia Ostatnio używany wiersz i kolumna w pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft). Kolumna
Krok 10: Jak wspomnieliśmy wcześniej, dane w arkuszu są określone przez zakres komórek. Musisz ustawić zakres obrotu w następnym kroku. Jest już zadeklarowany jako zmienny, aby użyć zakresu obrotu „pvrange”.
Kod:
'inicjowanie zakresu danych tabeli przestawnej Ustaw pvrange = pdsheet.Cells (1, 1). Resize (plr, plc)
Ponieważ zakres jest ustawiany za pomocą właściwości resize zakresów komórek, zmieni rozmiar pvrange, odpowiednio dostosuje zakres przestawny. Pvrange dostosuje się, jeśli nastąpi jakieś dodanie lub usunięcie wierszy lub kolumny.
Krok 11: Czas ustawić pamięć podręczną przestawną, która jest źródłem tabeli przestawnej. Użyj obiektu pvcache, aby ustawić źródłową pamięć podręczną.
Kod:
'pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)
Krok 12: Tutaj raport sprzedaży dla różnych produktów zostanie przekształcony w tabelę przestawną. Utwórz pustą tabelę przestawną, do której możesz dalej dodać zestaw danych.
Kod:
'nowa pusta tabela przestawna Ustaw pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")
Krok 13: Po wstawieniu osi przestawnej musisz określić różne pola, które chcesz wstawić do tabeli przestawnej. Więc wstaw pole pierwszego rzędu. Tutaj pierwszy rząd zaczyna się od produktu.
Kod:
„Wstaw produkt do wiersza zapisanego za pomocą pvsheet.PivotTables („ Sales_Report ”). PivotFields („ Product ”) .Orientation = xlRowField .Position = 1 End With
Krok 14: Następnym jest określenie drugiego pola, które chcesz wstawić do tabeli przestawnej. W ten sam sposób wstaw tabelę pola drugiego rzędu do tabeli przestawnej.
Kod:
„Wstaw ulicę do wiersza zarchiwizowanego i ustaw pozycję 2 za pomocą pvsheet.PivotTables („ Sales_Report ”). PivotFields („ Street ”) .Orientation = xlRowField .Position = 2 End With
Krok 15: Wstaw następne pole do tabeli przestawnej, a następne to miasto. Podaj kod, aby wstawić pole miasta.
Kod:
„Wstaw miasto do kolumny wypełnionej pvsheet.PivotTables („ Sales_Report ”). PivotFields („ Town ”) .Orientation = xlColumnField .Position = 1 End With
Krok 16: W tym celu wstawione dane były typu tekstowego. Teraz musimy wstawić liczbę sprzedaży do tabeli przestawnej.
Kod:
„Wstaw kolumnę Sprzedaż do pola danych za pomocą pvsheet.PivotTables („ Sales_Report ”). PivotFields („ Sales ”) .Orientation = xlDataField .Position = 1 End With
Krok 17: Wstawiłeś pola, które muszą utworzyć tabelę przestawną. Tabela przestawna jest już prawie gotowa, teraz możesz ustawić format tabeli przestawnej. Określi to typ tabeli poprzez styl tabeli. Układ osi rzędów jest również ustawiony w żądany sposób.
Kod:
'ustaw format tabeli przestawnej pvsheet.PivotTables („Sales_Report”). ShowTableStyleRowStripes = True pvsheet.PivotTables („Sales_Report”). TableStyle2 = „PivotStyleMedium14”
Krok 18: Aby wyświetlić pozycje wartości wiersza w formie tabeli, dodaj poniższy kod na dole.
Kod:
', aby wyświetlić tabelę przestawną w formie tabeli pvsheet.PivotTables („Sales_Report”). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True
Krok 19: Naciśnij przycisk uruchamiania lub naciśnij klawisz F5, aby uruchomić kod. Spowoduje to utworzenie tabeli przestawnej z arkusza źródła danych. Dane podane w wierszach i kolumnach zostaną zmienione w tabelę przestawną. Tabela przestawna będzie widoczna w arkuszu tabeli przestawnej.
Sprawdź dane wyjściowe i zobaczysz, że źródło danych jest konwertowane na tabelę przestawną, jak poniżej, wspomniane kolumny są konwertowane na formularze filtrów.
Pola przestawne są widoczne po prawej stronie. Możesz wprowadzać zmiany zgodnie z potrzebami dotyczącymi sposobu prezentacji danych.
W celach informacyjnych podałem kod poniżej.
Kod:
Sub PivotTable () Dim pvtable As PivotTable Dim pvcache As PivotCache Dim pvrange As Range Dim pvsheet As Arkusz Dim pdsheet As Arkusz Dim plr As Long Dim plc As Long Po wznowieniu błędu Następna aplikacja.DisplayAlerts = False Application.ScreenUpdating = Fałszywe arkusze „) .Usuń”, aby usunąć istniejącą tabelę przestawną w arkuszu. Arkusze dodawane po: = ActiveSheet, aby dodać nowy arkusz ActiveSheet.Name = „pvsheet”, aby zmienić nazwę arkusza na „pvsheet” W przypadku błędu GoTo 0 Ustaw pvsheet = Arkusze („pvsheet”) Ustaw dwie zmienne pdsheet = Arkusze („pdsheet”) ”, aby znaleźć ostatnio używany wiersz i kolumnę w arkuszu pds plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Komórki (1, Columns.Count) .End (xlToLeft) .Column 'inicjowanie zakresu danych tabeli przestawnej Ustaw pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Ustaw pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'nowa pusta tabela przestawna Ustaw pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Wstaw produkt do wiersza zapisanego za pomocą pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 End With' Insert Street to Wiersz złożony i pozycja 2 za pomocą pvsheet.PivotTables („Sales_Report”). PivotFields („Street”) .Orientation = xlRowField .Position = 2 End With 'Insert town to Column Filed With pvsheet.PivotTables („Sales_Report”). PivotFields („ Town ") .Orientation = xlColumnField .Position = 1 End With 'Wstaw kolumnę Sales do pola danych za pomocą pvsheet.PivotTables („ Sales_Report ”). PivotFields („ Sales ”) .Orientation = xlDataField .Position = 1 End With” ustaw format Tabela przestawna pvsheet.PivotTables („Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables („Sales_Report"). TableStyle2 = "PivotStyleMedium14" ', aby wyświetlić tabelę przestawną w formie tabelarycznej pvsheet. .DisplayAlerts = True Application.ScreenUpdating = True End Sub
Rzeczy do zapamiętania
- Podaj arkusz źródłowy, w którym dane mają zostać dostarczone dla tabeli przestawnej.
- Początkowy wiersz i kolumna powinny być odpowiednio wymienione.
- Użyj niezbędnych obiektów, aby wskazać pamięć podręczną przestawną, zakres, początek i koniec wierszy.
- Ułóż źródło danych w odpowiednim formacie, ponieważ jest to proces automatyzacji.
Polecane artykuły
Jest to przewodnik po tabeli przestawnej VBA. Tutaj omawiamy sposób tworzenia tabeli przestawnej za pomocą kodów VBA w programie Excel wraz z przykładem i szablonem programu Excel do pobrania. Możesz także przejrzeć następujące artykuły, aby dowiedzieć się więcej -
- Jak usunąć tabelę przestawną w programie Excel?
- Korzystanie z VBA w PowerPoint
- Tabela przestawna z wieloma arkuszami
- Jak utworzyć krajalarkę tabel przestawnych?
- Odśwież tabelę przestawną w VBA
- VBA o błędzie Wznów dalej (przykłady)
- Aktualizacja Excel VBA