GENERAR GRÁFICO DINÁMICO CON VBA

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

 

Anuncios

7 pensamientos en “GENERAR GRÁFICO DINÁMICO CON VBA

  1. Hola, excelente aporte el que haces en esta página, estoy tratando de adaptar esta macro para mi tabla pero me arroja error en el rango para crear la tabla, no entiendo porque colocas el “C7” en el origen de datos? te agradecería bastante me aclararas esa duda, mi origen de datos va de A1 hasta V22000, no se que debería ingresar en lugar de “C7” (si es que debo ingresar otra referencia?)

    Muchas muchas gracias

    Will

    Me gusta

    • Hola William,

      Fíjate en la fórmula: "DATOS!R1C1:R1" & final & "C7" en especial en R1C1: que es en realizadad la notación de estilo R1C1 donde nos indica las filas “R” y las columnas “C”, en mi ejemplo es de la fila 1 hasta la columna 7 eso es C7. El rango de mis datos van desde la columna 1 (A) hasta la columna G (7), o lo que es lo mismo, C7.

      En tu caso vas desde la columna 1: “C1” hasta la 22: “C22”. Así que en la fuente de datos has de poner: "DATOS!R1C1:R1" & final & "C22"

      Espero haber contestado a tu pregunta.

      *** Por otra parte si a tus datos le has dado formato de tabla, puedes sustituir lo anterior por:
      SourceData:="Tabla1" (o el nombre que le hayas asignado a la tabla) y borrar o comentar la siguiente línea del código, dado que no tendrás campos en blanco:

      .PivotItems("(blank)").Visible = False

      De una forma o de otra te funcionará sin problema.

      Saludos!!

      Me gusta

      • Si señor, funcionó perfecto, claro, yo estaba tomando “C7” como si fuera la celda y no la Columna 7, muchas gracias por tu ayuda y nuevamente excelente aporte.

        Me gusta

  2. Hola, Muy buen aporte!
    Una consulta, en mi caso me sale un error: “run-time error ‘5’ invalid procedure call or argument”, señalando al siguiente código
    :
    ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    “BD-2016!R1C1:R1” & Final & “C13″, Version:=xlPivotTableVersion10).CreatePivotTable _
    TableDestination:=”Gráfico 01!R3C1″, TableName:=”Tabla dinámica1”, _
    DefaultVersion:=xlPivotTableVersion10

    Gracias por la atención.

    Saludos.

    Me gusta

    • Hola Omar:

      El problema es el nombre que le has dado a la hoja, “gráfico 01”, vba está leyendo “gráfico 1” no reconoce el cero, por lo tanto: Sustituye gráfico 01 por gráfico_01. Siempre que trabajes con este tipo de nombres utiliza el guión bajo, así lo conviertes en una cadena.

      Por lo demás debería funcionarte correctamente.

      Saludos

      Me gusta

  3. Hola, excelente aporte, solo que lo quiero modificar a una tabla dinamica ya existente pero no logro hacer la adaptación !!

    Me puedes ayudar

    Gracias

    Me gusta

¿Te ha gustado?. Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s