Gráficos Dinámicos con Autofiltro

Una característica interesante de Autofiltro es que si creamos una gráfico a partir de una lista, al aplicar Autofiltro, el gráfico se irá adaptando a los datos visibles (filtrados).

El archivo con el ejemplo se puede descargar aqui


Por ejemplo, a partir de esta lista



creamos este gráfico (que no tiene mucho sentido, en esta etapa del ejemplo)



Si filtramos la lista para mostrar sólo las ventas de José, el gráfico se adaptará



En cuanto empezamos a trabajar con este gráfico descubrimos que hay un problema: el gráfico se "encoge" cuando navega sobre líneas ocultas. Por ejemplo, nuestro gráfico puede llegar a verse así



Podemos sobreponernos a este problema y, mejor aún, hacer que en el título de nuestro gráfico aparezca el nombre del agente cuyas ventas estamos mostrando. Todo esto puede hacerse sin macros y usando las técnicas que mostramos en la nota anterior sobre cómo ocultar gráficos en Excel.

Como en ese ejemplo, usaremos dos hojas. En la Hoja1 tendremos la tabla de datos y mostraremos el gráfico (de hecho, una imagen del gráfico).
En la hoja dos reside el "motor" del modelo: el gráfico y la lista de los agentes. En la celda contigua al agente ponemos una fórmula que totaliza las ventas de cada agente. Esto nos servirá para identificar el nombre del agente cuyo gráfico queremos exhibir en la Hoja1.

Empezamos por cortar y pegar el gráfico que acabamos de crear a otra hoja (en nuestro ejemplo a la Hoja2).

Seleccionamos el rango "sobre" el cual se encuentra el gráfico y lo definimos con un nombre. En nuestro caso definimos el nombre "grafico"

grafico: =Hoja2!$A$2:$G$17

En la Hoja2 elegimos una celda vacía, por ejemplo $I$1 y la llamamos sin_graf

sin_graf =Hoja2!$I$2

En el rango A20:A24 ponemos la lista de los agentes; en la celda B20 ponemos la fórmula =SUMAR.SI(agente,A20,ventas) y la copiamos al rango B20:B24.

Los nombres usados en la fórmula se refieren a rangos en la Hoja1

agente: =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)

ventas: =DESREF(Hoja1!$C$2,0,0,CONTARA(Hoja1!$C:$C)-1,1)

En la Hoja1 ponemos en la celda E1 la siguiente fórmula

=INDICE(Hoja2!A20:A24,COINCIDIR(Hoja1!F1,Hoja2!B20:B24,0))

En la celda F1 ponemos la fórmula

=SUBTOTALES(9,ventas)

Esta fórmula nos da el total de ventas del agente elegido con Autofiltro. La fórmula en la celda E1 nos da el nombre del agente, basado en el resultado de la celda F1.

Volvemos por un instante a la Hoja2 y ligamos el cuadro de texto del título del gráfico a la celda E1 en la Hoja1



Ahora tenemos que crear una imagen vinculada al gráfico en la Hoja3.
Elegimos una celda vacía, y la copiamos (Ctrl+C). Seleccionamos la celda E3, abrimos el menú Edición pulsando Mayúsculas (Shift) y elegimos Pegar--Vínculos Imagen. El resultado es una imagen vacía del tamaño de la celda.

Ahora creamos el nombre

mostrar_graf =SI(ESNOD(Hoja1!$E$1),sin_graf,grafico)

Esta fórmula nos permite mostrar u ocultar el gráfico basado en su resultado. Para qu esto sucede ligamos la imagen a la fórmula de la siguiente manera:

Seleccionamos la imagen que creamos con Pegar Vínculos—Imagen y en la barra de fórmulas reemplazamos la referencia por "=mostrar_graf"



Ahora podemos filtrarla lista con Autofiltro y veremos aparecer el gráfico. Por ejemplo, si elegimos Miguel



Este modelo tiene un serio problema potencial: si dos agentes tienen exactamente el mismo volumen de ventas, no se mostrará el gráfico.
Las soluciones posibles son:

1 – usar macros para determinar el nombre de la primera fila visible en la columna A a partir de la celda A2; ver el ejemplo desarrollado en el sitio Contextures;

2 – agregar una columna auxiliar que agregue a cada total de ventas del mes de cada agente un número identificatorio suficientemente pequeño como para no alterar los resultados. Por ejemplo, a Roberto le damos el número 1; a las ventas de Roberto le sumamos 1/1000000. Lo mismo con los restantes (Pedro 2/1000000, etc). Luego basamos las fórmulas y el gráfico en la columna auxiliar.

Technorati Tags: MS Excel