Trenutno možemo dinamički mijenjati ili ažurirati zaokretne tablice pomoću Excel tablica ili dinamičkih imenovanih raspona. Ali ove tehnike nisu sigurne. Budući da ćete i dalje morati ručno osvježavati zaokretnu tablicu. Ako imate velike podatke koji sadrže tisuće redaka i stupaca, tablice programa Excel neće vam puno pomoći. Umjesto toga, to će učiniti vašu datoteku teškom. Dakle, jedini način ostaje VBA.
U ovom ćemo članku naučiti kako možemo postići da naša zaokretna tablica automatski promijeni izvor podataka. Drugim riječima, automatizirat ćemo ručni proces promjene izvora podataka tako da dinamički uključuje nove retke i stupce dodane izvornim tablicama i odmah odražava promjenu u zaokretnoj tablici.
Upišite kôd u tablicu s izvornim podacima
Budući da želimo da to bude potpuno automatski, koristit ćemo module za pisanje koda umjesto osnovnog modula. To će nam omogućiti korištenje događaja na radnom listu.
Ako su izvorni podaci i zaokretne tablice na različitim listovima, napisat ćemo VBA kôd za promjenu izvora podataka zaokretne tablice u objektu lista koji sadrži izvorne podatke (ne koji sadrži zaokretnu tablicu).
Pritisnite CTRL+F11 da biste otvorili VB editor. Sada idite na Project explorer i pronađite list koji sadrži izvorne podatke. Dvaput kliknite na nju.
Otvorit će se novo područje kodiranja. Možda nećete vidjeti nikakve promjene, ali sada imate pristup događajima na radnom listu.
Pritisnite lijevi padajući izbornik i odaberite radni list. Na lijevom padajućem izborniku odaberite deaktiviranje. Vidjet ćete prazan podpisnik napisan na radnom listu naziva_deativate naziva kodnog područja. Naš kôd za dinamičku promjenu izvornih podataka i osvježavanje zaokretne tablice ići će u ovaj blok koda. Ovaj kôd će se pokrenuti kad god pređete s podatkovnog lista na bilo koji drugi list. Ovdje možete pročitati o svim događajima na radnom listu.
Sada smo spremni za implementaciju koda.
Izvorni kôd za dinamičko ažuriranje zaokretne tablice s novim rasponom
Da bih objasnio kako to funkcionira, imam radnu bilježnicu. Ova radna knjiga sadrži dva lista. List 1 sadrži izvorne podatke koji se mogu promijeniti. List 2 sadrži zaokretnu tablicu koja ovisi o izvornim podacima lista 2.
Sada sam napisao ovaj kôd u području kodiranja lista 1. Koristim događaj Worksheet_Deactivate, tako da se ovaj kôd pokreće radi ažuriranja zaokretne tablice kad god pređemo s izvorne podatkovne tablice.
Privatni pod -radni list_Deactivate () Dim pt Kao zaokretna tablica Dim pc As PivotCache Dim source_data As Range lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft). Stupac Set source_data = Range (Cells (1, 1), Cells (lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivot pc End Sub
Ako imate sličnu radnu knjigu, možete izravno kopirati ove podatke. U nastavku sam objasnio da ovaj kôd radi.
Učinak ovog koda možete vidjeti na gif -u ispod.
Kako ovaj kôd automatski mijenja izvorne podatke i ažurira zaokretne tablice?
Prije svega, upotrijebili smo radni list_deactivate događaj. Ovaj se događaj aktivira samo kad se list s kodom prebaci ili deaktivira. Dakle, kod se automatski izvodi.
Sada za promjenu izvornih podataka zaokretne tablice mijenjamo podatke u zaokretnoj predmemoriji.
Zaokretna tablica izrađuje se pomoću zaokretne predmemorije. Zaokretna predmemorija sadrži stare izvorne podatke sve dok se zaokretna tablica ne ručno osvježi ili dok se raspon izvornih podataka ručno ne promijeni.
Napravili smo reference naziva zaokretnih tablica pt, zaokretne predmemorije s imenom pc i raspona s imenom source_data. Izvorni podaci sadržavat će cijele podatke.
Za dinamičko dobivanje cijele tablice kao raspona podataka određujemo zadnji redak i zadnji stupac.
lstrow = Ćelije (Redovi.Broj, 1). Kraj (xlUp) .Red
lstcol = ćelije (1, stupci.broj) .kraj (xlToLeft). stupac
Pomoću ova dva broja definiramo izvorne_podatke. Sigurni smo da će raspon izvornih podataka uvijek početi od A1.
Postavi izvor_data = raspon (ćelije (1, 1), ćelije (lstrow, lstcol))
Sada imamo izvorne podatke koji su dinamični. Moramo ga samo koristiti u zaokretnoj tablici.
Te podatke spremamo u zaokretnu predmemoriju jer znamo da zaokretna predmemorija pohranjuje sve podatke.
Postavi pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Zatim definiramo zaokretnu tablicu koju želimo ažurirati. Budući da želimo ažurirati zaokretnu tablicu1 (naziv zaokretne tablice. Možete provjeriti naziv zaokretne tablice na kartici za analizu dok odabirete zaokretnu tablicu.) Na listu 1, postavili smo pt kao što je prikazano u nastavku.
Postavi pt = Sheet2.Zavodne tablice ("Zaokretna tablica1")
Sada jednostavno koristimo ovu zaokretnu predmemoriju za ažuriranje zaokretne tablice. Koristimo changePivotCache metodu pt objekta.
pt.ChangePivotCache kom
I imamo zaokretnu tablicu automatiziranu. Ovo će automatski ažurirati vašu zaokretnu tablicu. Ako imate više tablica s istim izvorom podataka, samo upotrijebite istu predmemoriju u svakom objektu zaokretne tablice.
Pa da, dečki, evo kako možete dinamički promijeniti raspon izvora podataka u Excelu. Nadam se da sam vam dovoljno objasnio. Ako imate pitanja u vezi ovog članka, javite mi u odjeljku komentara ispod.
Kako automatski osvježiti zaokretne tablice pomoću VBA: Za automatsko osvježavanje zaokretnih tablica možete koristiti VBA događaje. Koristite ovaj jednostavan red koda za automatsko ažuriranje zaokretne tablice. Možete koristiti bilo koju od 3 metode automatskog osvježavanja zaokretnih tablica.
Pokrenite makronaredbu ako dođe do promjene na listu u navedenom rasponu: U vašim VBA postupcima dobit ćete potrebu za pokretanjem makronaredbi kad se promijeni određeni raspon ili ćelija. U tom slučaju, za pokretanje makronaredbi kada se izvrši promjena u ciljnom rasponu, koristimo događaj change.
Pokreni makronaredbu kad se na listu unese bilo kakva promjena | Dakle, za pokretanje vašeg makronaredbe kad god se list ažurira, koristimo VBA događaje na radnom listu.
Najjednostavniji VBA kôd za označavanje trenutnog retka i stupca pomoću | Pomoću ovog malog isječka VBA označite trenutačni redak i stupac lista.
Događaji na radnom listu u Excelu VBA | Događaj na radnom listu zaista je koristan kada želite da se vaše makronaredbe izvode kada se na listu pojavi određeni događaj.
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti | Budite brži u izvršavanju svojih zadataka. Ovih 50 prečaca učinit će da radite još brže na Excelu.Funkcija VLOOKUP u Excelu | Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
COUNTIF u Excelu 2016 | Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste računali određenu vrijednost. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu | Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.
