Tabela przestawna VBA programu Excel
Tabele przestawne są sercem podsumowania raportu z dużą ilością danych. Możemy również zautomatyzować proces tworzenia tabeli przestawnej poprzez kodowanie VBA. Są ważną częścią każdego raportu lub kokpitu, w Excelu łatwo jest tworzyć tabele za pomocą przycisku, ale w VBA musimy napisać kilka kodów, aby zautomatyzować naszą tabelę przestawną, przed excelem 2007 i jego starszymi wersjami w VBA nie musieliśmy utwórz pamięć podręczną dla tabel przestawnych, ale w programie Excel 2010 i nowszych wersjach pamięci podręczne są wymagane.
VBA może zaoszczędzić nam mnóstwo czasu w naszym miejscu pracy, mimo że opanowanie go nie jest takie łatwe, ale warto poświęcić czas, aby się tego nauczyć. Zrozumienie procesu tworzenia tabel przestawnych za pomocą języka VBA zajęło mi 6 miesięcy. Wiesz, co te 6 miesięcy zdziałało dla mnie cuda, ponieważ popełniłem tak wiele błędów, próbując stworzyć tabelę przestawną.
Ale tak naprawdę nauczyłem się na własnych błędach i teraz piszę ten artykuł, aby pokazać, jak tworzyć tabele przestawne za pomocą kodu.
Jednym kliknięciem przycisku możemy tworzyć raporty.
Kroki, aby utworzyć tabelę przestawną w VBA
Możesz pobrać ten szablon tabeli przestawnej VBA tutaj - Szablon tabeli przestawnej VBAAby utworzyć tabelę przestawną, ważne są dane. W tym celu utworzyłem fikcyjne dane, możesz pobrać skoroszyt, aby śledzić ze mną te same dane.
Krok 1: Tabela przestawna to obiekt odwołujący się do tabeli przestawnej, który deklaruje zmienną jako tabele przestawne.
Kod:
Sub PivotTable () Dim PTable As PivotTable End Sub
Krok 2: Zanim utworzymy tabelę przestawną, musimy najpierw utworzyć pamięć podręczną przestawną, aby zdefiniować źródło danych.
W zwykłym arkuszu tabeli przestawnej bez kłopotów utworzymy pamięć podręczną przestawną w tle. Ale w VBA musimy tworzyć.
W tym celu zdefiniuj zmienną PivotCache.
Kod:
Dim PCache As PivotCache
Krok 3: Aby określić zakres danych przestawnych, zdefiniuj zmienną jako zakres.
Kod:
Dim PRange As Range
Krok 4: Aby wstawić tabelę przestawną, potrzebujemy osobnego arkusza, aby dodać arkusz roboczy do tabeli przestawnej, zadeklarować zmienną jako arkusz roboczy.
Kod:
Przyciemnij arkusz jako arkusz roboczy
Krok 5: Podobnie, aby odwołać się do danych zawierających arkusz roboczy, zadeklaruj jeszcze jedną zmienną jako arkusz roboczy.
Kod:
Przyciemnij arkusz DS jako arkusz roboczy
Krok 6: Wreszcie, aby znaleźć ostatnio używany wiersz i kolumnę, zdefiniuj dwie dodatkowe zmienne jako Long.
Kod:
Dim LR As Long Dim LC As Long
Krok 7: Teraz musimy wstawić nowy arkusz, aby utworzyć tabelę przestawną. Wcześniej, jeśli jest tam jakikolwiek arkusz przestawny, musimy go usunąć.
Krok 8: Teraz ustaw zmienną obiektu PSheet i DSheet odpowiednio na Pivot Sheet i Data Sheet.
Krok 9: Znajdź ostatnio używany wiersz i ostatnio używaną kolumnę w arkuszu danych.
Krok 10: Teraz ustaw zakres obrotu, używając ostatniego wiersza i ostatniej kolumny.
Spowoduje to idealne ustawienie zakresu danych. Automatycznie wybierze zakres danych, nawet jeśli nastąpi dodanie lub usunięcie danych w arkuszu danych.
Krok 11: Zanim utworzymy tabelę przestawną, musimy utworzyć pamięć podręczną przestawną. Ustaw zmienną pamięci podręcznej obrotu za pomocą poniższego kodu VBA.
Krok 12: Teraz utwórz pustą tabelę przestawną.
Krok 13: Po wstawieniu tabeli przestawnej musimy najpierw wstawić pole wiersza. Więc wstawię pole wiersza jako moją kolumnę Kraj.
Uwaga: Pobierz skoroszyt, aby zrozumieć kolumny danych.
Krok 14: Teraz jeszcze jeden element wstawię do pola wiersza jako element drugiej pozycji. Wstawię Produkt jako drugą pozycję w polu wiersza.
Krok 15: Po wstawieniu kolumn do pola wiersza musimy wstawić wartości do pola kolumny. Wstawię „Segment” do pola kolumny.
Krok 16: Teraz musimy wstawić liczby do pola danych. Dlatego wstaw „Sprzedaż” w polu danych.
Krok 17: Skończyliśmy z częścią podsumowującą tabelę przestawną, teraz musimy sformatować tabelę. Aby sformatować tabelę przestawną, użyj poniższego kodu.
Aby wyświetlić pozycje wartości w wierszach w formie tabelarycznej, dodaj poniższy kod na dole.
Ok, skończymy, jeśli uruchomimy ten kod za pomocą klawisza F5 lub ręcznie, powinniśmy otrzymać tabelę przestawną w ten sposób.
W ten sposób, używając kodowania VBA, możemy zautomatyzować proces tworzenia tabeli przestawnej.
W celach informacyjnych podałem poniższy kod.
Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
