Excel VBA rakurstabula
Pagrieziena tabulas ir galvenā ziņojuma kopsavilkums par lielu datu apjomu. Mēs varam arī automatizēt pagrieziena tabulas izveidošanas procesu, izmantojot VBA kodēšanu. Tās ir svarīga jebkura pārskata vai informācijas paneļa sastāvdaļa, programmā Excel ir viegli izveidot tabulas ar pogu, taču VBA mums ir jāraksta daži kodi, lai automatizētu mūsu rakurstabulu, pirms Excel 2007 un tās vecākajām VBA versijām mums nebija nepieciešams izveidojiet kešatmiņu rakurstabulām, taču programmā Excel 2010 un tās jaunākajās versijās ir nepieciešamas kešatmiņas.
VBA mums var ietaupīt daudz laika mūsu darba vietā. Kaut arī apgūt to nav tik vienkārši, bet ir vērts tērēt laiku, lai to iemācītos. Man vajadzēja 6 mēnešus, lai izprastu pagrieziena tabulu izveidošanas procesu, izmantojot VBA. Jūs zināt, ko šie 6 mēneši man ir darījuši brīnumus, jo, mēģinot izveidot pagrieziena tabulu, es pieļāvu tik daudz kļūdu.
Bet patiesībā esmu mācījies no savām kļūdām, un tagad es rakstu šo rakstu, lai parādītu, kā izveidot rakurstabulas, izmantojot kodu.
Tikai ar vienas pogas klikšķi mēs varam izveidot pārskatus.
Darbības, lai izveidotu rakurstabulu VBA
Lai izveidotu rakurstabulu, ir svarīgi, lai būtu dati. Šim nolūkam esmu izveidojis dažus manekena datus. Jūs varat lejupielādēt darbgrāmatu, lai sekotu līdzi man ar tiem pašiem datiem.
1. darbība: rakurstabula ir objekts, kas atsaucas uz rakurstabulu, kurā mainīgais tiek deklarēts kā rakurstabulas.
Kods:
Apakšvirsmas tabula () Dim PTable kā rakurstabulas beigu apakšgrupa
2. solis: Pirms vispirms izveidojam rakurstabulu, mums jāizveido šarnīra kešatmiņa, lai definētu datu avotu.
Parastajās darblapās šarnīra tabula, neraizējot mūs, fonā izveidos pagrieziena kešatmiņu. Bet VBA mums ir jārada.
Šim nolūkam definējiet mainīgo PivotCache.
Kods:
Blāvs PCache kā rakurstēvs
3. solis: lai noteiktu pagrieziena datu diapazonu, definējiet mainīgo kā diapazonu.
Kods:
Dim PRange kā diapazons
4. solis: Lai ievietotu rakurstabulu, mums ir nepieciešama atsevišķa lapa, lai pievienotu darblapas rakurstabulai, lai mainīgo mainītu kā darblapu.
Kods:
Dim PSheet kā darblapa
5. darbība. Līdzīgi, lai atsauktu datus, kas satur darblapu, deklarējiet vēl vienu mainīgo kā darblapu.
Kods:
Dim DSheet kā darblapa
6. solis: Visbeidzot, lai atrastu pēdējo izmantoto rindu un kolonnu, definējiet vēl divus mainīgos kā Long.
Kods:
Dim LR Tik ilgi Dim LC Tik ilgi
7. solis: Tagad mums jāievieto jauna lapa, lai izveidotu pagrieziena tabulu. Pirms tam, ja tur ir kāda šarnīra lapa, mums tas ir jāizdzēš.
8. solis: Tagad iestatiet objekta mainīgo PSheet un DSheet attiecīgi uz Pivot Sheet un Data Sheet.
9. solis: datu lapā atrodiet pēdējo izmantoto rindu un pēdējo izmantoto kolonnu.
10. solis: Tagad iestatiet pagrieziena diapazonu, izmantojot pēdējo rindu un pēdējo kolonnu.
Tas lieliski iestatīs datu diapazonu. Tas automātiski atlasīs datu diapazonu, pat ja datu lapā ir kāds datu papildinājums vai dzēšana.
11. darbība: pirms izveidojam rakurstabulu, jāizveido pagrieziena kešatmiņa. Iestatiet pagrieziena kešatmiņas mainīgo, izmantojot tālāk redzamo VBA kodu.
12. solis: Tagad izveidojiet tukšu rakurstabulu.
13. solis: Pēc rakurstabulas ievietošanas mums vispirms jāievieto rindas lauks. Tāpēc es ievietošu rindas lauku kā kolonnu Valsts.
Piezīme. Lejupielādējiet darbgrāmatu, lai izprastu datu kolonnas.
14. solis: Tagad vēl vienu vienumu ievietošu rindas laukā kā otro pozīcijas vienumu. Es ievietošu produktu kā otro rindas vienību rindas laukā.
15. solis: Pēc kolonnu ievietošanas rindas laukā kolonnu laukā mums jāievieto vērtības. Kolonnas laukā ievietošu “Segments”.
16. solis: Tagad datu laukā mums jāievieto skaitļi. Tāpēc datu laukā ievietojiet “Pārdošana”.
17. darbība: mēs esam pabeiguši rakurstabulas kopsavilkuma daļu. Tagad mums ir jāformatē tabula. Lai formatētu rakurstabulu, izmantojiet zemāk esošo kodu.
Lai tabulas veidā parādītu rindā iesniegto vērtību vienumus, pievienojiet zemāk esošo kodu.
Labi, mēs esam pabeiguši, ja palaižam šo kodu, izmantojot F5 taustiņu vai manuāli, tad mums vajadzētu iegūt šādu rakurstabulu.
Šādi, izmantojot VBA kodēšanu, mēs varam automatizēt rakurstabulas izveidošanas procesu.
Jūsu zināšanai es norādīju zemāk esošo kodu.
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 Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
