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 dashboardu, 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. Nawet jeśli opanowanie tego 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 tyle błędów, próbując stworzyć tabelę przestawną.
Ale tak naprawdę nauczyłem się na błędach, a 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
Aby utworzyć tabelę przestawną, ważne jest, aby mieć dane. W tym celu utworzyłem fikcyjne dane. Możesz pobrać skoroszyt, aby wraz ze mną śledzić 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 najpierw utworzymy tabelę przestawną, musimy utworzyć pamięć podręczną przestawną, aby zdefiniować źródło danych.
W zwykłych arkuszach roboczych tabela przestawna bez kłopotania nas utworzy w tle pamięć podręczną przestawną. 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 oddzielnego arkusza, aby dodać arkusze do tabeli przestawnej, aby zadeklarować zmienną jako arkusz roboczy.
Kod:
Przyciemnij arkusz jako arkusz roboczy
Krok 5: Podobnie, aby odwołać się do danych zawierających arkusz, zadeklaruj jeszcze jedną zmienną jako arkusz roboczy.
Kod:
Dim DSheet As Worksheet
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 istnieje jakiś 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 przy użyciu 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ć taką tabelę przestawną.
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 'Spowoduje to usunięcie istniejącego arkusza roboczego tabeli przestawnej Worksheets.Add After: = ActiveSheet' Spowoduje to dodanie nowego arkusza roboczego ActiveSheet.Name =" Arkusz przestawny "'Spowoduje to zmianę nazwy arkusza na„ Arkusz przestawny ”W przypadku błędu Idź do 0 Set PSheet = Worksheets ("Pivot Sheet") Set DSheet = Worksheets ("Data Sheet") 'Znajdź ostatnio używany wiersz i kolumnę w arkuszu danych LR = DSheet.Cells (Rows.Count, 1) .End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Ustaw zakres danych tabeli przestawnej Set PRange = DSheet.Cells (1, 1) .Resize (LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)' Utwórz pustą tabelę przestawną Ustaw PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Cells (1, 1), TableName: = "Sales_Report") 'Wstaw kraj do wiersza wypełnionego za pomocą PSheet.PivotTables ("Sales_Report"). PivotFields ("Country") .Orientation = xlRowField .Position = 1 Koniec z' Wstaw produkt do Wiersz Złożony & pozycja 2 z PSheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 2 Koniec z 'Wstaw segment do kolumny wypełnionej & pozycja 1 z PSheet.PivotTables ("Sales_Report"). PivotFields ("Segment") .Orientation = xlColumnField .Position = 1 End With 'Wstaw kolumnę Sales do pola danych With PSheet.Tabele przestawne ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 Kończy się na 'Formatowanie tabeli przestawnej PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_ReportTables ="). "PivotStyleMedium14" 'Pokaż w formie tabelarycznej PSheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
