U prethodnom smo članku naučili kako možete dinamički mijenjati i ažurirati pojedinačne zaokretne tablice sa smanjenjem ili proširenjem izvora podataka.
U ovom ćemo članku naučiti kako možemo postići da sve zaokretne tablice u radnoj knjizi automatski promijene izvor podataka. Drugim riječima, umjesto da mijenjamo jednu zaokretnu tablicu odjednom, pokušat ćemo promijeniti izvor podataka svih zaokretnih tablica u radnoj knjizi tako da dinamički uključuje nove retke i stupce dodane izvornim tablicama i trenutno odražava promjenu zaokretnih tablica.
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 zaokretne tablice).
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 svih zaokretnih tablica u radnoj knjizi s novim rasponom
Da bih objasnio kako to funkcionira, imam radnu bilježnicu. Ova radna knjiga sadrži tri lista. List 1 sadrži izvorne podatke koji se mogu promijeniti. List 2 i List 3 sadrže zaokretne tablice koje ovise 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 podradni list_Deactivate () Zatamni izvorne podatke kao raspon 'Određivanje broja zadnjeg retka i stupca lstrow = ćelije (brojevi redaka, 1). Kraj (xlUp). Red lstcol = ćelije (1, stupci.broj). Kraj (xlToLeft). Stupac 'Postavljanje novog raspona Postavi source_data = Raspon (ćelije (1, 1), ćelije (lstrow, lstcol))' Kôd za petlju kroz svaki list i zaokretnu tablicu Za svaki ws U ovoj radnoj knjizi. Radni listovi Za svaki pt U ws.PivotTabele pt. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Sljedeći pt Sljedeći ws Kraj Sub
Ako imate sličnu radnu knjigu, možete izravno kopirati ove podatke. U nastavku sam objasnio da ovaj kôd radi tako da ga možete izmijeniti prema svojim potrebama.
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.
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. Možete definirati vlastitu referentnu ćeliju.
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.
Budući da ne znamo koliko će zaokretnih tablica jedna radna knjiga sadržavati odjednom, proći ćemo kroz svaki list i zaokretne tablice svakog lista. Tako da ne postoji zaokretna tablica. Za to koristimo ugniježđene for petlje.
Za svaki ws u ThisWorkbook.Worksheets
Za svaku točku U ws.Zivotnim tablicama
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
Vrsta izvora: = xlDatabase, _
Izvorni podaci: = izvorni_data)
Sljedeća točka
Sljedeći ws
Prva petlja prolazi kroz svaki list. Druga petlja ponavlja svaku zaokretnu tablicu u listu.
Zaokretne tablice dodijeljene su varijabli pt. Koristimo ChangePivotCache metodu pt objekta. Dinamički stvaramo zaokretnu predmemoriju pomoću ThisWorkbook.PivotCaches.Create
Metoda. Ova metoda uzima dvije varijable SourceType i SourceData. Kao izvorni izvor deklariramo xlDatabase, a kao SourceData prosljeđujemo raspon source_data koji smo ranije izračunali.
I to je to. Zaokretne tablice imamo automatizirane. Ovo će automatski ažurirati sve zaokretne tablice u radnoj knjizi.
Pa da, dečki, evo kako možete dinamički promijeniti raspone izvora podataka svih zaokretnih tablica u radnoj knjizi u Excelu. Nadam se da sam vam dovoljno objasnio. Ako imate pitanja u vezi ovog članka, javite mi u odjeljku komentara ispod.
Kako dinamički ažurirati raspon izvora podataka zaokretne tablice u Excelu: Za dinamičku promjenu raspona izvornih podataka zaokretnih tablica koristimo zaokretne predmemorije. Ovih nekoliko redaka može dinamički ažurirati bilo koju zaokretnu tablicu promjenom raspona izvornih podataka.
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.