mostrar y ocultar series de un grafico

Luego de la entrada sobre el cambio de imágenes sin macros y el manejo de rangos dinámicos a la hora de asignar datos a un gráfico (aquí), me llega una consulta mas que interesante: de que forma es posible mostrar u ocultar las series de un gráfico. La cuestión es mas sencilla de comprender si les muestro la siguiente imagen:

Podemos observar en la imagen superior la evolución de un determinado producto a lo largo de 6 meses, para 5 continentes. Si... todo muy útil, pero... ¿no se torna un poco confuso observar tanta información "de golpe"? Y menos mal que son 5 continentes, por que si fuesen 10, la gráfica directamente sería ilegible. Entonces mi lector me pregunta ¿puedo mostrar "por partes" esa información, es decir, solamente América o Africa, o solamente Europa.... o Europa, América y Oceanía? 

[+/-] Ver el resto / Ocultar

Si, es totalmente posible y sin hacer uso de las macros. Para ello será buena idea que vean (o revean) esta entrada, en donde explico como Excel arma los datos de un gráfico a partir de una función, la cual podemos modificar y personalizar a nuestro gusto y necesidad.

Luego de leer lo anteriormente expuesto, analicemos la siguiente tabla, que es el origen de los datos de nuestro gráfico:

Cuando ingresamos esa tabla como "origen de datos" del gráfico, Excel toma la primer fila (encabezado) y lo utiliza para mostrar los nombres que se ven a la derecha de la primer imagen que subí, dentro de un recuadro.

Luego se hace cargo de las filas (registros): desde enero a junio lo pone como rótulos del eje X, dejando al eje Y para remarcar las cantidades mínimas y máximas en juego.

El resto de las celdas, en este caso desde B2 hasta F7 son los datos que arman la gráfica. Noten que por cada columna, existe una serie: América, Africa, Europa, Oceania y Africa.

Los datos para América los toma del rango B2:B7, para Africa C2:C7 y así sucesivamente, hasta llegar a Asia, que lee desde el rango F2:F7 (a esto lo podemos observar si hacemos click sobre cada serie y luego miramos la barra de fómulas).

seleccioné la serie América y miren la barra de fórmulas: Hoja1!$B$2:$B$7.... O sea que esa es la columna desde donde está leyendo los datos de América.

Lo que expliqué hasta ahora es por el siguiente motivo: si quiero mostrar/ocultar una serie, será fundamental conocer su fuente de datos y así saber a que rango debo hacer referencia para mostrarla o no. Espero haber sido claro, para una lectura adicional: click acá, en donde verán el tema de los argumentos de la función SERIES() de forma mas detallada.

A cada columna le daré un nombre, correspondiente al continente que reflejan. Vamos a ir seleccionando columna x columna y desde el "cuadro de nombres" llevaremos a cabo la tarea:

Vamos con la otra columna:

Y repetimos la operación hasta llegar a la columna F, es decir, Asia. El cuadro de nombres debería quedarnos así:

cada continente tiene su propio "nombre de rango", que fuimos asignando en los pasos anteriores.

Volvemos a nuestro gráfico y seleccionamos solo la serie América, haciendo click sobre ella, que es la linea de color azul oscuro. Ahora nos dirigimos a la barra de fórmulas, editamos, y cambiaremos el argumento Hoja1!$B$2:$B$7 por esto: 'grafico-series-ocultas.xls'!america (en donde 'grafico-series-ocultas.xls' es el nombre del archivo sobre el cual estoy trabajando. Luego del signo ! agrego el nombre del rango definido con anterioridad para esa serie)

Luego seguimos con Africa (la serie de color fucsia), y hacemos lo mismo, solo que agregamos al final el rango "africa" (creado anteriormente):

Y así continuamos reemplazando el argumento de la función SERIES() para cada serie del gráfico: borramos lo que puso por defecto Excel (ejemplo: Hoja1!$D$2:$D$7) poniendo en su lugar el nombre que le dimos a ese rango (europa), tal como se puede ver en las dos imagenes anteriores. Repetimos el proceso (ojo de no  equivocarnos y colocar incorrectamente el nombre del rango creado) hasta llegar a Asia. Observen que Excel quita el Hoja1! y lo reemplaza por el nombre del libro, que en este caso es 'grafico-series-ocultas.xls'

¿Que hice hasta ahora? Muy sencillo: personalicé la función SERIES() del gráfico, poniendo un nombre de rango en lugar de la clásica A2:A7 o C2:C7. Si miramos con atención nuestra gráfica, esta sigue mostrando correctamente los datos de origen:

todo sigue igual, funcionando correctamente.

Casi llegamos: cada rango tiene su nombre y cada nombre fue asignado a la función SERIES() del gráfico. ¿Pero como muestro/oculto cada serie con lo visto hasta ahora?

Como primer medida colocaremos un control de la barra de herramientas Formulario. Podríamos usar varios, como el ComboBox, el ListBox, el Control de Número, Casillas de verificación o Barra de Desplazamiento.

Una alternativa, por ejemplo, sería colocar dentro de ComboBox los nombres de los continentes, y mostrarlo en la gráfica de acuerdo a la selección que realizó el usuario:

O bien un Cuadro de Lista:

Como ya saben, cada uno de los mencionados controles se vinculan a una celda en particular, reflejando un valor. Sería ese valor el que nosotros aprovecharíamos para saber que continente mostrar. Pero todos adolecen de un problema: nos permite una única selección, lo que no nos dejaría margen para mostrar dos o tres continentes al mismo tiempo. Entonces, la opción mas correcta es inclinarnos por colocar varias Casillas de Verificación, una por cada continente:

Analicemos: coloqué un control por cada continente y lo vinculé con la celda que se encuentra dos filas mas abajo. Si el control está tildado (activado) su celda vinculada refleja VERDADERO y si no, FALSO. Y esa gran propiedad es la que aprovecharemos para mostrar (VERDADERO) o no (FALSO) un continente dentro del gráfico.

Para esto vamos a necesitar modificar el cuadro "se refiere a" del formulario de asignación de nombres (menú insertar / nombres / definir). Cuando creamos el rango America, pusimos:

O sea que America es el rango B2:B7. Bueno, a partir de ahora eso será "relativo", ya que agregaremos una función al "se refiere a": nuestra querida SI(). Colocaremos lo siguiente:

=SI(Hoja1!$A$11;Hoja1!$B$2:$B$7;Hoja1!$G$2:$G$7)

es decir: si el valor en A11 es verdadero (el control está activado), entonces el nombre "america" se aplicará al rango B2:B7, si es falso, "america" será el rango G2:G7.... que está vacío. Y al estar vacío la serie dentro de la gráfica se borrará automáticamente.

Seguimos modificando los nombres, agregando la función SI() a cada uno de ellos, hasta llegar hasta Asia. Obviamente cuidamos bien cada cambio, para Africa sería:

=SI(Hoja1!$B$11;Hoja1!$C$2:$C$7;Hoja1!$G$2:$G$7)

Con "europa" de esta forma:

=SI(Hoja1!$C$11;Hoja1!$D$2:$D$7;Hoja1!$G$2:$G$7)

y así hasta terminar.

Lo importante es analizar correctamente las cosas: si la casilla del continente está activada, VERDADERO aparecerá en la celda vinculada. Luego tomo ese VERDADERO para crear o no el nombre del rango: si está activado, hago referencia a la columna del continente respectivo, caso contrario apunto esa referencia a un rango en blanco, por lo tanto la gráfica no mostrará ningún dato.

Es hora de visualizar los resultados. Ahora pueden observar que la gráfica solo muestra aquellos items cuyo control se encuentra tildado:

Y vamos con otra, para quedarnos tranquilos:

Y bueno, ya que estamos, otra:

Ahora bien: visto que ya podemos ocultar o mostrar series Ad líbitum ¿será factible hacer lo mismo con múltiples entradas de datos, realizando las mismas acciones sobre los meses o las cantidades? Si, pero será tema de futuras entradas.

No olviden practicar este ejercicio, les será de mucha utilidad y es de esos casos en los que luego todos dicen ¿como hace este tipo para manejar Excel de esta forma?

Un abrazo y les dejo el link al archivo.