domingo, octubre 29, 2006

Excel - Graficos con listas desplegables

En una entrada en JLD Excel mostraba como usar controles en hojas de cálculo Excel.

Uno de los usos de estos controles es generar listas desplegables (combo boxes). Cuando de hojas de cálculo se trata, podemos usar la funcionalidad Validación de Datos para generar estas listas desplegables. Pero cómo hacemos para poner una lista desplegable en un gráfico de Excel?
La respuesta: con el control cuadro combinado de la barra de herramientas "Formularios"





Supongamos que tenemos esta lista de ventas por mes y por línea de productos



Nuestro objetivo es generar un gráfico como este:



Cada vez que elegimos un mes de la lista desplegable, los datos del gráfico se adaptan a nuestra elección.

Los pasos a seguir son los siguientes:

1 – Preparamos el rango A1:D2 para que contenga los datos del gráfico




en el rango B2:D2 escribimos esta fórmula

=INDICE($B$7:$D$18,$A$3,COINCIDIR(B$1,$B$6:$D$6,0))


y en la celda A2 escribimos esta: =INDICE(A7:A18,A3)

Por ahora las fórmulas dan un resultado #¡VALOR!, cosa que será corregida escribiendo en la celda A3 el número 1 (o cualquier número entre 1 y 12).

2 – Creamos un gráfico de columnas con el rango A1:D2



borramos la leyenda y le damos un color distinto a cada serie (línea de productos).

3 – Activamos la barra de herramientas "Formularios", y cliqueamos el control "cuadro combinado". Lo ubicamos dentro del gráfico y abrimos el menú de formato del control



4 – En la ventanilla "rango de entrada" señalamos el rango que contiene los meses en la tabla de datos (A7:A18); en la ventanilla "vincular con la celda" señalamos A3; en "líneas de unión verticales" escribimos 12. También podemos marcas "sombreado 3D" para obtener este efecto en el control.

Al apretar "aceptar" las fórmulas darán #¡VALOR!, ya que el valor de la celda vinculada (A3) pasa a ser 0. Todo lo que hay que hacer es elegir un mes en la lista desplegable y veremos los resultados en el gráfico.

Otro truco es cambiar el color de fuente de la celda A3 a blanco, para "hacerla desparecer". Otra variante, más elegante tal vez, es ubicar este argumento en una celda que se encuentra "debajo" del gráfico, por ejemplo H10.

6 comentarios:

Guillermo dijo...

Jorge
Estupendo este tipo de grafico, al hecer una combinacion entre este grafico y uno con lineas de horizontales para fijar max y min, se produce el efecto de no sincronizarce los ejes "y" dado que queda fijo el derecho al ajustar manualmente, y las series derechas son cambiantes. a ver si se entiende dado que con esta opcion creo que quedaria un grafico tremendamente util. (pienso que talvez con una pequeña programacion se podrian ajustar)

Guillermo
ggvillafane@gmail.com

Pablo dijo...

Jorge
Me parece excelente este tipo de grafico y me sumo a la inquietud de Guillermo, pero tambien me interesaria si se pudiece utilizar 2 filtros uno por mes como tienes y otro por maquina.
Atte
Pablo

Jorge L. Dunkelman dijo...

Hola, me disculpo por la demora en responder.
Si pueden, envíenme un archivo con datos para estudiar el problema a jorgedun@gmail.com

SSY4 dijo...

TE FELICITO JORGE ME PARECIO ESTUPENDO

Guillermo Duque dijo...

Que tal Jorge, quisiera saber si hay alguna manera de cambiar el tipo de grafico usando un cuadro combinado.
Agradezco su colaboracion

Jorge L. Dunkelman dijo...

Buena pregunta. Publicaré una nota sobre el tema en breve.