Actualización automática de tablas dinámicas

Ha pasado casi un mes desde que publiqué mi última nota. Antes que mis lectores (y Google!) piensen que he abandonado el blog, publico una nota breve sobre actualización automática de tablas dinámicas en Excel.

Excel no tiene un método incorporado para actualizar tablas dinámicas en forma automática. Cambios en la base de datos se reflejarán en el reporte dinámico sólo cuando apretemos el icono Actualizar (o Alt+F5) o Actualizar Todo (Ctrl+Alt+F5) si queremos actualizar todas las tablas dinámicas del cuaderno

Para evitar tener que actualizar manualmente las tablas dinámicas de nuestro cuaderno (pensemos en un dashboard donde hacemos uso intensivo de tablas dinámicas) podemos crear un evento. El código es muy sencillo

Private Sub Worksheet_Deactivate()
    ActiveWorkbook.RefreshAll
End Sub

En este caso usamos el evento Worksheet_Deactivate de la hoja “datos” (la que contiene la base de datos del reporte dinámico). De esta manera nos aseguramos que cada actualizamos la base de datos, al pasar a otra hoja las tablas dinámicas se actualizan.

Esta técnica supone que la base de datos se encuentra en una hoja del cuaderno que contiene los reportes dinámicos. En el caso en que las tablas dinámicas se basen en fuentes de datos externas, usamos el mismo código pero lo disparamos usando el evento Worksheet_Activate de alguna de las hojas que contienen los reportes dinámicos.

Totales en gráficos de columnas apiladas

Sin lugar a dudas no es comparable a la invención de la rueda o de la pólvora, pero aquí les traigo un tip sobre como agregar automáticamente totales en gráficos de columnas apiladas en Excel.

Supongamos esta tabla de datos que muestra las ventas del año de las tres sucursales de una empresa imaginaria

Con los datos creamos este gráfico que muestra las ventas por mes

Hemos eliminado el eje de los valores (Y) y puesto etiquetas con los valores (en miles) en las porciones de las columnas. Lo que falta es una etiqueta para cada mes que muestre el total de las ventas.

Podemos, por supuesto, poner cuadros de texto sobre cada columna e inclusive ligarlos a los datos en la tabla. Pero queremos algo más automático.

Empezamos por seleccionar el rango F3:F14 (el total de ventas), lo copiamos y agregamos la serie al gráfico con Pegado Especial

Al hacer esto, Excel agrega la serie a las columnas apiladas deformando nuestro gráfico

Para solucionar esto relacionamos la nueva serie al eje de valores secundario, cambiamos el tipo de gráfico a “línea” y agregamos etiquetas

La línea es innecesaria y la hacemos desaparecer poniendo la opción “color de línea” a “sin línea”

Para que las etiquetas queda inmediatamente por encima de las columnas, usamos la opción “encima” de alineación de las etiquetas

Para quitar “serie 4” de la leyenda, lo seleccionamos y lo borramos (un clic para seleccionar la leyenda y un clic más para seleccionar la etiqueta “serie 4”)

Hacemos desaparecer los ejes de valores poniendo las opciones de marcas de graduación y etiquetas del eje a “ninguno”

Con esto terminamos. Podemos agregar un borde y sombra a las etiquetas del total

Uso de comodines (wildcards) en funciones de Excel

Cuando filtramos tablas o hacemos búsquedas en Excel podemos usar comodines (*, ?). ¿Podemos usarlos en las funciones? Bien, sí y no. Es decir, hay funciones que aceptan comodines en los argumentos y otras que no.

Por ejemplo, queremos evaluar si una celda contiene cierto texto. En caso afirmativo el resultado será "A", en caso negativo "B".

Obviamente =SI(A1="*no*";"A";"B") no funciona.

Pero veamos esta alternativa

Como pueden ver, =SI(ESNUMERO(HALLAR("no";A1));"A";"B") no requiere comodines para nuestro ejemplo.

Podemos ver cómo funciona esta fórmula descomponiéndola en sus partes:

La función HALLAR da la primera posición en la cadena de texto del texto buscado

=HALLAR("no";A2)

Si el texto no aparece el resultado el #¡VALOR!

La función ESNUMERO evalúa si el resultado de HALLAR es numérico. Cuando lo es da VERDADERO, cuando no lo es, también si el error, da FALSO. Estos resultados son los argumentos que usamos en SI.

Veamos un ejemplo más elaborado donde buscamos valores que en la segunda posición contienen una "n" y en la cuarta posición una "o".

Como ven, en =SI(ESNUMERO(HALLAR("?n?o";A2));"A";"B") estamos usando el comodín "?" para señalar que la primera y la tercera posición puede ser ocupada por cualquier valor, pero la segunda y la cuarta deben ser "n" y "o" respectivamente.

La función COINCIDIR también acepta comodines.

También la función CONTAR.SI. Por ejemplo, en la lista anterior, si queremos contar todos los nombres que terminan con la letra "o" usamos

=CONTAR.SI($A$2:$A$7;"*o")

De la misma manera SUMAR.SI comodines pero SUMAPRODUCTO no.