sábado, febrero 24, 2007

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 graficos dinamicos


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:

6 comentarios:

  1. Puedo agregar una condicional a las graficas con listas desplegables?

    ResponderBorrar
  2. A qué te refieres con agregar una condicional?

    ResponderBorrar
  3. Muy bueno el blog, lo descubrí ayer y ya lo visité 10 veces. La verdad es que este post es impresionante, no es que los demás no lo sean pero vio como es la cosa, si uno por ahí ya lo sabía... no lo valora tanto.
    En fin, felicitaciones, y eso de cambiar el título a un gráfico con una formula, muy bueno.
    Saludos, y siga así.
    Desde Rosario,
    Lavih

    ResponderBorrar
  4. muy buen blog, era justo lo que necesitaba...

    una consulta eso si... hice todo lo que aparece aca para usar el autofiltro en los gráficos... hasta ahi todo bien... pero me urge saber si existe un método para que en vez de aparecer un solo filtro, puedan aparecer dos o mas... me explico:

    el gráfico que tengo yo es similar al que aparece aca, pero en vez de los nombres tengo unos numeros que corresponden a un orden... pues bien, me gustaria saber si es posible que en el grafico apareciera, por ejemplo, los datos del numero 1 y el 2... o incluso, los datos del numero 5 al 9 por ejemplo... es decir, que no solo aparezcan de a uno sino ke se puedan seleccionar...

    espero su respuesta si es que la hay...

    saludos!!!

    ResponderBorrar
  5. Eduardo,
    con esta técnica, en el gráfico aparecen los datos visibles. Así que todo depende de como apliques Autofiltro. En tu caso podrías crear una columna auxiliar con categorías. Por ejemplo, los números 1, 2, 5 y 9 pertenecerían a la categoría A, los números 3, 4, 6, 7 y 8 a la B. Luego podrías aplicar Autofiltro usando los criterios de la columna auxiliar.

    ResponderBorrar
  6. Excelente!!... muy buena la función, vamos a probarla.

    Saludos
    AFAA

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.