miércoles, 10 de febrero de 2010

Gráficos dinámicos en Excel.

Contestando a un usuario, voy a atreverme con un asunto peliagudo de configuración de gráficos. La construcción de un Gráfico dinámico o variable. Pedia ayuda un lector:

...Quisiera saber cómo hacer un gráfico con una validación, es decir, que de una lista de 4 fechas pueda elegir una de ellas y en el gráfico se muestre sólo la que seleccioné de la lista....

Para resolver este problema emplearemos las Listas o Tablas (vimos algo de ellas en una entrada anterior), la función INDIRECTO y un Cuadro de lista-herramienta que pertenece a los Controles de formulario.
Disponemos de una tabla de datos, con referencias cruzadas entre las 24 horas de cada día y distintos días del año:

Gráficos dinámicos en Excel.


En primer lugar crearemos una Tabla o Lista para cada columna de datos, también daremos un Nombre al rango de las horas del día:

Gráficos dinámicos en Excel.
haz click en la imagen


Este paso es fundamental, ya que basaremos nuestro 'dinamismo' sobre estas tablas y nombres.
La siguiente etapa es construirnos una Tabla, rango M1:O7, donde referenciaremos cada fecha con una Tabla o Lista de las creadas:

Gráficos dinámicos en Excel.


En base a esta pequeñá tabla de datos construiremos nuestro Cuadro de Lista. Insertamos desde los Controles de Formulario la herramienta Cuadro de lista, y desde el 'Formato de control' asignamos el Rango de entrada y Vincular con la celda:

Gráficos dinámicos en Excel.


Hemos definido como elementos a desplegar con el Cuadro de Lista los valores de las celdas $N$1:$N$7, además hemos forzado que nuestra elección se refleje en la celda $R$2. Es importante observar que el resultado que obtengamos no es el mismo elemento seleccionado, si no una numeración ordenada desde el primer al último elemento de nuestro Rango de entrada, esto es, para el primero de los valores desplegados será el 1, el 2 para el segundo, etc.
No necesitamos más, con este valor de la celda $R$2 variable en función de la Fecha seleccionada dentro del Cuadro de lista, y con la Tabla de datos M1:O7, podremos saber qué Tabla o Lista es la que deberá emplear nuestro futuro Gráfico.
Obtenemos mediante funciones los siguientes valores. En la celda S2, con la función
=BUSCARV(R2;$M$1:$O$7;3;0)
el nombre de la Lista correspondiente a la Fecha elegida; y en la celda S3 con
=ELEGIR(R2;$B$1;$C$1;$D$1;$E$1;$F$1;$G$1;$H$1)
el día en formato dd/mm/aaa.

Gráficos dinámicos en Excel.


Nuestro penúltimo paso es, utilizando el valor de la celda S2, es decir, el de la Tabla o Lista que corresponde a la Fecha elegida con el Cuadro de Lista, y aprovechándonos que el rango que tienen todas las Listas es fijo, son 24 horas medidas en medias, lo que hace un rango de 48 celdas, con la función INDIRECTO en forma matricial sobre la celda S2, es decir, marcamos el rango T2:T49 y ejecutamos la función matricial (Ctrl+Mayus+Enter)
{=INDIRECTO(S2)}

Sólo nos queda construir el gráfico, para ello seleccionamos el rango 'Horas' y el rango T2:T49 y directamente nos vamos el menú Insertar > Gráfico y le daremos el aspecto que más nos guste:

Gráficos dinámicos en Excel.


11 comentarios:

  1. Muchas gracias, eso era lo que necesitaba

    ResponderEliminar
  2. Hola !

    tengo que hacer graficos de los años más sus datos
    y me rchza cada vez que intento incluir más filas de datos.
    Me pueden echar una mano con esto.
    Muchas gracias, tengo una plantilla como puedo incluirla para que se vea el ejemplo de lo que quiero graficar.
    EN LA PRIMERA COLUMNA PONGO LAS CIUDADES, SEGUIDAMENTE Y EN LA PRIMERA FILA LOS AÑO Y BAJO DE ESTOS LOS DATOS. TERMINANDO CON UNA MEDIA DE CIUDAD + AÑO.

    ResponderEliminar
    Respuestas
    1. Hola Gotvi,
      para añadir más filas de datos en un gráfico ya existente deberás activar el gráfico, y desde las Herramientas de gráficos > Diseño > Datos > Seleccionar datos y en la ventana seleccionar y modificar el Rango de datos del gráfico...
      Pruébalo y comentas.
      De todas formas me puedes enviar el ejemplo a:
      excelforo@gmail.com
      Slds

      Eliminar
  3. Ok , muchas gracias. te envio ejemplo

    ResponderEliminar
  4. Hola me pueden ayudar diseñe un libro de excel utilizando visual basic, y cuando termine de trabajar guarde mi trabajo pero al reanudarlo al dia siguiente me pone que "no se puede salir del modo diseño porque no se pudo crear control progressbar1" , cabe señalar que le modifique en excel 2010 y cuando lo corro en 2007 (otro equipo) es cuando me da el error, el diseño fue en 2007 originalmente . espero su respuesta Gracias.
    René.

    ResponderEliminar
    Respuestas
    1. Hola Rene,
      el problema de trabajar con macros (VBA) entre versiones diferentes es que se corre ese riesgo; parece, en tu caso, que aunque inicialmente creaste la macro en 2007, al modificarlo en 2010, has incluido algún elemento/instrucción/método/propiedad que no es reconocido en versiones anteriores.
      Por el mensaje que te sale, yo empezaría revisando ese objeto que te genera el error (ProgressBar).

      En general, si se va a trabajar con un mismo fichero con diferentes versiones, es conveniente desarrollar el código con la versión inferior.

      Un saludo!!

      Eliminar
    2. Gracias por tu respuesta, ya pude resolver el problema, la soluion fue grabar la copia que deje en el 2010 en forma de compatibilidad a 2003 y de aqui lo corri con la version 2007 y funciono sin problema. entonces el problema radica como dices algo no se reconocia. por lo tanto tratare de no moverme del 2007, muchas gracias por tu apoyo. Saludos.

      Eliminar
  5. quiero hacer unas graficas dinamicas en excel es decir que seleccionando de una lista de 10 nombres me despliegue una grafica ala vez por cada nombre y una segunda grafica segundaria al mismo tiempo, como lo puedo hacer o donde puedo ver el tema al repecto, mi correo es mmdelaro@hotmail.com, por tu atencion gracias...!!!!

    ResponderEliminar
    Respuestas
    1. Hola,
      entiendo que tienes en una celda un listado desplegable de 10 nombres, y quieres que según la elección de ese nombre te muestre dos gráficos, que tomarán los datos (supongo) de algún origen basado en tu elección del desplegable.
      Si es así, podrías aplicar lo explicado en esta entrada, o quizá en
      http://excelforo.blogspot.com.es/2012/06/vba-grafico-variable-en-excel-2010.html
      o también sin macros en (y con tablas dinámicas)
      http://excelforo.blogspot.com.es/2011/10/informe-de-grafico-dinamico-segun.html
      o sencillamente
      http://excelforo.blogspot.com.es/2011/03/graficos-segun-celda-de-validacion.html

      Espero te sirva alguna de ellas, si no es así, coméntamelo.
      Slds

      Eliminar
  6. Hola como estas, una pregunta como puedo construir un gráfico dinámico que vaya cambiando de acuerdo al grado de estudiantes, teniendo en cuenta que en la parte superior esta diversos colegios y de allí esta masculino y femenino y en el lado horizontal esta el nivel de instrucción. Si me podrias ayudar porfavor gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      entenderé como 'grado de estudiantes' el número de ellos para construir el gráfico y su comparativa por razón de sexo.
      Si esto es así bastaría que aplicaras como Tabla tu rango de datos, y construir sobre ésta el gráfico... sigue el ejemplo expuesto en esta misma entrada.

      También puedes leer algo en
      http://excelforo.blogspot.com.es/2013/10/grafico-de-excel-con-los-ultimos-datos.html

      Espero haber comprendido correctamente tu situación y lo que querías.
      Slds

      Eliminar