Gráfico con punto destacado similar a Google Analytics

En Google Analytics podemos hacer un seguimiento de las visitas a nuestro blog con el siguiente gráfico. Si situamos el cursor sobre un día nos indica la fecha y el número de visitas.

Google Analytics

En Excel

Creamos un gráfico similar en Excel. Partimos de dos columnas A y B con los datos exportados de Google Analytics, con las fechas y el número de visitas. Añadimos una columna que representará el punto destacado indicando el número de visitas diarias.

La fórmula de la columna C comprueba si el número de fila es el mismo que el número de la celda E2 vinculada a la barra de desplazamiento. Si es falso, introduce la función NOD que devuelve el valor de error #N/A. Así conseguimos que sólo represente el punto seleccionado con la barra de desplazamiento.

Creamos el gráfico

Creamos un gráfico de área para las visitas y de línea con marcadores para el punto destacado. Formateamos los mismos para obtener los colores apropiados y ajustamos la transparencia para hacer visibles las líneas de división de 60 y 120. Eliminamos la línea del eje de ordenadas. En el eje de abscisas dentro de opciones de eje, al ser una fecha podemos seleccionar la unidad (días, meses o años). En este caso cada 15 días.

Resultado final

Con la barra de desplazamiento controlamos la posición del punto destacado.

Calcular la duración del día y el horario de verano en Excel

El próximo fin de semana comienza el horario de verano (CEST – Central European Summer Time). En torno a los cambios de horario de verano en marzo y octubre, tendemos a fijarnos más en como los días se alargan o se acortan. ¿La duración del día varía más en algunos periodos del año o es un efecto acumulativo en el que repentinamente reparamos? He creado varios gráficos para responder a esta pregunta.

Gráficos

Horas de luz diurna

Resulta evidente que en primavera y otoño la duración del día varía más aceleradamente que en verano e invierno. Por ejemplo, de finales de enero a principios de mayo, tres meses, se incrementa en 4 horas (de 10 a 14). Mientras que en un periodo de prácticamente igual duración, de mayo a agosto, el número de horas se estabiliza en 14 horas llegando a un máximo de 15.

Amanecer, ocaso y duración del día

En el gráfico anterior vemos la hora del amanecer y del ocaso a lo largo del año. Los dos escalones reflejan el horario de verano. La línea verde es continua, idéntica a la del anterior gráfico, porque refleja las horas entre el amanecer y el ocaso. El horario de verano tan solo retrasa las horas de luz una hora, no altera obviamente el número de horas de luz.

Gráfico animado

Datos

En primer lugar, necesitamos obtener las horas del amanecer y del atardecer de todos los días del año. Una opción sería descargarlos para una población concreta. Otra más flexible sería crear una fórmula que nos calcule los mismos en función de la latitud y longitud que le indiquemos.

Por suerte, Greg Pelletier del Departamento de Ecología del Estado de Washington, ha adaptado a VBA los cálculos en Javascript de NOAA (National Oceanic and Atmospheric Administration). Nosotros solamente usaremos dos de sus funciones, sunrise (amanecer) y sunset (atardecer). Los argumentos de ambas son: latitud, longitud, año, mes, día, huso horario (zona horaria) y horario de verano (0 = no, 1 = sí).

Necesitamos determinar el inicio y el final del horario de verano, es decir, el último domingo de marzo y de octubre. Podríamos calcularlo con fórmulas disponibles en Excel pero recurrimos a una función definida por Chip Pearson. Hemos tenido que modificarla pues la transición en EE.UU. al Daylight Savings Time (DST) es distinta. 

Armados con dichas fórmulas, necesitamos saber la latitud y longitud (en grados decimales) de la población en cuestión. Pau Urquizu nos ha facilitado inmensamente la labor poniendo a nuestra disposición el listado completo de los municipios de España. También es posible acudir a Google maps y obtener las coordenadas de un punto del planeta:

Botón derecho en una ubicación del mapa > Selecciona ¿Qué hay aquí?

Tabla

Con todos los datos construimos una tabla con los 365 días del año.

NOAA Solar Calculator

Aquí podemos calcular el amanecer, ocaso y acimut para cualquier coordenada. Los cálculos de NOAA se basan a su vez en ecuaciones de los algoritmos astronómicos de Jean Meeus. Tienen una precisión de +/- 1 minuto para entre +/-72° de latitud y de 10 minutos fuera de esas latitudes. A su vez, la conversión de Greg Pelletier de Javascript a Excel VBA varía +/- 1 minuto respecto al código original de Javascript.

Gráfico de barras animado emulando Google Public Data Explorer - Parte 3 de 3

Finalizamos con los dos últimos pasos para elaborar un gráfico de barras animado emulando Google Public Data Explorer. Es importante señalar, que estos dos últimos pasos son independientes. Podemos optar por solamente uno de ellos o ambos. En anteriores entradas vimos:

1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

4. Control de número ActiveX para avanzar y retroceder en el tiempo.

El penúltimo paso es insertar un control de número.

Una vez insertado, modificamos en el cuadro de diálogo Propiedades:

- LinkedCell - Celda vinculada al valor del control de número.
- Max - Valor máximo permitido. Fila donde está el primer país del último mes.
- Min -  Valor mínimo permitido. En este caso 1.
- SmallChange - Variación cada vez que se hace clic en la flecha de control de número. En este caso 30 pues es el número de filas necesarias para empezar en el siguiente mes.

Desactivamos el modo diseño y el cuadro de control estará listo para ser utilizado

5. Animación con código VBA asociada a dos botones para iniciar y detener la animación
Insertamos 4 formas para crear dos botones: play y stop. Dos rectángulos redondeados y en el interior un triángulo y un cuadrado;

A continuación, copiamos el siguiente código en un módulo.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public StopMacro As Boolean
Sub Iniciar()
    Dim i As Integer
    StopMacro = False
    For i = 1 To 10711 Step 30: ' Min, Max y SmallChange
    DoEvents
    If StopMacro = True Then Exit Sub
        Range("G5").Value = i   ' Celda vinculada
        Application.Calculate
        Sleep (1)   ' Velocidad de la animación
    Next
End Sub
Sub Parar()
    StopMacro = True
End Sub

He modificado el código de S Anand para incluir incrementos de 30 en cada iteración (Step 30), y la opción de detener la subrutina pulsando el botón de stop. Creamos una variable booleana pública llamada Stopmacro. El loop continua mientras dicha variable sea false. Al apretar el botón stop, le asignaremos a Stopmacro el valor true, deteniéndolo.

Asignamos a cada botón su macro correspondiente: Iniciar y Parar.

Ya tendremos lista la animación:

Entradas para construir un gráfico animado emulando Google Public Data Explorer:
1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación.

Gráfico de barras animado emulando Google Public Data Explorer - Parte 2 de 3

Continuamos una entrada anterior con los pasos para elaborar un gráfico de barras animado emulando Google Public Data Explorer. En esta ocasión explicamos el tercer paso.

1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

3. Series ordenadas con los 10 primeros valores en orden descendente.

Creamos la tabla dinámica y ordenamos el campo GEO (países) en orden descendente por la suma del número de Value (desempleados). Clic con el botón secundario sobre GEO, Ordenar, Más opciones de ordenación . Allí, clic sobre Descendente y elegimos Sum of value.

Una vez ordenada la tabla dinámica necesitamos crear una tabla, con los 10 primeros países por mes, que será el origen de datos de nuestro gráfico. También necesitamos una celda que será el argumento número de fila de la función índice para encontrar los 10 primeros países de cada mes. Esa celda estará vinculada al control de número con el que incrementar o disminuir su valor.

Si mostramos las fórmulas de la hoja (Alt + º):

Empleamos la fórmula INDICE para obtener los países y el nº de desempleados:

 En el argumento Matriz seleccionamos la columna B de la tabla dinámica con los nombres de países. En el argumento Núm_fila la celda vinculada al cuadro de número. Le añadimos FILA() - 7 para incrementar una fila más al arrastrar la fórmula hacia abajo. El argumento Núm_columna lo dejamos a 0, pues queremos que devuelva la columna B. Hacemos exactamente lo mismo para el nº de desempleados, cambiando la columna B por la C.

Entradas para construir un gráfico animado emulando Google Public Data Explorer:
1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

Gráfico de barras animado emulando Google Public Data Explorer - Parte 1 de 3

Google Public Data Explorer permite explorar series de datos públicas de organismos e instituciones como el Banco Mundial, Eurostat, la OCDE o de la Oficina del Censo de los Estados Unidos. Estos datos se pueden visualizar de diferentes modos (líneas, barras, mapas, etc.) y animar en el tiempo. Ver ejemplo a continuación:



Siguiendo el ejemplo anterior, he tratado de emular en la medida de lo posible, el mismo gráfico animado en Excel. Lo primero es descargar los datos de Eurostat, importarlos en Excel y hacer algún pequeño ajuste para emplearlos como origen de una tabla dinámica. Solamente incluyo los diez primeros países de cada año para aumentar la claridad.

Describiremos por encima el proceso, deteniéndonos sólo en las técnicas más generales y versátiles que se pueden aplicar en otras ocasiones:

1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

El resultado final sería:

1. Título del gráfico dinámico. 

Insertamos un cuadro de texto vinculado a la celda que cambiará. En la ficha Insertar, grupo Texto, clic sobre Cuadro de texto. Después arrastramos para dibujar el cuadro del tamaño deseado. Mientras está el cursor dentro del cuadro de texto, en lugar de escribir, situamos el cursor en la barra de fórmulas y tecleamos = y clic sobre la celda a vincular. Cambiamos el formato del cuadro, a p.ej, borde sin línea.

2. Formato personalizado del eje vertical.

Para acceder al cuadro de diálogo Dar formato a eje: botón secundario sobre el eje vertical y clic sobre Dar formato a eje o CTRL+1. O bien clic sobre el gráfico y en la ficha Formato en el grupo Selección actual, en el cuadro Elementos de gráfico seleccionamos el EjeVertical (Valor) y clic sobre Aplicar formato a la selección. En Código de formato escribimos: #." M"

Entradas para construir un gráfico animado emulando Google Public Data Explorer:

1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación