- Używanie GetPivotData do uzyskania wartości
- Tworzenie tabeli przestawnej na arkuszu
- Tworzenie tabeli przestawnej na nowym arkuszu
- Dodawanie pól do tabeli przestawnej
- Zmiana układu raportu tabeli przestawnej
- Usuwanie tabeli przestawnej
- Sformatuj wszystkie tabele przestawne w skoroszycie
- Usuwanie pól tabeli przestawnej
- Tworzenie filtra
- Odświeżanie tabeli przestawnej
Ten samouczek pokaże, jak pracować z tabelami przestawnymi przy użyciu języka VBA.
Tabele przestawne to narzędzia do podsumowywania danych, których można używać do wyciągania kluczowych wniosków i podsumowań na podstawie danych. Spójrzmy na przykład: mamy zbiór danych źródłowych w komórkach A1:D21 zawierający szczegóły sprzedanych produktów, pokazane poniżej:
Używanie GetPivotData do uzyskania wartości
Załóżmy, że masz tabelę przestawną o nazwie Tabela przestawna1 ze sprzedażą w polu Wartości/dane, Produkt jako pole Wiersze i Region jako pole Kolumny. Za pomocą metody PivotTable.GetPivotData można zwracać wartości z tabel przestawnych.
Poniższy kod zwróci 1130,00 USD (całkowita sprzedaż w regionie Wschód) z tabeli przestawnej:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData("Sprzedaż", "Region", "Wschód") |
W tym przypadku Sprzedaż to „Pole danych”, „Pole1” to Region, a „Pozycja1” to Wschód.
Poniższy kod zwróci 980 USD (całkowita sprzedaż produktu ABC w regionie północnym) z tabeli przestawnej:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData("Sprzedaż", "Produkt", "ABC", "Region", "Północ") |
W tym przypadku Sprzedaż to „Pole Danych”, „Pole1” to Produkt, „Pozycja1” to ABC, „Pole2” to Region, a „Pozycja2” to Północ.
Możesz również dołączyć więcej niż 2 pola.
Składnia GetPivotData to:
Pobierz dane przestawne (Pole danych, Pole1, Przedmiot 1, Pole2, Pozycja2… ) gdzie:
| Parametr | Opis |
|---|---|
| Pole danych | Pole danych, takie jak sprzedaż, ilość itp., które zawiera liczby. |
| Pole 1 | Nazwa pola kolumny lub wiersza w tabeli. |
| Przedmiot 1 | Nazwa towaru w polu 1 (opcjonalnie). |
| Pole 2 | Nazwa pola kolumny lub wiersza w tabeli (opcjonalnie). |
| Pozycja 2 | Nazwa towaru w polu 2 (opcjonalnie). |
Tworzenie tabeli przestawnej na arkuszu
Aby utworzyć tabelę przestawną na podstawie powyższego zakresu danych, w komórce J2 w arkuszu Arkusz1 aktywnego skoroszytu, użyjemy następującego kodu:
| 1234567891011 | Arkusze("Arkusz1").Komórki(1,1).WybierzActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _„Arkusz1!R1C1:R21C4”, Wersja:=xlWersja tabeli przestawnej15). Utwórz tabelę przestawną _TableDestination:="Arkusz1!R2C10", NazwaTabeli:="Tabela Przestawna1", Domyślna Wersja _:=xlWersja tabeli przestawnej15Arkusze("Arkusz1").Wybierz |
Wynik to:
Tworzenie tabeli przestawnej na nowym arkuszu
Aby utworzyć tabelę przestawną na podstawie powyższego zakresu danych, w nowym arkuszu aktywnego skoroszytu, użyjemy następującego kodu:
| 12345678910111213 | Arkusze("Arkusz1").Komórki(1,1).WybierzArkusze.DodajActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _„Arkusz1!R1C1:R21C4”, Wersja:=xlWersja tabeli przestawnej15). Utwórz tabelę przestawną _TableDestination:="Arkusz2!R3C1", NazwaTabeli:="Tabela Przestawna1", Domyślna Wersja _:=xlWersja tabeli przestawnej15Arkusze("Arkusz2").Wybierz |
Dodawanie pól do tabeli przestawnej
Możesz dodać pola do nowo utworzonej tabeli przestawnej o nazwie Tabela przestawna1 na podstawie powyższego zakresu danych. Uwaga: Arkusz zawierający tabelę przestawną musi być arkuszem aktywnym.
Aby dodać produkt do pola wierszy, użyj następującego kodu:
| 123 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Produkt").Orientation = xlRowFieldActiveSheet.PivotTables("Tabela przestawna1").PivotFields("Produkt").Pozycja = 1 |
Aby dodać region do pola kolumny, użyj następującego kodu:
| 123 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlColumnFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1 |
Aby dodać sprzedaż do sekcji wartości w formacie liczbowym waluty, użyj następującego kodu:
| 123456789 | ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _"PivotTable1").PivotFields("Sprzedaż"), "Suma sprzedaży", xlSumZ ActiveSheet.PivotTables("PivotTable1").PivotFields("Suma sprzedaży").NumberFormat = "$#,##0.00"Kończyć z |
Wynik to:
Zmiana układu raportu tabeli przestawnej
Możesz zmienić układ raportu swojej tabeli przestawnej. Poniższy kod zmieni układ raportu tabeli przestawnej na formularz tabelaryczny:
| 1 | ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight18" |
Usuwanie tabeli przestawnej
Możesz usunąć tabelę przestawną za pomocą VBA. Poniższy kod spowoduje usunięcie tabeli przestawnej o nazwie Tabela przestawna1 w aktywnym arkuszu:
| 12 | ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, TrueWybór.WyczyśćZawartość |
Sformatuj wszystkie tabele przestawne w skoroszycie
Wszystkie tabele przestawne w skoroszycie można sformatować za pomocą języka VBA. Poniższy kod wykorzystuje strukturę pętli w celu przechodzenia przez wszystkie arkusze skoroszytu i usuwania wszystkich tabel przestawnych w skoroszycie:
| 12345678910111213 | Formatowanie podrzędne Wszystkie tabele przestawne w skoroszycie()Dim wks jako arkusz roboczyDim wb jako skoroszytUstaw wb = Aktywny skoroszytDim pt As Pivot TableDla każdego tygodnia W wb.SheetsDla każdego pt w wks.Tabele przestawnept.TableStyle2 = "PivotStyleLight15"Następny pktNastępny tydzieńNapis końcowy |
Aby dowiedzieć się więcej o tym, jak korzystać z pętli w VBA, kliknij tutaj.
Usuwanie pól tabeli przestawnej
Pola w tabeli przestawnej można usuwać za pomocą języka VBA. Poniższy kod spowoduje usunięcie pola Produkt w sekcji Wiersze z tabeli przestawnej o nazwie Tabela przestawna1 w aktywnym arkuszu:
| 12 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Produkt").Orientation = _xlUkryty |
Tworzenie filtra
Tabela przestawna o nazwie Tabela przestawna1 została utworzona z produktem w sekcji wiersze i sprzedażą w sekcji wartości. Możesz także utworzyć filtr dla swojej tabeli przestawnej za pomocą VBA. Poniższy kod utworzy filtr oparty na Regionie w sekcji Filtry:
| 123 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlPageFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1 |
Aby filtrować tabelę przestawną na podstawie pojedynczego elementu raportu, w tym przypadku regionu wschodniego, użyj następującego kodu:
| 12345 | ActiveSheet.PivotTables("Tabela przestawna1").PivotFields("Region").ClearAllFiltersActiveSheet.PivotTables("PivotTable1").PivotFields("Region").CurrentPage = _"Wschód" |
Załóżmy, że chcesz filtrować tabelę przestawną na podstawie wielu regionów, w tym przypadku wschodniego i północnego, użyj następującego kodu:
| 1234567891011121314 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlPageFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1ActiveSheet.PivotTables("Tabela przestawna1").PivotFields("Region"). _EnableMultiplePageItems = TrueZ ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").PivotItems("Południe").Visible = Fałsz.PivotItems("Zachód").Visible = FalseKończyć z |
Odświeżanie tabeli przestawnej
Możesz odświeżyć swoją tabelę przestawną w VBA. Aby odświeżyć konkretną tabelę o nazwie PivotTable1 w VBA, użyjesz następującego kodu:
| 1 | ActiveSheet.PivotTables("Tabela przestawna1").PivotCache.Refresh |
