por 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í):
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:
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
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
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 = FalseDe una forma o de otra te funcionará sin problema.
Saludos!!
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.
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.
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
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
Hola Luis.
Puedes enviarme la Tabla ( o un ejemplo). Con el gráfico generado y le echo un vistazo en cuanto tenga un momento.
excelsignum@yahoo.es
Saludos
Hola te felicito por el excelente trabajo! Tal vez me puedas ayudar, he buscado por internet pero no he encontrado nada con respecto a lo que te plantearé. Resulta que tengo distintas hojas de excel y en cada hoja tengo una tabla con datos y a su vez una tabla con los datos de desviacion estandar, estos últimos valores los deseo utilizar con las «barras de errores» que te permiten los gráficos. Sin embargo, son muchas hojas (más de 100) y por tanto he decidido hacer una macro para generación de los gráficos, pero esta no me reconoce los valores de desviacion estandar que están en cada hoja y me generar un error en el código. Espero puedas ayudarme, de antemano muchas gracias
Hola Christian:
Necesitaría que me enviases un archivo con lo que deseas realizar y una breve explicación. Así será más sencillo que te pueda responder.
Saludos.
Hola tengo una duda
hoja_a = ActiveSheet.Name
Final = Application.CountA(Worksheets(hoja_a).Range(«A:A»))
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
«hoja_a!R2C2:R» & Final & «C16″, Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:=»Resultado!R3C1″, TableName:=»Tabla Dinamica2», _
DefaultVersion:=xlPivotTableVersion10
Donde se coloca si tengo una tabla y si quiero tomar como referencia mi variable hoja_a ya que hice un formato que se va a repetir y por cada formato va a hacer el resultado
Hola Jaquelin:
La variable debe ser colocada en la fuente de datos:
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
hoja_a & «!R1C1:R» & final & «C7″, Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:=»RESULTADO!R3C1″, TableName:=»Tabla dinámica1», _
DefaultVersion:=xlPivotTableVersion10
Tendrás que incluir un loop en la macro para que la variable vaya tomando diferentes valores.
SAludos.
hola es posible agregar otro grafico mas en la misma hoja de la misma base? por ejemplo de clasificacion por estudios? en que parte puedo incorporar mas?
Sí solo tienes que replicar el código cambiar el destino: TableDestination:=»RESULTADO!R3C1″ en el que quieras incorporar un nuevo gráfico.
Saludos
ActiveChart.ChartArea.Left = Sheets(«RESULTADO»).Cells(3, 4).Left ha sido de utilidad es lo que buscaba para situar correctamente mi grafico.
Gracias.
Muchas gracias Ana. Saludos.
No me sale el eje vertical y creo que es porque le he cambiado el estilo del gráfico y al hacerlo tengo que añadir algo más. Solo he añadido esto: ActiveChart.ChartStyle = 228
Ya que estamos tampoco se como añadir una segmentación de datos
Te he enviado un archivo con el ejemplo que necesitas. Saludos
Hola, quisiera hacerte una consulta. Estoy usando Excel 2003, tengo un libro con varias hojas con series de datos, no en tablas. Estos datos son pruebas que hago a distintos elementos ( corriente v/s tiempo). hice un formulario donde ingreso estos datos y los comparo con los registros en las hojas, para determinar si están dentro de tolerancia. Para ello hago uso de gráficos (2) dentro de cada hoja. Son del tipo dispersión, creados utilizando insertar gráfico del excel. Necesito agregar al gráfico activo un punto, cuya base de datos está radicada en los textbox. He logrado parcialmente este objetivo un código en VBA para insertarlo como una serie de dos datos (X = corriente e Y= tiempo), por ejemplo corriente 100 y tiempo 50. logro colocar un punto en esa posición, pero me provoca efectos secundarios indeseados tales como; generar un punto adicional en una posición inexistente o borrar una serie sin haberlo programado.
Mi pregunta, hay algún código VBA que me permita realizar esta acción.
Agradecido por tu atención
Hola Manuel, tendría que ver el ejemplo concreto para poder valorar si existe una solución y poder ayudarte.
Saludos
Hola, estimado, como seria hacer un grafico directamente de una tabla de registros (sin tabla dinámica), La tabla de registros es dinámica porque puede tener mas o menos filas dependiendo de la actividad del día. Por supuesto con VBA
Es mucho más sencillo:
En la hoja4 crear una tabla, la variable final cuenta los registros que actualizan en rango.
Sub Macro4()final = Application.CountA(Worksheets("Hoja4").Range("A:A"))
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Hoja4!$A$1:$B$" & final)
End Sub