Tabla dinámica de Excel VBA

La tabla dinámica de VBA lo ayuda a resumir los informes de un conjunto de datos grande. Pivot puede considerarse como una pequeña forma de todo el conjunto de datos. La vista rápida de un gran conjunto de datos es posible a través de una tabla dinámica. Una tabla dinámica es una manera fácil de filtrar los datos en consecuencia. De los datos disponibles, puede resaltar los datos de la manera que desee. Una tabla dinámica le permite combinar la gran cantidad de datos, analizar los datos y producir informes que cumplan con los requisitos de su negocio.

El Excel ofrece una tabla dinámica incorporada que es fácil de crear aplicando una tabla dinámica sobre una hoja de datos. Es posible generar un informe automáticamente en Excel una vez que se proporcionan los datos. Los códigos VBA lo ayudarán a crear una tabla dinámica automática.

¿Cómo crear una tabla dinámica usando Excel VBA?

Los pasos comunes para insertar una tabla dinámica son primero insertar una tabla dinámica desde el menú Insertar y luego seleccionar las tablas que desea cambiar en una tabla dinámica. La tabla seleccionada se convertirá en los datos de origen y la tabla dinámica se creará en consecuencia.

Para crear un informe dinámico, debe seleccionar los campos para los filtros, valores, etc. De manera similar, estos pasos pueden automatizarse utilizando los códigos VBA. Necesitamos una hoja de datos de Excel para operar con una tabla dinámica. Aprendamos a crear una tabla dinámica a través de los códigos VBA de Excel.

Puede descargar esta plantilla de Excel de tabla dinámica de VBA aquí - Plantilla de Excel de tabla dinámica de VBA

Paso 1 : la tabla dinámica debe considerarse como un objeto de referencia. Cree un objeto para la tabla dinámica en códigos adicionales. Este objeto se utilizará para señalar la tabla dinámica. Nombra la variable como pvtable. Declarar una función y objeto de tabla dinámica.

Código:

 Sub PivotTable () Dim pvtable As PivotTable End Sub

Paso 2: para mantener los archivos de datos, necesitamos un caché dinámico para declarar el origen de los datos. Antes de crear una tabla dinámica, declare una variable dinámica de caja. Declare la variable pvcache para la memoria caché dinámica.

Código:

 Dim pvcache As PivotCache

Paso 3: Los datos están dentro de la hoja de trabajo y deben especificar un rango para golpear la celda que necesita. En la tabla dinámica, los datos de origen se distribuyen en filas y columnas, por lo que para señalar un rango particular necesitamos una variable. Defina pvrange como una variable de rango.

Código:

 Rango de luz tenue como rango

Paso 4: una hoja de trabajo debe insertar la tabla dinámica que necesita crear. Declarar una variable como una hoja de trabajo. Defina pvsheet como una hoja de trabajo.

Código:

 Dim pvsheet As Worksheet

Paso 5: necesita una variable similar para usar la hoja de datos que debe contener los datos que desea trazar como una tabla dinámica. Por lo tanto, la variable de hoja de datos se declara como pdsheet.

Código:

 Dim pdsheet As Worksheet

Paso 6: necesita dos variables más como tipo de datos largo para indicar la última fila y columna utilizadas para la creación de la tabla dinámica. Esto puede ser cualquier fila o columna, por lo que existen posibilidades de que haya varias filas y columnas más allá del límite de tipo de datos entero. Vamos a llamarlo plr y plc.

Código:

 Dim plr As Long Dim plc As Long

Paso 7: el siguiente paso es eliminar si ya se ha creado una tabla dinámica. Esto ayudará a evitar confusiones en qué tabla se trazarán los datos de origen. Por lo tanto, elimine la hoja anterior de la tabla dinámica y cree una nueva hoja para insertar la tabla dinámica.

Código:

 En caso de error, reanudar la siguiente aplicación.DisplayAlerts = False Application.ScreenUpdating = False Worksheets ("pvsheet"). Delete 'para eliminar la tabla dinámica existente en la hoja de trabajo Worksheets.Add After: = ActiveSheet' para agregar una nueva hoja de trabajo ActiveSheet.Name = " pvsheet "'para cambiar el nombre de la hoja de trabajo a" pvsheet "en caso de error Ir a 0

Paso 8: Asigne una variable de objeto para la hoja pivotante y la hoja de datos respectivamente a las variables pvsheet y pdsheet. Esto se utilizará más para especificar las hojas de trabajo.

Código:

 Establecer pvsheet = Worksheets ("pvsheet") Establecer pdsheet = Worksheets ("pdsheet")

Paso 9: Una vez que se configuran las hojas de trabajo, el siguiente elemento es que necesitamos la última fila y columna utilizadas para crear un informe dinámico. Encuentre la última fila y columna utilizada utilizando las variables declaradas plr y plc.

Código:

 'dos variables para encontrar Última fila y columna utilizadas en pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet.Cells (1, Columns.Count) .End (xlToLeft) .Column

Paso 10: Como mencionamos anteriormente, los datos en la hoja de trabajo se especifican por el rango de celdas. Tienes que establecer el rango de pivote en el siguiente paso. Ya está declarado como variable para usar el rango de pivote "pvrange".

Código:

 'inicializando rango de datos de tabla dinámica Establecer pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)

Dado que el rango se establece usando la propiedad de cambio de tamaño de los rangos de celdas, cambiará el tamaño del rango de ajuste y ajustará el rango de pivote en consecuencia. Por lo tanto, el rango de ajuste se ajustará si ocurre alguna adición o eliminación de las filas o columnas.

Paso 11: es hora de establecer la memoria caché dinámica que es la fuente de la tabla dinámica. Use el objeto pvcache para establecer el caché de origen.

Código:

 'pivot cahe Establecer pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange)

Paso 12: Aquí, el informe de ventas para diferentes productos se convertirá en una tabla dinámica a través de esto. Cree una tabla dinámica en blanco a la que pueda agregar aún más el conjunto de datos.

Código:

 'nueva tabla dinámica en blanco Establecer pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cells (1, 1), TableName: = "Sales_Report")

Paso 13: Una vez que se inserta el pivote, debe especificar los diferentes campos que desea insertar en la tabla dinámica. Entonces inserte el campo de la primera fila. Aquí la primera fila comienza con el producto.

Código:

 'Insertar producto en fila archivado con pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 End With

Paso 14: el siguiente es especificar el segundo campo que desea insertar en la tabla dinámica. Del mismo modo, inserte la calle del campo de la segunda fila en la tabla dinámica.

Código:

 'Insertar Street to Row Filed & position 2 With pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End With

Paso 15: inserte el siguiente campo en la tabla dinámica y el siguiente es una ciudad. Dé el código para insertar el campo de la ciudad.

Código:

 'Insertar ciudad en columna archivada con pvsheet.PivotTables ("Sales_Report"). PivotFields ("Town") .Orientation = xlColumnField .Position = 1 End With

Paso 16: Hasta esto, los datos insertados fueron de tipo texto. Ahora necesitamos insertar el número de ventas en la tabla dinámica.

Código:

 'Insertar columna de ventas en el campo de datos con pvsheet.PivotTables ("Sales_Report"). PivotFields ("Sales") .Orientation = xlDataField .Position = 1 End With

Paso 17: ha insertado los campos que deben crear una tabla dinámica. Y la tabla dinámica está casi terminada, ahora puede establecer el formato de la tabla dinámica. Esto especificará el tipo de tabla a través del estilo de tabla. El diseño de eje de fila también se configura de la manera que desee.

Código:

 'establece el formato Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14"

Paso 18: para mostrar los elementos de valores archivados en forma de tabla, agregue el código a continuación en la parte inferior.

Código:

 'para mostrar la tabla dinámica en forma tabular pvsheet.PivotTables ("Sales_Report"). RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True

Paso 19: presiona el botón Ejecutar o presiona F5 para ejecutar el código. Esto producirá una tabla dinámica a partir de la hoja de origen de datos. Los datos proporcionados en filas y columnas se cambiarán a la tabla dinámica. La tabla dinámica estará visible en la hoja de trabajo de la tabla dinámica.

Verifique el resultado y puede ver que la fuente de datos se convierte en la tabla dinámica como se muestra a continuación, las columnas mencionadas se convierten en los formularios de filtro.

Los campos dinámicos son visibles en el lado derecho. Puede realizar cambios de acuerdo con su necesidad de cómo deben mostrarse los datos.

Para su referencia, he dado el código a continuación.

Código:

 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 = False Worksheets ("pvsheet ") .Elimine 'para eliminar la tabla dinámica existente en las hojas de trabajo de la hoja de trabajo. Agregue después: = ActiveSheet' para agregar una nueva hoja de trabajo ActiveSheet.Name =" pvsheet "'para renombrar la hoja de trabajo en" pvsheet "En caso de error Ir a 0 Configurar pvsheet = Hojas de trabajo ("pvsheet") Establezca pdsheet = Worksheets ("pdsheet") 'dos ​​variables para encontrar la última fila y columna utilizada en pdsheet plr = pdsheet.Cells (Rows.Count, 1) .End (xlUp) .Row plc = pdsheet. Cells (1, Columns.Count) .End (xlToLeft) .Column 'initializing table pivot data range Set pvrange = pdsheet.Cells (1, 1) .Resize (plr, plc)' pivot cahe Set pvcache = ActiveWorkbook.PivotCaches.Create (xlDatabase, SourceData: = pvrange) 'nueva tabla dinámica en blanco Establecer pvtable = pvcache.CreatePivotTable (TableDestination: = pvsheet.Cel ls (1, 1), TableName: = "Sales_Report") 'Insertar producto en fila archivado con pvsheet.PivotTables ("Sales_Report"). PivotFields ("Product") .Orientation = xlRowField .Position = 1 Finalizar con' Insertar calle en Fila archivada y posición 2 con pvsheet.PivotTables ("Sales_Report"). PivotFields ("Street") .Orientation = xlRowField .Position = 2 End With 'Insert town to Column Filed With pvsheet.PivotTables ("Sales_Report"). PivotFields (" Town ") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With pvsheet.PivotTables (" Sales_Report "). PivotFields (" Sales ") .Orientation = xlDataField .Position = 1 End With' set the formato Pivot Table pvsheet.PivotTables ("Sales_Report"). ShowTableStyleRowStripes = True pvsheet.PivotTables ("Sales_Report"). TableStyle2 = "PivotStyleMedium14" 'para mostrar la tabla dinámica en forma tabular pvsheet.PivotTables () RowAlAut.Alta_Arq. .DisplayAlerts = True Application.ScreenUpdating = True End Sub

Cosas para recordar

  • Especifique la hoja de origen donde se deben suministrar los datos para una tabla dinámica.
  • La fila y la columna de inicio deben mencionarse correctamente.
  • Use los objetos necesarios para identificar la memoria caché dinámica, el rango, el inicio y el final de las filas.
  • Organice la fuente de datos con el formato adecuado ya que este es el proceso de automatización.

Artículos recomendados

Esta es una guía de la tabla dinámica de VBA. Aquí discutimos cómo crear una tabla dinámica usando códigos VBA en Excel junto con un ejemplo y una plantilla de Excel descargable. También puede consultar los siguientes artículos para obtener más información:

  1. ¿Cómo eliminar la tabla dinámica en Excel?
  2. Usando VBA en PowerPoint
  3. Mesa pivotante con varias hojas
  4. ¿Cómo crear un rebanador de tabla dinámica?
  5. Actualizar tabla dinámica en VBA
  6. VBA en caso de error Reanudar a continuación (ejemplos)
  7. Actualización de pantalla de Excel VBA

Categoría:

VBA