lunes, octubre 04, 2010

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

¿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.

31 comentarios:

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

    ResponderBorrar
  2. 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.

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

    ResponderBorrar
  4. 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.

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

    ResponderBorrar
  6. 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

    ResponderBorrar
  7. 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.

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

    ResponderBorrar
  9. 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.

    ResponderBorrar
  10. 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...

    ResponderBorrar
  11. 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".

    ResponderBorrar
  12. 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.

    ResponderBorrar
  13. 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.

    ResponderBorrar
  14. 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

    ResponderBorrar
  15. 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.

    ResponderBorrar
  16. 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

    ResponderBorrar
  17. 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)).

    ResponderBorrar
  18. ¡Muy buen aporte!

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

    ResponderBorrar
  19. 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.

    ResponderBorrar
  20. 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

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

    ResponderBorrar
  22. 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

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

    ResponderBorrar
  24. 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

    ResponderBorrar
  25. Acabo de agregar el enlace al final de la nota.

    ResponderBorrar
  26. Hola buenas me ha servido un monton muchas gracias :D

    ResponderBorrar
  27. Quisiera a través de un Check, mostrar y ocultar, solamente las etiquetas de datos, no las series (barras o líneas), veré como resulta y comento.

    ResponderBorrar
  28. Hola, muy bueno el ejemplo de las gráficas. la verdad no he podido hacerlo. ya que no le entiendo bien donde están las formulas para poder trabajarlo

    ResponderBorrar
  29. Al final del post hay un enlace para descargar el ejemplo. Te sugiero que lo descargues y lo estudies para comprender como funciona.

    ResponderBorrar
  30. Será posible hacerlo con una gráfica dinámica?

    ResponderBorrar
    Respuestas
    1. Los gráficos dinámicos se manejan con los controles de la tabla dinámica en que se basan. Es más, se puede agregar los filtros en el gráfico y usarlos directamente alli.

      Borrar

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