Praegu saame Exceli tabeleid või dünaamilisi nimevahemikke kasutades pöördetabeleid dünaamiliselt muuta või värskendada. Kuid need tehnikad ei ole lollikindlad. Kuna peate ikkagi pöördetabeli käsitsi värskendama. Kui teil on suured andmed, mis sisaldavad tuhandeid ridu ja veerge, ei aita Exceli tabelid teid palju. Selle asemel muudab see teie faili raskeks. Nii et ainus võimalus on VBA.
Selles artiklis õpime, kuidas saame muuta oma pöördtabeli andmeallikat automaatselt muutma. Teisisõnu, automatiseerime andmeallika muutmise käsitsi, et dünaamiliselt kaasata uued tabelitele lisatud read ja veerud ning kajastada koheselt pöördetabeli muutust.
Kirjutage kood lähteandmete lehele
Kuna soovime, et see oleks täiesti automaatne, kasutame põhimooduli asemel koodi kirjutamiseks lehtmooduleid. See võimaldab meil kasutada töölehe sündmusi.
Kui lähteandmed ja liigendtabelid on erinevatel lehtedel, kirjutame VBA -koodi, et muuta pöördtabeli andmeallikat leheobjektis, mis sisaldab lähteandmeid (mitte seda, mis sisaldab pöördtabelit).
VB redaktori avamiseks vajutage CTRL+F11. Nüüd minge projektiuurija juurde ja leidke leht, mis sisaldab lähteandmeid. Topeltklõpsake seda.
Avaneb uus kodeerimisala. Te ei pruugi muudatusi näha, kuid nüüd on teil juurdepääs töölehe sündmustele.
Klõpsake vasakpoolset rippmenüüd ja valige tööleht. Valige vasakpoolsest rippmenüüst deaktiveeri. Näete tühja alamosa, mis on kirjutatud koodiala nime töölehele_deativate. Meie kood lähteandmete dünaamiliseks muutmiseks ja liigendtabeli värskendamiseks läheb sellesse koodiplokki. See kood käivitub alati, kui lülitate andmelehelt mõnele muule lehele. Kõigi töölehe sündmuste kohta saate lugeda siit.
Nüüd oleme valmis koodi rakendama.
Pivot -tabeli dünaamilise värskendamise lähtekood uue vahemikuga
Selle toimimise selgitamiseks on mul töövihik. See töövihik sisaldab kahte lehte. Leht1 sisaldab lähteandmeid, mida saab muuta. Leht2 sisaldab pöördtabelit, mis sõltub lehe 2 lähteandmetest.
Nüüd olen selle koodi lehe1 kodeerimisalasse kirjutanud. Kasutan sündmust Worksheet_Deactivate, nii et see kood jookseb liigendtabeli värskendamiseks alati, kui me lähtestame andmelehelt.
Privaatne alamleht_Deaktiveeri () Dim pt PivotTable -liigendtabelina Dim pc As PivotCache Dim source_data As Range lstrow = Lahtrid (Ridade arv, 1). Lõpp (xlUp). Veerg Määra source_data = Vahemik (lahtrid (1, 1), lahtrid (lstrow, lstcol)) Määra pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivotC pc End Sub
Kui teil on sarnane töövihik, saate need andmed otse kopeerida. Olen selgitanud, et see kood töötab allpool.
Selle koodi mõju näete allpool olevas gifis.
Kuidas see kood automaatselt lähteandmeid muudab ja liigendtabeleid värskendab?
Kõigepealt kasutasime sündmust worksheet_deactivate. See sündmus käivitub ainult siis, kui koodi sisaldav leht on sisse lülitatud või deaktiveeritud. Nii töötab kood automaatselt.
Pivot -tabeli lähteandmete muutmiseks muudame pivot -vahemälu andmeid.
Pivot -tabel luuakse pivot -vahemälu abil. Pöördvahemälu sisaldab vanu lähteandmeid seni, kuni pöördtabelit käsitsi ei värskendata või lähteandmete vahemikku käsitsi muudetakse.
Oleme loonud viited liigendtabelite nimele pt, pivot -vahemälu nimega pc ja vahemikule nimega source_data. Lähteandmed sisaldavad kõiki andmeid.
Kogu tabeli andmevahemikuks dünaamiliselt saamiseks määrame viimase rea ja viimase veeru.
lstrow = lahtrid (ridade arv, 1). lõpp (xlUp). rida
lstcol = Lahtrid (1, Veerud. Loend). Lõpp (xlToLeft). Veerg
Nende kahe numbri abil määratleme source_data. Oleme kindlad, et lähteandmete vahemik algab alati A1 -st.
Määra source_data = vahemik (lahtrid (1, 1), lahtrid (lstrow, lstcol))
Nüüd on meil dünaamilised lähteandmed. Peame seda lihtsalt pöördtabelis kasutama.
Salvestame need andmed pivot -vahemällu, kuna teame, et pivot -vahemälu salvestab kõik andmed.
Määra arvuti = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Järgmisena määratleme pöördtabeli, mida soovime värskendada. Kuna me soovime PivotTable1 (pöördetabeli nimi. Pivot tabeli nime saate vaadata vahekaardil analüüsida, samal ajal kui valite pöördetabeli.) 1. lehel, siis seadistame p1, nagu allpool näidatud.
Määra pt = Sheet2.PivotTables ("PivotTable1")
Nüüd kasutame lihtsalt seda pöördvahemälu pöördetabeli värskendamiseks. Kasutame pt objekti meetodit changePivotCache.
pt.ChangePivotCache arvuti
Ja meie pöördlaud on automatiseeritud. See värskendab automaatselt teie pöördtabelit. Kui teil on sama andmeallikaga mitu tabelit, kasutage lihtsalt iga vahetabeli objekti sama vahemälu.
Nii et jah, poisid, nii saate Exceli andmeallikate vahemikku dünaamiliselt muuta. Loodan, et olin piisavalt selgitav. Kui teil on selle artikli kohta küsimusi, andke mulle sellest teada allpool olevas kommentaaride jaotises.
Pivot -tabelite automaatne värskendamine VBA abil: Pivot tabelite automaatseks värskendamiseks saate kasutada VBA sündmusi. Kasutage seda lihtsat koodirida oma pöördtabeli automaatseks värskendamiseks. Pivot -tabelite automaatseks värskendamiseks saate kasutada ühte kolmest meetodist.
Käivitage makro, kui lehel tehakse kindlaksmääratud vahemikus muudatusi: Oma VBA praktikas oleks teil vajadus käivitada makrosid teatud vahemiku või lahtri muutumisel. Sel juhul kasutame makrosid käivitamiseks, kui sihtvahemikku muudetakse, muutmise sündmust.
Käivitage makro, kui lehel tehakse muudatusi | Nii et makro käivitamiseks iga kord, kui lehte värskendatakse, kasutame VBA töölehe sündmusi.
Lihtsaim VBA kood praeguse rea ja veeru esiletõstmiseks | Kasutage seda väikest VBA -katkendit lehe praeguse rea ja veeru esiletõstmiseks.
Töölehe sündmused Excel VBA -s | Töölehe sündmus on tõesti kasulik, kui soovite oma makrosid käivitada, kui lehel toimub teatud sündmus.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks | Saa oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.Funktsioon VLOOKUP Excelis | See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
COUNTIF Excelis 2016 | Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea konkreetse väärtuse loendamiseks oma andmeid filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis | See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.