Šiuo metu galime dinamiškai keisti arba atnaujinti suvestines lenteles naudodami „Excel“ lenteles arba dinaminius pavadintus diapazonus. Tačiau šie metodai nėra patikimi. Kadangi vis tiek turėsite atnaujinti suvestinę lentelę rankiniu būdu. Jei turite daug duomenų, kuriuose yra tūkstančiai eilučių ir stulpelių, „Excel“ lentelės jums labai nepadės. Vietoj to jūsų failas bus sunkus. Taigi lieka vienintelis kelias - VBA.
Šiame straipsnyje mes sužinosime, kaip galime padaryti, kad mūsų suvestinė automatiškai pakeistų duomenų šaltinį. Kitaip tariant, automatizuosime rankinį duomenų šaltinio keitimo procesą, kad dinamiškai įtrauktume naujas eilutes ir stulpelius, pridėtus prie šaltinių lentelių, ir akimirksniu atspindėtume suvestinės lentelės 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ė lentelė).
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 „Pivot“ lentelę naudojant naują diapazoną
Norėdami paaiškinti, kaip tai veikia, turiu darbo knygą. Šioje darbaknygėje yra du lapai. 1 lape yra šaltinio duomenys, kurie gali keistis. 2 lape yra suvestinė lentelė, kuri 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 pt kaip „PivotTable“ Dim pc Kaip „PivotCache“ Dim source_data As Range lstrow = Cells (Rows.Count, 1). End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft). Stulpelių rinkinys source_data = Diapazonas (langeliai (1, 1), langeliai (lstrow, lstcol)) Nustatykite pc = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data) Set pt = Sheet2.PivotTables ("PivotTable1") pt.ChangePivot „PC End Sub“
Jei turite panašią darbaknygę, galite tiesiogiai nukopijuoti šiuos duomenis. Paaiškino, kad šis kodas veikia toliau.
Š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.
Dabar norėdami pakeisti suvestinės lentelės šaltinio duomenis, keičiame duomenis suvestinėje talpykloje.
Sukamoji lentelė sukuriama naudojant sukamąją talpyklą. „Pivot“ talpykloje yra seni šaltinio duomenys, kol suvestinė lentelė neatnaujinama rankiniu būdu arba šaltinio duomenų diapazonas nekeičiamas rankiniu būdu.
Mes sukūrėme nuorodas į suvestines lenteles, pavadintas pt, suvestinę talpyklą, pavadintą pc, ir diapazoną, pavadintą source_data. Šaltinio duomenys apims visus duomenis.
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.
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.
Mes saugome šiuos duomenis „pivot cache“, nes žinome, kad „pivot cache“ saugo visus duomenis.
Nustatykite kompiuterį = ThisWorkbook.PivotCaches.Create (xlDatabase, SourceData: = source_data)
Toliau apibrėžiame suvestinę lentelę, kurią norime atnaujinti. Kadangi norime atnaujinti „PivotTable1“ (suvestinės lentelės pavadinimas. Suvestinės lentelės pavadinimą galite patikrinti analizės skirtuke, pasirinkdami suvestinę lentelę.) 1 lape, mes nustatome pt, kaip parodyta žemiau.
Nustatykite pt = Sheet2.PivotTables ("PivotTable1")
Dabar mes tiesiog naudojame šią suvestinę talpyklą, kad atnaujintume suvestinę lentelę. Mes naudojame pt objekto metodą changePivotCache.
pt. ChangePivotCache pc
O pasukamąją lentelę mes turime automatizuoti. Tai automatiškai atnaujins jūsų suvestinę lentelę. Jei turite kelias lenteles su tuo pačiu duomenų šaltiniu, naudokite tą pačią talpyklą kiekviename suvestinės lentelės objekte.
Taigi taip, vaikinai, taip galite dinamiškai keisti duomenų šaltinių diapazoną „Excel“. Tikiuosi, kad buvau pakankamai aiškinantis. Jei turite klausimų apie šį straipsnį, praneškite man toliau pateiktame komentarų skyriuje.
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.