Tabel rotativ Excel VBA
Tabelul rotativ VBA vă ajută să rezumați rapoartele dintr-un set mare de date. Pivotul poate considera o formă minusculă a întregului set de date. Vizualizarea rapidă a setului de date mari este posibilă printr-un tabel pivot. Un tabel pivot este un mod ușor de a filtra datele în consecință. Din datele disponibile, puteți evidenția datele așa cum doriți. Un tabel pivot vă permite să combinați datele uriașe pentru a analiza datele și pentru a produce rapoarte care îndeplinesc cerințele dvs. de afaceri.
Excel oferă un tabel pivot încorporat, care este ușor de creat prin aplicarea unui tabel pivot pe o fișă de date. Este posibilă generarea unui raport automat în excel, odată furnizate datele. Codurile VBA vă vor ajuta să creați un tabel pivot automat.
Cum să creați un tabel rotativ folosind Excel VBA?
Pașii obișnuiți pentru a introduce o tabelă pivot este să introduceți mai întâi o tabelă pivot din meniul Inserați apoi să selectați tabelele pe care doriți să le schimbați într-o tabelă pivot. Tabelul selectat va deveni datele sursă și tabelul pivot va fi creat în consecință.
Pentru a crea un raport pivot, trebuie să selectați câmpurile pentru filtre, valori etc. Într-un mod similar, acești pași pot fi automatizați folosind codurile VBA. Avem nevoie de o foaie excel pentru a funcționa cu un tabel pivot. Să învățăm cum să creăm un tabel pivot prin codurile VBA Excel.
Puteți descărca acest șablon Excel PIVOT Table Pivot aici - Șablonul VBA Pivot Table ExcelPasul 1 : Tabelul rotativ ar trebui să fie considerat un obiect de referință. Creați un obiect pentru tabelul pivot pe coduri suplimentare acest obiect va fi folosit pentru a indica tabelul pivot. Denumiți variabila ca pvtable. Declarați o funcție și un obiect din tabelul rotativ.
Cod:
Sub PivotTable () Dim pvtable Ca pivotTable End Sub
Pasul 2: Pentru a păstra fișierele de date avem nevoie de un cache pivot pentru a declara sursa datelor. Înainte de a crea un tabel pivot, declarați o variabilă pivot în numerar. Declarați variabila pvcache pentru cache-ul pivot.
Cod:
Dim pvcache Ca PivotCache
Pasul 3: Datele se află în fișa de lucru și ar trebui să specifice un interval pentru a atinge celula de care aveți nevoie. În tabelul pivot, datele sursă sunt răspândite sub formă de rânduri și coloane, astfel încât pentru a indica un anumit interval avem nevoie de o variabilă. Definiți pvrange ca o variabilă de interval.
Cod:
Dim pvrange ca rază de acțiune
Pasul 4: O foaie de lucru trebuie să insereze tabelul pivot pe care trebuie să-l creezi. Declarați o variabilă ca o foaie de lucru. Definiți pvsheet ca o foaie de lucru.
Cod:
Dim pvsheet ca fișă de lucru
Pasul 5: Aveți nevoie de o variabilă similară pentru a utiliza fișa tehnică care ar trebui să conțină datele pe care doriți să le reprezentați ca tabel pivot. Deci, variabila fișei de date este declarată ca pdsheet.
Cod:
Dim pdsheet Ca fișă de lucru
Pasul 6: Aveți nevoie de încă două variabile, ca tip de date lung, pentru a indica ultimul rând și coloana utilizate pentru crearea tabelului pivot. Acesta poate fi orice rând sau coloană, astfel încât există șanse să existe un număr de rânduri și coloane dincolo de limita de tip întreg de date. Să-l numim plr și plc.
Cod:
Dim plr As Long Dim plc As Long
Pasul 7: Următorul pas este să ștergeți dacă există vreun tabel pivot deja creat. Acest lucru va ajuta la evitarea confuziilor pe care tabelul de date sursă care urmează să fie reprezentate. Ștergeți deci foaia de tabel pivot anterior și creați o foaie nouă pentru a insera tabelul pivot.
Cod:
On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet"). Ștergeți "pentru a șterge tabelul pivot exisiting din foaia de lucru Worksheets.Add After: = ActiveSheet" pentru a adăuga o nouă foaie de lucru ActiveSheet.Name = " pvsheet "'pentru a redenumi foaia de lucru în" pvsheet "la eroarea GoTo 0
Pasul 8: Alocați o variabilă obiect pentru foaia de pivotare și respectiv fișa de date variabilelor pvsheet și pdsheet. Aceasta va fi utilizată în continuare pentru specificarea foilor de lucru.
Cod:
Set pvsheet = Worksheets ("pvsheet") Set pdsheet = Worksheets ("pdsheet")
Pasul 9: Odată ce foile de lucru sunt setate, următorul element este că avem nevoie de ultimul rând și coloană utilizate pentru crearea unui raport pivot. Găsiți ultimul rând și coloana folosit folosind variabilele declarate plr și plc.
Cod:
'două variabile pentru a găsi Ultimul rând și coloana utilizate în pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp). Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column
Pasul 10: Așa cum am menționat anterior, datele din foaia de lucru sunt specificate de gama de celule. Trebuie să setați intervalul pivot în pasul următor. Este deja declarat ca variabilă utilizarea intervalului de pivot „pvrange”.
Cod:
'inițializarea intervalului de date din tabelul pivot Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)
Deoarece intervalul este setat folosind proprietatea de redimensionare a intervalelor de celule, va redimensiona pvrange-ul va ajusta intervalul de pivot în consecință. Așadar, pvrange se va regla, se va adăuga sau se va șterge rândurile sau coloana.
Pasul 11: Este timpul să setați cache-ul pivot care este sursa tabelului pivot. Utilizați obiectul pvcache pentru a seta cache-ul sursă.
Cod:
'pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)
Pasul 12: Aici raportul de vânzări pentru diferite produse va fi convertit într-un tabel pivot prin intermediul acestui. Creați un tabel pivot ca gol la care puteți adăuga setul de date în continuare.
Cod:
'new table pivot blank Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")
Pasul 13: după ce pivotul este inserat, trebuie să specificați diferitele câmpuri pe care doriți să le introduceți în tabelul pivot. Așadar, introduceți câmpul din primul rând. Aici primul rând începe cu produsul.
Cod:
'Introduceți produsul în rândul arhivat cu pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 End With
Pasul 14: Următorul este să specificați al doilea câmp pe care doriți să îl introduceți în tabelul pivot. În același mod, introduceți strada de câmp din al doilea rând în tabelul pivot.
Cod:
'Introduceți Street to Row Filed & position 2 Cu pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End With
Pasul 15: Introduceți câmpul următor în tabelul pivot și următorul este un oraș. Dați codul pentru a introduce câmpul orașului.
Cod:
'Inserați orașul în coloana fișieră cu pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town") .Orientation = xlColumnField .Position = 1 End With
Pasul 16: Până la aceasta, datele introduse au fost de tip text. Acum trebuie să introducem numărul de vânzări în tabelul pivot.
Cod:
'Introduceți coloana de vânzări în câmpul de date cu pvsheet.PivotTables ("Sales_Report"). PivotFields ("Vânzări") .Orientation = xlDataField .Position = 1 End With
Pasul 17: Ați introdus câmpurile care trebuie să creeze un tabel pivot. Iar tabelul pivot este aproape terminat acum puteți seta formatul tabelului pivot. Aceasta va specifica tipul de tabel prin stilul tabelului. Layout Axis Row este setat și în modul dorit.
Cod:
'setează formatul Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"
Pasul 18: Pentru a afișa elementele de valori înregistrate în formă de tabel, adăugați codul de mai jos în partea de jos.
Cod:
'pentru a afișa tabelul pivot în forma tabulară pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True
Pasul 19: Apăsați butonul de rulare sau apăsați pe F5 pentru a rula codul. Aceasta va produce un tabel pivot din foaia sursă de date. Datele date în rânduri și coloane vor fi schimbate în tabelul pivot. Tabelul pivot va fi vizibil pe foaia de lucru a tabelului pivot.
Verificați ieșirea și puteți vedea sursa de date este transformată în tabelul pivot, ca mai jos, coloanele menționate sunt convertite în formularele de filtrare.
Câmpurile pivot sunt vizibile în partea dreaptă. Puteți face modificări în funcție de nevoia dvs. de modul în care datele trebuie prezentate.
Pentru referință, am dat codul de mai jos.
Cod:
Sub PivotTable () Dim pvtable As PivotTable Dim pvcache As PivotCache Dim pvrange As Range Dim pvsheet As Worksheet Dim pdsheet As Worksheet Dim plr As Long Dim plc As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = Falsesheets "). Șterge" pentru a șterge tabelul pivot exisiting din foaia de lucru Worksheets.Add After: = ActiveSheet 'pentru a adăuga o nouă foaie de lucru ActiveSheet.Name = "pvsheet"' pentru a redenumi foaia de lucru în "pvsheet" Pe eroarea GoTo 0 Set pvsheet = Worksheets ("pvsheet") Set pdsheet = Worksheets ("pdsheet") 'two variable to find Ultima linie și coloană utilizate în pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp). Row plc = pdsheet. Cells (1, Columns.Count) .End (xlToLeft) .Column 'Initialization table table pivot range Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'nou tabel pivot necompletat Set pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Insert Product in Row Filed With pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 End with' Insert Street to Rând Filed & position 2 Cu pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End with 'Insert town to Column Filed With pvsheet.PivotTables ("Sales_Report"). "PivotFields" Oraș ") .Orientation = xlColumnField .Position = 1 End with 'Insert Sales column in the field data with pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End With' set the format Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = Adevărat pvsheet.PivotTables ("Sales_Report"). .DisplayAlerts = Aplicare adevărată.ScreenUpdating = Sub final final
Lucruri de amintit
- Specificați foaia sursă unde se vor furniza datele pentru un tabel pivot.
- Rândul și coloana de pornire trebuie menționate corect.
- Utilizați obiectele necesare pentru a identifica cache-ul pivot, intervalul, începutul și sfârșitul rândurilor.
- Aranjați sursa de date cu formatul adecvat, deoarece acesta este procesul de automatizare.
Articole recomandate
Acesta este un ghid pentru tabelul rotativ VBA. Aici vom discuta despre cum se poate crea un tabel pivot folosind codurile VBA în excel împreună cu un exemplu și un șablon excel descărcabil. De asemenea, puteți consulta următoarele articole pentru a afla mai multe -
- Cum se șterge tabelul rotativ în Excel?
- Utilizarea VBA în PowerPoint
- Masa rotativă cu mai multe foi
- Cum să creați Slicer Table Pivot?
- Actualizează tabelul pivot în VBA
- VBA pe eroare reluă Următorul (Exemple)
- Excel VBA ScreenUpdating