Después de unas merecidas vacaciones toca volver con fuerza y con ganas. La verdad es que los aires del Atlántico me han revitalizado lo suficiente y el haber pasado unos días cerca de la familia siempre vienen bien.

Hoy además, para los que no os habéis dado cuenta, os comunico que Excel Signum ahora tiene dominio propio y su url ahora es: www.excelsignum.com. La decisión de realizar este cambio obedece a que el Blog afortunadamente tenía un volumen alto de visitas y comentarios y he creído oportuno dar un salto más, y “oficializar” la herramienta.

Para esta entrada, y siguiendo una consulta que en su momento ya había respondido, trabajaremos con gráficos dinámicos en VBA y algún que otro código interesante.

La consulta era la siguiente: “¿podría automatizar, a partir de una tabla de datos, que mediante un botón se generase un gráfico dinámico, especificando una situación concreta en la hoja (a la misma altura que la tabla dinámica) y que actualice todos los datos de la hoja fuente en caso que aumente la información?”.

Bien, la pregunta me resultó interesante por dos motivos:

– El programar en VBA la forma de situar el gráfico alineado a la misma altura que la tabla dinámica.
– Hacer que la tabla dinámica obtenga la referencia al rango de la hoja fuente incluyendo todos los registros incluso cuando estos varíen.

Para comenzar, es necesario confeccionar una tabla con los datos con los que queremos trabajar, en este caso, he tomado como referencia la plantilla de empleados de unos grandes almacenes. Para ello he tenido que echar mano de los datos del INE para componer los nombres de las personas (en la información de nacimientos, existen datos de nombres y apellidos que permiten componer esta información (ver aquí):

GENERAR GRAFICO DINAMICO_1

En la tabla tenemos los siguientes Campos: Id, Nombre Completo, Sección, Nacimiento, Sexo, 2º Idioma, Estudios. Estos datos están en la hoja (“Datos”) y que queremos generar un gráfico dinámico en la segunda pestaña, denominada (“Resultado”), donde nos muestre la información de empleados por Sección.

Para ello, incluiremos esta macro en un nuevo módulo:

Sub GENERAR()
'DEFINIMOS EL RANGO DE DATOS DE LA HOJA "DATOS"
final = Application.CountA(Worksheets("DATOS").Range("A:A"))
'---------------------------------------GRAFICO DINÁMICO--------------------------------------------
'INDICAMOS QUE LA FUENTE DE DATOS SIEMPRE VAN A SER EL RANGO COMPLETO DE LOS DATOS DE LA
'TABLA DE LA HOJA DATOS. Y COLOCAMOS LA TABLA DINÁMICA A PARTIR DE LA FILA3 COLUMNA1
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DATOS!R1C1:R" & final & "C7", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="RESULTADO!R3C1", TableName:="Tabla dinámica1", _
DefaultVersion:=xlPivotTableVersion10
'MOSTRAMOS LA INFORMACIÓN POR SECCIÓN EN LA TABLA DINÁMICA
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("SECCION")
.Orientation = xlRowField
.Position = 1
‘OCULTAMOS DATOS VACÍOS
End With
'AJUSTAMOS QUE LA TABLA DINÁMICA MUESTRE EL NÚMERO DE EMPLEADOS POR SECCIÓN.
ActiveSheet.PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica1").PivotFields("ID"), "Suma de ID", xlSum
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("Suma de ID")
.Caption = "Cuenta de ID"
.Function = xlCount
End With
'SE AGREGA UN NUEVO GRÁFICO
ActiveSheet.Shapes.AddChart.Select
'SE DETALLA EL TIPO DE GRÁFICO
ActiveChart.ChartType = xlColumnClustered
'SE SELECCIONA EL GRÁFICO Y SE LE DAMOS UN ANCHO (600)
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Width = 600
'POSICIONAMOS EL GRÁFICO EN LINEA CON LA FILA 3 Y COLUMNA 4 PARA QUE QUEDE SIEMPRE 'ALIENADO A LA TABLA DINÁMICA
ActiveChart.ChartArea.Left = Sheets("RESULTADO").Cells(3, 4).Left
ActiveChart.ChartArea.Top = Sheets("RESULTADO").Cells(3, 4).Top
'DETERMINAMOS QUE LOS DATOS DEL GRÁFICO SEAN DE SECCIÓN.
'AGREGAMOS VALORES A ETIQUETA DE DATOS
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
'DESACTIVAMOS MOSTRAR LISTA DE CAMPOS
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

Una vez aplicado este será el resultado:

GENERAR GRAFICO DINAMICO_2

En este código observamos que previamente a generar el gráfico debemos insertar una tabla dinámica, que será nuestra fuente de información para crear el gráfico. Para determinar que todos los datos de la tabla de la hoja (“Datos”) se incluyan automáticamente en la tabla dinámica, debemos definir en primer lugar el rango y lo hacemos como siempre, contando 🙂

final = Application.CountA(Worksheets("DATOS").Range("A:A"))

Ahora que sabemos en todo momento cuantas filas tenemos, podemos hacer referencia dentro de la tabla dinámica a este rango, y lo hacemos de esta forma, especificándolo en:

SourceData :=  "DATOS!R1C1:R" & final & "C7"

De esta forma ya estamos contestando a una de las dudas que nos planteaban.

Saltando ahora al código que define el gráfico, podemos determinar su situación con el siguiente procedimiento:

ActiveChart.ChartArea.Left = Sheets("RESULTADO").Cells(3, 4).Left
ActiveChart.ChartArea.Top = Sheets("RESULTADO").Cells(3, 4).Top

Así indicamos tanto vertical como horizontalmente dónde queremos situar el gráfico. Además de eso, vamos a aplicar ancho nuevo al gráfico para que muestre todas las secciones:

ActiveChart.ChartArea.Width = 600

Tal y como podemos ver, ya hemos contestado a parte de la pregunta que nos hacía el lector, pero queda un detalle importante, el que cada vez que se ejecute la macro, se borren todos los datos de la hoja (“Resultado”), incluyendo también el propio gráfico, de lo contrario se irían acumulando gráficos por cada ejecución.

Lo haremos incluyendo al principio de la macro anterior estas dos instrucciones, así limpiamos todos los datos incluido en la hoja (que no sean objetos insertados):

Sheets("RESULTADO").Select
Cells.Select
Selection.Clear

De esta forma limpiamos gráficos, donde mediante una condición  si hay algún gráfico en la hoja, lo borramos.

Sheets("RESULTADO").Select
If ActiveSheet.Shapes.Count > 0 Then
ActiveSheet.ChartObjects.Delete
End If

Y ahora ya tenemos automatizado completamente la generación y configuración de un gráfico dinámico a partir de una base de datos en otra hoja. Actualiza todos los datos a partir de una tabla base y permite crear nuevas consultas con solo introducir un nuevo campo en la siguiente línea de código:

With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("SECCION")

Por ejemplo cambiando el campo «Sección» por «Estudios». Y ahora os dejo el archivo de prueba para que examinéis directamente el código 🙂

Descarga el archivo de ejemplo pulsando en: GENERAR GRÁFICO DINÁMICO

 

Comparte este post