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

Importar información desde Internet (Web Queries)

Internet es una fuente de información y para sacar ventaja de esa información y poder utilizarla en nuestros archivos de Excel explicare la herramienta Web Queries que nos permite importar información de una página web directamente a nuestro archivo de Excel.


Veamos como realizar la importación


Para importar datos debemos seleccionar la opción Desde Web de la ficha Datos. Luego se debe ingresar la dirección de la página que contiene los datos que queremos importar.


Solamente podrán ser importados datos que se encuentren dentro de una tabla (HTML ‹table›). En la pantalla de importación se deberá seleccionar que parte de la página o etiqueta ‹table› se desea importar. Para ello se debe hacer clic en los íconos que figuran en la página.


Una vez completado estos paso se puede refrescar la conexión y se actualizaran los datos que hayan cambiado en la tabla de la página web.

La principal ventaja es cuando tenemos información que se actualiza regularmente ya que Excel nos permite actualizar dicha información desde Excel simplemente haciendo clic en el botón Actualizar todo en la sección Conexiones de la ficha Datos.


Veamos un ejemplo donde importamos información que se actualiza regularmente


En este ejemplo importaremos la información de tasas de cambio, que son actualizadas diariamente.

En primer lugar creamos la conexión con la siguiente URL : http://www.oanda.com/lang/es/currency/historical-rates?lang=es&result=1&date1=01.05.10&date=30.06.10&date_fmt=normal
&exch=USD&exch2=&expr=ARS&expr2=&margin_fixed=0
&format=ASCII&SUBMIT=Obtener+Tabla

Si queremos que nuestros datos se actualicen automáticamente pondremos una fecha a futuro con lo cual sin cambiar la conexión se actualizarán datos nuevos.

Finalmente aplicamos unas fórmulas para separar los datos que se descargan en una sola columna para poder utilizarlos en Excel.

Para acceder a los archivos de ejemplos haga clic aquí.

Dentro de un entorno corporativo esta herramienta nos permitirá importar en Excel información que se encuentre en otra aplicación web o intranet.

En una futura nota mostraré como utilizar parámetros y macros con las conexiones para poder modificar el contenido que se desea descargar.

Acceder los elementos de una tabla mediante fórmulas

Si bien podemos considerar que todas las hojas de Excel son una tabla, en Excel 2007 se introdujo una nueva herramienta llamada "Tablas". Esta herramienta permite darle un nombre a un rango determinado de celdas y poder acceder a los elementos de la tabla mediante una referencias, y no el nombre de la celda donde está el dato, que pueden usarse en formulas.

Las principales ventajas es que las referencias son más fáciles de usar que los nombres de las celdas ya que pueden tener el nombre que el usuario desee. Funciona de forma similar a la definición de un nombre para un rango.

Se puede cambiar el tamaño de la tabla o mover la tabla y esto ajustara todas las referencias a la tabla sin tener que cambiar las formulas una por una.

Si alguna de las columnas de la tabla tiene una fórmula esta se copiará automáticamente al resto de las líneas.

Las referencias a una tabla se componen de la siguiente forma : =NombreTabla[NombreColumna]

También se pueden acceder distintas partes de la tabla utilizando la siguiente notación

• =NombreTabla[#Todas] Para acceder a la tabla completa.


• =NombreTabla[#Datos] El rango de todos los datos, sin encabezados ni totales.


• =NombreTabla[#Encabezados] Solamente incluye la primer línea de encabezados.


• =NombreTabla[#Totales] Solamente incluye la última línea.

Veamos algunos ejemplos.


Utilizaremos una tabla de datos como la siguiente


Si todavía los datos no fueron convertidos a una tabla se debe seleccionar la opción tabla de la ficha Insertar. Luego se debe definir el nombre tblVentas. El nombre se puede cambiar seleccionado la ficha Herramientas de tabla (solo aparece cuando la celda activa es parte de la tabla) y completar el campo nombre de tabla.


Utilizando fórmulas similares a las explicadas en la nota de sumas condicionales con mas de un criterio, utilizaremos referencias a las tablas para obtener los resultados.

Si deseamos obtener la cantidad de productos para un determinado país podemos usar la fórmula =SUMAR.SI(TblVentas[Pais];"Argentina";TblVentas[Cantidad]).

Para obtener la cantidad de un producto determinado y en un solo país podemos usar la fórmula =SUMAR.SI.CONJUNTO(TblVentas[Cantidad];TblVentas[Pais];"Argentina";TblVentas[Producto];"Manzana").

También podemos combinar otras fórmulas de Excel y por ejemplo obtener un promedio de unidades =PROMEDIO(TblVentas[Cantidad]) o el precio promedio de todas las ventas =TblVentas[[#Totals];[Importe]]/TblVentas[[#Totals];[Cantidad]].

Para acceder al archivo del ejemplo pueden hacer clic aquí.

Definición de nombres en Excel

Cuando hacemos referencia a una celda usualmente utilizamos la nomenclatura A1, A2, A3, etc donde la primera letra corresponde a la columna y el número corresponde a la fila. Adicionalmente Excel posee la función para otorgarle nombres definidos por el usuario a una celda o a un rango de celdas.

Esta herramienta se encuentra en la ficha Formulas, en la opción definir nombres dentro de la sección Nombres definidos.


Una vez seleccionada esa opción aparecerá un cuadro de dialogo para definir el nombre, donde ingresamos el nombre del rango y la celda o celdas a las que hace referencia.



A continuación veremos algunos ejemplos sencillos sobre como definir fórmulas utilizando nombres definidos por el usuario y cuales son sus ventajas.

Si tenemos una lista de precios y deseamos devolver el precio tomando como parámetro el código podemos definir el rango A1:B5 con el nombre Datos y utilizar la siguiente formula para la búsqueda =BUSCARV("Uva",Datos,2,Falso).


Si tenemos dicha fórmula en varios lugares en nuestra planilla y tenemos que cambiar el rango Datos o agrandarlo solo debemos hacerlo en un solo lugar y esto afectara a todas las formulas facilitando la administración y mantenimiento de dicha hoja de cálculos.

Otra aplicación es cuando tenemos un valor fijo que es aplicado en varias formulas en la planilla y para facilitar le lectura de la formula definimos un nombre para ese rango, si tenemos un nombre Descuento que tiene el valor 0.15 entonces podemos definir una formula =A1 * Descuento para obtener el resultado.


Cuando deseamos cambiar el valor del descuento solo modificamos el valor asignado al nombre y esto actualiza todas las fórmulas sin tener que hacerlo una por una.

Evaluar formulas en Excel

Cuando tenemos formulas complejas y no funcionan como necesitamos es difícil encontrar el problema. Para ayudarnos con esta tarea Excel posee una herramienta Evaluar Fórmulas que nos permite seguir paso a paso cada uno de los cálculos que Excel realiza hasta llegar al valor final.

Esta herramienta es de especial importancia cuando tenemos funciones con matrices o arrays ya que en éstas Excel realiza muchos cálculos que están incluidos en una sola fórmula.

En Excel 2007 la herramienta se encuentra en la ficha fórmulas dentro del grupo Auditoría de fórmulas


En Excel 2003 la herramienta se encuentra en el menú Herramientas, auditoría de fórmulas, Evaluar fórmulas.

Una vez seleccionada la herramienta podemos evaluar la fórmula paso a paso haciendo clic en el botón Evaluar.


Veamos un ejemplo


Utilizaremos la fórmula publicada en el articulo "Utilizando Matrices o Arrays en Excel" para sumar los 3 valores más grandes de una lista :

{=SUMA(K.ESIMO.MAYOR(A1:A10;FILA(INDIRECTO("1:3")))}

En el primer paso Excel resuelve la formula INDIRECTO("1:3") lo que nos devuelve la referencia para la siguiente fórmula


En el segundo paso Excel resuelve FILA("$1:$3") al ser una fórmula array esta se evalúa tres veces y su resultado es un array {1,2,3}


En el tercer paso Excel resuelve la fórmula K.ESIMO.MAYOR(A1:A10;{1,2,3}), nuevamente al tratarse de una formula array Excel la evalúa tres veces devolviendo el mayor, el segundo mayor y el tercer mayor en un nuevo array


En el último paso Excel resuelve la fórmula SUMA, sumando todos los valores devueltos en el array del paso anterior.