V tomto článku sa naučíme Ako automaticky obnoviť údaje kontingenčnej tabuľky v programe Excel.
Scenár:
Ako všetci vieme, kedykoľvek vykonáme zmeny v zdrojových údajoch kontingenčnej tabuľky, neodráža sa to okamžite v kontingenčnej tabuľke. Kontingenčné tabuľky musíme aktualizovať, aby sa zmeny prejavili pri každom otvorení zošita programu Excel. A ak odošlete aktualizovaný súbor bez obnovenia kontingenčných tabuliek, môžete pocítiť rozpaky. Naučte sa tu, ako nájsť možnosť obnovenia pri použití kontingenčnej tabuľky
Pri otváraní súboru v programe Excel obnovte údaje
Najprv vytvorte kontingenčnú tabuľku a potom kliknite pravým tlačidlom myši na ľubovoľnú bunku kontingenčnej tabuľky.
Prejdite na Možnosti kontingenčnej tabuľky> karta Údaje> Začiarknite políčko, ktoré hovorí Pri otváraní súboru obnovte údaje
To umožní automatické aktualizácie údajov pri každom otvorení súboru.
Príklad:
Toto všetko môže byť mätúce na pochopenie. Poďme pochopiť, ako používať funkciu na príklade. Tu máme nejaké údaje a musíme najskôr vytvoriť kontingenčnú tabuľku a potom nájsť možnosti na povolenie kontingenčných tabuliek s automatickou aktualizáciou.
Vytvorte kontingenčnú tabuľku a potom kliknite pravým tlačidlom myši na ľubovoľnú bunku kontingenčnej tabuľky, ako je to znázornené nižšie.
Vyberte Možnosti kontingenčnej tabuľky a tým sa otvorí dialógové okno Možnosti tabuľky PIvot.
Vyberte kartu Údaje a potom začiarknite políčko Obnoviť údaje pri otváraní súboru. Môžete to urobiť bez otvorenia a zatvorenia súboru pomocou VBA.
Použitie VBA
Tu sa teda naučíme, ako automaticky aktualizovať kontingenčnú tabuľku pomocou VBA. Tento spôsob je jednoduchší, ako ste si predstavovali.
Toto je jednoduchá syntax na automatické obnovenie kontingenčných tabuliek v zošite.
| 'Kód v objekte zdrojového listu
Súkromný čiastkový pracovný hárok_Deaktivovať () sheetname_of_pivot_table.Kontingenčné tabuľky ("pivot_table_name") .PivotCache.Refresh Koniec pod |
Čo sú to kontingenčné cache?
Každá kontingenčná tabuľka ukladá údaje do kontingenčnej pamäte cache. Preto môže pivot zobrazovať predchádzajúce údaje. Keď obnovíme kontingenčné tabuľky, aktualizuje vyrovnávaciu pamäť novými zdrojovými údajmi, aby odrážali zmeny v kontingenčnej tabuľke.
Na obnovu pamäte cache kontingenčných tabuliek teda potrebujeme iba makro. Urobíme to pomocou udalosti pracovného hárka, aby sme makro nemuseli spúšťať ručne.
Kde kódovať automatickú obnovu kontingenčných tabuliek?
Ak sú vaše zdrojové údaje a kontingenčné tabuľky na rôznych listoch, kód VBA by mal byť uvedený v zdrojovom hárku.
Tu použijeme udalosť Worksheet_SelectionChange. Vďaka tomu sa kód spustí vždy, keď prejdeme zo zdrojového listu na iný list. Neskôr vysvetlím, prečo som využil túto udalosť.
Tu mám zdrojové údaje v hárku2 a kontingenčné tabuľky v hárku1.
Otvorte VBE pomocou klávesov CTRL+F11. V Prieskumníkovi projektov môžete vidieť tri objekty, List1, List2 a zošit.
Pretože Sheet2 obsahuje zdrojové údaje, dvakrát kliknite na objekt sheet2.
Teraz môžete vidieť dve rozbaľovacie ponuky v hornej časti oblasti kódu. V prvom rozbaľovacom zozname vyberte pracovný hárok. A z druhej rozbaľovacej ponuky vyberte Deaktivovať. Tým sa vloží prázdny pod názov Worksheet_Deactivate. Náš kód bude zapísaný v tomto pododdiele. Všetky riadky napísané v tomto pododdiele sa spustia hneď, ako sa používateľ prepne z tohto hárku na iný hárok.
Na hárku 1 mám dve kontingenčné tabuľky. Chcem aktualizovať iba jednu kontingenčnú tabuľku. Na to potrebujem vedieť názov kontingenčnej tabuľky. Ak chcete poznať názov akejkoľvek kontingenčnej tabuľky, vyberte ľubovoľnú bunku v tejto kontingenčnej tabuľke a prejdite na kartu analýzy kontingenčnej tabuľky. Na ľavej strane uvidíte názov kontingenčnej tabuľky. Tu môžete tiež zmeniť názov kontingenčnej tabuľky.
Teraz poznáme názov kontingenčnej tabuľky, môžeme napísať jednoduchý riadok na aktualizáciu kontingenčnej tabuľky.
| Súkromný čiastkový pracovný hárok_Deaktivovať ()
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Koniec pod |
A je hotovo.
Teraz, kedykoľvek prepnete zo zdrojových údajov, sa spustí tento kód vba na obnovenie kontingenčnej tabuľky1. Ako vidíte na obrázku nižšie.
Ako obnoviť všetky kontingenčné tabuľky v zošite?
V uvedenom príklade sme chceli obnoviť iba jednu konkrétnu kontingenčnú tabuľku. Ak však chcete v zošite obnoviť všetky kontingenčné tabuľky, stačí, ak v kóde urobíte malé zmeny.
| Súkromný čiastkový pracovný hárok_Deaktivovať ()
'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Pre každý počítač v ThisWorkbook.PivotCaches pc. Obnoviť Dalsi pc Koniec pod |
V tomto kóde používame slučku For na to, aby sme mohli prechádzať každou medzipamäťou v zošite. Objekt ThisWorkbook obsahuje všetky kontingenčné pamäte cache. Na prístup k nim používame ThisWorkbook.PivotCaches.
Prečo používať udalosť Worksheet_Deactivate?
Ak chcete kontingenčnú tabuľku obnoviť hneď po vykonaní akejkoľvek zmeny zdrojových údajov, mali by ste použiť udalosť Worksheet_Change. Ale neodporúčam to. Z vášho zošita sa spustí kód pri každej zmene hárka. Skôr ako budete chcieť vidieť výsledok, možno budete musieť vykonať stovky zmien. Excel však obnoví kontingenčnú tabuľku pri každej zmene. To povedie k plytvaniu časom a zdrojmi spracovania. Ak teda máte kontingenčné tabuľky a údaje v rôznych listoch, je lepšie použiť udalosť deaktivácie pracovného hárka. Umožňuje vám dokončiť prácu. Keď prepnete na hárky kontingenčnej tabuľky, aby ste videli zmeny, zmeny zmení.
Ak máte na jednom hárku kontingenčné tabuľky a zdrojové údaje a chcete, aby sa kontingenčné tabuľky automaticky obnovovali, môžete použiť udalosť Worksheet_Change.
| Súkromný čiastkový pracovný hárok_Zmeniť (ByVal cieľ ako rozsah)
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Koniec pod |
Ako obnoviť všetko v zošitoch, keď dôjde k zmene zdrojových údajov?
Ak chcete obnoviť všetko v zošite (grafy, kontingenčné tabuľky, vzorce, atď.), Môžete použiť príkaz ThisWorkbook.RefreshAll.
| Súkromný čiastkový pracovný hárok_Zmeniť (ByVal cieľ ako rozsah)
ThisWorkbook.RefreshAll Koniec pod |
Poznámka : Kód nemení zdroj údajov. Ak teda pridáte údaje pod zdrojové údaje, tento kód tieto údaje automaticky nezahrnie. Na uloženie zdrojových údajov môžete použiť tabuľky programu Excel. Ak nechcete používať tabuľky, môžeme použiť VBA aj na zahrnutie nových údajov. Naučíme sa to v nasledujúcom návode.
Dúfam, že tento článok o automatickom obnovení údajov kontingenčnej tabuľky v programe Excel je vysvetľujúci. Tu nájdete ďalšie články o výpočte hodnôt a súvisiacich vzorcoch programu Excel. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.
Ako dynamicky aktualizovať rozsah zdrojov údajov v kontingenčnej tabuľke v programe Excel : Na dynamickú zmenu rozsahu zdrojových údajov kontingenčných tabuliek používame kontingenčné pamäte cache. Týchto niekoľko riadkov môže dynamicky aktualizovať akúkoľvek kontingenčnú tabuľku zmenou rozsahu zdrojových údajov. Vo VBA používajte objekty kontingenčných tabuliek, ako je uvedené nižšie …
V prípade zmeny vykonanej na hárku v určenom rozsahu spustite makro : Vo vašich postupoch VBA by ste potrebovali spustiť makrá, keď sa zmení určitý rozsah alebo bunka. V takom prípade na spustenie makier pri zmene cieľového rozsahu použijeme udalosť zmeny.
Spustite makro, keď sa na hárku vykonajú akékoľvek zmeny : Na spustenie makra vždy, keď sa aktualizuje hárok, používame udalosti pracovného hárka VBA.
Najjednoduchší kód VBA na zvýraznenie aktuálneho použitia riadkov a stĺpcov : Použite tento malý útržok VBA na zvýraznenie aktuálneho riadka a stĺpca listu.
Udalosti pracovného hárka v programe Excel VBA : Udalosť pracovného hárka je skutočne užitočná, keď chcete, aby sa vaše makrá spustili, keď sa v hárku vyskytne zadaná udalosť.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity : Zrýchlite sa so svojimi úlohami v Exceli. Tieto skratky vám pomôžu zvýšiť efektivitu práce v programe Excel.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.