Pašlaik mēs varam dinamiski mainīt vai atjaunināt rakurstabulas, izmantojot Excel tabulas vai dinamiskos nosauktos diapazonus. Bet šīs metodes nav drošas. Tā kā jums joprojām būs manuāli jāatsvaidzina rakurstabula. Ja jums ir lieli dati, kas satur tūkstošiem rindu un kolonnu, Excel tabulas jums daudz nepalīdzēs. Tā vietā jūsu fails būs smags. Tātad vienīgais ceļš ir VBA.
Šajā rakstā mēs uzzināsim, kā panākt, lai mūsu rakurstabula automātiski mainītu datu avotu. Citiem vārdiem sakot, mēs automatizēsim manuālo datu avota maiņas procesu, lai dinamiski iekļautu jaunas rindas un kolonnas, kas pievienotas avota tabulām, un uzreiz atspoguļotu izmaiņas rakurstabulā.
Ierakstiet kodu avota datu lapā
Tā kā mēs vēlamies, lai tas būtu pilnīgi automātiski, koda rakstīšanai mēs izmantosim lokšņu moduļus, nevis pamata moduli. Tas ļaus mums izmantot darblapas notikumus.
Ja avota dati un rakurstabulas atrodas dažādās lapās, mēs uzrakstīsim VBA kodu, lai mainītu rakurstabulas datu avotu lapas objektā, kurā ir avota dati (nevis tajā, kas satur rakurstabulu).
Nospiediet CTRL+F11, lai atvērtu VB redaktoru. Tagad dodieties uz projektu pētnieku un atrodiet lapu, kurā ir avota dati. Veiciet dubultklikšķi uz tā.
Tiks atvērts jauns kodēšanas apgabals. Jūs, iespējams, neredzēsit nekādas izmaiņas, bet tagad jums ir piekļuve darblapas notikumiem.
Noklikšķiniet uz kreisās nolaižamās izvēlnes un atlasiet darblapu. Kreisajā nolaižamajā izvēlnē atlasiet deaktivizēt. Jūs redzēsit tukšu apakšrakstu, kas uzrakstīts uz koda apgabala nosaukuma darblapas_deativēt. Šajā kodā tiks iekļauts mūsu kods, lai dinamiski mainītu avota datus un atsvaidzinātu rakurstabulu. Šis kods darbosies ikreiz, kad pārslēgsities no datu lapas uz jebkuru citu lapu. Par visiem darblapas notikumiem varat lasīt šeit.
Tagad mēs esam gatavi ieviest kodu.
Avota kods, lai dinamiski atjauninātu rakurstabulu ar jaunu diapazonu
Lai izskaidrotu, kā tas darbojas, man ir darba burtnīca. Šajā darbgrāmatā ir divas lapas. Lapa1 satur avota datus, kurus var mainīt. Lapa2 satur rakurstabulu, kas ir atkarīga no lapas 2 avota datiem.
Tagad esmu uzrakstījis šo kodu lapas1 kodēšanas apgabalā. Es izmantoju notikumu Worksheet_Deactivate, lai šis kods darbotos, lai atjauninātu rakurstabulu ikreiz, kad pārslēdzamies no avota datu lapas.
Privāta apakšdarblapa_Deaktivizēt () Dim pt kā PivotTable Dim pc Kā PivotCache Dim source_data As Range lstrow = Cells (Rows.Count, 1). End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft). Kolonnu kopa source_data = Diapazons (šūnas (1, 1), šūnas (lstrow, lstcol)) Set pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivota pc End Sub
Ja jums ir līdzīga darbgrāmata, varat tieši kopēt šos datus. Es esmu paskaidrojis, ka šis kods darbojas tālāk.
Jūs varat redzēt šī koda ietekmi zemāk esošajā gif.
Kā šis kods automātiski maina avota datus un atjaunina rakurstabulas?
Vispirms mēs izmantojām notikumu worksheet_deactivate. Šis notikums tiek aktivizēts tikai tad, ja lapa, kurā ir kods, tiek pārslēgta vai deaktivizēta. Tātad kods darbojas automātiski.
Tagad, lai mainītu rakurstabulas avota datus, mēs mainām datus šarnīra kešatmiņā.
Pivot tabula tiek izveidota, izmantojot šarnīra kešatmiņu. Šarnīra kešatmiņa satur vecos avota datus, līdz rakurstabula nav manuāli atsvaidzināta vai avota datu diapazons ir manuāli mainīts.
Mēs esam izveidojuši atsauces uz rakurstabulu nosaukumu pt, pivot kešatmiņu ar nosaukumu pc un diapazonu ar nosaukumu source_data. Avota dati ietvers visus datus.
Lai dinamiski iegūtu visu tabulu kā datu diapazonu, mēs nosakām pēdējo rindu un pēdējo kolonnu.
lstrow = šūnas (rindas. skaits, 1). beigas (xlUp). rinda
lstcol = šūnas (1, kolonnas. skaitlis). beigas (xlToLeft). kolonna
Izmantojot šos divus skaitļus, mēs definējam avota_datus. Mēs esam pārliecināti, ka avota datu diapazons vienmēr sāksies no A1.
Iestatīt avota_datus = diapazons (šūnas (1, 1), šūnas (lstrow, lstcol))
Tagad mums ir dinamiski avota dati. Mums tas vienkārši jāizmanto šarnīra tabulā.
Mēs glabājam šos datus šarnīra kešatmiņā, jo mēs zinām, ka pivot kešatmiņa saglabā visus datus.
Iestatiet pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Tālāk mēs definējam rakurstabulu, kuru vēlamies atjaunināt. Tā kā mēs vēlamies atjaunināt PivotTable1 (rakurstabulas nosaukums. Pivot tabulas nosaukumu varat pārbaudīt cilnē Analīze, atlasot rakurstabulu.) Uz 1. lapas, mēs iestatām pt, kā parādīts zemāk.
Iestatiet pt = Sheet2.PivotTables ("PivotTable1")
Tagad mēs vienkārši izmantojam šo šarnīra kešatmiņu, lai atjauninātu rakurstabulu. Mēs izmantojam pt objekta metodi changePivotCache.
pt. ChangePivotCache dators
Un mūsu pagrieziena galds ir automatizēts. Tas automātiski atjauninās jūsu rakurstabulu. Ja jums ir vairākas tabulas ar vienu un to pašu datu avotu, izmantojiet tikai vienu kešatmiņu katrā rakurstabulas objektā.
Jā, puiši, šādā veidā varat dinamiski mainīt datu avotu diapazonu programmā Excel. Ceru, ka biju pietiekami izskaidrojošs. Ja jums ir kādi jautājumi par šo rakstu, dariet man to zināmu zemāk esošajā komentāru sadaļā.
Kā automātiski atsvaidzināt rakurstabulas, izmantojot VBA: Lai automātiski atsvaidzinātu rakurstabulas, varat izmantot VBA notikumus. Izmantojiet šo vienkāršo koda rindu, lai automātiski atjauninātu rakurstabulu. Varat izmantot kādu no 3 metodēm, lai automātiski atsvaidzinātu rakurstabulas.
Palaidiet makro, ja lapā tiek veiktas izmaiņas noteiktā diapazonā: VBA praksē jums būtu jāpalaiž makro, kad mainās noteikts diapazons vai šūna. Tādā gadījumā, lai palaistu makro, kad tiek veiktas izmaiņas mērķa diapazonā, mēs izmantojam izmaiņu notikumu.
Palaist makro, kad lapā tiek veiktas izmaiņas | Tātad, lai palaistu jūsu makro ikreiz, kad lapa tiek atjaunināta, mēs izmantojam VBA darblapas notikumus.
Vienkāršākais VBA kods, lai izceltu pašreizējo rindu un kolonnu, izmantojot | Izmantojiet šo mazo VBA fragmentu, lai iezīmētu lapas pašreizējo rindu un kolonnu.
Darblapas notikumi programmā Excel VBA | Darblapas notikums ir patiešām noderīgs, ja vēlaties, lai makro darbotos, kad lapā notiek noteikts notikums.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.Funkcija VLOOKUP programmā Excel | Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
COUNTIF programmā Excel 2016 | Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai uzskaitītu konkrētu vērtību. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot SUMIF funkciju programmā Excel | Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.