sábado, mayo 10, 2008

Gráfico dinámico a partir de lista desplegable.

En la nota anterior vimos como construir un gráfico dinámico cuyos valores cambian de acuerdo a qué celda se escoja.
En la nota sobre gráficos con listas desplegables, vimos como agregar un control al gráfico de manera de poder elegir la serie de datos a ser exhibida.
Otro enfoque a este tema es el me consulta un lector: que el gráfico cambie de acuerdo a los valores de una lista desplegable en una celda. Por ejemplo, partiendo de los datos de la nota anterior, crear una lista desplegable con los años, de manera que cuando elijamos un año de la lista los valores del gráfico cambien de acuerdo.




La forma más práctica de crear una lista desplegable en una celda es con Validación de Datos. Seleccionamos el rango B2:E2, que contiene los años y creamos el nombre "periodo" usando el cuadro de nombres



Ahora podemos crear una lista desplegable, por ejemplo en la celda B15 usando Validación de Datos






Creamos el gráfico sólo para el año 2005. Seleccionamos las barras y en la barra de fórmulas podemos ver la fórmula SERIES que define los datos del gráfico



Nuestra tarea es reemplazar el rango B3:B12 en la fórmula SERIES por un nombre que contenga un rango dinámico. Empezamos por definir cuatro nombres, uno para cada año, con los rangos relevantes



per2005 =Hoja1!$B$3:$B$12
per2006 =Hoja1!$C$3:$C$12
per2007 =Hoja1!$D$3:$D$12
per2008 =Hoja1!$E$3:$E$12

Al definir nombres en Excel podemos usar toda combinación de letras y números, sin espacios en blanco, pero el nombre debe empezar con una letra. Por este motivo hemos puesto "per" al principio de cada nombre. Esto nos obliga a agregar una celda donde combinamos el valor de la lista desplegable con el prefijo "per". En nuestro caso, en la celda C15.



Ahora creamos otro nombre, "pername", con esta definición:

pername =INDIRECTO(Hoja1!$C$15)

Usamos la función INDIRECTO, para que Excel interprete el texto de la celda C15 como rango.

Nuestro próximo paso es reemplazar el rango que aparece en Valores en el diálogo de Datos de Origen,



por el nombre que acabamos de crear



Para mejorar el aspecto de nuestra hoja, le damos a la fuente de la celda C15 el mismo color que el fondo, haciéndola "invisible".

Ahora los valores del gráfico se acomodarán de acuerdo al año que hayamos elegido en la lista desplegable.

El archivo con el ejemplo se puede descargar aquí.

1 comentario:

Anónimo dijo...

Hola
Sigo con atención todos sus ejemplos.
En este gráfico dinámico, al poner en origen de datos/ valores el "pername" en mi hoja me sale error. Tampoco se porque se pone el nombre de la hoja interactivo2.xls. Creo que es un buen ejemplo para desarrollar .
gracias