Tiempo atrás se me planteó un problema sobre la forma de construir un gráfico condicionando las 'Series' mostradas a una celda validada; en su momento me pareció interesante la manera de utilizar los nombres (ver) dentro del especial mundo de los gráficos.
Lo explicaré sobre un ejemplo sencillo.
Supongamos un origen de datos:
Nuestro trabajo consistirá en mostrar una serie concreta en un gráfico cualquiera, por ejemplo tipo columnas, en función al valor de una celda validad con tipo lista.
Lo más importante en estos casos es asignar correctamente los nombres a los rangos de datos, así como emplearlos en la forma adecuada dentro de las formulaciones de los gráficos.
Los nombres asignados a rangos serán para nuestro ejemplo:
españa =Hoja1!$B$2:$B$7
portugal =Hoja1!$C$2:$C$7
pais =Hoja1!$B$1:$C$1
datos =INDIRECTO(Hoja1!$F$1)
los dos primeros ('españa' y 'portugal') son los rangos donde se encuentran los valores a incluir en el gráfico. El nombre 'pais' servirá para listar los valores en la celda validada. El más importante es el nombre 'datos', ya que será este el que se incluirá en la fórmula del gráfico (como vemos se le ha añadido la función INDIRECTO para que Excel reconozca el texto de la celda validada como un rango).
El paso siguiente es construir nuestro gráfico, dándole la estrucutura y formato que queramos:
Insertar > Gráficos > Columna > columna agrupada
Desde las Herramientas de gráfico > Diseño > Datos > Seleccionar datos configuramos nuestro gráfico, dejando sólo una serie de datos, y como etiquetas del eje horizontal el rango A2:A7 que son los años:
Añadiremos a la hoja de cálculo la celda F1 validada tipo lista con el origen de datos el nombre pais previamente creado.
Llegamos al final...si seleccionamos la serie 'españa' que nos queda veremos en la barra de fórmulas la siguiente función:
=SERIES(gráfico!$B$1;gráfico!$A$2:$A$7;gráfico!$B$2:$B$7;1)
siendo el primer argumento el valor que asigna al 'Título del gráfico', el segundo argumento los valores de la etiqueta del eje horizontal, y el tercero el más importante, ya que serán los valores de la serie mostrada.
Trabajaremos sobre esta fórmula cambiando el primer argumento por la referencia o celda F1, que es donde se encuentra la celda con la validación de datos tipo lista. Además sustituiremos el tercer argumento por el nombre datos que nos vincula con la serie del país que deseemos ver; pero ojo, lo haremos de una forma algo especial, ya que sólo admite este cambio si por delante del nombre añadimos el nombre del libro de trabajo:
=SERIES(gráfico!$F$1;gráfico!$A$2:$A$7;'grafico según validación celda.xlsx'!datos;1)
Una vez finalizado, según cambiemos el valor de la celda F1 validada veremos cómo cambia el grafico con los valores de la serie 'españa' o 'portugal', que era lo que buscábamos.
Lo explicaré sobre un ejemplo sencillo.
Supongamos un origen de datos:
Nuestro trabajo consistirá en mostrar una serie concreta en un gráfico cualquiera, por ejemplo tipo columnas, en función al valor de una celda validad con tipo lista.
Lo más importante en estos casos es asignar correctamente los nombres a los rangos de datos, así como emplearlos en la forma adecuada dentro de las formulaciones de los gráficos.
Los nombres asignados a rangos serán para nuestro ejemplo:
españa =Hoja1!$B$2:$B$7
portugal =Hoja1!$C$2:$C$7
pais =Hoja1!$B$1:$C$1
datos =INDIRECTO(Hoja1!$F$1)
los dos primeros ('españa' y 'portugal') son los rangos donde se encuentran los valores a incluir en el gráfico. El nombre 'pais' servirá para listar los valores en la celda validada. El más importante es el nombre 'datos', ya que será este el que se incluirá en la fórmula del gráfico (como vemos se le ha añadido la función INDIRECTO para que Excel reconozca el texto de la celda validada como un rango).
El paso siguiente es construir nuestro gráfico, dándole la estrucutura y formato que queramos:
Insertar > Gráficos > Columna > columna agrupada
Desde las Herramientas de gráfico > Diseño > Datos > Seleccionar datos configuramos nuestro gráfico, dejando sólo una serie de datos, y como etiquetas del eje horizontal el rango A2:A7 que son los años:
Añadiremos a la hoja de cálculo la celda F1 validada tipo lista con el origen de datos el nombre pais previamente creado.
Llegamos al final...si seleccionamos la serie 'españa' que nos queda veremos en la barra de fórmulas la siguiente función:
=SERIES(gráfico!$B$1;gráfico!$A$2:$A$7;gráfico!$B$2:$B$7;1)
siendo el primer argumento el valor que asigna al 'Título del gráfico', el segundo argumento los valores de la etiqueta del eje horizontal, y el tercero el más importante, ya que serán los valores de la serie mostrada.
Trabajaremos sobre esta fórmula cambiando el primer argumento por la referencia o celda F1, que es donde se encuentra la celda con la validación de datos tipo lista. Además sustituiremos el tercer argumento por el nombre datos que nos vincula con la serie del país que deseemos ver; pero ojo, lo haremos de una forma algo especial, ya que sólo admite este cambio si por delante del nombre añadimos el nombre del libro de trabajo:
=SERIES(gráfico!$F$1;gráfico!$A$2:$A$7;'grafico según validación celda.xlsx'!datos;1)
Una vez finalizado, según cambiemos el valor de la celda F1 validada veremos cómo cambia el grafico con los valores de la serie 'españa' o 'portugal', que era lo que buscábamos.
Hola, no entiendo como insertar la formula datos =INDIRECTO(Hoja1!$F$1) en la hoja de calculo, en que celda ubico la formula?
ResponderEliminarHola Gerard,
ResponderEliminarno se inserta en ninguna celda, lo que hacemos es asignar un nombre, al que llamamos datos, asignándole la fórmula =INDIRECTO(Hoja1!$F$1)
Echa un vistazo a http://excelforo.blogspot.com/search/label/Asignar%20nombres%20a%20rangos, en esa categoría del blog se explican varios casos de cómo hacerlo.
Un saludo
Hola, he realizado todo tal cual como dices, pero a la hora de cambiar la formula en el tercer parametro dice, DEBE HACER REFERENCIA A LA HOJA DE CALCULA ABIERTA
ResponderEliminarHola Carlos,
ResponderEliminares una limitación de este 'truco', se debe trabajar con referencias de la misma hoa de trabajo.
Slds
Me gustaría aprender más, me puedes explicar.. Estoy haciendo referencia a la hoja donde estoy trabajando.
ResponderEliminarHola Carlos,
Eliminarenvíame si quieres el ejemplo a
excelforo@gmail.com
le echo un vistazo y te comento.
Slds
Hola, Ismael. Antes de nada, felicitarte y agradecerte tan estupendo blog.
ResponderEliminarHe estado intentando reproducir este ejemplo y, al cambiar el tercer argumento de la serie del gráfico, me sale el mensaje "Una fórmula de esta hoja de cálculo contiene una o más referencias no válidas". Creo que lo he hecho todo exactamente como se explica (lo he revisado una docena de veces), pero no hay manera. Necesito ayuda.
Muchas gracias y un saludo
Hola de nuevo. Ya resolví el problema. Introduje la referencia al nombre datos no en la barra de fórmulas sino a través de la edición de la serie y ahora funciona (pero en la barra de fórmulas aparece el nombre del archivo sin las comitas simples)
ResponderEliminarMuchas gracias de todas formas.
Muchas gracias Andres!
Eliminarme alegro dieras con la solución.. efectivamente hay que incluirlo desde la Selección de datos y la edición de la Serie...
Los apostrofes salen si el nombre del Libro tiene espacios.. quizá tu Libro no tenga espacios en el nombre, por ejemplo: LibroTrabajo.xlsx
No es importante..
Saludos