Gráfico de columnas con formato dinámico

sábado, mayo 12, 2012

Al presentar datos en forma gráfica suele surgir la necesidad de resaltar uno o varios puntos de una serie.

Supongamos esta situación: presentamos las ventas de un año y queremos resaltar los mese en los que las ventas han caído por debajo de un cierto límite.



En el gráfico del ejemplo podemos ver con facilidad en qué meses las ventas han caído por debajo de límite establecido. Si se trata de un gráfico estático, donde los datos no cambian, nos basta con dar formato a los puntos de la serie en cuestión.

Pero si queremos mostrar distintos escenarios, cambiando el valor del límite por ejemplo, tenemos que convertir nuestro gráfico en dinámico. Es decir, que el color de los puntos de la serie cambie de acuerdo a la relación al valor del límite.

El principio básico para lograr este tipo de gráfico es separar los puntos de la serie (los meses) en dos series: por encima y por debajo del límite. Esto lo haceos creando un tabla auxiliar en una rango oculto



Los valores de la columna B (sobre el límite) los calculamos con la fórmula:

=SI(F5>=$F$2,F5,NOD())

Los valores de la columna C (debajo del límite) con:

=SI(F5<$F$2,F5,NOD()) 

Al representar vemos dos detalles “indeseables” en el gráfico:


  1. El formato numérico del eje de las Y 
  2. Las columnas no están ordenadas en forma simétrica. 



El primer detalle lo corregimos usando formato personalizado para los valores del eje



Este formato presenta los valores por miles: ###,###, "M";(###,###,)" M";0

El segundo problema se debe a que estamos representando dos series de datos en el gráfico de manera que para cada mes hay dos valores. Este problema lo solucionamos asignando una de las series al eje Y secundario



Al hacerlo so nos presenta un tercer problema: las escalas de los ejes Y no coinciden!



Podemos corregir esto manualmente con el menú de formato del eje. Pero al volver a cambiar el valor del límite o cualquier otro dato, no enfrentaremos con el mismo problema. Una solución es fijar los valores de ambos ejes. Una solución más general es programar un evento que corrija el valor del eje secundario de manera que siempre coincida con el primario.

Empezamos por definir un nombre que se refiera al rango de los datos (rngDatos en nuestro ejemplo se refiere a la celda que contiene el límite y a las celdas con los valores de las ventas).



También cambiamos el nombre por defecto del objeto gráfico (Gráfico 1) usando el Panel de Selección



En el módulo Vba de la hoja ponemos este código


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngActCell As Range

    Application.ScreenUpdating = False
   
    If Union(Target, Range("rngVentas")).Address = Range("rngVentas").Address Then
   
    Set rngActCell = ActiveCell
   
     ChartObjects("grfVentas").Activate
        ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = _
                ActiveChart.Axes(xlValue, xlPrimary).MaximumScale
   
    rngActCell.Select
   
    End If
   
    Application.ScreenUpdating = True
   
End Sub





Un último paso es ocultar el eje secundario



El archivo del ejemplo se puede descargar aquí.

5 comments:

acangros 12 mayo, 2012 15:49  

Muy interesante. Dos dudas, hay alguna forma de tocar el segundo eje sin pasar por macros?

No acabo de ver este formato: ###,###, "M";(###,###,)" M";0

¿Puedes detallarlo un poco?, por favor

Gracias

Jorge L. Dunkelman 13 mayo, 2012 06:16  

Si, como menciono en la nota, se puede determinar el máximo y el mínimo del eje con el menú de formato del eje (fijate en la última imagen en la nota).
En cuanto al formato numérico personalizado puedes ver esta nota y también esta otra.

Lennon 13 mayo, 2012 08:49  

Que excelente, pero a la vez que simple.

Leí el titulo y dije "qué util, esto me va a servir", pero leí el post y dije "esto también se me habría ocurrido", pero claro, es facil decirlo.

Un abrazo, gracias por tu ayuda

Francisco Mira 11 junio, 2012 14:44  

Antes que nada, felicitarte por este estupendo post.

He estado probando el archivo y me falla cuando ningún mes alcanza el límite inferior. Supongo que es porque la escala del eje principal la toma automáticamente de los valores de los meses (que son los que salen en la gráfica).

También tengo una duda, ¿no se obtendría lo mismo superponiendo las series un 100%, en vez de usar un eje secundario?

Jorge L. Dunkelman 17 junio, 2012 19:54  

Efectivamente, se obtiene lo mismo usando columnas apiladas en lugar de agrupadas. Buena observación.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP