Mejoras para el gráfico de Cascada o Waterfall

En la nota anterior expliqué como crear un gráfico de cascada, ahora realizaremos algunos cambios que mejoraran ese gráfico. Si bien estos cambios también pueden lograrse mediante macros, en este artículo mostraré alternativas sin usar macros ya que en ciertos casos es necesario generar un archivo sin incluir macros.

Como en el ejemplo anterior utilizamos la siguiente tabla para armar nuestro grafico y le asignamos el nombre TablaChart.



Veamos los cambios


El primer cambio nos permitirá asignarle distintos colores a las variaciones, en el caso que sean positivas o negativas. Para lograr esto separamos los valores positivos y negativos en dos columnas que serán incluidas como dos series distintas de datos y por lo tanto podremos asignarle distintos colores.

La columna que tiene los valores positivos contiene la siguiente fórmula =SI(B2>0;B2;0). En el caso de ser un valor negativo entonces mostramos un cero y por lo tanto esto no queda visible en el gráfico. A esta columna la llamaremos Series2Mas.

Por otra parte la columna que tiene los valores negativos contiene la siguiente fórmula =SI(B2<0;ABS(B2);0). Similar al caso anterior, si la variación es positiva se mostrará cero y sino el valor absoluto. A esta columna la llamaremos Series2Menos.


El otro cambio es la utilización de las tablas de Excel 2007 para que podamos ingresar nuevas filas según la necesidad y el gráfico se actualice automáticamente. Para lograr que funcione correctamente la tabla y se actualicen las fórmulas debemos utilizar una misma fórmula para cada fila y que ésta haga referencia a la misma fila.

En el ejemplo anterior las fórmulas de la columna Series1 eran diferentes para la primera y última fila ya que siempre llevan el valor cero. Lo primero que debemos lograr es una fórmula que devuelva 0 solo para la primera y última fila de la tabla. Para saber si estoy en la primera fila utilizamos FILA(TablaChart) y si es igual a FILA(), que devuelve la fila actual, entonces estamos en la primer fila. Para calcular la última fila FILA(TablaChart)+FILAS(TablaChart)-1, de la misma forma chequeamos si esta es igual a FILA(). La fórmula completa sería =SI(O(FILA(TablaChart)=FILA();FILA(TablaChart)+FILAS(TablaChart)-1 = FILA());0;B2).

Finalmente la fórmula utilizada para acumular los valores, en el ejemplo anterior llamada Ultimo tope, tampoco funciona correctamente al insertar nuevas filas en una tabla y por ello lo reemplazamos por =SUMA($B$2:B2), la referencia absoluta fija la primer fila mientras que la segunda se incrementa a medida que se incrementan las filas en la tabla.

Si integramos la fórmula para acumular con la explicada en el paso anterior obtenemos la siguiente fórmula =SI(O(FILA(TablaChart)=FILA();FILA(TablaChart)+FILAS(TablaChart)-1 = FILA());0; SI(B2<0;SUMA($B$2:B2);SUMA(B$2:B2)-B2))


Para acceder al archivo del ejemplo haga clic aquí.

Crear un gráfico de Cascada o Waterfall

Excel posee muchos tipos de gráficos predeterminados (ver tipos aquí) y por ello es una herramienta muy utilizada para la visualización de datos. Un gráfico bastante utilizado que no está incluido en el paquete estándar, es el gráfico de Cascada. Este gráfico es muy conveniente para explicar la variación entre un valor inicial y un valor final.

Un buen ejemplo para el gráfico de cascada es cuando tenemos un valor inicial y otro final como por ejemplo el número de unidades vendidas el mes anterior y el del mes actual. Para explicar la variación entre estos dos valores se pueden definir varios conceptos positivos o negativos que llevan del valor inicial al final.

En este artículo explicaré como podemos crear un gráfico de Cascada utilizando uno de los tipos de gráficos incluidos con Excel.


Veamos el ejemplo


Tenemos una lista con dos valores uno inicial y otro final y cinco variaciones que explican la diferencia entre los dos valores.


Tomaremos como base de nuestro gráfico el tipo de gráfico de barras apiladas incluido en Excel.


Para realizar el gráfico de cascada necesitaremos agregar algunas columnas auxiliares que serán utilizadas en nuestro gráfico. La columna Series1 muestra el valor que se encuentra entre el valor 0 y el comienzo de la variación. La columna Series2 muestra las variaciones y como nuestro gráfico muestra solo valores positivos esta columna es el valor absoluto de la variación. La última columna Último Tope calcula el valor total de las dos columnas anteriores, esta es utilizada para graficar la siguiente variación.


Las formulas incluidas en cada columna son:

Para la columna Series1 utilizamos =SI(B3<0;E2+B3;E2). Aquí tenemos que tomar en cuenta dos condiciones, una si la variación es negativa y la otra si es positiva. En el caso de ser negativa tomamos el último tope, calculado en la fila anterior y le sumamos la variación (como esta es negativa es una resta). Esto nos dará el punto de partida de la variación. Si la variación es positiva solamente tomamos el último tope.

Para la columna Series2 utilizamos =ABS(B3) para obtener el valor absoluto. Esta columna es necesaria ya que no queremos graficar las variaciones negativas debajo del eje x sino que solo queremos reducir el valor sobre el cual graficamos la variación (este cálculo lo realizamos en la columna Series1).

Para la columna Último Tope utilizamos =E2+B3. Esta fórmula calcula el valor sobre el cual termina nuestra columna en el gráfico y se realiza en base al último valor más la variación.

Luego graficamos las columnas Series1 y Series2 y obtendremos un gráfico similar al siguiente.


El último paso es hacer las columnas de la serie 1 transparentes para lograr el efecto del gráfico de cascada. Para ello seleccionamos el gráfico y luego la ficha Formato del grupo Herramientas de Gráficos. Seleccionamos la Serie 1 de la lista desplegable Selección actual y finalmente seleccionamos Sin relleno de la lista desplegable.




Para acceder al archivo del ejemplo haga clic aquí.