Grafico de presupuesto con formato condicional

Esta es una variación del tema de la nota sobre gráficos de columnas con formato condicional.
Uno de mis lectores pregunta cómo dar formato condicional a un gráfico que muestra valores actuales comparados con un plan. Queremos un color para "cumplido", otro para "no cumplido" y un tercer color para "superado".

El gráfico que queremos construir será del tipo "termómetro".

Como en el caso anterior, construimos tres series de datos usando fórmulas. De esta manera podemos dar un color a cada serie, según el caso: verde para cumplido, rojo para no cumplido y azul para superado.

Construimos una tabla similar a la de la nota anterior, pero agregamos una columna para los valores de "Plan"

La otra diferencia reside en la fórmula que empleamos para crear las distintas series de datos. La fórmula es más sencilla y todo lo que hace es compara el valor de las ventas (valor actual) con el valor del plan

=SI($B2<$F2;$B2;NOD())

A partir de aquí construimos el gráfico usando algunos de los "trucos" que ya hemos mostrado en otras notas. Empezamos por seleccionar el rango de los datos

Como ven, el rango B1:B13 no forma parte del rango elegido. Elegimos el tipo de gráfico "columnas agrupadas" y pulsamos aceptar.



Ahora empezamos a reorganizar el gráfico. Primero cambiamos las definiciones de formato de Excel, que son bastante feas. Esto incluye, entre otras cosas, quitar la señal de Autoescala de los ejes y la leyenda; cambiar el formato de las líneas de división y quitar el fondo y los bordes del área de trazado



El próximo paso será cambiar el formato de la serie "Plan" y poner las otras series sobre un eje secundario (hasta este momento el gráfico tiene sólo un eje principal).

Seleccionamos una de las columnas de Plan y abrimos el menú de formato de series de datos



En la pestaña Ejes, comprobamos que la opción de eje esté en "eje principal" ; en Tramas, elegimos un color gris y efecto de relleno degradado



A esta altura del partido nuestro gráfico se ve así:



El próximo paso es sincronizar los ejes, para lo cual ponemos la misma escala en ambos



Ahora empezamos a ocuparnos de las otras series. Seleccionamos una de las series, por ejemplo "No cumplido" y abrimos el menú de formato de series de datos y en la pestaña Ejes señalamos la opción eje secundario. En Tramas elegimos el color adecuado (en este caso, rojo). En Opciones ponemos Superposición en 100 y Ancho del rango en 150



El resultado final será



Opcionalmente podemos eliminar el eje secundario



obteniendo un gráfico más "liviano"

Otro gráfico de presupuesto con imágenes

En la nota anterior vimos como impresionar a nuestro jefe representando un presupuesto con un gráfico que muestra un billete de 100 euros partido en pedazos, donde cada pedazo representa una porción del presupuesto



Pero sucede que a veces la suerte no nos acompaña y justo a nosotros nos vino a tocar ese tipo de jefe que siempre tiene lo que decir, al que nada le viene bien. En ese caso, su reacción será algo así como:

- si, muy lindo. Pero yo quiero que el gráfico esté orientado horizontalmente. Dónde vio que se muestre un billete verticalmente?

La técnica para crear el gráfico orientado horizontalmente es distinta y es lo que mostraré en esta nota. En este caso no utilizaremos Autoformas.

Partiendo de la misma tabla de datos, creamos esta vez un gráfico de barra % apilada, con las series en filas




Como en el caso anterior eliminamos todo tipo de objetos innecesarios que Excel pone por defecto en el gráfico, entre ellos las líneas de división, el título y el eje de la X. Nos quedará algo así



Ahora seleccionamos el área de trazado



y pegamos la imagen del billete de 100 euros (o cualquier otro billete que se nos ocurra) con la técnica que mostramos en la nota anterior



A partir de aquí empezamos con las manipulaciones. Seleccionamos una de las series y abrimos el menú de formato de series de datos en la pestaña Opciones



Llevamos el valor de Ancho del rango a 0



Como ven, las barras de las series tapan la imagen del billete. Para hacerlo visible y, al mismo tiempo, agregar las divisiones entre los pedazos del billete, seleccionamos una de las series y abrimos nuevamente el menú de formato de serie de datos. En la pestaña Tramas, y definimos Borde como Personalizado, con el grosor máximo y el color idéntico al del fondo del área de trazado



El resultado será



Damos el mismo formato al resto de las series seleccionándolas y aprestando F4



Todo los que no queda ahora es agregar otros detalles, como los rótulos de las series y esta vez sí impresionar a nuestro jefe



Por supuesto, esta misma técnica puede usarse para crear el gráfico vertical utilizando el tipo de columna 100% apilada.

Gráfico de presupuesto con Autoformas

En una de las primeras notas de este blog mostraba cómo se pueden usar Autoformas en gráficos de Excel.

En esta nota mostraré como se puede usar esa técnica para crear este gráfico



Como ven se trata de representar una presupuesto de viaje en este caso, "partiendo" un billete de 100 euros de manera que cada porción represente el porcentaje del gasto del total del presupuesto. Este es un buen truco para representar gráficamente el presupuesto de una empresa y distraer la atención del directorio de "la triste realidad" (en broma…).

Empezamos con nuestra tabla de datos



Con estos datos creamos un gráfico de línea 100% apilada



y asegurándonos que los datos estén en filas



Quitamos casi todos los elementos menos el eje de la Y y las series de datos



En una carpeta de nuestro computador (ordenador) tenemos una imagen de un billete de 100 euros. Esta imagen la pegamos al área de trazado. Esto lo hacemos seleccionando el área de trazado y en el menú de Formato vamos a la pestaña de Imagen. Allí presionamos el botón Seleccionar imagen y pegamos la imagen deseada




Ahora tendremos que "jugar" con el tamaño de gráfico



hasta obtener una imagen aceptable



El próximo paso será poner un fondo gris claro al área del gráfico (esto no es indispensable).

Hasta ahora no nos hemos ocupado de los marcadores de los puntos de las series (hay que notar que tenemos cinco series, cada una con un solo punto).



Creamos un rectángulo con Autoformas



Le quitamos las líneas y le damos el mismo color como el fondo del área de gráfico. También nos aseguramos que sea un poco más ancho que nuestro billete



Ahora, seleccionamos el rectángulo y lo copiamos (Ctrl+C). Seleccionamos uno de los marcadores de las series y pulsamos Ctrl+V. Así vamos seleccionando cada uno de los marcadores y volvemos a pegar el rectángulo. El resultado será



Para agregar los rótulos, seleccionamos el marcador de la serie y con el menú de Formato de punto de la serie marcamos Nombre de la serie en la pestaña Rótulos de datos



Aquí tendremos que hacer un poco de "trabajos manuales" para mover los rótulos a la derecha del gráfico. Para esto seleccionamos el rótulo con un clic, seguido de otro (no doble clic). Una vez seleccionado el cuadro de texto que contiene al rótulo, lo movemos a la posición deseada.




También podemos quitar la línea del eje Y y la marca de graduación principal.

Gráficos enlazados en Excel

Esta nota se originó en la consulta de uno de mis lectores:

tengo la necesidad de hacer una serie de gráficos de columnas para los cuales me gustaría utilizar la técnica conocida como "drill down" que consiste en por ejemplo al hacer doble click sobre la barra de un gráfico, por ejemplo gastos de marzo, me lleve a la gráfica detallada de cada uno de los gastos de ese mes en particular

No se que término utilizar en castellano para traducir "drill down", así que hasta que alguien sugiera algo más acertado usaremos gráficos enlazados.

Empecemos por describir nuestro modelo. Tenemos un cuaderno con seis hojas: Total, con dos tablas. En la superior resumimos las ventas de todo el año de las cuatro zonas de una empresa (Norte, Sur, Este y Oeste), y en la inferior tenemos el detalle de cada zona por mes.




A partir de las tablas hemos creado cinco gráficos que hemos puesto en distintas hojas.



Nuestro objetivo es que cuando hagamos clic a una de las columnas del gráfico Total, nos lleve al gráfico detallado de la zona correspondiente.

Esto la haremos usando eventos, tema que ya he tratado en mi blog sobre Excel.

Excel permite programar también eventos relacionados con gráficos. En nuestro caso usaremos el programaremos el evento Chart_MouseDown. Este evento es disparado cuando el Mouse está ubicado sobre un gráfico y un botón es presionado.

En el editor de Vba, seleccionamos la hoja Grafico Total y en la ventanilla izquierda del módulo elegimos Chart y en la ventanilla derecha el evento Mouse Down



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

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long


GetChartElement x, y, ElementID, Arg1, Arg2
If ElementID = xlSeries Then
Select Case Arg2
Case 1
Sheets("Grafico Norte").Activate
Case 2
Sheets("Grafico Sur").Activate
Case 3
Sheets("Grafico Este").Activate
Case 4
Sheets("Grafico Oeste").Activate
End Select
End If
End Sub

Lo que hace este código es determinar sobre qué elemento del gráfico se encuentra al Mouse al apretar el botón. De acuerdo a la ubicación, el código activa la hoja correspondiente.

He agregado dos eventos en cada una de las hojas con los gráficos detallados. Al activar la hoja se recibe un mensaje que nos informa que un doble clic nos llevará de vuelta al gráfico Total. El segundo evento nos lleva al gráfico Total al hacer doble clic.



Como verán, a lo largo de esta nota he considerado gráficos ubicados en hojas separadas. Para usar eventos en caso de gráficos que residen en hojas de cálculo de Excel, tenderemos que usar otra técnica basada en Class Modules. Pero esto será el tema de la próxima nota.