Pyyhkäise näyttääksesi valikon

Pivot-taulukko tiivistää taulukon vetämällä kenttiä riveihin, sarakkeisiin ja arvoihin — ja jokaiselle näistä vedä ja pudota -toiminnoista löytyy suora VBA-vastine, mikä tarkoittaa, että koko Pivot-taulukkoraportti voidaan rakentaa makrolla alusta asti aina, kun uutta dataa saapuu.

Pivot-taulukon rakentaminen

Sub BuildProfitPivot()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim tbl As ListObject
    Dim pc As PivotCache
    Dim pt As PivotTable
 
    Set wsData = ThisWorkbook.Worksheets("Reports")
    Set tbl = wsData.ListObjects("tblReports")
 
    ' start clean: remove an existing Pivot sheet if this has run before
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Pivot").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
 
    Set wsPivot = ThisWorkbook.Worksheets.Add
    wsPivot.Name = "Pivot"
 
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=tbl.Range)
 
    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="ptProfitByRegion")
 
    With pt
        .PivotFields("Region").Orientation = xlRowField
        .PivotFields("Month").Orientation = xlColumnField
        .AddDataField .PivotFields("Profit"), "Sum of Profit", xlSum
    End With
End Sub

Rivikohtainen läpikäynti

On Error Resume Next yhdessä DisplayAlerts-kytkimen ja .Delete-komennon kanssa on turvallinen "poista jos olemassa" -malli: jos yritetään poistaa laskentataulukkoa, jota ei ole olemassa, VBA normaalisti pysäyttäisi makron virheen vuoksi, mutta On Error Resume Next käskee VBA:ta jatkamaan hiljaa kyseisen virheen ohi; DisplayAlerts = False estää Excelin oman "Haluatko varmasti poistaa tämän taulukon?" -vahvistusikkunan;
On Error GoTo 0 heti perään palauttaa normaalin virheraportoinnin — jättäen On Error Resume Next aktiivisena koko Sub-proseduurin ajan, mikä nielisi hiljaisesti myös kaikki myöhemmät, asiaan liittymättömät virheet, mikä on ansa, jota kannattaa välttää;
ThisWorkbook.PivotCaches.Create ottaa tilannevedoksen taulukon tiedoista — PivotCache, ei vielä PivotTable — joka on objekti, josta jokainen PivotTable todellisuudessa rakennetaan taustalla;
pc.CreatePivotTable muuttaa tämän tilannevedoksen näkyväksi PivotTable-taulukoksi, joka sijoitetaan uuden Pivot-taulukon soluun A3 ja nimetään ptProfitByRegion, jotta myöhempi koodi (esim. RefreshTable) löytää sen nimen perusteella;
PivotFields("Region").Orientation = xlRowField ja sitä seuraava Month-rivi ovat suoraa koodivastinetta sille, että vetäisit Region-kentän Rivit-laatikkoon ja Month-kentän Sarakkeet-laatikkoon kenttäluettelossa;
AddDataField täyttää Arvot-alueen — toinen argumentti ("Sum of Profit") on vain sarakeotsikkona näkyvä nimi, ja xlSum kertoo Excelille, että arvot lasketaan yhteen eikä esimerkiksi keskiarvoisteta tai lasketa kappalemäärää.

Raporttien päivittäminen

Kun PivotTable on luotu, sitä ei rakenneta uudelleen aina kun uutta dataa tulee — se päivitetään, mikä on nopeampaa ja säilyttää käyttäjän tekemät mahdolliset asettelumuutokset:

ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion").RefreshTable

Tämä yksi rivi lukee PivotCache-muistin nykyisestä tblReports-tilasta ja päivittää kaikki PivotTable-taulukon luvut vastaamaan sitä — mutta jättää asettelun täysin ennalleen, mukaan lukien kaikki sarakeleveydet, lukumuotoilut tai kenttäjärjestykset, joita käyttäjä on käsin säätänyt Pivotin luonnin jälkeen. Tämä on tärkein etu verrattuna BuildProfitPivotin uudelleenkutsumiseen: alusta asti rakentaminen loisi taulukon uudelleen ja poistaisi kaikki nämä käsin tehdyt muutokset.

PivotChartien päivittäminen

PivotTableen perustuva PivotChart päivittää tietonsa automaattisesti aina, kun PivotTable päivitetään — joten taulukon päivittäminen riittää yleensä pitämään myös liitetyn kaavion ajan tasalla:

Dim pt As PivotTable
Set pt = ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion")
pt.RefreshTable
' any PivotChart based on pt updates automatically — no extra code needed

Tämä kannattaa verrata tavallisiin kaavioihin, joita käsitellään seuraavassa osiossa: tavallinen kaavio tarvitsee erillisen SetSourceData-kutsun osoittaakseen uuteen dataan, kun taas PivotChart on pysyvästi linkitetty PivotTableen ja seuraa sitä automaattisesti. Jos koontinäyttö tarvitsee kaavion, joka aina heijastaa uusimmat PivotTable-luvut mahdollisimman vähällä koodilla, PivotChartin rakentaminen on yleensä parempi valinta kuin itsenäinen kaavio.

Tehtävä

Suorita BuildProfitPivot täsmälleen ohjeen mukaan ja varmista, että uusi "Pivot"-taulukko ilmestyy, jossa Region on riveillä ja Month sarakkeissa.
Lisää manuaalisesti uusi March-rivi tblReports-taulukkoon kuvitteelliselle kuudennelle alueelle ja suorita vain RefreshTable-rivi — varmista, että Pivot päivittyy ilman uudelleenrakennusta.
Muokkaa BuildProfitPivot-makroa niin, että se tiivistää Sales-arvot Profitin sijaan ja vaihtaa Regionin ja Monthin paikat niin, että Month on riveillä ja Region sarakkeissa.

Ohje

Tässä on koodi, jolla lisätään kuudes alue uutena maaliskuun rivinä taulukkoon tblReports käyttämällä ListRows.Add -metodia manuaalisen syöttämisen sijaan:

Sub AddSixthRegion()
    Dim tbl As ListObject
    Dim newRow As ListRow

    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")
    Set newRow = tbl.ListRows.Add

    newRow.Range(1, 1).Value = "March"        ' Month
    newRow.Range(1, 2).Value = "Southwest"    ' Region
    newRow.Range(1, 3).Value = 41500          ' Sales
    newRow.Range(1, 4).Value = 28200          ' Expenses
    newRow.Range(1, 5).Value = 13300          ' Profit
    newRow.Range(1, 6).Value = 39000          ' Target
End Sub

Suorita tämä kerran ja aja sitten aiemmin esitelty RefreshProfitPivot-makro — Pivot-taulukossa pitäisi nyt näkyä "Southwest" uutena rivinä North-, South-, East-, West- ja Central-alueiden rinnalla, ilman että Pivot-taulukon rakentavaa makroa on tarvinnut muuttaa.

Vihje

1. BuildProfitPivot-makron suorittaminen sellaisenaan

Kopioi Sub täsmälleen sellaisena kuin se on luvussa ja suorita se kerran omassa moduulissasi.
Tarkista Project Explorerista tai taulukkovälilehdistä — pitäisi ilmestyä uusi taulukko nimeltä "Pivot", jossa Region on riveillä ja Month sarakkeissa, ja Profit summataan.

2. Kuudennen alueen lisääminen ja pelkkä päivitys

Syötä uusi rivi suoraan taulukkoon (ei koodilla) — lisää tblReports-taulukon loppuun maaliskuun rivi keksitylle alueelle, esim. "Southwest".
Älä suorita BuildProfitPivot-makroa uudelleen — se poistaisi ja rakentaisi koko Pivot-taulukon uudestaan, mikä ei ole tämän harjoituksen tarkoitus.
Suorita sen sijaan vain luvussa esitelty yhden rivin RefreshTable-komento — sinun täytyy viitata olemassa olevaan Pivot-taulukkoon nimellä, samalla tavalla kuin luvun päivitysesimerkissä.

3. Kenttien vaihtaminen ja yhteenvedon muuttaminen

Kolme riviä With pt -lohkon sisällä täytyy muuttaa: mikä kenttä on xlRowField, mikä on xlColumnField ja mihin kenttään AddDataField viittaa.
Anna tälle muokatulle versiolle eri Sub-nimi ja eri TableName — samojen nimien käyttäminen aiheuttaisi virheen tai ylikirjoittaisi alkuperäisen Pivot-taulukon.
AddDataField-metodille annettava otsikko (toinen argumentti, kuten "Sum of Profit") on vain näyttöteksti — päivitä se vastaamaan sitä, mitä nyt lasketaan yhteen.

Ratkaisu

Kohta 2 — kun olet lisännyt kuudennen alueen rivin käsin, suorita vain tämä:

Sub RefreshProfitPivot()
    ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion").RefreshTable
End Sub

Kohta 3 — erillinen, muokattu versio:

Sub BuildSalesPivotByMonth()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim tbl As ListObject
    Dim pc As PivotCache
    Dim pt As PivotTable

    Set wsData = ThisWorkbook.Worksheets("Reports")
    Set tbl = wsData.ListObjects("tblReports")

    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("SalesPivot").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set wsPivot = ThisWorkbook.Worksheets.Add
    wsPivot.Name = "SalesPivot"

    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=tbl.Range)

    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="ptSalesByMonth")

    With pt
        .PivotFields("Month").Orientation = xlRowField
        .PivotFields("Region").Orientation = xlColumnField
        .AddDataField .PivotFields("Sales"), "Sum of Sales", xlSum
    End With
End Sub

Suorita BuildSalesPivotByMonth ja saat uuden "SalesPivot"-taulukon, jossa Month on riveillä, Region sarakkeissa ja Sales summat taulukon keskellä — alkuperäisen asettelun peilikuva.

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 4. Luku 3

Kysy tekoälyä

Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme

Pivot-taulukoiden Luominen VBA:lla