Biblioteca Online : ¡La Suscripción ENI por 9,90 € el primer mes!, con el código PRIMER9. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. VBA Excel (versiones 2021 y Microsoft 365)
  3. Tablas dinámicas y gráficos
Extrait - VBA Excel (versiones 2021 y Microsoft 365) Programar en Excel: Macros y lenguaje VBA
Extractos del libro
VBA Excel (versiones 2021 y Microsoft 365) Programar en Excel: Macros y lenguaje VBA Volver a la página de compra del libro

Tablas dinámicas y gráficos

Las tablas dinámicas

La colección PivotTables contiene todos los objetos de tabla dinámica (objetos PivotTable) que hay en una hoja de cálculo.

1. El objeto PivotTable

Esta sección describe las colecciones, propiedades y métodos más comúnmente utilizados para la creación y modificación de tablas dinámicas.

a. Colecciones

CalculatedFields

Colección de todos los campos calculados de la tabla dinámica especificada.

ColumnFields

Colección de todos los campos de la tabla dinámica especificada que se muestran como campos de columna.

DataFields

Colección de todos los campos de la tabla dinámica especificada que se muestran como campos de datos.

HiddenFields

Colección de todos los campos del origen de datos de la tabla dinámica especificada que no se muestran.

PageFields

Colección de todos los campos de la tabla dinámica especificada que se muestran como campos de página.

RowFields

Colección de todos los campos de la tabla dinámica especificada que se muestran como campos de fila.

PivotFields

Colección de todos los campos del origen de datos de la tabla dinámica especificada, se muestren o no.

VisibleFields

Colección de todos los campos del origen de datos de la tabla dinámica especificada que se muestran.

Todas estas colecciones devuelven objetos PivotField que representan un campo de la tabla dinámica.

b. Propiedades

Las propiedades descritas a continuación corresponden a las diferentes opciones de las tablas dinámicas (a las que se puede acceder por la opción del menú contextual Opciones de tabla dinámica).

Están clasificadas por pestañas y ordenadas por orden de visualización en la pestaña.

Opciones de la pestaña Diseño y formato

Propiedad

Descripción

Tipo

MergeLabel

Corresponde a la opción Combinar y centrar celdas con etiquetas.

Booleano

CompactRowIndent

Número de caracteres de sangría de las etiquetas de fila en forma compacta.

Entero largo

DisplayErrorString

Indique si se muestra una cadena personalizada en las celdas con error.

Booleano

ErrorString

Cadena que se muestra en las celdas que contienen errores cuando la propiedad DisplayErrorString tiene el valor True.

Cadena de caracteres

DisplayNullString

Indica si se muestra una cadena...

Los gráficos

Un gráfico está representado por un objeto Chart, que está en la colección Shapes (objetos Shape) que representan las formas u objetos dibujados en una hoja de cálculo. En el caso de un gráfico, el objeto Shape representa la zona del gráfico.

1. El objeto Shape

Esta sección describe las propiedades y métodos más comúnmente utilizados para crear y dar formato a las zonas de gráficos.

a. Propiedades

Propiedades

Descripción

Tipo

Chart

Devuelve un objeto Chart que representa el gráfico contenido en la forma.

Objeto

Fill

Devuelve un objeto FillFormat que representa el formato de relleno de la forma.

Objeto

Line

Devuelve un objeto LineFormat que contiene las propiedades de formato del borde de la forma.

Objeto

TopLeftCell

Devuelve un objeto Range que representa la celda que está en la esquina superior izquierda de la forma.

Objeto

HasChart

Indica si la forma contiene un gráfico.

Booleano

Height

Altura de la forma en puntos.

Real simple

Left

Distancia, en puntos, entre el borde izquierdo de la forma y el borde izquierdo de la columna A.

Real simple

Top

Distancia, en puntos, entre el borde superior de la forma y el borde superior de la hoja de cálculo.

Real simple

Width

Largo de la forma en puntos.

Real simple

b. Métodos

Copy

Copia la forma en el Portapapeles.

CopyPicture

Copia la forma en el Portapapeles como una imagen.

Delete

Elimina la forma.

2. El objeto Chart

Esta sección describe las propiedades y métodos más comúnmente utilizados para la creación y formato de gráficos.

a. Colecciones

Axes

Colección de todos los ejes del gráfico.

SeriesCollections

Colección de todas las series de datos del gráfico.

b. Propiedades

Propiedad

Descripción

Tipo

ChartArea

Devuelve un objeto ChartArea que representa la zona del gráfico.

Objeto...

Ejemplo de aplicación

1. Presentación

El siguiente ejemplo permite generar automáticamente tablas y gráficos estadísticos sobre el reparto del tiempo de trabajo de los empleados por día, semana, actividad...

Los datos de origen están situados en la tabla de celdas llamada "TablaTiempos" que puede ver a continuación.

Extracto de los datos origen

images/05RITEXCV01.png

Ejemplo de tabla dinámica y gráfico generado

images/05RITEXCV02.png

2. Código VBA del ejemplo

El procedimiento GeneraEstadisticas permite generar todas las tablas dinámicas y gráficos. Hace una llamada al procedimineto Creacion_TCD para la creación de las tablas dinámicas.

Option Explicit  
 Dim oWbk As Workbook  
_____________________________________________________________  
Sub GeneraEstadisticas ()  
Dim oSheet As Worksheet  
Dim oShape As Shape  
Dim oChart As Chart  
Dim iNumVision As Integer  
Dim iNumAct As Integer  
Dim i As Integer  
Dim j As Integer  
Dim k As Integer  
   
'   Elimina las hojas existentes 
Set oWbk = ThisWorkbook  
Application.DisplayAlerts = False  
For i = oWbk.Sheets.Count To 2 Step -1  
   oWbk.Sheets(i).Delete  
Next  
Application.DisplayAlerts = True  
   
'   Añade las nuevas hojas 
For i = oWbk.Sheets.Count + 1 To 5  
   oWbk.Sheets.Add after:=oWbk.Sheets(oWbk.Sheets.Count)  
Next  
oWbk.Sheets(2).Name = "RESUMEN TAREA" 
oWbk.Sheets(3).Name = "RESUMEN ACTIVIDAD" 
oWbk.Sheets(4).Name = "RESUMEN DIA" 
oWbk.Sheets(5).Name = "RESUMEN VISION" 
   
'    Tabla dinámica Resumen Tarea 
Creacion_TCD "TCD1", "Resumen Tarea", "Actividad", "Tarea" 
  
'    Tabla dinámica Resumen Actividad 
Creacion_TCD "TCD2", "Resumen Actividad", "Actividad", "" 
  
'    Tabla dinámica Resumen Día 
Creacion_TCD "TCD3", "Resumen Dia", "Semana", "Día" 
  
'    Tabla dinámica...

Creación de una tabla dinámica con minigráficos

El ejemplo siguiente permite:

  • crear y personalizar una tabla dinámica utilizando las colecciones de objetos PivotTables y PivotFields,

  • agregar un formato condicional de tipo barra de datos, utilizando la colección de objetos FormatConditions,

  • agregar minigráficos utilizando la colección de objetos SparklineGroups,

  • agregar un segmento para filtrar los datos de un cliente, utilizando la colección de objetos SlicerCaches.

La hoja de cálculo, creada mediante código VBA, se visualiza del siguiente modo: 

images/05RITEXCV03.png

Para ejecutar este ejemplo, realice las siguientes operaciones:

 Abra el libro Informe de ventas.xslx proporcionado con los ejemplos.

 Guarde el libro como Informe de ventas.xlsm.

 Asigne el nombre Ventas_2021 a todas las celdas de la hoja "Datos origen" (rango "A1:G278").

 Inserte el código del siguiente procedimiento en un nuevo módulo.

 Ejecute el código.

Sub Tabla_Dinamica() 
Dim wSheet As Worksheet 
Dim i As Integer 
Dim NumLineas As Integer  
  
   '   Selecciona el rango (origen de datos) 
   Application.Goto Reference:="Ventas_2021" 
  
   '   Elimina la hoja TD_Productos si existe 
   For i = 1 To Worksheets.Count 
      If Worksheets(i).Name = "TD_Productos" Then 
         Application.DisplayAlerts = False 
         Worksheets(i).Delete 
         Application.DisplayAlerts = True ...