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.
Buen, día Maestro:
ResponderBorrarQue gran creatividad tiene usted más aún compartirla con todos nosotros.
Saludos.
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.
ResponderBorrarMuy simple la solucion pero muy poderoso el resultado, yo lo aplique pero agregando al nombre una referencia dinamica.
ResponderBorrarSALUDOS
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.
ResponderBorrarGracias.
Tendría que ver tu archivo. Puedes mandarlo por mail (de acuerdo a lo que explico en el enlace Ayuda).
ResponderBorrarAnte todo muchas gracias por ayudarnos a sacarle partido a esta pedazo de herramienta. Tu trabajo es impresionante.
ResponderBorrarYo 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
Eres un fenomeno! Que facil parece Excel con tus explicaciones... Gracias por darnos luz ante Excel!
ResponderBorrarUna 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.
Gracias Melkwe.
ResponderBorrarEn el blog hay varias notas sobre el tema. Haz una búsqueda en el blog con la palabra "dinámicos".
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).
ResponderBorrarEstou pensando escribir una revisión sobre el tema del uso de nombres, consulta que se repite a menudo.
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)
ResponderBorrarGracias...
Es el nombre de la hoja de la referencia (la hoja donde se encuentra el rango al que nos referimos).
ResponderBorrar"dinamico!$G$20" es la referencia a la celda G20 de la hoja "dinamico".
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.
ResponderBorrarNo, 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.
ResponderBorrarSi has hecho esos pasos, hay un error en el rango que estás usando para la referencia.
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
ResponderBorrarPero 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
Sandra,
ResponderBorrarluego 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.
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)
ResponderBorrarya 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
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¡Muy buen aporte!
ResponderBorrar¿Hay opción de que desaparezca también la leyenda para dicha serie?
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.
ResponderBorrarBuen 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
ResponderBorrarFijate de estar haciendo el reemplazo en forma correcta (dejando el símbolo !)
ResponderBorrarHola 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)
ResponderBorraralguna 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
Herminio, tendrías que enviarme el archivo para que pueda analizar donde está el problema.
ResponderBorrarNo 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
ResponderBorrarmuchas gracias
Aty2
Acabo de agregar el enlace al final de la nota.
ResponderBorrarHola buenas me ha servido un monton muchas gracias :D
ResponderBorrarQuisiera 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.
ResponderBorrarHola, 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
ResponderBorrarAl final del post hay un enlace para descargar el ejemplo. Te sugiero que lo descargues y lo estudies para comprender como funciona.
ResponderBorrarSerá posible hacerlo con una gráfica dinámica?
ResponderBorrarLos 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