miércoles, enero 22, 2014

Gráficos cascada (Waterfall) con valores negativos

Hace poco más de cuatro años atrás publiqué este post sobre cómo crear gráficos de tipo "Waterfall" ("Cascada" o "Flying Bricks") con Excel.
Este tipo de gráficos permite ver el efecto acumulado de valores positivos y negativos en una secuencia. En el post mostramos este ejemplo, partiendo de los resultados anuales de una cadena de tiendas

tabla de datos
mostramos este gráfico para mostrar el aporte de cada tiena al cambio en el resultado anual de la tienda

gráfico de columnas apiladas

Este gráfico lo creamos usando el gráfico de columnas apiladas elaborando los datos con una tabla auxiliar (las fórmulas y técnicas pueden verse en la nota del enlace, también podrán descargar el archivo del ejemplo).

Esta técnica tiene un inconveniente, como me lo hace notar uno de mis lectores. Los valores de los cambios no cruzan el eje horizontal. Para demostrarlo, supongamos que nuestra cadena de tiendas ha tenido un año 2013 terrible

cuadro de datos

Aplicando la técnica que usamos hasta ahora obtendríamos este gráfico


grafico
La columna ("ladrillo") tendría que aparecer cruzando el eje horizontal, entrando en la zona de los números negativos:

grafico


Para solucionar este problema tendremos que cambiarlas fórmulas en la tabla auxiliar. Esta es la tabla del modelo sencillo










Esta es la nueva tabla auxiliar sobre la cual construimos el gráfico









A diferencia del modelo sencillo, calculamos dos columnas para los valores positivos (Positivo +; Positivo -) y dos columnas para los valores negativos (Negativo +; Negativo -). "Positivo +" y "Negativo +" para los valores que se mostrarán por encima del eje horizontal; "Positivo -" y "Negativo -" para los valores que aparecerán por debajo del eje de la X.

Las fórmulas de las columnas G y H son obvias.

La fórmula de la columna I (Acumulado) es:

=MAX(0,MIN(SUMA(G$3:G3),SUMA(G$3:G4)))+MIN(0,MAX(SUMA(G$3:G3),SUMA(G$3:G4)))

Esta fórmula crea la parte "invisible" de la columna.

La fórmula en la columna J (Positivo +): =MAX(0,MIN(SUMA(G$3:G4),G4))

La fórmula en la columna K (Positivo -): =-MAX(0,G4-J4) (prestar atención al "-")

La fórmula en la columna L (Negativo +): =MAX(0,M4-G4)

La fórmula en la columna M (Negativo -): =MIN(0,MAX(SUMA(G$3:G4),G4))

Para crear el gráfico seleccionamos primero el rango F2:F8 y manteniendo el botón Ctrl apretado seleccionamos el rango H2:N8. Seleccionamos el gráfico de columnas apiladas
















El último paso es aplicar los formatos necesarios.

El archivo se puede descargar aquí.

5 comentarios:

  1. Muchas gracias Jorge, con este formato de tabla, funciona bajo todas las variables.
    Me ha sido de gran ayuda. Un saludo!!

    ResponderBorrar
  2. Muchas gracias por compartir esta interesante solución.
    Saludos desde Argentina

    JJ

    ResponderBorrar
  3. Muchas gracias por la aportación, tenía hecho algo parecidio pero este está mejorado y contempla todos los casos.
    Saludos desde Bilbao.

    ResponderBorrar
  4. Hola. Excelente el dato. Sabe como hacerlo con apertura para cada barra. Por ejemplo, que cada sucursal tengo colores divididos para definir las 3 áreas más importantes de cada una?

    ResponderBorrar
  5. Tal vez se podr'ia hacer, pero mi sugerencia es que hagas un gráfico para cada sucursal.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.