„Excel VBA Pivot“ lentelė
„Pivot“ lentelės yra pagrindas apibendrinant didelio duomenų kiekio ataskaitą. Mes taip pat galime automatizuoti sukimo lentelės kūrimo procesą naudodami VBA kodavimą. Jie yra svarbi bet kurios ataskaitos ar informacijos suvestinės dalis. „Excel“ programoje lengva sukurti lenteles mygtuku, tačiau VBA turime parašyti keletą kodų, kad automatizuotume savo sukamą lentelę. Prieš „Excel 2007“ ir senesnes VBA versijas mums nereikėjo sukurti talpyklą šarnyrinėms lentelėms, tačiau „Excel 2010“ ir naujesnėse versijose reikalingos talpyklos.
VBA gali sutaupyti daug laiko mūsų darbo vietoje. Nors tai įvaldyti nėra taip lengva, tačiau verta skirti laiko tam išmokti. Užtrukau 6 mėnesius, kad suprasčiau suvestinių lentelių kūrimo procesą per VBA. Jūs žinote, ką tie 6 mėnesiai padarė man stebuklų, nes aš padariau labai daug klaidų bandydamas sukurti sukamą lentelę.
Tačiau iš tikrųjų aš išmokau iš savo klaidų ir dabar rašau šį straipsnį norėdamas parodyti, kaip sukurti sukamas lenteles naudojant kodą.
Vos spustelėję mygtuką, galime sukurti ataskaitas.
Veiksmai, kaip sukurti „Pivot“ lentelę VBA
Norint sukurti sukamą lentelę, svarbu turėti duomenų. Tam aš sukūriau keletą manekeno duomenų. Galite atsisiųsti darbaknygę, kad galėtumėte sekti su manimi su tais pačiais duomenimis.
1 veiksmas: „ Pivot Table“ yra objektas, nurodantis „pivot“ lentelę, deklaruojančią kintamąjį kaip „PivotTables“.
Kodas:
Sub PivotTable () Dim PTable Kaip PivotTable End Sub
2 žingsnis: Prieš sukurdami suvestinę lentelę, turime sukurti suvestinę talpyklą, kad apibrėžtume duomenų šaltinį.
Įprastose darbalapiuose sukimosi lentelė, nesukeldama mums rūpesčių, sukurs pasukamą talpyklą fone. Tačiau VBA turime kurti.
Tam apibrėžkite kintamąjį „PivotCache“.
Kodas:
Blankus „PCache“ kaip „PivotCache“
3 žingsnis: Norėdami nustatyti suvestinių duomenų diapazoną, apibrėžkite kintamąjį kaip diapazoną.
Kodas:
„Dim PRange As Range“
4 žingsnis: norėdami įterpti suvestinę lentelę, mums reikia atskiro lapo, kad pridėtume darbalapius, skirtus šarnyrinei lentelei, kad kintamasis būtų deklaruojamas kaip darbalapis.
Kodas:
„Dim PSheet As Worksheet“
5 žingsnis: Panašiai, norėdami nurodyti duomenis, kuriuose yra darbalapio, dar vieną kintamąjį paskelbkite darbalapiu.
Kodas:
„Dim DSheet As Worksheet“
6 žingsnis: Galiausiai, norėdami rasti paskutinį kartą naudotą eilutę ir stulpelį, apibrėžkite dar du kintamuosius kaip „Long“.
Kodas:
Dim LR As Long Dim LC Long
7 žingsnis: Dabar, norėdami sukurti sukamą lentelę, turime įterpti naują lapą. Prieš tai, jei yra koks nors sukamasis lapas, turime tai ištrinti.
8 žingsnis: Dabar nustatykite objekto kintamąjį PSheet ir DSheet atitinkamai į „Pivot Sheet“ ir „Data Sheet“.
9 žingsnis: Raskite paskutinę naudotą eilutę ir paskutinį kartą naudojamą stulpelį duomenų lape.
10 žingsnis: Dabar nustatykite pasukimo diapazoną naudodami paskutinę eilutę ir paskutinį stulpelį.
Tai puikiai nustatys duomenų diapazoną. Jis automatiškai pasirinks duomenų diapazoną, net jei duomenų lape yra duomenų arba jie ištrinti.
11 žingsnis: Prieš sukurdami suvestinę lentelę, turime sukurti suvestinę talpyklą. Nustatykite „Pivot“ talpyklos kintamąjį naudodami žemiau esantį VBA kodą.
12 žingsnis: Dabar sukurkite tuščią suvestinę lentelę.
13 žingsnis: Įterpę sukamą lentelę, pirmiausia turime įterpti eilutės lauką. Taigi eilutės lauką įterpsiu kaip stulpelį „Šalis“.
Pastaba: atsisiųskite darbaknygę, kad suprastumėte duomenų stulpelius.
14 žingsnis: Dabar dar vieną elementą įterpsiu į eilutės lauką kaip antrą pozicijos elementą. Produktą įterpsiu kaip antrą eilutės elementą į eilutės lauką.
15 žingsnis: Įterpę stulpelius į eilutės lauką, turime įterpti reikšmes į stulpelio lauką. Į stulpelio lauką įterpsiu „Segmentas“.
16 žingsnis: Dabar turime įterpti skaičius į duomenų lauką. Taigi į duomenų lauką įrašykite „Pardavimai“.
17 veiksmas: mes baigėme suvestinės lentelės suvestinės dalį. Dabar turime formatuoti lentelę. Norėdami suformatuoti suvestinę lentelę, naudokite toliau pateiktą kodą.
Norėdami eilutėje pateiktų verčių elementus rodyti lentelių pavidalu, apačioje pridėkite žemiau esantį kodą.
Gerai, mes baigėme, jei paleisime šį kodą naudodami F5 klavišą arba rankiniu būdu, turėtume gauti tokią suvestinę lentelę.
Panašiai, naudodami VBA kodavimą, galime automatizuoti sukimo lentelės kūrimo procesą.
Jūsų pavyzdžiu pateikiau žemiau esantį kodą.
Sub PivotTable () Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Atnaujinkite kitą taikymą.DisplayAlerts = False Application.ScreenUpdating = False Worksheet ("Pivot Lapas "). Ištrinti 'Tai ištrins esamus suvestinės lentelės darbalapio darbalapius. Pridėti po: =„ ActiveSheet “' Tai pridės naują darbalapį„ ActiveSheet.Name = "Pivot Sheet" 'Tai pakeis darbalapio pavadinimą į „Pivot Sheet“ On On GoTo 0 Nustatyti PSheet = darbalapiai („Pivot Sheet“) Nustatyti DSheet = darbalapiai („Data Sheet“) 'Rasti paskutinį kartą naudotą eilutę ir stulpelį duomenų lape LR = DSheet.Cells (Rows.Count, 1) .End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Set pivot table data range Set PRange = DSheet.Langeliai (1, 1). Resize (LR, LC) "Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)" Sukurti tuščią suvestinę lentelę Nustatykite PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Langeliai (1, 1), TableName: = "Pardavimo_atskaita") 'Įterpti šalį į eilutę, pateiktą su PSheet.PivotTables ("Pardavimų_Reportas"). „PivotFields“ („Šalis“). Orientacija = xlRowField .Pozicija = 1 Pabaiga su „Įterpti produktą į Pateikta eilutė ir 2 padėtis naudojant „PSheet.PivotTables“ („Pardavimo ataskaita“). „PivotFields“ („Produktas“). Orientacija = xlRowField. Pozicija = 2 Pabaiga su „Įterpti segmentą į pateiktą stulpelį“ ir 1 pozicija su „PSheet.PivotTables“ („Pardavimo ataskaita“). „PivotFields“ („Segmentas“). Orientacija = xlColumnField .Positija = 1 Pabaiga su „Įterpti pardavimus“ į duomenų lauką „Naudojant PSheet“.„PivotTables“ („Sales_Report“). „PivotFields“ („Sales“). Orientation = xlDataField .Positija = 1 Pabaiga su „Format Pivot Table PSheet.PivotTables („ Sales_Report “). ShowTableStyleRowStripes = True PSheet.PivotTables2 (Sales_Report2) "PivotStyleMedium14" "Rodyti lentelės forma PSheet.PivotTables (" Pardavimų_ ataskaita "). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub) .RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
