Iepriekšējā rakstā mēs uzzinājām, kā varat dinamiski mainīt un atjaunināt atsevišķas rakurstabulas, samazinot vai paplašinot datu avotus.
Šajā rakstā mēs uzzināsim, kā panākt, lai visas darbgrāmatas rakurstabulas automātiski mainītu datu avotu. Citiem vārdiem sakot, tā vietā, lai mainītu vienu rakurstabulu vienlaikus, mēs centīsimies mainīt visu darbgrāmatas rakurstabulu datu avotu, lai dinamiski iekļautu jaunas avota tabulām pievienotās rindas un kolonnas un uzreiz atspoguļotu izmaiņas rakurstabulās.
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 rakurstabulas).
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 visas darbgrāmatas rakurstabulas ar jaunu diapazonu
Lai izskaidrotu, kā tas darbojas, man ir darba burtnīca. Šajā darbgrāmatā ir trīs lapas. Lapa1 satur avota datus, kurus var mainīt. Lapa2 un lapa3 satur rakurstabulas, kas ir atkarīgas no lapas2 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 avota_dati kā diapazons 'Pēdējās rindas un kolonnas numura noteikšana 'Jaunā diapazona iestatīšana Iestatīt source_data = Diapazons (šūnas (1, 1), šūnas (lstrow, lstcol))' Kods, kas jāapmeklē caur katru lapu un šarnīra tabulu. Katram ws šajā darba grāmatā. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Next pt Next ws End Sub
Ja jums ir līdzīga darbgrāmata, varat tieši kopēt šos datus. Es paskaidroju, ka šis kods darbojas tālāk, lai jūs varētu to mainīt atbilstoši savām vajadzībām.
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.
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. Jūs varat definēt savu sākuma šūnas atsauci.
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ā.
Tā kā mēs nezinām, cik daudz rakurstabulu darbgrāmatā vienlaikus būs, mēs aplūkosim katru lapu un katras lapas rakurstabulas. Lai netiktu atstāta neviena šarnīra tabula. Šim nolūkam mēs izmantojam ligzdotas cilpas.
Par katru ws šajā darba grāmatā. Darba lapas
Par katru pt In ws.PivotTable
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
Avota tips: = xlDatabase, _
Avota dati: = avota_dati)
Nākamais pt
Nākamais ws
Pirmā cilpa cilpas caur katru lapu. Otrā cilpa atkārtojas pa katru lapas šarnīra tabulu.
Šarnīra tabulas ir piešķirtas mainīgajam pt. Mēs izmantojam pt objekta ChangePivotCache metodi. Mēs dinamiski izveidojam šarnīra kešatmiņu, izmantojot šo darbu. PivotCaches
Metode. Šī metode izmanto divus mainīgos SourceType un SourceData. Kā avota veidu mēs deklarējam xlDatabase un kā SourceData mēs nododam avota_datu diapazonu, ko esam aprēķinājuši iepriekš.
Un tas arī viss. Mūsu pagrieziena tabulas ir automatizētas. Tas automātiski atjauninās visas darbgrāmatas rakurstabulas.
Tātad, jā, puiši, šādā veidā varat dinamiski mainīt visu darbgrāmatas rakurstabulu datu avotu diapazonus 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ā dinamiski atjaunināt rakurstabulas datu avota diapazonu programmā Excel: Lai dinamiski mainītu rakurstabulu avota datu diapazonu, mēs izmantojam šarnīra kešatmiņas. Šīs dažas rindiņas var dinamiski atjaunināt jebkuru rakurstabulu, mainot avota datu diapazonu.
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.