Eelmises artiklis õppisime, kuidas saate üksikuid pöördtabeleid dünaamiliselt muuta ja värskendada, vähendades või laiendades andmeallikaid.
Selles artiklis õpime, kuidas saame panna kõik töövihiku pöördtabelid automaatselt andmeallikat muutma. Teisisõnu, ühe pöördetabeli korraga muutmise asemel proovime muuta töövihiku kõigi liigendtabelite andmeallikat, et see dünaamiliselt kaasata uusi tabeleid lisatud ridu ja veerge ning kajastada muutusi koheselt.
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 pöördtabelid on erinevatel lehtedel, kirjutame VBA -koodi, et muuta pöördtabeli andmeallikat leheobjektis, mis sisaldab lähteandmeid (mitte seda, mis sisaldab pöördtabeleid).
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.
Lähtekood, et dünaamiliselt värskendada kõiki töövihiku liigendtabeleid uue vahemikuga
Selle toimimise selgitamiseks on mul töövihik. See töövihik sisaldab kolme lehte. Leht1 sisaldab lähteandmeid, mida saab muuta. Leht 2 ja leht 3 sisaldavad pöördtabeleid, mis sõltuvad 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 source_data As Range 'Viimase rea ja veeru numbri määramine lstrow = Lahtrid (Rows.Count, 1). End (xlUp) .Row lstcol = Lahtrid (1, Columns.Count) .End (xlToLeft) .Veerg 'Uue vahemiku seadmine Määra source_data = Range (Lahtrid (1, 1), Lahtrid (lstrow, lstcol))' Kood, mis tuleb iga lehe ja pöördtabeli kaudu läbi vaadata käesolevas töövihikus. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Järgmine pt Järgmine ws End Sub
Kui teil on sarnane töövihik, saate need andmed otse kopeerida. Olen selgitanud, et see kood töötab allpool, et saaksite seda vastavalt oma vajadustele muuta.
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.
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. Saate määrata oma alguslahtri viite.
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.
Kuna me ei tea, kui palju liigendtabeleid töövihik korraga sisaldab, vaatame läbi iga lehe ja iga lehe pöördtabelid. Nii et ühtegi pöördlauda ei jääks. Selleks kasutame silmuste jaoks pesastatud.
Iga töö jaoks käesolevas töövihikus. Töölehed
Iga pt kohta ws.PivotTable
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
Allika tüüp: = xlDatabase, _
Allikaandmed: = allikaandmed)
Järgmine pt
Järgmine ws
Esimene silmus lingib läbi iga lehe. Teine silmus kordub lehe iga liigendtabeli kohal.
Pöördtabelid on määratud muutujale pt. Kasutame pt objekti ChangePivotCache meetodit. Loome dünaamiliselt liigenduva vahemälu, kasutades funktsiooni ThisWorkbook.PivotCaches.Create
Meetod. See meetod kasutab kahte muutujat SourceType ja SourceData. Allikatüübina deklareerime xlDatabase ja SourceData edastame varem arvutatud vahemiku source_data.
Ja see on kõik. Meil on meie pöördlauad automatiseeritud. See värskendab automaatselt kõiki töövihiku liigendtabeleid.
Nii et jah, poisid, nii saate Exceli töövihiku kõigi pöördtabelite andmeallikavahemikke 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 -tabeli andmeallika vahemiku dünaamiline värskendamine Excelis: Pivot -tabelite lähteandmete vahemiku dünaamiliseks muutmiseks kasutame pivot -vahemälu. Need paar rida võivad dünaamiliselt värskendada mis tahes liigendtabelit, muutes lähteandmete vahemikku.
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.