Influcharla: tablas dinámicas, scan secuencial y datos agrupados

Influcharla: tablas dinámicas, scan secuencial y datos agrupados

John Vergara nos acompaña en esta fantástica sesión repasando algunos de los casos más interesantes vistos durante el mes

Una novedad de tablas dinámicas y un truco de matrices que resuelve medio Excel

Este segundo meetup de la comunidad repasa dos cosas muy distintas: una novedad que llega a las tablas dinámicas (probada en el canal Beta Insider) y un caso real de la comunidad que sirve de excusa para explicar una técnica que aparece una y otra vez: cómo comparar cada fila con la anterior dentro de una fórmula matricial.

La tabla dinámica que se actualiza sola

Es probablemente la petición más repetida por cualquiera que use tablas dinámicas: cambias los datos de origen y tienes que acordarte de pulsar Actualizar. Hasta ahora se resolvía con apaños de VBA que detectaban cambios en la hoja.

La opción nativa ya existe. Aparece en Opciones de tabla dinámica como "actualizar automáticamente", y se puede activar tabla por tabla; también hay un ajuste equivalente en la pestaña de datos para actualizar cuando cambien los datos de origen.

Ahora bien, en la sesión se prueban los bordes y salen tres comportamientos que conviene conocer:

1. El orden no se respeta al añadir valores nuevos. Si añades una categoría nueva, se coloca al final, no en la posición que le tocaría. La solución es un poco contraintuitiva: aunque la tabla ya salga ordenada por defecto, fuerza una ordenación explícita. A partir de ahí la tabla recuerda que la ordenaste tú, y coloca cada valor nuevo en su sitio. Importa poco en una tabla de ejemplo y mucho en algo con estructura fija, como una cuenta de resultados.

2. Las colisiones ya no avisan igual. Antes, si la tabla al crecer chocaba con celdas escritas, salía un aviso preguntando si querías continuar. Con la actualización automática, la tabla simplemente se queda parada en el punto del choque y muestra un resultado incompleto, sin avisar. Al quitar el obstáculo se recompone sola.

3. El aviso sí aparece si actualizas a mano. Forzando la actualización manual vuelve a salir el mensaje de colisión de siempre.

Como es una funcionalidad todavía en fase de pruebas, algunos de estos comportamientos pueden cambiar antes de llegar a producción.

El caso del tanque: quedarse solo con el consumo

El segundo bloque parte de un problema planteado en el grupo. Hay una cisterna con una toma de volumen cada 5 minutos. El nivel baja mientras se consume, y en algún momento se rellena y vuelve a subir. La curva tiene forma de sierra.

La pregunta: ¿cuánto se ha consumido cada día? Es decir, hay que sumar solo los tramos de bajada e ignorar por completo los de llenado.

El problema de fondo: mirar la fila anterior

Para saber si un valor es consumo o llenado hay que compararlo con el valor inmediatamente anterior. Y ahí aparece un hueco real en las funciones de matrices dinámicas:

  • BYROW y MAP recorren fila a fila, pero cada fila solo se ve a sí misma.
  • SCAN sí arrastra información de las filas anteriores, pero lo hace sobre el acumulado, no sobre el valor anterior suelto.

Se puede tirar de DESREF, pero hay una solución mucho más limpia.

El truco: desplazar la matriz una fila

La idea es construir una segunda columna que sea la misma serie corrida una posición hacia abajo. Así, en la misma fila tienes el valor actual y el anterior, y ya puedes restarlos.

Se hace en dos pasos:

  1. Apilar verticalmente un valor inicial encima del rango original. Ese valor de arranque depende del cálculo (aquí, el primer nivel del tanque).
  2. Quitar la última fila del resultado, porque al haber empujado todo hacia abajo sobra un valor por el final que nunca se usará como "anterior".

Para el segundo paso está EXCLUIR, que admite quitar filas por el principio (con un número positivo) o por el final (con un número negativo). Si prefieres no usar números negativos, TOMAR(rango; FILAS(rango) - 1) hace exactamente lo mismo.

El resultado son dos matrices del mismo tamaño, desplazadas una fila entre sí.

Comparar las dos series con MAP

Con las dos columnas alineadas, MAP puede recibir ambas matrices a la vez y darles nombre dentro de la LAMBDA:

=MAP(rango; rangoDesplazado; LAMBDA(actual; anterior;
    SI(0 > actual - anterior; actual - anterior; 0)
))

La lógica es directa: si la diferencia es negativa, ha habido consumo y nos quedamos con ella; si es positiva, el tanque se estaba llenando y devolvemos cero.

Ese cero es lo que hace que la columna sea acumulable sin más filtros: los tramos de llenado dejan de aportar.

Agrupar por día sin extraer el día

Falta el último paso: las tomas son cada 5 minutos y el resultado se quiere por día.

Aquí aparece un atajo elegante con AGRUPARPOR. En lugar de crear una columna auxiliar extrayendo día, mes y año, se envuelve la columna de fecha y hora en TEXTO con el formato deseado (por ejemplo, día y mes escrito). AGRUPARPOR recibe ese texto como clave de agrupación y funciona directamente, sin tratamiento previo.

Un aviso práctico de la sesión: al agrupar por texto, el orden es alfabético. Si necesitas orden cronológico, elige un formato de texto que se ordene igual alfabéticamente que numéricamente (por ejemplo, empezando por el año).

Conclusión

El caso del tanque es simpático precisamente por lo que tiene de poco corporativo —se agradece que no sea otra factura—, pero la técnica que enseña es de las que se reutilizan constantemente: apilar un valor inicial, recortar el final y comparar la matriz consigo misma desplazada.

Cada vez que un cálculo necesite "esto frente a lo de antes" —variaciones diarias, detectar cambios de valor, medir rachas— esa es la herramienta. Y es una de esas cosas que no se deducen leyendo la documentación de las funciones: aparecen cuando alguien plantea un problema real en el grupo y varios se ponen a resolverlo.

Más casos con estas funciones

Más contenido de Excel en InflueXcel