viernes, 8 de junio de 2012

VBA: Gráfico Variable en Excel 2010.

Hace bastante tiempo expliqué una manera de construir un gráfico en Excel vinculado al valor de ciertas celdas asociadas (ver), era una manera sencilla de obtener de un origen de datos aquellos valores que deseábamos representar gráficamente en nuestras Hojas de Excel.
Hoy explicaré una nueva forma de conseguir un Gráfico en Excel asociado a dos variables, obteniendo los Rangos de datos de Hojas diferentes, para poder responder la cuestión planteada por un lector del blog:

...Se trataria de crear un grafico en esta misma hoja en el cual pueda mostrar los datos respectivos al Año(D1) y Tipo(B1)conjuntamente.
Por ejemplo, yo selecciono 'Año 2010' y 'Vehiculos' y me mostraria un grafico de barras con los valores correspondientes a esos dos datos; el eje de x tendria como rotulos las celdas F1 a Z1 y el eje de y la escala correspondiente a los valores de F2 a Z2.
Si cambio la selección a otro año y otro concepto ( utilizaria el desplegable de D1 y B1) pues que me muestre esos nuevos valores....

Para ello, y para no complicar nuestro trabajo, comenzaremos con un Gráfico ya creado llamado 'GráficoFinal', y con dos celdas validadas tipo lista con los Años de estudio (celda D1)y los Tipos de datos (celda B1). También importante el nombre de cada Hoja con datos corresponde en nombre con '2012', '2013' y '2014', es decir, con los años a desplegar.

VBA: Gráfico Variable en Excel 2010.



Veamos por último la estructura de datos de cada una de las Hojas, muy importante para no complicar el código de nuestra macro siempre situadas en el mismo rango de celdas E1:Z4.
El código VBA de la siguiente macro se basa precisamente en la estructura de datos de esas hojas, ya que mediante una instrucción SELECT CASE obtendremos los valores de la Serie de datos a representar en función del Año y Tipo seleccionado:

VBA: Gráfico Variable en Excel 2010.



Insertaremos en un módulo del Explorador de proyectos del Editor de VBA el siguiente código, formando nuestra macro de Excel:

Sub GraficoVariable()
Dim valores, cabecera, fuente As Range
Dim rngvalores As Range
Dim Año As String

Application.ScreenUpdating = False
Año = Sheets("Gráfico").Range("D1").Value
valores = Sheets("Gráfico").Range("B1").Value

Set cabecera = Sheets(Año).Range("E1:Z1")
'Con SELECT CASE seleccionamos el Rango de Serie datos del gráfico
'según Tipo y Año
Select Case valores
    Case "Vehículo/100 habitantes": Set rngvalores = Sheets(Año).Range("E2:Z2")
    Case "Vehículos": Set rngvalores = Sheets(Año).Range("E3:Z3")
    Case "Habitantes": Set rngvalores = Sheets(Año).Range("E4:Z4")
End Select
'con UNION creamos un multirango, que servirá para crear el gráfico
Set fuente = Union(cabecera, rngvalores)

'Activamos el gráfico definitivo, y le asignamos los datos de Rótulos y Valores
'seleccionados mediante la Validación de datos de B1 y D1
ActiveSheet.ChartObjects("GráficoFinal").Activate
ActiveChart.SetSourceData Source:=fuente
ActiveChart.SeriesCollection(1).Name = valores & " Año " & Año

Application.ScreenUpdating = True
ActiveCell.Select

Set cabecera = Nothing
Set rngvalores = Nothing
Set fuente = Nothing
End Sub


Vemos como la macro de Excel que hemos creado, tras ejecutarla presionando el Botón (Control de formulario), obtiene los valores representados en nuestra 'GráficoFinal a partir de la selección del Año y Tipo, añadiendo como Serie de datos aquel rango de valores elegido, que era lo que queríamos obtener.

2 comentarios:

  1. Hola buenas tardes mi nombre es Jose, tengo una consulta. estoy trabajando en unos graficos en los que al establecer un rango de años me muestre la grafica correspondiente a cada año, es decir, tengo los costos asociados a cada mes para diferentes años y deseo representar en la grafica los costos mes a mes para los años 2010 2011 2012 2013 2014 etc etc , pero requiero que la grafica tenga la opción de poder mostrar un rango de años, es decir, 2011 al 2012 o 2010 al 2012 o 2012 al 2014 o 2010 al 2014 etc... yo emplee una función desref de excel para conseguir variar el rango sengun los años que establesca en mis cendas de inicio y fin =DESREF('Margen Brut Net- Cost Ope'!$C$5;COINCIDIR('GRAF-PROYECTOS'!INICIO;'Margen Brut Net- Cost Ope'!$B$6:$B$12;0);0;COINCIDIR('GRAF-PROYECTOS'!FIN;'Margen Brut Net- Cost Ope'!$B$6:$B$12;0);12)pero al hacer el llamado en la grafica sencillamente me da error pq esa función contempla una matriz y no una fila... lo que pretendo es que la grafica pueda incluir y exxluir series en función del rango de fecha. agradeceria la ayuda que me pueda prestar. Mi correo es joemart_30@hotmail.com.

    ResponderEliminar
    Respuestas
    1. Hola José,
      es complicado trabajar con gráficos, sobre todo en Excel 2007/2010, cuando hay que aplicar nombres definidos.
      Probablemente lo más sencillo sería trabajar con macros, combinando lo explicado en
      http://excelforo.blogspot.com.es/2012/08/vba-mostrarocultar-series-de-un-grafico.html
      para mostrar las series que necesites, y lo explicado en esta misma entrada para mostrar ciertos elementos (en tu propuesta meses).

      Otra opción mucho más sencilla, pero habría que ver la distribución de datos, sería aplicar un gráfico dinámico...
      Subiré un post en próximas fechas explicando esta última opción..
      Slds

      Eliminar