Gráfico de columnas con formato dinámico

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í.

Uso de tablas en hojas protegidas

No es necesario que nos extendamos sobre las bondades del uso de Tablas (Listas en Excel 2003). Sin embargo existe un inconveniente que aún no ha sido tratado por Microsoft. En una hoja protegida las tablas dejan de expandirse automáticamente.

En general usamos tablas para introducir datos en una base de datos plana y que los objetos que hayamos creado a partir de la tabla (gráficos por ejemplo) se adapten automáticamente y/o para evitar la necesidad de copiar fórmulas a lo largo de una columna cuando agregamos filas. En este tipo de situaciones no existe una necesidad real de proteger la hoja. La necesidad puede surgir si tenemos una o más columnas en la tabla con fórmulas y queremos evitar que el usuario las pueda modificar o queremos ocultarlas.

Si bien el menú de protección incluye las posibilidades Insertar Columnas e Insertar Filas,

la tabla dejará de agregarlas automáticamente en una hoja protegida.

Una solución posible es agregar filas en la tabla de antemano. Esto es relativamente razonable si el modelo tiene lógicamente un número definido de filas (por ejemplo, comparación de ventas-plan por mes de un año determinado).

Una solución más dinámica es usar una macro, más precisamente un evento. Supongamos este modelo

El rango A1:E8 lo hemos definido como tabla y debe expandirse automáticamente a medida que agregamos datos. La tabla H1:I6 contiene el plan de ventas mensual de las sucursales y la usamos en la fórmula de la columna D de la tabla

=BUSCARV([@Sucursal],tblPlanMensual,2,0)

Para que la tabla se autoexpanda, también en una hoja protegida, abrimos el módulo del editor Vba de la hoja (clic con el botón derecho a la pestaña de la hoja)

y ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    With ActiveSheet
        .Unprotect
        .ListObjects("Tabla1").Resize Target.CurrentRegion
        .Protect
    End With
 
End Sub

Explicación del código:
Las tres primeras líneas del código interrumpen el evento si:

“If Target.Count > then Exit Sub” – el rango seleccionado incluye más de una celda.

“If Target.Row = 1 Then Exit Sub” - si la fila es 1

“If Target.Column <> 1 Then Exit Sub” – si la columna no es A.

El resto del código quita la protección (.Unprotect), expande el rango de la tabla (.Resize Target.CurrentRegion) y vuelve a proteger la hoja.

“Tabla1” es el nombre de la tabla, visible en el menú Tabla cuando activamos alguna de sus celdas

Es una buena práctica cambiar el nombre por defecto (Tabla1) por un nombre más significativo. El cuaderno con el código puede descargarse aquí.

Formato condicional para resaltar máximos y mínimos según criterios

Hace casi un mes que no publico nada en el blog. No por pereza o desidia sino por una inesperada carga de trabajo. Cuento con la comprensión de los lectores que han quedado sin respuesta a sus consultas.

Uno de mis lectores me consulta cómo usar formato condicional para resaltar mínimos (o máximos). Supongamos una lista de precios donde varios productos aparecen con precios distintos

Cómo extraer los máximos y mínimos según criterios (producto, en nuestro caso), ya hemos visto en la nota que publiqué en abril de año pasado. En nuestro ejemplo la solución sería

Las fórmulas para calcular los máximos y mínimos son matriciales (se introducen apretando simultáneamente las teclas Ctrl-Mayúsculas-Enter):

• para los máximos: =MAX(($A$2:$A$13=D2)*$B$2:$B$13)

• para los mínimos: =MIN(SI(($A$2:$A$13=D2),$B$2:$B$13))

Podemos usar las mismas fórmulas para aplicar el formato condicional. Por ejemplo, para resaltar los mínimos en la lista de precios

En formato condicional no hace falta introducir la fórmula en forma matricial.