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

Ejemplo de tabla dinámica y gráfico generado

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:

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 ...