Gráficos animados con Excel

viernes, junio 15, 2012

En este blog ya hemos mostrado cómo crear gráficos interactivos (pueden ver las notas apretando el enlace Gráficos en la nube de etiquetas). Entendemos por gráficos interactivos aquellos donde el usuario puede cambiar el aspecto, las series y/o puntos de las series sin necesidad de acceder a la base de datos que alimentan el gráfico.

Podemos dar otro paso adelante y crear un gráfico animado, como éste



En este gráfico mostramos los cambios en la relación entre el real brasileño (BRL) y el dólar estadounidense (USD).

No me entusiasman particularmente los gráficos animados, pero en casos como éste, nos ayudan a ver o descubrir tendencias.

En esta nota vamos a mostrar cómo construirlo (el modelo se puede descargar aquí).

EL primer paso, por supuesto, es obtener los datos. En este ejemplo he obtenido los datos del sitio OANDA (datos históricos)



El segundo paso es construir el gráfico que en esta etapa será estático.



El próximo paso es agregar una barra de desplazamiento que nos permita ir cambiando los datos de la serie en el gráfico. En este caso usamos la barra de desplazamiento (scrollbar) de la colección de comandos ActiveX



En el cuadro de propiedades del control, definimos la celda B9 de la hoja “dinamico” como la celda ligada



A la celda B9 le hemos asignado el nombre “chrtCounter”.

En la celda B6 de la hoja “dinamico” ponemos el número de puntos que queremos que aparezcan en el gráfico. En nuestro caso hemos definido 30



A la celda le hemos asignado el nombre “chrtStep”.

Un último parámetro a definir para la barra de desplazamiento es el valor máximo. Nuestra serie tiene 366 puntos (valores); 30 aparecen en el gráfico así que el valor máximo de la barra de deslazamiento será 336 (para evitar que aparezcan puntos sin valor). Si la cantidad de puntos de la serie y el número de puntos a exhibir en el gráfico no varían, podemos poder una constante en la celda B7 (a la que le hemos asignado el nombre definido “chrtMaxScrollBar”).
Pero si queremos tener más control de las definiciones, usamos una fórmula para determinar dinámicamente el valor máximo. En la celda B7 ponemos:

=CONTAR(datos!$B$2:$B$367)-chrtStep

Excel no nos permite usar una referencia a la celda para definir el valor máximo de la barra de desplazamiento. Para hacerlo programamos el evento ScrollBar1_Change()

Private Sub ScrollBar1_Change()
    ScrollBar1.Max = Range("chrtMaxScrollBar")
End Sub


Ahora tenemos que definir nombres que se refieran dinámicamente a los puntos de la serie de datos y a la categoría (los valores del eje de las X). Creamos dos nombres:

- Para los puntos de la serie de datos

chrtSeries =DESREF(datos!$C$2,chrtCounter,0,12+chrtStep,1)

- Para los puntos del eje de las X

chrtCategory =DESREF(datos!$B$2,chrtCounter,0,1+chrtStep,1)

Ambos nombres se refieren a fórmulas que usan la función DESREF para determinar el rango de valores a mostrar de acuerdo a los valores que el usuario haya asignado a la barra de desplazamiento.
Cada vez que el usuario pulsa la barra de desplazamiento, el valor de la celda ligada (chrtCounter) cambia.



Para que estos cambios se reflejen en el gráfico remplazamos los rangos de la función SERIES del gráfico con los nombres definidos. Seleccionamos el gráfico y usamos el menú “seleccionar origen de datos-editar” para remplazar la referencia fija al rango por el nombre que se refiere al rango dinámico



A esta altura de los acontecimientos podemos lograr la animación sencillamente arrastrando el marcador de la barra de desplazamiento (como se ve en el video). Pero en nuestro caso usamos una macro para disparar la animación y otra para volver el gráfico al punto de partida. Estas macros están ligadas a los botones con los símbolos “>>” y “<<” grafAnim07 Todo lo que hace la macro para animar el gráfico es cambiar el valor de “chrtCounter”


Sub animate_Chart()
    Dim iX As Integer, Delay As Single, Start As Single
   
    'fijar valores del eje Y del grafico
    Call fix_Y_Values
   
    Range("chrtCounter") = 0
   
    For iX = 0 To Range("chrtMaxScrollBar")
        Range("chrtCounter") = Range("chrtCounter") + 1
            Delay = 0.1
            Start = Timer
            Do While Timer < Start + Delay
                DoEvents
            Loop
    Next iX
   
End Sub



Para volver el gráfico a la situación inicial sencillamente fijamos el valor de “chrtCounter” a 0

Sub backTo0()
    Range("chrtCounter") = 0
End Sub



El ultimo detalle es fijar el valor mínimo y máximo del eje de las Y. Podemos usar constantes, pero mejor es determinar estos valores en forma dinámica usando estas fórmulas

Para el valor mínimo (chrtXmin): =MULTIPLO.INFERIOR(MIN(datos!$C$2:$C$367),0.1)

Para el valor máximo(chrtXmax): =MULTIPLO.SUPERIOR(MAX(datos!$C$2:$C$367),0.1)

Estos valores se fijan al empezar la macro de la animación con la rutina Call fix_Y_Values

Private Sub fix_Y_Values()
   
    ActiveSheet.ChartObjects("chrtUSDBRL").Activate
    With ActiveChart
        .Axes(xlValue).MinimumScale = Range("chrtXmin")
        .Axes(xlValue).MaximumScale = Range("chrtXmax")
    End With
   
    Range("A1").Select
   
End Sub


El cuaderno con el ejemplo se puede descargar aquí.

16 comments:

Bernardo Cortina 15 junio, 2012 17:03  

Está fantástico esto de los gráficos animados, me servirán de mucho, gracias!!!

Anónimo,  20 junio, 2012 21:30  

Gracias Jorge por tu Blog.

Perdona mi ignorancia, pero por que guardas el archivo como "hoja de calculo binaria" hay algo en particular en guardar asi los archivos.

Saludos

Jorge L. Dunkelman 21 junio, 2012 22:51  

No hay ningún motivo en particular, es una de las posibilidades de Excel 2010. Los archivos binarios no requieren que los definas de antemano como "habilitados para macros" (.xlsm)
Puedes consultar esta nota de Microsoft

Anónimo,  22 octubre, 2012 03:49  

Por favor pueden indicarme donde se colocan las fomulas de chrtSeries y chrtCategory

Jorge L. Dunkelman 22 octubre, 2012 19:54  

Está expplicado en la nota donde dice:
Para que estos cambios se reflejen en el gráfico remplazamos los rangos de la función SERIES del gráfico
Fijate en la imagen

Jorge Hernando Ortega 23 julio, 2013 15:12  

Buenas, buen tutorial, pero me falta entender donde se tienen que crear las variables chrtSeries y chrtCategory

Jorge Dunkelman 23 julio, 2013 16:11  

Son nombres (names) que se crean en Fórmulas-Nombres Definidos-Asignar Nombre (o Administrador de nombres).

Jorge Hernando Ortega 24 julio, 2013 16:10  

Buenas,

conseguido, enhorabuena por el tutorial!!!

Anónimo,  22 octubre, 2013 21:55  

Hola! excelente tutorial... muchas gracias!
¿Cómo se podría agregar un botón de pausa al avance del gráfico?
Saludos, Giuliano

Anónimo,  23 octubre, 2013 00:08  

Hola Jorge,
Me queda otra duda, ¿El desplazamiento del gráfico no se detiene?... El contador de barra sigue avanzando, luego de superar el límite indicado por la celda del máximo de la barra. Lo hago manual, todo bien, ejecuto la macro... y sigue...
Descargué tu ejemplo, la misma cosa... Existe alguna forma de detener automáticamente el avance. El botón, << reinicia el gráfico no lo detiene.
Saludos, Giuliano

Jorge Dunkelman 24 octubre, 2013 18:26  

Hola Giulano, descargué el ejemplo, y no veo que suceda lo que señalás en tu segundo comentario. Cyuando alcanza el valor máximo , la animación termina y la celda del contador (B9) muestra el máximo (B7) más 1. El botón << efectivamente vuelve el gráfico al primer punto de la serie. Para deternelo hay que agregar código. En cuanto encuentre un poco de tiempo lo publicaré.

Jorge Dunkelman 24 octubre, 2013 18:29  

Algo más, para tener control del desplazamiento del gráfico se puede correr el botón de la barra de desplazamiento mantiéndolo apretado con el mouse.

Anónimo,  09 octubre, 2014 18:02  

Hola, Jorge:

Tengo un problema a la hora de introducir la variable chrtSeries en la serie del gráfico. Me dice que la variable introducida debe pertenecer a la hoja de cálculo...

Un saludo y muchas gracias por tu ayuda. Álvaro

Jorge Dunkelman 10 octubre, 2014 17:51  

Hola Álvaro,
tal vez no quede del todo claro en la nota. Para usar el nombe definido abrimos las definiciones de la serie; supongamos que aparece en principio

Hoja1!A1:A10

borramos la referencia al rango dejando elnombre de la hoja

Hoja1!

aquí agregamos el nombre definido

Hoja1!chartSeries

Aceptamos y cerramos. Al abrir de nuevo veremos que Excel reemplaza la refrencia Hoja1 por la referencia al libro.

Anónimo,  19 noviembre, 2014 18:52  

Hola Jorge,
Mira he conseguido armar el gráfico animado siguiendo tus instrucciones, sin embrago lo que intento hacer es un grafico con 10 variables y solo consigo mover la primera, es decir en vez de tener solo "USD/BRL" tendria "USD/BRL2","USD/BRL3"...."USD/BRL10". Espero me puedas ayudar.
Gracias por tu atención.

Saludos,
Chris W.

Jorge Dunkelman 21 noviembre, 2014 16:27  

En el nota explico como crear el rango dinámico para la única serie de datos que hay en el ejemplo:

Ahora tenemos que definir nombres que se refieran dinámicamente a los puntos de la serie de datos y a la categoría (los valores del eje de las X). Creamos dos nombres:

- Para los puntos de la serie de datos

chrtSeries =DESREF(datos!$C$2,chrtCounter,0,12+chrtStep,1)


De la misma manera tienes que definir un rango dinámico para cada serie de datos.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP