V tem članku se bomo naučili, kako samodejno osvežiti podatke vrtilne tabele v Excelu.
Scenarij:
Kot vsi vemo, kadar koli spremenimo izvorne podatke vrtilne tabele, se to ne odrazi takoj v vrtilni tabeli. Vrtilne tabele moramo osvežiti, da vidimo spremembe, ko odpremo Excelov delovni zvezek. Če pošljete posodobljeno datoteko, ne da bi osvežili vrtilne tabele, se boste morda počutili nerodno. Zato se tukaj naučite, kako poiščite možnost osveževanja pri uporabi vrtilne tabele
Osvežite podatke pri odpiranju datoteke v Excelu
Najprej ustvarite vrtilno tabelo in nato z desno miškino tipko kliknite katero koli celico vrtilne tabele.
Pojdite na Možnosti vrtilne tabele> zavihek Podatki> Potrdite polje, ki pravi Ob odpiranju datoteke osvežite podatke
To bo omogočilo samodejno posodabljanje podatkov vsakič, ko se datoteka odpre.
Primer:
Vse to je lahko zmedeno za razumevanje. Poglejmo, kako uporabiti funkcijo na primeru. Tukaj imamo nekaj podatkov in najprej moramo ustvariti vrtilno tabelo, nato pa poiskati možnosti za omogočanje samodejne posodobitve vrtilnih tabel.
Ustvarite vrtilno tabelo in nato z desno miškino tipko kliknite katero koli celico vrtilne tabele, kot je prikazano spodaj.
Izberite Možnosti vrtilne tabele in to bo odprlo pogovorno okno Možnosti vrtilne tabele.
Izberite zavihek Podatki in nato potrdite polje, kjer piše Osveži podatke pri odpiranju datoteke. To lahko storite brez odpiranja in zapiranja datoteke z uporabo VBA.
Uporaba VBA
Tako se bomo tukaj naučili, kako samodejno osvežiti vrtilno tabelo z uporabo VBA. Ta način je lažji, kot ste si predstavljali.
To je preprosta skladnja za samodejno osveževanje vrtilnih tabel v delovnem zvezku.
| „Koda v predmetu izvornega podatkovnega lista
Zasebni pod -delovni list_Deactivate () ime_tabele_vrtilne_tabele.Vrtilne tabele ("ime vrtilne_tabele") .PivotCache.Posveži End Sub |
Kaj so vrtilni predpomnilniki?
Vsaka vrtilna tabela shranjuje podatke v vrtilnem predpomnilniku. Zato pivot lahko prikaže prejšnje podatke. Ko osvežimo vrtilne tabele, posodobi predpomnilnik z novimi izvornimi podatki, da odraža spremembe v vrtilni tabeli.
Zato potrebujemo samo makro za osvežitev predpomnilnika vrtilnih tabel. To bomo storili z dogodkom na delovnem listu, da nam ne bo treba ročno izvajati makra.
Kje kodirati za samodejno osveževanje vrtilnih tabel?
Če so vaši izvorni podatki in vrtilne tabele na različnih listih, mora biti koda VBA v izvornem podatkovnem listu.
Tukaj bomo uporabili dogodek Worksheet_SelectionChange. Tako se bo koda zagnala, kadar koli preidemo z izvornega lista na drug list. Kasneje bom razložil, zakaj sem uporabil ta dogodek.
Tukaj imam izvorne podatke v listu 2 in vrtilne tabele v listu 1.
Odprite VBE s tipko CTRL+F11. V raziskovalcu projektov lahko vidite tri predmete, list 1, list 2 in delovni zvezek.
Ker Sheet2 vsebuje izvorne podatke, dvokliknite objekt sheet2.
Zdaj lahko vidite dva spustna menija na vrhu področja kode. V prvem spustnem meniju izberite delovni list. V drugem spustnem meniju izberite Deaktiviraj. To bo vstavilo prazen podimenik Worksheet_Deactivate. Naša koda bo zapisana v tem podpodročju. Vse vrstice, napisane v tem podnaslovu, se izvedejo takoj, ko uporabnik preklopi s tega lista na kateri koli drug list.
Na listu 1 imam dve vrtilni tabeli. Osvežiti želim samo eno vrtilno tabelo. Za to moram vedeti ime vrtilne tabele. Če želite vedeti ime katere koli vrtilne tabele, izberite katero koli celico v tej vrtilni tabeli, pojdite na zavihek za analizo vrtilne tabele. Na levi strani boste videli ime vrtilne tabele. Tu lahko spremenite tudi ime vrtilne tabele.
Zdaj vemo ime vrtilne tabele, lahko napišemo preprosto vrstico za osvežitev vrtilne tabele.
| Zasebni pod -delovni list_Deactivate ()
List1.Vrtilne tabele ("Vrtilna tabela1"). Vrtilni predpomnilnik. Osveži End Sub |
In to je storjeno.
Zdaj, ko preklopite iz izvornih podatkov, se bo ta koda vba zagnala za osvežitev vrtilne tabele1. Kot lahko vidite na spodnjem gifu.
Kako osvežiti vse vrtilne tabele v delovnem zvezku?
V zgornjem primeru smo želeli osvežiti samo eno posebno vrtilno tabelo. Če pa želite osvežiti vse vrtilne tabele v delovnem zvezku, morate samo nekoliko spremeniti kodo.
| Zasebni pod -delovni list_Deactivate ()
'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Za vsak računalnik v tej delovni knjigi pc.Osveži Naslednji pc End Sub |
V tej kodi uporabljamo zanko For za prehod skozi vse vrtilne predpomnilnike v delovnem zvezku. Objekt ThisWorkbook vsebuje vse vrtilne predpomnilnike. Za dostop do njih uporabljamo ThisWorkbook.PivotCaches.
Zakaj uporabljati dogodek Worksheet_Deactivate?
Če želite osvežiti vrtilno tabelo, takoj ko pride do kakršne koli spremembe v izvornih podatkih, uporabite dogodek Worksheet_Change. Ampak tega ne priporočam. Tako bo vaš delovni zvezek zagnal kodo vsakič, ko na listu naredite kakršno koli spremembo. Morda boste morali narediti stotine sprememb, da boste videli rezultat. Toda Excel bo osvežil vrtilno tabelo pri vsaki spremembi. To bo povzročilo izgubo časa in sredstev za obdelavo. Torej, če imate vrtilne tabele in podatke na različnih listih, je bolje uporabiti dogodek za deaktiviranje delovnega lista. Omogoča vam, da dokončate svoje delo. Ko preklopite na liste vrtilne tabele in si ogledate spremembe, jih spremeni.
Če imate na istem listu vrtilne tabele in izvorne podatke in želite, da se vrtilne tabele samodejno osvežijo, boste morda želeli uporabiti dogodek Worksheet_Change.
| Zasebni pod -delovni list_Sprememba (cilj ByVal kot obseg)
List1.Vrtilne tabele ("Vrtilna tabela1"). Vrtilni predpomnilnik. Osveži End Sub |
Kako osvežiti vse v delovnih zvezkih, ko se spremenijo izvorni podatki?
Če želite osvežiti vse v delovnem zvezku (grafikone, vrtilne tabele, formule itd.), Lahko uporabite ukaz ThisWorkbook.RefreshAll.
| Zasebni pod -delovni list_Sprememba (cilj ByVal kot obseg)
ThisWorkbook.RefreshAll End Sub |
Opomba : Koda ne spremeni vira podatkov. Če torej pod izvorne podatke dodate podatke, ta koda teh podatkov ne bo vključila samodejno. Za shranjevanje izvornih podatkov lahko uporabite Excelove tabele. Če ne želite uporabljati tabel, lahko uporabimo VBA tudi za vključitev novih podatkov. Tega se bomo naučili v naslednji vadnici.
Upam, da je ta članek o tem, kako samodejno osvežiti podatke vrtilne tabele v Excelu, razložljiv. Več člankov o izračunu vrednosti in sorodnih formulah Excel najdete tukaj. Če so vam bili naši blogi všeč, jih delite s prijatelji na Facebooku. Prav tako nas lahko spremljate na Twitterju in Facebooku. Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo, dopolnimo ali inoviramo svoje delo in ga izboljšamo. Pišite nam na spletnem mestu e -pošte.
Kako dinamično posodobiti obseg vira podatkov vrtilne tabele v Excelu : Za dinamično spreminjanje obsega izvornih podatkov vrtilnih tabel uporabljamo vrtilne predpomnilnike. Teh nekaj vrstic lahko dinamično posodobi katero koli vrtilno tabelo s spreminjanjem območja izvornih podatkov. V VBA uporabite predmete vrtilnih tabel, kot je prikazano spodaj …
Zaženite makro, če je na listu v določenem obsegu prišlo do kakršne koli spremembe : V praksah VBA boste morali zagnati makre, ko se določeno območje ali celica spremeni. V tem primeru za izvajanje makrov pri spremembi ciljnega območja uporabimo dogodek spremembe.
Zaženi makro, če na listu pride do kakršne koli spremembe : Zato za zagon makra vsakič, ko se list posodobi, uporabljamo dogodke delovnega lista VBA.
Najpreprostejša koda VBA za označevanje trenutne vrstice in stolpca : S tem majhnim delčkom VBA označite trenutno vrstico in stolpec lista.
Dogodki na delovnem listu v Excelu VBA : Dogodek na delovnem listu je zelo uporaben, če želite, da se vaši makri izvajajo, ko se na listu pojavi določen dogodek.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost : Pospešite svoje naloge v Excelu. Te bližnjice vam bodo pomagale povečati učinkovitost dela v Excelu.
Kako uporabljati funkcijo VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo IF v Excelu : Stavek IF v Excelu preveri pogoj in vrne določeno vrednost, če je pogoj TRUE, ali vrne drugo posebno vrednost, če je FALSE.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
