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.

Rangos Dinámicos

Excel nos permite trabajar con cantidades muy grandes de datos y muchas veces esta información va creciendo a medida que transcurre el tiempo. Al modificarse los rangos en donde se encuentran los datos se deben modificar las fórmulas que usan dichos datos para que incluyan la totalidad del rango.

Si tenemos un archivo con muchas fórmulas esto genera un trabajo tedioso y que puede generar resultados incorrectos. Para facilitar este proceso es posible definir nombres a los rangos utilizados que nos permite ajustar el rango definido para ese nombre y luego todas las fórmulas utilizarán el nuevo rango. Para rangos de datos que cambian constantemente este proceso también puede volverse tedioso.

En la nota de hoy veremos una alternativa que nos permite definir el rango dinámicamente sin tener que actualizar las fórmulas o los nombres de los rangos.

Para poder definir el nombre del rango dinámicamente debemos detectar cual es la última fila utilizada en el rango y para ello utilizamos la siguiente fórmula array : =MAX(FILA(1:65378)*(A1:A65378<>"")) (recuerden presional Ctrl + Shift + Enter)
Luego definimos el nombre UltimaCelda para la celda que contiene esta fórmula.

Esta fórmula genera en primer lugar un arrary con números de filas (FILA(1:65378)) del tamaño que nosotros deseamos. Esto definirá el límite de crecimiento del rango. Luego se genera un array con valores lógicos, que serán VERDADEROS si la celda no esta vacía. Al multiplicarlos solo me quedan los valores de las filas con datos, el resto es siempre cero. Finalmente la función MAX devuelve el valor máximo del array.

Con esta información definimos un nuevo nombre en Excel llamado Rango. (Para mas información ver nota sobre nombres) y en el campo Aplica a en lugar de escribir un rango fijo (del tipo A1:C10) escribimos la siguiente fórmula: =DESREF(Hoja1!$A$1;0;0;UltimaCelda)


La función DESREF permite definir un rango a partir de una celda de referencia, la función toma 5 parámetros. El primero es la celda de referencia, los siguientes dos indican la cantidad de filas y columnas de distancia que va a tener nuestro rango desde el valor de referencia. Los últimos dos valores indican la cantidad de filas y columnas que tendrá nuestro rango.

Para descargar el ejemplo haga clic aquí.

Otra aplicación de esta fórmula es para la definición del rango de impresión. Si ya hemos definido una, entonces encontraremos en la lista de nombre uno llamado : Área_de_Impresión si ingresamos la fórmula arriba explicada el rango se actualizará automáticamente.

Descubriendo las funciones de Excel 3 BUSCARH (HLOOKUP)

Retomando los artículos sobre funciones de búsquedas, explicaremos en el artículo de hoy una función que no es muy utilizada pero es muy fácil ya que es similar a otra función que es una de las de mayor uso en Excel. Por supuesto no referimos a BUSCARV.

La función BUSCARH (HLOOKUP para la versión en inglés) nos permite realizar búsquedas en un rango y una vez encontrado devolverá una celda que se encuentra en la misma columna pero a una cantidad determinada de líneas hacia abajo.

Dicha función toma cuatro parámetros. El primero es el valor buscado, el segundo es el rango donde se va a realizar la búsqueda, el tercero es el número de la fila dentro del rango que se utilizará para devolver el valor y el último parámetro indica si queremos que el valor buscado coincida exactamente con algún valor en el rango.

Si no hay coincidencias se devuelva el error #N/A. Si deseamos evitar este error podemos utilizar la función SI.ERROR como se explicó en una nota anterior.

Como pueden ver los parámetros y su funcionamiento son muy similares a la función BUSCARV y por lo tanto surge la pregunta: ¿Por qué tenemos dos funciones tan similares?

En muchos casos en Excel tenemos que trabajar con datos de planillas creadas por otras personas y por lo tanto no podemos cambiar los formatos sino que tenemos que adaptarnos a ellos. De acuerdo como estén distribuidos la función BUSCARH puede ser mas conveniente que BUSCARV.


Veamos algunos ejemplos.


Disponemos de una lista de ventas donde se muestran en las columnas las regiones y en las filas los meses.


Si deseamos buscar las ventas del mes de Mayo para la región Sur podemos utilizar la siguiente fórmula =BUSCARH("Sur";B1:E13;6;FALSO) y para buscar las ventas del mes de Agosto para la región Oeste podemos utilizar =BUSCARH(“Oeste”;B1:E13;9;FALSO).

Podran ver que el número de la fila sumando uno es igual al nro del mes. Esto es porque la primer fila contiene los encabezados. De esta forma podemos fácilmente obtener los distintos meses utilizando los valores.

Formato condicional para resaltar los elementos buscados

El formato condicional es una herramienta que nos permite aplicar el formato que deseamos a una celda de acuerdo a cierto criterio. Estos criterios pueden ir desde algunos simples como por ejemplo si el valor de la celda es mayor que un número determinado a algunos muy complejos donde una formula de Excel se evalúa como verdadero o falso para determinar si aplica el formato o no.

Para los criterios simples, Excel dispone de varias combinaciones que podemos utilizar seleccionando la opción de Formato Condicional de la sección Estilos en la ficha Principal.



Para ver más información sobre el uso de estos criterios simples del formato condicional pueden visitar este link: http://office.microsoft.com/es-es/excel/HP100739393082.aspx?pid=CH100648453082



Veamos el ejemplo


Utilizando el formato condicional resaltaremos los valores de una lista que se encuentren en otra lista. Con este ejemplo daremos formato a todos los valores que coincidan y nuestra lista de valores buscados pueden contener más de uno.

Disponemos de dos listas que contienen valores, la lista en la columna "A" tiene todos los datos y la lista en la columna "C" son aquellos que queremos resaltar.


Definimos un nombre llamado Valores para el rango en la columna "C" donde se encuentran los datos buscados. De esta forma es más flexible la cantidad de datos a buscar y más sencilla la formula.

Ahora debemos ingresar la formula que será evaluada para definir si se tiene que aplicar o no el formato condicional. Para ello activar la celda que contiene el primer dato de la lista (en este ejemplo la celda A2) y debemos seleccionar la opción de formato condiciona y elegir la última opción Nueva Regla.

En la pantalla de Nueva regla de formato seleccionamos Usar una fórmula para determinar que celdas formatear. En el cuadro de texto ingresamos la siguiente fórmula: =COINCIDIR(A2;Valores;0) > 0

La función COINCIDIR buscará el valor en la lista Valores y devolverá la posición una posición mayor a cero significa que fue encontrado.


Es muy importante no utilizar referencias absolutas ya que esto nos permitirá aplicar el formato condicional y la formula siempre evaluará el contenido de la celda a formatear y no utilizar siempre el contenido de la celda A2.

Al ingresar la fórmula Excel nos muestra la pantalla del Gestor de Reglas de Formato Condicional aquí podemos indicar en qué rango de celdas aplicamos el formato condicional modificando el rango en la columna Aplicar a.


Finalmente vemos que Excel ha resaltado las celdas cuyos valores se encuentran en la columna "C".