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:
- El formato numérico del eje de las Y
- 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í.
Muy interesante. Dos dudas, hay alguna forma de tocar el segundo eje sin pasar por macros?
ResponderBorrarNo acabo de ver este formato: ###,###, "M";(###,###,)" M";0
¿Puedes detallarlo un poco?, por favor
Gracias
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).
ResponderBorrarEn cuanto al formato numérico personalizado puedes ver esta nota y también esta otra.
Que excelente, pero a la vez que simple.
ResponderBorrarLeí 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
Antes que nada, felicitarte por este estupendo post.
ResponderBorrarHe 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?
Efectivamente, se obtiene lo mismo usando columnas apiladas en lugar de agrupadas. Buena observación.
ResponderBorrar