Mostrar y ocultar series en gráficos de Excel dinámicamente

lunes, octubre 04, 2010

¿Cómo podemos hacer para mostrar u ocultar series de datos en un gráfico de Excel dinámicamente? Con controles de la barra de formularios, que son sencillos de usar y no requieren programación.
Por ejemplo, supongamos este gráfico




Nuestro objetivo es añadir un elemento que nos permita con un clic mostrar u ocultar cada una de las series.

Veamos los pasos a dar:

Agregamos tres casillas de verificación de la barra de formularios, una para cada serie



Reemplazamos el texto de cada casilla por el nombre de la serie.

Definimos la celda vinculada al control



En este caso he elegido una celda visible, para mostrar el funcionamiento del control. En una implementación real, usaremos una celda oculta.

Hacemos lo mismo con los restantes controles.

Cuando la casilla está activada, el valor de la celda vinculada pasa a ser VERDADERO; cuando no lo está el valor será FALSO. Vamos a aprovechar estos valores en nuestro mecanismo.



El próximo paso consiste en crear cuatro nombres definidos, uno para cada serie y uno para los valores de las categorías (el eje de las X, los meses en nuestro ejemplo).

Para los valores del eje de las X

Mes=dinamico!$B$3:$B$14

Para las series

Ventas =SI(dinamico!$G$20,dinamico!$C$3:$C$14,dinamico!$F$3:$F$14)
Costos=SI(dinamico!$H$20,dinamico!$D$3:$D$14,dinamico!$F$3:$F$14)
Ganancia=SI(dinamico!$I$20,dinamico!$E$3:$E$14,dinamico!$F$2:$F$14)

Como puede verse, estos nombres contienen fórmulas condicionales. Si el valor de la casilla de verificación es VERDADERO, la condición se cumple y el rango es el definido en la tabla de datos; si el valor es FALSO, el rango elegido es F3:F14 que no contiene ningún valor (y por lo tanto no tiene representación en el gráfico).

Ahora tenemos que reemplazar los rangos relevantes en la función SERIES de cada una de las series con los nombres definidos. Esto puede hacerse de varias maneras, por ejemplo:

Seleccionamos la serie VENTAS para que en la barra de las fórmulas aparezca la fórmula SERIES



Reemplazamos los rangos relevantes por los nombres tomando en cuenta no eliminar el signo de admiración (!) que aparece a la izquierda del rango



Apretamos Enter. En lugar del nombre de la hoja aparecerá ahora el nombre del cuaderno



También podemos usar el formulario Modificar Serie de la opción Seleccionar datos



La ventaja de esta técnica es que podemos usar Pegar Nombres (F3) en lugar de tener que escribirlos manualmente. La otra ventaja es que podemos reemplazar el rango de la categorías en el formulario de Editar Rótulos de Eje, sin necesidad de tener que reemplazarlo en la fórmula SERIES de cada serie.

Finalmente, ocultamos las celdas vinculadas, agregamos un cuadro de grupo para agrupar los controles (sólo por cuestiones estéticas) y ya hemos completado nuestro modelo



Descargar el ejemplo.

26 comments:

Anónimo,  05 octubre, 2010 18:46  

Buen, día Maestro:
Que gran creatividad tiene usted más aún compartirla con todos nosotros.
Saludos.

Piotr Nabobda 06 octubre, 2010 08:07  

Cuanto más profundizo en excel más me alucina este software que la mayor parte de los oficinistas usamos al 10%. Gracias, una vez más.

Jorge 06 octubre, 2010 15:56  

Muy simple la solucion pero muy poderoso el resultado, yo lo aplique pero agregando al nombre una referencia dinamica.
SALUDOS

Edicson,  22 octubre, 2010 22:42  

Buenas Tardes Jorge, soy de venezuela y quiero expresar mi agradecimiento por tan brillante Posts, pero tengo una duda y es que cuando trato de usar los datos en una "Tabla", la casilla funciona una sóla vez y posteriormente pierde el cambio realizado. A qué se debe esto ya que si lo opero normalmente sin convertilo a los datos en Tabla funciona perfecto.
Gracias.

Jorge L. Dunkelman 25 octubre, 2010 20:36  

Tendría que ver tu archivo. Puedes mandarlo por mail (de acuerdo a lo que explico en el enlace Ayuda).

Melkwe,  23 noviembre, 2010 12:33  

Ante todo muchas gracias por ayudarnos a sacarle partido a esta pedazo de herramienta. Tu trabajo es impresionante.

Yo tengo un ligero problema: En el paso de crear 4 nombre definidos me quedo atascado y no se muy bien como seguir....No se donde crear dichos nombres...

Gracias de antemano

Melkwe,  24 noviembre, 2010 08:05  

Eres un fenomeno! Que facil parece Excel con tus explicaciones... Gracias por darnos luz ante Excel!

Una preguntilla, si tenemos una tabla que va aumentando dia a dia los datos, como hacemos para ir añadiendo los datos al grafico de forma automatica??

Gracias de antemano.

Jorge L. Dunkelman 24 noviembre, 2010 08:46  

Gracias Melkwe.
En el blog hay varias notas sobre el tema. Haz una búsqueda en el blog con la palabra "dinámicos".

Jorge L. Dunkelman 24 noviembre, 2010 09:14  

Los nombres se crean con el administrador de nombres (en la pestaña Fórmulas) o introduciendo el nombre en el cuadro de nombres (en la parte superior izquierda al lado de la barra de fórmulas).
Estou pensando escribir una revisión sobre el tema del uso de nombres, consulta que se repite a menudo.

KJGR89 19 diciembre, 2010 20:23  

Me pueden decir a que se refiere la palabra "dinamico", es decir, en la parte donde se definen los nombres: Ventas =SI(dinamico!$G$20,dinamico!$C$3:$C$14,dinamico!$F$3:$F$14)

Gracias...

Jorge L. Dunkelman 20 diciembre, 2010 07:13  

Es el nombre de la hoja de la referencia (la hoja donde se encuentra el rango al que nos referimos).
"dinamico!$G$20" es la referencia a la celda G20 de la hoja "dinamico".

Anónimo,  28 octubre, 2011 06:14  

Al crear el nombre "Mes=dinamico!$B$3:$B$14" me da un error de referencia, tengo una duda sobre si tambien se agrega Mes con las demas series de datos, ojala me puedas ayudar.

Jorge L. Dunkelman 28 octubre, 2011 13:04  

No, los nombres se crean con Insertar-Nomnres (Excel 97-2003) o Fòrmulas-Administrador de Nombres-Nuevo (Excel 2007-10). En el formulario que se abre pones "Mes" (sin las comillas) en la casilla Nombre y el rango en la casilla Hace referencia a.
Si has hecho esos pasos, hay un error en el rango que estás usando para la referencia.

Sandra,  28 octubre, 2011 22:18  

Grax estoy tratando de hacer el ejemplo que aparece en la pagina, entonces al definir Mes=dinamico!$B$3:$B$14 me da un error de ref
Pero si la defino dejando un espacio despues del signo de igual no me da error
Mes= dinamico!$B$3:$B$14
Entonces no me deja continuar, ya que no me acepta el paso de la imagen 6, ojala me puedas ayudar, no se que es lo que estoy haciendo mal

Jorge L. Dunkelman 29 octubre, 2011 06:39  

Sandra,
luego de leer tu comentario y también el anterior, me parece que no estás familiarizada con el uso de "nombres" en Excel. Te sugiero que leas esta nota.

Sandy,  01 noviembre, 2011 05:38  

Grax Jorge, ya por fin pude realizar el ejemplo, de hecho no se si se deba a que uso Office Excel 2007, pero de hecho no utilice dinamico, en lugar de eso puse: =SI(Hoja1!$G$20,Hoja1!$C$3:$C$14,Hoja1!$F$3:$F$14)
ya he logrado hacer el ejercicio, ahora solo me queda una pregunta, es posible activar una opcion para poder seleccionar todo y de esta forma no tenga que estar seleccionando que se muestren todas las series, grax de antemano

Jorge L. Dunkelman 02 noviembre, 2011 18:41  

Si ya estás usando SI podrías agregar un tercer nivel. Es decir =SI(condición 1, rango 1, SI(condición 2, rango2,rango completo)).

Poldark 11 noviembre, 2011 22:05  

¡Muy buen aporte!

¿Hay opción de que desaparezca también la leyenda para dicha serie?

Jorge L. Dunkelman 12 noviembre, 2011 17:20  

No en forma directa. Se puede hacer, pero la explicación es un poco larga como para ponerla en un comentario. Tal vez publique una nota sobre el tema.

Anónimo,  29 junio, 2012 17:41  

Buen día... quisiera saber por qué en el momento de darle ENTER, al finalizar el reemplazo de los rangos por los nombres creados no me edita nada... le doy ENTER y parece como si estuviera bloqueada la gráfica, no me deja editarla :S

Jorge L. Dunkelman 29 junio, 2012 18:41  

Fijate de estar haciendo el reemplazo en forma correcta (dejando el símbolo !)

Herminio,  19 julio, 2012 04:53  

Hola buen dia, gracias por los tips, aplique estos en un grafico con 12 diferentes series y me funciono perfectamente en 10 series y en dos de ellas me paso lo que mencionan en un comentario anterior. al editar y dar enter no lo acepta y parece que se bloqueara (ya verifique que todo estuviera correctamente escrito)
alguna idea?

Buen día... quisiera saber por qué en el momento de darle ENTER, al finalizar el reemplazo de los rangos por los nombres creados no me edita nada... le doy ENTER y parece como si estuviera bloqueada la gráfica, no me deja editarla :S

Jorge L. Dunkelman 19 julio, 2012 07:29  

Herminio, tendrías que enviarme el archivo para que pueda analizar donde está el problema.

Anónimo,  04 octubre, 2012 00:50  

No consigo hacerlo funcionar, lo he creado desde 0 y no hay manera, podrias poner un ejemplo para descargar? yo aprendo mas modificando cosas que funcionan y luego creando lo que necesito

muchas gracias

Aty2

Jorge L. Dunkelman 04 octubre, 2012 18:47  

Acabo de agregar el enlace al final de la nota.

Gonzalo muñoz 05 marzo, 2017 01:10  

Hola buenas me ha servido un monton muchas gracias :D

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP