Ankstesniame straipsnyje mes sužinojome, kaip galite dinamiškai keisti ir atnaujinti atskiras suvestines lenteles, mažindami arba plečiant duomenų šaltinius.
Šiame straipsnyje sužinosime, kaip galime padaryti, kad visos darbaknygės suvestinės lentelės automatiškai pakeistų duomenų šaltinį. Kitaip tariant, užuot keitę vieną suvestinę lentelę, mes stengsimės pakeisti visų darbaknygės suvestinių lentelių duomenų šaltinį, kad dinamiškai įtrauktų naujas eilutes ir stulpelius, pridėtus prie šaltinio lentelių, ir akimirksniu atspindėtų suvestinių lentelių pasikeitimą.
Įrašykite kodą į šaltinio duomenų lapą
Kadangi norime, kad tai būtų visiškai automatiška, kodui rašyti naudosime lakštinius modulius, o ne pagrindinį modulį. Tai leis mums naudoti darbalapio įvykius.
Jei šaltinio duomenys ir suvestinės lentelės yra skirtinguose lapuose, parašysime VBA kodą, kad pakeistume suvestinės lentelės duomenų šaltinį lapo objekte, kuriame yra šaltinio duomenys (ne tas, kuriame yra suvestinės lentelės).
Paspauskite CTRL+F11, kad atidarytumėte VB redaktorių. Dabar eikite į projekto tyrinėtoją ir raskite lapą, kuriame yra šaltinio duomenys. Dukart spustelėkite jį.
Bus atidaryta nauja kodavimo sritis. Galbūt nematysite jokių pakeitimų, bet dabar turite prieigą prie darbalapio įvykių.
Spustelėkite kairįjį išskleidžiamąjį meniu ir pasirinkite darbalapį. Kairiajame išskleidžiamajame meniu pasirinkite išjungti. Pamatysite tuščią antrinį elementą, parašytą kodo srities pavadinimo darbalapyje_aktyvinti. Mūsų kodas, skirtas dinamiškai keisti šaltinio duomenis ir atnaujinti suvestinę lentelę, bus pateiktas šiame kodo bloke. Šis kodas bus paleistas, kai perjungsite iš duomenų lapo į bet kurį kitą lapą. Apie visus darbalapio įvykius galite perskaityti čia.
Dabar esame pasirengę įdiegti kodą.
Šaltinio kodas, skirtas dinamiškai atnaujinti visas darbaknygės „Pivot“ lenteles su nauju diapazonu
Norėdami paaiškinti, kaip tai veikia, turiu darbo knygą. Šioje darbaknygėje yra trys lapai. 1 lape yra šaltinio duomenys, kurie gali keistis. „Sheet2“ ir „Sheet3“ yra suvestinės lentelės, kurios priklauso nuo 2 lapo šaltinio duomenų.
Dabar aš parašiau šį kodą „sheet1“ kodavimo srityje. Aš naudoju įvykį „Worksheet_Deactivate“, kad šis kodas būtų paleistas norint atnaujinti suvestinę lentelę, kai tik pereiname iš šaltinio duomenų lapo.
Privatus antrinis darbalapis_Deaktyvinti () Dim source_data As Range 'Paskutinės eilutės ir stulpelio numerio nustatymas lstrow = Ląstelės (Rows.Count, 1). End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column „Naujo diapazono nustatymas Nustatykite source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))“ Kodas, per kurį bus perkeliamas kiekvienas lapas ir „pivot“ lentelė. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Kitas pt Kitas ws Pabaiga
Jei turite panašią darbaknygę, galite tiesiogiai nukopijuoti šiuos duomenis. Paaiškinau, kad šis kodas veikia toliau, kad galėtumėte jį keisti pagal savo poreikius.
Šio kodo poveikį galite pamatyti žemiau esančiame gif.
Kaip šis kodas automatiškai keičia šaltinio duomenis ir atnaujina suvestines lenteles?
Pirmiausia naudojome įvykį worksheet_deactivate. Šis įvykis suaktyvinamas tik tada, kai lapas su kodu yra perjungiamas arba išjungiamas. Taigi kodas automatiškai paleidžiamas.
Norėdami dinamiškai gauti visą lentelę kaip duomenų diapazoną, nustatome paskutinę eilutę ir paskutinį stulpelį.
lstrow = ląstelės (eilutės. skaičius, 1). pabaiga (xlUp). eilutė
lstcol = Ląstelės (1, Stulpeliai. Skaičius). Pabaiga (xlToLeft). Stulpelis
Naudodamiesi šiais dviem skaičiais, mes apibrėžiame „source_data“. Esame įsitikinę, kad šaltinių duomenų diapazonas visada prasidės nuo A1. Galite nustatyti savo pradžios langelio nuorodą.
Nustatyti šaltinio duomenis = diapazonas (langeliai (1, 1), langeliai (lstrow, lstcol))
Dabar mes turime dinaminius šaltinio duomenis. Mums tiesiog reikia jį naudoti suvestinėje lentelėje.
Kadangi nežinome, kiek suvestinių lentelių vienu metu bus darbaknygėje, peržiūrėsime kiekvieną lapą ir kiekvieno lapo suvestines lenteles. Taigi, kad neliktų suvestinės lentelės. Tam mes naudojame įdėtus kilpoms.
Už kiekvieną ws šioje darbo knygoje. Darbo lapai
Už kiekvieną pt In ws.PivotTable
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
Šaltinio tipas: = xlDatabase, _
Šaltinio duomenys: = šaltinio duomenys)
Kitas pt
Kitas ws
Pirmoji kilpa kilpos per kiekvieną lapą. Antroji kilpa kartojasi per kiekvieną lapo sukamąją lentelę.
Suvestinės lentelės priskirtos kintamajam pt. Mes naudojame pt objekto „ChangePivotCache“ metodą. Dinamiškai sukuriame suvestinę talpyklą naudodami „ThisWorkbook.PivotCaches.Create“
Metodas. Šis metodas apima du kintamuosius „SourceType“ ir „SourceData“. Kaip šaltinio tipą deklaruojame „xlDatabase“, o kaip „SourceData“ perduodame anksčiau apskaičiuotą „source_data“ diapazoną.
Ir viskas. Mes pasukame lenteles automatizuotai. Tai automatiškai atnaujins visas darbaknygės suvestines lenteles.
Taigi, vaikinai, taip galite dinamiškai keisti visų „Excel“ darbaknygės suvestinių lentelių duomenų šaltinių diapazonus. Tikiuosi, kad buvau pakankamai aiškinantis. Jei turite klausimų apie šį straipsnį, praneškite man toliau pateiktame komentarų skyriuje.
Kaip dinamiškai atnaujinti „Pivot Table“ duomenų šaltinio diapazoną „Excel“: Norėdami dinamiškai keisti suvestinių lentelių šaltinių duomenų diapazoną, naudojame suvestines talpyklas. Šios kelios eilutės gali dinamiškai atnaujinti bet kurią suvestinę lentelę, pakeisdamos šaltinio duomenų diapazoną.
Kaip automatiškai atnaujinti „Pivot“ lenteles naudojant VBA: Norėdami automatiškai atnaujinti suvestines lenteles, galite naudoti VBA įvykius. Naudokite šią paprastą kodo eilutę, kad automatiškai atnaujintumėte suvestinę lentelę. Galite naudoti bet kurį iš 3 automatinio atnaujinimo suvestinių lentelių metodų.
Vykdykite makrokomandą, jei lape bus atlikti pakeitimai nurodytame diapazone: VBA praktikoje turėtumėte paleisti makrokomandas, kai pasikeičia tam tikras diapazonas ar langelis. Tokiu atveju, norėdami paleisti makrokomandas, kai keičiamas tikslinis diapazonas, naudojame pakeitimo įvykį.
Vykdyti makrokomandą, kai lape atliekami bet kokie pakeitimai | Taigi, norėdami paleisti makrokomandą, kai lapas atnaujinamas, naudojame VBA darbalapio įvykius.
Paprasčiausias VBA kodas, skirtas paryškinti dabartinę eilutę ir stulpelį | Naudokite šį mažą VBA fragmentą, kad paryškintumėte dabartinę lapo eilutę ir stulpelį.
Užduoties įvykiai „Excel VBA“ | Skaičiuoklės įvykis yra tikrai naudingas, kai norite, kad jūsų makrokomandos būtų paleistos, kai lape įvyksta nurodytas įvykis.
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti | Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.„VLOOKUP“ funkcija „Excel“ | Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti vertės iš įvairių diapazonų ir lapų.
COUNTIF „Excel 2016“ | Naudodami šią nuostabią funkciją, suskaičiuokite reikšmes su sąlygomis. Norint skaičiuoti konkrečią vertę, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ | Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.