miércoles, 30 de marzo de 2011

Gráficos según celda de validación.

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)


haz click en la imagen


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.

9 comentarios:

  1. Hola, no entiendo como insertar la formula datos =INDIRECTO(Hoja1!$F$1) en la hoja de calculo, en que celda ubico la formula?

    ResponderEliminar
  2. Hola Gerard,
    no 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

    ResponderEliminar
  3. 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

    ResponderEliminar
  4. Hola Carlos,
    es una limitación de este 'truco', se debe trabajar con referencias de la misma hoa de trabajo.
    Slds

    ResponderEliminar
  5. Me gustaría aprender más, me puedes explicar.. Estoy haciendo referencia a la hoja donde estoy trabajando.

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      envíame si quieres el ejemplo a
      excelforo@gmail.com
      le echo un vistazo y te comento.
      Slds

      Eliminar
  6. Hola, Ismael. Antes de nada, felicitarte y agradecerte tan estupendo blog.
    He 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

    ResponderEliminar
  7. 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)
    Muchas gracias de todas formas.

    ResponderEliminar
    Respuestas
    1. Muchas gracias Andres!
      me 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

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.