Nagu me kõik teame, ei kajastu pöördetabeli lähteandmetes muudatused alati pöördetabelis. Muudatuste nägemiseks peame pöördtabeleid värskendama. Ja kui saadate värskendatud faili ilma pöördtabeleid värskendamata, võite tunda piinlikkust.
Nii et selles artiklis õpime, kuidas pöördtabelit automaatselt värskendada VBA abil. See viis on lihtsam kui ette kujutasite.
See on lihtne süntaks töövihiku pöördtabelite automaatseks värskendamiseks.
'Kood lähteandmelehe objekti privaatse alamlehe_Deaktiveeri () lehe nimi_pöördtabeli tabel.PivotTable -liigendtabelid ("pivot_table_name"). PivotCache.Refresh End Sub
Mis on Pivot -vahemälud?
Iga liigendtabel salvestab andmed pivot -vahemällu. Seetõttu suudab pivot näidata varasemaid andmeid. Pivot -tabelite värskendamisel värskendab see vahemälu uute lähteandmetega, et kajastada pöördtabelis tehtud muudatusi.
Seega vajame pöördtabelite vahemälu värskendamiseks lihtsalt makro. Teeme seda töölehe sündmuse abil, et me ei peaks makro käsitsi käivitama.
Kuhu kodeerida liigendtabelite automaatne värskendamine?
Kui teie lähteandmed ja pöördtabelid on erinevatel lehtedel, peaks VBA -kood algandmete lehele minema.
Siin kasutame sündmust Worksheet_SelectionChange. See paneb koodi käima iga kord, kui vahetame lähteandmete lehelt teisele lehele. Ma selgitan hiljem, miks ma seda sündmust kasutasin.
Siin on mul lähteandmed lehel2 ja liigendtabelid lehel1.
Avage VBE klahvikombinatsiooni CTRL+F11 abil. Projektiuurijas näete kolme objekti: leht 1, leht 2 ja töövihik.
Kuna leht 2 sisaldab lähteandmeid, topeltklõpsake lehe2 objekti.
Nüüd näete koodiala ülaosas kahte rippmenüüd. Valige esimesest rippmenüüst tööleht. Teisest rippmenüüst valige Deaktiveeri. See lisab tühja alamnime Worksheet_Deactivate. Meie kood kirjutatakse sellesse alamkohta. Kõik sellesse alajaotisesse kirjutatud read täidetakse kohe, kui kasutaja lülitub sellelt lehelt mõnele muule lehele.
Lehel 1 on mul kaks pöördtabelit. Tahan värskendada ainult ühte pöördtabelit. Selleks pean teadma pöördtabeli nime. Mis tahes liigendtabeli nime teadmiseks valige selle liigendtabeli mis tahes lahter, minge vahekaardile Pivot tabeli analüüs. Vasakul küljel näete pöördtabeli nime. Siin saate muuta ka pöördtabeli nime.
Nüüd teame pöördetabeli nime, saame kirjutada lihtsa rea pöördetabeli värskendamiseks.
Privaatne alamleht_Deaktiveeri () Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
Ja seda tehakse.
Nüüd, kui vahetate lähteandmetelt üle, käivitatakse see vba -kood pöördetabeli värskendamiseks1. Nagu näete alloleval gifil.
Kuidas värskendada kõiki töövihiku pöördtabeleid?
Ülaltoodud näites tahtsime värskendada ainult ühte konkreetset liigendtabelit. Kuid kui soovite värskendada kõiki töövihiku pöördtabeleid, peate lihtsalt oma koodis kergeid muudatusi tegema.
Privaatne alamleht_Deaktiveeri () 'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Iga arvuti jaoks selles töövihikus.PivotCaches p.Rresh
Selles koodis kasutame töövihiku iga pöörleva vahemälu läbimiseks silmust For. See töövihiku objekt sisaldab kõiki pöördvahemälu. Nende juurde pääsemiseks kasutame ThisWorkbook.PivotCaches.
Miks kasutada sündmust Worksheet_Deactivate?
Kui soovite pöördtabelit värskendada kohe, kui lähteandmetes muudatusi tehakse, kasutage sündmust Worksheet_Change. Aga ma ei soovita seda. See paneb teie töövihiku koodi käivitama iga kord, kui muudate lehte. Enne tulemuse nägemist peate võib -olla tegema sadu muudatusi. Kuid Excel värskendab pöördetabelit iga muudatuse korral. See toob kaasa töötlemisaja ja ressursside raiskamise. Seega, kui teil on pöördtabelid ja andmed erinevatel lehtedel, on parem kasutada töölehe desaktiveerimise sündmust. See võimaldab teil oma tööd lõpetada. Kui lülitate muudatuste nägemiseks pöördtabeli lehtedele, muudab see muudatusi.
Kui teil on samal lehel liigendtabelid ja lähteandmed ning soovite, et liigendtabelid värskendaksid seda automaatselt, võite kasutada funktsiooni Worksheet_Change Event.
Privaatne alamlehe_muutmine (ByVal -sihtmärk vahemikuna) leht 1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub
Kuidas värskendada kõike töövihikutes, kui lähteandmeid muudetakse?
Kui soovite töövihikus kõike värskendada (diagrammid, liigendtabelid, valemid jne), võite kasutada käsku ThisWorkbook.RefreshAll.
Privaatne alamlehe muutmine (ByVal Target kui vahemik) ThisWorkbook.RefreshAll End Sub
Pange tähele, et see kood ei muuda andmeallikat. Seega, kui lisate andmed lähteandmete alla, ei sisalda see kood neid andmeid automaatselt. Lähteandmete salvestamiseks saate kasutada Exceli tabeleid. Kui te ei soovi tabeleid kasutada, saame uute andmete lisamiseks kasutada ka VBA -d. Me õpime seda järgmises õpetuses.
Nii et jah, semu, nii saate Exceli pöördtabeleid automaatselt värskendada. Loodan, et olin piisavalt selgitav ja see artikkel teenis teid hästi. Kui teil on selle teema kohta küsimusi, võite küsida minult allpool kommentaaride jaotises.
Pivot tabeli andmeallika vahemiku dünaamiline värskendamine Excelis: Pivot -tabelite lähteandmete vahemiku dünaamiliseks muutmiseks kasutame pivot -vahemälu. Need paar rida võivad dünaamiliselt värskendada mis tahes liigendtabelit, muutes lähteandmete vahemikku. VBA -s kasutage pöördtabelite objekte, nagu allpool näidatud …
Käivitage makro, kui lehel tehakse kindlaksmääratud vahemikus muudatusi: Oma VBA praktikas oleks teil vajadus käivitada makrosid teatud vahemiku või lahtri muutumisel. Sel juhul kasutame makrosid käivitamiseks, kui sihtvahemikku muudetakse, muutmise sündmust.
Käivitage makro, kui lehel tehakse muudatusi | Nii et makro käivitamiseks iga kord, kui lehte värskendatakse, kasutame VBA töölehe sündmusi.
Lihtsaim VBA kood praeguse rea ja veeru esiletõstmiseks | Kasutage seda väikest VBA -katkendit lehe praeguse rea ja veeru esiletõstmiseks.
Töölehe sündmused Excel VBA -s | Töölehe sündmus on tõesti kasulik, kui soovite oma makrosid käivitada, kui lehel toimub teatud sündmus.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks | Saa oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Funktsioon VLOOKUP Excelis | See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
COUNTIF Excelis 2016 | Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea konkreetse väärtuse loendamiseks oma andmeid filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis | See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.