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

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

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

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

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

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

El extraño caso del cálculo manual persistente

Excel tiene una manera peculiar de manejar las opciones de cálculo. Estas pueden ser manual, automático o automático excepto en tablas de datos. En las Opciones de Excel, en Fórmulas podemos ver las posibilidades

Las opciones de cálculo son hereditarias (ya verán por qué) y siguen estas reglas:

• El primer documento abierto utiliza el modo de cálculo con la que se guardó. Documentos abiertos posteriormente utilizan el mismo modo.

• Al cambiar el modo de cálculo de un documento abierto se cambia el modo de todos los documentos abiertos.

• Si se cierran todos los documentos y crear un documento nuevo, el nuevo documento utiliza el mismo modo de cálculo como los documentos cerrados previamente.

• Si ha cambiado el modo de cálculo en un libro y se guarda el archivo, se guarda el modo de cálculo actual.


Si cerramos una sesión de Excel y el último cuaderno que guardamos estaba definido con cálculo automático, al abrir una nueva sesión esperamos que también el primer cuaderno que creamos esté definido con cálculo automático.

Sin embargo mi Excel insistía en abrir el primer cuaderno de cada sesión en estado de cálculo manual. Intenté todos los remedios conocidos (guardar un cuaderno en Automático y cerrar Excel, revisar si hay un WorkbookOpen event en algún Add-in, si el cuaderno en XLSTART está definido con cálculo manual). ¡Nada! Es más, no tengo ningún archivo en XLSTART, así que mi sesión se abre sin ningún "Libro1".

Al borde de la desesperación encontré el problema: de alguna manera que no logro entender, había guardado el cuaderno Personal.xlsb con la opción Manual.

Podemos solucionar este problema de dos maneras:

Por defecto, el cuaderno Personal está oculto. Este cuaderno tiene una hoja y para cambiar sus propiedades tenemos que volverlo visible

Una vez visible cambiamos la propiedad, guardamos y volvemos a ocultar.

Otra posibilidad, sin necesidad de mostrar el Personal, es abrir el editor de Vb (Alt+F11), nos aseguramos que el Personal sea el cuaderno activo y en la ventanilla Inmediato ponemos:

Workbooks("Personal.xlsb").Parent.Calculation = xlCalculationAutomatic: Workbooks("Personal.xlsb").Save

Como usar la calculadora del Windows en Excel

Aprovechando un momento de descanso en mi viaje de trabajo, publico este tip rápido. ¿Se acuerdan de la calculadora del Windows?

A pesar que Excel nos permite hacer todo cálculo que queramos, a veces es prático tener esta calculadora a nuestra disposición.

Para activar la calculadora no tenemos que salir de Excel, sino que podemos agregarla a la barra de acceso rápido.

Este video muestra el proceso

Tal vez parezca supérfluo agregarla a la barra de acceso rápido, pero la calculadora tiene muchas más funciones y usos de lo que aparenta.

Por ejemplo, si tenemos que realizar conversiones entre unidades (Ctrl+U)

O diferencia entre fechas (Ctrl+E)

También podemos convertirla en calculadora científica, de programadores, etc.

Una alternativa interesante son las funcionalidades que aparecen bajo el rótulo Worksheets

Por ejemplo, para calcular la cuota de un préstamo