Tabel pivot Excel VBA
Tabelele pivot sunt inima rezumării raportului pentru o cantitate mare de date. De asemenea, putem automatiza procesul de creare a unei tabele pivot prin codificare VBA. Acestea sunt o parte importantă a oricărui raport sau tablou de bord, în Excel este ușor să creăm tabele cu un buton, dar în VBA trebuie să scriem câteva coduri pentru a automatiza tabelul nostru pivot, înainte de Excel 2007 și versiunile sale mai vechi în VBA, nu trebuia să creați un cache pentru tabelele pivot, dar în Excel 2010 și versiunile sale mai noi sunt necesare cache-uri.
VBA poate economisi tone de timp pentru noi la locul de muncă. Chiar dacă stăpânirea nu este atât de ușoară, dar merită să petreceți timp pentru a afla acest lucru. Am luat 6 luni pentru a înțelege procesul de creare a tabelelor pivot prin VBA. Știi ce au făcut minuni acele 6 luni pentru că am făcut atât de multe greșeli în timp ce încercam să creez tabelul pivot.
Dar adevărul este că am învățat din greșelile mele și acum scriu acest articol pentru a vă arăta cum să creați tabele pivot folosind codul.
Cu doar un clic de buton, putem crea rapoarte.
Pași pentru a crea tabelul pivot în VBA
Pentru a crea un tabel pivot, este important să aveți date. Pentru aceasta, am creat câteva date false. Puteți descărca registrul de lucru pentru a-l urma cu mine cu aceleași date.
Pasul 1: Tabelul pivot este un obiect care face referire la tabelul pivot care declară variabila ca tabele pivot.
Cod:
Sub PivotTable () Dim PTable Ca PivotTable End Sub
Pasul 2: Înainte de a crea mai întâi un tabel pivot, trebuie să creăm o memorie cache pivot pentru a defini sursa datelor.
În foile de lucru obișnuite, tabelul pivot fără să ne deranjeze va crea un cache pivot în fundal. Dar în VBA, trebuie să creăm.
Pentru aceasta, definiți variabila a PivotCache.
Cod:
Dim PCache ca PivotCache
Pasul 3: Pentru a determina intervalul de date pivot definiți variabila ca un interval.
Cod:
Dim PRange Ca Range
Pasul 4: Pentru a insera un tabel pivot, avem nevoie de o foaie separată pentru a adăuga foi de lucru pentru ca tabelul pivot să declare variabila ca foaie de lucru.
Cod:
Dim PSheet Ca foaie de lucru
Pasul 5: În mod similar, pentru a face referire la datele care conțin foaia de lucru, declarați încă o variabilă ca Foaie de lucru.
Cod:
Dim DSheet Ca foaie de lucru
Pasul 6: În cele din urmă, pentru a găsi ultimul rând și coloană utilizate, definiți încă două variabile ca Long.
Cod:
Dim LR As Long Dim LC As Long
Pasul 7: Acum, trebuie să inserăm o foaie nouă pentru a crea un tabel pivot. Înainte, dacă există o foaie pivot, trebuie să o ștergem.
Pasul 8: acum, setați variabila obiect PSheet și DSheet la Foaie pivot și respectiv Foaie de date.
Pasul 9: Găsiți ultimul rând folosit și ultima coloană utilizată în foaia de date.
Pasul 10: Acum setați intervalul pivot utilizând ultimul rând și ultima coloană.
Aceasta va seta gama de date perfect. Acesta va selecta automat intervalul de date, chiar dacă există orice adăugare sau ștergere a datelor în foaia de date.
Pasul 11: Înainte de a crea un tabel pivot, trebuie să creăm un cache pivot. Setați variabila cache pivot utilizând codul VBA de mai jos.
Pasul 12: acum, creați un tabel pivot gol.
Pasul 13: După introducerea tabelului pivot, trebuie să introducem mai întâi câmpul rând. Așa că voi insera câmpul rândului ca coloană de țară.
Notă: Descărcați registrul de lucru pentru a înțelege coloanele de date.
Pasul 14: Acum, încă un element pe care îl voi introduce în câmpul rând ca element de poziție a doua. Voi insera Produsul ca al doilea element rând în câmpul rând.
Pasul 15: După introducerea coloanelor în câmpul rând, trebuie să inserăm valori în câmpul coloanei. Voi introduce „Segment” în câmpul coloanei.
Pasul 16: Acum, trebuie să introducem numere în câmpul de date. Așadar, introduceți „Vânzări” în câmpul de date.
Pasul 17: Am terminat cu partea de rezumat a tabelului pivot. Acum trebuie să formatăm tabelul. Pentru a formata tabelul pivot, utilizați codul de mai jos.
Pentru a afișa elementele de valori înregistrate în rând sub formă de tabel, adăugați codul de mai jos în partea de jos.
Ok, am terminat dacă executăm acest cod folosind tasta F5 sau manual, atunci ar trebui să obținem tabelul pivot în felul acesta.
Astfel, folosind codarea VBA, putem automatiza procesul de creare a unui tabel pivot.
Pentru referință, am dat codul de mai jos.
Sub PivotTable () Dim PTable Ca PivotTable Dim PCache Ca PivotCache Dim PRange Ca Range Dim PSheet Ca Foaie de lucru Dim DSheet Ca Foaie de lucru Dim LR Ca Long Dim LC As Long On Error Reîncepeți următoarea aplicație.DisplayAlerts = False Application.ScreenUpdating = Foile de lucru false Foaie "). Ștergeți" Aceasta va șterge foaia de lucru existentă a tabelului pivot Foaie de lucru. Adăugați după: = Foaie activă 'Aceasta va adăuga o foaie de lucru nouă ActiveSheet.Name = "Foaie pivot"' Aceasta va redenumi foaia de lucru ca "Foaie pivot" La eroare Set PSheet = Worksheets ("Pivot Sheet") Set DSheet = Worksheets ("Data Sheet") 'Găsiți ultimul rând și coloană folosite în foaia de date LR = DSheet.Cells (Rows.Count, 1). End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count) .End (xlToLeft) .Column 'Setați intervalul de date al tabelului pivot Set PRange = DSheet.Cells (1, 1) .Resize (LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange)' Create pivot table Set PTable = PCache.CreatePivotTable (TableDestination: = PSheet. Celule (1, 1), TableName: = "Sales_Report") 'Inserați țara în rândul înregistrat cu PSheet.PivotTables ("Sales_Report"). PivotFields ("Country") .Orientation = xlRowField .Position = 1 End With' Insert Product to Rând 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 Terminați cu 'Inserați coloana Vânzări în câmpul de date cu 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"). "PivotStyleMedium14" "Afișați în formă tabelară PSheet.PivotTables (" Sales_Report "). 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
