VBA -opas pivot -taulukoihin

Tämä opetusohjelma osoittaa, miten voit käyttää pivot -taulukoita VBA: n avulla.

Pivot -taulukot ovat tietojen yhteenvetotyökaluja, joiden avulla voit piirtää tärkeitä tietoja ja yhteenvetoja tiedoistasi. Katsotaanpa esimerkkiä: meillä on lähdetietojoukko soluissa A1: D21, jotka sisältävät myytyjen tuotteiden tiedot.

Arvon saaminen GetPivotDatan avulla

Oletetaan, että sinulla on pivot -taulukko nimeltä PivotTable1, jonka Arvot/tietokentässä on Myynti, Tuote rivit -kentässä ja Alue sarakekentässä. Voit palauttaa arvoja pivot -taulukoista käyttämällä PivotTable.GetPivotData -menetelmää.

Seuraava koodi palauttaa Pivot -taulukosta 1 130,00 dollaria (itäisen alueen kokonaismyynti):

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Myynti", "Alue", "Itä")

Tässä tapauksessa myynti on "DataField", "Field1" on alue ja "Item1" on itä.

Seuraava koodi palauttaa pivot -taulukosta 980 dollaria (tuotteen ABC kokonaismyynti Pohjois -alueella):

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Myynti", "Tuote", "ABC", "Alue", "Pohjoinen")

Tässä tapauksessa myynti on ”DataField”, “Field1” on tuote, “Item1” on ABC, “Field2” on alue ja “Item2” on pohjoinen.

Voit myös sisällyttää enemmän kuin 2 kenttää.

GetPivotDatan syntaksi on:

GetPivotData (DataField, Kenttä 1, Kohta 1, Kenttä2, Kohta2… ) missä:

Parametri	Kuvaus
Tietokenttä	Tietokenttä, kuten myynti, määrä jne., Joka sisältää numeroita.
Kenttä 1	Taulukon sarakkeen tai rivikentän nimi.
Kohta 1	Kohteen nimi kentässä 1 (valinnainen).
Kenttä 2	Taulukon sarakkeen tai rivikentän nimi (valinnainen).
Kohta 2	Kohteen nimi kentässä 2 (valinnainen).

Pivot -taulukon luominen taulukolle

Voidaksemme luoda pivot -taulukon yllä olevan tietoalueen perusteella aktiivisen työkirjan taulukon 1 solussa J2 käytämme seuraavaa koodia:

1234567891011

Laskentataulukot ("Sheet1"). Solut (1, 1) .ValitseActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _)"Sheet1! R1C1: R21C4", versio: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Sheets ("Sheet1"). Valitse

Tulos on:

Pivot -taulukon luominen uudelle taulukolle

Voidaksemme luoda pivot -taulukon yllä olevan tietoalueen perusteella aktiivisen työkirjan uudelle arkille käytämme seuraavaa koodia:

12345678910111213

Laskentataulukot ("Sheet1"). Solut (1, 1) .ValitseArkit LisääActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _)"Sheet1! R1C1: R21C4", versio: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Sheets ("Sheet2"). Valitse

Kenttien lisääminen pivot -taulukkoon

Voit lisätä kenttiä äskettäin luotuun Pivot -taulukkoon nimeltä PivotTable1 yllä olevan tietoalueen perusteella. Huomautus: Pivot -taulukon sisältävän taulukon on oltava aktiivinen arkki.

Jos haluat lisätä tuotteen Rivikenttään, käytä seuraavaa koodia:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Tuote"). Orientation = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Tuote"). Sijainti = 1

Jos haluat lisätä alueen Sarakkeet -kenttään, käytä seuraavaa koodia:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Sijainti = 1

Jos haluat lisätä myynnin Arvot -osioon valuuttanumeromuodolla, käytä seuraavaa koodia:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"Pivot -taulukko1"). PivotFields ("Myynti"), "Myynnin summa", xlSumActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Summa of Sales").NumberFormat = "$#, ## 0.00"Lopeta

Tulos on:

Pivot -taulukon raportin asettelun muuttaminen

Voit muuttaa pivot -taulukon raportin asettelua. Seuraava koodi muuttaa pivot -taulukon raporttiasettelun taulukkomuodoksi:

1	ActiveSheet.PivotTables ("PivotTable1"). TableStyle2 = "PivotStyleLight18"

Pivot -taulukon poistaminen

Voit poistaa pivot -taulukon VBA: n avulla. Seuraava koodi poistaa pivot -taulukon nimeltä PivotTable1 aktiiviselta taulukolta:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueValinta.ClearContents

Alusta kaikki työkirjan pivot -taulukot

Voit muotoilla kaikki työkirjan pivot -taulukot VBA: n avulla. Seuraava koodi käyttää silmukkarakennetta selatakseen työkirjan kaikki taulukot ja poistaa kaikki työkirjan pivot -taulukot:

12345678910111213

AlimuotoiluAllThePivotTablesInAWorkbook ()Dim wks kuten laskentataulukkoDim wb TyökirjanaAseta wb = ActiveWorkbookDim pt Pivot -taulukkonaJokaiselle viikolle wb.SheetsJokaiselle pt: lle viikossa. Pivot -taulukotpt.TableStyle2 = "PivotStyleLight15"Seuraava ptSeuraavat viikotEnd Sub

Saat lisätietoja silmukoiden käytöstä VBA: ssa napsauttamalla tätä.

Pivot -taulukon kenttien poistaminen

Voit poistaa kenttiä pivot -taulukosta VBA: n avulla. Seuraava koodi poistaa Rivit -osion Tuote -kentän pivot -taulukosta nimeltä PivotTable1 aktiivisessa taulukossa:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Tuote"). Suunta = _xlPiilotettu

Suodattimen luominen

Pivot -taulukko nimeltä PivotTable1 on luotu Rivit -osiossa Tuote ja Arvot -osiossa Myynti. Voit myös luoda suodattimen pivot -taulukolle VBA: n avulla. Seuraava koodi luo Suodattimet -osion alueen perusteella suodattimen:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Sijainti = 1

Voit suodattaa pivot -taulukon yksittäisen raporttikohteen perusteella tässä tapauksessa itäisellä alueella käyttämällä seuraavaa koodia:

12345

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). CurrentPage = _"Itään"

Oletetaan, että halusit suodattaa pivot -taulukon useiden alueiden, tässä tapauksessa idän ja pohjoisen, perusteella käyttämällä seuraavaa koodia:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Sijainti = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). _EnableMultiplePageItems = TottaActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region").PivotItems ("Etelä"). Visible = False.PivotItems ("West"). Visible = FalseLopeta

Pivot -taulukon päivittäminen

Voit päivittää pivot -taulukon VBA: ssa. Käytät seuraavaa koodia päivittääksesi tietyn taulukon nimeltä PivotTable1 VBA: ssa:

1	ActiveSheet.PivotTables ("PivotTable1"). PivotCache.Refresh