Gráficos animados con Excel

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í.

Fijar vínculos entre hojas de cuadernos Excel

Al crear vínculos entre rangos de distintos cuadernos, como vimos en esta nota, puede presentarse un problema al introducir cambios en el cuaderno de origen.

Vamos a mostrarlo con un ejemplo. Supongamos dos cuadernos, Origen y Base. En la celda C2 de la hoja1 de Base creamos un vínculo a la celda C2 de la hoja1 de Origen

En la barra de las fórmulas podemos ver que el valor se refiere a la celda C2 del cuaderno Origen.

Guardamos y cerramos el cuaderno Base. Abrimos el cuaderno Origen y movemos le valor de la celda C2 a la celda D2. Guardamos el cuaderno Origen y lo cerramos.

Al volver a abrir el cuaderno Base veremos que el cambio en Origen no se refleja en la celda vinculada y por lo tanto muestra un valor erróneo

Esto se debe a que efectuamos el cambio en Origen después de haber cerrado el cuaderno Base. El vínculo en Base sigue refriéndose a la celda C2 de Origen.

Una solución a este problema es mantener ambos cuadernos abiertos hasta finalizar de realizar todos los cambios.

Una solución más segura es usar nombres. En nuestro caso creamos un nombre que se refiere al rango C2 en la hoja Origen (“DatoParaBase”)

Si movemos el valor de la celda C2 en origen y guardamos el cuaderno, al abrir Base veremos que la referencia se ajusta automáticamente (en lugar de la referencia C2 aparece el nombre que se refiere al rango)

Esta técnica funcionará también si Origen esta cerrado al abrir Base.

Gráfico de columnas con formato dinámico

Al presentar datos en forma gráfica suele surgir la necesidad de resaltar uno o varios puntos de una serie.

Supongamos esta situación: presentamos las ventas de un año y queremos resaltar los mese en los que las ventas han caído por debajo de un cierto límite.

En el gráfico del ejemplo podemos ver con facilidad en qué meses las ventas han caído por debajo de límite establecido. Si se trata de un gráfico estático, donde los datos no cambian, nos basta con dar formato a los puntos de la serie en cuestión.

Pero si queremos mostrar distintos escenarios, cambiando el valor del límite por ejemplo, tenemos que convertir nuestro gráfico en dinámico. Es decir, que el color de los puntos de la serie cambie de acuerdo a la relación al valor del límite.

El principio básico para lograr este tipo de gráfico es separar los puntos de la serie (los meses) en dos series: por encima y por debajo del límite. Esto lo haceos creando un tabla auxiliar en una rango oculto

Los valores de la columna B (sobre el límite) los calculamos con la fórmula:

=SI(F5>=$F$2,F5,NOD())

Los valores de la columna C (debajo del límite) con:

=SI(F5<$F$2,F5,NOD()) 

Al representar vemos dos detalles “indeseables” en el gráfico:

1. El formato numérico del eje de las Y 
2. Las columnas no están ordenadas en forma simétrica. 

El primer detalle lo corregimos usando formato personalizado para los valores del eje

Este formato presenta los valores por miles: ###,###, "M";(###,###,)" M";0

El segundo problema se debe a que estamos representando dos series de datos en el gráfico de manera que para cada mes hay dos valores. Este problema lo solucionamos asignando una de las series al eje Y secundario

Al hacerlo so nos presenta un tercer problema: las escalas de los ejes Y no coinciden!

Podemos corregir esto manualmente con el menú de formato del eje. Pero al volver a cambiar el valor del límite o cualquier otro dato, no enfrentaremos con el mismo problema. Una solución es fijar los valores de ambos ejes. Una solución más general es programar un evento que corrija el valor del eje secundario de manera que siempre coincida con el primario.

Empezamos por definir un nombre que se refiera al rango de los datos (rngDatos en nuestro ejemplo se refiere a la celda que contiene el límite y a las celdas con los valores de las ventas).

También cambiamos el nombre por defecto del objeto gráfico (Gráfico 1) usando el Panel de Selección

En el módulo Vba de la hoja ponemos este código


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngActCell As Range

    Application.ScreenUpdating = False
   
    If Union(Target, Range("rngVentas")).Address = Range("rngVentas").Address Then
   
    Set rngActCell = ActiveCell
   
     ChartObjects("grfVentas").Activate
        ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = _
                ActiveChart.Axes(xlValue, xlPrimary).MaximumScale
   
    rngActCell.Select
   
    End If
   
    Application.ScreenUpdating = True
   
End Sub




Un último paso es ocultar el eje secundario

El archivo del ejemplo se puede descargar aquí.