photosdematures.com

Taula dinàmica VBA d'Excel

Taules dinàmiques són el cor de resumir l’informe d’una gran quantitat de dades. També podem automatitzar el procés de creació d’una taula dinàmica mitjançant la codificació VBA. Són part important de qualsevol informe o tauler, a Excel és fàcil crear taules amb un botó, però a VBA hem d’escriure alguns codis per automatitzar la nostra taula dinàmica, abans d’excel 2007 i les seves versions anteriors a VBA, no calia creeu una memòria cau per a taules dinàmiques, però a Excel 2010 i les seves versions més recents calen memòries cau.

VBA ens pot estalviar tones de temps al nostre lloc de treball, tot i que dominar-lo no és tan fàcil, però val la pena dedicar-hi temps a aprendre-ho. Vaig trigar 6 mesos a entendre el procés de creació de taules dinàmiques mitjançant VBA. Saps què han fet meravelles aquests 6 mesos perquè he comès molts errors en intentar crear la taula dinàmica.

Però, en realitat, he après dels meus errors i ara escric aquest article per mostrar-vos com crear taules dinàmiques mitjançant codi.

Amb només fer clic d’un botó, podem crear informes.

Passos per crear una taula dinàmica a VBA

Podeu descarregar aquesta plantilla de taula dinàmica VBA aquí: plantilla de taula dinàmica VBA

Per crear una taula dinàmica és important tenir dades. Per a això he creat algunes dades fictícies, podeu descarregar el llibre de treball per seguir amb mi amb les mateixes dades.

Pas 1:La taula dinàmica és un objecte que fa referència a la taula dinàmica que declara la variable com a taules dinàmiques.

Codi:

 Taula dinàmica secundària () Dim Dim taula com a subfinestra de taula dinàmica 

Pas 2:Abans de crear una taula dinàmica primer, hem de crear una memòria cau dinàmica per definir la font de les dades.

A la taula dinàmica normal del full de treball sense problemes, crearem una memòria cau dinàmica en segon pla. Però a VBA hem de crear.

Per a això, definiu la variable a PivotCache.

Codi:

 Disminueix PCache com a PivotCache

Pas 3:Per determinar l'interval de dades de pivot, definiu la variable com a interval.

Codi:

 Dim PRange com a rang

Pas 4:Per inserir una taula dinàmica necessitem un full independent per afegir full de càlcul perquè la taula dinàmica declari la variable com a full de treball.

Codi:

 Disminueix el full PS com a full de treball

Pas 5:De manera similar a la referència, les dades que contenen el full de treball declaren una variable més com a full de treball.

Codi:

 Dismineix el full DS com a full de treball

Pas 6: Finalment, per trobar l'última fila i columna utilitzades, definiu dues variables més com a Llargues.

Codi:

 Dim LR As Long Dim LC Long 

Pas 7: Ara hem d’inserir un full nou per crear una taula dinàmica. Abans, si hi ha algun full dinàmic, hem de suprimir-lo.

Pas 8: Ara configureu la variable objecte PSheet i DSheet a Full pivot i Full de dades respectivament.

Pas 9: Cerqueu l'última fila utilitzada i l'última columna usada al full de dades.

Pas 10: Ara configureu l'interval de pivot mitjançant l'última fila i l'última columna.

Això configurarà l'interval de dades perfectament. Seleccionarà automàticament l’interval de dades fins i tot si hi ha alguna addició o supressió de dades al full de dades.

Pas 11: Abans de crear una taula dinàmica, hem de crear una memòria cau dinàmica. Establiu la variable de memòria cau de pivot mitjançant el codi VBA següent.

Pas 12: Ara creeu una taula dinàmica en blanc.

Pas 13: Després d’inserir la taula dinàmica, primer hem d’inserir el camp de fila. Per tant, inseriré el camp de fila com a columna País.

Nota: Baixeu-vos el llibre per entendre les columnes de dades.

Pas 14: Ara inseriré un element més al camp de fila com a element de segona posició. Inseriré el producte com a segona línia de comanda al camp de la fila.

Pas 15: Després d’inserir les columnes al camp de la fila, hem d’inserir valors al camp de la columna. Inseriré el "Segment" al camp de la columna.

Pas 16: Ara hem d’inserir números al camp de dades. Inseriu "Vendes" al camp de dades.

Pas 17: Ja hem acabat amb la part resum de la taula dinàmica, ara hem de formatar la taula. Per formatar la taula dinàmica, utilitzeu el codi següent.

Nota: Per tenir més estils de taula diferents, registreu-los en macro i obteniu els estils de taula.

Per mostrar els elements de valors arxivats en fila en forma de taula, afegiu el codi següent a la part inferior.

D'acord, ja hem acabat si executem aquest codi amb la tecla F5 o manualment, hauríem d'obtenir la taula dinàmica d'aquesta manera.

Així, mitjançant la codificació VBA, podem automatitzar el procés de creació d’una taula dinàmica.

Per a la vostra referència, he donat el codi següent.

 Sub PivotTable () Dim PTable Com PivotTable Dim PCache Com PivotCache Dim PRange As Range Dim PSheet As Full de treball Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Reproduïu la següent aplicació. Full "). Esborra 'Això suprimirà el full de càlcul existent del full de càlcul de la taula dinàmica. Afegeix després: = Full actiu' S'afegirà un nou full de càlcul ActiveSheet.Name =" Full pivot "'Això canviarà el nom del full de treball com a" Full dinàmic "a l'error GoTo 0 Establir PSheet = Fulls de treball ("Full dinàmic") Establir DSheet = Fulls de treball ("Full de dades") 'Cerca l'última fila i columna utilitzades al full de dades LR = DSheet.Cells (Rows.Count, 1). End (xlUp) .Row LC = DSheet.Cells (1, Columns.Count). Final (xlToLeft). Column 'Establir l'interval de dades de la taula dinàmica Set PRange = DSheet.Cells (1, 1). Resize (LR, LC)' Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = PRange) 'Crea una taula dinàmica en blanc Establir PTable = PCache.CreatePivotTable (TableDestination: = PSheet.Cells (1 , 1), TableName: = "Sales_Report") 'Insereix país a la fila arxivada amb PSheet.PivotTables ("Sales_Report"). PivotFields ("Country"). posició 2 Amb PSheet.PivotTables ("Sales_Report"). PivotFields ("Producte") .Orientation = xlRowField .Position = 2 Finalitza amb 'Insereix segment a la columna arxivada i posició 1 amb PSheet.PivotTables ("Sales_Report"). PivotFields (" Segment ") .Orientation = xlColumnField .Position = 1 Finalitza amb 'Insereix la columna Sales al camp de dades amb PSheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 Finalitza amb' Format Pivot Taula PSheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True PSheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" 'Mostra en forma tabular PSheet.PivotTables ("Sales_Report"). ScreenUpdating = Sub finalitat veritable